Fix an Excel Chart Series That Disappears After Changing Its Data Source
Question details
The user needs to restore a chart series that disappears after its data source is changed.

- 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.
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.
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.
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.
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.
Select the corrected cells and choose Home > Number > Percent Style. Adjust the decimal places as needed instead of using TEXT to control the display.
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.
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.

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. Open the workbook: Open WPS Spreadsheet and use Open to select the Excel workbook containing the chart.
- 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. Apply percentage formatting: Select the corrected cells and use Home > Percent Style to display the numeric results as percentages.
- 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.

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.




