So you’ve got a spreadsheet with a few formulas in it. You want to use those formulas in other cells by just copying the cells with the original formulas. Easy right? Well, maybe not!
[bctt tweet=”copying cells in a spreadsheet can cause a few issues” username=”adifrancis”]
If you don’t set it up correctly in the first place, copying cells in a spreadsheet can cause a few issues. So, how do you prevent these issues?
The first step is to understand what is actually going on.
By default, in any spreadsheet all cell references are relative references. This means that when you copy a formula across multiple cells, the formula will change based on its relative position.
Suppose you have written a formula that looks like =C3+C4. If you copy this formula into neighbouring cells, it automatically changes to =C4+C5. If this is not what you intended, suddenly all your calculations will be looking rather odd.
You will see in the example below that we have made up a grade book for assessments. The formula for the first result(expressed as a percentage) is correct. We have simply divided the mark obtained by the total marks and multiplied it by 100.
Instead of writing out this formula multiple times we just copy it down to the other cells . This is where the problem occurs. Because the formula automatically changes based on the relative position of the cells, we end up dividing by the row above rather than the total of the test.
This is obviously wrong! What we want to do is to divide each student’s result by the total marks which is located in C2. We could re-write the formula each time, but there is a far easier way.
The $ sign in a cell reference tells the spreadsheet that the reference is absolute – that it should not change when the formula is copied to another cell.If you put a dollar sign in front of the column reference, then this will not change when you copy the formula. The same applies when a dollar sign is used in a row reference.
Using our simple example above, if we add the dollar sign to the row reference (the number), then =C$3+C$4 is copied as =D$3+D$4. The dollar sign is like an anchor and anchors a column or a row when you copy it. This is called absolute referencing.
Looking at our simple marks book, all we need to do is:
Then when we copy the formula down, we will always have the correct references and hence the correct percentages for each student.
To copy quickly
Highlight the cell you wish to copy down.
Move your cursor to the bottom right of that cell (where you see a small blue square).
Hover over the small blue square until it ‘turns into’ a cross
Double click with the cross and your formula will be copied down to the last row of your data.
The result looks like this.
[bctt tweet=”Bonus time-saving tip! Use F4 to save time when absolute referencing” username=”adifrancis”]
Bonus time-saving tip!
Adding in a $ each time you have a big formula can be time consuming. Here is another handy hint:
Type in the formula normally.
Highlight the cells in the formula that need a $ in front of them.
Push the F4 key on your keyboard and dollar signs will be added automatically.
By understanding what the spreadsheet is doing and learning a couple of handy hints, you will be creating spreadsheets quicker and using them more effectively to improve your workflow.
[bctt tweet=”You will be creating spreadsheets quicker and using them more effectively to improve your workflow.” username=”adifrancis”]
