Daily Dose of Excel

This is a simple example of how to use a calculated field in a pivot table. I use the Orders table from Northwind.mdb to summarize the orders. I add a calculated field to show me the Extended Price for each product on the order. I’ll be going start to finish assuming you know nothing about pivot tables, so if you like screen shots, you’re in luck. If I gloss over any step, check the other posts in this category.

Select Pivot Table and Pivot Chart Report… from the Data menu. In Step1, select External Data so we can bring in the Orders table.

Select the Get Data button, select Microsoft Access 97 Database (or whatever version you like), navigate to the Northwind.mdb (Mine is in C:\Program Files\Microsoft Office\Office\Samples\). Once there, find the Order Details table (okay, I was less than forthright earlier).

Move all the fields over to the left listbox and click the Next button four times - or whatever it takes to return data to Excel without adding anything more. Excel should now tell you data’s been retrieved.

Click Next, then Finish to get something like this

Drag Order ID to the page field; Product ID to the row field; Unit Price to data items

Now the dragging gets a little tricky. Drag Quantity to Data Items like you did with unit price, but you don’t have those big letters to guide you home anymore.

Hover Quantity over the column and the column is highlighted. Release and you’re all set. Now do the same for discount.

I’m going to use the page field drop down to make the pivot table more manageable. I’m also going to format the last column for readability. Neither of these steps are necessary, however. Select anywhere in the pivot table and from the PivotTable toolbar, select Formulas > Calculated Field.

In the dialog, build the formula you want from the available fields. If you don’t have Field1 and Field2 in your available fields, then you weren’t trying to screw things up like me. Someday I’ll learn how to get rid of those, but for now I’ll just ignore them.

Now you can see the total for each product, by order.

It’s ugly and the totals don’t work, but what do you want? It’s not Publisher. We’ll learn how to make it pretty in another post (and I’m not kidding when I say we).

There were a lot of comments that people couldn't get parameters into their pivot tables. I didn't save the workbook with which I made that post, but I was able to recreate it. You can download it:

Download PivotTableParameters.zip

I didn't have any trouble recreating it, other than changing the Sheet2 reference in the code to Sheet1. I'm sure I'm just blowing by a step and not explaining it. Hopefully looking at the workbook will shed some light on why others couldn't get it to work.

Note that the external data that my pivot table points to will not likely be in the same path on your machine. From the Immediate Window, my stuff looks like this:

You'll have to change those paths to get it to work on your machine. You may, however, not need it to work, but just seeing the differences between this workbook and yours may be all you need.

Pivot tables that are based on external data are somewhat limited compared to normal external data queries. One limitation is that you can't (read: it's difficult) to create parameters. At least there's no way to do it in the user interface that I've seen. This post will show you how to modify the CommandText property of the PivotCache object to create a parameter.

Note that you can simply create a normal external data table and base your pivot table off that. That you should give you all the flexibility you need, but it's not as neat and clean. Also, I believe you have to perform two refreshes in that circumstance: one for the external data and one for the pivot table.

When you create the external data pivot table, include criteria to limit one of the fields. This builds the WHERE clause of the SQL statement and makes it far easier to convert to a parameter query. For this example, I'm using the Invoices table from Northwind.mdb and pivoting the ExtendedPrice on the PostalCode.

This produces a pivot table that shows how much we've invoiced for this particular zip code. I picked a zip code that was in the database, but it's just a placeholder for now.

The SQL statement behind this pivot table is stored as a property of the PivotCache object. Here's what that property looks like via the Immediate Window:

That property is just a string, so if you know what a parameter looks like, you can manipulate that string. Here's how I might do it in a macro:

I'm replacing my placeholder zip code, 05022, with a question mark. The question mark is interpreted by MSQuery as a parameter. Once I run this sub, the pivot table tries to refresh, and I get this

Now that the question mark is the SQL statement, every time you refresh the pivot table, it will ask you for a value. What I can't seem to do is manipulate the parameter to, say, change the prompt or base it on a cell. I know all those features are built in to the class, MS just didn't expose them in this situation (i.e. they didn't create a Parameters property of the PivotCache object like they did with QueryTable object). Consarnit!

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.

Pivot tables can be used to summarize data in interesting ways. In this example, I start with selected data from the Invoices table of Northwind.mdb. The table contains 2,156 rows, each representing one invoice. The selected data is the Customer ID, City, and Extended Price. I want to see the sales by City.

Select a cell in the table and choose PivotTable and PivotChart Report... from the Data menu

Step1 allows you to select where to get the data and what kind of report to produce. We'll be using an Excel table and producing a Pivot Table.

With a cell from the table selected, Excel will guess what the whole table is. You need to have a header row for your data, the value of which you will see in later steps. If Excel doesn't guess correctly, you can change the range in this step. You can also select the entire range before starting the wizard to help Excel guess correctly.

The final step of the wizard allows you to define where the table will be created, either on an existing sheet or a new sheet. For this example, I'll choose New Sheet.

This step also provides a Layout button and an Options button. You can define the rows and columns of your table using the Layout button or just click finish and define them on the worksheet. I'll choose to define them directly on the new table as opposed to using the Layout button. By clicking finish, Excel creates the table and displays the skeleton.

The Pivot Table toolbar shows all the fields from the Excel list, from which you can define the layout of the table. The three fields Customer ID, City, and Extended Price are shown as buttons on the toolbar.

To define the layout, drag the City button to the area that says "Drop Row Fields Here" and drag the Extended Price button to the "Drop Data Items Here" area.

Excel guesses what you want to do with the Data Items. In this case, since Extended Price is a number, it guesses that you want to Sum them and creates the Sum of Extended Price data item. If you don't like Excel's guess, you can change it by right clicking on Sum of Extended Price and choosing Field Settings.

Change the Summarize By listbox to Count

to see the number of invoices by city instead of the total sales.

That's the basics of creating a simple pivot table. There are a lot more options and much more that you can do with pivot tables, which I will cover in future posts.