Pivot Chart Not Showing All Data? How to Fix It

A PivotChart usually misses data because its PivotTable source has not been refreshed or expanded, or because a filter, slicer, or field setting is hiding records. Refresh the PivotTable, check its source range, and clear its filters first; then inspect blanks, grouping, and chart settings if categories are still missing.

Start with the fastest checks

Before rebuilding anything, confirm that the missing records are present in the source data and that the PivotTable itself can display them. A PivotChart is tied to its associated PivotTable, so a missing item in the table will generally be missing from the chart too. If the table contains the item but the chart does not, look at chart-specific display choices and the fields used to build the chart.

Select the PivotTable and choose PivotTable Analyze > Refresh. To update every connection and PivotTable in the workbook, use Data > Refresh All.
Open the dropdowns on the PivotTable’s row, column, and report-filter fields. Choose Clear Filter where appropriate, and inspect any slicers or timelines connected to the report.
Check whether the source data includes the new rows or columns. If it does not, change the source range or convert the source into an Excel Table.
Verify that the missing records have usable values in the fields used for categories and series. Blank labels, unexpected spelling differences, and errors can change how items appear.

These checks distinguish a data-source problem from a display problem. For example, if a new product is absent from the PivotTable, expanding the chart’s visual axis will not help: the PivotTable must first be able to include that product.

How a PivotChart gets its data

A regular chart is typically linked to a cell range that you select. A PivotChart instead visualizes a PivotTable’s summarized results. Its fields come from the PivotTable layout: fields in Rows or Columns define categories or series, while fields in Values provide the plotted numbers. Report filters, slicers, and other PivotTable filters affect what the chart displays.

This relationship explains a common misconception: a PivotChart does not independently plot every row in the underlying list. If the source has 500 transactions but the PivotTable groups them by month and sums revenue, the chart normally shows monthly totals, not 500 individual transaction points. To see different detail, change the PivotTable layout—for instance, put a transaction identifier in Rows—or create a separate regular chart from an appropriate detail range.

Because the chart follows the PivotTable, removing or rearranging a field can change the chart’s categories or series. A chart that appears to have “lost” data may simply be displaying a different summary, such as totals by region rather than by product. Review the PivotTable Fields pane before treating the issue as missing source records.

Refresh the PivotTable and its source

A PivotTable does not necessarily update the moment source cells change. After editing source data, select a cell in the PivotTable and choose PivotTable Analyze > Refresh. Refresh recalculates the PivotTable from its defined source; it cannot include records that lie outside that source range. Data > Refresh All is useful when the workbook contains several PivotTables or external connections, but it still depends on each source being defined correctly and available.

Check the source definition by selecting the PivotTable and choosing PivotTable Analyze > Change Data Source. Review the range shown in the dialog. If the original source was, for example, Sheet1!$A$1:$D$200, records added in rows 201 through 230 are outside that range until it is extended. Select the complete current range in the dialog, then refresh the PivotTable.

If data comes from an external connection or the Excel Data Model, the source may be managed through the connection rather than a simple worksheet range. In that case, refresh the connection and check its query or model source. A disabled or unavailable Change Data Source command can be a clue that the PivotTable uses a connection-based source; do not assume that selecting a larger block of worksheet cells will change it.

Expand the source range when new records are missing

Fixed ranges are a frequent cause of incomplete PivotCharts. If a report was built from rows 1 through 200, appending rows beneath row 200 does not automatically make those rows part of that original range. Refreshing alone cannot fix an undersized source. Extend the range through PivotTable Analyze > Change Data Source, or use an Excel Table as the source so that added records are more readily included when the PivotTable is refreshed.

To make a Table, select a cell in the source list and choose Insert > Table, confirm the range, and indicate whether the data has headers. You can also use Home > Format as Table. When adding records, enter them directly below or beside the Table so Excel can expand it. Then refresh the PivotTable. If the PivotTable was originally created from a fixed cell range, changing the source to the Table may require using Change Data Source once; merely formatting cells as a Table does not necessarily retarget an existing PivotTable.

Also inspect the first and last rows of the selected source. A blank row in the middle can interrupt a range selected by automatic detection, while an extra title row above the actual headers can make the field list confusing. Source columns should have clear, unique headers, and each column should represent one kind of information. Those practices make the PivotTable easier to refresh and troubleshoot.

Look for filters, slicers, and hidden categories

Filters are meant to limit a report, so they can make a complete source look incomplete. Check the dropdowns on row and column labels, any report-filter area above the PivotTable, and all connected slicers and timelines. A slicer can remain filtered even when the field dropdown looks unrestricted. Use its clear-filter button, or choose the intended items, and see whether the missing category returns.

Value filters can be less obvious than selecting a category manually. A Top 10 filter, a label condition, or a value condition may exclude items that are present in the source. Open the relevant field dropdown and inspect its filter options. Also check whether the chart and table are being viewed under different states—for example, a slicer may be connected to more than one PivotTable, while another report uses a separate PivotTable that is not connected.

For categories with no records in the current period, Excel may omit the category because there is nothing to summarize. In some PivotTables, you can change this behavior by right-clicking a field item, choosing Field Settings, and checking the layout and print options, or by opening PivotTable Options > Display and enabling Show items with no data on rows or Show items with no data on columns. Availability and results depend on the data source and field arrangement. Showing an empty item does not create a value that is absent from the source; it only changes whether an eligible category is displayed.

Check the fields and summary calculation

A field placed in the wrong area can make data seem absent or unfamiliar. In the PivotTable Fields pane, confirm that the expected category is in Rows or Columns, and that the numeric measure is in Values. If a field was accidentally dragged into Filters, it may affect the report without appearing as a chart category. If a chart has too many series or categories, simplifying the field layout can also make the relevant data easier to see.

Inspect how Excel summarizes the Values field. Right-click a value in the PivotTable and choose Summarize Values By, or open Value Field Settings. Numeric data often defaults to Sum, while text or mixed data may be counted. Count is not the same as Sum: a PivotChart showing the number of populated records will not display the monetary totals a reader expects. Conversely, a sum can be misleading if values are stored as text and therefore are not being treated as numbers.

Check the source for inconsistent labels, too. “North,” “North ” with a trailing space, and “NORTH” can be treated as separate items rather than one category. A blank category may appear as (blank), and an error in a category or value field can affect the summary. Correct the source data when labels should be consistent, then refresh. Avoid deleting a category merely because it is inconvenient in the chart; first confirm that it is genuinely invalid.

Understand blanks, zeros, and missing values

A blank is not always the same thing as zero. If a source cell is empty, a sum may have nothing to plot for that intersection. If the source contains the number 0, Excel can plot a zero value, although the chart type and axis scale may make it look like no mark is present. A blank category label can instead be grouped under (blank). These are different cases and require different fixes.

Look at the corresponding PivotTable cell before changing chart options. If it is empty, inspect the source records and the PivotTable’s layout. If it contains zero, check the vertical axis scale and whether the chosen chart type makes zero values visually hard to distinguish. For a PivotTable, right-click it and choose PivotTable Options; the Layout & Format tab includes options for displaying values for empty cells. Entering a display value such as 0 changes how empty result cells look; it does not add source records or turn every missing observation into a true zero.

Be cautious when replacing missing values with zero in the source. A missing measurement may mean “not recorded,” not “none.” Treating the two as equivalent can distort totals, averages, and business conclusions. Decide what an empty cell means in the context of the data before applying a replacement.

Investigate dates, grouping, and chart appearance

Date fields can be grouped into months, quarters, or years. When a PivotTable groups dates, the chart shows those groups rather than each original date. Check the field buttons and labels to see whether the chart is displaying the desired level of detail. If dates fail to group as expected, inspect the source column for blanks, text-formatted dates, or values that are not valid dates; inconsistent source values can prevent a clean date grouping.

Chart type and axis choices can also hide differences without removing records. A line chart may connect points across a long time span, while a column chart with many categories may compress labels until they are difficult to read. A secondary scale, an unusually large value, or a narrow chart can make smaller values appear nearly flat. Try enlarging the chart, checking the axis bounds, or using a chart type suited to the categories. First verify that the PivotTable contains the expected figures so that a visual adjustment does not conceal a source problem.

PivotCharts include field buttons that allow filtering and rearranging fields from the chart. If the buttons are hidden, select the chart and use PivotChart Analyze > Field Buttons to show them. Their visibility affects how convenient it is to inspect fields, not whether the source records exist. A regular chart offers different controls and is not governed by the PivotTable’s field layout in the same way.

A practical troubleshooting sequence

Work from the source toward the chart rather than changing several settings at once. This makes it easier to identify the actual cause and reduces the chance of “fixing” the display while leaving incorrect data underneath.

Find one specific missing record in the source and confirm its category, date, and numeric value.
Check whether the record falls inside the PivotTable’s source range or connection, then refresh the PivotTable.
Look for filters in the PivotTable, report-filter fields, slicers, and timelines. Clear them temporarily if appropriate.
Inspect the PivotTable itself. If the item is absent there, investigate source, filters, grouping, and field settings. If it is present, inspect the PivotChart’s field layout and visual presentation.
Check the summary method, blank or error values, and category spelling. Correct the source where needed and refresh again.
Restore the intended report filters and confirm that the chart still communicates the result clearly.

For example, suppose a sales list gains a new “West” region in rows below the existing source range. If the PivotTable still shows only East and Central after Refresh, the first suspect is the range, not the chart design. If West appears in the PivotTable but disappears when a quarter slicer is selected, the filter state is the likely explanation. If West appears in the table with a count but not a revenue total, inspect the value field’s summary and the source values’ data types.

Prevent the same problem next time

Use a consistently structured source Table, keep one header row, avoid blank rows within the data, and add new records directly to the Table. Refresh after changes, and consider Data > Refresh All for workbooks that rely on several PivotTables or connections. Before sharing a report, check its slicers, report filters, and date range so readers know which records are currently included.

For recurring reports, document the intended filters and summary calculation. A chart can be technically correct but misleading if the reader cannot tell that it shows only one region or a particular year. Keep the PivotTable available for verification, and compare a few chart values with the underlying summary before relying on the result.

Frequently Asked Questions

Why does my PivotChart not update when I add rows?
Refresh the PivotTable, then confirm that its source includes the new rows. A fixed source range will not necessarily expand when data is appended. An Excel Table can make future additions easier to include, but an existing PivotTable may need its source changed to that Table first.

Why is a category in my PivotTable but not in my PivotChart?
Check the PivotChart’s field layout, filters, and chart appearance. The category may be filtered out, included in a field that is not being plotted, or difficult to see because the chart is crowded or its axis scale is unsuitable.

Does Refresh All include records outside the source range?
No. Refresh All updates PivotTables and connections using their defined sources; it does not automatically expand every fixed worksheet range. Extend the source or configure the PivotTable to use the intended Table or connection, then refresh.

How do I show items with no data in a PivotChart?
First check whether the PivotTable field supports showing items with no data. Depending on the source and layout, use the field’s settings or PivotTable Options on the Display tab. This can show eligible empty categories, but it does not invent measurements or restore records missing from the source.

Why does my PivotChart show counts instead of totals?
Excel may be counting a field rather than summing it, especially if the source values are text or mixed with nonnumeric entries. Open Value Field Settings or Summarize Values By, confirm the intended calculation, and ensure the source values are stored as numbers when they represent quantities.

Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.

More in Charts & pivot tables

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.