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

How can one cell display two possible values? By using a logical function. Each of these spreadsheet apps includes a number of logical functions; the three particularly useful ones below appear in all three apps.

The first one lets you sum numbers based on whether or not they satisfy a condition you define. =SUMIF(TEST_RANGE,CONDITION,OPTIONAL_SUM_RANGE) (If you leave out OPTIONAL_SUM_RANGE, then TEST_RANGE is also used as the range of values to sum.)

As an example, using the worksheet for the shipping supplies company, this formula will total the Qty On Hand column, adding only those items for which the Profit is more than $2.25: =SUMIF(D2:D8,">2.25",E2:E8).

In this formula, the Profit column (D2:D8) is compared to the test (is the value greater than 2.25?); if it's greater, then the amount in column E (E2:E8) is added to the sum.

The second function, SUMIFS, is related to SUMIF, but can take many optional pairs of test ranges and conditions: =SUMIFS(SUM_RANGE,TEST_RANGE,CONDITION1,TEST_RANGE2,CONDITION2, etc.).

For example, if you want the total quantity on hand for items with profit over $2.25 and build cost under $3.00, it would look like this: =SUMIFS(E2:E8,D2:D8,">2.25",B2:B8,"<3"). The formula will sum the Qty On Hand column (E2:E8), but only if both the profit (D2:D8) is over $2.25 (">2.25"), and if the Build Cost (B2:B8) is under $3.00 ("<3").

The third (and, for me, most often-used) logical function you should know how to use is the simple IF function. Using IF is a great way to vary cell results based on conditions being met or not being met. The syntax is pretty simple: =IF(CONDITION,RESULT_IF_TRUE,RESULT_IF_FALSE).

As one example, consider the worksheet of the shipping supplies company; the Order Alert column consists of nothing but IF statements that all look like this one from cell G2: =IF(E2/F2<1.25,"Order Soon!","").

The condition being tested is whether or not the ratio of the number of items on hand to the reorder point is less than 1.25 (125 percent). If it is, it's time to order, and the "Order Soon!" warning appears. If it's not, then all is fine, and we leave the cell empty by specifying an empty string as the False result.

IF statements can get very complicated, as they can be nested and can include not just static text, but also references to other cells, or even ranges of cells.

8. Mix Text and Formula Results

In the section on number formatting, I explained how to add text to a custom number format (in Excel and Sheets; not in Numbers). While this works, there are other ways of mixing text and numeric results, thanks to string-based formulas.

Consider our hypothetical shipping supplies company again. Assume you want to prepare a report for management, showing the total investment (Build Cost X Quantity On Hand) for a given product. Of course you could quickly do the math and then type out an email with the details.

Instead of doing that yourself, though, you could have your spreadsheet app build the sentence for you:

"The total investment in Tape is $262.50 (we have 150 in stock at $1.75 each)."

To do this, you need a special character: the ampersand (&). The ampersand can be used to join one formula value, including text strings, to another.

So, for example, to build the sentence above (in any of the three apps), the formula would look like this:

="The total investment in "&A8&" is "&DOLLAR(B8*E8)&" (we have "&FIXED(E8,0)&" in stock at "&DOLLAR(B8,2)&" each)." The trick here is to use the DOLLAR and FIXED functions; these convert numbers to text that looks like numbers; the DOLLAR version function automatically formats in currency, complete with the dollar sign.

9. Use Conditional Formatting

As covered earlier, you can format numbers, text, and cells in all three of these spreadsheet apps. But all three share a limitation when using traditional formatting: once something is formatted, it retains that format regardless of what might happen to the data in the cell. If you're creating the line dividers in your table of data, this isn't much of a problem. But if you're trying to highlight a specific type of number (when you reach the inventory-order point, say), then fixed formatting isn't much help.

That's when conditional formatting is really handy. It's just what it sounds like: Formatting that changes based on conditions you specify. Numbers refers to this feature as Conditional Highlighting, and you'll find it in the Cell tab of the Format sidebar. Both Excel and Sheets refer to it as Conditional Formatting, and you'll find it with that name in the Format menu of both apps.

Conditional formatting is a complex topic; to fully explore it requires many more words than we have here. But as one example of what it can do for you, consider the IF function example, used earlier to fill in the Order Alert column. Instead of having the formula simply display an alert when supplies are low, we can modify the formula to display a message stating that there's plenty of inventory. That change is simple: =IF(E2/F2<1.25,"Order Soon!","Stock OK").

Ideally, the Order Soon! alert should be bold and red. But that doesn't make sense for Stock OK; that might be better rendered as light green and not boldfaced. By creating a conditional highlighting/formatting rule, it's possible to change the cell's format based on the value.

Excel: Select the range from G2 to G8 (in this example), then select the Format > Conditional Formatting menu item. In the new window that appears, click the plus sign (to add a new rule). When the New Formatting Rule window appears, set the Style pop-up to Classic, which will open yet another window (that's three windows, and you haven't even created a single rule yet).

In this newest window, leave the Style pop-up set to Classic, then set the second pop-up to Format Only Cells That Contain. Set the next two pop-ups to Specific Text and Containing, and then type Order Soon! in the text box. Set the Format With pop-up to Custom Format, which will open a fourth window. Click on the Font tab, and set the Color pop-up to red, and click the Bold entry in the Font Style box. Make sure nothing is filled in on the Border and Fill tabs, then click OK (to close the fourth window), and click OK again (to close the third).

Now do the same thing again, starting with the plus-sign click. But this time, set the text box entry to Stock OK, set the font color to green, and make sure the font style is normal, not bold. When you get back to the Manage Rules window, you should see both rules listed; click OK to (finally!) apply the rules.

Numbers: Select the range of cells (G2:G8), then click the Conditional Highlighting button on the Cell tab of the Format sidebar. This will change the sidebar; click the Add A Rule button to display a pop-up list of rules, then click the Text tab. Click the Is entry, set the first pop-up to Text Is, and type Order Soon! in the box. Click the triangle in the menu below the text entry box, and select the final entry, Custom Style.

This will add yet another panel to the sidebar; select a red tone in the color wheel, and click on the B button for bold text. Finally, click Done.

That formats the Order Soon! cells. But what about the Stock OK messages, which are presently just black text? With the G2:G8 range still selected, click Show Highlighting Rules in the sidebar, then click Add a Rule again. Repeat the steps as above, except change the text box to read Stock OK, and set the custom style to have nonbolded green text. Click Done, and you'll see both bold red and normal green text in the cell.

Sheets: Select the G2:G8 range, then select the Format > Conditional Formatting menu item. In the dialog box that appears, set the first pop-up to Text Contains, then type Order Soon! in the text box. Check the Text box to format the text, then click the next seemingly empty box to the right in order to display the color picker; choose a nice shade of red. You might also want to check the Background box and set a background color (bright yellow will get your attention), because Sheets doesn't let you alter the font's appearance with conditional formatting.

Click the Add Another Rule link, and repeat the steps, but type Stock OK in the text box, and use green for the text color. After you've set up the second rule, click Save Rules to see the results of your work.

This story, "Nine things everyone should know how to do with a spreadsheet" was originally published by Macworld.

What’s wrong? The new clean desk test
Join the discussion
Be the first to comment on this article. Our Commenting Policies