Chart & Visualization Issues

How to Remove Gaps in Excel Stacked Column Charts with Hidden Data

Emma Brown Oct 10, 2026 868 views

Question details

The user needs to eliminate unwanted gaps and narrow columns in an Excel stacked column chart that appear when source data rows or columns are hidden.

Product: Microsoft Excel
Device & OS: not provided
Scenario: Creating or formatting a two-dimensional stacked column chart while hiding certain data rows or columns in the source worksheet.
Observed behavior: Excel treats the hidden data as empty cells, which causes the chart to display blank spaces (gaps) and unusually narrow columns in place of the hidden data points.

Before you start

Verify which rows or columns in your source dataset are currently hidden, and select your stacked column chart to activate the Chart Design tools.

Solution 1Recommended

Adjust Hidden and Empty Cells Settings in Chart Design

Change how Excel handles hidden data within your chart settings to prevent it from reserving empty spaces for hidden rows or columns.

Excel's default behavior is to ignore hidden data but sometimes retains the space on the chart axis. Adjusting the specific setting for hidden and empty cells can quickly resolve this.

Select the chart

Click on the stacked column chart you want to modify to reveal the 'Chart Design' and 'Format' tabs on the Excel ribbon.

Open Select Data settings

Navigate to the 'Chart Design' tab and click on the 'Select Data' button located in the Data group.

Access Hidden and Empty Cells

In the Select Data Source dialog box, click the 'Hidden and Empty Cells' button located at the bottom left corner.

Modify the setting

Choose the appropriate option for how hidden and empty cells should be displayed. If you want the hidden data to still show without gaps, check the 'Show data in hidden rows and columns' box, then click OK.

Adjust Hidden and Empty Cells Settings in Chart Design

Grayed Out Options: You may notice that 'Connect data points with line' is grayed out. This is perfectly normal for a stacked column chart, as this specific setting only applies to line and scatter charts.

Solution 2

Create a Helper Range for the Chart Source

Use a separate, condensed range of data containing only the visible totals to feed the chart, completely bypassing Excel's hidden data mechanics.

Create Professional Charts Easily

Easily Manage Charts and Hidden Data with WPS Spreadsheet

WPS Spreadsheet provides intuitive tools for creating stunning charts and offers precise control over how hidden data is displayed, ensuring you never have to deal with unexpected visual gaps in your reports.

1. Open your data: Launch WPS Spreadsheet and open your workbook containing the chart data.
2. Insert a stacked chart: Highlight your desired data range, go to the 'Insert' tab, click 'Chart', and select a Stacked Column Chart.
3. Adjust hidden data settings: Right-click the chart, choose 'Select Data', and click 'Hidden and Empty Cells' to easily configure how hidden rows are shown.

Seamlessly handles hidden rows and columns in stacked charts without unwanted gaps.Fully compatible with Microsoft Excel (.xlsx) file formats and complex chart structures.Free, lightweight, and features a familiar, easy-to-use interface for data visualization.

microsoft office alternative - wps office

Frequently Asked Questions

Why is the 'Connect data points with line' option grayed out in Excel?

This option is designed specifically for line charts and scatter plots. Since stacked column charts do not use continuous lines to connect data points, Excel disables this option. You should adjust the empty cell display behavior or use a helper range instead.

Can I force my chart to display data even if the rows are hidden?

Yes. By default, Excel removes data from charts if the corresponding rows or columns are hidden. You can change this by selecting your chart, navigating to Chart Design > Select Data > Hidden and Empty Cells, and checking the 'Show data in hidden rows and columns' option.

Why are the columns in my stacked chart unusually narrow?

Unusually narrow columns typically occur because Excel is still reserving horizontal space on the X-axis for your hidden data points, treating them as blank categories. Modifying your hidden cell settings or structuring a clean helper data range without hidden rows will fix the spacing.

Excel & Data Problems