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