Fix Excel Recommended Chart Data When Numbers Are Stored as Text
Question details
The user needs Excel Recommended Charts to recognize all source values and display the intended data correctly.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- The user is creating a chart from data that includes measurements such as centimeters or hertz.
- Observed behavior
- Excel may omit values or produce unexpected chart results because numeric-looking cells are stored as text.
Check the source range for values that contain unit letters, spaces, or apostrophes. Keep measurement units in the column headers rather than inside the data cells used for charting.
Remove units from data cells and convert values to numbers
Use this solution when chart source values include units such as cm or Hz and Excel is treating the entries as text.
Charts require numeric data points. A value such as "25 cm" is text in Excel, even though it contains a number.
In the worksheet, change headings to labels such as "Length (cm)" or "Frequency (Hz)," then remove cm, Hz, and other unit text from every data cell below the heading.
Select the affected cells. If a warning icon appears, click it and choose Convert to Number. Alternatively, use Data > Text to Columns > Finish to convert text-formatted numeric values.
With the cells selected, go to Home > Number and choose Number or General. Numeric values should normally align to the right side of the cells.
Create the chart again from the corrected range
Use this after cleaning the data to rebuild the chart with the corrected numeric source values.
Create Accurate Charts from Clean Numeric Data in WPS Spreadsheet
If Excel chart recommendations continue to interpret your values incorrectly, WPS Office is a free, lightweight alternative with a familiar spreadsheet interface. Open your existing workbook, clean text-formatted numbers, and insert charts while keeping your Excel file format for a seamless migration.
- 1. Open the workbook in WPS Spreadsheet: Launch WPS Office, choose Open, and select your Excel XLS or XLSX workbook.
- 2. Clean the chart source values: Edit the worksheet so data cells contain numbers only, and place units such as cm or Hz in the column headings.
- 3. Insert a chart from the corrected range: Select the data range, open the Insert tab, click Chart, and choose a recommended chart or an XY Scatter chart.
Frequently Asked Questions
Why does Excel omit some values from a Recommended Chart?
Excel can omit values when they are stored as text instead of numbers. Remove unit text from the data cells and convert the entries through the warning icon's Convert to Number option or Data > Text to Columns > Finish.
Can I keep cm or Hz in my Excel chart data?
Keep cm, Hz, and similar units in the column heading, such as "Distance (cm)." The cells beneath the heading should contain numeric values only for reliable charting.
How can I tell whether an Excel value is text or a number?
Text-formatted numbers often align left by default, may show a green triangle warning, and may not respond to number formats. Select the cell and use Home > Number to check or change its format.
When should I use an XY Scatter chart instead of a recommended chart?
Use Insert > Scatter (X, Y) or Bubble Chart when both columns are numeric measurements and one column should be plotted as X values while the other is plotted as Y values.
