Fix WPS Spreadsheets Scatter Charts with Blank or Empty-String Cells
Question details
The user needs to stop a WPS Spreadsheets scatter chart from treating formula-driven empty strings as problematic data and to plot blank cells correctly.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- A scatter chart is created from a source range that includes formulas returning empty strings, and the user wants blank or missing data to display cleanly.
- Observed behavior
- The chart reports that the data is too complex or plots empty-string cells differently from truly blank cells.
Open the workbook in WPS Spreadsheets and identify the chart source range. Check which cells are truly empty and which contain formulas that return an empty string before changing chart settings.
Configure Hidden and Empty Cells settings for the scatter chart
Use this when the chart source range contains blank or empty-string cells and you want to control how WPS Spreadsheets plots them without rebuilding the data.
Click the scatter chart in WPS Spreadsheets so the Chart Tools tabs appear on the ribbon.
Go to Chart Tools, click Design, and choose Select Data. You can also right-click the chart and select Select Data.
In the Select Data Source dialog, click the Hidden and Empty Cells button in the lower-left area.
Select Gaps, Zero, or Span with line for empty cells, then click OK twice to apply the setting to the scatter chart.
Use a clean helper range that returns #N/A instead of empty strings
Best when you want a stable chart source and consistent gap or interpolation behavior without changing the chart data every time.
Fix WPS Spreadsheets scatter charts with blank and empty-string cells
Use WPS Office Spreadsheets to configure empty-cell handling, replace empty-string formulas with #N/A helper ranges, and keep scatter plots clean in .xlsx workbooks.
- 1. Open your workbook in WPS Spreadsheets: Launch WPS Office and open the workbook that contains the scatter chart and formula-driven empty strings.
- 2. Select the scatter chart: Click the chart to activate Chart Tools, then click Design and choose Select Data.
- 3. Configure empty cell handling: Click Hidden and Empty Cells, choose Gaps, Zero, or Span with line, and click OK to apply.
- 4. Replace empty strings with NA if needed: Create a helper range that returns =NA() for empty-string cells and point the chart series to that range.
- 5. Save in Microsoft-compatible format: Save the workbook as .xlsx to preserve the scatter chart settings and share it with Microsoft Excel users.
Frequently Asked Questions
Why does WPS Spreadsheets say my scatter chart data is too complex when cells look empty?
The cells may contain formulas returning an empty string, which charting features can treat as text. Open Select Data, click Hidden and Empty Cells, and choose Gaps, or use a helper range that returns #N/A for missing values.
How do I make empty cells appear as gaps in a scatter chart?
Select the chart, go to Chart Tools, click Design, choose Select Data, click Hidden and Empty Cells, select Gaps, and click OK twice.
Should I use NA() instead of "" in formulas for a scatter chart?
Yes, returning #N/A from formulas usually tells WPS Spreadsheets to ignore that point instead of treating the cell as text or zero.
Can I keep my original formulas and still fix the chart?
Usually yes. First configure Hidden and Empty Cells for the chart. If the chart still misbehaves, point the series to a helper range that converts empty strings to NA().
Does WPS Office preserve these chart settings in Microsoft Excel?
Yes, WPS Spreadsheets saves .xlsx files, and the Hidden and Empty Cells settings transfer to Microsoft Excel.
