Nine things everyone should know how to do with a spreadsheet

Rob Griffiths
16 July, 2014
View more articles fromthe author
AAA
Help

Spreadsheets, help, macworld australiaIf your job requires you to do anything substantial with numbers, chances are good that you use a spreadsheet app to do it. For most of us the choice comes down to Microsoft’s Excel 2011, Apple’s Numbers (version 3.2) or the browser-based Sheets portion of Google Docs.

Whichever app you use, the question is: How well do you know how to use it, really?

I 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 general techniques and concepts that you should know in order to graduate from casual to serious user.

(As of this writing, Google was publicly previewing a new version of Sheets, one that comes much closer to Numbers and Excel in features and performance; that’s the version I used for the following tips.

If you’re a Google Docs user, you should switch to the preview version before trying these tips (you can easily switch back).

 

1. FORMAT NUMBERS

Because numbers can take many forms (decimals, integers, percentages), you need to apply formatting to clarify what they mean; for example, most people would more readily understand 25% as opposed to 0.25. So, after you enter a number and select that cell, take a few extra steps.

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

The Custom option (recently added to Numbers as well) is very useful, as it allows you to 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, and select the Cell entry in the resulting sidebar. Select an option (Automatic, Number and so on) from the pop-up menu. You may need to set other values. If you choose Numeral System, for example, 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 and more; these can help you create intuitive data-entry forms.)

numbers sliders

You can use Numbers’ formatting tools to create star ratings, checkboxes, sliders (shown here) and more. 

SHEETS. Number formats are found in the Format > Number menu, with each formatting option in its own submenu. You can create custom formats that mix text and numbers – but the option is buried deep in the Format > Numbers > More Formats submenu.

 

2. MERGE CELLS

Another useful formatting trick is to merge cells. This tactic is a great way to centre a header above a number of columns, for example.

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 action. To select the range of cells, click the first cell (the one with the data) and drag through the other cells 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 – I often use Merge and Center.

NUMBERS. Select Table > Merge Cells.

numbers merged

To create a header in your spreadsheet that’s centred over several columns of data, use the ‘Merge Cells’ command in Numbers. 

SHEETS. Select Format > Merge Cells, and 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.

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

numbers function browserWith 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 frequently 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 must add 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 the end of some lines of text. The TRIM function removes all of those leading and trailing spaces but leaves the spaces between words.

Beyond these examples, the best way to learn 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.

 

4. DISTINGUISH BETWEEN RELATIVE AND ABSOLUTE REFERENCES

In the functions listed above, CELL and RANGE are references to an individual cell or a range of cells. For example, =ROUND(C14,2) will take the value in cell C14 and round it to two digits; =SUM(A10:A20) will sum the numbers entered in cells A10 through A20. You can enter these cell locations either by typing them or by clicking (or, for ranges, clicking and dragging).

Spreadsheets are smart; if you copy =SUM(A10:A20) and paste it into the column to the right, the formula will change to =SUM(B10:B20). This behaviour 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 absolute addressing mode. An absolute address doesn’t change when you copy it to a new location.

All three apps use the same symbol for creating an absolute address: 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.

numbers addressingYou 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 coloured bubbles, with a triangle to the right; you click the triangle to pop up Numbers’ absolute/ relative cell addressing window. But although this method works, I find it more time-consuming than typing the dollar signs where I want them to be.

 

5. NAME CELL REFERENCES

Referring to cells by location may be convenient, but that technique can also make it hard to figure out 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 the formula 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 namingEXCEL. Select the cell or range you’d like to name, and select Insert > Name > Define, which will pop up a new window. Type the name you’d like to create in the first box, and then click Add. Repeat for as many names as you want 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 the first name in the list, and 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, this app does not support named ranges.

SHEETS. Select the cell or range you’d like to name, and then select Data > Named Range. This command displays 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 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, for instance, a 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 such a question, but the real-world version of the table may have hundreds of rows.

Here’s a better way: 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.)

numbers shipping supplies 1

You use VLOOKUP when your data is arranged to list each item on its own row, with multiple columns of associated data. You use HLOOKUP 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 VLOOKUP formulas, you can create a lookup tool to quickly return all the data about a given product. In the screenshot at the lower left, you can see a worksheet with a product-lookup table added at the top. In this sample I also included the formulas generating the results, to show how VLOOKUP works.

numbers shipping supplies 2

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 – meaning the contents of the green box. Table 1::$A$2:$G$8 is the range of cells Numbers will search for a match for whatever is in $A$2. (This is why naming ranges would be handy in Numbers.)

The 5 tells Numbers to return the value stored in column five (the first column is column one), which 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 – items 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 that there was something wrong with the lookup.

The other formulas are basically identical, differing only in which column number’s data is returned.

 

7. DO LOGICAL TESTS

Often, you need to set a cell’s value based on the results of one or more other cell values. 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 they satisfy a condition you define: =SUMIF(TEST_ RANGE, CONDITION, OPTIONAL_SUM_RANGE).

(If you leave out the OPTIONAL_SUM_ RANGE part, then TEST_RANGE is also used as the range of values to sum.)

In the worksheet for the shipping supplies company, =SUMIF(D2:D8,”>2.25”, E2:E8) will total the Qty On Hand column, adding only those items for which the Profit is more than $2.25.

In this particular 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, CONDITION[,TEST_ RANGE2,CONDITION2, etc.]).

If you wanted, say, 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 more than $2.25 (“>2.25”) and if the Build Cost (B2:B8) is less than $3.00 (“<3″).

The third (and, for me, the most frequently 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 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 to 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). While that works, you have other ways of mixing text and numeric results, thanks to string-based formulas.

Consider our shipping supplies company again. Say you want to prepare a report showing the total investment (Build Cost X Quantity On Hand) for a given product. You could quickly do the maths and then type out an email with the details. But instead of doing that yourself, 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 serve to join one formula value, including text strings, to another. For example, to construct the sentence above (in any of the three programs), 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 function automatically formats in currency, complete with the dollar sign.

 

9. USE CONDITIONAL FORMATTING

As I covered earlier, you can format numbers, text and cells in the three spreadsheet apps. But they all 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. This effect is fine if you’re creating line dividers in your table of data. But if you’re attempting to highlight a specific type of number (when you reach the inventory-order point, say), fixed formatting doesn’t 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, which we used earlier to fill in the Order Alert column. Instead of having the formula display an alert only when supplies are low, we can modify it to display a message, when appropriate, 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, you can change the cell’s format based on the value.

EXCEL. Select the range from G2 to G8 (in this example), and then select Format > Conditional Formatting. In the 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 another window.

excel conditional formatting

Conditional formatting in Excel.

In this newest window, leave the Style pop-up set to Classic, and 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 the Font tab, set the Color pop-up to red, and click the Bold entry in the Font Style box. Make sure that nothing is filled in on the Border and Fill tabs, and then click OK to close the fourth window. Click OK again to close the third.

Now do the same thing again, starting with clicking the plus sign. But this time, type Stock OK in the textbox entry, set the font colour 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 apply the rules.

numbers conditional resultsNUMBERS. Select the range of cells (G2:G8), and then click the Conditional Highlighting button on the Cell tab of the Format sidebar. This action will change the sidebar; click the Add a Rule button to display a pop-up list of rules, and 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 item, Custom Style. This action will add yet another panel to the sidebar; select a red tone in the colour wheel, and click the B button for bold text. Finally, click the Done button.

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, and 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 nonbolded green text. Click Done; you’ll see both bold red and normal green text in the cell.

SHEETS. Select the G2:G8 range and then select the Format > Conditional Formatting menu item. In the dialogue box that appears, set the first pop-up to Text contains, and then type Order Soon! in the textbox. Check the Text box to format the text, and click the next seemingly empty box on the right to display the colour picker; choose red.

You might also want to check the Background box and choose an eye- catching background colour, such as bright yellow, because Sheets doesn’t let you alter the font’s appearance with conditional formatting.

sheets conditional formatting

Google Sheets’ conditional formatting tool.

Click the Add another rule link and repeat the steps, but type Stock OK in the textbox, and use green for the text colour. After you’ve set up the second rule, click Save rules to see the results of your work.

One Comment

One person was compelled to have their say. We encourage you to do the same..

  1. (Dr) Kaiying Chan says:

    I have been using Appleworks for well over a decade, and I still stick to the last version which is version 6 (and so MacOS 10.6.8) for spreadsheets. It’s more user friendly to use than all the others, and adequate to handle all personal budgetting, retirement planning, tax returns, etc.

    The others are just overkill and unnecessary for personal use. And, despite their sophistication, they are not adequate for work on simple statistics, e.g. Poisson stats or even Brownian motion.

Leave a Comment

Please keep your comments friendly on the topic.

Contact us