Chart & Visualization Issues

How to Fix Excel Chart Not Including All Selected Data

Maira Mehtab Sep 20, 2026 869 views

Question details

The user is experiencing an issue where an Excel chart fails to include or highlight all the selected data values despite the data range appearing to be visually correct.

Product: Excel
Device & OS: not provided
Scenario: Selecting a range of data that includes numerical values mixed with text units (such as 'ml') to generate a chart.
Observed behavior: Excel does not highlight or plot all the selected values because the mixed content causes the data cells to be interpreted as text strings rather than usable numeric values.

Before you start

Check your data source to ensure that all cells intended for the chart contain strictly numeric values, without any text units, letters, or special characters mixed directly into the numbers.

Solution 1Recommended

Remove Text Units from Data Cells

Format your cells to contain purely numeric data by removing typed text units like 'ml' from individual cells and moving them to the column or row headers.

When text characters such as 'ml', 'kg', or 'USD' are typed directly into a cell alongside a number, the spreadsheet interprets the entire cell as text. Because charts can only plot numeric values, these text-formatted cells are automatically ignored or excluded from the visualization.

Identify text-formatted cells

Review the data range you selected for your chart and look for cells that contain both numbers and text units (e.g., '50 ml').

Remove units from individual cells

Delete the text units from the cells so that only the raw numerical value remains (e.g., change '50 ml' to '50').

Add units to column or row headers

To maintain data clarity, edit the column header or row label to include the unit, such as changing 'Volume' to 'Volume (in mL)'.

Recreate the chart

Select the updated, numbers-only data range, go to the 'Insert' tab, and choose your desired chart type to generate a complete chart.

Custom Number Formatting Alternative: If you want the units to appear in the cells visually without breaking the chart, right-click the cell, select 'Format Cells', go to 'Custom', and enter a format like 0 "ml". This keeps the underlying data numeric.

WPS Spreadsheet Solutions

Create and Format Charts Easily with WPS Spreadsheet

WPS Spreadsheet provides intuitive tools for data formatting and chart creation. You can easily set custom number formats to visually display units without breaking your charts, and it is fully compatible with Microsoft Excel files.

1. Open your data in WPS Spreadsheet: Launch WPS Office and open your existing workbook containing the dataset you want to chart.
2. Apply Custom Formatting: Select your numerical data, right-click, and choose 'Format Cells'. Under the 'Custom' category, enter a format like General "mL" so the unit displays without converting the number to text.
3. Insert Chart: Highlight your fully recognized numeric data, navigate to the 'Insert' tab, and click on 'Chart' to select the best visual representation.

Seamless format compatibility with Microsoft Office Excel (.xlsx) filesIntuitive custom cell formatting to display units safely in chartsWide variety of professional and customizable chart templatesFree, lightweight, and fast alternative for data analysis

microsoft office alternative - wps office

Frequently Asked Questions

Why does my chart show a blank space for some data points?

Blank spaces usually occur if a cell is entirely empty, contains hidden text characters, or has a zero value that is formatted to be hidden. Ensure all cells in your charted range contain valid numbers and no erroneous spaces.

Can I display units in my cells without breaking the chart?

Yes, you can use Custom Number Formatting. Right-click the selected cells, choose 'Format Cells', navigate to the 'Custom' tab, and type your desired format, such as 0 "kg". This changes the visual display but keeps the underlying value strictly numeric.

How do I select non-adjacent data ranges for a chart?

Hold down the 'Ctrl' key (or 'Cmd' on Mac) while clicking and dragging over the different columns or rows you want to include. Once highlighted, go to the Insert tab to create your chart.

Why is my date axis not displaying correctly in the chart?

If dates are formatted as text, the chart may treat them as generic text categories instead of a continuous chronological timeline. Select your date cells, ensure they are formatted explicitly as 'Date', and verify there are no hidden spaces or incorrect separators.

Excel & Data Problems