How Excel Charts Handle Blank Cells, Empty Strings, and #N/A Values
Question details
Understand how Excel formulas and charts treat genuinely empty cells, formula-generated empty strings, and #N/A error values.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- The user is preparing worksheet data for a chart and needs missing or unavailable values to display predictably.
- Observed behavior
- A truly empty cell, a cell containing a formula such as ="", and a cell containing #N/A are different value types that can produce different formula and chart results.
Check whether the affected cells are truly empty or contain formulas. Click a cell and review the formula bar before changing chart settings or replacing values.
Use the correct value type for missing Excel chart data
Use this method when you need formulas and charts to distinguish between blank input, a displayed empty result, and unavailable data.
A truly blank cell contains no value and no formula. A formula that returns an empty string, such as ="", is not blank: ISBLANK returns FALSE and COUNTA counts the cell. The #N/A value is an error and can propagate through dependent formulas unless it is handled explicitly.
Select a cell and look at the formula bar. If the cell has no formula and no entered value, it is genuinely empty; test it with =ISBLANK(A1) if needed.
If the formula bar contains a formula such as =IF(A1="","",A1), the cell contains a formula even when it displays nothing. Use =COUNTA(A1) or =ISBLANK(A1) to confirm how Excel evaluates it.
In the source-data formula, use =NA() or a condition such as =IF(A1="",NA(),A1) when the value should be treated as unavailable rather than as a zero or a normal blank.
Where a downstream formula should replace an unavailable value, use a targeted function such as =IFNA(A1,"") or test the value with =ISNA(A1) before calculating.
Configure how Excel charts display empty cells and #N/A values
Use this method when the chart line, bars, or markers do not display missing source data as intended.
Control Blank and #N/A Values in WPS Spreadsheet Charts
WPS Spreadsheets lets you build compatible Excel-style formulas and charts while controlling whether missing source values appear as blank results, errors, gaps, or zero values. Use formula checks and chart data settings to make chart behavior consistent before sharing an XLSX file.
- 1. Open the worksheet in WPS Spreadsheets: Open the XLSX or XLS file in WPS Office, then select the worksheet that contains the chart source data.
- 2. Classify the source cells: Click an affected cell and inspect the formula bar to determine whether it is empty, contains a formula returning "", or returns #N/A.
- 3. Apply a formula for unavailable values: Enter a formula such as =IF(A1="",NA(),A1) when an unavailable value should not be treated as ordinary numeric data.
- 4. Adjust the chart data behavior: Select the chart, open the chart data or Select Data options, and review the available settings for hidden and empty cells.
Frequently Asked Questions
Is a cell containing ="" blank in Excel?
No. The cell displays nothing, but it still contains a formula. For a cell A1 containing ="", ISBLANK(A1) returns FALSE and COUNTA(A1) returns 1.
How can I make an Excel chart skip an unavailable data point?
Return #N/A from the source formula with =NA() or =IF(condition,NA(),value). Then select the chart and use Chart Design > Select Data > Hidden and Empty Cells to review chart display settings.
Why does my Excel chart show a zero when the source cell looks empty?
The source cell may contain a formula returning an empty string or the chart may be configured to show empty cells as zero. Inspect the formula bar, then open Chart Design > Select Data > Hidden and Empty Cells and change the empty-cell option.
When should I use ISBLANK instead of ISNA in Excel?
Use ISBLANK to test for a genuinely empty cell with no formula or value. Use ISNA to test whether a formula result is the #N/A error value.
Can WPS Spreadsheets preserve Excel formulas and charts with #N/A values?
Yes. WPS Spreadsheets supports common Excel-compatible formulas, including IF, ISBLANK, ISNA, IFNA, and NA(), and can open and save XLSX and XLS workbooks with charts.
