Learning Excel through games – Episode 1
Excel is used on a daily basis by people all over the world with most of them constantly learning Excel in their work. It has lots of features that can be used from a basic to a more advanced level, with one of these features being macros. An Excel macro allows you to save a number of steps, by either recording them as you do the action or by typing them in as code (or a bit of both!) and then assigning that macro to a button to use in your spreadsheet.
In this blog, we have an example of an Excel macro for you to look at. If you would like to try it out, get in touch and we can send you a copy of the spreadsheet.
In this spreadsheet, we have 3 students taking part in a long jump competition. They each jump 3 times and their distances are recorded. There is also a column with the world record distance in it. At the bottom of each student a formula has been used to pick up their longest jump distance. There is then a formula in cell F5 that works out who the winner is and shows it in the cell, however the cell font colour has been coloured white to hide it until the button above is pressed. This button has a macro assigned to it that will show the name of the winner and fill the cell red to show it when pressed. How the macro works is explained below.
Firstly the formula in cell F5 is this: =INDEX($B$1:$D$1,MATCH(E5,B5:D5,1)).
The MATCH part of the formula is looking at the students longest jumps (B5:D5) and then comparing them to the world record figure in cell E5. The ‘1’ part of the MATCH formula is saying that it is looking for the figure that is next closest in value to the world record figure. The range must be sorted in ascending order for the ‘1’ argument in the MATCH function.
The INDEX part of the formula links the jump value worked out as the highest to the name that it correlates to (so it returns the name of the student that jumped the furthest).
However for this to all work, the highest figures at the bottom need to be ordered in value from left to right (lowest to highest). Normally sorting is carried out vertically but in this spreadsheet, we need the sorting to be carried out horizontally. This is changed through using the options button in Data, Sort. Under here you can change the orientation of the sort.
All of this comes together when a macro is recorded. The macro recording to begin with is just of the ‘longest jump’ figures being sorted. Once recorded, the macro is edited to add some VBA code in to fill the cell F5 red so that the name of the student stands out. Once this is done the macro can then be assigned to the button so that when it is pressed, it all comes together and the name of the student who has won appears in the cell with a red background!
Want to give it a go? Then get in touch so we can send you a copy to try!
Once you have done it and a name has appeared, change a distance of a student who didn’t win so that they would win, reset the fill colour cell F5 to no fill and then press the button again and the new student should show as winner!
If you’re interested in learning Excel, then get in touch we would be happy to help!
We hope you liked this hint and tip on learning Excel through games. Why not take a look at our previous hint and tip post on creating a pivot table in Excel?