Troubleshoot charts

Data and text in charts

ShowMy xy (scatter) chart doesn't use my values along the horizontal (x) axis.

Make sure you didn't select a line chart. If your category axis displays numbers like 1, 2, 3, 4 instead of the values you want, you may have selected a line chart instead of an xy (scatter) chart when you created the chart. Delete the chart, select the worksheet data that you want to plot, and then click Chart Wizard Button image. In the Chart Wizard - Step 1 of 4 - Chart Type dialog box, click XY (scatter) in the Chart type box. If you want lines to connect the data markers (data marker: A bar, area, dot, slice, or other symbol in a chart that represents a single data point or value that originates from a worksheet cell. Related data markers in a chart constitute a data series.), click one of the xy (scatter) subtypes that has a line.

Make sure to enter the data for the xy (scatter) chart in the correct order on the worksheet. The top row (or left column) of your data selection represents the x series, and each successive row (or column) represents a y series.

Make sure that your x values are formatted as numbers. Numbers formatted as text may appear as 0, 1, 2, 3 instead of the values you want.

 Note   You can also create an xy (scatter) chart with a different x value for each set of y values. On the Series tab in the Source Data dialog box (Chart menu), select the series for which you want to change the x values. Then, in the X values box, enter a cell reference or type the x values you want, separated by commas; for example .02, .03, .04.

ShowI'm using dates in my chart, but I don't get a time-scale axis.

Time-scale option     Make sure that the Time-scale option on the Axes tab in the Chart options dialog box (Chart menu) is selected. If this option is not available, either you are not using the correct chart type or you don't have dates on the category axis of your chart.

Chart type     The time-scale axis is available only on 2-D or 3-D line, column, bar, area, or stock chart types.

PivotChart reports     The time-scale axis is not available on a PivotChart report (PivotChart report: A chart that provides interactive analysis of data, like a PivotTable report. You can change views of data, see different levels of detail, or reorganize the chart layout by dragging fields and by showing or hiding items in fields.).

Dates on category axis     Make sure the dates are on the category axis of your chart.

Multiple-level category labels     You cannot use a time-scale axis with multiple-level category labels (multiple-level category labels: Category labels in a chart that, based on worksheet data, are automatically displayed on more than one line in a hierarchy. For example, the heading Produce might appear above a row with headings Tofu, Apples, and Pears.). To change to one-level category labels in a chart created from worksheet data, do not include multiple rows of labels in the source data for the chart.

ShowText is missing along the horizontal axis of the chart.

There may not be enough room in the chart to display all of the axis labels. If some of the category names aren't visible along the horizontal axis of the chart, try one of the following:

ShowI typed new text or numbers on the worksheet, but the chart wasn't updated.

If you type text or values for the data series (data series: Related data points that are plotted in a chart. Each data series in a chart has a unique color or pattern and is represented in the chart legend. You can plot one or more data series in a chart. Pie charts have only one data series.) and categories on the Series tab of the Source Data dialog box (Chart menu, Source Data command), Microsoft Excel breaks the links between the chart and the worksheet data that the chart is based on. Excel also breaks the links between data labels (data label: A label that provides additional information about a data marker, which represents a single data point or value that originates from a worksheet cell.) and the worksheet data that the labels are based on if you type information directly in the label in the chart. You can reestablish each of these links by using one of the following methods.

Chart sheets     To reestablish links between the worksheet and a chart sheet (chart sheet: A sheet in a workbook that contains only a chart. A chart sheet is beneficial when you want to view a chart or a PivotChart report separately from worksheet data or a PivotTable report.), redefine the range of cells used to create the chart.

ShowHow?

  1. Click the chart you want to change.
  2. On the Chart menu, click Source Data, and then click the Data Range tab.
  3. Make sure the entire reference in the Data range box is selected.
  4. On the worksheet, select the cells that contain the data you want to appear in the chart.

If you want the column and row labels to appear in the chart, include the cells that contain them in the selection.

Embedded charts     To reestablish links between the worksheet and an embedded chart (embedded chart: A chart that is placed on a worksheet rather than on a separate chart sheet. Embedded charts are beneficial when you want to view or print a chart or a PivotChart report with its source data or other information in a worksheet.), redefine the cell range, or drag and resize the color-coded ranges on the worksheet to include the data you want.

Data labels     To reestablish links between worksheet data and data labels, select the Automatic text check box on the Data Labels tab of the Chart Options dialog box (Chart menu) for the selected series or the entire chart.

Additional data     If you typed additional labels and values on the worksheet that are outside the range of data that the chart is based on, you need to add the new data to the chart.

ShowHow?

To add data to a chart sheet (chart sheet: A sheet in a workbook that contains only a chart. A chart sheet is beneficial when you want to view a chart or a PivotChart report separately from worksheet data or a PivotTable report.), copy and paste the data from the worksheet to the chart.

To add data to an embedded chart (embedded chart: A chart that is placed on a worksheet rather than on a separate chart sheet. Embedded charts are beneficial when you want to view or print a chart or a PivotChart report with its source data or other information in a worksheet.) created from adjacent worksheet cells, use the color-coded ranges that surround the data on the worksheet. You can also add data to an embedded chart by dragging the data from the worksheet to the chart. If your embedded chart is created from nonadjacent selections (nonadjacent selection: A selection of two or more cells or ranges that don't touch each other. When plotting nonadjacent selections in a chart, make sure that the combined selections form a rectangular shape.), use the copy and paste procedure.

ShowAdd data to a chart by copying and pasting

  1. Select the cells that contain the data you want to add to the chart. If you want the column or row label for the new data to appear in the chart, include the cell that contains the label in the selection.
  2. Click Copy Button image.
  3. Click the chart.
  4. Do one of the following:
    • To have Microsoft Excel automatically paste the data in the chart, click Paste Button image.
    • To specify how you want to plot the data in the chart, click Paste Special on the Edit menu, and then select the options you want.

ShowAdd data to a chart by using color-coded ranges

The embedded chart must be created from adjacent selections and be on the same worksheet as the data used to create it.

  1. Type the data and labels you want to add on the worksheet in the cells next to the existing data.
  2. Select the chart area (chart area: The entire chart and all its elements.) by clicking the blank area between the border of the chart and the plot area (plot area: In a 2-D chart, the area bounded by the axes, including all data series. In a 3-D chart, the area bounded by the axes, including the data series, category names, tick-mark labels, and axis titles.).
  3. Do one of the following:

ShowAdd data to a chart by dragging

  1. Select the cells that contain the data you want to add to the chart. The cells must be next to each other on the worksheet.

If you want the column or row label for the new data to appear in the chart, include the cell that contains the label in the selection.

  1. Point to the border of the selection.

Pointer on selection border

  1. Drag the selection to the embedded chart you want to update.

If Microsoft Excel needs more information to plot the data, the Paste Special dialog box appears. Select the options you want.

 Note   If you cannot drag the selection, make sure that the Allow cell drag and drop check box is selected. To check this setting, click Options on the Tools menu, and then click the Edit tab.

PivotChart reports     A PivotChart report (PivotChart report: A chart that provides interactive analysis of data, like a PivotTable report. You can change views of data, see different levels of detail, or reorganize the chart layout by dragging fields and by showing or hiding items in fields.) is not linked to worksheet data. Instead, it uses a copy of your data that is stored in memory. If you made a change to your source data and the PivotChart report wasn't updated, try to refresh the PivotChart report.

ShowHow?

 Note   If the report is based on an offline cube file (offline cube file: A file you create on your hard disk or a network share to store OLAP source data for a PivotTable or PivotChart report. Offline cube files allow you to keep working when you are not connected to the OLAP server.), refreshing it completely rebuilds the cube file from the OLAP (OLAP: A database technology that has been optimized for querying and reporting, instead of processing transactions. OLAP data is organized hierarchically and stored in cubes instead of tables.) server and may take as long as initial creation of the cube file.

  1. Click the report.
  2. On the PivotTable toolbar (toolbar: A bar with buttons and options that you use to carry out commands. To display a toolbar, press ALT and then SHIFT+F10.), click Refresh Data Button image.
  3. If you want Microsoft Excel to refresh the report automatically when you open the workbook, set this option.

ShowHow?

  1. On the PivotTable toolbar, click PivotTable or PivotChart, and then click Table Options or Options.
  2. Select the Refresh on open check box.
  1. If the report is based on external data, and you want Excel to refresh it at timed intervals, set this option.

ShowHow?

  1. On the PivotTable toolbar, click PivotTable or PivotChart, and then click Table Options or Options.
  2. Select the Refresh every check box, and then enter the interval you want in the minutes box.

 Note   When you refresh a PivotChart report or its associated PivotTable report (associated PivotTable report: The PivotTable report that supplies the source data to the PivotChart report. It is created automatically when you create a new PivotChart report. When you change the layout of either report, the other also changes.), some chart formatting may be lost.

ShowTip

If your database prompts for a password every time you refresh, you can have Excel save the password so you won't have to enter it repeatedly. On the PivotTable toolbar, click PivotTable or PivotChart, click Table Options or Options, and then select the Save password check box. This option saves the password as readable text, so if security is a greater concern than convenience, you may not want to use this option.

ShowI published a chart, but it's empty when I view it in the browser.

You must include both x and y values in an xy (scatter) chart and x, y, and bubble size values in a bubble chart that you want to publish to the Web. Microsoft Excel supplies default values if x values are not included in your data selection. These values appear in the chart in Excel, but they are not published. If you didn't include all necessary values in your chart, type them in an adjacent column and then add them to the chart.

Formatting

ShowWhy does the font size in my chart keep changing?

ShowThere are gaps between the dates plotted in my chart.

If the worksheet data for the category axis contains date number formatting, Microsoft Excel automatically uses a special type of axis in your chart called a time-scale axis. A time-scale axis shows a blank category for dates for which you have no data. If you do not want to see these gaps — for example, if you have data for 1-Jan, 15-Jan, 3-Feb, 12-Feb, and 2-Mar, and you want to plot the days next to each other — you can change the time-scale axis to a standard category axis. Click the chart to select it, and then click Chart Options on the Chart menu. Then on the Axes tab, click Category under Category (X) axis.

ShowThe multiple-level category labels in my chart are positioned differently than when I created the chart.

Chart linked to a closed workbook     When your chart is linked to data in a workbook that is closed, multiple-level category labels (multiple-level category labels: Category labels in a chart that, based on worksheet data, are automatically displayed on more than one line in a hierarchy. For example, the heading Produce might appear above a row with headings Tofu, Apples, and Pears.) are displayed differently than when you originally created the chart. For example, the bottom level of labels may be left-aligned instead of centered. You can change the position of labels by opening the workbook that contains the source data for the chart. To do this, click Links on the Edit menu in the workbook that contains the chart. In the list, click the source file that contains the chart data, and then click Open Source. When the source workbook is open, the labels should return to their original positions.

Chart viewed in an earlier version of Microsoft Excel     Multiple-level category labels might also look different if you save your workbook by using an earlier version format and then open the workbook in an earlier version of Excel.

ShowI made or moved a chart, but I can't find it.

Do one of the following:

If you moved or created a sheet tab for the chart at the bottom of the worksheet window.

Sheet tabs

If you moved or created an embedded chart (embedded chart: A chart that is placed on a worksheet rather than on a separate chart sheet. Embedded charts are beneficial when you want to view or print a chart or a PivotChart report with its source data or other information in a worksheet.), you can locate it on the worksheet by clicking Go To on the Edit menu, and then clicking Special. Click Objects, and then click OK.

ShowThe text box, picture, or floating text I added to my user-defined chart type is not included when I create a chart.

Text boxes, pictures, and floating text on charts are not saved in user-defined charts. Add these objects after you create the chart that is based on the user-defined chart.

PivotChart reports

ShowTroubleshoot PivotChart reports

Creating a report

ShowAn 'insufficient memory' message appears

Check the memory use setting

  1. Click OK in the message dialog box.
  2. Create the report again, and in step 3 of the PivotTable and PivotChart Wizard, click Options.
  3. 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 (page field: A field that's assigned to a page orientation in a PivotTable or PivotChart report. You can either display a summary of all items in a page field, or display one item at a time, which filters out the data for all other items.) to retrieve the data as you select each item (item: A subcategory of a field in PivotTable and PivotChart reports. For instance, the field "Month" could have items such as "January," "February," and so on.).

  1. In step 3 of the wizard, click Layout.
  2. Drag one or more fields to the PAGE area.
  3. Double-click each page field.
  4. Click Advanced.
  5. 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 (source data: The list or table that's used to create a PivotTable or PivotChart report. Source data can be taken from an Excel list or range, an external database or cube, or another PivotTable report.) used for the report. When you want to use a large number of fields, add them as series fields (series field: A field that's displayed in the series area of a PivotChart report. Items in a series field are listed in the legend and provide the names of the individual data series.) or page fields. Page fields use the least memory. Series fields use less memory than category fields (category field: A field that's displayed in the category area of the PivotChart report. Items in a category field appear as the labels on the category axis.).

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 (associated PivotTable report: The PivotTable report that supplies the source data to the PivotChart report. It is created automatically when you create a new PivotChart report. When you change the layout of either report, the other also changes.) must be in the same workbook.

ShowThe 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 (associated PivotTable report: The PivotTable report that supplies the source data to the PivotChart report. It is created automatically when you create a new PivotChart report. When you change the layout of either report, the other also changes.) 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 (page field: A field that's assigned to a page orientation in a PivotTable or PivotChart report. You can either display a summary of all items in a page field, or display one item at a time, which filters out the data for all other items.) 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.

ShowI'm having problems with an OLAP data source

Creating OLAP cubes

ShowDate or time fields have incorrect levels

Check the date or time field in the source database     If you don't see the levels (level: A part of an OLAP dimension. Within a dimension, data is organized into lower and higher levels of detail, such as Year, Quarter, Month, and Day levels in a Time dimension.) you expect in the date or time dimensions (dimension: An OLAP structure that organizes data into levels, such as Country/Region/City for a Geography dimension. In a PivotTable or PivotChart report, each dimension becomes a set of fields where you can expand and collapse detail.) 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.

ShowThe 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 (query: In Query or Access, a means of finding the records that answer a particular question you ask about the data stored in a database.) 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.

ShowI can't change my OLAP cube

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.

ShowA '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 (offline cube file: A file you create on your hard disk or a network share to store OLAP source data for a PivotTable or PivotChart report. Offline cube files allow you to keep working when you are not connected to the OLAP server.) 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 (query: In Query or Access, a means of finding the records that answer a particular question you ask about the data stored in a database.) 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.

ShowSaving 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.

ShowI 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:

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

ShowData 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.

ShowMy 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 Button image on the PivotTable toolbar.

ShowData 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 (refresh: To update the contents of a PivotTable or PivotChart report to reflect changes to the underlying source data. If the report is based on external data, refreshing runs the underlying query to retrieve new or changed data.) 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.

ShowNew data doesn't appear in my report when I refresh

The offline cube file (offline cube file: A file you create on your hard disk or a network share to store OLAP source data for a PivotTable or PivotChart report. Offline cube files allow you to keep working when you are not connected to the OLAP server.), 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.

ShowMicrosoft 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

ShowThe field buttons are gone, and I can't change the layout

Check whether the buttons are hidden     Click the report, click PivotChart on the PivotTable toolbar, and make sure the Hide PivotChart Field Buttons command is not checked.

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 (associated PivotTable report: The PivotTable report that supplies the source data to the PivotChart report. It is created automatically when you create a new PivotChart report. When you change the layout of either report, the other also changes.), 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.

ShowData 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 (data area: The part of a PivotTable report that contains summary data. Values in each cell of the data area represent a summary of data from the source records or rows.) 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 Button image is turned off. The items (item: A subcategory of a field in PivotTable and PivotChart reports. For instance, the field "Month" could have items such as "January," "February," and so on.) won't appear as you drag fields to the series and category areas, but will appear once you add a data field (data field: A field from a source list, table, or database that contains data that is summarized in a PivotTable report or PivotChart report. A data field usually contains numeric data, such as statistics or sales amounts.).

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.

ShowI can't drag a field

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 (OLAP: A database technology that has been optimized for querying and reporting, instead of processing transactions. OLAP data is organized hierarchically and stored in cubes instead of tables.) source data, some fields can be used only as series (series field: A field that's displayed in the series area of a PivotChart report. Items in a series field are listed in the legend and provide the names of the individual data series.), category (category field: A field that's displayed in the category area of the PivotChart report. Items in a category field appear as the labels on the category axis.), or page fields (page field: A field that's assigned to a page orientation in a PivotTable or PivotChart report. You can either display a summary of all items in a page field, or display one item at a time, which filters out the data for all other items.). These fields have Dimension field icon icons in the PivotTable Field List window. Fields with Data field icon icons can be used only as data fields (data field: A field from a source list, table, or database that contains data that is summarized in a PivotTable report or PivotChart report. A data field usually contains numeric data, such as statistics or sales amounts.).

Check for VBA macros     A Visual Basic for Applications (VBA) macro (macro: An action or a set of actions that you can use to automate tasks. Macros are recorded in the Visual Basic for Applications programming language.) 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 (associated PivotTable report: The PivotTable report that supplies the source data to the PivotChart report. It is created automatically when you create a new PivotChart report. When you change the layout of either report, the other also changes.) 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.

ShowThe field list is missing

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 Button image on the PivotTable toolbar.

ShowA field doesn't have a dropdown arrow

Check whether it's the only data field     The Data field appears next to the category fields (category field: A field that's displayed in the category area of the PivotChart report. Items in a category field appear as the labels on the category axis.) with a dropdown arrow only when you've added two or more data fields (data field: A field from a source list, table, or database that contains data that is summarized in a PivotTable report or PivotChart report. A data field usually contains numeric data, such as statistics or sales amounts.) 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 (OLAP: A database technology that has been optimized for querying and reporting, instead of processing transactions. OLAP data is organized hierarchically and stored in cubes instead of tables.) source data, dimension (dimension: An OLAP structure that organizes data into levels, such as Country/Region/City for a Geography dimension. In a PivotTable or PivotChart report, each dimension becomes a set of fields where you can expand and collapse detail.) fields in the series and category areas have an arrow Field arrow in the field button (field button: Button that identifies a field in a PivotTable or PivotChart report. You can drag the field buttons to change the layout of the report, or click the arrows next to the buttons to change the level of detail displayed in the report.) 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 (level: A part of an OLAP dimension. Within a dimension, data is organized into lower and higher levels of detail, such as Year, Quarter, Month, and Day levels in a Time dimension.) of a dimension are hidden, the topmost field is hidden, and none of the displayed fields have the arrow Field arrow. Right-click any field button in the dimension, and then click Show Levels on the shortcut menu.

ShowThe dropdown arrow for a field doesn't work

On the PivotTable toolbar, make sure Always Display Items Button image is turned on. If you don't want to turn on this feature, drag a field to the data area (data area: The part of a PivotTable report that contains summary data. Values in each cell of the data area represent a summary of data from the source records or rows.). Once you have a field in the data area, the dropdown arrows will work for all fields in the report.

Formatting a report

ShowFormatting, 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 (associated PivotTable report: The PivotTable report that supplies the source data to the PivotChart report. It is created automatically when you create a new PivotChart report. When you change the layout of either report, the other also changes.), Microsoft Excel discards any formatting you've applied to data labels (data label: A label that provides additional information about a data marker, which represents a single data point or value that originates from a worksheet cell.), data points (data points: Individual values that are plotted in a chart. Related data points make up a data series. Data points are represented by bars, columns, lines, slices, dots, and other shapes. These shapes are called data markers.), and data series (data series: Related data points that are plotted in a chart. Each data series in a chart has a unique color or pattern and is represented in the chart legend. You can plot one or more data series in a chart. Pie charts have only one data series.), including any trendlines (trendline: A graphic representation of trends in data series, such as a line sloping upward to represent increased sales over a period of months. Trendlines are used for the study of problems of prediction, also called regression analysis.) and error bars (error bars: Usually used in statistical or scientific data, error bars show potential error or degree of uncertainty relative to each data marker in a series.) you've added.

Changes that result in lost formatting include changing the layout, adding or removing fields (field: In a PivotTable or PivotChart report, a category of data that's derived from a field in the source data. PivotTable reports have row, column, page, and data fields. PivotChart reports have series, category, page, and data fields.), displaying or hiding items (item: A subcategory of a field in PivotTable and PivotChart reports. For instance, the field "Month" could have items such as "January," "February," and so on.), displaying a different page in a page field (page field: A field that's assigned to a page orientation in a PivotTable or PivotChart report. You can either display a summary of all items in a page field, or display one item at a time, which filters out the data for all other items.), grouping or ungrouping items, displaying or hiding detail, sorting, changing the summary function (summary function: A type of calculation that combines source data in a PivotTable report or a consolidation table, or when you are inserting automatic subtotals in a list or database. Examples of summary functions include Sum, Count, and Average.) for a field, changing the display of subtotals, specifying different source data (source data: The list or table that's used to create a PivotTable or PivotChart report. Source data can be taken from an Excel list or range, an external database or cube, or another PivotTable report.) including changing the query (query: In Query or Access, a means of finding the records that answer a particular question you ask about the data stored in a database.) for external data, and refreshing (refresh: To update the contents of a PivotTable or PivotChart report to reflect changes to the underlying source data. If the report is based on external data, refreshing runs the underlying query to retrieve new or changed data.) 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 (macro: An action or a set of actions that you can use to automate tasks. Macros are recorded in the Visual Basic for Applications programming language.) as you apply the desired formatting and then run the macro when you need to reapply your formatting.

ShowI can't move or resize the legend, titles, or plot area

In a PivotChart report, you can't move or resize the legend (legend: A box that identifies the patterns or colors that are assigned to the data series or categories in a chart.), titles (titles in charts: Descriptive text that is automatically aligned to an axis or centered at the top of a chart.), or plot area (plot area: In a 2-D chart, the area bounded by the axes, including all data series. In a 3-D chart, the area bounded by the axes, including the data series, category names, tick-mark labels, and axis titles.) 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.

ShowThe report doesn't use the position or size of items in my user-defined chart type

You can't move or resize the legend (legend: A box that identifies the patterns or colors that are assigned to the data series or categories in a chart.), titles (titles in charts: Descriptive text that is automatically aligned to an axis or centered at the top of a chart.), or plot area (plot area: In a 2-D chart, the area bounded by the axes, including all data series. In a 3-D chart, the area bounded by the axes, including the data series, category names, tick-mark labels, and axis titles.) 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.

ShowNumber 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 (data area: The part of a PivotTable report that contains summary data. Values in each cell of the data area represent a summary of data from the source records or rows.) of the associated PivotTable report (associated PivotTable report: The PivotTable report that supplies the source data to the PivotChart report. It is created automatically when you create a new PivotChart report. When you change the layout of either report, the other also changes.).

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

ShowThe 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 (series field: A field that's displayed in the series area of a PivotChart report. Items in a series field are listed in the legend and provide the names of the individual data series.) to category fields (category field: A field that's displayed in the category area of the PivotChart report. Items in a category field appear as the labels on the category axis.) or vice versa, drag the fields to the appropriate drop areas (drop area: An area in a PivotTable or PivotChart report where you can drop fields from the Field List dialog box to display the data in the field. The labels on each drop area indicate the types of fields you can create in the report.). 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.

ShowThe page field options are unavailable.

Check the type of field     The field (field: In a PivotTable or PivotChart report, a category of data that's derived from a field in the source data. PivotTable reports have row, column, page, and data fields. PivotChart reports have series, category, page, and data fields.) 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 (OLAP: A database technology that has been optimized for querying and reporting, instead of processing transactions. OLAP data is organized hierarchically and stored in cubes instead of tables.) source data. The field list has Dimension field icon and Data field icon 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 (OLE: A program-integration technology that you can use to share information between programs. All Office programs support OLE, so you can share information through linked and embedded objects.) objects.

Check for parameter query support     The ODBC driver (Open Database Connectivity (ODBC) driver: A program file used to connect to a particular database. Each database program, such as Access or dBASE, or database management system, such as SQL Server, requires a different driver.) for your external database might not support parameter queries (parameter query: A type of query that, when you run it, prompts for values (criteria) to use to select the records for the result set so that the same query can be used to retrieve different result sets.). These settings are available only if the driver for your data source (data source: A stored set of "source" information used to connect to a database. A data source can include the name and location of the database server, the name of the database driver, and information that the database needs when you log on.) 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 (associated PivotTable report: The PivotTable report that supplies the source data to the PivotChart report. It is created automatically when you create a new PivotChart report. When you change the layout of either report, the other also changes.), drag the field to the row or column area, right-click any grouped items (item: A subcategory of a field in PivotTable and PivotChart reports. For instance, the field "Month" could have items such as "January," "February," and so on.), 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.

ShowWhile 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 refresh indicator 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.

ShowA 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 (summary function: A type of calculation that combines source data in a PivotTable report or a consolidation table, or when you are inserting automatic subtotals in a list or database. Examples of summary functions include Sum, Count, and Average.) by default. Double-click the field and click Sum in the Summarize by box.

Check for OLAP source data     In reports based on OLAP (OLAP: A database technology that has been optimized for querying and reporting, instead of processing transactions. OLAP data is organized hierarchically and stored in cubes instead of tables.) 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 Dimension field icon and Data field icon icons in OLAP-based reports.

ShowData is missing after the report is refreshed

Set page fields to view all data     Select All in every page field (page field: A field that's assigned to a page orientation in a PivotTable or PivotChart report. You can either display a summary of all items in a page field, or display one item at a time, which filters out the data for all other items.) 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 (source data: The list or table that's used to create a PivotTable or PivotChart report. Source data can be taken from an Excel list or range, an external database or cube, or another PivotTable report.) 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.

  1. On the Data menu, click PivotTable and PivotChart Report.
  2. Click Back.
  3. Change the source range specification to include the new data or to specify the new location.
  4. 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 (cube: An OLAP data structure. A cube contains dimensions, like Country/Region/City, and data fields, like Sales Amount. Dimensions organize types of data into hierarchies with levels of detail, and data fields measure quantities.) 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 (associated PivotTable report: The PivotTable report that supplies the source data to the PivotChart report. It is created automatically when you create a new PivotChart report. When you change the layout of either report, the other also changes.) 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.

ShowThe 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 (OLAP: A database technology that has been optimized for querying and reporting, instead of processing transactions. OLAP data is organized hierarchically and stored in cubes instead of tables.) 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.

 
 
Applies to:
Excel 2003