Work quickly, confidently, and effectively in Apple’s Numbers app!
Take Control of Numbers
Work quickly, confidently, and effectively in Numbers 6 with Sharon Zardetto’s detailed instructions. Input, calculate, sort, filter, format, and chart your data with ease as you learn not just the basics but also special tricks and power-user features.
All Take Control books are delivered in three ebook formats—PDF, EPUB, and Mobipocket (Kindle)—and can be read on nearly any device.
There are many benefits to using Apple’s Numbers app, chief among them that it is now free for all Mac users! If you’ve ever wanted to add to your knowledge of Numbers—whether you’re new to spreadsheet programs in general, new to Numbers, or simply new to this latest version of Numbers—this book gives you detailed information about how to get the most out of this powerful app.
Following the advice of author Sharon Zardetto, you’ll learn how to input, calculate, sort, filter, format, and chart your data with ease. Taking you from the basics, all the way through complex formulas, charting, and other power-user features, this book will expand your understanding of what Numbers can do.
This book will show you how to:
Get started with Numbers: Learn about Numbers’ terminology and interface, sheets and templates, table basics, and cell basics. For those new to spreadsheets, learn about the anatomy of a table and data entry basics. For those new to Numbers, but comfortable with spreadsheets, learn about working with sheets and tabs, the template chooser, and pop-up and contextual table menus.
Work more efficiently: Customize your environment, utilize built-in and custom templates, speed up your work with autocomplete, autofill, and text substitution, make custom templates, use table styles, paragraph styles, and define a default text box.
Use formulas and functions: Find out about formula-building basics, cell references, and functions and arguments. Explore the formula editor and the Function Browser, and use quick calculation tokens.
Polish your formatting: Explore your formatting options, standardize the look of components in tables and sheets with styles, use rulers and ruler guides, add graphical elements (shapes and text boxes), and use color controls for everything from text to cell borders to shapes.
Visualize data with charts: Learn the basics of chart parts and terminology, how to choose the right chart, how to create a chart, and how to work with 3D charts and interactive charts.
Work with other people: Share, collaborate, and add comments to your work with other Numbers users. Find out how to import, export, and share files when others are not using Numbers.
Sharon Zardetto has been writing about the Macintosh professionally since 1984, including nearly a thousand articles in Macintosh magazines and over 20 books. She’s best known for writing several editions of The Macintosh Bible, along with The Mac Almanac.
Sometimes it’s difficult to keep up with Apple’s app updates. Within days of the original release of the second edition of this book, for instance, Numbers jumped from version 4.3 to 5.0, which we were able to accommodate with a swift update (to version 2.1) of this book. Since then, there have been several minor Numbers updates for “stability and performance improvements,” as well as tweaks to existing features and/or the addition of minor but convenient features. Numbers 5.3 presented a major new feature—Categories (known in the Excel world as Pivot Tables)—along with some other tweaks and additions. We were in the final editing phase of our update about that when Apple polished up a few more things and released Numbers 6.0. In all, a wonderful, if frustrating, example of coevolution. As far as I’m concerned, the jump to 6.0 was deserved for the addition of Categories, and not for what’s been tweaked since, but it’s now at the version number it deserves.
The major additions to this book since the last version are:
The Organize inspector: This provides access to the pre-existing Sort and Filter tabs, as well as the new Categories capability. I talk about the inspectors in general in "The Sidebar and Inspectors."
Categories: Numbers’ answer to Excel’s pivot tables is covered in "Analyze Data with Categories."
Import and export improvements: The vastly improved importing of CSV and other delimited text files is described in "Import and Export Delimited Files." Major improvements for importing and exporting Excel files are described in "Exchange Files with Excel," and "Handle Fixed-Width Files" is new.
Voice recordings: Create voice recordings on a sheet for your own reminders, or for comments or instructions to someone else.
Other additions and changes for this version include:
Chart-tweaking: There’s always more to formatting charts, so you can learn how to: use and format leader lines (that’s in a list in "The Pie and Donut Chart Tabs"); tweak your data presentation by adding reference lines (my favorite new small feature); adjust value-label angles; and round the edges of bars and columns.
Data Redistribution: The procedure I previously described for redistributing data in unmerged cells no longer works, so I’ve provided a new method, described in "Unmerged Data Redistribution."
Shapes: Have you ever wondered why those million or so shapes in the Shape menu are frequently, but not always, blue, and wished you could set a starting-out color for different sheets? Read "The Color of Shapes." And as if 1000+ built-in shapes weren’t enough, you can save a custom shape to the shape menu.
What versions and platforms does this book cover?
This book is about Numbers 6 on a Mac running Mojave (macOS 10.14.x). It does not specifically covers Numbers in iCloud or on iOS devices. Almost all the information in the book, however, is directly applicable to Numbers for iCloud, and the fundamentals (including how to construct formulas, which charts to use for what kind of data, and filtering and sorting data) apply to the iOS version as well.
Longtime Excel users have another reason to need it: some multi-worksheet files are so complexly intertwined that the automatic recalculation of connected formulas every time you enter new data can slow things down. So, they turn off the automatic calculation feature and trigger it when they want the sheet refreshed.
But, back to Numbers. It’s unlikely your spreadsheets will be so complex that recalculation will slow things down; and, in any case, there’s no way to turn off the automatic recalculation that occurs when you enter data. But you can’t specifically trigger a recalc of cells, so those containing random numbers (from the RAND or RANDBETWEEN function) are quite static. Whether you’ve set up a bunch of random-number cells to test what will happen when real data is entered, or intend to keep the random-number formula in place to interact with data you input later, it’s important to test the results of various possibilities in your random range.
Why? What would happen if, coincidentally, all the randomly generated numbers are even, or there’s nothing under 10 or over 99, or perhaps there are no multiples of 5, and you’ve set up something where an odd number, a low or high number, or a multiple of 5 will result in flawed output? (Even if “flawed output” is nothing more than a column that’s not wide enough to display three digits.) You won’t realize there’s a problem because the random numbers are static until something else you enter forces a recalculation.
The solution I proposed in the book was to go to any cell adjacent a block of cells that include RAND or RANDBETWEEN cells and use Command-X to force Numbers to recalculate the entire table, resulting in newly generated random numbers. If there was nothing in the cell, nothing gets cut, and if there was, you can paste it right back.
Well, as of Numbers 3.6.1—and perhaps before, because this was only recently brought to my attention—this no longer works. Command-V in any cell works, but that runs the risk of accidentally pasting something that could overwrite nearby cells and really make a mess of your table. So, let me recommend the trick that everybody in the know has used all along for recalculating Numbers spreadsheets; I had ignored it because it requires using a cell specifically for the recalc trigger.
Simply format any cell to hold a checkbox: select the cell and go to the Format Inspector’s Cell tab, and then choose Checkbox from the Data Format menu. When you check or uncheck the checkbox, every formula in the document—in every cell of every table on every sheet—will be recalculated.
The fact that the recalc is global in the document, really takes care of my objection to setting aside a cell in one of your tables for this purpose. You could, of course, stick it in the usually unused cell A1 where it’s easy to get at, and you could easily just delete it and set it up again if you don’t need it in the interim. But since the recalc works globally, you don’t have to keep the checkbox in any of your working tables. Make a table specifically for the checkbox—it can even be alone on its own tab. And, you can delete all but its first row and first column, making it a single-cell table (yes, an amoeba table!) and duplicate it on every tab for convenience.
Sharon Zardetto and Chuck Joiner discuss Sharon’s exhaustive, but far-from-exhausting, book about Apple’s Numbers. Don’t think spreadsheets can be fun? Sharon thinks different. Find out why on this episode of MacVoices.
Although this ebook covers many features, it doesn’t discuss scripting with Apple’s Automator and AppleScript tools to automate repetitive tasks that would be better carried out by a script than by you. To learn more about what’s possible and get started, check out the iWork & Automation portion of the Mac OS X Automation Web site.
If you’ve never thought about creating automation on a computer before, Automator or AppleScript may be a little much to jump into, though there are helpful introductory materials on the site. For even more help, check out Take Control of Automating Your Mac, by Joe Kissell, which introduces you to the automation mindset, helps you understand a variety of common automation techniques, and helps you take those first few baby steps toward Automator or AppleScript proficiency.
Apple has posted a support document, Size limits of iWork for iCloud beta documents. Among other interesting facts provided by the document are these: the iWork for iCloud beta can share a document with as many as 100 users at a time, an iWork document can be as large as 1 GB (though a few such documents can use up your iCloud storage allocation quickly), and each image in a document (JPEG, PNG, or GIF) can be as large as 10 MB (except in IE 9 on Windows, where the limit is 5 MB). Also, in Numbers you can create as many sheets as you like, and change as many as 100,000 table cells at one time. Furthermore, tables can have as many as 255 columns, 65,536 rows, and 130,000 data-containing cells.