Tom Gorham
The ability to organise data into different categories makes Numbers a powerful tool if you find yourself working with long lists of information.
Sometimes it seems that Numbers can’t win. Back at the turn of the year I was asked by a friend to recommend a Mac-based accounting program. Based on his simple needs – invoice tracking and expense-tallying was pretty much the sum of it – I thought that he’d be better off sticking with iWork’s Numbers rather than shelling out for a dedicated finance application.
Surprisingly, this money-saving suggestion didn’t please him as much as I thought it might. ‘But spreadsheets are a little too complicated for me,’ he complained. ‘Is there anything simpler?’
Fast forward to earlier this week when I overheard a Windows-using colleague voicing what sounded suspiciously like exactly the opposite opinion to the Mac user. ‘What Numbers lacks,’ he said in a reassuringly knowledgeable tone, ‘is features. It’s just too basic for business use. It doesn’t even have PivotTables.’
So Numbers is too complicated and too simple. Both of these people may have a point, but they’re also missing the fact that while Numbers is more powerful than many people think, it also makes it easier to organise data than you’ve been led to believe.
Yes, Numbers lacks Excel’s PivotTable feature. For those swathes of iWork users who haven’t the foggiest notion of what pivot tables are, never mind their value, essentially they organise source data interactively, so you can group related content and rotate its rows and columns to see different summaries of the same data. In short, it provides flexible views of your data.
It’s equally true that Numbers in its most basic form lacks the sort of presentational punch you get from a finance application: examining row after row of invoice data in a table isn’t the easiest way to see which client is behind in their payments.
However, a little-known Numbers 09 feature, category tables, could address both those complaints. These can do the complicated organisational grunt-work that my Excel-loving colleague would like. At a click, they can round up and sort all that free-range data, simplifying and clarifying your spreadsheet in assorted ways. In fact, in many ways category tables are a perfectly good functional substitute for pivot tables.
Its power is best shown by example. Suppose you had a spreadsheet containing multiple rows of invoice information, with columns comprising client names, invoice numbers, project names, amount invoiced, amount paid and so on. As it just recorded invoices as they were prepared, there would be several occurrences of client names and projects in the list.
Such a basic list makes it impossible to see the important bigger picture at a glance – how much did you earn from one client, or what invoices remain unpaid?
When you create a category table, you organise such a list into separate groups. You can categorise data automatically by clicking in a column header you wish to use as a classification, and selecting ‘Categorize by this column’. This sorts the data by that column and creates a different category for each unique value in it. If, in our example, we chose to sort by the Client column, each client would appear as a different category, with all invoices that relate to that client contained within it. Numbers creates a category row above each grouping that shows the name of the category.
Instantly your data becomes better organised. If you don’t want to see invoices relating to one particular client, you simply click the disclosure triangle to the left of the category row, which collapses its contents. Rows automatically move under the correct category if their categorised value changes – so if you changed the name of a client, the row to which it relates would automatically move – or create a new category if one didn’t exist beforehand.
You can create sub-categories within categories, too. Let’s say, for example, that you wanted to sort your invoices by client and organise these by individual project. To do this, first categorise by the client column, and then select the Project column and choose ‘Categorize by this column’. The contents are now organised by project within each client category.
The beauty of using Numbers as an invoicing tool is its flexibility: you can see whether an invoice has been paid just by adding an extra conditional formula to the table and categorising by this formula. The formula checks whether the value of the cell containing the amount paid by the client is the same as the amount invoiced. If it is, then the invoice is marked as ‘paid’; if not, it’s flagged as ‘unpaid’.
As a Numbers formula, this could be expressed as =IF(D5=C5, “PAID”, “UNPAID”), where D5 contains the value of the invoice paid, and C5 the value of the original invoice. Copy this formula down the column and then categorise according to this column.
For presentational purposes, I prefer to hide this column from view (‘Hide column’ from the column header’s pop-up menu), as it’s neater and the category table works fine without it.
This flexibility also allows you to see data in different ways, such as flipping categories around. Instead of organising by client and then by project, you can arrange by project and then client by promoting the sub-category above its parent category: just select the ‘Promote’ option next to the category row header.
Category tables provide some nice little time-savers. Styling one category row will automatically style all category rows the same way, and if you edit a category’s name by double-clicking its title cell, that automatically changes all instances of that category’s value within the document – a sort of super-fast find and replace.
Where category tables show their true worth is in their ability to automatically calculate values and summarise data within each category. You create summaries by clicking on the category row title for a particular column and selecting one of the available options – total, average and so on – from the pop-up list. In our example, that would provide a quick insight into how much a particular client has paid over time, or the value of their unpaid invoices. This is a great way to summarise your data if used in conjunction with the Collapse All command that you can apply to categories from the category row pop-up menu. All categories are collapsed and show a neat summary of your invoice income under various headings.
What would be good is if you could work with these figures outside the category table. The biggest weakness of category tables is that you can’t reference the contents of a category cell in any other calculation. The reason is probably that category tables are simply presentational views of your data. Sadly, there’s not much you can do directly with the information displayed by the category table, save for the ability to export it in its organised form to Pages.
Still, category tables are a great way to visualise data and are useful in all sorts of situations. While I’ve used invoicing as an example, it’s also ideal for organising data such as class scores – checking who’s passed an exam in which class, or showing students’ average marks.
