Chart & Visualization Issues

How to Create an Excel Scatterplot with Empty or Formula-Based Cells

Adam Davis Sep 28, 2026 869 views

Question details

The user needs to create an accurate scatterplot from a dataset where some seemingly blank cells contain formulas, preventing the chart from plotting them as zero values.

How to Create an Excel Scatterplot with Formula-Based Empty Cells

Product: Excel
Device & OS: not provided
Scenario: Creating a scatterplot from filtered or pivot-based data where some cells use formulas to return an empty text string when data is missing.
Observed behavior: Excel interprets the formula-generated empty strings as text, plotting them as unexpected zero points on the scatterplot instead of ignoring them.

Before you start

Verify your data range to identify which cells use formulas that output empty strings ("") instead of actual numeric values.

Solution 1Recommended

Use the NA() Function to Exclude Data Points

Replace empty strings in your formulas with the #N/A error so the scatterplot correctly skips plotting those missing values.

Cells containing formulas are never truly blank, even if they return an empty string (""). Because Excel treats this empty string as text, the scatterplot plots it as a zero, skewing your visualization. By forcing the formula to return an #N/A error, Excel recognizes the data is missing and safely excludes it from the chart.

Locate the formula

Select the first cell in your data column that uses an IFERROR or IF statement returning an empty string (e.g., =IFERROR(B2, "")).

Modify the output to NA()

Click into the formula bar and change the empty quotes "" to the NA() function. Your new formula should look like this: =IFERROR(B2, NA()).

Apply the updated formula

Press Enter, then click and drag the fill handle at the bottom-right corner of the cell to apply this updated formula to the rest of your data column.

Review the scatterplot

Check your scatterplot. Excel will automatically exclude the rows returning the #N/A error, ensuring only valid X and Y data points appear on the chart.

Use the NA() Function to Exclude Data Points

Verify with ISBLANK: If you are unsure whether a cell is truly empty, use the =ISBLANK() formula. A cell containing a formula that returns "" will result in FALSE.

Advanced Data Visualization

Create Professional Scatterplots Seamlessly in WPS Spreadsheet

WPS Spreadsheet provides powerful data processing and charting capabilities that handle complex formula outputs perfectly. You can manage missing data and create accurate scatterplots with ease.

1. Open data in WPS Spreadsheet: Launch WPS Office and open your .xlsx data file containing the formula-based dataset.
2. Apply the NA() adjustment: Update your data formulas to return NA() for missing values instead of empty strings.
3. Insert the scatter chart: Highlight your data range, navigate to the Insert tab on the ribbon, and click on the Scatter Chart icon.
4. Customize your visualization: Use the Chart Elements menu to adjust titles, axes, and legends for a professional finish.

Fully compatible with Microsoft Excel formulas, including IF, IFERROR, and NA().Accurately renders scatterplots by intelligently handling #N/A errors without plotting zeros.Free and lightweight alternative to heavy Office suites with an intuitive user interface.Offers one-click chart customization and seamless file format compatibility (.xlsx).

microsoft office alternative - wps office

Frequently Asked Questions

Why does my Excel scatterplot drop to zero when a cell looks blank?

If a cell appears blank but contains a formula returning an empty string (""), Excel treats it as a text value and plots it as a zero. You must alter the formula to return #N/A to prevent this.

Can I hide the #N/A error in my worksheet cells while keeping the chart accurate?

Yes. You can use Conditional Formatting to hide #N/A errors. Highlight your data, create a Conditional Formatting rule for errors, and set the text color to match the cell's background color (e.g., white).

Does replacing empty strings with NA() work for line charts too?

Yes, using the NA() function prevents data points from dropping to the zero axis in line charts just as it does for scatterplots.

Excel & Data Problems