Tips for Numbers on iOS
22 Create forms for easy data entry
One of Numbers’ best features is one that hardly anybody knows about: Forms. They present a view to tables in your spreadsheet which are tailor-made for entering data quickly and easily on smallscreen devices. All you need is at least one table on the sheet to have a header row.
Open the sheet on your iPhone or iPad, tap the + symbol at the top-left and then New Form. Now you get a dedicated view for that table – complete with exotic cell types such as checkboxes and star ratings – that makes it easy to enter data. You’re not duplicating the data. Instead the form is just populating data back into the table, and if you’re syncing over iCloud it will be there when you open the document on your Mac.
23 Unfreeze headers
By default, headers are frozen – kept in place when you scroll – which is usually a good idea. If you want to reclaim the space they take up on a small screen, turn freezing off from the Headers tab of the Format menu. On a Mac, this option is located in the Table menu.
24 Fill data
The option to fill data up, down, left or right is also available on iOS, but it’s a bit tucked away. Select a cell or cells, then tap Fill in the black pop-over menu (tap the right-pointing arrow if you don’t see it) to reveal yellow borders. Drag them in the direction you want to fill.
25 Filter your spreadsheet
One really handy feature in Numbers is the ability to filter your tables. In this way, you can quickly focus on bits of data that you need to examine more closely. What’s more, you can set the filters up and then switch them on and off with a checkbox. You’re not actually deleting any data, you’re only hiding it.
For example, if you were self-employed and used a Numbers spreadsheet to track your invoices, you could set up a filter so that you can hide those invoices that have been paid by clicking the Sort & Filter button at the top-right. There are a few ways you could do it, but if you have a column recording the dates your invoices were paid, add a filter for that column and have it match the condition‘ cell is blank’.
26 Temporarily highlight a cell’s column and row
It’s easy for your eye to skip a row or a column on big spreadsheets, so hold while the pointer is over a cell to highlight the cell’s row and column in blue. (If you do this often, consider using Numbers’ Alternating Row Color formatting option, which makes it easier to read across wide spreadsheets.)
27 Reference cells on other sheets
Like most spreadsheet software, Numbers lets you have multiple sheets per document (though it also lets you easily mix tables, charts, pictures and other media on each sheet, which is less common), and it’s easy to refer across these sheets in formulae and data visualisations. You could, for example, have a price list on a second sheet and an order form on the first. If you enter ‘3’ as a quantity in column B on the order sheet then, in the total cost cell of the same row, start typing =B2* and then click on the tab at the top of the document to switch to the price list and click the appropriate cell. Numbers will reference that cell’s value to calculate what three of that product costs. Of course, if the price list is updated, the order form will be too.
If you need to change a cell reference in a formula you’ve already written, double-click the cell that contains the formula. This reveals the formula in a floating bar, which you can move out of the way by dragging on the handle at its left-hand side. Click once on the coloured lozenge that points to the currently referenced cell – if that cell is on a different sheet, the view will switch to it – then click on the cell you want to reference instead.
28 Add headers and footers to printed pages
When you print Numbers documents you get a page number automatically centred at the bottom of each page, but it’s not clear how to remove it or add other information to the header and footer. You might notice that moving the pointer over the top or bottom of the print layout display you get after you press displays a thin grey box divided into three. Click in any one of these areas (including the one with the page number, which you’re able to delete) and a text pane will appear next to the Page Setup options in the sidebar. Here you can change the format of the header and footer text, and choose whether they appear on all sheets or only the sheet you’re typing in.
Under the Insert menu you’ll find options such as Page Number and Page Count – which you could combine by choosing Page Number, typing ‘ of ’ and then choosing Page Count – and Date & Time. To change a date’s format, click on it after you’ve inserted it into one of the header or footer areas.
29 Custom date and time formats
As well as choosing from the extensive list of date and time formats built into Numbers, you can set up custom formats to mix dynamic data placeholders with entered text. For example, we might track deadlines for MacFormat using a column of deadlines for our print edition and another column for the iPad edition, and in a third column note down how many days we have to build the latter after sending the former to the printers. By setting up a custom date format, we can display this as ‘[days] to make the iPad edition’.