Partner The Knowledge Base
Share this content
AIA

Excel FAQs: Working with accounts data

by
13th Jun 2012
Partner The Knowledge Base
Share this content

Simon Hurst reviews the techniques available for extracting and reporting on accounts data with the tools available in Excel 2010.

Looking back through the Excel Compendium at the range of questions on this subject, the most common issues come from accessing the data in the first place and then finding efficient and reliable ways to summarise the data.

Is Excel the most effective tool for reporting on accounts?
A recent Any Answers query on downloading the books into Excel and then moving things around revived an old ExcelZone debate on whether accounting data should be manipulated in Excel, rather than just summarised and presente. And once again, we should heed the warnings to keep the processing within the accounts system.

As accounting software application suppliers provide new facilities to export data or to link other applications to it directly, getting at the data should be easier. But there’s still the issue of organising the date into a form that enables Excel to work with it.

Prior to the introduction of the Excel 2010 PowerPivot addin, the choice seemed to be to pre-process the data in a database such as Microsoft Access or to use Excel’s data import and linking tools to bring in raw data and use Excel formulae and functions to sort it out. PowerPivot brings elements of database functionality into Excel making it increasingly possible to cut out the Access middleman.

Which are better for summarising accounts data - PivotTables or SUMIF functions?
Once you’ve got the data into Excel the likelihood is that any processing will involve summarising the data in some way. The main question is whether to use PivotTables or Excel functions. Although PivotTables provide a far simple method of summarising chunks of raw data, functions to allow for more flexibility, not only in creating the summary figures in the first place but, perhaps more importantly, in creating a formatted report.

Although most of the questions implied a knowledge, and frequent use, of the SUMIF() function to summarise data based on a single criterion, issues often arose when it was necessary to summarise based on multiple criteria. For Excel 2007 and 2010 users the new SUMIFS() function with its ability to cope with up to 127 pairs of range and criterion was the obvious answer. The alternative to SUMIFS() is the SUMPRODUCT() function which has the added advantage, not only of working in versions prior to 2007, but also working with references to cells in closed workbooks – something that generates an error with both SUMIF() and SUMIFS().

What can you do with Excel Tables?
Another feature key to working with accounting data that arrived in Excel 2007 was the Excel Table – a development of the List feature in Excel 2003. When Excel 2007 or 2010 are linked to an external data source the resulting data range is created as a Table. As well as the pretty formatting, Tables have a range of special features. Formulae added to columns adjacent to the data are automatically copied to any additional rows of data added when the range is refreshed and formatting is copied down to any added rows.

Significantly for those who use Excel functions to summarise their data, references to Table columns are created using a structured language that uses the Table and column name and should help ensure that summaries automatically include the values in any additional rows. The automation provided by Tables, together with the ability to set external data ranges to refresh automatically, could help make the route from raw data in an accounting system to formatted report in Excel both automatic and robust.

Other ExcelZone management reporting tutorials
Improve your reporting skills with self-teach tutorials
Interested in Pivot Tables? Start here
Sales analysis at Northwind Traders

Replies (5)

Please login or register to join the discussion.

Glen Feechan
By Glen Feechan
15th Jun 2012 16:59

Pivot Tables vs SUMIF

Great post, Simon.

I thought I'd add my two penn'orth on the Pivot Tables vs SUMIF debate.

Both have their roles and limitations, but I find a combination of Pivot Tables and GETPIVOTDATA gives the best of both worlds.

Some of the big advantages of Pivot Tables are:

They summarise ALL of the data, not just those codes you've included in your formulae - this is particularly important when handling new nominal ledger codes;They are very transparent, you can drilldown to the raw data that makes up any number.

The main advantage of SUMIF, I believe, is that it gives complete flexibility over the presentation. This is a big advantage, as a pivot table might be great for presenting various reports in a management accounts pack, but does not look like a set of accounts.

I take the following approach, which gives the flexibility of SUMIF with the robustness and transparency of Pivot Tables:

Cross reference table to map nominal ledger codes to lines of the management accounts;VLOOKUP to pull this management accounts code alongside the TB data;A pivot table to summarise the TB by management accounts code;Two extra reference columns to the left of the management accounts to show the management accounts code and whether the sign should be reversed (with a +1 or -1);A GETPIVOTDATA formula where you would have used a SUMIF, to pull the numbers from the pivot table, multiplied by the sign reversal column. With clever use of the dollar signs, you can copy the same GETPIVOTDATA formula throughout the whole set of accounts.

I did a blog post a while back covering this approach in more detail, here.

 

Thanks (0)
avatar
By chanpangchi
15th Jun 2012 17:55

PowerPivot and Slicer

It is a great article to summarize Excel aggregation functions but it will be more comprehensive if you can include PowerPivot and Slicer.

Pivot table is a powerful utility but it has some limits.  If the data is stored in an Excel workbook, then we can only have 1 million rows; if we store data in a database, then we may have a performance issue.  PowerPivot may provide us a mean to handle large volume of data (e.g. last 10 years sale time series analysis) in a lighting speed.  It also come with its own expression formula that can handle much more sophisticated aggregation than traditional pivot table.

Slicer is a flexible tool that allows us to dynamically change our aggregation criteria visually.  Sumifs is still the most flexible way to define aggregation criteria but slicer is much easier to use because it is a visual tool.  We can use slicer with either pivot table or PowerPivot.

Thanks (0)
avatar
By Cantona1
15th Jun 2012 19:27

PowerPivot has a look up function as well, so no need to learn Lookup if you use PP.

Imo, Sum Product is the most versatile and unknown excel functions.

I had worked on very complex IF functions wherre It could have easier to use SumProduct. For e.g, sales commission calculation based on tier scale, but it is only with hindsight. I know now I can easily use Select case in VBA.

I think Excel is designed for data manipulation, not a storage tool.

Most accounting packages are not more than  data bases with the addition of double booking entries. Until MS comes up with full excel and Access integration, no matter how brillilant your accounting software would be, there is always a job for Excel.

Thanks (0)
Replying to RobertD:
avatar
By chanpangchi
15th Jun 2012 20:00

Excel is a very good data analysis tool but not a storage tool.  However, we have to load data into Excel before we can do analysis.  PowerPivot is like a personal BI tool that we can load much more data than regular Excel workbook and make our life easier.  The example I was was a time series analysis when we may need to load 10 years of sales data.

Thanks (0)
avatar
By Excelcrafter
19th Jun 2012 04:00

PowerPivot and Excel Cube Functions

Simon,thank you for the reminder on SUMPRODUCT(). That's one I overlook too often.

Worth a mention here is the support in PowerPivot/Excel 2010 for Excel cube functions that blows away GETPIVOTDATA(). There is no requirement to have a visible PivotTable and the functions can be created by converting a PivotTable. If your data is already in an Excel range you can get it into PowerPivot using the Create Linked Table button on the PowerPivot tab. Then build a PivotTable off of that and click the Convert to Formulas on the OLAP Tools tab (disabled in the case of a regular Excel PivotTable) and lay out your report the way you want.

You can link to a chart of accounts table which contains a column for report groupings/hierarchy of NL codes.

Here is a video comparing PivotTables from PowerPivot with those from a regular Excel range.

Thanks (0)