1. Introduction to cell references
Welcome back!
2. Recap: cell addresses
Recall that each cell in a spreadsheet can be uniquely identified by its column and row index, which are combined into a cell address, such as D4.
So far, when we've wanted to use a cell's value in a formula, we've identified the cell using its cell address, looked at the value it contained, then manually inputted this value into another cell's formula.
This is a very manual and error-prone process, which won't scale to larger datasets. In this chapter, we'll discover how cell references can be used to bypass much of this manual work.
3. Cell references
Cell references are a lot like map references, where a letter and a number can be used to point to any geographical location,
4. Cell references
such as E3.
5. Cell references
Similarly, cell references allow us to point to cells to retrieve their values, rather than having to find and manually input these values, as we've done previously.
Let's say that we want to create a cell reference in cell E2 that will point to and retrieve the value from A3.
6. Cell references
First, we select cell E2,
7. Cell references
and start a formula by typing equals. This is really important, as cell references can only be used inside formulas.
8. Cell references
Then, type the address of the cell to point to, in this case, A3. Notice that the spreadsheet highlighted the referenced cell, which is a nice sanity check.
9. Cell references
E2 now returns the contents of A3.
10. Cell references
Let's update cell A3 to 13, and see what happens.
11. Cell references
E2 also updates to 13! This is one of the most powerful uses of cell references: they allow cells to react and update to real-time changes in other cells.
12. Circular references
We've just seen that when a referenced cell is updated, the referencing cell updates instantly. This is great in most cases, but what happens if the referencing cell references itself? Let's give it a try by updating cell A3 to reference itself.
13. Circular references
This throws a hashtag-ref warning, which is a reference warning.
14. Circular references
Hovering over the cell informs us that there is a circular dependency detected.
15. Circular references
This occurred because A3 is pointing to itself, and when it looks up the value contained in itself, it only finds the reference to itself and no value. This creates a loop whereby no value can be returned.
16. Circular references
What happens if we try referencing E2, which remember, references back to A3.
17. Circular references
We get the same error again.
18. Circular references
A reference that either directly, or by a chain of other references, refers to itself is called a circular reference, and any formulas created using one can't be computed.
19. Copying references
Now that we've seen a common pitfall of cell references, let's return to its strengths.
Cell references can be copied by dragging the bottom-right corner of the cell border either vertically or horizontally. To understand what's going on here,
20. Copying references
let's return to our map, where E2 is pointing to A3. If we copy the referencing cell down one square into E3,
21. Copying references
the referenced cell also shifts down by one, so E3 will point to A4.
22. Copying references
Similarly, if we copy the referencing cell across one square into F2, the referenced cell also shifts across by one, so F2 will point to B3.
23. Copying references - vertically
The same thing happens when copying cell references: copying a cell reference vertically will shift the reference up or down by the same amount, so copying the cell reference, B5, up by one cell will result in the reference, B4.
24. Copying references - horizontally
Likewise with copying cell references horizontally. Copying cell reference, B5, to the right by one will result in the reference, C5.
25. Let's practice!
Time to experiment with cell references in the exercises!