Nine things everyone should know how to do with a spreadsheet

By Rob Griffiths, Macworld |  Enterprise Software

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.

Enterprise SoftwareWhite Papers & Webcasts

White Paper

White Paper

White Paper

CRM's Dirty Little Secret: How to Avoid CRM Sticker Shock

See more White Papers | Webcasts