Getting Started
Just like Pages, Numbers can be purchased off the App Store for the low price of $19.99 or you can choose to get it pre-installed along with your brand shiny new Mac. And, just like Pages, it also supports iCloud so you can view your spreadsheets across all of your devices.
Numbers comes with some really well-designed and useful templates, built in, which are sorted by category (e.g. personal finance, business, education and so on) so if you’re looking to whip something up quickly without having to bother design your spreadsheet first, then these may be a good option for you to start with.
For this tutorial, however, I’m going to start with a brand new, blank spreadsheet so go ahead and select theBlank template to start with.
Finding Your Way Around
If you’ve used a spreadsheet program before, then Numbers shouldn’t be too difficult to find your way around. Rows run horizontally and are numbered, whereas columns run vertically and are marked with letters. Each spreadsheet can have a number of sheets in it and you can easily add new ones by clicking on the Sheetbutton in the toolbar.
You can choose a style for your spreadsheet by clicking on the Styles section in the bottom-right hand corner of the screen. This can make your spreadsheets a lot easier to read which can be important, especially if you’re presenting a lot of data. Though for this tutorial, we’ll keep the standard Basic style.
Working with Data
To show you what you can do with Numbers, I’m going to use some sample data showing sales representatives across various regions in Canada and the items that they have sold. As you can see in the screenshot below, I have the titles for each data set in the top row (Numbers has conveniently placed these in bold, so that I can see them easier) and each set of data running down the rows.
At the moment, the data is completely unsorted. I want to sort it by the number of units sold by each rep, so I can see which one is performing the best. Click on Reorganize in the toolbar (the fourth icon along from the left), where a little dialogue box should pop up:
Here, the default setting is sorting by Region, as you can see in the screenshot above. I’m going to sort viaUnits and I want the sales rep with the highest number of units sold at the top of the table, so I selectdescending. Make sure that the entire table is sorted (not just selected rows) then click on Sort Now. You can now see that my data has been sorted by the number of units and I can see that sales reps “Howard” and “Kivell” have each sold 96 pens and pen sets respectively.
Of course, you can sort via any category — just select the relevant one from the drop down box — and either by ascending or descending values (if you are sorting alphabetically, then choose ascending to sort your data from A to Z).
Formulas
Even if you aren’t a genius at maths, formulas in Numbers can make it very easy to manipulate large amounts of data very easily. There are absolutely loads of formulas built-in to Numbers (so many that this will be covered in more detail in a separate tutorial!) but we’ll have a look at some of the basics here.
Adding up columns of numbers is probably the simplest formula that Numbers offers, but you’ll find that this one comes in handy so many times when you’re using the software! Let’s say that I want to add up my Units,Unit Cost and Total column. I’ve added a blank row right at the bottom for my totals (to do this, hover over the last row — in my case, row 44 — where a little arrow button should appear. Click on this then select Add Row Below; alternatively, select the last row then press Option + Down to add a new one!) so I can see them easier. Select the box where you’d like your total, then type in the following:
=SUM(
It’s probably worth just breaking off here to explain briefly how formulas work in Numbers. In short, you define which cells you’d like Numbers to work with. So, in my Units column (which is column D in this example), if I’d like numbers to add up the first 3 values, then I’d write:
=SUM(D2+D3+D4)
This is, in short, the same as doing:
96 + 96 + 95 = 287
But, I want Numbers to add up the entire column. Of course, I could start typing:
=SUM(D2+D3+D4+D5+...)
This method is extremely long-winded, however. Don’t fret, there’s a simpler solution — just type:
=SUM(D2:D44)
This adds up all the data in cells D2–D44 (D44 happens to be the last cell before my total). Alternatively you can start typing in the formula then select the data values you wish to calculate with using the mouse, as you can see in the screenshot below:
Numbers has highlighted the cells that I’m going to be working with in blue. When you’ve finished, click on the little green tick icon in the formula box (or simply hit Enter) and the result should be displayed for you. This method works for a variety of other formulas, such as
AVERAGE (to find the mean of a data set), MIN (to display the minimum value), MAX (to display, you guessed it, the maximum value) and COUNT (to give you the number of observations or data sets).
I mentioned earlier that I wanted to extend this formula to the other columns. I could do it the long-winded way and enter the formulas separately in each column, however there is a quicker way: simply hover over the bottom right-hand corner of the cell you’ve just used for the formula (the cursor should change to a cross symbol) then drag it across to the next column:
Numbers conveniently displays a preview of the result and the formula is automatically applied to the next column. So, as you can see in the screenshot above, my
=SUM( formula has been applied to column E and F (my Unit Cost and Total column).
This method of dragging can also be used in other scenarios. Say, for example, you wanted a column with a sequence, for example, the months of the year, in there. Simply enter the first couple of values (I usually do 3, so Numbers cottons on quickly), i.e. January 2013, February 2013, March 2013 then click on the bottom-right hand corner of the cell containing March 2013 then drag downwards. Numbers will automatically continue the sequence, meaning that you don’t have to enter each value manually.
Sequences can be almost anything, from numerical (e.g. 1, 2, 3; 2, 4, 6; 10, 20, 30) to days and years. Just ensure you enter the first couple of values so Numbers knows what kind of sequence it is!
Formatting Cells
Formatting cells within Numbers is really easy to do and is fairly similar to Pages. Running along the top, you’ve got the toolbar where you can change the font, its size, the background colour and the font colour of your individual cells. You can also change the number format within cells; looking at the screenshot below the 1.0 icon will format your cells as a number with 2 decimal places, the £ symbol will format it as a currency (using your Mac’s default regional settings), the % icon as a percentage and the tick icon as a checkbox.
For more advanced cell formatting options, you can click on the little downwards arrow which will present you with a few more options (e.g. dates, times and alternative currencies). You can also increase and decrease the number of decimal places by clicking on the .00 and .0 buttons respectively — Numbers will automatically round off any cells as required.
Exporting Your Work
Numbers allows you to export your spreadsheets in either PDF, Excel or CSV format. To do so, just click on File then Export where you can choose your desired format. If you save it to iCloud then it’ll appear on all your iOS devices as well, where you can edit it on the move and all changes will automatically be synced when you get back. You can also revert spreadsheets back to previous versions by clicking on File then Revert To and select Browse All Versions.
As you can see from the screenshot above, you’ll be presented with a Time Machine-like view allowing you to scroll back through all versions of your spreadsheet. Any changes you make are automatically synced, so if you’ve made a major error and don’t fancy hitting Command + Z (undo) a hundred times, then the Versions feature can save you a lot of time!
That’s It!
Congratulations, you’ve successfully reached the end of this tutorial! Numbers is of course a really advanced application that allows you do an awful lot of stuff (and too much to cover in one tutorial) so I’ll be covering some more specific features in future tutorials. This one is simply designed to give you an overview of it and how it works – especially if you’re switching from another spreadsheet, such as LibreOffice’s Calc or Microsoft’s Excel.
I hope it has been useful and if you need any help with anything then just ask me in the Comments section below – I’ll be glad to help you out!