Mac Tips: Numbers for Mac: 15 productivity-boosting tips

[sincity]

http://cdn.mos.techradar.com/art/MacLifeWebContent/Numbers%20for%20Mac/numbers_for_mac_tips-hero-470-75.jpg

Tips 1-5

Data entry isn't most people's idea of fun, but the following 15 tips might change that. These shortcuts and advanced techniques will make quick work of your spreadsheets, so even the most tedious tasks breeze by.

1. Password-protect your documents


You can do this for all iWork documents, but spreadsheets can contain especially sensitive data, so 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 your Mac requiring a password to wake from screen saver, this could leave the file vulnerable.

If you have to convert your spreadsheet to Excel for sharing with some recipients, 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.

2. 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. You can explore these options 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, take a look at the window's bottom-left corner to see the actual, behind-the-scenes value that's recorded in that cell – such as FALSE for an unchecked checkbox and TRUE for a checked one – so you know what values to enter in your formula.

3. Copy and paste styles

This one also works in all three iWork apps. The formal way to take a set of attributes (such as text size and color) 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 later, but if you 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 you want to apply that style to, and choose Format > Paste Style.

4. 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 those tweaks 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 corner. Once the style is defined, you can drag it to the first screen just to make it easier to access, and then select other tables and apply it to them.

http://mos.futurenet.com/techradar/art/MacLifeWebContent/Numbers%20for%20Mac/copy_and_paste_table_styles-420-90.jpg

5. Copy from Excel!

If you're trying to do something complex in Numbers, it's often hard to find help on the web, since Excel is so much more common. The good news is that many formulae have the same names and work largely the same way in the two apps, so it's still worth reading tips designed for Excel. We can't guarantee they'll all work, but in our experience it's a handy first step to try out. Also check out discussions.apple.com/community/iwork/numbers.

Tips 6-10

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.

http://mos.futurenet.com/techradar/art/MacLifeWebContent/Numbers%20for%20Mac/quickly_extend-420-90.jpg

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.

Tips 11-15

11. 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, hiding or revealing data instantly. For example, if you used a Numbers spreadsheet to track invoices, you could set up a filter so that you can hide 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 to match the condition "cell is blank."

12. 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 Option 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.)

http://mos.futurenet.com/techradar/art/MacLifeWebContent/Numbers%20for%20Mac/temporarily_highlight-420-90.jpg

13. Add headers and footers to printed pages

When you print Numbers documents you get a page number automatically centered at the bottom of each page, but it's not clear how to remove it or add other information to the header and footer. Moving the pointer over the top or bottom of the print layout display you get after you press Command + P 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.

http://mos.futurenet.com/techradar/art/MacLifeWebContent/Numbers%20for%20Mac/adding_headers_and_footers-420-90.jpg

14. 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, our friends at Mac|Life might track deadlines using a column of dates for their print edition and another column for their iPad edition, and in a third column note down how many days they have to build the latter after sending the former to the printers. By setting up a custom date format, they can display this as "[days] to make the iPad edition."

15. Reference cells on other sheets

Like most spreadsheet software, Numbers lets you set up multiple sheets per document, and it's easy to refer across these sheets in formulae. 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. If the price list is updated, the order form will be too. If you need to change a cell reference in a formula that you've already written, double-click the cell that contains the formula. This reveals the formula in a floating bar. Click once on the colored 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 new cell you want to reference.

http://rc.feedsportal.com/r/238385224847/u/49/f/415085/c/669/s/498ccab4/sc/28/rc/1/rc.img

http://rc.feedsportal.com/r/238385224847/u/49/f/415085/c/669/s/498ccab4/sc/28/rc/2/rc.img

http://rc.feedsportal.com/r/238385224847/u/49/f/415085/c/669/s/498ccab4/sc/28/rc/3/rc.img

http://da.feedsportal.com/r/238385224847/u/49/f/415085/c/669/s/498ccab4/sc/28/a2.imghttp://pi.feedsportal.com/r/238385224847/u/49/f/415085/c/669/s/498ccab4/sc/28/a2t.imghttp://rss.feedsportal.com/c/669/f/415085/s/498ccab4/sc/28/mf.gifhttp://feeds.feedburner.com/~r/techradar/software-news/~4/SHLcM0zWTno