Excel Chart Not Showing All Data? Here’s Why (and the Fix)

Your chart is plotting six months when the table has twelve. Or it’s dropping the last three products, or ignoring a whole series. Nothing’s broken — Excel is doing exactly what it was told. The problem is that “what it was told” is one of five things, and the fix depends on which one.

Here they are in order of how often they’re actually the cause. Start at the top. In most cases you’ll be done before you reach number three.

The fastest check: is a filter hiding rows?

This is the culprit maybe half the time, so check it first. A chart built from a filtered table only plots the visible rows. Filter a sales table to “East region” and every other region vanishes from the chart too — because Excel treats filtered-out rows as hidden, and hidden rows don’t plot by default.

The fix takes two seconds. Click anywhere in your data, go to the Data tab, and click Clear in the Sort & Filter group. Every filtered-out row reappears, and so does its data in the chart.

If you want the filter to stay but you also want the chart to show everything, that’s a different setting — jump to the hidden-rows fix below.

Cause 2: The chart range stops short of your data

The second most common reason is the dullest one: the chart’s source range simply doesn’t include all the rows. This happens constantly when you build a chart on ten rows of data and later paste in five more. The chart doesn’t stretch to meet the new rows — it keeps plotting the range it was born with.

To check, click the chart. Excel highlights the source range on the worksheet with a colored border. If that border stops above your last row, there’s your answer.

Right-click the chart, choose Select Data, and either drag the range handle down to cover everything or edit the range reference directly so it ends at your final row.

(If your chart range was locked to A1:B5, the May data won’t appear until you update it).

That fixes it today. But if data gets added to this sheet regularly, you’ll be back here next month. The permanent answer is to build the chart on an Excel Table (select your data and press Ctrl+T) instead of a plain range. A Table auto-extends when you add rows, and any chart built on it grows automatically. This is the same principle behind charts that update themselves — worth setting up once if this is a recurring headache.

Cause 3: Rows or columns are hidden

Someone hid rows 15–20 to tidy up the sheet, and now those points are gone from the chart. Same underlying behavior as the filter case — hidden means unplotted — but the fix is different because you may not want to unhide the data.

If you’re happy to unhide: select the rows on either side of the hidden block, right-click, and choose Unhide. The data returns to the chart.

The permanent override: “Show data in hidden rows”

If you need those rows to stay hidden on the sheet but still appear in the chart, tell Excel to plot hidden data. Right-click the chart, choose Select Data, click Hidden and Empty Cells (bottom-left of that dialog), and check Show data in hidden rows and columns. Click OK twice.

Now the chart ignores hiding entirely and plots the full range regardless. This is the setting most people are actually looking for and never find, because it’s buried three clicks deep in a dialog nobody opens.

Cause 4: Blank cells breaking the series

If the gap is inside the data rather than at the end — a line that stops halfway, a bar missing from the middle — you probably have blank cells in the source range. Excel’s default is to leave a gap where a cell is empty.

In the same Hidden and Empty Cells dialog, you get three choices for how to treat blanks: show them as gaps (the default), show them as zero, or connect data points with a line (interpolate) for line charts. Pick whichever tells the truth about your data. A genuinely missing month should stay a gap; a month that was legitimately zero should show as zero.

One trap worth knowing: a cell holding a formula that returns "" (empty text) is not blank to Excel — it counts as text, and text plots as zero. If a “blank” cell is misbehaving, check whether a formula is putting an empty string there. If it is, and you want a true gap, have the formula return =NA() instead, which suppresses the point.

Cause 5: A PivotChart filtered at the source

If your chart is a PivotChart, none of the above applies — the data is controlled by the pivot table behind it. Click the chart; if a PivotChart Analyze tab appears in the ribbon, that’s what you’ve got.

Check the pivot table itself for active filters: report filters at the top, or field filters on the rows and columns. Clear the ones you don’t want, and the chart follows. There’s no separate “chart range” to fix here; the pivot is the range.

Still nothing?

If you’ve been through all five and data is still missing, the odds are high the chart is caching something stale — the source is right but the chart won’t redraw. That’s a refresh problem, not a range problem, and it has its own set of fixes.

Want to skip the diagnosis? The working file below has each of these three main causes reproduced on its own tab, each next to a corrected version, so you can see exactly what a filtered-out, short-range, or hidden-row chart looks like — and what the fix does to it.

FAQ

Why does my Excel chart disappear when I filter the table? Because filtered-out rows are treated as hidden, and Excel doesn’t plot hidden rows by default. Either clear the filter (Data > Clear) or turn on “Show data in hidden rows and columns” in Select Data > Hidden and Empty Cells if you want the filter to stay but the chart to show everything.

How do I make my chart automatically include new rows I add? Build the chart on an Excel Table (Ctrl+T) rather than a plain cell range. Tables expand when you add rows, and charts built on them expand too — no manual range editing.

My chart is missing points in the middle, not at the end. Why? Blank cells in the source data. Open Select Data > Hidden and Empty Cells and choose whether blanks show as gaps, zeros, or connected points. Watch for formulas returning "", which Excel reads as text and plots as zero rather than leaving blank.

My PivotChart won’t show all the data even though the source has it. The filter is on the pivot table, not the chart. Clear the filters in the pivot table (check the report-filter area and the row/column field drop-downs) and the PivotChart updates to match.

Is there a setting to just always plot everything, no matter what’s hidden? Yes. Right-click the chart > Select Data > Hidden and Empty Cells > check “Show data in hidden rows and columns.” That one setting overrides both hiding and filtering for that chart.

---

Not sure the chart type is even right for what you’re plotting? Our guide to choosing the right chart covers which chart fits which kind of data.