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.

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.

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.

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).

Then add the Expense Account field to the row area.

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

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.