Excel charts are powerful tools for visualizing data, but sometimes they may not display all the information you’ve entered. This can be frustrating, especially when it’s not immediately clear why some data points are missing. Here are some common reasons why your chart might not be showing all the data.
Table of contents
YouTube: Chart not including all data
Check the Data Range
One of the most common reasons for missing data in a chart is that the data range selected doesn’t include all the relevant cells. As shown below, you can verify this by right-clicking on your chart and selecting “Select Data.”
In the dialog box that appears, click on the series you want to check and then click “Edit.”
This will give you the below where you can review and adjust the data range for that series to include all the necessary data.
Alternatively, you can click on one of the lines on the chart to highlight the series it represents.
Ensure Data Is on the Correct Sheet
Another issue could be that the chart is referencing data from a different sheet than the one you’re viewing.
For example, if you make changes to your dataset and the chart doesn’t update, it’s possible that the chart is linked to data on another sheet. This can occur if the chart was originally created using data from a different location. To verify this, right-click on the chart and select Select Data from the context menu. In the Select Data Source dialog box (see below), check the Chart data range field at the top to ensure it references the correct sheet and range. In the Example below we are in the sheet called ‘ChartWrongSheet’ but the Chart data range is referencing the sheet called ‘Data’. To correct this, click inside the field and navigate to the correct sheet, and select the appropriate data range. Click “OK” to confirm the changes.
Watch for Hidden Data and Filters
Charts are also sensitive to hidden rows, columns, and filters. If any part of your data is hidden, it won’t appear in the chart. This is especially common when using filters. For instance, in the example below, our chart is only showing 2020 data. This is because a filter is applied and is hiding the rest of the data. To fix this, click anywhere in your dataset, go to the Data tab in the Excel ribbon, and click on Clear in the Sort & Filter group. This will remove any applied filters, and you’ll see all data points reappear on the chart.
Similarly, if someone hides a row or column that contains data included in your chart, that data will also be excluded from the chart. To unhide the relevant rows or columns, highlight the rows or columns adjacent to the hidden data, right-click, and choose Unhide from the context menu. The previously hidden data will now be visible in your chart.
Dealing with Pivot Charts
If your chart is a pivot chart, it might not show all data due to filters applied within the pivot table itself. You can identify a Pivot Chart by clicking on it; if the PivotChart Analyze tab appears in the Excel ribbon, then it’s a Pivot Chart.
Pivot charts are dynamic and directly linked to the pivot table, so any filtering in the pivot table will be reflected in the chart. To fix this, first, click anywhere in the pivot table, and then go to the PivotTable Analyze tab in the ribbon. Check the Filters area to see if any filters are applied. Remove any unwanted filters by clicking the drop-down arrow next to the filter field. In the example below the filter is on the Months field so we select Clear Filter from “Months”.
Adjust the Axis Limits
Sometimes, the issue isn’t with the data selection but with the chart’s axis settings. If your chart’s axis limits are manually set too low, some data points might be hidden.
In the example below, the line chart stops at a certain point even though there are more data points. This occurs because the maximum axis limit is set too low.
To adjust this, right-click on the axis that needs modification and select Format Axis from the context menu. In the Format Axis pane that appears on the right, look under Axis Options for the Bounds section. Notice that the Maximum value has been manually set. To fix this, delete the value and press Enter or select Auto to reset the axis limits automatically. This will ensure that all data points are visible on the chart.
Tips and Troubleshooting
- Use “Refresh All” to update charts, especially when linked to external data or pivot tables.
- Break down large datasets into smaller segments to simplify troubleshooting.
- Use “Data Validation” to ensure that data feeding your chart meets required criteria.