Figure 875 A frequency distribution.

GROUPING 1 PIVOT TABLE GROUPS THEM ALL

Problem: I am building two pivot tables. One will show daily sales detail. The other will summarize by month. I arranged the pivot tables side by side. I use the Group feature to group the second pivot table by month.

3

Figure 876 Group the second pivot table by month.

Unfortunately, this groups both pivot tables by month.

Figure 877 Both pivot tables are grouped.

Strategy: One solution is to group by Days, Months, and Years. You can then use different fields in the two pivot tables. However, the point of this topic is how to create two pivot tables that do not share the same pivot table cache.

When you create a pivot table, the data from your worksheet is loaded into memory to a special area called the pivot table cache. A pivot table is fast because it is calculated from the cache in memory.

Way back in Excel 2003, Excel would ask you if you wanted all of your pivot tables to share the same pivot table cache. This would save memory...each pivot table cache increases the size of the workbook by the amount of data in the data set. But, sharing a cache causes problems like the one here; when you group fields or calculate fields, those changes happen in all of the pivot tables.

In Excel today, any pivot table created using Insert, PivotTable automatically shares the cache. Microsoft doesn’t even ask you.

However, you can force Microsoft to ask if you want to share the cache by using the old pivot table wizard.

Follow these steps

1. Create the first pivot table as normal.

2. To create the second pivot table, select one cell in your original data set.

3. Press Alt+D followed by P. This was the Excel 2003 keyboard shortcut for Data, PivotTable. Excel will display step 1 of the old pivot table wizard.

Figure 878 Complete with new artwork, the old wizard.

4. Click Next in step 1.

5. Make sure your data range is correct in step 2.

Figure 879 Check the data range.

6. Click Next. Between Step 2 and Step 3, Excel will display a lengthy message that encourages you to have this pivot table share the cache with the other pivot table in order to conserve memory. You want to click No to this dialog.

Figure 880 Click No here.

7. In Step 3 of the wizard, choose the location for your pivot table. Click Finish.

Figure 882 These pivot tables do not share a cache.

REDUCE SIZE 50% BEFORE SENDING

Problem: I have to upload a file over my aunt’s 56 baud modem. Can I reduce the size of the workbook?

Strategy: When you save a workbook with a pivot table, Excel saves the data on the worksheet, plus the data in the pivot table cache. You can’t actually delete the pivot table cache, but you can delete the data on the worksheet.

Here is a workbook that contains data and a pivot table. You are seeing two windows of the same work- book.

Figure 883 The data and the pivot cache.

I deleted the worksheet with the data. Now, the workbook appears to have text in only two cells.

Figure 884 The workbook with only a pivot table and no data.

Here is a comparison of file sizes. The workbook from Figure 883 is 55K. The workbook without the data worksheet is 23K. The workbook from the previous topic, the one with two pivot tables and two pivot caches is 79K.

Figure 885 File size is reduced by having only the pivot cache.

OK, so I’ve proved that you can save space in a workbook by deleting the data. What good is that?

Say that you transfer that file over the modem, then you get back to work and open the file with only the

2-cell pivot table.

Excel will ask if you want to enable the data connection to the invisible cache.

Cell A4 in the pivot table is essentially a grand total of all rows in the pivot table. Double-click that cell and Excel will bring put the contents of the pivot table cache into a new worksheet in the workbook!