12. Spreadsheets with Numbers
With Numbers you can create data spreadsheets, perform calculations, and create forms and 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.
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. Tap in one of the cells to select the sheet. An outline appears around the cell.
5. Double-tap the cell this time. An on-screen keypad appears.
6. Use the keypad to type a number. The number appears in both the cell and a text field above the keypad. Use this text field to edit the text, tapping inside it to reposition the cursor if necessary.
7. Tap the upper next button, the one with the arrow pointing right.
The four buttons just above and to the left of the keypad represent number, time, text, and formula formats for cells. If you select the number, you get a keypad to enter a number. If you select the clock, you get a special keypad to enter dates and times. If you select the T, you get a regular keyboard. Finally, if you select the equal sign (=), you get a keypad and special buttons to enter formulas.
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. The keypad changes to a standard keyboard to type text instead of numbers.
11. Type a label for this first column.
12. Tap in each of the other two column heads to enter titles for them as well.
13. Tap to the left of the first number you entered. Type a row title.
14. Now enter a few more rows of data.
15. Tap the Done button.
16. Tap and drag the circle with four dots in it to the right of the bar above the table. Drag it to the left to remove the unneeded columns.
17. 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 a few for future use.
Totaling Columns
One of the most basic formula types is a sum. In the previous example, for instance, you might want to total each column.
1. Start with the result of the previous example. Double-tap in the cell just below the bottom number in the first column.
2. Tap the = button to switch to the formula keypad.
3. Tap the SUM button on the keypad.
4. The formula for the cell appears in the text field.
5. Tap the green check mark button.
6. The result of the formula appears in the cell. Repeat steps 2 through 4 for the other columns in the table.
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.
Averaging Columns
We lucked out a bit with the sum function because it has its own button. What about the hundreds of other functions? Let’s start with something simple like column averages.
1. Continuing with the example from the previous section, double-tap on the cell below the total of the first column of numbers.
2. Tap the = button to switch to formula mode.
3. Tap the functions button.
4. Tap the Categories tab at the top of the menu, and tap Statistical from the Functions button menu.
5. Tap AVERAGE from the list of functions.
6. Now you get AVERAGE(value) in the entry field. The light blue means the “value” is selected and ready to be defined.
7. Tap cell B2 (Apples for Store A).
8. Drag the bottom dot to include cells B2 through B6. Don’t add the Total row to the average. The entry field should now read AVERAGE(B2:B6).
9. Tap the green check mark button.
10. The average of the column should now be in the cell. Tap it once to see the Cut/Copy/Paste menu.
11. Tap Copy.
12. Tap the cell below the total for the second column of numbers.
13. Drag the bottom-right dot to expand the area to cover the next cell as well.
14. Tap in the two cells to bring up the Paste option.
15. Tap Paste.
16. Tap Paste Formulas.
17. All three columns now show the average for rows 2 through 6. Notice how Numbers is smart enough to understand when you copy and paste a formula from one column to another, that it should look at the same rows but a different column.
Performing Calculations
So far, we have seen two simple formulas. Let’s see what else you can do with one of hundreds of different functions and the standard mathematical symbols.
1. Start with a table like this one. It shows the base and height measurements for three triangles.
2. Double-tap in the third column.
3. Tap the = button to enter a formula.
4. Tap the first number in the first column. “Base Triangle 1” should fill the entry field.
5. Tap the division symbol.
6. Tap the 2.
7. Tap the Multiplication button.
8. Tap the first number in the second column.
9. The entry field now reads “Base Triangle 1 ÷ 2 x Height Triangle 1.”
10. Tap the green check mark.
11. You get the result of 10.5, which is half the base of the triangle times its height, or the area of the triangle.
Note that a more careful mathematician would rather see it written “(Base Triangle 1 ÷ 2) × Height Triangle 1.” By grouping the base divided by 2 inside parentheses, you guarantee the correct result. You can use the parentheses to do that in the formula keypad.
Formatting Tables
Let’s move away from calculations to design. You have many formatting options to make your spreadsheets pretty.
Formatting Cells
1. Go back to the original example or something similar.
2. Select the six cells that make up the totals and average.
3. Tap the paintbrush button.
4. Tap Cells to see cell styling, formatting, and coloring choices.
5. Select Fill Color.
6. Tap the lightest shade of blue. You can also drag to the left to go to the second page of colors, which is actually a set of grays. The second page includes an option to reset the fill to the original style.
7. Tap the back arrow to go back to the Cells menu.
8. Tap B to make the text bold.
9. Change the selection to include only the row of averages.
10. Tap the paintbrush button again.
11. Tap Text Options.
12. Tap Color.
13. Choose the third darkest blue.
14. Tap the back arrow twice to go back two menus.
15. Tap Format.
16. Tap the blue arrow to the right of Number.
17. Set the number of decimal places. Try 2.
Formatting Whole Tables
Beyond just formatting cells, you can also use many options to change the basic style of your table. Let’s explore some of the options.
1. Starting with the table from the previous example; tap anywhere in the table to select it.
2. Tap the paintbrush button to bring up the menu.
3. Tap Table.
4. Try a different style, like the greenish one on the left, second down.
5. The new style replaces the formatting we did for the cells, so it is best to find a table style before you customize the cell styles.
6. Tap the Table Options button to explore other table options.
7. Tap the Table Name switch to add or remove the title.
8. Tap the Table Border switch to add or remove a border.
9. Tap the Alternating Rows switch to have the color of the rows alternate.
10. Tap Grid Options for more detailed control of the look of the grid used in the table.
11. Tap Text Size and Table Font to change the size and font used in the table.
Using Headers and Footers
Let’s continue with the previous example to explore headers and footers:
1. Tap the Back button to return to the main formatting menu.
2. Tap the Headers button to adjust the number of header rows and columns and add footer rows.
3. Tap the Footer Rows up arrow to increase the footer rows to 4.
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. Let’s continue with the previous example and use it to make a form.
1. Tap the + button, which looks like a second tab in the document.
2. Tap New Form. Note that in order to get the option to make a form, you need to have at least one column with a value in its header row.
3. Choose a table. We have only one, so the choice is simple. Tap Table 1 to see the first page in the form, which represents the first row of data from our table.
4. Tap the right arrow at the bottom of the screen to move through the five existing rows (pages) of data.
5. Tap the + button at the bottom of the screen to enter a new row of data.
6. Tap at the top of the screen to enter a row heading.
7. Tap in each of the three fields to enter data.
8. Use the next button on the on-screen keypad to move to the next field.
9. When you finish, tap the first tab, Sheet 1, to return to the original spreadsheet. You should see the new data in a new row.
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. Create a new blank spreadsheet and then fill it with some basic data to use as an example. Shrink the table to remove unneeded cells.
2. Tap the + button at the top of the screen.
3. Select Charts.
4. Page through six different chart color variations. Tap the chart at the top left.
5. You will now be asked to tap the chart and then select data from your spreadsheet.
6. Tap and drag over all the numbers in the body of your table to add all the rows of data to the table.
7. Tap Done.
8. Tap and drag the chart and position it on the sheet.
9. Tap on the chart to make sure it is selected.
10. Tap the paintbrush button. Notice that you can alter all sorts of properties using the Chart/X Axis/Y Axis and Arrange menu.
11. Tap Chart Options.
12. Tap Chart Type.
13. Tap Line to change the chart type to a line graph.
14. Tap outside the menu to dismiss it.
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.
1. Create a new, blank spreadsheet and fill it with data as in the example image.
2. Shrink the table to remove any unneeded cells.
3. Select the cells in the body.
4. Tap the paintbrush button.
5. Tap Format.
6. Tap Currency.
7. Tap outside the menu to dismiss it.
8. Tap the table to select it. Make sure just the table as a whole is selected, not a cell.
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.
9. Tap the paintbrush button.
10. Tap Table Options.
11. Tap the Table Name switch to give the table a name.
12. Tap outside the menu to dismiss it.
13. Select just the table name and change it.
15. Tap Tables.
16. Select the first table type.
17. Enter the data as shown and shrink the table to remove any unneeded cells.
18. Select the table title and change it.
To keep this tutorial short, I left some things out. For instance, you can select the date columns and change the formatting. Obviously each row represents a month. So, you don’t need the full date, including the day. You can change the date format of those columns to one that doesn’t include the day, only the month and year. Just select those cells and tap the paintbrush button and look under Format. Select Date & Time and tap the blue circle to choose a specific date and time format.
19. Select the entire second table.
20. Tap the Copy button.
21. Tap outside the table in a new location in the sheet.
22. Tap Paste.
23. Change the title and contents of the new table as shown.
24. Now select the second and third tables and expand them with one extra column each, as shown.
25. Double-tap in the first cell under Cost.
26. Tap on the = button next to the entry field to enter a formula.
27. Tap the Apples cell for the first row.
28. Tap × in the on-screen keyboard, and tap the cost of apples from the Cost and Price table.
29. Tap +.
30. Tap the Oranges cell and then tap × again. Then tap the cost of oranges and again tap +.
31. Tap the Bananas cell. Then tap × and tap the cost of bananas.
32. Tap the part of the formula that reads Cost Apples.
33. Turn on all four preservation switches to prevent the cell reference from changing as we copy and paste. We want the amount of inventory to change with each row, but the price from the other table remains the same.
34. Repeat steps 32 and 33 for the cost of oranges and the cost of bananas in the formula.
35. Tap the green check mark to complete the formula.
36. Tap the paintbrush button.
37. Change the format of the cell to Currency.
38. Copy that cell and paste it in to the three below it. When prompted, choose to Paste Formulas not Values.
The result is that you have a calculation based on data from two tables. You can complete this spreadsheet for practice, if you want. Create a similar formula for the revenue column of the next table, based on the price of each item and the amount sold.
