12 Spreadsheets with Numbers
With Numbers, you can create data spreadsheets, perform calculations, and create forms and charts.
→ Creating a New Spreadsheet
→ Calculating Totals and Averages
→ Styling Tables and Cells
→ Creating Forms
→ Using Multiple Tables
→ Creating Charts
Numbers is a versatile program that enables you to create the most boring table of numbers ever (feel free to try for the world record on that one) or an elegant chart that illustrates a point like no paragraph of text ever could.
Creating a New Spreadsheet
The way you manage documents in Numbers is exactly the same as you do in Pages, so if you need a refresher, refer to Chapter 11, “Writing with Pages.” Let’s jump right in to creating a simple spreadsheet.
1. Tap the Numbers icon on your Home screen to start.
2. Tap + and then Create Spreadsheet to see all the template choices.
3. Tap Blank to choose the most basic template.
Numbers Terminology
A grid of numbers is called a table. A page of tables, often just a single table taking up the whole page, is a sheet. You can have multiple sheets in a document, all represented by tabs. The first tab in this case represents “Sheet 1.” Tap the + to add a new sheet.
4. Double-tap in one of the cells to select the sheet. An outline appears around the cell.
5. A keyboard appears at the bottom of the screen. The keyboard in Numbers has many different modes. What you see here is text mode, where you can enter text and labels into cells. To get to the numeric entry keyboard to enter a value, tap here to switch to number mode.
The Action Menu
Notice that when you have selected a cell or other item in Numbers, you see an Action Menu button at the lower right. It has the name of the selected item, such as Cell, and a lightning bolt symbol. If the keyboard is open, the button appears at the top-right corner of the keyboard.
Tap this button to see some common actions for the selected item. For instance, for a cell you might see buttons to insert the current date, start a formula, copy, paste, or clear.
6. Use the keypad to type a number.
7. Tap the upper Next button, the one with the arrow pointing right.
8. The cursor moves to the column in the next cell. Type a number here, too.
9. Tap the Next button again and enter a third number.
10. Tap the space just above the first number you entered.
11. Tap the letters ABC above the keyboard to switch back to alphanumeric text input. The ABC changes to a 123.
12. Type a label for this first column.
13. Tap in each of the other two column heads to enter titles for them as well.
14. Tap to the left of the first number you entered. Type a row title.
15. Now enter a few more rows of data.
16. Tap the button at the bottom-right corner of the keyboard (looks like a mini keyboard with a down arrow below it) or tap outside of the table to dismiss the keyboard.
17. Tap any cell in the table to select the table.
18. Tap and drag the circle with two lines in it to the right of the bar above the table. Drag it to the left to remove the unneeded columns. To do this, you might need to drag the whole screen to bring the right side of the table into view.
19. Tap and drag the same circle at the bottom of the vertical bar to the left of the table. Drag it up to remove most of the extra rows, leaving two at the bottom for future use.
20. Double-tap the title of the table and give it a name.
Calculating Totals and Averages
One of the most basic formula types is a sum. In the previous example, for instance, you might want to total each column. You might also want to find the average of the numbers in the columns.
With tables, you typically put these kinds of calculations in footer rows. So, your table has header rows with the title of each column and footer rows with things like sums and averages.
1. Start with the result of the previous example. Select any cell in the table. Then tap the paintbrush to bring up the controls.
2. Tap Headers & Footer.
3. Increase the number of Footer Rows to 2. This turns the last two rows in the table to footer rows.
The Advantage of Footer Rows
You can put a formula to perform calculations in any cell of a table. So why bother with footer rows? They give you two nice features. First, you can now ask for the sum or average of an entire column, and Numbers knows to not include values in header and footer rows. Second, if you are entering values in the last cell of the last row above a footer row, and you tap the New key on the keyboard, Numbers inserts a new row and automatically moves the footer rows down.
4. Double-tap in the cell just below the bottom number in the first column.
5. Tap the Action Menu button to bring up common actions.
6. Tap the Sum button to place a SUM formula in the cell. Numbers knows that if you are entering a SUM formula into a footer cell, you want to get the sum of all the cells in that column, excluding the header and footer cells.
7. The formula for the cell appears in the text field. Because you are using a footer row, Numbers automatically assumes you want the sum of this column, so it puts the name of the column into the formula. Otherwise, you would have to tap the column letter at the top of the column, manually enter B, or select a range of cells such as B2:B6 for the formula.
8. The result of the formula instantly appears in the spreadsheet.
9. Tap here to dismiss the keyboard.
10. Repeat steps 2 through 4 for the other columns in the table.
11. Double-tap the last cell in the first column.
12. Tap the = button to go to the formula entry keyboard to manually enter the formula this time, instead of using the Action Menu button as shown in step 5. This gives you access to many more functions, and the ability to use basic operators like addition, multiplication, and so on.
13. Tap the functions button.
14. Tap Statistical to dig down into those functions.
15. Tap AVERAGE in the list that appears.
16. Immediately after tapping AVERAGE, tap the column heading B, to indicate that you want the average of column B.
17. Tap Return to dismiss the keyboard and complete the formula.
18. Do the same for the next two columns. Or, you can select the first cell, copy it, then paste it into the second and third. When you do, select Paste Formula so the formula, and not the value, is pasted. The references to the columns shift automatically so each averages the appropriate column.
19. You can also enter labels for these two footer rows.
Automatic Updates
If you are not familiar with spreadsheets, the best thing about them is that formulas like this automatically update. So if you change the number of Apples in Store C in the table, the sum in the last row automatically changes to show the new total.
Styling Tables and Cells
When working with tables, you can assign many different style options. It is easy to set the style for an entire table, but you can also make design choices for a single cell or group of cells.
1. Start with a table like the one we have been working with in the previous tasks. Select any cell in it.
2. Tap the paintbrush button.
3. Select Table.
4. Select a style. You see it reflected in the table immediately.
5. You can switch off the table name so it is no longer visible.
6. You can switch off the thin outline that surrounds the entire table.
7. Rows in the table normally alternate between light and dark background shades to make it easier to read. You can turn this off as well.
8. Grid Options lets you decide where lines appear between cells.
9. Swipe up for more controls. You can change the font and size used by the table.
10. Select a single cell or group of cells. In this case, select a cell to highlight to point out something in the table.
11. Tap the paintbrush button.
12. Tap Cell.
13. Tap any of the style buttons at the top to apply a style, such as bold.
14. Change the size of the text.
15. Tap to bring up a control to change the color of the text.
16. Tap Cell Fill to change the background color of the cell.
17. Tap Format.
18. This menu allows you to set a format for the cell. For instance, you could choose Currency, and a currency symbol, such as $, would be placed before the number. There are a wide variety of different formats to choose from.
Creating Forms
Forms are an alternative way to enter data in a spreadsheet. A form contains many pages, each page representing a row in a table. As an example, let’s create a sign-up table where people attending a meeting can enter their names, phone numbers, and email addresses.
1. In Numbers, create a new Spreadsheet.
2. Enter titles for three columns, such as Name, Phone, Email.
3. Shrink the table by grabbing the handle at the bottom left. You should end up with only the header row, the header column, and blank cells in the first row.
4. Tap the paintbrush button. Then go to Table, Headers & Footer, and change the number of header columns to 0.
5. Tap and enter a title for the table.
6. Tap the + button.
7. Tap New Form. This option only appears when you already have a table with labels in the header columns.
8. Select a table in one of the other sheets in this document. In this case, there is only one option, the Sign In Sheet table.
9. You are now in form entry mode. You can enter data in the three fields in the form.
10. Tap + to enter another row of data. Enter a few sample rows.
11. When you are done with some examples, tap the Sheet’s tab to go to the spreadsheet.
12. The data in the form is also in the spreadsheet table. The table and the form are linked, so changes to one are reflected in the other.
13. You can return to the form at any time by tapping its tab.
14. In the form, you can use the arrow buttons to move through the rows of data.
15. You can tap and drag this array of dots to flip quickly through large sets of data.
16. You can use the trash button to delete a row.
Using Multiple Tables
The primary way Numbers differs from spreadsheet programs such as Excel is that Numbers emphasizes page design. A Numbers sheet is not meant to contain just one grid of numbers. In Numbers, you can use multiple tables. By using multiple tables, you can track data more efficiently. Let’s look at an example.
1. Continuing with the example from earlier in this chapter, keep the current table and move it down to make space for another. Tap the + button at the top right to add a second table.
Selecting a Table
It can be difficult to select an entire table without selecting a cell. Tap in a cell to select it. Then tap the circle that appears in the upper-left corner of the table to change your selection to the entire table.
2. Using what you have learned in this chapter so far, create a small second table with prices as shown.
3. Expand the original table by adding one more column.
4. Name this column Revenue. Notice that when you expand the table, the total and average formulas in the footer columns are automatically added. The average now gives an error because it is attempting to find the average of an empty column.
5. Double-tap the first empty cell in the new column to bring up the keyboard.
6. Tap the = button to start entering a formula.
7. You want to multiply the number of items in each cell by the price that matches it. Tap the cell that represents Apples from Store A, tap the X symbol on the keyboard, and then tap the cell that represents the price of Apples in the upper table. Tap + in the keyboard and do the same for Oranges and Bananas.
8. The plan is to copy and paste this formula into all rows. When you do that, the references in the formula change to match the relative position of the cell it is being pasted into. You want that for the Inventory numbers, but you don’t want that for the prices. You want those to stay fixed. So tap the little up arrow next to the Price Apples item in the formula to bring up a menu.
9. Switch on all Preserve options for this item. This ensures that as you paste the formula elsewhere, it always points to the price of apples in the upper table.
10. Do the same for Price Oranges and Price Bananas.
11. The formula should now look like this. Notice the $ symbol is used to indicate that a reference is absolute and not relative to the position of the formula. Tap the green check mark to finish the formula.
12. Tap to select this cell.
13. Tap Copy from the context menu.
14. Tap the next cell, and then expand the selection to include all the empty cells in that column.
15. Tap Paste in the context menu that should appear.
16. Tap Paste Formulas.
17. The values for all the rows are calculated.
18. Note that the total and averages are calculated as well.
19. If you change one of the prices in the upper table, all the values for revenue change instantly to reflect it.
Enhance the Sheet
Another thing you can do is add more titles, text, and images to the sheet—even shapes and arrows. These not only make the sheet look nice, but can also act as documentation as a reminder of what you need to do each month—or instruct someone else what to do to update the sheet.
Creating Charts
Representing numbers visually is one of the primary functions of a modern spreadsheet program. With Numbers, you can create bar, line, and pie charts and many variations of each.
1. Start with a table similar to the one you have been working with in this chapter. Select the whole table. Numbers uses the header column and header row, along with the numbers in the body of the table, to build the chart.
2. Tap the + button.
3. Select charts.
4. You can select 2D, 3D, or Interactive charts. For this example, stick with a simple 2D chart. But take the time to explore the others as well.
5. You can swipe horizontally to see different chart styles. There are several pages of them.
6. Tap a chart to insert it into your sheet.
7. A chart is created using the data from your table. In this case, each store is represented along the horizontal axis, with a bar for each product. The vertical axis shows you the number sold.
8. With the chart selected, tap the paintbrush button.
9. Tap Chart.
10. You can select a different style for the chart.
11. You can turn various elements on or off.
12. You can also customize the text in the chart.
13. Swipe up for more options, such as the ability to change the chart type.
14. Tap X Axis or Y Axis.
15. You can turn off various portions of the chart related to the selected axis.
16. So what happens if you choose a different type of chart? Numbers tries its best to match the data to the chart. If you choose a pie chart, only one row of data is used. You can see when the chart is selected that the table above it uses colors to show which cell matches which slice of the pie.
