Excel Pivot Chart Show Zero Values? Fixes That Work

Yes. An Excel PivotChart can show zero values, but the right fix depends on whether the PivotTable contains a true zero, an empty result, or no category at all. Start by checking the PivotTable: use its empty-cell display setting for blanks, include missing categories when appropriate, and then check chart labels and formatting.

Why zero values can seem to disappear

A PivotChart is linked to its PivotTable, so it charts the values and categories the PivotTable provides. A zero is a numeric value, but it has no visible bar height in a column or bar chart. On a line chart, it may sit exactly on the axis baseline. That means a zero can be present even when it does not look like a mark on the chart.

Other situations that look similar have different causes:

The PivotTable cell is blank. The source records may be empty for that intersection, or the field combination may have no matching records.
The category is missing. A month, product, or department with no records may not appear as a PivotTable row or column, so the chart has no category to plot.
The chart hides the zero visually. A zero-height column, a data label that is turned off, or a number format that suppresses zero can make a real zero hard to see.
The source value is not numeric zero. A blank cell, text such as “0”, or a formula returning an empty string is not necessarily treated the same as the number 0.

Look at the matching PivotTable cell before changing the chart. It is the quickest way to identify which case applies.

Show blank PivotTable results as zero

If the relevant PivotTable cell is empty and you want it to display as 0, change the PivotTable’s empty-cell display setting. In Excel for Microsoft 365 on Windows:

Right-click a cell inside the PivotTable and choose PivotTable Options.
Open the Layout & Format tab.
Under Format, select For empty cells show, then enter 0 in the box.
Select OK and check both the PivotTable and its chart.

This setting changes how empty PivotTable value cells are displayed; it does not add source records or convert every blank in the source data into a numeric zero. If the chart still looks unchanged, inspect its labels and the category list as well. The option is useful when an empty intersection should communicate “zero” in the report, but it can also make a genuinely unknown or unreported value look like a measured zero. Choose it only when that meaning is appropriate.

Include categories that have no records

If an entire category is absent—for example, April is missing because no April transactions were recorded—displaying empty cells as zero may not be enough. There must first be a category for the PivotTable and chart to display. You can ask a PivotTable field to show items with no data, where that option is available.

In Windows, right-click an item in the relevant row or column field and select Field Settings. Check the Layout & Print tab for Show items with no data, then confirm. The exact availability depends on the field and the PivotTable’s data source. You can also check the Display tab in PivotTable Options for options to show items with no data on rows or columns.

This displays items Excel already knows about; it does not automatically invent every possible category. For example, if the source has no April date and the PivotTable has no calendar table or date grouping that includes April, there may be no April item to show. For a reliable month-by-month report, use a complete date list or calendar table as part of the source model, then relate or combine it with the transaction data as appropriate. After enabling missing items, use the empty-cell setting if those newly displayed intersections should read as zero.

Make a real zero visible on the chart

When the PivotTable already displays 0, the data may be correct and only the chart presentation needs attention. A zero-value column has no height, so it cannot look like a normal positive bar. Add a label if the viewer needs to see the number explicitly: select the chart, then choose Chart Design > Add Chart Element > Data Labels and select a suitable position. Check whether labels appear for the zero points; available label positions vary by chart type.

Also check the number format. A custom format can deliberately hide zero values. For example, the format 0;-0;;@ leaves the third section—the section for zero—empty, so zeros are not printed. Select the relevant PivotTable values or chart labels, open their number-format settings, and choose a format that displays zero, such as 0, if that matches the report’s needs. A format such as 0;-0;0;@ explicitly displays zero in the zero section.

For a line chart, look at the value axis and the location of the zero baseline. If the axis minimum is set above zero, a zero may fall outside the displayed range; set an appropriate minimum or return the axis bounds to automatic. A zero can also overlap an axis or another label. Do not change the data to a small positive number just to make a mark appear: that would misrepresent the value.

Check the source data and value calculation

Before changing display options, confirm what the PivotTable is calculating. Select a PivotTable value and open Value Field Settings to review the summary calculation, such as Sum, Count, or Average. A Sum of numeric zeroes is zero, but a field with no matching numeric values may produce a blank result. Count counts nonblank entries, so it can also behave differently from a sum when source cells are empty.

Check representative source rows as well. A cell containing the number 0 is different from a blank cell, and text that looks like zero may not be included in a numeric sum. A formula that returns an empty string can also look blank while not being an ordinary empty cell. If the source data was changed after the PivotTable was created, refresh the PivotTable using PivotTable Analyze > Refresh. Refreshing updates the PivotTable from its source; it will not create categories or values that are absent from that source.

For a quick diagnostic, follow one missing chart point back to its PivotTable intersection, then identify the source rows that contribute to it. This separates calculation problems from display problems and avoids applying a chart setting to data that was never zero.

Understand the effect of chart type

Chart type affects how easy it is to notice zero. In a column or bar chart, a zero has no bar length. In a line chart, the point is on the zero level, but markers may be disabled or obscured. In an area chart, a zero contributes no filled area at that position. A data label can make the numeric value explicit, although crowded labels may reduce readability.

For comparisons where zero matters, consider a chart with clear category labels and visible markers or data labels rather than relying only on bar height. If many categories have no activity, a table alongside the chart can communicate exact values more clearly. Do not switch to a chart type that implies a different relationship merely to make zeros easier to see; choose one that suits the data and comparison.

Refresh and verify the PivotChart

After changing source data or PivotTable settings, refresh and verify the report. On Windows, select a PivotTable cell and choose PivotTable Analyze > Refresh. If the workbook has several PivotTables using the same source, Data > Refresh All can refresh workbook connections and reports. Then check that the target category appears, the PivotTable cell displays the intended value, and the PivotChart shows the category or label as expected.

Remember that a chart’s appearance can be affected by filters. Check the PivotChart field buttons, slicers, and report filters to make sure the category has not been filtered out. Also check whether the chart is showing the intended value field; a PivotTable can contain multiple measures, and the chart may be plotting a different one than expected. A filter change can remove a category even when its source records remain in the workbook.

Windows, Mac, and Excel for the web

The instructions above describe Excel for Microsoft 365 on Windows. In Excel for Mac, select the PivotTable and look for the PivotTable Analyze tab and PivotTable options; ribbon placement and dialog wording can vary by build. If a particular field option is unavailable, the data source or PivotTable type may not support it. Excel for the web has fewer PivotTable configuration controls than the desktop app, so open the workbook in desktop Excel if you cannot find the empty-cell or field display setting.

PivotCharts are tied to their PivotTables, unlike ordinary charts whose source ranges can usually be edited directly. If you need chart behavior that a PivotChart does not offer, you can build a separate ordinary chart from a helper range linked to the PivotTable. Keep that range and chart synchronized when filters or PivotTable layout change; otherwise, the chart can become misleading or stale.

Frequently Asked Questions

Can an Excel PivotChart display zero values?
Yes. A true zero can be plotted, but it may have no visible bar height or may sit on the axis. Add data labels or use a chart format that shows zero, and confirm that the PivotTable includes the relevant category.

How do I show zero instead of a blank in a PivotTable?
Right-click inside the PivotTable, choose PivotTable Options, open Layout & Format, and enter 0 beside For empty cells show. This displays empty value cells as zero; it does not add missing source records.

Why is a month with zero sales missing from my PivotChart?
The month may not exist as an item in the PivotTable field, or it may be filtered out. Show items with no data where available, check filters, and make sure the source or model contains a complete list of months if you need months with no transactions.

Why does my zero show in the PivotTable but not on the chart?
A zero-height column or bar can be visually indistinguishable from no mark, and chart labels may be off or formatted to suppress zero. Add data labels and check the chart’s number format and axis bounds.

Does “For empty cells show 0” change my source data?
No. It changes how empty cells in the PivotTable are displayed. The underlying source remains unchanged, and a blank or missing value should not be treated as a real measured zero unless that is correct for the report.

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.