How PowerPivot can help financial reporting
Business intelligence expert Philip Taylor (aka ExcelCrafter) puts the case for using the PowerPivot add-in for management and financial reporting.
Unlike earlier versions, Excel 2010 offers two approaches to building a PivotTable from a worksheet range:
- The first is the usual method of clicking in the range and then, on the Insert tab, clicking PivotTable - AccountingWEB’s Excel Zone provides a wealth of resources and tutorials on this technique.
- The second approach uses the PowerPivot add-in, a free download for Excel 2010 users available from Microsoft: Microsoft SQL Server 2012 PowerPivot for Microsoft Excel 2010.
Early adopters of Excel 2013 will require the Office Professional Plus version to take advantage of the functionality described in this article. Enable the add-in which is automatically installed as part of the Office 2013 installation (named Microsoft Office PowerPivot Add-in for Excel 2013).
Creating a PivotTable with PowerPivot
The steps required for creating a PivotTable using PowerPivot are easier than you might think. Watch this video or follow the instructions below.
Click in the Excel range that holds the data. On the PowerPivot tab, click Create Linked Table (or Add to Data Model in Excel 2013):
Tick the box My table has headers and click OK.
After a few seconds, the PowerPivot window will open and display in a table (called Table1 or similar) your data from the Excel range. You can rename this table and you can use the functionality of PowerPivot to merge data from other tables or to use the DAX language to create additional calculated columns. For now let's keep it simple and click on the PivotTable button on the PowerPivot tab; choose to create the PivotTable either in an existing worksheet or a new one:
The 2010 PowerPivot Field List looks somewhat different from the classic one. It has a Search box, Slicer sections (Slicers can still be added to a classic Excel 2010 PivotTable) but no Defer Layout Update check box for some reason. Also the list of tables and columns is displayed differently (without going into detail). The 2013 version has reverted more to the classic style.
Creating the PivotTable returns you to Excel. Notice that the data range you started out with is now an Excel table that expands and contracts as you add data (by keying or pasting in new rows) or remove data:
What’s the advantage of using PowerPivot?
A PivotTable created from PowerPivot data gives you the option (on the PivotTable Tools/Options tab, in the OLAP Tools drop-down) to Convert to Formulas:
If your report has a report filter, you will be asked if you also want to convert it to a formula. For now say No; you can always click on it, repeat these steps, and convert it later.
The ability to convert a PivotTable to formulas containing Excel cube functions allows you move bits of the report where you want them, just like a regular Excel report. The CUBEVALUE functions retrieve data from PowerPivot based on parameters in the worksheet, typically row labels, column headers and report filters. It is quite easy to add rows or columns later to accommodate new accounts or time periods.
You can insert/delete/cut/copy/paste/hide all you want. But your report will still be live-linked to the PowerPivot data model. That is enormously powerful when you are building management reports and dashboards or financial reports that require very specific placement such as profit and loss statements and balance sheets. For this you would need to import your account balances into PowerPivot either directly or by pasting them into a Linked Table.
A linked Table can also be used to load reporting journal entries into your PowerPivot model but that is a topic for another article.
If you create a PivotTable from a range in the usual way (Insert-PivotTable), then the menu options on the OLAP Tools drop-down will be greyed out and you won’t be able to convert your PivotTable to formulas. While you can use GETPIVOTDATA functions to achieve some flexibility in report placement, these require a visible PivotTable to link to and are not as powerful nor as easy to work with as cube functions.
Using Excel cube functions is just one of several benefits you can derive from getting your data into PowerPivot, either from a worksheet range, from external sources, or both, and using it as your data source for PivotTables and reports.
Philip Taylor is a business intelligence architect/developer who operates an Excel/BI information-and-products web site, www.excelcraft.com