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
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
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
=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:
=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.