Nine things everyone should know how to do with a spreadsheet

If your job (or even your personal life) requires you to do anything substantial with numbers, chances are you use a spreadsheet app to do it. As a Mac user, you've got plenty of choices among spreadsheet apps, but for most of us the choice comes down to three: Microsoft's Excel 2011; Apple's Numbers (version 3.2); and the browser-based Sheets section of Google Docs.

The one to use is really a personal choice, and that decision is not the focus of this article. (I personally prefer Excel, possibly because I've been using it for nearly 30 years). But regardless of the app you use, the question here is: How well do you know how to use it, really?

As a spreadsheet vet, I gave that question some thought and came up with the following list of things that I think every savvy spreadsheet jockey--not beginners, but people who've been using one of these apps for a while--should know. I'm not talking about any specific task. Rather, these are the techniques and concepts that I think you should know in order to graduate from casual to serious user.

1. Format Numbers

Because numbers can take many forms (decimals, integers, percentages), you need to apply formatting to make it clear what they mean. For example, most people would find it easier to understand 25% as opposed to 0.25. So, after you enter the number in a cell and select that cell:

Excel: Many often-used number formatting options are visible in the Home ribbon. You can also use the Format > Cells menu, then click Number in the dialog box that appears. All number formats are listed down the left edge of the dialog box; select one, and its options appear on the right.

The Custom option (recently added to Numbers as well) is especially useful, as you can combine text with your formatted number. For example, a format of #,##0.00 "widgets" would format your number with a comma if needed, two decimal places, and the word widgets after the number. Your cells will still be treated as numbers for use in calculations, but they will display with the defined text.

Numbers: Click the Format icon (the paintbrush) in the toolbar, then select the Cell entry in the resulting sidebar. Select the option (Automatic, Number, and so on) you want to use from the pop-up menu. You may need to set other values: For example, if you choose Numeral System, you'll need to set values for Base, Places, and how to represent negative numbers. (Numbers also includes special number formats such as Slider, Stepper, Pop-up Menu, and more; these can be used to create intuitive data entry forms.)

Sheets: All number formats can be found in the Format > Number menu; each formatting option appears in its own submenu. As in Excel, you can create custom number formats that mix text and numbers--but you have to find the option first, as it's buried in the Format > Numbers > More Formats submenu.

2. Merge Cells

Another useful formatting trick is to merge cells. Merged cells are what they sound like: two or more cells merged into one. This is a great way to center a header above a number of columns, for example. Merged cells are a powerful way to get away from the strict column-and-row layout of a typical spreadsheet.

To merge cells, you want to have a value only in the first cell you intend to merge, as values in any other cells will be wiped out by the merge. Select the range of cells to merge, by clicking on the first cell (the one containing the data) and dragging through the range you wish to merge.

Excel: Click the Merge entry in the Home ribbon, and then select one of the Merge options that appear in the pop-up menu--Merge and Center is what I use most often.

Numbers: Select Table > Merge Cells.

Sheets: Select Format > Merge Cells, then choose one of the Merge options, such as Merge Horizontally.

You can also merge cells vertically, which can be useful in tables where you have a parent cell (Salesperson, for instance) that contains multiple rows of data (for example, Product Sold and Units Sold).

3. Use Functions

You probably already know how to use basic formulas to do basic arithmetic on cell contents. But functions, which let you manipulate text and numbers in many other ways, are how you really unlock the potential of spreadsheets.

If quantity mattered most, then Excel would win, with (if I counted correctly) 398 unique functions. Google Sheets comes in a close second with 343, and Numbers has 282. But the total count is irrelevant, as long as the app has the functions you need.

All three apps share a large set of commonly used functions. For instance, to add up numbers across a range of cells, they all offer =SUM(RANGE) (where RANGE is a reference to the range of cells to be summed in the parentheses). To find the average of a range of numbers, they have =AVERAGE(RANGE). To round off a number to two decimal places, you can use =ROUND(CELL,2).

With 250-plus functions in each app, there's no way I can describe even a reasonable portion of them. But here are some of the less-obvious ones that I use all the time; they also happen to exist in the same form in all three apps:

=COUNT(): Counts all numeric entries in a range. Nonnumeric values will be skipped. To include nonnumeric values, use =COUNTA(RANGE) instead.

=MAX(RANGE) and =MIN(RANGE): Return the largest and smallest values in a range. Related to these two, I also often use =RANK(CELL,RANGE), which returns the rank of a given cell within the specified range.

=NOW: Inserts the current date and time, which is then updated each time the spreadsheet recalculates. (In both Excel and Sheets, you need to add a set of parentheses: =NOW().)

=TRIM(CELL): If you work with text that you copy and paste from other sources, there's a good chance you'll find extra spaces at the beginning or end of some lines of text. The TRIM function removes all those leading and trailing spaces but leaves the spaces between words.

Beyond these examples, the best way to get to know the functions in each app is to play around with its function browser. In Numbers, you'll see the browser as soon as you type an equal sign (=); it appears in the right sidebar and provides a nice description and example of each function. In Excel, select View > Formula Builder (in the Toolbox). In Sheets, select Help > Function List, which simply opens the Sheets webpage showing the list of functions.

4. Distinguish Between Relative and Absolute References

In the functions listed above, CELL and 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 VLOOKUP and 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 VLOOKUP works.

As one example, here's the formula in the On Hand row: VLOOKUP($A$2,Table 1::$A$2:$G$8,5,0)

$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.

7. Perform Logical Tests

Many times, you need to set a cell's value based on the results of one of more other cell values. For instance, in the worksheet for the shipping supplies company, the Order Alert column is either blank (if there's plenty of stock on hand) or it contains the Order Soon! warning (when inventory is getting low).

1 2 Page
Insider: How the basic tech behind the Internet works
Join the discussion
Be the first to comment on this article. Our Commenting Policies