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. Two of the most commonly asked questions in Excel concern how to get a count of all items in a list, and how to create a list of unique items from a list that contains many duplicates. In this section, 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 you want to know how many items are on the list, as well as generate a list of unique items. Select cell A1 (your heading) and then select Data → PivotTable and PivotChart Report (or Data → PivotTable Report on Macs) to start the PivotTable Wizard.
Make sure that either Microsoft Excel List or Database is selected, or 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. 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.) 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. If it is highlighted, click the Next button. Otherwise, use your mouse to select the range. Click the Layout button and drag to the Data area what will be your only field—you should see your title as it appears in cell A1 floating about. Drag the field again, this time into the Row area. Your screen should look something like Figure 4-4. Click OK.
At this stage, if you want you can double-click the Field button in
the Data area (this is labeled Count of Names in Figure 4-3) and change the
by: option to a function of your
choice—e.g., Sum, Average, etc. Excel will by default use the
COUNT function if it's working
with text and use the
SUM function if
it's working with numbers.
Finally, select New Worksheet as the destination of your PivotTable Report and click the Finish button. You should see your PivotTable on a new worksheet containing the unique items from your list along with a count of how many times each item (name) appears in your list.
What if you want to have a macro perform all those steps for you, creating a PivotTable from any column you feed it? If you simply record a macro, you'll find it often works only if your data has the same heading. To avoid this, you can create a simple macro stored in your workbook or in your personal macro workbook (described in Chapter 7) that you can use to create a PivotTable on any list of items. This requires that you write some generic VBA code and enter it into a standard module in your personal macro workbook or in any other workbook.
Sub GetCount( ) Dim Pt As PivotTable Dim strField As String strField = Selection.Cells(1,1).Text Range(Selection, Selection.End(xlDown)).Name = "Items" ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, _ SourceData:="=Items").CreatePivotTable TableDestination:="", _ TableName:="ItemList" Set Pt = ActiveSheet.PivotTables("ItemList") ActiveSheet.PivotTableWizard TableDestination:=Cells(3, 1) Pt.AddFields RowFields:=strField Pt.PivotFields(strField).Orientation = xlDataField End Sub
To return to Excel, close the Script window, or press Alt/
-Q, and save your workbook.