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 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.
The latest version of Numbers restores our ability to use AppleScript to automate some actions in your spreadsheets. You can use AppleScript to create new commands in Numbers and do things that could be difficult or impossible to do otherwise. Take a look at some simple examples that populate cells with random numbers and modify the values of checkboxes.
When you create formulas in Numbers, the references to other cells are relative to the location of the formula. So when you move or copy the formula into another cell, the references follow along. But you can use absolute cell references to force the formula to always refer to the exact same cell, no matter where the formula is placed.
Filters let you view portions of data in your tables based on criteria. You can filter a long table by what is found in a column. You can combine multiple filters to drill down into data.
You can use special cell types like checkboxes, ratings, sliders and pop-up menus to represent values in cells in Numbers. These cells can make it easier to enter data, or they can visually represent the data in a better way. You can use the values of these cells just like regular numbers and other data types.
Numbers can automatically fill in cells for you if you want to repeat a value, or create a sequence. You can use Autofill to add a set of sequential numbers or dates to cells. You can also have the cells follow a pattern. If you are using tables properly, formulas included in cells will repeat as you add new rows to the table.
Learn how to use the IF function to perform calculations in Numbers. IF is an important function to learn if you want to graduate to creating more advanced spreadsheets. You can use it to test a value in one cell and return two different values depending on the results of that test. Also learn related functions like SUMIF and COUNTIF.
Numbers lets you create charts that have an extra dimension. You can move through columns of data using a slider or buttons, with the chart changing to reflect the data in each column. This can be useful for showing changes over time.
Sometimes you want to be able to count the number of items in a table and get the total. You can do this with the COUNTIF function. It will return the numbers of items that match a certain criteria. You can also use the COUNTIFS function to match multiple criteria in a more complex table.
You can use data in your spreadsheet tables to create colorful charts of many different types. Learn how to quickly create a chart and modify its look. You can also put charts on separate sheets to make them easier to share and print.
There are many advantages to using multiple tables in a single spreadsheet in Numbers. Take a look at how you can use two tables with the LOOKUP function to make computations easier. The LOOKUP function will grab data from another table by looking up a value in one column and returning the value of another.
Learn how to use simple formulas in Numbers to calculate results. You can use basic math operations or complex functions from a library. You can feed these functions single cells or ranges of cells. You can also copy and paste formulas to perform the same calculations on other rows or columns.
Learn how to use conditional highlighting to make cells in your tables stand out. Your can search for a number of different criteria and then change the color or styling of cells depending on the match. You can even have multiple conditions and give them priority. You can also use other cells and formulas as input for the conditions.
When you create a basic table in Numbers, it is important to use Headers and Footers properly. Headers allow you to define the contents of rows and columns, and you will see these labels appear in formulas and charts. Footer rows allow you to perform functions like SUM and AVERAGE on an entire column, with the formula adjusting to the content in between the header and the footer.
Calculating percentages in Numbers is just a matter of dividing two numbers. But you can use cell formatting to display the result as a percentage instead of a decimal fraction. You can also use functions to calculate percentages from lists of numbers or lists of items.
You can use AppleScript to create scripts that control Numbers and Pages. This is similar to how Microsoft Office users use macros to control Word and Excel. In either case, you need some level of programming skill to be able to write scripts. If you can do it, this can be a very powerful way to enhance Numbers and Pages, adding functionality that isn't possible otherwise.
Creating useful spreadsheets is usually a matter of combining several techniques. In this tutorial, you'll learn how to create a table to hold a small database of information. Then, you'll create a second table to count the number of records that have certain criteria. Finally, you'll use the second table to create a pie chart. Each element updates automatically as new records are added or changed.
You can create a chart in Numbers and then copy and paste it into a Pages or Keynote document. When you do so, the chart is linked and changes to the chart data in Numbers can be synced to your Pages or Keynote document. However, only data can be updated. Labels and the table itself cannot sync between apps, though copy and paste is a decent work-around.