From the author of

From the author of

Numbers is a spreadsheet application that benefits from tight integration with the other Apple productivity applications, Pages and Keynote. Numbers is available on OS X for Mac, iOS for the iPad and iPhone, and via web browsers.

NOTE

This article is Part 2 of a three-part series on Apple's productivity applications—Pages, Numbers, and Keynote. For more in-depth tutorials, check out my book Apple Pro Training Series: Pages, Numbers, and Keynote.

This article provides 10 tips for Numbers, highlighting ideas and functions that will help boost your productivity, but some of the advice is also applicable to Pages and Keynote. For example, Pages and Keynote can create tables and charts similar to those created in Numbers; however, for extra control over your data, you should create tables and charts in Numbers and then copy them into Pages and Keynote as needed.

Tip 1: Think of Numbers as a freeform canvas

Numbers documents can contain multiple sheets, each of which can be used as a blank canvas on which to add tables and charts. You might have expected these features; however, users often overlook the ability to add media such as photos or video. For example, when documenting a school's science lab experiment, adding photos and video of the processes involved could make for a visually rich presentation.

Figure 1 shows an example of a father and son's efforts to research facts about World War II. The charts and tables in Numbers were copied and pasted into Pages and Keynote for class presentations. Notice the size of the spreadsheet in Numbers; it offers plenty of space for adding special features to the research documentation.

Figure 1. The spreadsheets in Numbers are vast. Much of the text here is set in 10 pt, and the selected photo measures 6 × 5 inches.

Other types of spreadsheets might include business fact-finding, vacation planning, and so on. By adding screen grabs of websites or photos of the products or places you're researching, you can use Numbers as a number-crunching pin-board.

Tip 2: Use multiple tables rather than a large single table

Numbers lets you add tables to a sheet with ease. Where appropriate, however, break up calculations into multiple tables to make navigating your spreadsheet easier (see Figure 2).

Using smaller tables also helps to isolate any math problems in complex calculations. These concepts are explored further in Lesson 10, "Making Customized Calendars," in Apple Pro Training Series: Pages, Numbers, and Keynote. The lesson also explains the syntax for creating advanced formulas, which can lead to spreadsheets that work as mini-applications. The example shown in Figure 2 is a photographer's price calculator for prints. The spreadsheet can be used to choose a variety of paper types, sizes, and qualities, quickly returning a price that the photographer can quote to her clients.

Tip 3: Label that table!

It's a good habit to rename tables and charts as you create them in Numbers. Suppose you create multiple tables, as suggested in Tip 2. If you don't rename them, you'll be confronted with the default references (Table 1, Table 2, Table 3, and so on) when you try to apply formulas.

In Figure 3, the table has been renamed Price Calculator. The Inspector shows that the Table Name option is enabled. If this option were deselected, the table would still be identified in the Formula Editor as Price Calculator, but the label on the sheet would disappear.

Figure 3. Labeling tables and charts descriptively is a best practice.

In the same vein, when the sheet tab is clicked. a menu appears, listing all the tables and charts on that sheet. Notice in Figure 4 that the tables are labeled as Price Calculator and Paper Prices. Having descriptive labels helps you to find and jump to the required table or chart more easily than with the default table names of Table 1, Table 2, and so forth.

Tip 4: Change data formats as needed

Numbers will try to guess which data format to use, depending on what you type into a cell. Often Numbers will guess correctly, but sometimes you'll want to set the data format manually (see Figure 5).

Set telephone numbers as text; otherwise, any zeroes at the beginning of a phone number will disappear.

The Numbers application's guesswork can be a great time saver, too. In Figure 6, I typed Monday into cell A1 and then dragged across the adjacent columns. Numbers automatically added the remaining days of the week.

Figure 6. The combination of Numbers' guesswork and autofill is a great time-saver for filling in days of the week, months of the year, dates, or number sequences.

To explore formatting in more detail, see Apple Pro Training Series: Pages, Numbers, and Keynote.

Tip 5: Use interactive data formats

Numbers has five interactive data formats: Checkbox, Star Rating, Slider, Stepper, and Pop-Up Menu. Often, one of these formats will be the ideal solution for data input. For example, use Pop-Up Menu to create a custom list of items from which users can select, or use Slider to quickly apply a number value in a range of your choosing.

The example in Figure 7 comes from Lesson 9, "Making Interactive Spreadsheets," in Apple Pro Training Series: Pages, Numbers, and Keynote. A pop-up menu is configured to show all the junior grading levels for a Kempo Ju-Jitsu club. The lead instructor only has to click a student's grade, and a custom pop-menu appears.

Staying with the Ju-Jitsu club, Figure 8 shows a range of interactive data formats in a grading assessment table. Checkboxes are used for the first five assessment criteria, but for the training bouts Star Ratings have been set for qualitative assessment.

Figure 8. Use the interactive data formats to make data input intuitive and more accurate.

You can apply this idea to a wide range of assessment types, and not just in education or sports—perhaps in medical or engineering assessments, etc.

Tip 6: Learn how to change cell references

For users taking their first steps with spreadsheets, setting up calculations is a great achievement. The default reference settings work well in many cases, but changing the cell references unlocks a world of possibilities.

Earlier I mentioned Lesson 10 in the book. The lesson begins with an exercise to construct a multiplication table. For the math to work correctly, the cell references have to be changed from the default settings. In Figure 9, cell F4 calculates 5 × 4. To achieve this result, the references to row 1 and column A must be preserved, as shown in Figure 10. (If you work through Lesson 10, these concepts will be clearer.)

Figure 9. The formula in cell F4 shows that cells F1 and A4 are being referenced. Numbers' default referencing would show cells F3 and E4 in the Formula Editor, so the default referencing wouldn't work for this multiplication table example.

Figure 10. Clicking a reference in the Formula Editor opens a pop-up menu in which you can change the cell referencing.

The inquiring mind will dare to imagine great things from spreadsheets. Understanding the options for referencing cells in formulas will have you moving forward by leaps and bounds.

Tip 7: Save versions to mark waypoints in your work

This tip applies to Keynote and Pages, too. All three applications automatically save your documents as you work. This feature removes the need to remember to save your work periodically, but here's one reason why you still might want to save manually. Imagine a scenario where you've reached a point of perfection; your spreadsheet, essay, or slideshow is dazzlingly brilliant, but you want to try to take things further. Choose File > Save or press Command-S to save that specific version of your document. You can save as many versions as you like (see Figure 11).

Later, if you want, you can use Revert To options to restore that point of perfection. Find the revert options by choosing File > Revert To. A window appears that looks and behaves much like OS X Yosemite's Time Machine.

Figure 11. Restoring a version of a Pages, Numbers, or Keynote document opens a window very much like OS X Yosemite's Time Machine.

Tip 8: Design spreadsheets on OS X to use on iOS devices for data input

The versions of Pages, Numbers, and Keynote on your Mac are heavy hitters amid the other versions of the applications on iOS and iCloud. To make the most of the portable convenience of your iPad or iPhone, and the seamless integration of Numbers across the ecosystem of Apple products, design your spreadsheet on your Mac. Save the spreadsheet to iCloud so that you can test your design on your iPad or iPhone, and then continue refining the spreadsheet on your Mac until the design also displays neatly on your iOS device (see Figure 12).

Of course, you can develop spreadsheets exclusively on your iOS device, but the OS X version has additional features you may find useful.

Tip 9: Use the form options on iOS

Stepping away from the OS X version of Numbers, this tip offers a great benefit for anyone entering data on an iPad or iPhone. As Tip 8 suggests, developing your spreadsheet on a Mac is likely to be the best option, but one feature in Numbers is only found in the iOS version. As you tap the plus (+) icon to create a new sheet in the iOS version of Numbers, you get two options: New Sheet or New Form (see Figure 13).

Figure 13. The Form options in Numbers for iOS are accessed via the plus (+) sign adjacent to the sheets.

Forms are based on existing tables and make data entry a cinch. Figure 14 shows the Junior Register table from Figure 12 in Form view. The image was captured on an iPad Mini, and even with the smaller screen size you can see that plenty of space is available to navigate through the register.

Tip 10: Secure your data by using passwords

Spreadsheets often contain sensitive information, whether that's product pricing, personnel details, or a record of all the shopping you've done. Locking down a spreadsheet is not just prudent—it may be necessary to comply with data protection laws. Applying password protection is easy, and the same process is used for Keynote and Pages: Just choose File > Set Password, and enter your password in the required fields.

TIP

Adding a password hint is a good idea, but for added security, don't let Keychain remember the password. This means you'll always have to enter the password to open the spreadsheet, whether it's on your Mac or an iOS device.

Conclusion

We've reached the end of our brief look at getting the most out of Numbers. Apple Pro Training Series: Pages, Numbers, and Keynote. explores these concepts in greater depth, with exercises based on real-world scenarios to help you consolidate your learning.