Learn how to use Apple’s spreadsheet program, Numbers

Numbers isn’t just a spreadsheet application. The tables that can be placed on the Sheets in Numbers are the spreadsheets, but a sheet can contain multiple tables, charts, and media. The media can be photos, graphics, sound clips, or movies. Try using Numbers as a flexible and expansive pin-board that can perform data analysis. For example, if researching a new business idea copy text and picture information from webpages, and other documents, then paste them into Numbers as either text boxes or images objects creating a pin-board on which you can add tables to develop your budget plans.

Other pin-board scenarios could include school science reports, where videos of experiments made can be included. Numbers like Pages, and Keynote comes with templates that suggest other pin-board topics for example Travel Planning.

To help navigate the pin-board take note of the options beneath the Toolbar. On the left is a plus symbol, followed by the names of the Sheets in the current Numbers document. Clicking the plus symbol adds a new Sheet, and hovering a cursor over the Sheet tab reveals a menu symbol. Clicking that symbol opens the Sheet menu, where you can perform operations to a sheet such as rename, duplicate, or delete it.

Read: Microsoft Excel vs Apple Numbers

Building on the first tip, it is a good idea to name sheets. To explore this you could open a new spreadsheet from the Party Planner template. In Numbers choose File > New, from the template chooser window locate the Party Planner template and open it. The new document opens with four sheets, Budget, Guest List, Vendors, and To-Do List. The Budget sheet is the only Sheet to contain multiple tables and charts, these have been named, Budget, and Guests. The chart is called Budget Overview.

Tables and Charts can be renamed. When first created, they inherit the default names Table 1, Table 2, or Chart 1, and so on. To rename a Table or Chart first select it then use the Format Inspector. The inspector is context aware so if you click a table it will show a Table tab, or a Chart tab when a chart is selected. In the respective tab locate the Table Name or Chart Options and make sure the Title is selected. When the Title is selected the Table or Chart will display its title as a label. Double-clicking that label will let you change the title. When building advanced spreadsheet with multiple tables and charts having descriptive names for the sheet contents is essential.

Read: Alternatives to Numbers

Way back in the soft focus mists at the dawn of the spreadsheet, users would often use a single spreadsheet to hold multiple calculations. This methodology has been taught in some schools, but really needs to be reevaluated. It is better to place multiple tables on either a single sheet or over several sheets to break up complex calculations and help organise your data. Let’s use the example of business planning. A simple business budget could have one table for, incomes, expenditure, and cash flow. The income element of that table could be linked to a separate table that totals the total billable hours. The calculation could involve the total billable hours for a team of people each charged at different rates. Isolating the billable hours as a separate table makes troubleshooting and data analysis easier. The results from one table can be fed into other tables using Cell Referencing.

To reference a cell, regardless of the table it is in, click the cell where you want the referenced data to appear. Press the = symbol, this opens the Formula Editor. Then click the cell you wish to reference. To maximise the potential of this topic you’ll need to understand the difference between relative and absolute cell referencing, this is covered in a later slide.

Read: How to use Pages on the Mac

Pages, Numbers, and Keynote are all context aware. They try and guess at what you’re doing and try to assist. It’s a bit like predictive text; often a help, but sometimes a pain. The tables in Pages, Numbers, and Keynote share an automatic Data Format feature. When a £ sign is typed into a cell, the application guesses you’re adding currency values so formats the numbers that follow a currency sign appropriately. However, if you’re trying to add a phone number to a cell, let’s say with an area code beginning 01538, Numbers may automatically remove the zero. This is automatic formatting at work, if this is case you’ll need to set the Data Format manually. To change the Data Format click the required cell then in the Cell Inspector change the Data Format to Text.

To apply a manually set Data Format across several cells, the format can be dragged. If you hover your cursor over a selected Cell yellow circles appear in the cell borders. The yellow circles can be dragged to apply the format across other cells in a row or column.

Another use for this dragging is to automatically populate cells with data. On a blank table, with at least seven columns, type Monday into the first column on the left. Click that cell and hover your cursor over the cell to invoke the yellow circle on the right hand edge of the cell. Drag that circle to the right. The columns will automatically fill with Tuesday, Wednesday, Thursday and so on. The same can be done with months, dates, and other sequences.

Although you can work with Numbers solely on your iPad or iPhone, the Mac version of the application contains more editing features. By starting a spreadsheet design on a Mac you can add more functions and enjoy easier editing than via iOS. But, saving your spreadsheets to an iOS device makes portable data entry a breeze. The easiest way to do this is via your iCloud account. On your Mac save the Numbers spreadsheet to iCloud then open it on your iOS device from the same location.

When the spreadsheet is open on the iOS device assess it for legibility, text, and cell size. You’ll need to be able to tap cells to edit them. If they are too small you could enter a frustrating world of missed taps and incorrect selections. Another design consideration for both Mac and iOS is keeping header rows and columns in view at all times, regardless of table size. To do you this select the required table. The table inspector needs to show that there is at least one header for columns and rows. Then clicking the reference header for the rows choose Freeze Header Rows. Repeat this operation on the reference header for columns, and Freeze Header Columns.

Read: Numbers for iPad & iPhone review

Having Numbers on OS X and iOS is more than a simple convenience. Moving beyond simply using Numbers for data entry on iPads, you can develop spreadsheets that behave like mini-apps. Okay there are lots of apps readymade, but if for example you need a custom calculator to generate a job quote the process of developing one doesn’t require any special coding knowledge. Numbers can be used to create easy to use, and clear user interfaces. Deployment of the spreadsheet is easy too, and doesn’t not require developer licenses et al.

To make a Numbers spreadsheet behave more like an app investigate the interactive data formats. To access these, click a cell in any table and look at the Interactive Data Formats found in the Cell Inspector. Check boxes and Star Ratings are self explanatory. Steppers and Sliders let you fix a range of values. If a Slider was to represent the hours of work on a project it might set to range from zero to one hundred. Creating pop-up menus are super-useful, using the job quote example imagine a curtain maker who wants a reliable calculator to give on site costings to client; all on an iPhone. One pop-up menu could be created for fabric types, and other for the fabric colour. These pop-up values can be converted to number values using the Formula Editor.

Read about how you can switch from iPhone to Mac or vice versa in some Mac programs: How to switch between your iPhone, iPad and Mac using Handoff

Clicking a cell and pressing the = key opens the Formula Editor. By default this appears as a narrow single line, but can be stretched both horizontally and vertically. Making the Formula Editor larger helps you input and analyse larger formulae.

In the example an IF query has been used. The formula used was typed like this:

=( IF(F2= “Red”, 10, IF(F2= “Green”,8, IF(F2= “Blue”,9,))))

The cell F2 is selected in the screen shoot, rather than being labelled F2 in the Formula Editor the name is created from its headers Colour and Curtain 1, but the cell referenced is F2.

In plain English the formula reads if the cell F2 contains the word Red, then return a value of 10. Referring to the curtain designer example this could be used to turn a fabric colour into a money value say £10 per metre if red, or £8 if green.

It would be import to use pop-up menus for the colours, fabrics and so on as mistyping red, green, or blue in this equation would return an error.

When the Formula Editor is open the inspector changes to a Function browser that list functions grouped in to disciplines such as Engineering, Financial, and Statistical. The browser also gives examples of the functions in a variety of formulae to help explain their use.

By default Numbers uses relative cell references. In the illustration the formula references the Curtain 1 Row and Colour Column. To populate Column A in the Totaliser table a drag operation was used. This automatically filled Column A of the Totaliser with cell references from the Colour column of the Curtain Calculator. The default relative referencing works for the column.

However the screen grab shows a drag operation taking place in Row 1 of the Totaliser. This will cause an error because the formula will look at two columns to the right of the Colour column, which do not exist. To overcome this problem the form of cell referencing can be changed. Clicking a cell with a formula in it, then click the cell again opens the Formula Editor. Any referenced cells appear in lozenge shaped elements. Clicking one of these lozenges opens options for how cells are referenced.

When using multiple tables where one table generates a total to be used in a second table, changing the cell reference is often essential.

In an earlier tip it was suggested that you could design spreadsheets to display appropriately on iOS devices. This would aid data entry on iPads or iPhones. Numbers for iOS does have a neat form option that bypasses the need to consider cell and table sizes on iPads. It helps to have named your tables before using Forms.

In Numbers for iOS you need to have at least one sheet that contains a table with a populated header row, for example First Name, Surname, Address, Telephone. Towards the top left of the iOS screen is a + symbol. Tapping invokes the option to either create a New Sheet or New Form. Choosing the later moves you to a new screen that asks to select the table to be used for the form, hence the suggestion to name your tables.

The Form view makes adding or modifying entries simple and clear. Interactive Data Formats are honoured so you be able to use any pop-up menus, or sliders you created.

To learn more about Numbers for iOS try turning on the coaching tips, these are revealed by tapping the question mark in the top right of the iOS screen.

The information in spreadsheets will often need protecting, perhaps it is commercially sensitive, or lists names and addresses so is subject to data protection. All this can make for a rather expansive topic, but from a Numbers stand point applying password protection is a good idea, and will demonstrate you exercise due diligence. The password protection process is the same for Pages and Keynote. In Numbers for OS X choose File > Set Password, and enter a password in the required fields.

Adding a password hint is a good idea, but to strengthen your security do not let Keychain remember the password. This means you will need to enter the password every time you open the spreadsheet regardless of whether it is on your Mac or on an iOS device. Having to enter the password ever time you open a spreadsheet may seem like a drag, but with devices like iPads that get used in public spaces it’s a small price to pay for the extra layer of protection.