Numbers
Make your spreadsheets work harder and look better
12 Password-protect your documents
You can do this for all iWork documents, but since spreadsheets can contain especially sensitive data, it’s most appropriate here. To set a password, pick that option from the File menu. You can add the password to your keychain so you don’t have to enter it when you open the file on your Mac, but be wary of this option. Unless you have other security measures in place, such as the option turned on in System Preferences to require your password to wake from screen saver, and a screen saver that activates after only a short delay, this could leave the file vulnerable.
13 Special types of cells
Numbers supports some special cells that make data entry really easy, including clickable checkboxes, star ratings and pop-up menus so you can pick from a range of predetermined values. Explore them at the foot of the Data Format drop-down menu under the Cell pane of the Format sidebar.
If you want to have a formula act on the data in these special cells, look at the window’s bottom-left corner and you’ll see what the actual, behind-the-scenes value that’s recorded in that cell is – such as FALSE for an unchecked checkbox and TRUE for a checked one – so you know what values to enter in your formula.
If you’re sharing your spreadsheet with the wider world and have to convert it to Excel, you have the option of adding a password to the exported file – but remember that this password only applies to this exported version, not to your original Numbers document.
14 Copy and paste styles
This one also works in all three apps. The ‘proper’ way to take a set of attributes from one object to another is to define it as a style – something you still have to do if you want to copy table styles – so you can easily reuse it or tweak it globally later, but if you know you’re not going to reuse the style extensively, or you just want to do so quickly, you can just copy and paste it. Select some text, a picture or a shape and then choose Format > Copy Style. Then select the object – or objects – you want to apply that style to, and choose Format > Paste Style.
15 Copy and paste table styles
If you tweak the formatting of a table on one sheet, you can then easily make all the others in your document match it by defining it as a style. Set up the table as you want it, select it, and then click the rightwards-pointing arrow in the Table Styles pane of the Format sidebar. Click the + in the top-left slot. Once the style is defined, you can drag it to the first screen so it’s easier to access, and select other tables and apply it to them.
16 Copy from Excel!
If you’re trying to figure out how to use advanced formulae and the built-in help isn’t helping one bit, you would probably turn to Google for assistance. But Numbers is both little used compared to Excel (so there’s less help around) and its name makes it hard to search for. The good news is many formulae have the same names and work largely the same way, so it’s still worth reading tips designed for Excel. We can’t guarantee they’ll work, but in our experience it’s a handy first step. Also check out discussions.apple.com/community/iwork/numbers.
17 Concatenate cells
The ampersand symbol (&) has a special function on a spreadsheet, and that’s to bring together values from other cells into one. Note that it’s not adding them mathematically – so if you have the value 3 in one cell and 4 in another, using ‘&’ would produce the result 34 – but it’s handy for grouping stuff. Let’s say you have a list of surnames in column A, forenames in B, and titles in C. In column D you could use the formula =C2&B2&A2 to produce, for example, Mr Christopher Phin. Actually, that would give you MrChristopherPhin, so the formula needs to be =C2&" "&B2&" "&A2 to concatenate in some spaces. After you’ve filled the formula once though – in row 2, say – you don’t have to laboriously enter it for every other row, as you’ll discover in the next step.
18 Quickly extend values and formulae
You can quickly copy the values or formulae of cells to other cells, and Numbers is smart about how it does it. The quick way to do it is to select a cell then move your mouse to the centre of any of its four edges so that a yellow handle appears.
Like in the example above, select the cell that contains your formula and then drag the yellow handle on the bottom edge down to fill the formula into the cells below it. Numbers is smart enough to infer that the references to row 2 should be replaced with references to row 3 in the next cell down, and so on.
It’s also a quick way to populate a sheet with repeating numbers. Type 1, 2, 3 into the first few cells, then drag the yellow handle down. Numbers will continue with 4, 5, 6 and so on.
This works for other data types too. Say you’re planning a rota, and each of the rows in the first column is the starting date of a week. Enter a couple of dates and then fill down. Numbers will realise that the dates are seven days apart and increment by that amount as it fills each of the cells.
19 Enter carriage returns within cells
Pressing Return usually advances you to the next cell down in Numbers, but if you hold as you press it, you’ll get a carriage return so you can enter text on a new line within the same cell.
20 Conditional formatting
You can have cells change their appearance – highlighting the cell with a colour, changing the appearance of the text it contains, and so on – based on whether some criteria are met. For example, if you’ve set a monthly budget but you’ve gone over it, you might change the number’s colour to red.
Select the cells you want to act on, and then click the Format button to reveal the sidebar. Click the sidebar’s Cell tab and then click the Conditional Highlighting… button at the bottom to set up formatting rules for the selected cells. There are a load of useful preset styles, but scroll to the bottom of the list and choose Custom Style if you want to set up
your own.
21 Lock objects
If you lock any element on a Numbers page – or in any other iWork app, come to that – it means it can’t be moved or deleted, making it perfect for standing information such as a logo and a text box detailing terms and conditions on invoice sheets. Just select the object then
press or choose Lock in the Arrange menu. Now, even if someone selects it, either by clicking on it or by pressing and
then hits Delete, it will stay on the page. If you need to adjust the element, select it and choose Arrange > Unlock or press