How to Create a WPS Office Spreadsheets Line Chart That Ignores Missing Data
Question details
The user wants to create a line chart in WPS Office Spreadsheets that connects available data points and does not plot missing data as zero values.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- Creating or editing a line chart from a data range that contains blank cells, formulas returning empty text, or zero values.
- Observed behavior
- The chart may display zero values or interrupted lines instead of skipping the missing categories and connecting available data points.
Before changing chart settings, check whether the source cells are truly blank, contain formulas that return an empty string, or contain actual zeros. This determines whether the chart's Connect data points with a line option will work or whether the formulas must be adjusted first.
Use 'Connect data points with a line' for empty cells in WPS Spreadsheets
Use this when the source range contains truly blank cells and you want the line to continue across the gap instead of dropping to zero.
WPS Office Spreadsheets includes the same Hidden and Empty Cells dialog used for line charts. This setting controls whether blank source cells appear as gaps, zeros, or connected points.
Open your workbook in WPS Office Spreadsheets and click the line chart to activate it.
Right-click the chart and choose Select Data, or go to the Chart Tools tab and click Select Data.
In the Select Data Source dialog, click Hidden and Empty Cells in the lower-left corner.
Select Connect data points with a line, then click OK to close both dialogs.
Check that the line now crosses blank cells and only available data points are plotted.
Replace empty-string formula results with #N/A for chart gaps
Use this when cells contain formulas that return empty text and the Connect data points option does not hide them.
Separate real zeros from missing data before charting
Use this when zero values are legitimate data but the chart is treating them as missing, or when zeros in the range should not be connected.
Create a Missing-Data Line Chart in WPS Office Spreadsheets
In WPS Office Spreadsheets, you can create a line chart that skips blanks and connects available data points using the Hidden and Empty Cells dialog, giving you the same chart controls as Excel in a free, familiar spreadsheet environment.
- 1. Download and open WPS Office: Install WPS Office, launch WPS Spreadsheets, and open your workbook.
- 2. Select the line chart: Click the line chart that contains blanks or gaps to activate the chart tools.
- 3. Open Hidden and Empty Cells: Right-click the chart, choose Select Data, then click Hidden and Empty Cells.
- 4. Connect data points: Choose Connect data points with a line, click OK, and return to the chart.
- 5. Save or export the result: Save the WPS spreadsheet or export it to .xlsx to keep the chart settings for Microsoft Excel users.
Frequently Asked Questions
Why does my line chart show zero when the cell is blank?
WPS Spreadsheets may plot empty cells as gaps or zeros depending on the Hidden and Empty Cells setting. Select the chart, click Select Data > Hidden and Empty Cells, and choose Connect data points with a line.
What is the difference between an empty cell and a formula returning empty text in a chart?
A truly empty cell can usually be handled by Connect data points with a line. A formula returning "" produces a text value that may still be plotted, so change the result to NA() or clear the cell.
Can I use #N/A in a WPS line chart to skip a data point?
Yes. Enter =NA() or return NA() from a formula in the source cell. WPS Spreadsheets does not plot #N/A values, and the line connects the adjacent points.
Does WPS Office keep these line chart settings when I open the file in Excel?
Yes. WPS Spreadsheets saves workbooks in .xlsx format, and the Hidden and Empty Cells / Connect data points with a line setting is retained in the chart.
How do I hide chart gaps instead of connecting points?
Open Select Data > Hidden and Empty Cells and choose Show empty cells as gaps, then click OK.
