Here we’re going to look at how references to cells will allow you to do multiple calculations really quickly. We’ll look at:
- How to reference cells with each other
- How to quickly copy formulas across other cells
- How cell references change when they are moved around the sheet (relative cell references)
- How you can fix a cell reference so that it doesn’t change when moved (absolute cell references)
As always, let’s use an example to demonstrate this. In this sheet, I have some exam marks. In rows 9-14 I have the raw marks I’ve entered for my 4 students, for each of the exam sections, Reading, Writing, Listening, and Speaking.
First, I want to work out the total marks each student got. Let’s start with Fred. In B14, I’m going to add up the marks. So, in B14 I type:
=b10+b11+b12+b13
In case you haven’t read my previous post on formulas, the equals sign tells Sheets I’m going to start writing a formula, then I write the cells I want to add up, and press Enter, to enter the formula into cell B14.
Note, I would normally have used the sum formula here (=sum(b10:b13)), but I want to demonstrate the references.
Now we have Fred’s total. So, what about the other students’ totals? I could type in a similar formula for each one, but that would be a bit too much hard work. Slightly better, I could also copy B14 and paste it into cells C14, D14 and E14. Better still, I can just copy-drag the contents of B14 across into the cells C14 to E14.
Click on B14 and click on the little blue square on the bottom right-hand corner, a little black cross will appear. Holding down the mouse, drag it along to cell E14, then let go. This copies the formula across all those cells, and is the quickest way to do it.
I’ll just change the view and show the formulas in those cells (pressing Ctrl+acute accent key(the one probably next to the number 1 key-it depends on your keyboard layout).
Notice that the formula in Fred’s total, relates to column B, but in Barney’s column it relates to column C and so on. So, Sheets has automatically changed the reference, as it knows that I moved one cell to the right, and so it also moved the references one cell to the right.
This is what’s called relative cell referencing. The cell reference changes as it is moved on the sheet and it a really useful feature when you want to copy formulas without having to type the new references in.
OK, so now I have the raw marks and the totals, which is fine for comparing the students with each other, but what I really want to know is how well they did in each section. For that, I need to know the total marks possible for each section, then with that I want to work out the percentages they got in each part and overall.
First, I’ve entered the total marks per section in cells B3 to B6 and also added them up using the formula =sum(B3:B6) in cell B7.
Below the raw marks I’ve added a section to work out the percentages. All I did was select the raw marks table, copy it and paste it below. Then I changed the format of the cells to percentage, see my post on cell formatting if you’re not sure how to do this.
Now we need to calculate the percentages. In B17 I type:
=B10/B3
In other words, it’s dividing Fred’s reading mark by the reading section total.
Let’s just see what happens if I now copy that across to the other students. As before, click on the cell (B10), click on the little blue square and drag it across to the other students.
Oh, we have some errors! “#DIV/0!” means the formula in that cell is trying to divide something by 0, which isn’t possible. Can you guess what the problem is? Let’s have a look at the formulas and see what it is.
As you can see from the formulas, the first part of the formula is fine, it changes columns as it should, i.e. B10 to C10 to D10, etc, BUT the second part also changes. B3 changes to C3 to D3 to E3 which isn’t what we want. There’s nothing in C3, D3 or E3.
There is of course a solution, we need to tell the Sheets that we don’t want to change that reference when the formula is moved along the cells. So, to fix the column in place, we add a dollar sign before the column reference B. So, the formula becomes:
=B10/$B3
Let’s copy that across to the other students now.
Great it’s now working as we want it.
The dollar sign is used to create absolute cell references, i.e. the cell reference doesn’t change as you move it on the sheet.
Let’s add the formulas for Fred’s column. Click on B17, and copy-drag the cell down to the total row (21).
Looking at the formulas, we can see that the first part changed as we went down the rows, as it is a relative cell reference. So, for example, Fred’s writing percentage looked at his Writing raw mark in B11. We can also see that in the second part of the formula, that the row number has changed, i.e. B3, B4, etc. This is because we didn’t fix the row number, only the column letter, B, which is what we want.
Now, if the select all of Fred’s cells (B17 to B21) and copy-drag them along to the other students, we can see it has copied the formulas correctly. In particular, we can see that the column reference B hasn’t changed despite being moved across columns.
One note, on why I entered the total marks separately and referred to them in the formulas. I could have typed in a formula for each student, e.g. Fred’s reading would be =35/40, but the reason I put the total marks separately, is that sometimes later on, the exam is changed and the section marks are different.
So, instead of having to go back into each of the student’s cells and change, e.g. the 40 in each case, I just refer it to the cell B3. Then if the total mark has changed, I just need to change B3 and all the student’s cells will automatically use the new total.
If you wanted to fix the row number, the dollar sign would be put before the row number, e.g. b$3 If you wanted to fix both the row and column, then add a dollar sign before the column letter and row number, e.g. $b$3.
So, in summary, relative cell references are easy-going fellows, who adapt to wherever they are put and absolute ones are paid in dollars to never change.
eBooks available on Drive, Forms, Sheets, Docs, Slides, and Sheet Functions:
- “Beginner’s Guide to Google Drive” – iBooks store / Kindle store
- “Beginner’s Guide to Google Forms” – iBooks store / Kindle store
- “Beginner’s Guide to Google Sheets” – iBooks store / Kindle store
- “Beginner’s Guide to Google Docs” – iBooks Store / Kindle store
- “Beginner’s Guide to Google Slides” – iBooks Store / Kindle store
- “Google Sheet Functions – A step-by-step guide” – iBooks Store / Kindle Store
Baz Roberts (Google+ / Flipboard / Twitter)
6 comments
Comments are closed.