'%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)
Fix Excel Charts Plotting Values for Formulas Returning Blank Strings
Question details
Users need to prevent charts from plotting unexpected data points when source cells contain formulas that output empty strings.
- Product
- Microsoft Excel / WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- Creating or modifying a chart linked to a dataset where certain cells use formulas returning empty strings instead of actual data.
- Observed behavior
- The chart plots values (such as zeros or connected lines) for formula-driven empty strings instead of leaving gaps, because the cells are not recognized as truly blank.
Verify your chart data range and identify which specific formulas in your dataset are outputting the empty strings ("").
Modify Hidden and Empty Cells Settings
Adjust how your chart handles empty or hidden cells directly in the chart settings.
Click on the chart you want to modify to bring up the Chart Design tab in the top ribbon.
Click on 'Select Data' in the Chart Design ribbon to open the Data Source dialog box.
Click the 'Hidden and Empty Cells' button located in the bottom left corner of the dialog. Choose 'Gaps' or 'Connect data points with line', then click OK.
Use the NA() Function in Formulas
Force the chart to completely ignore the data point by modifying your formulas to output a #N/A error instead of an empty string.
Easily Manage Chart Data with WPS Spreadsheet
WPS Spreadsheet provides intuitive tools for handling complex data visualizations, including seamless management of hidden and empty cells without complicated workarounds.
- 1. Open your workbook: Launch WPS Spreadsheet and open the file containing your chart.
- 2. Access Chart Tools: Click on the chart, then go to the 'Chart Tools' tab and select 'Select Data'.
- 3. Adjust Settings: Click 'Hidden and Empty Cells' and choose 'Gaps' to prevent plotting empty string formulas.
Frequently Asked Questions
Why does Excel treat empty strings as zeros in charts?
Excel evaluates formulas returning "" (empty strings) as text, not as truly blank cells. Consequently, charts interpret these non-blank text cells as zero values and plot them on the axis.
Does the 'Show empty cells as gaps' setting work for formula blanks?
Often, the 'Gaps' setting only works for genuinely empty cells (those with no data or formula). If a formula returns an empty string, you will likely need to use the NA() function workaround for the chart to properly skip the point.
Can I hide the #N/A error values in my data table while keeping the chart intact?
Yes. You can use Conditional Formatting with a rule like =ISNA(A1) and set the font color to match your cell's background color. The error becomes invisible in the sheet but still tells the chart to skip the point.
