4. Distinguish Between Relative and Absolute References
In the functions listed above,
RANGE are references to either an individual cell or a range of cells. So
=ROUND(C14,2) will take the value in cell C14 and round it off to two digits;
=SUM(A10:A20) will add up all the numbers in cells A10 through A20.
You can enter these cell locations either by typing them or by clicking (or, for ranges, clicking and dragging) the mouse.
Spreadsheet apps are also quite smart; if you copy
=SUM(A10:A20) and paste it into the column to the right, it will automatically change to
=SUM(B10:B20). This is called relative addressing, as the functions’ contents are relative to where they’re placed; it’s the default for formulas in all three apps.
If you don’t want the cell references to change when you copy or move a formula, all three apps offer a mode called absolute addressing. An absolute address doesn’t change when copied to a new location. All three apps use the same symbol for creating one: a dollar sign before the row and/or column symbols in a formula. So instead of typing
A10:A20, for example, you type
$A$10:$A$20 to create a fixed formula that always refers to those cells, regardless of where you put it.
You can also lock only one direction: $A10:$A20 will always refer to column A, but if you copy the formula over one column and down 50 rows, it would change to $A60:$A70. Similarly, A$10:A$20 would lock the rows; copy this formula over one and down 50, and it would change to B$10:B$20.
If you’re typing cell addresses directly, all three apps let you simply type the dollar sign manually. But if you’re selecting cells with clicks and drags, Numbers has another way of switching between relative and absolute addressing.
Cell references added via clicking and dragging appear in small colored bubbles, with a triangle to the right; you click the triangle to pop up Numbers’ absolute/relative cell-addressing window. But while this method works, I find it more time-consuming than simply typing the dollar signs where I want them to be.
5. Name Cell References
Referring to cells by location may be convenient, but it can also make it hard to figure out exactly what a given formula is doing. It also means you need to remember the location of often-used cells, which can be tricky in a large spreadsheet. If you name cells (and ranges), however, you can make the formula easier to read, as well as make reusing those cells in other formulas easier.
Consider this formula as an example:
=PMT(C5/12,C6,C7). Just by reading it, you can probably guess that it returns a payment of some sort, and maybe you can tell that cell C5 contains an annual interest rate. But really, it’s not easy to discern what this formula is doing. Here’s the same formula using named cells:
=PMT(INT_RATE/12,TERM,LOAN_AMT). Now it’s a lot clearer what’s going on, and you no longer need to remember that cell C5 is the annual interest rate.
Excel: Select the cell or range you’d like to name, then select Insert > Name > Define, which will pop up a new window. Type the name you’d like to create in the first box, then click Add. Repeat for as many names as you’d like to define. Once you’ve defined all your names, Excel even provides a way to apply them to existing functions. Select Insert > Name > Apply, and you’ll get a little window showing all your named cells and ranges. Hold down the <Shift> key, click on the first name in the list, then click the last name in the list to select them all. Click OK, and Excel will insert the names into any function that references a named cell or range.
Once you’ve named a cell or range, the spreadsheet always uses it in formulas—even if you click on a cell, Excel will insert its name in the formula.
Numbers: Sadly, it doesn’t support named ranges.
Sheets: Select the cell or range you’d like to name, then select Data > Named Range. This will display a sidebar where you can type the name of the range and (if necessary) change the cell reference. Click Done, and you’ve created a named range (even if it’s just one cell). I’m not aware of any way to apply newly created names to existing formulas. Unlike Excel, Sheets won’t use a name unless you specifically type it in.
6. Extract Data From Ranges
One of the most-common uses of a spreadsheet is to create tabular data and then extract values from that data. Consider the following worksheet for a company that sells shipping supplies:
Your job is to answer coworkers’ queries, such as “What’s our cost on the packing peanuts?” and “How many rolls of tape do we have on hand?” You could, of course, just look at the table every time someone asked a question, but consider that the real-world version of the table may have hundreds or thousands of rows. There has to be a better way.
And there is: The
HLOOKUP functions pull data out of tables, by matching a lookup value to a value in the table. (These functions are identical in all three apps, so I’ll explain how they work in Numbers.)
VLOOKUP is used when your data is as shown in the table above: each item is on its own row, with multiple columns of associated data.
HLOOKUP is used when each item is in its own column, with multiple rows of associated data.
The layout of the formula is the same in each app:
VLOOKUP(LOOKUP_VALUE, COLUMN_NUMBER (ROW_NUMBER for HLOOKUP) TO RETURN, REQUIRE EXACT MATCH)
Using a few
VLOOKUP formulas, you can create a lookup tool to quickly return all the data about a given product. Here’s the same worksheet as above, but with a product-lookup table added to the top. I’ve also included the actual formulas that are generating the results, so you can see how
As one example, here’s the formula in the On Hand row:
$A$2 is an absolute reference to the value to match in the table—the contents of the green box, in other words.
Table 1::$A$2:$G$8 is the range of cells in which Numbers will search for a match for whatever’s in
$A$2. (This is a great example of why naming ranges would be handy in Numbers.) The
5 tells Numbers to return the value in the fifth column (the first column is column one); this is the column that holds the quantity on hand.
Finally, that trailing zero is very important: It tells the spreadsheet to return only exact matches. If you leave that off either lookup formula, Numbers will return fuzzy matches—matches that come close to matching the lookup value. In this case, that would be bad—if you make a typo in your lookup cell, you don’t want to see a closely matched product, you want to see error messages, letting you know there was something wrong with the lookup.
The other formulas are basically identical, differing only in which column number’s data is returned.