|   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 fieldyou 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 Summarize by : option to a function of your choicee.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 . | |
Figure 4-4. PivotTable Field and PivotTable Layout dialogs  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. To start, select Tools Macro Visual Basic Editor (Alt/Option-F11) and then select Insert Module. Enter the following code: 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. Before running this code, select the heading of your list and ensure that your list contains no blank cells. | | Sorting your list will remove blank cells quickly. | |
The code will automatically create a named range of your list, called Items. It will then create the PivotTable based on this named range on a new worksheet. The next time you have a long list of data, you can simply select its heading and run this macro. All the PivotTable setup work will be done in the blink of an eye. | 