How to Fix Excel Chart Not Plotting Some Values from Source Data
Question details
The user's spreadsheet chart is failing to display certain data points, even though the values correctly appear in the Select Data window.
- Product
- Spreadsheet
- Device & OS
- not provided
- Scenario
- Creating or updating a data chart from a source table.
- Observed behavior
- Some source values are missing from the generated chart due to potential formatting errors, hidden data settings, or configuration issues.
Ensure your source data range does not contain completely empty rows or columns that might break the chart's data series sequence, and verify that you haven't applied active filters hiding the data.
Convert Text Cells to Number Format
Charts cannot plot numbers that are formatted as text. Converting the affected cells to numbers usually resolves the missing data issue.
Often, imported data or manually typed values are stored as text, which the charting engine ignores. You must convert these text strings back into recognized numerical values.
Highlight the cells in your source data table that are missing from the chart.
Right-click the selected cells, choose 'Format Cells', navigate to the 'Number' tab, and select 'Number' from the category list. Click OK.
If a small green triangle appears in the corner of the cells, click the warning icon next to it and select 'Convert to Number'.
Right-click the chart and click 'Select Data', then click OK to force the chart to refresh and plot the newly formatted numbers.
Enable Chart to Show Hidden or Empty Cells
By default, charts do not plot data located in hidden rows or columns.
Recreate the Chart in a New Workbook
If formatting and hidden settings are correct, the workbook or the specific chart configuration may be corrupted.
Create and Manage Charts Flawlessly with WPS Spreadsheet
WPS Spreadsheet offers intuitive charting tools that automatically detect data formats and prevent missing data points, ensuring a smooth and error-free visualization experience.
- 1. Open your data in WPS: Launch WPS Office and open your spreadsheet document containing the source data.
- 2. Select the data range: Click and drag to highlight the rows and columns you want to visualize, including your headers.
- 3. Insert a chart: Go to the 'Insert' tab on the top ribbon and click the 'Chart' icon to open the chart selection panel.
- 4. Customize and apply: Choose your preferred chart type from the list. WPS will instantly plot all recognized values accurately.
Frequently Asked Questions
Why does my chart show a gap where empty cells exist?
By default, spreadsheet applications leave gaps for empty cells. You can change this behavior by right-clicking the chart, choosing 'Select Data', clicking 'Hidden and Empty Cells', and selecting 'Zero' or 'Connect data points with line'.
Can I plot data from non-adjacent columns in my chart?
Yes. To select non-adjacent columns, highlight the first column, hold down the 'Ctrl' key on your keyboard, and then click and drag to highlight the subsequent columns before inserting your chart.
Why is my chart not updating when I change the source data?
This usually happens if your workbook calculation is set to Manual. Go to the Formulas tab, click 'Calculation Options', and change it to 'Automatic'. Alternatively, your chart's data range might be fixed; verify it via the 'Select Data' menu.
