Automate PivotTable Creation

The steps you need to follow to create a PivotTable require some effort, and that effort often is redundant. With a small bit of VBA, you can create simple PivotTables automatically.

PivotTables are a very clever and potent feature to use on data that is stored in either a list or a table. Unfortunately, the mere thought of creating a PivotTable is enough to prevent some people from even experimenting with them. Although some PivotTable setups can get very complicated, you can create most PivotTables easily and quickly in most situations. For example, two of the most commonly asked questions in Excel concern how to get a count of all items in a list, and howto create a list of unique items from a list that contains many duplicates. In this tutorial, we'll show you how to create a PivotTable quickly and easily that accomplishes these tasks.

Assume you have a long list of names in column A, with cell A1 as your heading, and the heading of Column A is First Name. To find out howmany items are on the list, as well as generate a list of unique items, follow these steps:

1. Select cell A1 (your heading) and select Insert → Pivot Table (pre-2007, Data → Pivot Table Report).
2. Ensure that you have selected NewWorksheet as the placement for the Pivot table, and click OK.
3. Now drag the First Name field to the Row labels area.
4. Click on the First Name field again and drag it to the Values area.
5. Click OK.

To create a PivotTable in a pre-2007 version of Excel, follow these steps:

1. For versions of Excel prior to 2007, go to Data → PivotTable and PivotChart Report (or Data → PivotTable Report on Macs) to start the Pivot- Table Wizard.
2. Before you start, make sure that you have selected a single cell within your data. This will allow Excel to automatically detect the underlying data it is to use next.
3. If you're using a Windows PC, select PivotTable under "What kind of report do you want to create?" (This question isn't asked on Macintoshes.)
4. Click the Next button. The PivotTable Wizard should automatically have picked up the correct range for your data in column A and will highlight it in your sheet.
5. If it is highlighted, click the Next button. Otherwise, use your mouse to select the range before clicking the Next button.
6. Click the Layout button and drag to the Data area the Names field.
7. Drag the Names field again, this time into the Row area.
8. Finally, select NewWorksheet as the destination of your PivotTable Report and click the Finish button.

At this stage, you could additionally double-click the button called Count of Product and change the "Summarize value field by:" option to a function of your choice-e.g., Sum, Average, etc. By default, Excel will use the COUNT function if it's working with text and use the SUM function if it's working with numbers.

You should see your PivotTable on a neww orksheet containing the unique items from your list along with a count of how many times each item (name) appears in your list.