MacMost: Numbers

When building Numbers spreadsheets it is important to remember that a row in a table is the equivalent to a record in a database. To build a billing system, you can put clients in one table and invoice items such as billable hours in another table. Then you can build a sheet with tables that use functions to populate an invoice. The requires some advanced functions like LOOKUP, INDEX, IF, AND, COUNTIF and more. But once you are done, you can easily add more records and create invoices.

If you need to carry over the value of a cell from on table to another in Numbers, you can do that simply by including a formula that just references that other cell. There is no need to figure out the exact way to reference the original cell, as you can just click to grab the cell reference. The same technique can be used to reference a cell from a different sheet.

Formulas can get very large and complex. One way to simplify them is to use columns to perform parts of the calculation, and then hide those columns when you have your system working. In this example, we'll use columns to extract the year and month from a date, and then use that in a SUMIF formula that would be very complex otherwise.

Pages, Numbers and Keynote for Mac share a common code base and can support many of the same elements, like text boxes, shapes, images, tables and more. You can copy and paste elements from one into the other. This can be handy when you wish to include tables or charts from Numbers in a presentation of word processing document. Or, when you want to take a slide from Keynote and use it in a spreadsheet or Pages document.

Version 5.2 of Numbers brings a new/old feature, Smart Categories. This is similar to the old Categories feature in Numbers prior to 2013, and similar to Pivot Tables in Excel. Using Smart Categories is relatively simple, however, as you simply groups together similar rows and allows you to see totals, averages, counts and other data pertaining to those groups.

Many people share Numbers spreadsheets with others by exporting to Excel. This creates a cumbersome process where the spreadsheet is exported and imported multiple times and rules out using some of the best features of Numbers. But you can use the Collaborate feature to share a document with another users regardless of whether they are on Mac or Windows and what software they have installed or whether they have an iCloud account. You can password-protect the document and even collaborate in real time.

While it isn't possible to create dynamic Pop-Up Menus in Numbers alone, you can use an AppleScript service to pull data from one table, present it in a list that will then populate the value of a cell. You can set this service up with a keyboard shortcut to make data entry easier. The list will automatically update as you update the second table.

A point of frustration with Numbers users is that Pop-Up Menu cells must be populated with a list of typed values. Often it is useful to get these values from another table instead. While there is no easy solution, you can simulate a dynamic pop-up with a special technique that involves creating the Pop-Up Menu in the second table and pasting it into the first. This method requires a manual update of the Pop-Up Menus when the selections change, but can handle large lists.

If you have a large Numbers table and regularly search and filter it, you may want to use a formula and a filter to enable quick and easy searches instead. The technique involves creating a hidden column that will use a single-cell second table to search, and then a filter based on the results in that column.

You can easily save a portion of a webpage or document as a streamlined PDF or Image file. However, many people take screenshots instead, which are poor resolution. Using the Open In Preview function in the Print Dialog, you can quickly and easily convert portions of webpages or documents to smooth and scalable PDFs or even higher resolution images.

A new feature in Numbers is the ability to round the corners in column and bar charts. You can use this to soften the corners or to completely round the ends of the bars. This also works in the latest versions of Pages and Keynote.

You can use the wildcard characters question mark and asterisk in some Numbers functions to get more powerful results. In this example, we'll look at using a SUMIF function that sums rows that match only a portion of a string, rather than an exact match.

While there is no pivot table function in Numbers, you can simulate the most common uses for pivot tables using formulas, filters and hiding columns. In this example we'll look at a table of sales numbers and see how you can sum the number of sales for each product listed. Initially you end up with the first row containing each product showing the total for every sale for that product. But you can filter the list to only show those rows. Then you can hide columns that present excess data to end up with similar results as you would with a pivot table.

In Numbers for iOS there is a special feature that allows you to enter data into form, rather than directly into a table. This one-record-per-screen data entry can make it much easier to enter information on the iPhone or iPad's small screen. The data in forms and the spreadsheet table is one and the same.

You can use the MATCH function to get the location of a value in a row or column. You can also use the INDEX function to grab the value of a cell at a given location. This allows you to make 2-dimensional lookup tables to find values based on two variables. In this example, one table shows prices based on a store name and a product name. The other table will look up a price from the first table given the store and product.

If you need to encrypt your documents for security or legal reasons, you can do this easily in Pages, Numbers and Keynotes with the Set Password option. It is important to remember your password or you will lose access to the document. You can also just opt to use File Vault to encrypt all of the data on your Mac.

A new feature in Keynote, Pages, and Numbers, is the ability to reduce the size of your file by compressing images, video and trimming the unused parts of videos. These reductions can significantly reduce the file size of image and video-heavy files. You can also save a draft copy that will be even smaller if you need it.

A new feature of Numbers, Pages and Keynote is the ability to use Donut charts instead of Pie charts to represent a single row of data. Donut charts are popular right now, and some say they are better ways to visualize data than Pie charts.

The new version of Numbers for Mac allows you to make adjustments after importing a table of data from a text file. You can import comma-separated, tab-separated or spaced text data, or use custom separators as well. This feature allows you to remove excess header rows, move spacing, deal with quotes and other options.

The new versions of Pages, Numbers and Keynote include the ability to draw freehand with the Apple Pencil, or your finger. You can use a variety of drawing tools when in this mode. The result is an image that can be arranged just like any shape or imported photo.