Hostingheaderbarlogoj
Join InMotion Hosting for $3.49/mo & get a year on Tuts+ FREE (worth $180). Start today.
Advertisement

Getting Started With Numbers on OS X

by
This post is part of a series called Spreadsheets for Finance.
How to Extract Data from a Spreadsheet using VLOOKUP, MATCH and INDEX
How to Format Your Spreadsheets in Excel with Styles

In a recent tutorial I took an introductory look at Pages, Apple's often neglected word processor and tried to show you that despite its fairly basic interface, it is an incredibly powerful piece of software that allows you to create a multitude of different documents. In this tutorial I'm looking at Numbers -- Apple's answer to Microsoft Excel. I'm a seasoned Excel user, mostly because of my university degree which requires us to manipulate data in Excel and I'll freely admit here: Numbers is certainly nowhere as powerful as Excel when it comes to advanced features.

Having said that, though, Numbers does make it easy to create a nice, simple spreadsheet and manipulate some data, and in this tutorial I'm going to show you how to do so!


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.

The splash screen of Numbers, showing the iCloud integration.
The splash screen of Numbers, showing the iCloud integration.

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.

Numbers comes with loads of templates already built-in, which is extremely handy if you just want to get up and started straight away.
Numbers comes with loads of templates already built-in, which is extremely handy if you just want to get up and started straight away.

For this tutorial, however, I'm going to start with a brand new, blank spreadsheet so go ahead and select the Blank 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 Sheet button in the toolbar.

The main interface of Numbers.
The main interface of Numbers.

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.

Some sample data that I've added in my spreadsheet.
Some sample data that I've added in my spreadsheet.

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:

Reorganising data is a really simple yet useful tool within Numbers.

Reorganising data is a really simple yet useful tool within Numbers.

 

Here, the default setting is sorting by Region, as you can see in the screenshot above. I'm going to sort via Units and I want the sales rep with the highest number of units sold at the top of the table, so I select descending. 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.

As you can see, my data has been sorted by the number of units sold. The highlighted section shows the top sales reps.

As you can see, my data has been sorted by the number of units sold. The highlighted section shows the top sales reps.

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:

Notice how Numbers has coloured the column blue, making it easier to see which data is being manipulated.

Notice how Numbers has coloured the column blue, making it easier to see which data is being manipulated.

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.

Working with a sequence within Numbers.

Working with a sequence within Numbers.

 

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.

The formatting toolbar within Numbers.

The formatting toolbar within Numbers.

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.

The Versions view within Numbers.
The Versions view within Numbers.

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!

Advertisement