Chapter 2: Creating Spreadsheets with Numbers

In This Chapter

Opening, saving, and creating spreadsheets

Selecting cells

Entering and editing cell 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 Mac 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 like video editing, songwriting, and desktop publishing — 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 Numbers can help you organize data, analyze important financial decisions, and yes, even maintain a household budget! You’ll soon see why the Numbers spreadsheet program is specifically designed with the home Mac owner in mind.

Before We Launch Numbers . . .

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 of all kinds (including dates, times, and currency) by using a grid system of rows and columns. The intersection of each row and column is a cell, and cells can hold either text or numeric values (along with calculations that are usually linked to the contents of other, 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 include

Car and mortgage loan comparisons

A college planner

My household budget (not that we pay any attention to it)

Creating a New Numbers Document

Like Pages, Apple’s desktop publishing application, 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:

1. Click the Launchpad icon on the Dock.

2. Click the Numbers icon.

Numbers displays the Template Chooser window you see in Figure 2-1.

3. Click the type of document you want to create in the list to the left.

The document thumbnails on the right are updated with templates that match your choice.

4. Click the template that most closely matches your needs.

5. Click the Choose button to open a new document with the template you selected.

Opening an Existing Spreadsheet File

If a Numbers document appears in a Finder window (or you locate it using Spotlight or the All My Files location), you can 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:

1. From the Launchpad, click the Numbers icon to run the program.

2. Press +O to display the Open dialog.

3. Click the desired drive in the Devices list at the left of the dialog and then drill down through folders and subfolders until you locate the desired Numbers document.

The Search box at the top-right corner of the Open dialog makes it easy to locate a document. Click in the Search box, type a portion (or all) of the filename, and then choose Filename Contains from the pop-up menu that appears. (From the same pop-up menu, you can also search for text within your documents.)

4. Double-click the spreadsheet to load it.

If you want to open a spreadsheet you’ve been working on over the last few days, choose File⇒Open Recent to display recently used Numbers documents.

The Template Chooser window also sports both an Open Recent button and an Open Existing File button. Convenience is A Good Thing!

Save Those Spreadsheets!

Thanks to Mountain Lion’s AutoSave feature, you no longer have to fear losing a significant chunk of work because of a power failure or a co-worker’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 the first time you save your spreadsheet to your hard drive:

1. Press +S.

If you’re saving a document that hasn’t yet been saved, the Save As sheet appears.

2. Type a filename for your new spreadsheet.

3. From the Where pop-up menu, choose a location to save the file.

This allows you to select common locations, such as your Desktop, Documents folder, or Home folder.

If the location you want isn’t listed in the Where pop-up menu, you can also 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 drill down through 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. (Heck, you can even create a new folder in the full Save As dialog.)

4. Click Save.

After you save a Numbers document for the first time, you can create a version of that document by choosing File⇒Save a Version. To revert the current document to an older version, choose File⇒Revert Document. You can choose to revert to the last saved version, or you can click Older Version to browse multiple versions of the document and choose one of those to revert to.

Exploring the Numbers Window

Apple has done a great job of minimizing the complexity of the Numbers window. Figure 2-2 illustrates these major points of interest:

Sheets list: Because a Numbers project can contain multiple spreadsheets, they’re displayed in the Sheets list at the left of the window. To switch between spreadsheets in a project, click the top-level headings (each of which has a spreadsheet icon).

Sheet canvas: Numbers displays the rows and columns of your spreadsheet in this section of the window; you enter and edit cell values within the sheet canvas.

Toolbar: The Numbers toolbar keeps the most common commands you’ll use within easy reach.

Formula box: Use the Formula box to enter formulas into a cell, allowing Numbers to automatically perform calculations based on the contents of other cells.

Format bar: Located directly under the toolbar, the Format bar displays editing controls for the object that’s selected. (If you enter an equal sign into the Formula box, the Format bar changes into the Formula bar. No, I’m not making this up.) My goodness, this is starting to sound like that classic movie about the chocolate tycoon and those kids!

Navigate and Select Cells in a Spreadsheet

Before you can enter data into a cell, you need to know how to get to the cell where you want to enter that data. 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. For this reason, Numbers has various movement shortcut keys that you can use to navigate, and I list them in Table 2-1. After you commit these keys to memory, your productivity shoots straight to the top.

Table 2-1 Movement Shortcut Keys in Numbers

Key or Key Combination	Where the Cursor Moves
←	One cell to the left
→	One cell to the right
	One cell up
	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)

You can use the mouse to select cells in a spreadsheet:

To select a single cell, click it.

To select a range of multiple adjacent cells, click a cell at any corner of the range you want and then drag the mouse in the direction you want.

To select a column of cells, click the alphabetic heading button at the top of the column.

To select a row of cells, click the numeric heading button on the far left side of the row.

Entering and Editing Data 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:

1. Either click the cell or press the spacebar.

A cursor appears, indicating that the cell is ready to hold any data you type.

2. Type in your data.

Spreadsheets can use both numbers and text within a cell — either type of information is considered data in the Spreadsheet World.

3. To edit data, click within the cell that contains the data to select it and then click the cell again to display the insertion cursor. Drag the insertion cursor across the characters to highlight them and then type the replacement data.

4. To simply delete characters, highlight the characters and press Delete.

5. When you’re ready to move on, press Return (to save the data and move one cell down) or press Tab (to save the data and move one cell to the right).

Selecting the Right Number Format

After your data has been entered into a cell, row, or column, you still might need to format it before it appears correctly. Numbers gives you a healthy selection of formatting possibilities. Number formatting determines how a cell displays a number, such as a dollar amount, a percentage, or a date.

Characters and formatting rules, such as decimal places, commas, and dollar and percentage notations, are included in number formatting. So if your spreadsheet contains units of currency, such as dollars, format it as such. Then all you need to do is type the numbers, and the currency formatting is applied automatically.

To specify a number format, follow these steps:

1. Select the cells, rows, or columns you want to format.

2. Click the Inspector toolbar button.

3. Click the Cells Inspector button on the Inspector toolbar to display the settings you see in Figure 2-3.

4. Open the Cell Format pop-up menu and choose the type of formatting you want to apply.

Aligning Cell Text Just So

You can also change the alignment of text in the selected cells. (The default alignment for text is flush left.) Follow these steps:

1. Select the cells, rows, or columns you want to format.

See “Navigate and Select Cells in a Spreadsheet,” earlier in this chapter, for tips on selecting stuff.

2. Click the Inspector toolbar button.

3. Click the Text Inspector button on the Inspector toolbar to display the settings you see in Figure 2-4.

4. Click the corresponding alignment button to choose the type of formatting you want to apply.

You can choose from left, right, center, justified, and text left and numbers right. Text can also be aligned at the top, center, or bottom of a cell.

Do you need to set apart the contents of some cells? For example, you might need to create text headings for some columns and rows or to highlight the totals in a spreadsheet. To change the formatting of the data displayed within selected cells, select the cells, rows, or columns you want to format and then click the Font Family, Font Size, or Font Color buttons on the Format Bar.

Format with Shading

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:

1. Select the cells, rows, or columns you want to format.

2. Click the Inspector toolbar button.

3. Click the Graphic Inspector button on the Inspector toolbar.

Numbers displays the settings you see in Figure 2-5.

4. Open the Fill pop-up menu to select a shading option.

5. Click the color box to select a color for your shading.

Numbers displays a color picker (also shown in Figure 2-5).

6. Click to select a color.

7. Click the Close button in the color picker.

8. Click the Inspector’s Close button to return to your spreadsheet.

Insert and Delete Rows and Columns

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. Really — you can! First, select the row or column that you want to delete or that you want to insert a row or column next to, and do one of the following:

For a row: Right-click and choose Add Row Above, Add Row Below, or Delete Row from the contextual menu that appears.

For a column: Right-click and choose Add Columns Before, Add Columns After, or Delete Column from the contextual menu that appears.

If you select multiple rows or columns and choose Add, Numbers inserts the same number of new rows or columns as what you originally selected. Remember that you can also take care of this business from the Table menu. (Personally, I like to right-click.)

The Formula Is Your Friend

Sorry, but it’s time to talk about formulas. These equations 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

By the way, formulas in Numbers always start with an equal sign (=).

“So what’s the big deal, Mark? Why not use a calculator?” Sure, 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 that spreadsheets are often used to predict trends and forecast budgets).

To add a simple formula within your spreadsheet, follow these steps:

1. Select the cell that will hold the result of your calculation.

2. Click inside the Formula box and type = (an equal sign).

The Formula box appears to the right of the Sheets heading. Note that the Format bar changes to show a set of formula controls (also known as the Formula bar).

3. Click the Function Browser button, which bears the fx label.

It appears next to the red Cancel button on the Formula bar.

4. In the window that appears, as shown in Figure 2-6, click the desired formula and click the Insert Function button to add it to the Formula box.

5. After you finish, click the Accept button to add the formula to the cell.

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.

To display all the formulas that you’ve added to a sheet, click the Formula List button on the toolbar.

Adding Visual Punch with a Chart

Sometimes you just have to see something to believe it — hence the ability to use the data you add to a spreadsheet to generate a professional-looking chart! Follow these steps to create a chart:

1. Select the adjacent cells you want to chart by dragging the mouse.

To choose individual cells that aren’t adjacent, you can hold down the key while you click.

2. Click the Charts button on the Numbers toolbar. The Charts button bears the symbol of a bar graph.

Numbers displays the thumbnail menu you see in Figure 2-7.

3. 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 using the handles that appear on the outside of the object box to resize your chart. Figure 2-8 illustrates the 3-D chart I generated with just a couple of mouse clicks.

Click the Inspector toolbar button, and you can switch to the Chart Inspector dialog, where you can change the colors and add (or remove) the chart title and legend.

4. To change the default title, click the title box once to select it; click it again to edit the text.

After you add your chart to the sheet, it appears in the Sheets list, as also shown in Figure 2-8. To edit the chart at any time, just click the corresponding entry in the Sheets list.