Excel FAQs: Working with accounts data
You might also be interested in
Simon Hurst is the founder of technology training consultancy The Knowledge Base and is a past chairman of the ICAEW's IT Faculty.
Replies (5)
Please login or register to join the discussion.
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.
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.
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.
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
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.