Excel Pivot Chart Not Showing All Data? Fixes

If an Excel PivotChart is not showing all the data, first clear filters on the chart, its connected PivotTable, and any slicers, then refresh the PivotTable. If records or categories are still missing, check the PivotTable’s source range and field settings; a PivotChart displays the summarized data in its PivotTable, not every source row as a separate point.

How PivotCharts get their data

A PivotChart is tied to a PivotTable. The table groups and summarizes records—for example, sales by month and product—and the chart visualizes that summary. Filtering or rearranging fields in either the PivotTable or PivotChart can therefore change what appears in both. This connection is useful for interactive reports, but it also explains why a PivotChart behaves differently from a regular chart built directly from worksheet cells.

Suppose a source table has 500 order rows. A PivotTable might summarize them into 12 months and four product categories. The PivotChart will show those grouped results, not 500 individual orders. If you need one plotted point per order, use a regular chart based on appropriate source columns instead.

PivotCharts also follow PivotTable conventions. They generally do not plot grand totals as an extra category, and subtotals may not appear as charted points. A missing grand-total bar is not necessarily evidence that records were lost: totals are calculations over the displayed groups, not another group to plot.

Clear every filter that can hide a category

Start with the visible controls. On the PivotChart, open the field-button dropdowns and look for a selected filter, a checked subset of items, or a Top 10 filter. Clear the filter or select all relevant items. If field buttons are hidden, use the connected PivotTable’s field dropdowns instead. A filter applied to the table affects its chart, too.

Check slicers and timelines as well. A slicer can keep a subset selected even when the chart itself looks unfiltered. Select the slicer and use its clear-filter button, usually shown as a funnel with a red X. If a slicer controls several PivotTables, inspect the report connections: a slicer may affect more than one report, or may not control the PivotTable you expect.

For a quick check, use the PivotTable field dropdowns to clear filters, then inspect the chart again. On Windows, the Clear command is available from the PivotTable Analyze tab. Its precise placement can vary with the ribbon layout. Clearing filters is different from removing a field: clearing restores items, while removing a field changes the chart’s grouping.

Refresh the PivotTable and verify its source

A PivotChart cannot show records the connected PivotTable has not included. If you added rows after creating the report, refresh it first: select a cell in the PivotTable and choose PivotTable Analyze > Refresh. To refresh workbook connections and PivotTables together in Microsoft 365 for Windows, choose Data > Refresh All. On Mac, use the Data tab’s refresh commands; labels and locations can differ slightly by Excel release.

Refreshing updates data within the PivotTable’s existing source. It does not always expand a fixed cell range to include new rows. Select the PivotTable and choose PivotTable Analyze > Change Data Source to inspect the range or table name. If the source is a range such as A1:F500 and new records are in rows 501 onward, those rows may be outside the source.

For data that will grow, convert the source range to an Excel table before building the report. Select a cell in the source and choose Insert > Table, making sure the header option is correct. A PivotTable based on a table can include added table rows after refresh. If the PivotTable already uses a fixed range, changing the source to the table may be necessary; simply making the data a table later does not automatically change the old PivotTable’s source.

With an external connection or Data Model source, Change Data Source may be unavailable or work differently. Refresh the connection and check its query, connection, or model rather than assuming a worksheet range controls it. If the expected record is absent from the source query or model, the PivotChart cannot display it.

Check field layout and items with no records

A category can be absent because there are no source records for it. For example, a product launched this month may not appear in a PivotTable grouped by product if no transactions exist yet. To display categories with no data, right-click the relevant row or column field in the PivotTable, choose Field Settings, then open Layout & Print and select Show items with no data. The exact availability can depend on the source type and field arrangement.

In some layouts, the setting is also available through PivotTable Options on the Display tab, where Excel offers options for showing items with no data on rows or columns. A displayed category with no records may appear as blank or as zero, depending on the value field and settings; it does not mean Excel has created a transaction that never existed. Data Model and OLAP reports can have additional limitations.

Also look at the field wells. If a field was removed from Rows, Columns, or Legend (Series), its categories will no longer form chart groups. If a field was moved into Filters, it may limit the report without creating a separate chart category. Compare the PivotTable layout with the chart you intended to build, and place fields in the appropriate areas.

Investigate dates, blanks, and unexpected grouping

Date fields are often grouped into years, quarters, or months. A chart showing 12 month names may be aggregating records across several years, while a year filter may be excluding some periods. Expand grouped fields in the PivotTable or inspect the field dropdowns to see which levels are active. If a date is stored as text or includes invalid entries, Excel may treat it differently from genuine date values and grouping may not behave as expected.

Blank cells in a source field can produce a blank category, while inconsistent labels can split what looks like one category into several. For example, North and North followed by a space are different text values. Check the source for extra spaces, spelling variations, and blanks if a category seems missing or duplicated. Correct the source and refresh; do not assume that a chart filter is the only possible cause.

For numeric values, verify the summary calculation. A field summarized by Count will count nonblank entries rather than add the amounts. In the PivotTable, right-click a value and choose Summarize Values By to inspect choices such as Sum, Count, or Average. Changing the summary may make the chart look very different even though the underlying records have not changed.

Check chart filters, axes, and chart type

Sometimes the PivotChart contains the data but the display makes it hard to see. Open the chart’s filter controls and inspect both Categories and Series. Then check the value axis: a minimum bound set above zero can make small values appear absent, and a very large maximum can compress smaller values. Right-click the axis, choose Format Axis, and review the Bounds and Units settings. Returning bounds to automatic is a useful diagnostic.

A chart type can also conceal a series. In a stacked chart, a small segment may be difficult to distinguish; in a line chart, blank cells or categories with no values can interrupt a line. Check Chart Design > Change Chart Type and consider whether the chosen type suits the number and scale of the series. Changing the chart type does not recover records excluded by a filter or source range, so resolve those first.

For a PivotChart, chart field filters and PivotTable filters are linked to the report. The funnel button available on a regular chart is not a substitute for checking PivotTable fields, slicers, and the source. If field buttons clutter the view after troubleshooting, use PivotChart Analyze > Field Buttons to hide them; hiding controls changes the display, not the report data.

Use a regular chart when you need different behavior

A regular chart is a reasonable alternative when you need to plot individual records, combine values from unrelated ranges, or customize a chart independently of a PivotTable. Create a clean summary range with the exact categories and values required, then select it and choose Insert > Recommended Charts or another chart type. A regular chart is not automatically more complete: its selected cell range can also omit new rows, so verify the range when the source grows.

Keep the PivotChart when interactive filtering and aggregation are central to the report. If readers need to see every source record, use a table or a regular chart designed for the individual observations. If they need summarized trends and slicer-driven exploration, a PivotChart is usually the more suitable choice. The right option depends on what a plotted mark is supposed to represent.

A reliable troubleshooting order

Work from the quickest checks toward the source. This order helps separate a display issue from missing source data:

Clear field filters on the PivotChart and connected PivotTable; check slicers and timelines.
Refresh the PivotTable or use Data > Refresh All for workbook connections.
Inspect Change Data Source and confirm that newly added rows are included.
Check field placement, date grouping, blank or inconsistent labels, and the value summary calculation.
Review items with no data, chart filters, axis bounds, and the chart type.
Compare the PivotTable totals with the source or a separate check before concluding that records are missing.

For example, if a chart shows sales for only three of four regions, first clear the Region filter and any region slicer. If the fourth region still has no row in the PivotTable, verify that its records are inside the source and refresh. If the source contains the region but has no transactions for it, enable items with no data if the report needs to show that empty category. Each result points to a different cause.

Frequently Asked Questions

Why does my PivotChart show fewer rows than the source data?
A PivotChart plots the PivotTable’s summarized groups, not each source row. It may show fewer marks because many records are combined into categories, or because filters exclude groups. Use a regular chart or table if each source record needs to appear individually.

Why are new rows missing from my Excel PivotChart?
Refresh the PivotTable, then confirm its source includes the added rows. A PivotTable built from a fixed range may not expand when data is appended below it. Using an Excel table as the source makes expansion easier, but the PivotTable still needs refreshing.

How do I show categories with no data in a PivotChart?
In the connected PivotTable, right-click the category field, choose Field Settings, open Layout & Print, and select Show items with no data if available. The option and result can depend on the source type and report layout. An empty category is not the same as a category with a zero-valued transaction.

Does filtering a PivotTable also filter its PivotChart?
Yes. A PivotChart is linked to its PivotTable, so field filters generally affect the chart as well. Slicers and timelines can also filter connected reports. Clear those controls when checking whether categories are being hidden.

Why is the PivotChart missing a grand total?
PivotCharts generally do not plot PivotTable grand totals as an additional category or data point. They chart the underlying groups. If the total itself must be shown, place it in a separate cell or report element and use a regular chart or another display designed to include it.

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.