Troubleshoot charts

Microsoft Office Excel 2003

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

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

  • If the chart is an embedded chart, click the chart, and then drag one of the black sizing handles to enlarge the chart. By default, the fonts in the chart scale proportionally as you resize the chart.
  • Click the axis, click the arrow next to Font Size Font size box, and then click a smaller font size.
  • Double-click the axis labels, and then change the rotation angle on the Alignment tab. If the angle is 0, 90, or – 90 degrees, the text will wrap.

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

Show How?

To add data to a chart sheet, copy and paste the data from the worksheet to the chart.

To add data to an embedded chart 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, 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 by clicking the blank area between the border of the chart and the plot area.

  3. Do one of the following:

    • To add new categories and data series to the chart, on the worksheet, drag the blue sizing handle to include the new data and labels in the rectangle.

    • To add new data series only, on the worksheet, drag the green sizing handle to include the new data and labels in the rectangle.

    • To add new categories and data points, on the worksheet, drag the purple sizing handle to include the new data and labels in the rectangle.

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.

  2. Point to the border of the selection.

    Pointer on selection border

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

Show How?

Note  If the report is based on an offline cube file, refreshing it completely rebuilds the cube file from the OLAP server and may take as long as initial creation of the cube file.

  1. Click the report.

  2. On the PivotTable toolbar, 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.

  4. 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, 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?

By default, fonts in a chart scale proportionally when you resize an embedded chart or the chart area of a chart sheet. If you want the font sizes in a chart to remain constant, select the chart area by clicking the blank area between the border of the chart and the plot area. On the Format menu, click Selected Chart Area, click the Font tab, and then clear the Auto scale check box.

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 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, 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 to retrieve the data as you select each item.

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

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

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

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

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.

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

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:

  • 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 Plus box 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 Plus box box next to Measures, and select only the data fields you're using in the report.

  • Eliminate items of data    Click the Plus box 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

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

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 source data, some fields can be used only as series, category, or page fields. 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.

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.

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 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 Field 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 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. 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, 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.

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

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

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

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

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

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

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

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