MacMost: Numbers

If you have a list of names in Numbers it is very difficult to sort by last name unless you have the last name in a separate column. However, you can use a series of formulas to extract the last name and first name, format them properly as Last, First, and then sort them. It will take some extra effort to deal with middle names and initials as well. You can even get rare last names that include spaces to sort properly.

One advantage that Numbers for iPad has over Numbers for Mac is Form mode. If you want to simulate a form in Mac Numbers, you can do it using a few lines of JavaScript code in Script Editor and even make those script available in the menu bar. This could be the starting point for a more complex set of scripts that check values and even allow you to edit row data in a form too.

Creatign pie charts from a set of numbers is easy. But if you have to count occurances in a long list of data and create a pie chart from the results, it can take a second table. However, if you use Categories, you can create it in fewer steps without the need of a second table or any forumlas.

Conditional highlighting in Numbers is simply a way to change the style of a cell based on the contents of that same cell. However, by using a trick you can have conditional highlighting affect other cells or an entire row.

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.