Numbers for Mac: 15 productivity-boosting tips

6. Enter carriage returns within cells

Pressing Return usually advances you to the next cell down in Numbers, but if you hold Option as you press it, you'll get a carriage return so you can enter text on a new line within the same cell.

7. 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 – 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.

8. 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 this. The quick way to do it is to select a cell then move your mouse to the center 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 – if you enter a few dates that are a week apart and extend the box, it will keep adding dates that a week on from the last.

Numbers for Mac tips

You don't need to type every value in a sequence because Numbers can work out the pattern and fill it over a range of cells for you.

9. Conditional formatting

You can have cells change their appearance – highlighting the cell with a color, changing the appearance of text, 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 that number's color to red. Select the cells you want to act on, and click the Format button to reveal the sidebar. Click the sidebar's Cell tab and then click Conditional Highlighting… at the bottom to set up formatting rules for the selected cells. As always, there are a load of useful preset styles, but scroll to the bottom of the list and choose Custom Style to set up your own.

10. Lock objects

If you lock any element on a Numbers page (or in any other iWork app) 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 Command + L, or choose Lock in the Arrange menu. Now, even if someone selects it, either by clicking on it or by pressing Command + A, 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 Option + Command + L.