You may have been wondering if you were going to have to rewrite all your formulae into all the other cells under row 8…well rest assured you don’t. Spreadsheets provide a utility called “Autofill” that is going to make our lives much easier!
Start by selecting the range A8:M8, your selection should look something like this when you’re done:
At the bottom of Cell M8 you will notice a small black square. When you hover your mouse over it, the cursor changes to a cross. If you now left mouse click and drag that square down to Row 37, “Calc” will automatically fill in all the formulae in the selection. (Question: Why did we fill to Row 37?)
The Autofill utility can very quickly highlight problems in our formulae. If your cells don’t look correct then we may need to revise a formula or two. In our example the most obvious error is in Cell E9 which should have a score in it but doesn’t:
Can you pick why it didn’t work? Have a look at the formula in Cell E8 which is definitely correct and the formula in Cell E9 which is definitely wrong:
Cell E8 : =IF(AND(MID(gv.G1,1,1)="1",gv.G5<>""),gv.G5,"") Cell E9 : =IF(AND(MID(gv.G2,1,1)="1",gv.G6<>""),gv.G6,"")
Notice that Autofill has automatically increased the references to the cells. This is amazingly helpful and saves you having to create each formula individually…however, the reference to “gv.G1” in our original formula needs to stay the same throughout all the formulae in the Task 1 column.
Absolute vs. Relative Cell referencing
This problem highlights exactly why we need to be able to have absolute cell references and relative cell references. By default, all our cell references are relative, meaning that if we use autofill to replicate formulae, it will do so while maintaining the relative differences between rows and columns.
But if we want our cell reference to be absolute (and never change when using autofill) then we need to add a dollar symbol in front of the column reference and/or the row reference.
- gv.G1 : Both Column G and Row 1 are relative
- gv.$G1 : Column G is absolute, Row 1 is relative
- gv.G$1 : Column G is relative, Row 1 is absolute
- gv.$G$1 : Both Column G and Row 1 are absolute
Returning to our example:
So in our example, when we drag the formulae down we need the row reference to remain the same, therefore we need to put a dollar symbol (“$”) in front of the Row reference in all the Task formulae…so undo the autofill we just tried and alter your formulae before trying again…this time drag your formulae down to Row 38:
Once again (at least in my spreadsheet) I have a couple of errors. My Student ID cells are attempting to fill in blank squares with a zero and my SURNAME field is giving me an error because it’s trying to apply functions to a blank cell. Both of these errors are easily fixed with an appropriate “IF” function wrapped around them…so once again I’ll undo my autofill and have another go:
Okay, so this time row 38 contains nothing, which indicates my formulae are outputting blanks when there is no data…exactly what I wanted. Technically I can now delete all the formulae that exists in Row 38, the easiest way to do this is to:
- Click on the number representing Row 38 (to select the whole row)
- Press the “Del” key (which deletes all content in the selection)
Final Autofill Comments
So, now that you’ve seen autofill in action, keep it in mind when building your formulae. If you think ahead when creating a formula you can often save yourself a lot of time!
Note also: Autofill can recognise patterns, so if you create 2 or 3 cells next to each other that mimic a sequence you’re trying to achieve, autofill will often copy your sequence with the rest of the cells you apply it to.