MacMost: Numbers

The IF function is how to test values in spreadsheets. You can use it to simply test a value and show different results. But the key to making complex spreadsheets to learning more about the IF function. You can pass through values when a condition is met, combine conditions with AND and OR functions, and nest IF functions for more than two possible results.

The INDIRECT function allows you to look up the value of a cell based on a cell reference. So you can take stings like C2 and find the value of the cell C2. This allows you to construct cells references from other values to create powerful lookups that refer to cells in other tables or even sheets.

With standard Mac apps like Pages, Numbers and Keynote, you can revert to a previously saved version of the file to retrieve text or items you may have deleted or changed. You can completely revert to the old version, or copy and paste some text from an old version. This also works with TextEdit and some third-party apps.

If you are designing a Numbers spreadsheet to give to users that allows them to enter values then you may want to prevent them from entering values outside of a range. You can use conditional highlighting to change the color or style of a cell if the user enters a bad value. You can use a formula in another cell to indicate a bad value, plus instructions. You can also use sliders, steppers and pop-up menu cell formats to limit values.

The new version of Numbers makes things easier for those that use filters. You can also link text to other sheets in the same document and there is better performance and calculation precision. Numbers also gets the same text fill improvements as Pages and Keynote, as well as inline images and face recognition for image cropping.

It is fairly easy to use a SUMIF function to find a total for rows that meet a condition. But there is no easy way to get the total for rows inside a range of dates. By using extra hidden columns, you can easily mark rows depending on whether they are in a date range, and then use SUMIF to get the total. Getting the maximum value in that range is a little trickier, but can be done with an additional column and the MAX function.

When you filter rows in Numbers, any totals or other footer calculations will still be based on all of the rows, not just the ones visible. You can change that by using functions like SUMIF that match the filter. But then you need to adjust your formulas and filters every time so they are the same. However, you can use a hidden column and a single-cell separate table to make this quick, easy and useful.

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 group 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.