Chapter 18
In This Chapter
Opening, saving, and creating spreadsheets
Selecting cells, entering data, and editing data
Formatting cells
Adding and removing rows and columns
Creating simple calculations
Adding charts to your spreadsheets
Printing a Numbers spreadsheet
Are you downright afraid of spreadsheets? Does the idea of building a budget with charts and all sorts of fancy graphics send you running for the safety of the hall closet? Well, good iMac owner, Apple has once again taken something that everyone else considers super-complex and turned it into something that normal human beings can use! (Much as Apple did with video editing and songwriting — heck, is there any type of software that Apple designers can’t make intuitive and easy to use?)
In this chapter, I get to demonstrate how the Numbers spreadsheet program can help you organize data, analyze important financial decisions — and yes, even maintain a household budget! You’ll soon see why Numbers is specifically designed with the home Mac owner in mind.
Just in case you’re not familiar with applications like Numbers and Microsoft Excel — and the documents they create — let me provide you with a little background information.
A spreadsheet organizes and calculates numbers by using a grid system of rows and columns. The intersection of each row and column is a cell, which can hold either text or numeric values (along with calculations called formulas and functions that are usually linked to the contents of surrounding cells).
Spreadsheets are wonderful tools for making decisions and comparisons because they let you “plug in” different numbers — such as interest rates or your monthly insurance premium — and instantly see the results. Some of my favorite spreadsheets that I use regularly are
Like Pages (the Apple desktop publishing application; see Chapter 17), Numbers ships with a selection of templates that you can modify quickly to create a new spreadsheet. For example, after a few modifications, you can easily use the Budget, Loan Comparison, and Mortgage templates to create your own spreadsheets.
To create a spreadsheet project file, follow these steps:
Numbers displays the Template Chooser window, as shown in Figure 18-1. (To display the Template Chooser window and start a new Numbers project at any time, just choose File⇒New.)
The document thumbnails on the right are updated with templates that match your choice.
If you see an existing Numbers document in a Finder window (or you locate it using Spotlight or the All My Files location), just double-click the Document icon to open it; Numbers automatically loads and displays the spreadsheet. However, it’s equally easy to open a Numbers document from within the program. Follow these steps:
As an iMac power user running OS X Mavericks, you can save and load Numbers documents directly to and from your iCloud folder. All three iWork applications — Pages, Numbers, and Keynote — feature an Open dialog that can display the contents of your iCloud folder as well as your iMac’s internal drive. Click the iCloud or On My Mac button to switch locations.
If you’re unsure where the document is, click in the Search box at the top of the Open dialog and type in a portion of the document name, or even a word or two of text it contains. Note that you can choose to search your drive, your iCloud folder, or both.
Thanks to the Auto-Save feature in Mavericks, you no longer have to fear losing a significant chunk of work because of a power failure or a coworker’s mistake. However, if you’re not a huge fan of retyping data, period, you can always save your spreadsheets manually after making a major change. Follow these steps to save your spreadsheet to your hard drive:
If you’re saving a document that hasn’t yet been saved, the Save As sheet appears.
Common locations are your Desktop, Documents folder, or Home folder.
If the location you want isn’t listed in the Where pop-up menu, click the down-arrow button next to the Save As text box to display the full Save As dialog. Click the desired drive in the Devices list at the left of the dialog and then click folders and subfolders until you reach the desired location. Alternatively, type the folder name in the Spotlight search box at the top right and double-click the desired folder in the list of matching names. (As an extra bonus, you can also create a new folder in the full Save As dialog.)
After you save a Numbers document for the first time, you can create a version of that document by choosing File⇒Save. To revert the current document to an older version, choose File⇒Revert To. You can choose to revert to the last saved version, or you can click Browse All Versions to browse multiple versions of the document and choose one of those to revert to.
Apple has done a great job of minimizing the complexity of the Numbers window. Figure 18-2 illustrates these major points of interest:
You can use the scroll bars to move around in your spreadsheet, but when you enter data into cells, moving your fingers from the keyboard is a hassle. Numbers has various handy navigation shortcut keys that you can employ, and I list them in Table 18-1. After you commit these keys to memory, your productivity level shoots straight to the top.
Table 18-1 Movement Shortcut Keys in Numbers
Key or Key Combination |
Where the Cursor Moves |
Left arrow (←) |
One cell to the left |
Right arrow (→) |
One cell to the right |
Up arrow (↑) |
One cell up |
Down arrow (↓) |
One cell down |
Home |
To the beginning of the active worksheet |
End |
To the end of the active worksheet |
Page Down |
Down one screen |
Page Up |
Up one screen |
Return |
One cell down (also works within a selection) |
Tab |
One cell to the right (also works within a selection) |
Shift+Return |
One cell up (also works within a selection) |
Shift+Tab |
One cell to the left (also works within a selection) |
You can also use the mouse or trackpad to select cells in a spreadsheet:
After you navigate to the cell in which you want to enter data, you’re ready to type your data. Follow these steps to enter That Important Stuff:
A cursor appears, indicating that the cell is ready to hold any data you type.
Spreadsheets can use both numbers and alphabetic characters within a cell; either type of information is considered data in the Spreadsheet World.
Make a mistake? No big deal:
After you enter your data (in a cell, row, or column), you might need to format it so that it appears correctly. For example, say you want certain cells to display a specific type of number, such as a dollar amount, percentage, or date. Numbers gives you a healthy selection of number-formatting possibilities.
To specify a number format, follow these steps:
You can also change the alignment of text in the selected cells. The default alignment is flush left for text and flush right for numeric data. Follow these steps:
See “Navigating and Selecting Cells in a Spreadsheet,” earlier in this chapter, for tips on selecting stuff.
You can choose left, right, center, justified, and text left and numbers right. Text can also be aligned at the top, center, or bottom of a cell.
Shading the contents of a cell, row, or column is helpful when your spreadsheet contains subtotals or logical divisions. Follow these steps to shade cells, rows, or columns:
Figure 18-5 illustrates the controls for a gradient fill.
Numbers displays a color picker (also shown in Figure 18-5).
What’s that? You forgot to add a row, and now you’re three pages into your data entry? No problem. You can easily add — or delete — rows and columns. First, select the row or column adjacent to where you want to insert a row or column (or the one that you want to delete) and do one of the following:
If you select multiple rows or columns, right-click and choose Add. Numbers inserts the same number of new rows or columns as what you originally selected.
It’s time to talk about formulas, which are equations that calculate values based on the contents of cells you specify in your spreadsheet. For example, if you designate cell A1 (the cell in column A at row 1) to hold your yearly salary and cell B1 to hold the number 12, you can divide the contents of cell A1 by cell B1 (to calculate your monthly salary) by typing this formula into any other cell:
=A1/B1
Formulas in Numbers always start with an equal sign (=), and may include one or more functions as well. A function is a preset mathematical, statistical, or engineering calculation that will be performed, such as the sum (or average) of a series of cells.
“So what’s the big deal, Mark? Why not use a calculator?” Sure, you could. But maybe you want to calculate your weekly salary. Rather than grab a pencil and paper, you can simply change the contents of cell B1 to 52, and boom! The spreadsheet is updated to display your weekly salary.
That’s a simple example, of course, but it demonstrates the basis of using formulas (and the reason why spreadsheets are often used to predict trends and forecast budgets). It’s the “what if?” tool of choice for everyone who works with numeric data.
To add a simple formula within a spreadsheet, follow these steps:
The Formula Box appears within the confines of the cell.
Instead of scrolling through the entire function library, it’s easier to choose a category — such as Financial for your budget spreadsheet — to filter the selection. (Alternatively, you can click in the Search box and type a function name or keyword.)
To display more information about a specific function, click it in the right column of the Functions Drawer. In Figure 18-6, choosing the SUM function brings up a description at the bottom of the Functions Drawer.
The function appears in the Formula box, along with any arguments it requires.
In case you’re not familiar with the term argument, it refers to a value specified in a cell that’s used by a formula. For example, the SUM formula adds the contents of each cell you specify to produce a total; each of those cell values is an argument.
Numbers automatically adds the cell you indicated to the formula. Repeat this for each argument in the formula.
That’s it! Your formula is now ready to work behind the scenes, doing math for you so that the correct numbers appear in the cell you specified.
Sometimes you just have to see something to believe it — hence using the data you add to a spreadsheet to generate a professional-looking chart! After you’ve entered the data you want to chart, follow these steps:
To choose individual cells that aren’t adjacent, you can hold down the key as you click.
The Chart button bears the symbol of a bar graph.
Numbers displays the thumbnail menu you see in Figure 18-7. Note that you can display different categories of charts by clicking one of the three tabs at the top (2D/3D/Interactive), and the menu can be scrolled to reveal additional thumbnails by using the left- and right-arrow buttons.
Numbers inserts the chart as an object within your spreadsheet so that you can move the chart. You can drag using the handles that appear on the outside of the object box to resize your chart.
With your chart selected, click the Format toolbar button to display our old friend the Format Drawer, complete with the controls you can use to customize the appearance of your chart. For example, you can change the colors and add (or remove) the title and legend.