Number of Row Fields in Font

Generator PDF417 in Font Number of Row Fields

32,500 255

32,768

Excel calculates the number of potential field item combinations that your pivot table requires, and that number, in theory, can t exceed 32,768. If you try to add column fields that will result in the number being exceeded, you ll see an error message. For more information on this limit, see Section 9.5. The limits for row field item combinations, approximately 2.1 billion, is much greater than the column field item limit.

2^31

Using your OLAP database as the source, a pivot table responds very slowly when you add fields to a new pivot table, or move fields to a different area of the pivot table, using the worksheet layout, and you d like to speed up the process.

On the PivotTable toolbar, turn off the Always Display Items feature. This hides the row and column fields until you add fields to the data area. Then, when creating a new pivot table, add the page, row, and column fields first, then add fields to the data area. Follow these steps to modify an existing pivot table: 1. On the PivotTable toolbar, if the Always Display Items button is enabled, click it to turn it off (see Figure 9-5).

Note If there is only one data field, the Data field button will show the name of the data field, e.g., Sum of

3. Change the pivot table layout by adding, removing, or moving the pivot fields. 4. Drag the removed data fields back from the pivot field list to the pivot table data area.

If the process is still slow, use the PivotTable and PivotChart Wizard to make layout changes. This method can be faster than the worksheet layout if the source data has a large number of records or multiple data fields. 1. Right-click a cell in the pivot table, and choose PivotTable Wizard. 2. Click the Layout button. 3. Add, remove, or move the pivot fields in the layout diagram. 4. Click OK, then click Finish to return to the worksheet.

Also, use the PivotTable and PivotChart Wizard method if you want to add fields to the pivot table s page area to show one page of data at a time from a large external source. This creates server pages, and is described in Section 9.1.

Turning off the Always Display Items feature may have a positive impact on OLAP-based pivot tables, because if items are showing, the OLAP server is queried each time a field is added. For example, in a pivot table based on an OLAP cube with two data fields, two row fields were moved to a different position in the row area. As shown in Table 9-2, using the PivotTable and PivotChart Wizard method was approximately twice as fast as moving the fields on the worksheet, with the Always Display Items feature turned on.

Change layout in PivotTable and PivotChart Wizard: Change layout on worksheet, Always Display Items off: Change layout on worksheet, Always Display Items on:

Your pivot table is based on an external data source, and you wonder if its speed will improve if you use the Optimize memory option.

You can turn on the Optimize memory option if you experience insufficient memory errors while working with your pivot table: 1. Right-click a cell in the pivot table, and choose Table Options. 2. Under External data options, add a checkmark to Optimize memory, then click OK.

The Optimize memory option was introduced in Excel 97 to address memory problems that occurred when creating pivot tables in earlier versions of Excel. When the option is on, Excel queries the external data if the pivot table is refreshed, or the layout is changed, to count the unique items in each field. If there are 256 or fewer items, it can optimize the storage for that field. As a result, when the option is turned on, refreshing the pivot table is much slower. For example, with a pivot table based on a Microsoft Access query with 30,000 records, the pivot table refreshed in under 3 seconds with the Optimize memory option turned off. With the option turned on, the refresh took more than 10 seconds. The memory limit has been increased dramatically in Excel 2003, compared to earlier versions from 128MB in Excel 2002 to 1GB in Excel 2003, so memory errors occur less frequently. The differences are outlined in the Microsoft Knowledge Base article Not Enough Memory Error Messages When You Copy Formulas Over Large Area in Excel, at http://support.microsoft.com/default.aspx id=313275. For a thorough discussion of Excel s memory limits, see Charles Williams article, Out of Memory, Memory Limits, Memory Leaks, Excel Will Not Start, at www.decisionmodels.com/memlimitsc.htm.