Sometimes you have non-consecutive data in Numbers that you wish to chart. For instance, you may have skipped dates or large gaps in time. However, you want to graph the data with a consistent timeline at the bottom of the graph even if all of the dates are not represented. Here is one method for doing that, using a second hidden table that is automatically populated from the main table.
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.
You can use a trick to force a line return in Numbers formulas so they are easier to read and edit. The same trick can be used in some web forms and other apps to add line returns when a regular Return keystroke doesn't work.
If you need a quick random number there are at least three ways to do this on your Mac. You can do it in the Calculator app, but you'll need to perform some extra steps to get a useful value. You can use the RANDBETWEEN function in a temporary Numbers spreadsheet. You can also just ask Siri.
You can use CONVERT to convert numbers from one unit to another. Convert distances from meters to feet, temperatures from Fahrenheit to Celsius, and so on. You can also use cell formatting to attach unit symbols to numbers.
When you open a Pages, Numbers or Keynote document that is missing fonts, you can use a handy new feature to globally replace that font throughout the document. You can also use this to quickly change any font in your document.
A new feature in Numbers is the ability to add leader lines to pie charts. This makes the charts look more professional and easier to follow. This also works with pie charts in Keynote and Pages.
A new feature in Numbers allows you to use live stock prices and other information in your spreadsheets. You can get the current price, volume, change and other data. You can also grab historical data.
See how you can use Numbers to work with basic physics problems like orbital calculations. In this example a table is use to calculate the position of a satellite over time. A scatter chart is then used to show the position of the satellite and a line chart to show the altitude.
The Lookup function in Mac Numbers can be used to find a value based on another value. You can also use numerical and time ranges by simply stating the start of each range. In this example, we look at a rental rate sheet and calendar where the price for each date is populated from the the rates in another table.
A key skill every Mac user should master is how to select multiple items. In icon-based situations like the Finder or Keynote, you can drag a rectangle around items. In icon and list-based situations you can select multiple items using the Command and Shift keys.
Learn how to create a Numbers spreadsheet with a checklist and pie chart that tracks your progress through the checklist. To do this, you'll need the functions COUNTIF and COUNTA.
You can create new shapes by using a set of four commands in Pages, Numbers and Keynote. These commands will combine one or more shapes to make a new shape. This is often easier than drawing your own shape from scratch.
You can sort rows in Numbers using a trick with the rand function. You can also sort or shuffle a single column by copying that column to another table, performing the sort, and then copying it back to the original table.
You can count the number of times a text string appears in a Numbers spreadsheet column using the COUNTIF function. The function can be hard-coded to look for text, or it can use the value of another cell.
Conditional Highlighting in Numbers lets you color a cell based on the contents of that cell. If you need to set a cell to highlight based on the contents of other cells, you can use the IF function first, and then the conditional highlighting based on the results of that IF function.
Trend lines can help you find meaning in what could seem like random data. One example looks at daily sales for a store and finds an upward trend over the course of a year. Another example looks at physics data and converts it to a mathematical formula.
If you are building a spreadsheet in Numbers it can be useful to have sample data to help construct your tables and get things right before you have real data to enter. You can use the RANDBETWEEN function to create random numbers. Then if you copy and paste correctly you can turn those formulas into static numbers that won't change.
With new updates to Pages, Number and Keynote you have new ways to select colors in those apps. This includes an eyedropper tool on the iPad that allows you to select a color from anywhere on the screen and use that to color text, shapes and other objects. This eyedropper tool also exists on the iPhone, but in a different location.
Learn how to sort tables in Numbers. You can sort by a single column, but also by multiple columns. See some techniques for making the tables look better once they are sorted.