How to Keep Excel Chart Bars Visible When Using Slicers
Question details
The user wants to prevent chart bars and categories from disappearing from the axis when a connected slicer filters out data for those specific categories.
- Product
- Spreadsheet
- Device & OS
- not provided
- Scenario
- Filtering chart data using a slicer without losing the axis categories that currently have no data.
- Observed behavior
- When a slicer filters out a category or returns no value, the corresponding chart bar and axis label completely disappear from the chart layout.
Before adjusting chart settings, ensure your chart is connected to a PivotTable or a structured dataset, and identify whether the filtered categories contain zeroes or are completely blank.
Enable 'Show Items with No Data' in PivotTable Options
If your chart is a PivotChart, you can force the PivotTable to display categories even when the slicer filters them out, keeping the chart axis intact.
PivotCharts are directly linked to PivotTables. When a slicer filters out a value, the PivotTable hides that row by default. By changing the field settings, you can force the table (and thus the chart) to retain the category label.
Right-click on the row or column field in your PivotTable that corresponds to the chart's axis categories, and select 'Field Settings' from the context menu.
In the Field Settings dialog box, click on the 'Layout & Print' tab.
Check the box next to 'Show items with no data' and click 'OK' to apply the changes.
Adjust Hidden and Empty Cell Settings in the Chart
Configure a standard chart to treat empty cells as zero values so the category remains visible on the axis.
Create and Manage Dynamic Charts with WPS Spreadsheet
WPS Office provides powerful data visualization tools. You can easily insert slicers, manage empty data points, and build interactive dashboards with full compatibility with Microsoft Excel formats.
- 1. Import Data: Open your dataset or existing Excel file in WPS Spreadsheet.
- 2. Create a PivotTable: Go to the 'Insert' tab and click 'PivotTable' to organize your raw data.
- 3. Insert Chart and Slicer: Insert a PivotChart from the 'Insert' menu and add a Slicer to dynamically filter your data.
- 4. Adjust Field Settings: Right-click the PivotTable fields, choose 'Field Settings', and check 'Show items with no data' to ensure chart axes remain intact when slicing.
Frequently Asked Questions
Why do my chart bars disappear when I click a slicer?
When a slicer filters out specific data points, the underlying PivotTable or data range hides those rows. If the chart is set to ignore hidden rows or missing data, the categories will automatically disappear from the visual layout.
Can I show a zero value instead of a gap in my standard Excel chart?
Yes. Right-click your chart, click 'Select Data', then click 'Hidden and Empty Cells'. Choose 'Zero' under the 'Show empty cells as' option to force the chart to plot a zero value.
Does 'Show items with no data' work for standard charts?
No, 'Show items with no data' is specifically a Field Setting feature for PivotTables and PivotCharts. For standard charts, you need to adjust the hidden and empty cell settings or use IF formulas in your data range to return a 0 instead of a blank.
