Excel Pivot Table for Financial Data

Last week, you saw a technique for changing monthly data into a better layout, when creating an Excel pivot table.

We used a multiple consolidation ranges pivot table to change this 13-column table into a 3-column data source.

Today we’ll look at a different approach to using multi-column data in a pivot table.

Bi-Weekly Data

Thanks to Jim LaBarr, who sent me a sample Excel file, with his solution to creating reports from multi-column financial data.

In his file, Jim has dates in the heading row – not just the month names. Every column has a heading, and there are no blank rows or columns within the data.

NOTE: Usually, Jim would create an Excel Table from this data, but that changes the headings to text. Don’t create an Excel Table if you want to use the technique that Jim is sharing today.

Pivot Table from Bi-Weekly Data

After setting up his bi-weekly financial data, Jim creates a pivot table, using multiple consolidation ranges. This creates a pivot table with 3 fields – Row, Column and Value.

The dates are the Column items, and the descriptions are the Row items.

This multiple consolidation ranges pivot table shows a grand total for the columns, because all the dates are in the Column field.

NOTE: If we had created a normal pivot table from the data, each date would be a separate field, and there would be no grand total.

Filter the Pivot Table

After creating the pivot table, Jim filters the Row and Column fields, to focus on specific data. For example, you could filter the Rows to show only the Revenue items (but not the Revenue Total). Then, filter the Columns, to show the December 2010 dates.

The Row and Column grand totals automatically update, to show the totals for the filtered pivot table.

Change the Pivot Table Values

When you create a pivot table, the values are summarized by Sum or Count, and shown as simple totals. For a different view of the data, Jim changes the way the Values are shown. In the pivot table shown below, the Values are shown as % of Column.

This is a quick way to see the revenue breakdown, for the two revenue streams. Revenue A produces 60% of the revenue, and 40% is from Revenue B.

Grouping the Dates

Because the multiple consolidation ranges pivot table puts the Dates in a single field, Jim is able to group those dates. This gives him even more flexibility in his pivot table reports.

Here, the dates are grouped by year and month, and again, the grand totals are automatically calculated.

Or, the Column field can be moved to the Row area, to show the dates at the left, in a vertical report.

Additional Summary Functions

In another version of his pivot table report, Jim puts 3 copies of the Value field in the Values area.

• Sum
• Change
• % Change

To show the Change, Jim changes the custom calculation to Difference From. For % Change, Jim uses the % Difference From custom calculation.

Create a Pivot Chart

To give a visual overview of the data, Jim create a pivot chart from the multiple consolidation range pivot table. Here, the Years and Months are in the Row area, and the Descriptions are in the Column area, filtered for Total Cost and Total Revenue.

Jim’s Tips for Financial Data Pivot Tables

If your financial data is in many columns, like Jim’s example, here are Jim’s tips for when to use this multiple consolidation ranges techniques, and when to use an Excel Table instead.

• Use this method to keep multiple columns, with pivot table benefits, like date grouping
• Use Tables if all you need is Filtering on Rows
• Use Tables if you need Chart of multiple Rows with no need for time period Grouping
• For source data with multiple description columns, use the normalization workaround

Download the Sample Financial Data Pivot Table File

To see Jim LaBarr’s sample data, and the pivot tables, you can download his Financial Data Pivot Table sample file. The file is in Excel 2003 format, and zipped.

______________