Chapter 18
IN THIS CHAPTER
Getting familiar with and navigating Numbers
Opening, saving, and formatting spreadsheets
Selecting cells, entering data, and editing data
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 again taken something that everyone else considers to be super complex and turned it into something that normal human beings can use! (Much as Apple did with video editing and songwriting. Is there any type of software that Apple designers can’t make intuitive and easy to use?) And Numbers is a free download from the App Store, to boot!
In this chapter, I demonstrate how the Numbers spreadsheet program can help you organize data and analyze important financial decisions for home and business — everything from a household budget to your company’s sales statistics!
In case you’re unfamiliar with applications such as Numbers and Microsoft Excel — and the documents they create — let me provide you 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. Cells can also hold calculations called formulas and functions, which 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. These are some of the spreadsheets that I use regularly:
Like Pages (Apple’s desktop publishing application, which I cover in Chapter 17), Numbers ships with a selection of templates you can modify quickly to create a new spreadsheet. 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:
Click the New Document button in the bottom-left corner of the Open dialog that appears.
Numbers displays the Choose a Template window, shown in Figure 18-1. (To display the Choose a Template window and start a new Numbers project at any time, just choose File ⇒ New.)
In the list on the left, select the type of document you want to create.
The document thumbnails in the center are updated with templates that match your choice.
If you see an existing Numbers document in a Finder window (or you find it by using Spotlight), just double-click the Numbers document icon to open it. Numbers automatically loads and displays the spreadsheet.
It’s equally easy to open a Numbers document from within the program. Follow these steps:
Press ⌘ +O to display the Open dialog.
As an iMac power user running macOS Monterey, you can save and load Numbers documents directly to and from your iCloud Drive. All three of Apple’s productivity applications — Pages, Numbers, and Keynote — feature an Open dialog that can display the contents of your iCloud Drive as well as those of your iMac’s internal drive. If the spreadsheet is stored in your iCloud Drive, click the Numbers item below the iCloud heading in the Sidebar on the left side of the dialog and then double-click the desired document thumbnail.
Drill down through folders and subfolders until you locate the desired Numbers document.
If you’re unsure where the document is, click the Search box at the top of the Open dialog and type a portion of the document name or even a word or two of text that it contains. Note that you can choose to search your drive, your iCloud Drive, or both.
Thanks to the Auto-Save feature in Monterey, you need not fear losing a significant chunk of work because of a power failure or a coworker’s mistake. But if you’re not a huge fan of retyping data, period, you can save your spreadsheets manually after making a major change. Follow these steps to save your spreadsheet to your iCloud drive or your internal drive:
Press ⌘ +S.
If you’re saving a document that hasn’t yet been saved, the Save As sheet appears.
Open the Where pop-up menu and choose a location in which to save the file.
Common locations are your iCloud Drive, Desktop, Documents folder, and 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 on the left side of the dialog, and click folders and subfolders until you reach the desired location. Alternatively, type the folder name in the Spotlight Search box in the top-right corner, and double-click the desired folder in the list of matching names. (As a bonus, you can 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 to an older version of the current document, choose File ⇒ Revert To. You can revert to the last saved version or click Browse All Versions to look through multiple versions of the document and choose one of those versions 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 shortcut keys you can employ, as shown in Table 18-1. After you commit these keys to memory, your productivity level will shoot straight to the top.
You can also use the mouse or trackpad to select cells in a spreadsheet:
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+Enter | One cell up (also works within a selection) |
Shift+Tab | One cell to the left (also works within a selection) |
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:
Click the cell or press the spacebar.
A cursor appears, indicating that the cell is ready to hold any data you type.
Enter your data.
Spreadsheets can use both numbers and alphabetic characters within a cell; either type of information is considered to be data in the Spreadsheet World.
Made a mistake? No big deal:
To delete characters: Select the cell, highlight the characters, and press Delete.
You can quickly step backward through changes you’ve made and undo them by using the Undo command. Press Command+Z or choose Edit ⇒ Undo from the Numbers menu.
After you enter your data (in a cell, row, or column), you may need to format it so that it appears correctly. Suppose that 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, which can make a big difference in readability for titles and “crowded” data that’s packed into close columns. The default alignments are flush left for text and flush right for numeric data. Follow these steps:
Select the cells, rows, or columns you want to format.
See the earlier section “Navigating and Selecting Cells in a Spreadsheet” for tips on selecting stuff.
Click the corresponding Alignment button to choose the type of formatting you want to apply.
You can choose left, right, center, and justified text with the strip of buttons that appear under the Text Color section. Click the buttons in the second strip to specify the left and right indent levels for text. Text can be aligned at the top, center, or bottom of a cell using the third strip of buttons.
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:
Click the triangle next to the Fill heading and choose a shading option from the Fill pop-up menu.
Figure 18-5 shows the controls for a gradient fill.
Click the color box to select a color for your shading.
Numbers displays a color picker wheel.
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 (or delete) a row or column, and do one of the following:
If you select multiple rows or columns, right-click and choose Add from the shortcut menu. Numbers inserts the same number of new rows or columns that you selected. You can also copy the contents of a row or column and paste them into any location in your spreadsheet.
It’s time to talk about formulas, which are equations that calculate values based on the contents of cells you specify in your spreadsheet. 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 in 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 figuring 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 basics of using formulas (and the reason that 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:
Type = (the equal sign).
The Formula box appears within the confines of the cell.
Click the category of calculation you want from the left column of the Inspector.
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 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 Inspector. In Figure 18-6, choosing the SUM
function brings up a description at the bottom of the Inspector.
After you select the perfect function in the right column, click the Insert Function button.
The function appears in the Formula box, along with any arguments it requires.
In case you're unfamiliar with the term argument, it refers to a value specified in a cell that a formula uses. The SUM
formula, for example, adds the contents of each cell you specify to produce a total; each of those cell values is an argument.
Click an argument button in the formula and click the cell that contains the corresponding data.
Numbers automatically adds the cell you indicated to the formula.
That’s it! Your formula is 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, so it can help to use the data you’ve added to a spreadsheet to generate a professional-looking chart. After you’ve entered the data you want to chart, follow these steps:
Select the adjacent cells you want to chart by dragging.
To choose individual cells that aren’t adjacent, you can hold down the ⌘ key as you click.
Click the Chart button on the Numbers toolbar.
The Chart button bears the symbol of a bar graph.
Numbers displays the thumbnail menu shown 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 you can scroll the menu to reveal additional thumbnails by clicking the left- and right-arrow buttons.
Click the thumbnail for the chart type you want.
Numbers inserts the chart as an object within your spreadsheet so that you can move the chart. You can drag by 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 your old friend the Inspector, complete with the controls you can use to customize your chart’s appearance. You can change the colors and add (or remove) the title and legend, for example.
You can easily send a Numbers spreadsheet to a USB or shared network printer. If you’d rather create an electronic copy of the document as an Adobe PDF file, you can click the PDF button that appears on the Numbers Print dialog; choose Save As PDF from the drop-down menu; and select a location where the file will be saved.
To print a Numbers spreadsheet on your default printer, follow these steps:
Choose File ⇒ Print from the Numbers menu bar.
Because a Numbers document can contain multiple spreadsheets, you can select a specific spreadsheet to print (by clicking it in the grid and then selecting the Print This Sheet radio button at the bottom-right corner of the window), or you can print all spreadsheets in the document (by selecting the Print All Sheets radio button in the same location).
Click the desired page orientation.
Spreadsheets with a large number of columns are often printed in landscape orientation to ensure that everything fits on the page. You can also scale the spreadsheet so that it automatically fits on the page by clicking the Fit button (next to the Content Scale slider).
Click Print at the bottom of the window.
Numbers displays the Print dialog. To display additional settings, click the Show Details button at the bottom of the sheet. (The PDF button appears in both views.)
Click the Print button to send the document to your printer.
Click the Share menu item and choose the Send a Copy to share your spreadsheet masterpiece with others (as an actual copy of the document file). You can choose to send the copy via Mail, Messages, Notes, or AirDrop.