Excel Hacks, 2nd Edition

Chapter 4. Hacking PivotTables

Hacks 50–54

PivotTables are one of Excel’s most powerful attractions, though many people don’t know what they do. PivotTables display and extract a variety of information from a table of data that resides within either Microsoft Excel or another compatible database type. PivotTables are frequently used to extract statistical information from raw data. You can drag around the different fields within a PivotTable to view its data from different perspectives.

Tip

The raw data for a PivotTable must be laid out in a classic table format. Row 1 of the table must be headings, with related data directly underneath. The data should not contain blank columns or blank rows. Even if you aren’t planning to use PivotTables, keeping your raw data in this format makes it possible for other people to analyze your data with PivotTables.

If you have not yet delved into the world of PivotTables, you should consider doing so. As a starting point, visit http://www.ozgrid.com/Excel/default.htm and work your way through a free online tutorial for Excel PivotTables. To learn more about the benefits of PivotTables as well as how you can create hacks that make PivotTables even more flexible and powerful, read on.

PivotTables: A Hack in Themselves

PivotTables are one of the wildest but most powerful features of Excel, an ingenious hack themselves that may take some experimentation to figure out.

We use PivotTables a lot when we develop spreadsheets for our clients. Once a client sees a PivotTable, they nearly always ask whether they can create one themselves. Although anyone can create a PivotTable, unfortunately many people tend to shy away from them, as they see them as too complex. Indeed, when you first use a PivotTable, the process can seem a bit daunting. Some persistence is definitely necessary.

You’ll find that persistence will pay off once you experience the best feature of PivotTables: their ability to be manipulated using trial and error and immediately show the result of this manipulation. If the result is not what you expect, you can use Excel’s Undo feature and have another go! Whatever you do, you are not changing the structure of your original table in any way, so you can do no harm.

Why Are They Called PivotTables?

PivotTables allow you to pivot data using drag-and-drop techniques and receive results immediately. PivotTables are interactive; once the table is complete, you can easily see how your information will be affected when you move (or pivot) your data. This will become patently clear once you give PivotTables a try.

Even for experienced PivotTable developers, an element of trial and error is always involved in producing desired results. You will find yourself pivoting your table a lot!

What Are PivotTables Good For?

PivotTables can produce summary information from a table of information. Imagine you have a table of data that contains names, addresses, ages, occupations, phone numbers, and zip codes. With a PivotTable, you very easily and quickly can find out:

• How many people have the same name

• How many people share the same zip code

• How many people have the same occupation

You also can receive such information as:

• A list of people with the same occupation

• A list of addresses with the same zip code

If your data needs slicing, dicing, and reporting, PivotTables will be a critical part of your toolkit.

Why Use PivotTables When Spreadsheets Already Offer So Much Analysis Capability?

Perhaps the biggest advantage to using PivotTables is the fact that you can generate and extract meaningful information from a large table of data within a matter of minutes and without using up a lot of computer memory. In many cases, you could get the same results from a table of data by using Excel’s built-in functions, but that would take more time and use far more memory.

Another advantage to using PivotTables is that if you want some new information, you can simply drag-and-drop (pivot). In addition, you can opt to have your information update each time you open the workbook or you can right-click and select the Refresh option to refresh at will.

PivotCharts Extend PivotTables

Microsoft introduced PivotCharts in Excel 2000. In an instant, you can create interactive charts that were previously impossible without using either VBA or Excel Controls. PivotCharts are created from PivotTables, so a PivotChart shows graphically a representation of your PivotTable. PivotCharts work very similarly to the standard Excel charts, and most of the features are available. The beauty of PivotCharts and PivotTables is that they are interactive with each other—change something in one, and it will also be reflected in the other.

Tip

PivotCharts are not available in Excel for the Macintosh.

Creating Tables and Lists for Use in PivotTables

When you create a PivotTable, you must organize the dataset you’re using in a table or in a list. As the PivotTable will base all its data on this table or list, it is vital that you set up your tables and lists in a uniform way.

In this context a table is a multi-columned set of data with data laid out directly below the appropriate headings. A list (only one column, also with a heading) is often referred to in the context of a table as well. The best practices that apply to setting up a list will help you greatly when you need to apply a PivotTable to your data.

When you extract data via the use of lookup or database functions, you can be a little less stringent in how you set up the table or list. This is because you can always compensate with the aid of a function and probably still get your result. Nonetheless, it’s still easiest to set up the list or table as neatly as possible. Excel’s built-in features assume a lot about the layout and setup up of your data. Although they offer a degree of flexibility, more often than not you will find it easier to adhere to the following guidelines when setting up your table or list:

• Headings are required, as a PivotTable uses them for field names. Headings should always appear in the row directly above the data. Also, never leave a blank row between the data and the headings. Furthermore, make the headings distinct in some way; for instance, boldface them.

• Leave at least three blank rows above the headings. You can use these for formulas, critical data, etc. You can hide the rows if you want.

• If you have more than one list or table on the same worksheet, leave at least one blank column between each list or table. This will help Excel recognize them as separate entities. However, if the lists and tables are related to each other, combine them into one large table.

• Avoid blank cells within your data. Instead of leaving blank cells for the same data in a column, repeat the data as many times as needed.

• Sort your list or data, preferably by the leftmost column. This will make the data easier to read and interpret.

If you follow these guidelines as closely as possible, using PivotTables will be a relatively easy task.

Figure 4-1 shows a well-laid-out table of data and a PivotTable in progress.

Figure 4-1. PivotTable generated from a well-laid out table of data

Note that many of the same dates are repeated in the Date column. In front of this data is the Pivot Table Field List, showing the field names (or headings) and the optional Areas you can drag them to.

Tip

In pre-2007 versions of Excel, the Layout step for the data shows the optional Page, Row, and Column fields, as well as the mandatory Data field.

PivotTable Creation

When you create a PivotTable (by going to the Insert tab and selecting Pivot Table; pre-2007, select Data → Pivot Table Report), a dialog pops up asking you to select either your table or range, or select an external data source. If your table was set up correctly (i.e., headings defined in some way and no blank rows/columns/cells) and you are clicked somewhere inside your data, your range will be selected automatically. You will then be asked if you want your PivotTable created in a new Worksheet or on the Existing Worksheet.

Tip

This is true for a lot of Excel’s functions and analysis tools. Your range will be automatically selected if your data is set up correctly—i.e., headings defined in some way and no blank rows/columns/cells.

Users of pre-2007 versions will need to go through the more cumbersome PivotTable and PivotChart Wizard. This Wizard guides you through the creation of a PivotTable using a four-step process, in which you tell Excel:

1. How the data is set up and whether to create an associated PivotChart (if PivotCharts are available in that version of Excel)

2. Where the data is stored—e.g., a range in the same workbook, a database, another workbook, etc.

3. Which column of data is going into which field: the optional Page, Row, and Column fields, as well as the mandatory Data field

4. Where to put your PivotTable (i.e., in a new worksheet or in an existing one)

You also can take many side steps along the way to manipulate the PivotTable, but most users find it easier to do this after telling Excel where to put it.

Now that you know more about PivotTables and what they do, it’s time to explore some handy hacks that can make this feature even more powerful.

Share PivotTables but Not Their Data

Create a snapshot of your PivotTable that no longer needs the underlying data structures.

You might need to send PivotTables for others to view, but for whatever reason you cannot send the underlying data associated with them. Perhaps you want others to see only certain data for confidentiality reasons, for instance. If this is the case, you can create a static copy of the PivotTable and enable the recipient to see only what he needs to see. Best of all, the file size of the static copy will be only a small percentage of the original file size.

Assuming you have a PivotTable in a workbook, all you need to do is select the entire PivotTable, copy it, right-click on a clean sheet, and select Paste Special… → Values. Now you can move this worksheet to another workbook or perhaps use it as is.

The one drawback to this method is that Excel does not paste the PivotTable’s formats along with the values. This can make the static copy harder to read and perhaps less impressive. If you want to include the formatting as well, you can take a static picture (as opposed to a static copy) of your PivotTable and paste this onto a clean worksheet. This will give you a full-color, formatted snapshot of the original PivotTable to which you can apply any type of formatting you want, without having to worry about the formatting being lost when you refresh the original PivotTable. This is because the full-color, formatted snapshot is not linked in any way to the original PivotTable.

To create a static picture, format the PivotTable the way you want it and then highlight the Pivot Table. Select Home → Clipboard → Paste → As Picture → Copy Picture, and make the selections shown in Figure 4-2 in the Copy Picture dialog box that pops up. Then, click OK.

Tip

Pre-2007, hold down the Shift key, select Edit → Copy Picture, click anywhere outside the PivotTable, and select Edit → Paste.

You will end up with a fully colored and formatted snapshot of your PivotTable, as shown in Figure 4-3. This can be very handy, especially if you have to email your PivotTable to other people for viewing. They will have the information they need, including all relevant formatting, but the file size will be small and they won’t be able to manipulate your data. Also, they will be able to see only what you want them to see.

Figure 4-2. Copy Picture dialog in action

You also can use this picture-taking method on a range of cells. You can follow the preceding steps, or you can use the little-noticed Camera icon.

To use this latter method, press the Office button, select Excel Options → Customize, and choose "Commands Not in the Ribbon” from the Choose Commands From: box. Locate the camera, click it, press Add to add it to your Quick Access toolbar, and then click OK.

Tip

In pre-2007 versions, select View → Toolbars → Customize…. From the Customize dialog, click the Commands tab; from the Categories box, select Tools; and from the Commands box on the right side, scroll down until you see Camera. Left-click and drag-and-drop this icon onto your toolbar where you want it to be displayed.

Figure 4-3. Original PivotTable contrasted with a picture of the PivotTable

Now select a range of cells, click the Camera icon, and then click anywhere on the spreadsheet, and you will have a linked picture of the range you just took a picture of. Whatever data or formatting you applied to the original range will automatically be reflected in the picture of the range.

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 how to create a list of unique items from a list that contains many duplicates. In this hack, 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 how many 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 New Worksheet as the placement for the Pivot table, and click OK. Your screen should look something like Figure 4-4.

   

   Figure 4-4. PivotTable Field and PivotTable Layout dialogs

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 PivotTable 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 New Worksheet as the destination of your PivotTable Report and click the Finish button.

Tip

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 new worksheet containing the unique items from your list along with a count of how many times each item (name) appears in your list.

Save Time with a Macro

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), and use it 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, right-click on the sheet tab that contains the data table and select View Code (on Mac, Alt/Option-F11; in pre-2007 versions, Tools → Macro → Visual Basic Editor). Then, choose Insert → Module and 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.

Tip

Sorting your list will remove blank cells quickly, because blank cells are always sorted to the bottom of a list.

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.

Move PivotTable Grand Totals

One of the most annoying things about PivotTables is that the Grand Total that summarizes your data always ends up at the bottom of the table, meaning you have to scroll down just to see the figures. Move your Grand Total up to the top where it’s easier to find.

Although PivotTables are a great way to summarize data and extract meaningful information, there is no built-in option to have the Grand Total float to the top for a quick bird’s-eye view.

Before we describe a very generic method to move the Grand Total to the top, we’ll explain how you can accomplish this with the GETPIVOTDATA function, which is designed specifically to extract data from a PivotTable.

You can use the function like this:

	=GETPIVOTDATA("Sum of Amount",$B$5)

or like this:

	=GETPIVOTDATA("Amount",$B$5)

Either function will extract the data and will track the Grand Total as it moves up, down, left, or right. We used the cell address $B$5, but as long as you use any cell within the PivotTable, you always will pick up the total.

The first function uses the Sum of Amount field, while the second one uses the Number Sold field. If your PivotTable has the Amount field in the Values area (pre-2007, in the Data area), you need to name the field Amount. If, however, the Amount field is being used two or more times in the Values area, you must specify the name you gave it, or the name you accepted by default, as shown in Figure 4-5.

You can double-click these fields to change them. This issue can become confusing if you are not up to speed with PivotTables. Luckily in Excel 2002 and later, the process is much easier, as you can have a cell fill in the arguments and give the correct syntax by using the mouse pointer. In any cell, type =(an equals sign) and then use your mouse pointer to click in the cell currently housing the Grand Total. Excel will automatically fill in the arguments for you.

Warning

Unfortunately, if you use the Function Wizard, or first type =GETPIVOTDATA() and then click in the cell currently housing the Grand Total, Excel makes a mess by trying to nest another GETPIVOTDATA function within that cell.

Figure 4-5. The Amount field used twice and named Sum of Amount in one case and Number Sold in the other

Probably the easiest, if least sophisticated, way to extract the Grand Total is to use the following function:

	=MAX(PivGTCol)

where the column currently housing the Grand Total is named PivGTCol.

You also can use the LARGE and SMALL functions to extract from a PivotTable a host of figures according to their size. The following formula, for instance, extracts the second largest figure from a PivotTable:

	=LARGE(PivGTCol,2)

You can add some extra rows immediately above the start of the PivotTable and place these formulas there so that you can see this type of information instantly, without having to scroll to the bottom of your PivotTable.

Efficiently Pivot Another Workbook’s Data

Use data residing in another workbook as the source for your PivotTable.

When creating a PivotTable in Excel, you have lots of options for your data source. By far the easiest and most powerful approach is to use data that resides within the same workbook. Unfortunately, for whatever reason, this is not always possible or feasible. Perhaps the data that resides in another workbook is entered daily, for instance, and the users entering the data should not see the PivotTable.

Using a dynamic named range will greatly decrease the refresh time needed for your PivotTable to update. As you cannot reference a dynamic named range from another workbook, this also means you prevented the PivotTable from referencing perhaps thousands of blank rows and causing the file size to increase substantially. This way, you can pull in data from another workbook, and then base your PivotTable on the data in the same workbook rather than referencing it externally. Let’s walk through the steps.

1. In the workbook that will contain your PivotTable, insert a new worksheet and call it Data.

2. Open the workbook containing the data to be referenced, and ensure that the worksheet containing the data is the active sheet. In any spare cell on this worksheet, enter this formula:

   	=IF(A1="","",A1)

   where A1 is the very first heading of your data table.

3. Select cell A1. Then cut it, activate your original workbook, and paste cell A1 in cell A1 on the Data sheet. This will give you the reference to the other workbook.

4. Copy this cell across as many columns as there are headings in your data source.

5. Select Formulas → Defined Names → Define Name on (pre-2007, Insert → Name → Define).

6. Type PivotDatain the Names: field and type the following in the “Refers to:” box:

   	=OFFSET($A$1,0,0,COUNTA($A:$A),COUNTA($1:$1))

7. Click OK.

8. Next, to insert some code that will run each time the workbook is opened, right-click on the sheet tab and select View Code.

9. Then, double-click This Workbook (pre-2007, go to the Excel icon located at the top left corner of the screen next to the File menu option, right–click, and select View Code) and enter the following code:

   	Private Sub Workbook_Open()
   	 With Worksheets("Data")
   	 .Range("2:1000").Clear
   	 .Range("1:1").AutoFill .Range("1:1000")
   	 .Range("2:1000") = .Range("2:1000").Value
   	 End With
   	End Sub

   Tip

   Right-clicking on the Excel icon isn’t available in Excel 2007 or on a Mac. On a Mac, you’ll have to open the VBE by pressing Option-F11 or by selecting Tools → Macro → Visual Basic Editor. Then, Ctrl-click This Workbook in the Projects window.

10. Finally, to return to Excel, close the script window or press Alt/⌘-Q, then save your workbook.

The code in Step 9 includes only 1,000 rows of data. The number you specify in the .Range statement should always be greater than the number of rows you believe you will need. In other words, if your table in the other workbook contains 500 rows, specify a few hundred more than that to accommodate any growth in the original table.

Warning

Avoid using an extremely high row number (like 10,000, unless you actually have that much data), as this will greatly impact how quickly the code runs and the data updates.

At this point you are ready to check your macro. Save the workbook, close it, and then reopen it, making certain that you enable macros. The code you added will fire automatically and will copy the formulas in row 1 on the Data sheet, then automatically convert all but row 1 into values only. This will leave you with a copy of your original data source, which will update each time you open the workbook.

If you wanted to, you could hide this sheet by right-clicking and selecting Hide from the shortcut menu, or by using the method described in “Hide Worksheets So That They Cannot Be Unhidden” [Hide Worksheets So That They Cannot Be Unhidden].

Now, to base a PivotTable on this dynamic named range, select anywhere within the PivotTable, select Pivot Table from the Insert tab, and type =PivotData under Select a Table or Range.

Tip

In pre-2007 versions, select the Wizard option from the PivotTable toolbar. Click the Back button until you reach Step 1 of the Wizard. Select the first option, Microsoft Excel List or Database, click Next, and in Step 2, type =PivotData (the name of the dynamic named range). Then click Finish.

You will not experience the lag that often occurs when a PivotTable is referencing an external data source because now the data itself is stored within the same workbook. As an added bonus, because you can use a dynamic named range, the PivotTable is dynamic without having to reference heaps of blank rows, and the file is kept to a manageable size.