A macro is a recorded sequence of steps that get grouped together for use again later. The perfect scenario for a macro is the need to be able to sort our data. So let’s make a macro that sorts the data via Student ID. Start by making sure you are on the “Summary” worksheet, then select the Record Macro option, i.e:
Tools > Macros > Record Macro
Then follow this sequence:
- Click on the “gv” worksheet
- Select Cells D5 to R34 (which should be your data)
- Select “Data > Sort…“
- Choose “Column D” and “Descending“, then press “OK“
- Select Cell A1 (just to return Sheet “gv” to the state it was in)
- Select the “Summary” worksheet
Then when you select “Stop Recording” from the box that appeared when you started recording your macro, you will be asked where you want to save it and what you want to call it:
Notice I’ve called mine “SortByID” and I’ve saved it inside the Spreadsheet itself, make sure you do something similar.
Now that you’ve made one macro, go ahead and make another that sorts the data via the Raw Score column (which is Column P in the “gv” sheet).
Attaching a Macro to a Button
While you can go back to the Macros section and run your macro that way, it’s much easier to have it linked to a button that runs it for you. To do this we first need to get the “Form Controls” toolbar up:
View > Toolbars > Form Controls
And then we need to press the button in the top right corner of this new toolbar which takes us into “Design Mode” and allows us to create/edit form components. In our case, we’d like to draw a button like the picture below:
Notice I’ve left room for four buttons…make sure you do the same. We can then make alterations to this button by selecting the “Properties” option from the toolbar, i.e:
Notice I’ve changed what the button says and its name, I’m using a naming scheme that makes sense to me but you can call it whatever you like. And while you can’t see it in the screenshot, I have scrolled down in the properties list and changed the font size to be a little smaller since I’m having quite narrow buttons on this form.
Now in order to attach this button to the macro, I need to click on the “Events” tab of the properties window and then attach an event to the “Execute” action:
You then simply select the Macro you want to attach which in our case is “SortByID“. Once that’s done you will need to turn off “Design Mode” by clicking on that button in the top right corner of the “Form Controls” toolbar again, then VOILA! You have a button that sorts your spreadsheet via Student ID.
I’m wanting three more buttons on my spreadsheet as you can see below:
“Sort by Score” should be easy since you’ve now done “Sort by ID“, the other two really are just as easy, they simply need to select all the names of the students and change the font colour to white if you’re hiding them and black if you’re revealing them…simple!
So there you have it…macros might sound complicated but in the end they are merely there to help you. Okay, we’re nearly done, let’s finish this tutorial by looking at charts…