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 series fields or page fields. Page fields use the least memory. Series fields use less memory than category fields.
Base multiple reports for the same data on one report When you create a PivotChart 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. Keep in mind that when you create a PivotChart report, its associated PivotTable report must be in the same workbook.
The report I want isn't listed in the PivotTable and PivotChart Wizard
The wizard only lists PivotTable reports If you want to base the new report on another PivotChart report, select the associated PivotTable report for the other PivotChart report. To determine which report that is, click the PivotChart report, click PivotChart on the PivotTable toolbar, and then click Options. The Name box contains the name of the associated PivotTable report.
Check the location of the report The wizard lists only the PivotTable 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.
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 Browse, and locate the .cub 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
The field buttons are gone, and I can't change the layout
Check whether the report was converted to a static chart Excel converts a PivotChart report to a static chart when you delete its associated PivotTable report, copy or move the PivotChart report to another workbook, or save the workbook in Microsoft Excel 95 or earlier format or a non-Excel format.
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 series, category, 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 series and category 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 series and category 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. On the Data menu, click PivotTable and PivotChart Report, 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 series, category, 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 chart sheet or the worksheet containing the associated PivotTable report is protected, you cannot move fields or make changes to the PivotChart report. Unprotect the chart sheet or worksheet, or contact the person who protected it.
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 next to the category fields with a dropdown arrow only when you've added two or more data fields to a report. If the report has a single data field, the field appears at the top of the report, below the page area.
Check whether the field is in an OLAP dimension In reports with OLAP source data, dimension fields in the series and category 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 in the dimension, and then click Show Levels on the shortcut menu.
The dropdown arrow for a field doesn't work
On the PivotTable toolbar, make sure Always Display Items
is turned on. If you don't want to turn on 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 a report
Formatting, trendlines, and error bars disappeared
Check for changes to the data displayed When you make changes that affect what data is displayed in a PivotChart report or its associated PivotTable report, Microsoft Excel discards any formatting you've applied to data labels, data points, and data series, including any trendlines and error bars you've added.
Changes that result in lost formatting include changing the layout, adding or removing fields, displaying or hiding items, displaying a different page in a page field, grouping or ungrouping items, displaying or hiding detail, sorting, changing the summary function for a field, changing the display of subtotals, specifying different source data including changing the query for external data, and refreshing the report.
Finish making changes before you add formatting Make sure you are satisfied with the layout and data displayed in the PivotChart report before you make formatting changes.
Record a macro to apply formatting If you change the report frequently, you can record a macro as you apply the desired formatting and then run the macro when you need to reapply your formatting.
I can't move or resize the legend, titles, or plot area
In a PivotChart report, you can't move or resize the legend, titles, or plot area as you would in a regular, noninteractive chart. Microsoft Excel automatically resizes the plot area to accommodate changes to the report.
Repositioning the legend You can't change the size of the legend, but you can change where it appears in the chart: on the Chart menu, click Chart Options, click the Legend tab, and then click an option under Placement.
Resizing titles You can't move a title, but you can change its size by changing the font size: click the title, click Selected Chart Title on the Format menu, click the Font tab, and then select the size you want.
The report doesn't use the position or size of items in my user-defined chart type
You can't move or resize the legend, titles, or plot area in a PivotChart report. Microsoft Excel automatically positions and sizes these elements as needed each time you change the report. As a result, when you specify a user-defined chart type for a PivotChart report, the report uses its automatic position and size for the legend, titles, and plot area instead of any sizes and positions you've saved in the chart type.
Number formatting doesn't match the source data
Microsoft Excel doesn't use the number formatting from Excel source data in the value axis in a PivotChart report. The value axis initially reflects the number formatting of the data area of the associated PivotTable report.
To change the value axis formatting, click the axis, click Selected Axis on the Format menu, click Number, and then select the formatting you want. This change does not affect the number formatting in the associated PivotTable report.
Data and calculations
The Source Data command is unavailable on the Chart menu
When you want to change the position of your series or category data, or redefine the source data to be included, you cannot use the Source data command on the Chart menu, as you can in a regular, noninteractive chart.
To change series fields to category fields or vice versa, drag the fields to the appropriate drop areas. To include different source data in the report, click the report, click PivotTable and PivotChart Wizard on the Data menu, click Back, and use step 2 of the wizard to specify different Excel source data or get different external source data for the report.
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 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 In the associated PivotTable report, drag the field to the row or column area, right-click any grouped items, point to Group and Show Details on the shortcut menu, click Ungroup, and then drag the field back to the page area to make the page field settings available.
While a query is running, I can't change the sheet
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 PivotChart, click Options, and then select the Background query check box.
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.
Data is missing after the report is refreshed
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: click the associated PivotTable report for the PivotChart report, click PivotTable on the PivotTable toolbar, 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.