How to enter formulas in Numbers

It almost goes without saying that some sort of office productivity suite is required for a new Mac system, and while some might gravitate to Microsoft Office there are others available that can perform quite similarly.

Topher Kessler MacFixIt Editor
Topher, an avid Mac user for the past 15 years, has been a contributing author to MacFixIt since the spring of 2008. One of his passions is troubleshooting Mac problems and making the best use of Macs and Apple hardware at home and in the workplace.
Topher Kessler
5 min read

When you purchase a new Mac it almost goes without saying that some sort of office productivity suite is required, and though, based on popularity, some might gravitate to Microsoft's Office, there are others available as well, such as OpenOffice, NeoOffice, some standalone word processors, and also Apple's iWork suite.

iWork's programs include Pages for word processing, Keynote for presentations, and a relatively unique approach to spreadsheets with its Numbers program, which if you are used to Excel, offers some similar ways to manage data, albeit with some uniquenesses.

Unlike most spreadsheet programs that offer a seemingly endless grid on which you organize your data and perform calculations, Numbers offers a more inviting starting point. The basic spreadsheet is presented as a blank canvas with a standalone table on it, offering you the option to organize your data on multiple tables on the same sheet rather than using a different section of the same grid, an organization that might be a bit more intuitive to some.

Numbers Spreadsheet
A basic spreadsheet has one table on it but allows you to add more to customize your organization. As with other spreadsheets, typing an equals symbol invokes the formula editor. Screenshot by Topher Kessler

While a spreadsheet can be used for organizing and arranging data, its main purpose is, of course, to perform calculations on that data, and if you are familiar with Excel, then once you have a data set in a Numbers document, you can use similar approaches to performing calculations.

Starting a calculation and adding data to it
As with most other spreadsheet applications the way you start a calculation in Numbers is by typing an equals symbol as the first character in the target cell, which will tell that cell to display the result of the calculation you are about to enter.

With the equals symbol in place, you can then select a data cell to include its value in the calculation. To do this you can click an individual cell or click and drag to select multiple cells as is done in other spreadsheet programs, but in Numbers if you wish, you can also use the keyboard to specify your selection. By holding the Option key and using the arrow buttons, your selection will move one cell at a time, allowing you to pinpoint a specific cell. If your cell of choice is just the starting point for a selection, then you can include Shift along with the Option key and when you press the arrows, you will select cells in the direction of the arrows.

Cell naming in Numbers
For single-cell selections, Numbers uses the row and column names. Screenshot by Topher Kessler

At times you may have data in different tables or on different sheets that you would like to include in the same formula. While you can use the mouse to click and select values from those tables in your calculation, you can also use the keyboard by pressing Option-Command followed by Page Up or Page Down to cycle through the various tables in your document. When the table with your desired value is selected, you can then use the Option or Shift-Option keys and the arrows to navigate it and select the data you wish to use.

One thing to keep in mind with Numbers is that when selecting individual cells, the program will use the row and column titles to specify the cell in the formula, so for instance, if column B is titled "Weight" and you have "Charlie" as a name in the third row, then when you select B3, the program will show the words "Weight Charlie" in the calculation instead of "B3." However, if you select multiple cells, then Numbers will switch to using the classic "B3:B4" notation to indicate the columns and rows in the selection.

Relative or absolute values
Pressing Command-K toggles treating data as relative or absolute values. Screenshot by Topher Kessler

Another feature of Numbers is its ability to specify relative or absolute cell references when performing calculations. By default references are relative (meaning when copied the reference will change to point to a location relative to the new cell), but if you wish to make them absolute values then you will need to put dollar signs before the numbers. To make this easier, you can click the value or value range in your formula and then press Command-K, which will toggle through the options of setting the row, column, or both to be relative values.

Performing basic calculations
Now that you can select data values or groups of data values either with the mouse or keyboard, you can create a calculation. After you have entered an equals symbol to start your calculation, you can select your first data value followed by basic operators like plus and minus signs, a slash for division, and an asterisk for multiplication, and parentheses for specifying the order of operations to build your formula, and when complete, then pressing enter will display the results of the formula.

Entering data in formulas
Clicking a gray placeholder and then selecting your data will input it into that area of a formula. Screenshot by Topher Kessler

Beyond basic calculations are the options for inserting predefined formulas that act on groups of values, which include options like sum and average calculations. Numbers has several ways to enter formulas, though the easiest is to use the Function option in the Numbers toolbar.

To use Functions, just select the cell that you want to use for the calculation, and then click the Function tool, which will display a menu containing common functions like sums, averages, minimums, and maximums, but in addition you can choose "Show function browser" to see a list of all the functions that Numbers has to offer. These are organized into general groups based on use, such as Engineering, Financial, Statistical, Date and Time management, and Trigonometry.

Numbers formula browser
This browser allows you to view all the built-in formulas in Numbers. Screenshot by Topher Kessler

In the browser you can select a function to read more about its uses, and then insert it into your spreadsheet by clicking the Insert Function button at the bottom of the window. To then put your data into the function, click the gray value placeholder in the formula and then use the various data selection options mentioned above to select the cells you wish to include in the function.

After doing this you can use the arrows (without the Option or Shift keys held) to move your cursor around in the calculation editor and continue to insert more functions if needed or otherwise expand or refine your calculation, and when you are ready, just press Return to perform your calculation.

Overall this process for entering data and performing calculations in Numbers is very similar to other spreadsheet options (and is relatively cheap at $19.99 through the Mac App Store), so if you are familiar with other spreadsheets and have decided to give Numbers a try, you should feel right at home with handling data entry and managing formulas and calculations.

Questions? Comments? Have a fix? Post them below or e-mail us!
Be sure to check us out on Twitter and the CNET Mac forums.