Chart & Visualization Issues

Fix an Excel Chart Series That Disappears After Changing Its Data Source

John WilsonJohn Wilson Nov 14, 2026 868 views

Question details

The user needs to restore a chart series that disappears after its data source is changed.

Fix an Excel Chart Series That Disappears After Changing Its Data Source
Product
Microsoft Excel
Device & OS
not provided
Scenario
A chart series is changed to use a structured table reference or a direct cell range containing percentage formulas.
Observed behavior
The series disappears when the source formulas use TRIM and TEXT to convert percentage values to text.
Before you start

Check the cells used by the missing series for formulas containing TEXT or TRIM. Identify the underlying numeric lookup or calculation and confirm which cells should supply the series values.

Solution 1Recommended

Keep the chart's percentage values numeric

Use this solution when TEXT or TRIM converts the source values to text and Excel no longer plots the series.

1
Inspect the source formulas

Select a cell in the chart's source range and inspect its formula in the formula bar. Look for TEXT or TRIM applied to the percentage result.

2
Return a numeric result

Edit the formula in the formula bar to remove TEXT and TRIM from the result while retaining the underlying numeric lookup or calculation. Fill the corrected formula down the source column.

3
Format the values as percentages

Select the corrected cells and choose Home > Number > Percent Style. Adjust the decimal places as needed instead of using TEXT to control the display.

4
Suppress zero values if needed

In a helper column, enter =IF(B2=0,NA(),B2), replacing B2 with the cell containing the numeric result, and fill the formula down. Use that helper column as the chart series source; #N/A results are not plotted.

5
Check the series range

Select the chart and open Chart Design > Select Data. Select the affected series, choose Edit, and verify that Series values points to the corrected numeric cells or helper column.

Keep the chart's percentage values numeric
About suppressed values: NA() displays #N/A in the worksheet and creates a gap in the chart. Use the helper column only if you want zero results omitted from the plotted series.
Work with charts in WPS Spreadsheet

Restore a numeric chart series in WPS Spreadsheet

Open the workbook in WPS Spreadsheet, remove text conversion from the percentage formulas, and point the chart series to the corrected numeric values.

  1. 1. Open the workbook: Open WPS Spreadsheet and use Open to select the Excel workbook containing the chart.
  2. 2. Correct the formulas: Select the source cells and edit their formulas in the formula bar to remove TEXT and TRIM from the numeric percentage results.
  3. 3. Apply percentage formatting: Select the corrected cells and use Home > Percent Style to display the numeric results as percentages.
  4. 4. Update the chart source: Select the chart, open Select Data from the chart tools, and edit the affected series to use the corrected cells. If you need to omit zeros, first create a helper column with =IF(B2=0,NA(),B2) and use that column for the series.
Plot numeric percentage values without converting them to textOpen and edit Microsoft Excel XLSX and XLS filesUse a helper column to omit zero results from a chartWork in a familiar spreadsheet interface
microsoft office alternative - wps office

Frequently Asked Questions

Why did my Excel chart series disappear after I changed its source?

The new source may contain text instead of numbers. Check the source formulas in the formula bar for TEXT or TRIM applied to percentage results, and return the underlying numeric values instead.

How can I show percentages without using the TEXT function?

Select the numeric result cells and choose Home > Number > Percent Style. This changes their appearance without converting them to text.

How can I prevent zero values from appearing in the chart?

Create a helper column with a formula such as =IF(B2=0,NA(),B2), fill it down, and use that column as the series source. The #N/A results appear in the worksheet but are not plotted.

Where can I check the range used by a chart series?

Select the chart, choose Chart Design > Select Data, select the affected series, and click Edit. Check that Series values refers to the corrected cells or table column.