Author Archives: Chris Mortimer

Not in a reading mood? Watch a VIDEO of this article HERE on YouTube, or simply scroll down to view videos

DOWNLOAD the PRACTICE FILE here.
 

• This article and video series show how to create a student progress tracker in Excel
• The tracker converts grades to numbers, compares actual vs. target grades, allows fast data input and generates visual data summaries
• The techniques we are cover are VLOOKUP, dropdown menus, COUNTIF, and charts

RATIONALE
We are writing this article on GCSE results day, 2014, which reminds us that the focus on performance measurement in education is relentless. For the modern teacher, basic data analysis skills are crucial. Yet, our experience of Excel Development Projects in education tells us that most teachers lack confidence in Excel.

Our philosophy is built on the conviction that a small amount of Excel learning, focused on the important techniques, can have a disproportionate effect on a person’s analytical skill. There is a fundamental misunderstanding of Excel – that it is the preserve of IT specialists and analysts; in fact, the important techniques are straightforward to learn and, with some guidance, most are perfectly capable of unleashing Excel’s awesome computational power – as the following example illustrates.

WHAT THIS ARTICLE COVERS
In this article and video series, we teach three techniques (VLOOKUP, dropdown menus, COUNTIF) that are considered ‘advanced’, but are relatively simple to learn. We explain how to combine the techniques together to create a student progress tracker.

First, the article unpicks a thorny problem: how to convert grades (A, B, C, 2a, 2b, 2c) into numbers in order to compare target vs. actual grades using VLOOKUP. This is a common problem for teachers and, as a company, we have been asked to do this at least a dozen times in the past 2-3 years.

Second, the article explores how to create dropdown menus that will accelerate the process of data input and minimise annoying keyboard work.

Third, the article sheds light on how to create data summaries, in tabulated and visual form, that communicate the bigger picture – and that might impress senior management.

Ensure that you download and use the practice file so that you can practise and develop the skill yourself.

DOWNLOAD the PRACTICE FILE here.

PART 1: CONVERT GRADES TO NUMBERS USING VLOOKUP

The first video (above) deals with the issue of comparing target and actual grades, and understanding the difference between them. Many education systems use letter-based rather than numerical grades – in the UK, for example, grades such as 2a, 2b, 2c are common. This approach offers a useful level of precision for teachers, but can be a nightmare when trying to quickly establish the difference between two grades for each individual in a group of students.

Excel can help by quickly converting the grades into a numerical value. Once the grades are in numeric form, it is easy for us to understand the difference between target and actual grades, and to track progress.

The formula we use for this is VLOOKUP. As explained in the above video, the formula can convert letters to numbers by referencing a table. It is first necessary, therefore, to create a table that contains all of the grades that you are using and an equivalent numerical value. A simple, two-column table with grades in the first column and numbers in the second will suffice. VLOOKUP can then ‘look up’ the grade in the table and return the appropriate number.

The video shows you how to build the formula step-by-step. The practice file (DOWNLOAD) contains a fully worked example for your reference. Don’t expect to get it right first time but, with practice, the skill will come.

If you are struggling with the VLOOKUP concept, take a look at our dedicated VLOOKUP video that features a much simpler example.

But, the video goes further. We implement two VLOOKUP formulae in the same cell, linked by a subtraction sign (-), one relating to the actual grade, and one to the target. This allows us to understand the difference – in numbers – between the actual and target grades, and to generate a simple, effective performance measure for each student. Nice!

Now, how can we speed up the process of data input in the student progress tracker, avoid annoying keyboard work, and generate simple summary analysis?

PART 2: CREATE DROPDOWN MENU AND USE COUNTIF TO ANALYSE

The second video shows how to create a dropdown menu for speedy data input. Dropdown menus allow us to bypass the keyboard when inputting text, saving time and freeing up headspace. The process for creating them is relatively simple, and is broken down in detail in the video above.

A dropdown menu is actually a form of data validation, because it predetermines – or ‘validates’ what appears in a particular cell. So, dropdown menus are generated through the Data Validation dialogue box under the Data tab in Excel 2013. Make sure that you try to make your own!

Now we have implemented two mechanisms – one to measure progress on the basis of target and actual grades (VLOOKUP), and the other to facilitate quick data entry (dropdown menus). We have already created a useful tool; but we are missing a vital element: summary information.

Effective summaries convey the key messages in the dataset. In our case, we are seeking to understand the overall performance of the group. If a senior manager were looking at multiple groups, she would want to understand at-a-glance if the group is a cause for concern or not; she would want to understand how much progress is being made, without having to dig into the data. With this in mind, what tools are available in Excel to help us create an effective summary?

COUNTIF is another little-known formula that creates powerful analyses. Put simply, it counts the number of cells in a given range that meet a certain criteria. In the video, we use the formula to count the number of students who are achieving A-C grades. Further, we use a simple SUM formula to aggregate the progress scores of each student into a single measure of group progress. Cool!

Now, how can we add a visual analysis to increase the communicative power of the spreadsheet?

PART 3: CREATE SUMMARY TABLE AND CHART

The third and final video deals with the topic of creating a visual summary in the form of a chart, alongside a compact summary table. We use simple formulae to link worksheets together and ‘pull through’ the summary information to a separate summary worksheet.

This mode of analysis – presenting tabulated and visual information side-by-side – is usually an effective approach, not least because it pleases both the aesthetes and the numerically-inclined! Using the various techniques, we have a created an easy-to-use tool that communicates the key points in a easy-to-understand manner. We have used Excel to create a student progress tracker that works at an individual and group level.

CONCLUSION: WHAT WE HAVE LEARNED AND WHY IT IS IMPORTANT
Education is a data-intensive environment and teachers’ analytical skills are stretched on a daily basis.

It is our belief that, with the right guidance and mindset, you can approach data analysis tasks with confidence. It is is case of knowing the right techniques, and knowing how they fit together. Unfortunately, conventional Excel training tends to teach techniques in isolation and out-of-context.

At Tiger, we believe that progress in Excel is not ‘linear’ – x hours study does not result in x units benefit. Rather, there is a group of techniques that – if you master – can dramatically accelerate your Excel learning, unlocking exponential benefits as you improve. Further, we offer a different approach to learning Excel based on practical, in-context examples and experiential learning (learning through making mistakes) that demystifies ‘advanced’ techniques and, hopefully, goes some way to democratising the field of data analysis.

This is the fundamental idea behind the #ExcelRevolution – that focused learning of a handful of key Excel techniques can transform your analytical ability. This has knock-on effects on a company- or organisation-wide level, and even on the economy as a whole.

Good luck, and don’t hesitate to get in touch.

Done? Now learn more about how to create a dynamic league table in Excel:

Rank Things and Create a League Table in Excel.

Or, watch more videos on our YouTube channel.

Can you help us improve these resources? We would love to hear from you. Contact Us.