Excel FAQs: Working with accounts data

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.

The rest of this article, available to logged-in AccountingWEB users, also covers:

 

  • Which are better for summarising accounts data - PivotTables or SUMIF functions?
  • What can you do with Excel Tables?

 

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

 

Continued...

» Register now

The full article is available to registered AccountingWEB members only. To read the rest of this article you’ll need to login or register.

Registration is FREE and allows you to view all content, ask questions, comment and much more.

Comments
gfeechan's picture

Pivot Tables vs SUMIF

gfeechan | | Permalink

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.

 

chanpangchi's picture

PowerPivot and Slicer

chanpangchi | | Permalink

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.

PowerPivot has a look up

Cantona1 | | Permalink

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.

chanpangchi's picture

Excel is a very good data

chanpangchi | | Permalink

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.

PowerPivot and Excel Cube Functions

Excelcrafter | | Permalink

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.