Take Control of iWork!
- PDF EPUB Mobi
- Apr 15, 2015
Apple’s Numbers has become a grown-up spreadsheet (and it’s free on every Mac sold since October 2013!), but many of us still haven’t learned even the basics, much less been able to take advantage of its surprisingly deep collection of features.
With the advice in this 330-page ebook, you’ll learn to input, calculate, sort, filter, format, and chart your data with ease, as author Sharon Zardetto guides you through the basics all the way to power-user features like conditional highlighting, custom data formats, and star ratings. Richly illustrated with hundreds of annotated screenshots, the ebook also includes a hands-on example spreadsheet you can use to try what you’re learning, plus a special tutorial chapter that helps you put it all together. Of course, you can also treat the book as a reference, using the Quick Start to focus on a topic of interest.
The book covers Numbers 3.5, which works on any Mac running OS X 10.10 Yosemite.
- More Info
You’ll learn how to…
Handle the basics:
- Input data into a table.
- Choose the best chart type for your data.
- Add, modify, and position shapes, such as arrows, comments, and text boxes.
- Import/export from Excel, text, CSV, and older versions of Numbers.
- Set up page numbers and other header/footer information for printing.
See what you want to see:
- Know what to expect when you sort by column.
- Hide and show columns.
- Merge and unmerge cells.
- Create rules that filter data to display only specific rows.
- Set up and view interactive charts.
- Work fluidly in the formula editor to create formulas.
- Make formulas from the tear-off tokens on the Quick Calc bar.
- Nimbly use various types of cell references in formulas.
- Understand how to use IF, TRUE, and FALSE in formulas.
- Take charge of logical operators (AND, OR, NOT) in formulas.
- Concatenate and break apart text.
- Strip extra spaces out of data.
- Find medians, modes, ranks, percentiles, and more.
Create clever formulas that can:
- Calculate a sales discount based on whether a total is over or under a certain amount.
- Sort seemingly unsortable data, such as items described as Poor, Good, or Excellent.
- Take a column of cells, with each cell containing a full name, and move all the last names into a different column.
- Add and remove grid lines and cell borders.
- Work with a cell’s background fill.
- Format a chart so it looks fabulous.
- Set up conditional highlighting that appears only when data meets certain criteria.
- Ensure accurate data entry with cell formats like sliders, menus, and checkboxes.
- Use and create templates.
- Format faster with character, paragraph, list, shape, and table styles.
- Update Plans
April 20, 2015 – At the moment, we have no special plan for updating this title.
Posted by Tonya Engst
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.
Posted by Sharon Zardetto (Permalink)
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.
Posted by Michael E. Cohen (Permalink)
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.
Posted by Michael E. Cohen (Permalink)
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.
Posted by Tonya Engst (Permalink)