Check the memory use setting
-
Click OK in the message dialog box.
-
Create the report again, and in step 3 of the PivotTable and PivotChart Wizard, click Options.
-
Select the Optimize memory check box.
Use page fields to save memory If you still can't create the report, set up one or more of the page fields to retrieve the data as you select each item.
-
In step 3 of the wizard, click Layout.
-
Drag one or more fields to the PAGE area.
-
Double-click each page field.
-
Click Advanced.
-
Click Query external data source as you select each page field item.
Simplify the report The number of fields you can add depends on the amount of memory in your computer and the amount of source data used for the report. When you want to use a large number of fields, add them as row fields or page fields. Page fields use the least memory. Row fields use less memory than column fields.
Base multiple reports for the same data on one report When you create a PivotTable report, Microsoft Excel creates a storage area in memory for the report. If you plan to create several reports from the same source data, select Another PivotTable report or PivotChart report in step 1 of the wizard so that the reports all use the same storage area.
Simplify the file Reduce the number of reports in the workbook.
Create the report in a separate file If your report is based on worksheet data, create the report in a different workbook from the workbook that contains the data. That way, the original data and the report do not have to be in memory at the same time.
Step 2 of the PivotTable and PivotChart Wizard doesn't list the report I want.
Check the location of the report The wizard lists only the reports in the workbook where you started the wizard. If the report you want to use as the source is in a different workbook, copy the report into the active workbook.
Check the page field settings The PivotTable report you want may have page fields that are set to query for external data as you select each item. To use a report as the source for another report, its page fields must all be set to retrieve external data for all items at once. Click the source report, double-click each page field, click Advanced, and then check the settings under Page field options.
Importing or pasting from a Web page didn't work right.
Use export instead of copy and paste If you used the Copy command from a PivotTable list on a Web page and then pasted the data into Microsoft Excel, only the cell values are pasted, creating a noninteractive table on the worksheet. To copy the data to Excel as a PivotTable report, use the Export to Microsoft Excel command in the PivotTable list.
Adjust the layout before you export A PivotTable list on a Web page can display detail data at the same time that it displays summarized data. As a result, some layouts possible in a PivotTable list can't be produced in an Excel PivotTable report. For more information about preparing a PivotTable list for export to Excel, click the Help button on the toolbar in the PivotTable list.
I'm having problems with an OLAP data source.
Creating OLAP cubes
Date or time fields have incorrect levels
Check the date or time field in the source database If you don't see the levels you expect in the date or time dimensions in source data from the OLAP Cube Wizard in Microsoft Query, check the date or time field from the underlying relational database that supplied the data for the cube. The database may store dates and times as text instead of a date or time format that the wizard can recognize. If you suspect this is the case, consult the database administrator for the relational database to verify and correct the date or time format for the field.
Check the top level of the dimension When you add date and time fields as lower levels of a dimension, the OLAP Cube Wizard does not automatically break the data out into year/quarter/month/week and hour/minute/second levels. The wizard does this only when you add the date or time field as the top level of a new dimension. If the date or time field is not the top level, modify the cube by opening the .oqy file in Microsoft Query, or contact the person who created the cube to make these changes.
The summary function I want is missing
In the OLAP Cube Wizard, the only summary functions available for data fields are Sum, Count, Min, and Max.
Create the report directly from the database records If you can simplify and reduce your query to where your system can handle the amount of data it returns, try returning the data directly to Microsoft Excel from Microsoft Query without creating a cube. When you create a PivotTable or PivotChart report directly from records in a database, you have access to the full set of PivotTable summary functions (Sum, Count, Average, Max, Min, Product, Count Nums, StdDev, StdDevp, Var, and Varp).
Consider setting up an OLAP server for the database The Microsoft OLAP server product, Microsoft SQL Server OLAP Services, lets you set up a wider range of summary fields than the OLAP client software included in Microsoft Office.
Make sure the original database is available To edit an OLAP cube, you must have access to the original server database that supplied the cube data. Check to make sure the database hasn't been renamed or moved. Make sure the server is available and you can connect to it.
Check for changes to the original database If you used the OLAP Cube Wizard to create the cube from a relational database, tables in the database might have been renamed or deleted so that the cube can no longer locate the original data. Connect to the database in Microsoft Query, and check for any changes to the organization, or schema, of the database. If the database has changed, you'll need to create a new cube.
A 'Data has been lost' message appears
A field you included in the cube is no longer available in the source database.
Check for changes to the original database If the cube is an offline cube file that was created from an OLAP server database, reconnect a report to the server database and check the fields available in the PivotTable Field List window. If the field is no longer available from the server, you can use the Offline OLAP command on the PivotTable menu to create a new offline cube file.
If you created the cube in Microsoft Query, use Query to open the .dqy query file that you used to create the cube, or if you did not keep a .dqy file, create a new query connecting to the original relational database. Check what fields are available. For full instructions, see Help in Microsoft Query. If fields have been removed from the database, you can create a new cube.
Saving the cube file is taking a long time
If you included a large subset of the OLAP data in the cube file, the file may be time consuming to create.
-
To cancel saving the file, click Stop in the Create Cube File – Progress dialog box.
-
To reduce the size of the file, eliminate data that you don't need to view in your report. In step 2 of the Offline Cube Wizard, select only the dimensions and levels within each dimension that you need to see. In step 3, select only the measures that you're using as data fields in the report, and in each dimension eliminate any items you don't need.
I run out of disk space while saving a cube
OLAP databases are designed to manage very large amounts of detailed data, and as a result, the server database might occupy a much larger amount of disk storage than your local hard disk provides. If you specify a large subset of this data for your offline cube file, you might run out of space.
Free up disk space or find another disk Try deleting files you don't need from your disk before saving the cube file. Or try saving the file on a network drive.
Include less data in the offline cube file Consider how you can minimize the amount of data in the file and still have what you need for your PivotTable or PivotChart report. Try the following:
-
Eliminate dimensions In step 2 of the Offline Cube Wizard, select only the dimensions that you actually have displayed as fields in your PivotTable or PivotChart report.
-
Eliminate levels of detail Click the
box next to each selected dimension in step 2 of the wizard, and clear the check boxes for levels lower than those displayed in your report. -
Eliminate data fields In step 3 of the wizard, click the
box next to Measures, and select only the data fields you're using in the report. -
Eliminate items of data Click the
box next to each dimension in step 3, and clear the check boxes for items you don't need to see in the report.
Stay connected to the server If you've eliminated all possible data and still cannot save an offline cube file, you'll need to continue using the connection to the server OLAP database to interact with your report.
Using OLAP cubes
Data is missing from my report after I create a cube file
When you selected the contents for the offline cube file, you might have left out some of the data for the fields used in the report. As a result, when you change the report to display different data, data you expected to see isn't available from the offline cube file.
Use the Offline OLAP command on the PivotTable menu to reconnect the report to the server database, and make sure the report displays the data you want to see. Then edit the offline cube file, making sure you include all dimensions, data fields, and levels of detail used in the report.
My report is using fields from the query instead of my cube
If the PivotTable Field List window is showing you the fields from the relational database that you queried to create the cube, you returned the data from your query to Microsoft Excel instead of opening the .oqy file created by the OLAP Cube Wizard. This file stores the cube definition, and if you saved a .cub offline cube file, provides access to that file. If you did not specify a new location for the .oqy file, the file was saved in either my documents\my data sources or winnt\profiles\your user name\my data sources, depending on your version of the Windows operating system.
To base a report on your new cube, click Open on the Excel File menu, click Query Files in the Files of type list, and then locate and double-click the .oqy file. If you want a PivotChart report, click the PivotTable report that's created when you open the .oqy file, and then click Chart Wizard
on the PivotTable toolbar.
Data I know is in the database is missing from my cube
Cubes created from OLAP server databases or relational databases don't necessarily include all of the data in the original database. Only the data that you select in the Offline Cube Wizard or OLAP Cube Wizard is included in the cube.
Wait for data retrieval to complete When you change your PivotTable or PivotChart report to display different data, new data is retrieved from the cube. When you refresh the report, new data is retrieved from the original database and the cube is completely reconstructed. This process might take a while.
Check with the person who created the cube If you got the cube from someone else, and the PivotTable Field List window is missing fields that you need or levels of detail that you wanted are unavailable, ask the creator of the cube to change it so that it includes additional data.
Change the contents of an offline cube file If you created the offline cube file in Microsoft Excel from an OLAP server database, use the Offline OLAP command on the PivotTable menu to change the file. Make sure you include all dimensions, data fields, and levels of detail used in the report.
Check the contents of a cube that was created in Query You cannot add data to cubes created with the OLAP Cube Wizard in Microsoft Query, but you can change how the cube is organized and delete data from the cube. If the cube is missing fields from the original relational database, you can create a new cube in Query to include those fields. In Query, open the .dqy file that you used to query the data for the OLAP cube, or create a new query if you did not keep a .dqy file. Add to the query any additional fields that you want in the cube, and then use the Create OLAP Cube command on the Query File menu create a new cube. For full instructions, see Help in Microsoft Query.
New data doesn't appear in my report when I refresh
The offline cube file, or the cube created in Microsoft Query, might not be able to connect with the original server database to retrieve new data.
Make sure the original database is available Check that the original server database that supplied the data for the cube hasn't been renamed or moved. Make sure the server is available and you can connect to it.
Make sure new data is available Check with the database administrator to determine whether the database has been updated in the areas included in your report.
Make sure the database organization hasn't changed If an OLAP server cube has been rebuilt, or a relational database supplying data to a cube has been reorganized, you might need to reorganize your report or create a new offline cube file or OLAP Cube Wizard cube to access the changed data. Contact the database administrator to find out about changes to the database.
Microsoft Excel can't find my offline cube file
The .cub might have been renamed or moved.
Make sure you have the file If someone else gave you the .oqy file you're opening, make sure you also have access to the .cub file.
Browse for the file If you're trying to connect to the offline cube file from the Offline OLAP Settings dialog box, click the Edit offline data file button and then use the Browse button in step 4 of the Create Cube File wizard to locate the file.
Reconnect to the original database If you cannot locate the file, you might be able to reconnect the report to the original OLAP server database. Click the report, click Offline OLAP on the PivotTable menu, and then click On-line OLAP. You can then create a new offline cube file.
Note For further help with problems with OLAP Cube Wizard cubes, see Help in Microsoft Query.
Laying out a report
Data takes a long time to appear when I add a field.
When a report is based on a large amount of external data or an OLAP Cube Wizard data source, delays can be lengthy when you drag fields from the field list onto the worksheet.
Drop data items last Drop fields in the row, column, and page areas first. Drop fields in the data area last. This approach minimizes the amount of data Microsoft Excel has to display for interim steps.
Turn off row and column item display On the PivotTable toolbar, make sure Always Display Items
is turned off. The items won't appear as you drag fields to the row and column areas, but will appear once you add a data field.
Use the wizard to change the layout If the response is still too slow, you can return to the PivotTable and PivotChart Wizard to lay out the report. Click the report, click PivotTable and PivotChart Report on the Data menu, and then click Layout.
Check the page field settings If the field you want to drag is set to query for external data as you select each item, the field may be locked in the page position. Double-click the field, click Advanced, and clear the Disable pivoting of this field check box under Page field options. If you drag the field to another area, Microsoft Excel retrieves the external data for all items in the field at once.
Check for OLAP source data In reports with OLAP source data, some fields can be used only as row, column, or page fields. These fields have 
icons in the PivotTable Field List window. Fields with 
icons can be used only as data fields.
Check for VBA macros A Visual Basic for Applications (VBA) macro can turn off the ability to change the layout by dragging fields. If the cancel symbol appears over the pointer when you try to drag the field, dragging has been turned off for that field. For help with this case, contact the author of the macros in your workbook.
Check for protection If the worksheet was protected with the Use PivotTable reports check box cleared in the Protect Sheet dialog box, you cannot move fields or make changes. Unprotect the worksheet, or contact the person who protected it.
I can't get data fields in the order I want.
Right-click a data field, point to Order on the shortcut menu, and use the commands on the Order menu to move the field to the position you want.
Click the report The list of fields appears only when the report is selected.
Display the field list If you still don't see the field list, click Show Field List
on the PivotTable toolbar.
A field doesn't have a dropdown arrow.
Check whether it's the only data field The Data field appears with a dropdown arrow only when you've added two or more data fields to a report.
Check whether the field is in an OLAP dimension In reports with OLAP source data, dimension fields in the row and column areas have an arrow 
in the field button only if the field is the topmost field in the dimension. You can use the arrow in this field to display or hide different levels of detail throughout the dimension.
Check whether upper levels are hidden When upper levels of a dimension are hidden, the topmost field is hidden, and none of the displayed fields have the arrow . Right-click any field button for the dimension, and then click Show Levels on the shortcut menu.
The dropdown arrow for a field doesn't work.
On the PivotTable toolbar, click Always Display Items
. If you don't want to use this feature, drag a field to the data area. Once you have a field in the data area, the dropdown arrows will work for all fields in the report.
Formatting and customizing a report
Formatting disappears when I refresh or change the layout.
Check the preserve setting Click PivotTable on the PivotTable toolbar, click Table Options, and make sure the Preserve formatting check box is selected.
Cell borders aren't retained Microsoft Excel does not keep cell borders when you change the layout or refresh the report.
Conditional formatting does not work If you try to apply conditional formats to cells in a PivotTable report, you will get unpredictable results.
An AutoFormat changed the layout.
Undo the autoformat Try clicking Undo
. If you changed the report to an indented format recently, you may be able to restore it to its previous format and layout.
Move fields Some autoformats change column fields in the report to row fields, and other autoformats change a single row field to a column field. Drag the fields back to their original positions.
Number formatting doesn't match the source data.
Microsoft Excel doesn't use the number formatting from Excel source data in a PivotTable report. By default, numbers are displayed with no decimal places, no 1000 separator, and a minus sign for negative numbers.
To change this format, right-click a cell in the data area, click Field Settings, click Number, and then click the category and options you want. Repeat for each field in the data area.
Renamed numeric items aren't formatted or sorted right.
By default, numeric items in PivotTable fields are formatted to match the type of the underlying source data. For example, an item named 2.0 would get numeric formatting. When you rename a numeric PivotTable item, the item is then formatted as text even if it contains a number. Because Microsoft Excel sorts text separately from numbers, renamed numeric items no longer sort in sequence with the rest of the numbers.
You can reorder the text items manually by dragging them to new positions.
Features I expect to see are missing.
The report may be based on OLAP source data. For example, OLAP databases provide different summary functions as separate data fields, rather than as a PivotTable setting.
To determine whether a report is based on OLAP source data, check the PivotTable Field List window. If the field list has and icons, the report has OLAP source data.
Property fields I selected aren't displayed.
Move the field to the row area In column fields, property fields are displayed only for the lowest level of detail, not for other levels of detail in the report. In row fields, you can display property fields for all levels of detail.
Check the layout setting for the field Double-click the field for which you're displaying property fields, click Layout, and make sure Show items in outline form is selected.
Make sure the level with the property fields is displayed Property fields are associated with specific levels of a dimension field. Right-click the field, and if Show Levels appears on the shortcut menu, levels have been hidden. Click Show Levels to redisplay them. Click the field and then click Show Detail
to display additional levels.
Make sure the cube has data for the property fields Check with the administrator for your OLAP server to make sure data is available for the property fields you selected.
Retrieving and refreshing data
A 'PivotTable is invalid' message appears.
Refresh the report For some types of source data, you will see this message when you first try to use a report that was saved as a Web page if a source field has more than one type of data
.
Check the source range If the report is based on worksheet data, make sure that the source data has not been deleted. If the source is a named range and you used the name to specify the source data when you created the report, make sure that the name has not been deleted from the source workbook.
Check the external data source For reports that are based on external data, make sure the database is available and you can connect to it.
Check available memory If a report is not valid when you open a workbook, close any files and quit any programs you don't need so that more memory is available, and then try to open the workbook again.
Wait for the refresh to complete before copying a report When you refresh a report, make sure the refresh is complete and the status bar displays "Ready" before you copy or paste the report into another workbook.
Check the file format If you are opening a report that was created in Microsoft Excel 97, and the report has calculated fields or has a page field that's set to retrieve data for each item individually, make sure you save the report in Excel workbook format (click Save As on the File menu, and click Microsoft Excel Workbook in the Save as type box). Then, if you need to, you can save the workbook in an earlier Excel format.
While a query is running, I can't change my worksheet.
Check whether the area you're editing is being updated While a query is running in the background, you can't change the report you're currently updating, or any other reports based on the report.
Wait for the query to complete, or cancel it To check the progress or cancel a query, double-click the 
icon in the status bar, and if desired click Stop Refresh.
Run the query in the background On the PivotTable toolbar, click PivotTable, click Table Options, and then select the Background query check box.
Data is missing after refresh.
Set page fields to view all data Select All in every page field before you refresh the report.
If a page field does not include All, either the field is set to query for external data one item at a time, or the source data doesn't include information for an All item. In both cases, the data is refreshed whenever you select a different item.
Check the location of the missing data For reports based on worksheet data, the new data might have been added outside the source range you originally specified, or the source data could have been moved to a new location.
-
On the Data menu, click PivotTable and PivotChart Report.
-
Click Back.
-
Change the source range specification to include the new data or to specify the new location.
-
Click Finish.
Check that the query is selecting the right data For reports based on external data, review the query in Microsoft Query to make sure it is retrieving the data you want.
Check the cube or cube file If the report is based on a cube created by the OLAP Cube Wizard, open the .oqy file in Microsoft Query and check the contents of the cube to make sure it contains the data you want. If the report is based on a cube file, check the cube file contents: on the PivotTable toolbar, click PivotTable, click Offline OLAP, and then click Edit offline data file. If the report is based on source data from an OLAP server database, contact your database administrator to find out whether the database has changed.
The report looks completely different after refresh, or can't be refreshed.
Check the availability of the source database Make sure you can still connect to the external database and view data.
Check for changes to the source database If the report is based on OLAP source data, changes may have been made to the data available in the cube on the server. Contact the administrator of the OLAP server for more information.
The page field options are unavailable.
Check the type of field The field you selected might not be a page field. These settings are available only for page fields.
Check for external source data The report might not be based on external data. These settings are not available for reports based on worksheet data.
Check for OLAP source data These settings are not available for reports that are based on OLAP source data. The field list has and icons in OLAP-based reports.
Check the data type for the field The page field settings are unavailable for memo fields or fields that contain OLE objects.
Check for parameter query support The ODBC driver for your external database might not support parameter queries. These settings are available only if the driver for your data source supports parameter queries.
The ODBC drivers supplied with Microsoft Query all support parameter queries. To find out whether a third-party driver supports parameter queries, contact your driver vendor.
Check for reports based on another PivotTable report When you base another PivotTable report or more than one PivotChart report on an existing PivotTable report, page field settings are not available for any of the reports.
Check whether the field is grouped Drag the field to the row or column area, right-click any grouped items, click Ungroup, and then drag the field back to the page area to make the page field settings available.
I changed my source data, but the report did not update.
Click the report, and then click Refresh Data
on the PivotTable toolbar.
Summarizing and calculating
A data field is using Count instead of Sum.
Check for text or blank items in the field If your data field contains any text values or blank cells, the field uses the Count summary function by default. Double-click the field and click Sum in the Summarize by box.
Check for OLAP source data In reports based on OLAP source data, the available summary functions are determined on the OLAP server, and you cannot change them in the Microsoft Excel report. The field list has and icons in OLAP-based reports.
My formula isn't calculating what I expected.
Calculated fields and calculated items are calculated in different ways. Formulas for calculated fields operate on the sum of the underlying data for any fields referenced in the formula. For example, the formula ='Order Amount' * 1.2 multiplies the sum of the order amounts for each product and salesperson by 1.2; it does not multiply each individual order by 1.2 and then sum the multiplied amounts.
Formulas for calculated items, however, operate on the individual records; the calculated item formula =Seafood * 115% multiplies each individual order for Seafood times 115%, after which the multiplied amounts are summarized together in the data area.
Check the formulas Make sure the formulas for all calculated fields and items in the report are what you intended. To display a list of the formulas, click PivotTable on the PivotTable toolbar, point to Formulas, and then click List Formulas.
Check the order of calculation If you created more than one formula for the cells of a calculated item, make sure the order in which the formulas are calculated is correct: click PivotTable on the PivotTable toolbar, point to Formulas, and then click Solve Order.
I can't change the summary function for a calculated field.
You cannot change the summary function for a calculated field. Calculated fields always use the Sum summary function.
There are asterisks after my totals.
The asterisks indicate that subtotals and grand totals include the values for all items in the field, including any items that you have hidden.
Exclude hidden items from totals On the PivotTable toolbar, click Include Hidden Items in Totals
.
Turn the asterisks off If you want totals to include hidden items but don't want the asterisks, click PivotTable on the PivotTable toolbar, click Table Options, and then clear the Mark Totals with * check box.