Make your spread­sheets work harder and look bet­ter

Mac Format - - IWORK TIPS -

12 Pass­word-pro­tect your doc­u­ments

You can do this for all iWork doc­u­ments, but since spread­sheets can con­tain es­pe­cially sen­si­tive data, it’s most ap­pro­pri­ate here. To set a pass­word, pick that op­tion from the File menu. You can add the pass­word to your key­chain so you don’t have to en­ter it when you open the file on your Mac, but be wary of this op­tion. Un­less you have other se­cu­rity mea­sures in place, such as the op­tion turned on in Sys­tem Pref­er­ences to re­quire your pass­word to wake from screen saver, and a screen saver that ac­ti­vates af­ter only a short de­lay, this could leave the file vul­ner­a­ble.

13 Spe­cial types of cells

Num­bers sup­ports some spe­cial cells that make data en­try re­ally easy, in­clud­ing click­able check­boxes, star rat­ings and pop-up menus so you can pick from a range of pre­de­ter­mined val­ues. Ex­plore them at the foot of the Data For­mat drop-down menu un­der the Cell pane of the For­mat side­bar.

If you want to have a for­mula act on the data in th­ese spe­cial cells, look at the win­dow’s bot­tom-left cor­ner and you’ll see what the ac­tual, be­hind-the-scenes value that’s recorded in that cell is – such as FALSE for an unchecked check­box and TRUE for a checked one – so you know what val­ues to en­ter in your for­mula.

If you’re shar­ing your spread­sheet with the wider world and have to con­vert it to Excel, you have the op­tion of adding a pass­word to the ex­ported file – but re­mem­ber that this pass­word only ap­plies to this ex­ported ver­sion, not to your orig­i­nal Num­bers doc­u­ment.

14 Copy and paste styles

This one also works in all three apps. The ‘proper’ way to take a set of at­tributes from one ob­ject to an­other is to de­fine it as a style – some­thing you still have to do if you want to copy ta­ble styles – so you can eas­ily re­use it or tweak it glob­ally later, but if you know you’re not go­ing to re­use the style ex­ten­sively, or you just want to do so quickly, you can just copy and paste it. Se­lect some text, a pic­ture or a shape and then choose For­mat > Copy Style. Then se­lect the ob­ject – or ob­jects – you want to ap­ply that style to, and choose For­mat > Paste Style.

15 Copy and paste ta­ble styles

If you tweak the for­mat­ting of a ta­ble on one sheet, you can then eas­ily make all the oth­ers in your doc­u­ment match it by defin­ing it as a style. Set up the ta­ble as you want it, se­lect it, and then click the right­wards-point­ing ar­row in the Ta­ble Styles pane of the For­mat side­bar. Click the + in the top-left slot. Once the style is de­fined, you can drag it to the first screen so it’s eas­ier to ac­cess, and se­lect other ta­bles and ap­ply it to them.

16 Copy from Excel!

If you’re try­ing to fig­ure out how to use ad­vanced for­mu­lae and the built-in help isn’t help­ing one bit, you would prob­a­bly turn to Google for as­sis­tance. But Num­bers is both lit­tle used com­pared to Excel (so there’s less help around) and its name makes it hard to search for. The good news is many for­mu­lae have the same names and work largely the same way, so it’s still worth read­ing tips de­signed for Excel. We can’t guar­an­tee they’ll work, but in our ex­pe­ri­ence it’s a handy first step. Also check out dis­cus­sions.ap­­mu­nity/iwork/num­bers.

17 Con­cate­nate cells

The am­per­sand sym­bol (&) has a spe­cial func­tion on a spread­sheet, and that’s to bring to­gether val­ues from other cells into one. Note that it’s not adding them math­e­mat­i­cally – so if you have the value 3 in one cell and 4 in an­other, us­ing ‘&’ would pro­duce the re­sult 34 – but it’s handy for group­ing stuff. Let’s say you have a list of sur­names in col­umn A, fore­names in B, and ti­tles in C. In col­umn D you could use the for­mula =C2&B2&A2 to pro­duce, for ex­am­ple, Mr Christo­pher Phin. Ac­tu­ally, that would give you MrChristo­pherPhin, so the for­mula needs to be =C2&" "&B2&" "&A2 to con­cate­nate in some spa­ces. Af­ter you’ve filled the for­mula once though – in row 2, say – you don’t have to la­bo­ri­ously en­ter it for ev­ery other row, as you’ll dis­cover in the next step.

18 Quickly ex­tend val­ues and for­mu­lae

You can quickly copy the val­ues or for­mu­lae of cells to other cells, and Num­bers is smart about how it does it. The quick way to do it is to se­lect a cell then move your mouse to the cen­tre of any of its four edges so that a yel­low han­dle ap­pears.

Like in the ex­am­ple above, se­lect the cell that con­tains your for­mula and then drag the yel­low han­dle on the bot­tom edge down to fill the for­mula into the cells be­low it. Num­bers is smart enough to in­fer that the ref­er­ences to row 2 should be re­placed with ref­er­ences to row 3 in the next cell down, and so on.

It’s also a quick way to pop­u­late a sheet with re­peat­ing num­bers. Type 1, 2, 3 into the first few cells, then drag the yel­low han­dle down. Num­bers will con­tinue with 4, 5, 6 and so on.

This works for other data types too. Say you’re plan­ning a rota, and each of the rows in the first col­umn is the start­ing date of a week. En­ter a cou­ple of dates and then fill down. Num­bers will re­alise that the dates are seven days apart and in­cre­ment by that amount as it fills each of the cells.

19 En­ter car­riage re­turns within cells

Press­ing Re­turn usu­ally ad­vances you to the next cell down in Num­bers, but if you hold as you press it, you’ll get a car­riage re­turn so you can en­ter text on a new line within the same cell.

20 Con­di­tional for­mat­ting

You can have cells change their ap­pear­ance – high­light­ing the cell with a colour, chang­ing the ap­pear­ance of the text it con­tains, and so on – based on whether some cri­te­ria are met. For ex­am­ple, if you’ve set a monthly bud­get but you’ve gone over it, you might change the num­ber’s colour to red.

Se­lect the cells you want to act on, and then click the For­mat but­ton to re­veal the side­bar. Click the side­bar’s Cell tab and then click the Con­di­tional High­light­ing… but­ton at the bot­tom to set up for­mat­ting rules for the se­lected cells. There are a load of use­ful pre­set styles, but scroll to the bot­tom of the list and choose Cus­tom Style if you want to set up

your own.

21 Lock ob­jects

If you lock any el­e­ment on a Num­bers page – or in any other iWork app, come to that – it means it can’t be moved or deleted, mak­ing it per­fect for stand­ing in­for­ma­tion such as a logo and a text box de­tail­ing terms and con­di­tions on in­voice sheets. Just se­lect the ob­ject then

press or choose Lock in the Ar­range menu. Now, even if some­one se­lects it, ei­ther by click­ing on it or by press­ing and

then hits Delete, it will stay on the page. If you need to ad­just the el­e­ment, se­lect it and choose Ar­range > Un­lock or press

Styles that you ap­ply to one ta­ble, in­clud­ing its head­ers and foot­ers, can be saved to ap­ply to oth­ers to achieve a con­sis­tent look.

Newspapers in English

Newspapers from Australia

© PressReader. All rights reserved.