Excel 2007 PivotTables Recipes A Problem-Solution Approach

are declared in the code. Using this setting is good practice, as it can help you prevent errors when you write or edit code. You can type Option Explicit at the top of each module, or you can change a setting, so it automatically appears in new modules. In the VBE, click the Tools menu, and then click Options. On the Editor tab, add a check mark to Require Variable Declaration. 3. Below the General Declarations section, add the following code, which includes the sheet names where the other pivot tables are located, and the name of the report filter: Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable) Dim wsOther1 As Worksheet Dim wsOther2 As Worksheet Dim pt As PivotTable Dim strFilter As String On Error GoTo err_Handler Set wsOther1 = Worksheets("Region Pivot") 'second PT sheet name Set wsOther2 = Worksheets("CitySales") 'third PT sheet name strFilter = "Product" 'Report Filter name Application.EnableEvents = False If UCase(Target.PivotFields(strFilter).CurrentPage) _ UCase(mstrFilter) Then mstrFilter= Target.PivotFields(strFilter).CurrentPage For Each pt In wsOther1.PivotTables pt.PageFields(strFilter).CurrentPage = mstrFilter Next pt For Each pt In wsOther2.PivotTables pt.PageFields(strFilter).CurrentPage = mstrFilter Next pt End If CHAPTER 11 ■ PROGRAMMING A PIVOT TABLE 219 err_Handler: Application.EnableEvents = True End Sub To run the event code, change the selection in the OrderDates pivot table’s report filter. How It Works A variable within a procedure is stored only as long as the procedure that declared it is run- ning. A module-level variable is used in this example, because it can store the current report filter selection while the workbook is open. When the pivot table is updated, the variable named mstrFilter is compared to the current selection in the Product Report Filter. If they’re different, the new report filter value is stored in the variable, and the report filter in each related pivot table is changed to match the report filter in the main pivot table. 11.8. Removing Filters in a Pivot Field Problem When you want to remove a filter from a pivot table field, you can manually check the Show All box to show all items in that pivot table field. When you record this step, the code shows a list of all the items in the pivot field, instead of using a Show All command. When you run the code later, it can be very slow, if the list of items is long, such as in an OrderDate field. Also, if you add new items to the field and run the macro, the new items aren’t made visible, because the macro contains a list of the original items only. This problem is based on the Filters.xlsm sample workbook. Solution Instead of using the recorded code, with its long list of items, you can use the ClearManualFilter method. For example, the following code shows all items, in all visible fields, in all tables on the active sheet. Store the code on a regular module. Sub ClearFilters () Dim pt As PivotTable Dim pf As PivotField Dim ws As Worksheet On Error GoTo err_Handler Set ws = ActiveSheet Application.ScreenUpdating = False Application.DisplayAlerts = False CHAPTER 11 ■ PROGRAMMING A PIVOT TABLE220 For Each pt In ws.PivotTables For Each pf In pt.VisibleFields On Error Resume Next pf.ClearManualFilter Next pf Next pt exit_Handler: Application.ScreenUpdating = True Exit Sub err_Handler: MsgBox Err.Number & ": " & Err.Description GoTo exit_Handler Set pf = Nothing Set pt = Nothing Set ws = Nothing End Sub To run the code, use a method described in Section 11.1. The code refers to ActiveSheet, so you can run the code on any sheet that contains a pivot table. How It Works The ClearFilters code uses For Each...Next loops to clear the manual filters in all visible fields, in all pivot tables on the active worksheet. ■Tip To show only column fields, change pt.VisibleFields to pt.ColumnFields. To show only the row fields, change pt.VisibleFields to pt.RowFields, and to show report filter fields, change pt.RowFields to pt.PageFields. The ClearManualFilter method only clears the manual filters applied to the pivot field. You apply a manual filter by using the check boxes in the filter list, or by right-clicking an item, clicking Filter, and then clicking Hide Selected Items. In addition to the manual filters, date, value, or label filters may have been applied to the row or column fields. To clear value or Top 10 filters, you can use the following method: pf.ClearValueFilters To clear label or date filters, you can use the following method: pf.ClearLabelFilters CHAPTER 11 ■ PROGRAMMING A PIVOT TABLE 221 To clear all filters in the pivot table, you can use the following method: For Each pt In ws.PivotTables pt.ClearAllFilters Next pt ■Tip While recording a macro, you can record the ClearAllFilters method. Right-click a filtered field, click Filter, and then click Clear Filter from FieldName. 11.9. Changing Content in the Values Area Problem You discovered that one of the totals in your OrderDates report is incorrect, but you haven’t received the corrected source data yet. You need to print the totals for the sales manager, who’s leaving for the airport in 15 minutes. As a temporary fix, you’d like to change the New York total and the Grand Total in the Values area of the pivot table, so you can print the report. However, when you try to type over a value, you see the error message “Cannot change this part of a PivotTable report.” This problem is based on the Change.xlsm sample workbook. Solution The best solution is to add a row in the source data, to adjust the New York total, with a com- ment to explain the entry. Then, refresh the pivot table, to see the updated total. After the corrected source data is received, another row can be added, to reverse the temporary adjust- ment. This solution would leave an audit trail, explaining the changes. However, if you change a PivotTable setting programmatically, you can make temporary changes to the PivotTable values. Store the code in a regular code module. Sub ChangePTValues() Dim pt As PivotTable Set pt = ActiveSheet.PivotTables(1) pt.EnableDataValueEditing = True Set pt = Nothing End Sub ■Note When the pivot table is refreshed, the manually entered values are overwritten. To run the code, use a method described in Section 11.1. The code refers to ActiveSheet, so you can run the code on any sheet that contains a pivot table. CHAPTER 11 ■ PROGRAMMING A PIVOT TABLE222 How It Works The EnableDataValueEditing property can only be set programmatically, and it allows tempo- rary changes to the pivot table data area cells. ■Caution If you rely on pivot tables to summarize your data, you should be aware that the data can be changed, accidentally or maliciously, and use other methods to verify the data, as a safeguard. To prevent accidental changes to the pivot table, you should create another macro that turns this setting to False, and run that macro as soon as you finish making changes to the values. Sub BlockPTValues() Dim pt As PivotTable Set pt = ActiveSheet.PivotTables(1) pt.EnableDataValueEditing = False Set pt = Nothing End Sub 11.10. Identifying a Pivot Table’s Pivot Cache Problem You’re working on next year’s budget, and your workbook contains sales data and a forecast for the upcoming year. Several pivot tables are based on similar Excel Tables, and you want to identify which pivot cache each pivot table uses. This problem is based on the Cache.xlsm sample workbook. Solution To determine which pivot cache a pivot table uses, you can run the following code to test the pivot table’s CacheIndex property and view the result in a message box. Store the code on a regular code module. Sub ViewCacheIndex() On Error GoTo err_Handler MsgBox "PivotCache: " & ActiveCell.PivotTable.CacheIndex Exit Sub err_Handler: MsgBox "Active cell is not in a pivot table" End Sub Select a cell in a pivot table, and then run the code, using one of the methods described in Section 11.1. A message box displays the CacheIndex property for the active cell’s pivot table. If the active cell is not in a pivot table, an error message is displayed. CHAPTER 11 ■ PROGRAMMING A PIVOT TABLE 223 How It Works When pivot caches are created, they are added to the workbook’s PivotCaches collection and given an index number. This number is displayed in the macro’s message box. If a pivot table is based on the same source data as an existing pivot table, it uses the same pivot cache. 11.11. Changing a Pivot Table’s Pivot Cache Problem You used the code from Section 11.10 to identify the pivot cache used by each pivot table in your workbook. You want to change the pivot cache of the Category pivot table, so it uses the same cache as the StoreTotals pivot table. This problem is based on the Cache.xlsm sample workbook. Solution To change the pivot cache, you can set the pivot table’s CacheIndex property. The following code sets the pivot table for the active cell to use pivot cache number 2. Store the code on a regular code module. Sub SetCache2Index() On Error GoTo err_Handler ActiveCell.PivotTable.CacheIndex = 2 Exit Sub err_Handler: MsgBox "Cache index could not be changed" End Sub To run the code, use a method described in Section 11.1. The code refers to ActiveCell, so you can run the code on any sheet that contains a pivot table. How It Works The SetCache2Index code sets the CacheIndex property for the active cell’s pivot table to 2. If a pivot table cell is not selected, or if there is no CacheIndex 2, an error message is displayed. Instead of setting the CacheIndex property to a specific number, you can obtain an index number in the code, and use that number as the setting. For example: Sub SetCacheIndex() Dim i As Integer i = Worksheets("StoreTotals").PivotTables(1).CacheIndex On Error GoTo err_Handler ActiveCell.PivotTable.CacheIndex = i Exit Sub err_Handler: MsgBox "Cache index could not be changed" End Sub CHAPTER 11 ■ PROGRAMMING A PIVOT TABLE224 11.12. Refreshing a Pivot Table on a Protected Sheet Problem You protected the StoreTotals worksheet, so no one is able to change the layout of the pivot table. Now, you want to refresh the StoreTotals pivot table, but the Refresh Data button on the PivotTable toolbar is disabled. This problem is based on the Refresh.xlsm sample workbook. Solution You can record a macro, modify it slightly, and then run that macro when you need to refresh the pivot table. Before you begin, protect the StoreTotals worksheet, with the password pwd. 1. Record a macro as you unprotect the StoreTotals sheet, refresh the pivot table, and then protect the worksheet. 2. View the recorded code in the VBE. For example, your code may look similar to the following: Sub RefreshPivot() ActiveSheet.Unprotect Range("B1").Select ActiveSheet.PivotTables("StorePT").PivotCache.Refresh ActiveSheet.Protect DrawingObjects:=True, _ Contents:=True, Scenarios:=True End Sub 3. In the recorded code, you can add a password to the Unprotect and Protect lines. For example, if your password is pwd, the revised code would be as follows: Sub RefreshPivot() ActiveSheet.Unprotect Password:="pwd" Range("B1").Select ActiveSheet.PivotTables("StorePT").PivotCache.Refresh ActiveSheet.Protect Password:="pwd", _ DrawingObjects:=True, Contents:=True, Scenarios:=True End Sub 4. Run the macro using one of the methods shown in Section 11.1. How It Works The RefreshPivot macro stores your password, and then uses it when unprotecting and pro- tecting the worksheet. While the worksheet is unprotected, it refreshes the pivot table. CHAPTER 11 ■ PROGRAMMING A PIVOT TABLE 225 Notes If you add your password to the macro, it is visible to anyone who can open your workbook project in the VBE. For information on protecting your code, see the article “Locking Your Solution’s VBA Project,” at Aa165442(office.10).aspx. Although the article refers to Excel 2000, it is still applicable to a VBA project in Excel 2007. 11.13. Refreshing Automatically When Source Data Changes Problem You frequently update the source data that’s in an Excel Table in your workbook, but you for- get to update the pivot table that’s based on it. Hours later, you realize you printed several reports, all of which contain outdated information. To avoid wasting time and paper, you want the pivot table to automatically refresh when changes are made in the Excel Table on which the pivot table is based. This problem is based on the Update.xlsm sample workbook. Solution You can use an event procedure to automatically update the pivot table if the source data changes. In this example, the source data is on the Forecast sheet, and the pivot table is on the Product sheet: 1. Add the following code to the Forecast worksheet module: Private Sub Worksheet_Change(ByVal Target As Range) Worksheets("Product").PivotTables(1).PivotCache.Refresh End Sub 2. If you protected the Product worksheet, include code to unprotect the worksheet, and then protect it again, as shown in Section 11.12. 3. To run the event code, make a change to the data in the Excel Table. How It Works When a change is made on the Forecast worksheet, that sheet’s Change event is triggered. In the Event code, the pivot cache for the Product sheet’s pivot table is refreshed. 11.14. Setting a Minimum Width for Data Bars Problem You created a pivot table to show the quantity of snacks sold each month, and you added con- ditional formatting, using the data bars option. Although zero were sold in March and only one sold in April, the data bars for those months are too wide, in proportion to the other bars. You changed the setting for the Lowest Value, as described in Section 4.17, but the bars are still too wide. You’d like to fix the data bars, so the bars that represent zero and one are barely visi- ble. This problem is based on the DataBars.xlsm sample file. CHAPTER 11 ■ PROGRAMMING A PIVOT TABLE226 Solution Although you can adjust the Lowest Value setting in the Edit Formatting Rule dialog box, you can’t change the size of the smallest bar, which will be approximately 10 percent the width of the bar for the highest value. However, the minimum width can be adjusted programmatically. For example, you can change the minimum width to 1 percent, and the bars for the zero and one will be much nar- rower (see Figure 11-8). Figure 11-8. The minimum width for data bars is reduced to 1 percent. In the following code, the minimum width of the data bars that start in cell B6 will be changed to 1 percent. Also, the Lowest Value will be set as a Number, with zero as the value. You can manually change this setting in the Edit Formatting Rules dialog box, but it is included here for convenience. Sub DataBarWidth() With Range("B6").FormatConditions(1) .MinPoint.Modify _ NewType:=xlConditionValueNumber, NewValue:=0 .PercentMin = 1 End With End Sub 11.15. Preventing Selection of (All) in a Report Filter Problem You’re sending a copy of your workbook to the regional managers. In the OrderDates pivot table, you want the managers to view the sales totals by selecting an order date. GetPivotData formulas are on the worksheet that will result in an error if (All) is selected, so you want to pre- vent them from choosing (All) in the report filter’s drop-down list. This problem is based on the BlockAll.xlsm sample workbook. CHAPTER 11 ■ PROGRAMMING A PIVOT TABLE 227 Solution You can’t remove the (All) option from the report filter’s drop-down list, but you can use pro- gramming to prevent users from selecting that option. The following code, stored on the OrderDates sheet’s module, can undo the report filter change, if (All) is selected or if multiple items are selected in the filter, and displays a message warning: Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable) Dim pf As PivotField On Error GoTo exit_Handler Application.EnableEvents = False Application.ScreenUpdating = False For Each pf In Target.PageFields If pf.CurrentPage = "(All)" Then Application.Undo MsgBox "Please select a single date." End If Next pf exit_Handler: Set pf = Nothing Application.EnableEvents = True Application.ScreenUpdating = True End Sub To run the code, select (All) from the OrderDate report filter’s drop-down list. How It Works When an item is selected from a report filter’s drop-down list, the PivotTableUpdate event is triggered. If the (All) item was selected, the code undoes the change to the report filter, and displays the previously selected item. 11.16. Disabling Pivot Field Drop-Downs Problem In the workbook you’re sending to regional managers, you selected the manager’s region name from the report filter on the Region Pivot worksheet, and all the stores in their region are in the Row Labels area. To ensure they’re viewing the correct data, you want to prevent the managers from selecting a different item in the report filter or in the Row Labels filters. This problem is based on the Protect.xlsm sample workbook. CHAPTER 11 ■ PROGRAMMING A PIVOT TABLE228 Solution You can use programming to disable many pivot table features. For example, the following macro disables selection in each field in the first pivot table of the active sheet. The drop- down arrows disappear, and users can’t change the displayed items. Store the code in a regular code module. ■Note Instead of PivotFields, you can use VisibleFields, RowFields, ColumnFields, or PageFields. Sub DisableSelection() Dim pt As PivotTable Dim pf As PivotField Set pt = ActiveSheet.PivotTables(1) For Each pf In pt.PivotFields pf.EnableItemSelection = False Next pf Set pf = Nothing Set pt = Nothing End Sub To run the code, use a method described in Section 11.1. The code refers to ActiveSheet, so you can run the code on any sheet that contains a pivot table. How It Works The code sets the EnableItemSelection property to False for each field in the pivot table, even if they aren’t visible in the layout. To reenable selection, create and run a similar macro that sets the EnableItemSelection property to True. 11.17. Preventing Layout Changes in a Pivot Table Problem In the workbook you’re sending to the regional managers, you created a SalesByDate pivot table that’s referenced in GetPivotData formulas on the OrderDates worksheet. You want to prevent the managers from rearranging the SalesByDate pivot table layout. This problem is based on the Protect.xlsm sample workbook. Solution The pivot table has DragTo settings you can change programmatically. For example, the fol- lowing macro prevents dragging fields to any pivot table area or off the pivot table. Store the code in a regular code module. CHAPTER 11 ■ PROGRAMMING A PIVOT TABLE 229 Sub RestrictPTDrag() Dim pt As PivotTable Dim pf As PivotField On Error Resume Next Set pt = ActiveSheet.PivotTables(1) For Each pf In pt.PivotFields With pf .DragToPage = False .DragToRow = False .DragToColumn = False .DragToData = False .DragToHide = False End With Next pf Set pt = Nothing End Sub To run the code, use a method described in Section 11.1. The code refers to ActiveSheet, so you can run the code on any sheet that contains a pivot table. How It Works The code stops users from moving the pivot table fields to a different location in the Pivot- Table Field List. It also prevents adding or removing fields in the pivot table layout. To allow layout changes again, create and run a similar macro that sets the DragTo properties to True. In addition to the security features discussed in this and previous sections, you can pro- grammatically control access to the following features: • PivotTable Field List • Field Settings • Refresh • Ribbon’s PivotTable Tools contextual tabs • Show Details The following macro turns off each of these features. Store the macro on a regular code module. Sub RestrictPTChanges() Dim pt As PivotTable Application.EnableEvents = False Set pt = ActiveSheet.PivotTables(1) CHAPTER 11 ■ PROGRAMMING A PIVOT TABLE230 With pt .EnableWizard = False 'hides Ribbon tabs .EnableDrilldown = False .EnableFieldList = False .EnableFieldDialog = False 'Field Settings .PivotCache.EnableRefresh = False End With Application.EnableEvents = True Set pt = Nothing End Sub To run the code, use a method described in Section 11.1. The code refers to ActiveSheet, so you can run the code on any sheet that contains a pivot table. The RestrictPTChanges macro turns off many features in the first pivot table in the active sheet. To allow use of the features, create and run another macro that changes the settings to True. 11.18. Resetting the Print Area to Include the Entire Pivot Table Problem The OrderDates pivot table is on a worksheet that contains other data, which you don’t want to print. The pivot table frequently changes size, and you have to reset the print area every time you want to print it. This problem is based on the Print.xlsm sample workbook. Solution Use programming to reset the print area automatically before printing. Add the following code to a regular code module: Sub SetPivotPrintArea() Dim ws As Worksheet Dim pt As PivotTable Set ws = ActiveSheet Set pt = ws.PivotTables(1) With ws.PageSetup .PrintTitleRows = "" .PrintTitleColumns = "" .PrintArea = pt.TableRange2.Address End With pt.PrintTitles = True ws.PrintOut Preview:=True Set pt = Nothing Set ws = Nothing End Sub CHAPTER 11 ■ PROGRAMMING A PIVOT TABLE 231 To run the code, use a method described in Section 11.1. The code refers to ActiveSheet, so you can run the code on any sheet that contains a pivot table. How It Works The SetPivotPrintArea macro clears the print titles for the active sheet, sets the print area based on the current layout of the pivot table, turns on the pivot table’s print titles options, and prints the worksheet. In this example, the ws.PrintOut line has Preview set to True, so the worksheet will preview instead of printing. When you finish testing the code, you can change the setting to False, and the sheet will print when the code runs. 11.19. Printing the Pivot Table for Each Report Filter Field Problem You’re preparing for a sales meeting, and you want to print a copy of the pivot table for each item in the Category report filter. This problem is based on the PrintCat.xlsm sample workbook. Solution The following code prints the pivot table once for each item in the Category report filter. Store the code in a regular code module. Sub PrintPivotFilters() Dim pt As PivotTable Dim pf As PivotField Dim pi As PivotItem Dim ws As Worksheet On Error Resume Next Set ws = ActiveSheet Set pt = ws.PivotTables(1) Set pf = pt.PageFields("Category") For Each pi In pf.PivotItems pf.CurrentPage = pi.Name ws.PrintOut Preview:=True Next pi Set pf = Nothing Set pt = Nothing Set ws = Nothing End Sub To run the code, use a method described in Section 11.1. The code refers to ActiveSheet, so you can run the code on any sheet that contains a pivot table. CHAPTER 11 ■ PROGRAMMING A PIVOT TABLE232 How It Works The PrintPivotFilters macro selects each item in the pivot table’s first report filter, and then prints the worksheet. In this example, the ws.PrintOut line has Preview set to True, so the worksheet will preview instead of printing. When you finish testing the code, you can change the setting to False, and the sheet will print when the code runs. Notes The procedure can be modified slightly to print a copy of a pivot chart on a chart sheet for each item in the Category report filter. Store the code on a regular code module. Sub PrintPivotChartFilters() Dim pt As PivotTable Dim pf As PivotField Dim pi As PivotItem Dim ch As Chart On Error Resume Next Set ch = ActiveChart Set pt = ch.PivotLayout.PivotTable Set pf = pt.PageFields("Category") For Each pi In pf.PivotItems pf.CurrentPage = pi.Name ch.PrintOut Preview:=True Next pi Set ch = Nothing Set pf = Nothing Set pt = Nothing End Sub 11.20. Scrolling Through Report Filter Items on a Pivot Chart Problem On the OrderDates Chart sheet, you have a list of categories in the Pivot Chart report filter. Instead of selecting the next category from the drop-down list to view its chart, you’d like scrolling buttons on the chart, so you can quickly view each category’s data. This problem is based on the PrintCat.xlsm sample workbook. Solution You can add arrow shapes to the chart, and assign a macro to each shape, and then click the arrows to select the next or previous report filter item. CHAPTER 11 ■ PROGRAMMING A PIVOT TABLE 233 1. Add the following two procedures to a regular code module: Sub PivotPageNext() Dim CountPI As Long Dim i As Long Dim pt As PivotTable Dim pf As PivotField Dim pi As PivotItem Set pt = ActiveChart.PivotLayout.PivotTable Set pf = pt.PageFields("Category") CountPI = 1 i = 1 For Each pi In pf.PivotItems If pf.CurrentPage.Name = "(All)" Then CountPI = 0 Exit For End If If pi.Name = pf.CurrentPage.Name Then Exit For End If CountPI = CountPI + 1 Next pi For i = CountPI + 1 To pf.PivotItems.Count + 1 On Error Resume Next If i = pf.PivotItems.Count + 1 Then pf.CurrentPage = "(All)" Exit For End If pf.CurrentPage = pf.PivotItems(i).Name If Err.Number = 0 Then Exit For End If Next i Set pf = Nothing Set pt = Nothing End Sub Sub PivotPagePrev() Dim CountPI As Long Dim i As Long Dim pt As PivotTable Dim pf As PivotField Dim pi As PivotItem CHAPTER 11 ■ PROGRAMMING A PIVOT TABLE234 Set pt = ActiveChart.PivotLayout.PivotTable Set pf = pt.PageFields("Category") CountPI = 1 For Each pi In pf.PivotItems If pf.CurrentPage.Name = "(All)" Then CountPI = pf.PivotItems.Count + 1 Exit For End If If pi.Name = pf.CurrentPage.Name Then Exit For CountPI = CountPI + 1 Next pi For i = CountPI - 1 To 0 Step -1 On Error Resume Next pf.CurrentPage = pf.PivotItems(i).Name If Err.Number = 0 Then Exit For If i = 0 Then pf.CurrentPage = "(All)" Exit For End If Next i Set pf = Nothing Set pt = Nothing End Sub 2. Select the OrderDates Chart sheet, and on the Ribbon’s Insert tab, in the Illustrations group, click Shapes. 3. Click the Left Arrow shape, and then click the chart, to add that shape. 4. On the chart, right-click the Left Arrow shape, and then click Assign Macro. 5. In the list of macros, select PivotPagePrev, and then click OK. 6. Add a Right Arrow shape, and assign it the PivotPageNext macro. 7. To scroll through the report filter items, click the arrows (see Figure 11-9). Figure 11-9. Scrolling arrows at the top of a pivot chart CHAPTER 11 ■ PROGRAMMING A PIVOT TABLE 235 How It Works The two macros check the current report filter item and calculate its position in the list of items. If the Left Arrow shape is clicked, the PivotPagePrev macro runs, and the visible item with the next lower number is shown. If the Right Arrow shape is clicked, the PivotPageNext macro runs, and the visible item with the next higher number is shown. CHAPTER 11 ■ PROGRAMMING A PIVOT TABLE236 ■symbols % Difference From custom calculation, 49, 65 % of Column custom calculation, 53 % Of custom calculation, 48 % of Row custom calculation, 52 % of Total custom calculation, 54 < less than symbol, 92 > greater than symbol, 92 [ ] square brackets, 181 * wildcard, 29 ? wildcard, 29 ■A Access queries preparing source data and, 13 source file and, 146 adding items, with calculated items, 57 Advanced Filters, 9 amounts, calculating percent difference between, 64 applying PivotTable styles, 72 area charts, 191 ascending sorts, 22 AutoFilters, 9 Average summary function, 43 AVERAGE worksheet function, 43 axis title, 200 ■B bar charts, 191 Between filter, 28 black-and-white printing, 173 blank cells, counting, 45 blank rows, as separations for field items, 96 Bottom 10 filtering, 36 Bottom 10 Items conditional formatting option, 84 Bubble charts, 191, 193 buttons hiding, 81 scrolling, for pivot charts, 233 ■C calculated fields, 56–67 vs. calculated items, 56 correcting results in, 67 creating, 61 count of unique items, 66 grand total, correcting for, 65 identifying, 63 list of, creating, 67 modifying, 62 removing, 63 calculated items, 56–69 adding to fields with grouped items, 64 vs. calculated fields, 56 created accidentally, 67 errors messages and, 105 identifying, 63 index numbers, using, 59 list of, creating, 67, 105 modifying, 58, 60 removing, 59 with no data, hiding, 135 calculations, 41–69 custom, 46–56 formulas and, 56–69 summary functions and, 41–45 captions, hiding, 81 Cartesian products, 135 categories, sorting, 21 category axis, of pivot charts, 189 cell references, 179–182 cells Highlight Cell Rules option and, 85 Merge and Center Cells with Labels option and, 97 changing. See editing charts. See pivot charts chart templates, 194 chart title, 199 cities, sorting, 21, 24 “Client Safety options…” error message, 152 Collapse/Expand indicator, 131 collapsing items in selected fields, 131 row labels, 130 color scales, 81, 87 column charts, 191 column headings freezing, 98 repeating across pages, 167 column labels, 9, 16 blank, adding N/A to, 133 filters for, 30 subtotals and, 118 column width, lost formatting and, 79 Index 237 columns % of Column custom calculation and, 53 Index custom calculation and, 55 monthly, 9 new, automatically including with source data, 141 organizing data in, 4 comments, adding to pivot table cells, 129 common filters, 32 Compact Form, 93 conditional formatting, 81–92 between two values, 85 bottom 10 items, 84 color scale, 81 data bars, 87, 226 data range, changing, 89 date period, 86 icon set, 82 order of rules, changing, 91 pivot charts and, 198 removing (clearing), 92 connections, enabling, 156 Contains filter, 29 contextual tooltips, 129 Count Numbers summary function, 43, 44 Count summary function, 41, 43, 213 blank cells and, 45 source data errors and, 44 COUNT worksheet function, 43 COUNTA worksheet function, 43 COUNTIF formula, 66 Create a Single Page Field for Me option, 17 creating calculated items/calculated fields, 61, 105 chart templates, 194 custom lists, 25 custom PivotTable styles, 74 customized copies of pivot tables, 187 Excel Tables, 6 lists of formulas, 67 pivot charts, 189–196, 203 pivot tables, 1–20 current month, conditional formatting for, 86 custom calculations, 46-56 custom lists, 25, 27 custom number formats, 92 custom order, sorting in, 25 ■D data creating pivot tables from, 15 extracting, 173–186 grouping/totaling, 101–121 normalizing 11–12 organizing in rows/columns, 4 preparing source data and, 4, 8 data bars, 87, 226 data connections, enabling, 156 database password, storing in connection string, 155 date filters, 28, 30, 32 dates current month, conditional formatting for, 86 data ranges, filtering for, 38 errors and, 185 filtering data by, 31 formatted, summarizing, 112 future, filtering for, 38 grouping, 101, 104, 107–113, 134 nonconsecutive, 33 pivot charts and, 201 Report Filter formatting and, 98 Running Total custom calculation and, 51 subtotal labels and, 99 default PivotTable style, changing, 74 deleting. See removing difference between amounts, calculating, 64 Difference From custom calculation, 46 disabling pivot table features, 228 security features, 230 Show Details feature, 230 Show Report Filter Pages feature, 160 displaying all field items, 134 multiple value fields, 114 seconds, hundredths of, 121 subtotals, 115, 118 districts, sorting, 8, 21 Document Themes, 77 Does Not End With filter, 29 doughnut charts, 191 Down, Then Over option, Report Filters and, 125 drag-and-drop functionality, 137 drop-downs, disabling, 228 dynamic filters, 32 ■E editing calculated fields, 62 calculated item formulas in cells, 60 calculated items, 58 default PivotTable style, 74 grand total label text, 100 layouts, 93, 229 order of rules, 91 pivot tables, 123–138 ranges of data, 89 Report Layouts, 93 source Access file, 146 source CSV file, 147 source Excel Table, 145 ■INDEX238 subtotal date format, 99 subtotal label text, 99 electronic format, sharing information and, 3 empty cells, showing zero, 80 Enable Selection option, 78, 97 error values, hiding, 79 errors calculated items and, 105 custom subtotals and, 183 dates and, 101, 185 Excel Field Names Not Valid, 8 GetPivotData formulas and, 179 GetPivotData function and, 182–186 numbers groupings and, 102 missing items and, 182 OLAP cubes and, 152 pivot charts and, 194 PivotTable reports and, 222 event code, 206 Excel 2007 new functions with, 71, 183 object model and, 205 Excel Field Names Not Valid error message, 8 Excel Lists (Excel 2003), 6, 15, 18 Excel Macro-Enabled Workbooks, 205 Excel Tables, 6 preparing source data and, 6, 9 source, 139, 144 external ranges of data, 143 extracted data deleting sheets created, 176 formatting, 175 extracting data, 173–186 extracting underlying data, 173 ■F field items blank, extracting data for, 182 displaying all, 134 printing each on a new page, 172 separating with blank rows, 96 field labels, centering vertically, 96 field list order, 20 field names showing on pivot charts, 199 changing in source data, 132 fields source data and, 2 using multiple, 113 file size, reducing, 164 filter lists, clearing old items from, 132 filtered data, preparing source data and, 8 filtering by selection, 35 date range, 38 nonconsecutive dates, 33 pivot charts, 195 pivot tables, 28–37 row field values, 32 top items, 36 filters clearing old items from, 132 for dates, 32 for row labels, 30 manual filter, including new items, 34 multiple filters, applying, 29 removing from pivot fields, 220 report filter items, hiding, 37 row label text, 28 using more than one at a time, 29 fiscal quarter, grouping dates by, 108 formatting extracted data, 175 hyperlinks, 98 losing when refreshing, 79 pivot charts and, 198 pivot tables, 71–100 formulas, 56–69 automatic insertion for, 176 changing Solve Order and, 68 creating list of, 67 determining type of, 63 freezing column/row headings, 98 functions, new with Excel 2007, 183 future dates, filtering for, 38 ■G GetPivotData function, 167, 176–186 blank field items, 183 cell references and, 179–182 custom subtotals, 183 date references, 185 errors and, 182–186 missing items, 182 referencing other workbooks, 179 referring to a pivot table, 186 turning off, 178 value fields, cell references, 182 grand total values changing label text for, 100 correcting for calculated field, 65 filtering, 32 hiding specific, 120 simulating an additional, 119 greater than (>), custom number formats and, 92 Group Selection command, 106 grouping another pivot table affected, 110 calculated items, error message, 105 data, 101–113 dates, 104, 107–113, 134 dates by month, 107 dates outside the range, 112 ■INDEX 239 Find itfasterat / error message, 101 fiscal quarter, 108 months and weeks, 110 numbers, 102 report filter, 104 showing all months, 134 starting date, 107 text items, 106 ■H heading cells, blank, 8 heading rows, freezing, 98 headings, repeating across pages, 167 hiding buttons, 81 error values, 79 grand total values, 120 items, 16, 36, 37, 135 labels/captions, 81 highest values data bars for, 87 highlighting, 81 Highlight Cell Rules conditional formatting option, 85 hours, totaling, 121 hyperlinks, formatting, 98 ■I I Will Create the Page Fields option, 17 icon sets, 82, 87 IFERROR function, 183 indentation, for row labels, 94 Index custom calculation, 55 INDEX function, 143 index numbers, in calculated items, 59 INDIRECT function, 186 Information Rights Management, 3 inner fields, 21 innermost fields, row/column label subtotals and, 118 items, 134–136 adding, with calculated items, 57 bottom, filtering for, 36 grouping, in Report Filters, 104 hiding, 16, 36, 37, 135 left-to-right sorting for, 24 limits on, 162 missing, preventing errors for, 182 new, sorting for, 23 old, clearing from filter lists, 132 in selected fields, collapsing, 131 top, filtering for, 36, 221 troubleshooting sorting of, 27 unique, counting, 66 with no data, hiding, 135 ■K keyboard shortcuts, for macros, 209, 212 ■L label filters, 28, 30, 32 labels, 9, 16. See also column labels; row labels blank, adding N/A to, 133 for grand total values, changing, 100 hiding, 81 merged, changing alignment for, 97 subtotals and, 99, 118 vertical centering and, 96 layouts changing, 93, 229 deferred update for, 154 pivot charts and, 197 pivot table performance improvement and, 164 preventing changes to, 229 left-to-right sorting, 24 legend fields, of pivot charts, 189 less than (<), custom number formats and, 92 Less Than Or Equal To filter, 29 limits, 162 line breaks, displaying, 97 line charts, 191 locating source Access file, 146 source data, 1 source Excel Table, 139 source pivot table, 202 lowest values data bars for, 87 highlighting, 81, 84 ■M macro recorder, 205, 208 macros, 4 changing recorded code and, 212 keyboard shortcuts for, 209, 212 modifying recorded code, 212 recording, 208–213 running, 206, 212 manual filters, 30, 34 Manual sorts, 23 MAX worksheet function, 43 Max summary function, 43 Median function, 42 Merge and Center Cells with Labels option, 97 Microsoft Access queries preparing source data and, 13 source file and, 146 Min summary function, 43 ■INDEX240 MIN worksheet function, 43 Mode function, 42 modifying. See also editing calculated fields, 62 calculated item formulas in cells, 60 calculated items, 58 pivot tables, 123–138 monthly columns, 9 months current, conditional formatting for, 86 grouping, 107, 110, 134 moving pivot charts, 203 source Excel Table, 144 Multiple Consolidation Ranges, 11, 16 multiple fields, sorting and, 21 multiple filters, 29 multiple text columns, normalizing data and, 12 multiple values for a field, 113 multiple value fields, displaying vertically, 114 ■N N/A, adding as text to blank row/column labels, 133 naming Excel Tables, 6 worksheets, 188 new items out of order, 23 nonconsecutive dates, 33 normalizing data, 10 multiple text columns and, 12 single text columns and, 11 numbers. See also dates custom number formats and, 92 grouping, 102 pivot charts and, 198 Report Filter formatting and, 98 ■O OFFSET function, 142 OLAP cubes “Client Safety options…” error message and, 152 preparing source data and, 14 OLAP-based pivot tables, cell references in, 180 order dates, 105, 112 order of rules, changing for conditional formatting, 91 OrderDate field, 101–113, 118 outer fields, 21 Outline Form, 94 Over, Then Down option, Report Filters and, 125 ■P page fields, 16-18 page labels, 16 passwords, 2 database, 155 workbook protection and, 161 worksheet protection and, 225 Paste Special Values, caution for, 96 percent % Difference From custom calculation and, 49, 65 % of Column custom calculation and, 53 % Of custom calculation and, 48 % of Row custom calculation and, 52 % of Total custom calculation and, 54 filtering for, 37 Percentile function, 42 performance, ways to improve, 163 permissions, 3 pie charts, 191 pivot caches, 111, 223, 224 PivotChart Field List, 196 PivotChart Filter Pane, 196 pivot charts, 189–204 adding text boxes to, 200 combination pivot chart, 203 converting to static charts, 199 chart title, 199 chart subtype, selecting, 191 chart type, selecting, 191 column charts, 191 creating, 189–196, 203 data table, formatting, 198 default chart type, 193 field names, showing, 199 filtering, 195 grand totals, 198 keyboard shortcut, 193 layout affects pivot table, 197 moving, 203 normal chart from pivot table data, 194 number format, 198 planning, 189 refreshing, 201 removing, 204 scrolling buttons for, 233 series order, 197 sorting, 197 source pivot table, locating, 202 static chart, converting to, 199 subtypes of, 192 templates, 194 types of, 191 years, multiple series, 201 pivot fields, modifying, 129–133 ■INDEX 241 Find itfasterat / pivot items hiding items with no data, 135 showing all field items, 134 trailing spaces, 136 pivot tables creating, 1–20 customized copies of, 187 deferred layout update for, 154 deleting, 137 disabling features for, 228 drag-and-drop, allowing, 137 filtering, 28–37 formatting, 71–100 grouping dates and, 110 lost formatting when refreshing, 79 modifying, 123–138 new features with Excel 2007, 71 overlapping, refreshes and, 153 pivot charts created from, 189–204 printing, 167–173, 208, 231 privacy and, 160 programming, 205–236 referenced in other workbooks by GetPivotData formula, 179 referring from one to another, 186 refreshing, 149–154 security/protection and, 155, 161 sharing, 2 sorting, 21–28 source for pivot chart, locating, 202 styles for. See PivotTable styles updating, 139–154, 226 PivotChart Filter pane, 195 PivotTable and PivotChart wizard, 10, 15 PivotTable Field List, 18, 195 PivotTable styles, 71–77 applying/canceling, 72 copying to a different workbook, 76 custom, 7, 74 default, changing, 74 how they work, 72 removing (clearing), 73 themes, impacting, 77 PivotTable wizard, 10, 15 planning a pivot table, 1-4 preparing source data, 4–14 previewing styles, 71 print area resetting automatically, 231 adjusting automatically, 170 printed format, sharing information and, 3 printing pivot tables 167–173 black and white, 173 for each report filter item, 170, 232 recording macros and, 208 resetting print area and, 231 starting each item on a new page, 172 privacy, 160 Product summary function, 43 PRODUCT worksheet function, 43 programming pivot tables, 205–236 changing recorded code and, 212 pivot cache and, 223 protection, 157–161 preventing changes to pivot tables and, 157 protected worksheets, refreshing pivot tables and, 152, 225 Show Report Filter Pages feature and, 160 ■Q QAT (Quick Access Toolbar), 10 quick styles. See PivotTable styles ■R radar charts, 191 ranges of data changing, 89 external, 143 formatting, 85 outside-of-range dates and, 112 records, limits and, 162 re-creating source data, 174 reducing file size, 164 refreshing after queries have been executed, 153 all pivot tables in a workbook, 151 automatically when source data changes, 226 defer layout update, 154 losing formatting, 79 every 30 minutes, 150 pivot charts, 201 pivot tables, 149–154, 225, 230 protected sheet, 152 stopping refreshes and, 150 two tables overlap, 153 when a file opens, 149 removing calculated fields, 63 calculated items, 59 conditional formatting, 92 filters, from pivot fields, 220 formulas, from accidentally created calculated items, 67 pivot charts, 204 pivot tables, 137 PivotTable styles, 73 renaming Values fields, 127 repeating headings, across pages, 167 report filters, across pages, 167 row labels, 95, 167 ■INDEX242 report filters, 18, 37 arranging, 124 changing selection in related tables, 218 grouping items and, 104 lost formatting and, 79 number format and, 98 pivot table printing and, 232 preventing selection of (All) in, 227 repeating across pages, 167 scrolling through items and, 233 shifts when adding, 123 worksheet printing and, 170 Report Layouts. See layouts resources for further reading Excel 2007 object model, 205 Information Rights Management, 3 Microsoft Access queries, 13 normalization, 10 OLAP cube error message, 152 OLAP cubes, 14, 162 programming Excel, 205 security/privacy, 161 statistical function improvements in Excel, 43 VAR/VARP improvements, 44 Reverse Icon Order option, 84 row headings, freezing, 98 row labels, 9, 16 blank, adding N/A to, 133 centering vertically, 96 collapsing, 130, 131 filtering, 28 grand total value filtering and, 32 indentation for, 94 merged, changing alignment, 97 repeating, 95, 167 selection filtering and, 35 sorting, 21 subtotals and, 118 rows % of Row custom calculation and, 52 Index custom calculation and, 55 new, automatically including with source data, 141 organizing data in, 4 rules Data Bar, 88 order of, changing for conditional formatting, 91 ranges of data, conditional formatting and, 89 running macros, 206 Running Total custom calculation, 50 ■S sales data OLAP cubes and, 14 by region, 15 summarizing, 1, 9 sales order data, 8 by district, 8 Microsoft Access and, 13 summarizing, 4 sample code, using, 205 sample files, folder for, 139 Scatter charts, 191, 193 Screen Tip feature, 71 seconds, displaying hundredths of, 121 secured network folders, 3 security, 3, 155–157 disabling security features and, 230 resources for additional reading and, 161 Selection, Enable, 78 sensitive data, 2 separate sheets, using Excel data on, 15 series order, pivot charts and, 197 sharing pivot tables, 2 Shortest Bar, data bars and, 88 Show Details feature, 173–176 disabling, 230 naming/formatting, 214 Show Report Filter Pages feature, disabling, 160 single text columns, normalizing data and, 11 Solve Order, changing, 68 sorting custom order, 25 left-to-right, 24 new items out of order, 23 pivot charts, 197 pivot tables, 21–28 row labels, 21 troubleshooting for, 27 values, 22 source Access file, 146 source CSV file, 147 source data automatically including new data with, 141, 143 changing the source Access file, 146 changing the source CSV file, 147 changing the source Excel Table, 145 custom number formats and, 92 errors in, 44 extracting, 173 field names in, changing, 132 filtered Excel data, 8 locating the source Access file, 146 locating the source Excel Table, 139 location of, 1 ■INDEX 243 Find itfasterat / moving the source Excel Table, 144 number fields in, 128 number of records and, 162 preparing, 4–14 re-creating, 174 update frequency of, 2 updating pivot tables and, 139–149, 226 source Excel Table, 139, 144 spaces, ignoring when summarizing data, 136 square brackets [ ], in field/item names, 181 starting date, grouping dates and, 107, 109 static charts, converting pivot charts to, 199 StDev summary function, 43 STDEV worksheet function, 43 StDevp summary function, 44 Stock charts, 191, 193 strong passwords, 3 styles. See PivotTable Styles. subtotals 115–119 creating multiple, 117 custom, errors and, 183 data format for, changing, 99 displaying, at bottom of group, 115 formatting, Enable Selection option and, 78 label text for, changing, 99 preventing from appearing, 116 removing from source data, 5 sorting and, 22 sum, filtering for, 37 Sum summary function, 41, 213 summarizing data, ignoring trailing spaces and, 136 formatted dates, 112 summary functions, 41–45, 213 changing for all value fields, 213 counting blank cells, 45 defaulting to Sum or Count, 41 errors in the source data, 44 surface charts, 191 ■T table. See Excel Tables Tabular Form, 94 templates, for pivot charts, 194 text for grand total labels, changing, 100 pivot charts and, 200 replacing with cell references, 179 for subtotal labels, changing, 99 in Values area, displaying, 128 text files, preparing source data and, 14 text items, grouping, 106 themes, 77 time field, totaling hours, 121 time formats, 121 tooltips, 129 Top 10 filtering, 36, 221 Top/Bottom Rules list, 84 totals. See also grand total values % of Total custom calculation and, 54 for data, 101, 119–121 for hours, 121 Index custom calculation and, 55 removing from source data, 5 Running Total custom calculation and, 50 trailing spaces, ignoring when summarizing data, 136 troubleshooting item sorting, 27 ■U unique count of items, 66 update frequency for source data, 2 updating pivot tables, 139–154 refreshing and, 149–154 OLAP-based pivot tables and, 152 source data and, 139–149 ■V value filters, 30 values. See also grand total values formatting specific range of, 85 value fields and, 113 Values area changing content in, 126, 222 displaying text in, 128 empty cells, showing zero/character in, 80 Values fields arranging vertically, 127 changing summary function for, 213 modifying, 126–129 multiple value fields, displaying vertically, 114 renaming, 127 text, showing, 128 using cell references for, 181 Var summary function, 44 VAR worksheet function, 44 Varp summary function, 44 VARP worksheet function, 44 VBA (Visual Basic for Applications), 208 VBE (Visual Basic Editor), 205 visual impact, adding to data via conditional formatting, 81–92 ■W week number, 110 weeks, grouping dates by, 108, 110 wildcard characters, 29 wizards PivotChart, 10, 15 PivotTable, 10, 15 ■INDEX244 workbooks custom styles, copying to, 76 deleting worksheets automatically upon closing, 216 pivot charts, removing from, 204 pivot table performance improvement and, 165 refreshing all pivot tables in, 151 sharing pivot tables and, 3 worksheets automatic deletion of when closing workbooks, 216 copying, 188 created by extracted data, deleting, 176 creating pivot tables and, 15 deleting pivot tables from, 137 event code and, 206 formatting data as Excel Table and, 6 protected, refreshing pivot tables and, 152, 225 Show Details feature and, 214 ■X .xlsm files, 205 ■Y year-to-date transactions, 14 ■Z zero, displaying in empty Values cells, 80 ■INDEX 245 Find itfasterat /