'%3e%3cpath%20d='M8%200C12.4183%200%2016%203.58172%2016%208C16%2012.4183%2012.4183%2016%208%2016C3.58172%2016%200%2012.4183%200%208C0%203.58172%203.58172%200%208%200ZM11.6162%204.38379C11.2257%203.99337%2010.5927%203.99338%2010.2021%204.38379L8%206.58594L5.79785%204.38379C5.40732%203.99334%204.77429%203.99329%204.38379%204.38379C3.99331%204.77429%203.99335%205.40733%204.38379%205.79785L6.58594%208L4.38379%2010.2021C3.99348%2010.5927%203.99341%2011.2257%204.38379%2011.6162C4.77426%2012.0066%205.40734%2012.0065%205.79785%2011.6162L8%209.41406L10.2021%2011.6162C10.5927%2012.0066%2011.2257%2012.0067%2011.6162%2011.6162C12.0067%2011.2257%2012.0066%2010.5927%2011.6162%2010.2021L9.41406%208L11.6162%205.79785C12.0066%205.40735%2012.0066%204.77429%2011.6162%204.38379Z'%20fill='%23080E17'%20fill-opacity='0.46'/%3e%3c/g%3e%3cdefs%3e%3cclipPath%20id='clip0_3761_713'%3e%3crect%20width='16'%20height='16'%20fill='white'/%3e%3c/clipPath%3e%3c/defs%3e%3c/svg%3e)
How to Stop Excel Charts from Plotting Blank Formula Results as Zero
Question details
The user needs to prevent spreadsheet charts from plotting cells containing formula-generated blank strings as zero values.
- Product
- Excel / WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- Creating a chart based on a data range where some cells use an IF formula to return an empty string when data is missing or invalid.
- Observed behavior
- The chart plots the empty string results as zeroes (creating a zero bar or dipping the line to the bottom) instead of leaving a gap, even if the 'show empty cells as gaps' setting is enabled.
Ensure you have access to the source data range of your chart and identify which formulas are currently returning empty strings ("").
Replace Empty Strings with the NA() Function
Modify your IF formulas to return an #N/A error instead of an empty string, which forces the chart engine to ignore the data point entirely.
Spreadsheet software distinguishes between a truly empty cell and a cell containing a formula. Even if the formula evaluates to an empty string (""), the cell is not empty, causing the chart to interpret the text string as a zero. Using the NA() function resolves this.
Click on the first cell in your data column that contains the formula returning an empty string (e.g., =IF(B2="", "", B2*2)).
Change the empty string "" to NA(). Your updated formula should look like: =IF(B2="", NA(), B2*2).
Press Enter, then click and drag the fill handle at the bottom-right of the cell to apply this updated formula to the rest of your data column.
Check your chart. It will now automatically ignore the #N/A values, drawing a connecting line or leaving a gap instead of dropping to zero.
Adjust Hidden and Empty Cell Chart Settings
Configure how the chart handles the gaps created by truly empty cells or #N/A errors.
Effortlessly Manage Charts and Data with WPS Spreadsheet
WPS Spreadsheet provides advanced charting capabilities that intelligently handle empty cells, #N/A values, and complex formulas. You can easily configure how hidden and empty cells are plotted without complex workarounds.
- 1. Open your data file: Launch WPS Spreadsheet and open your existing workbook containing the chart and data.
- 2. Insert or select the chart: Highlight your data and insert a chart via the 'Insert' tab, or click on your existing chart.
- 3. Open data settings: Right-click the chart and choose 'Select Data' from the dropdown menu.
- 4. Configure empty cells: Click 'Hidden and Empty Cells' to configure exactly how #N/A or blank values are displayed on your visual plot.
Frequently Asked Questions
Why does Excel treat my blank formula result as a zero?
Spreadsheet programs distinguish between a truly empty cell (which has no content whatsoever) and a cell containing a formula. Even if the formula evaluates to an empty string (""), the cell is not technically empty, causing the chart engine to interpret the underlying text string as a zero value.
How can I hide the #N/A errors in my worksheet if I use the NA() function?
You can use Conditional Formatting to hide #N/A errors. Select your data range, create a new rule using 'Format only cells that contain', choose 'Errors', and set the font color to match your cell background (usually white).
Can I make the chart line interpolate (connect) over missing data points?
Yes. Once you have replaced empty strings with the NA() function, right-click the chart, choose 'Select Data', click 'Hidden and Empty Cells', and select the 'Connect data points with line' option.
