Videos by Category: Numbers

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.

If you wish to represent a number in a cell with leading zeros, such as a zip code that starts with a 0 digit, or an International phone number, then you can do it using the Text cell format which treats the number as a series of characters, rather than a value. You can also use custom cell formatting to display a number with leading zeros, which can help you if you wish to have easy data entry and uniform cell formatting.

Since cells in Numbers are formatted in different ways, it is easy to have two cells appear to be the same, such as a date and the text name of a month. When using formulas to compare cells, the functions will often not match up these values because they are fundamentally different. You can use the bottom bar in Numbers to view the actual values of cells to see which value is actually stored in the cell.

Have you every wanted to print only a portion of a web page, such as an airline ticket? Or, have you ever wanted to print or share a small portion of a Pages or Numbers document? You can do this easily by using the print function to send the document as a PDF to Preview. Then in preview you can crop out everything you don't need before printing or sharing it. A few clicks can save a lot of ink.

The SUMIF function is one of the most useful functions in Numbers. You can use it to calculate the total for each category when you have a list of numbers and categories. The best way to use it is to create a second table and use one column in that table as the comparison value.

The Lookup function in Numbers allows you to get a value from one table based on a piece of information. In this example you'll learn how to look up a number from column B based on the text in column A in order to calculate the cost of a project.

You can use one of four functions in numbers to convert monetary values from one currency to another. You can use the price from the previous day, or a historical value. If you need to know the special currency symbol, you can look that up online.

If you create a pie chart in Mac Numbers you will get a slice for each row, even if that row is very small. You can use a series of techniques to create a second table and set all of the smaller values to 0, moving the sum of those values to a new row. Then you can create a pie chart from that second table that shows these small values grouped together.

Many people make the mistake of splitting Numbers data across multiple spreadsheets when it should be kept in one long table. You can use formulas and filters to keep your data in a single table and still easily show only a subset of the data, such as a monthly expense report.

You can merge together adjacent cells to create one cell in a table. This is typically used for formatting, to provide a larger space to center a title over a group of cells. It typically isn't needed in Numbers, since sheets can contain multiple tables. So each group of data can be in its own table. However, to create spreadsheets that are compatible with Excel and other apps, using merged cells can be useful.

When using a computer, you usually don't have a multiplication or division symbol on your keyboard. To do basic mathematical functions, you use the asterisk and slash keys instead. Some apps, like Numbers, will even convert these to multiply and divide symbols. At other times you will still see those characters in your math equations.

The new shapes included with updates to Pages, Numbers and Keynote can be customized by altering the points, lines and curves in the shape. You can also combine multiple shapes into one. You can save your new shape so it appears in all three apps for future use. If you break apart some shapes, you can apply different colors to different parts.

If you have a large table with some duplicate rows in a spreadsheet, you can find them by sorting and using a formula to identify duplicate rows. If you need to merge the data in duplicate rows, you can use a formula for that too. You can then sort and delete those rows easily, saving hours of manual work.

A new feature in Pages, Numbers and Keynote is the Preferences screen for Auto-Correct settings. You can customize things like automatic smart quotes and spell correction on a per-app basis. You can also add to a list of automatic text replacements and ignored spelling words for each app.

The new versions of Pages, Numbers and Keynote introduce hundreds of new shapes that you can use in your projects. You can change the color of the shapes, resize them and even edit their lines and break them apart. They could be useful in a variety of documents, presentations and spreadsheets. This feature is also in the iOS version of Pages, Numbers and Keynote.