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