Pivoting on Two Fields

In Creating a Simple Pivot Table, I showed how to summarize the data by one field, namely summarizing sales data by the city field. In this example, I show how to summarize the data based on two fields, a row and column field. Assume you have a list of expenses, their dates, and to which account they were charged.

Pivot21

To see the what was charged to each account by month, we’ll need to pivot the Amount data on both the Transaction Date and the Expense Account. Since we want to see data by month, and not day, we’ll create a new column adjacent to the data to hold the month.

Pivot22

With our fields set up, we can start creating the table. Choose Data>Pivot Table and Pivot Chart Report. On the wizard, choose an Excel list and a Pivot Table. Excel will guess the range, but if it guesses wrong, simply select the range that you want to pivot. Then choose New Sheet.

Pivot23

First, we’ll add the Month field to the column area (click on the Month button on the toolbar and drag to the “Drop Column Fields Here” area).

Pivot24

Then add the Expense Account field to the row area.

Pivot25

Finally add the Amount field to the data area. A little formatting later, and your table should look something like this.

Pivot26

Now you can see your total expenses for each month, the total by account for the whole year, and each month/account that makes up those totals.

Posted in Uncategorized

2 thoughts on “Pivoting on Two Fields

  1. Wouldn’t it have been eaiser to add the =month() formula as a column to your data, and then simply run the pivot table to include this column?

  2. An easier way may be to:
    – don’t create a month column
    – include the transaction date field in the pivot table instaed of the month field
    – put your cursor over any date in the table
    – right ckick and select GROUP AND SHOW DETAIL
    – select GROUP
    – Select Months and Years (quarters too if you like)

    The Pivot table will now create 2 new fields As years and months that you can now use dynamically in the pivot table


Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published. Required fields are marked *