How to Create an Excel Chart Without Gaps for Blank Cells
Question details
The user wants to generate an Excel chart from a dataset with missing entries (such as an incomplete list of U.S. states) without displaying gaps for the blank cells.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Creating a clean, continuous chart from an incomplete dataset containing blank rows or missing values.
- Observed behavior
- By default, the chart displays gaps, drops to zero, or leaves empty spaces on the axis where data is missing, making the visualization look disconnected.
Before modifying your chart, ensure your dataset doesn't contain hidden spaces or zero values masquerading as blanks, as Excel treats these differently than truly empty cells.
Filter the Dataset to Exclude Blanks
Apply a standard filter to your source data to temporarily hide rows with blank states, which automatically removes them from the chart.
The simplest way to remove gaps from a chart is to prevent the blank cells from feeding into the chart in the first place. By default, Excel charts do not display data from hidden rows.
Highlight your entire data range, including the headers for the states and their corresponding values.
Navigate to the Data tab on the Excel ribbon and click the 'Filter' button to add dropdown arrows to your headers.
Click the dropdown arrow on your values column, uncheck the box next to '(Blanks)', and click OK.
The chart will immediately update to exclude the filtered rows, completely removing the empty gaps on the axis.
Adjust Hidden and Empty Cell Settings
Use Excel's built-in chart options to connect data points with lines instead of leaving broken gaps.
Use the FILTER Formula for a Helper Range
Extract only the non-blank rows into a new dynamic range using the FILTER function, then build your chart from this clean dataset.
Use WPS Spreadsheet to Create Gap-Free Charts Effortlessly
WPS Office provides an intuitive spreadsheet application that allows you to manage data, apply filters, and customize chart settings to easily handle blank cells and missing data without complex formulas.
- 1. Open your dataset: Launch WPS Spreadsheet and open the file containing your incomplete states data.
- 2. Insert your chart: Highlight your data range and navigate to the Insert tab to add your preferred chart.
- 3. Remove the gaps: Right-click the chart, choose 'Select Data', and adjust the empty cell settings, or use the Data tab to filter out blank rows directly.
Frequently Asked Questions
Why does my Excel chart show zeros instead of gaps for empty cells?
Excel often interprets formulas that return an empty string ("") as a zero value. To fix this, change your formula to return #N/A instead (e.g., `=IF(A1="", NA(), A1)`). Excel charts natively ignore #N/A errors and will bridge the gap.
Does hiding rows in the spreadsheet affect the chart?
Yes, by default, charts do not display data located in hidden rows or columns. Simply filtering or manually hiding the row containing the blank state will instantly remove the gap from your visualization.
How do I stop a line chart from dropping to zero when data is missing?
Go to Chart Design > Select Data > Hidden and Empty Cells, and choose 'Connect data points with line'. This tells Excel to draw a line straight from the last known value to the next, bridging the empty space completely.
Can I make the chart dynamic so it automatically ignores new blanks?
Yes. Formatting your source data as an Excel Table and using a Pivot Chart is a great way to handle this. You can apply a value filter to the Pivot Chart to exclude blanks, and it will remain gap-free even as new data is added.
