'%3e%3cpath%20d='M8%200C12.4183%200%2016%203.58172%2016%208C16%2012.4183%2012.4183%2016%208%2016C3.58172%2016%200%2012.4183%200%208C0%203.58172%203.58172%200%208%200ZM11.6162%204.38379C11.2257%203.99337%2010.5927%203.99338%2010.2021%204.38379L8%206.58594L5.79785%204.38379C5.40732%203.99334%204.77429%203.99329%204.38379%204.38379C3.99331%204.77429%203.99335%205.40733%204.38379%205.79785L6.58594%208L4.38379%2010.2021C3.99348%2010.5927%203.99341%2011.2257%204.38379%2011.6162C4.77426%2012.0066%205.40734%2012.0065%205.79785%2011.6162L8%209.41406L10.2021%2011.6162C10.5927%2012.0066%2011.2257%2012.0067%2011.6162%2011.6162C12.0067%2011.2257%2012.0066%2010.5927%2011.6162%2010.2021L9.41406%208L11.6162%205.79785C12.0066%205.40735%2012.0066%204.77429%2011.6162%204.38379Z'%20fill='%23080E17'%20fill-opacity='0.46'/%3e%3c/g%3e%3cdefs%3e%3cclipPath%20id='clip0_3761_713'%3e%3crect%20width='16'%20height='16'%20fill='white'/%3e%3c/clipPath%3e%3c/defs%3e%3c/svg%3e)
How to Fix Excel Chart Showing Only Four Data Points
Question details
The user needs to fix an Excel chart that fails to display the entire dataset, showing only a limited number of categories (e.g., four data points) instead of the full data range.
- Product
- Microsoft Excel
- Device & OS
- Windows
- Scenario
- Visualizing a complete dataset using an Excel chart after a recent system or software update.
- Observed behavior
- The chart only displays a few categories or data points, even though the selected source data range contains all the expected values.
Ensure your dataset does not contain completely blank rows or columns in the middle of your selected range, as empty cells can disrupt the chart's data mapping.
Change the Horizontal Axis Type to Text Axis
Use this method if Excel has mistakenly interpreted your category labels as dates, causing it to compress or hide data points along the horizontal axis.
After system updates, Excel's automatic formatting sometimes incorrectly detects numerical or text labels as dates. This forces a Date axis, which clusters data chronologically and drops unidentifiable points.
Open your Excel chart and right-click on the horizontal (X) axis labels.
Choose 'Format Axis' from the context menu to open the formatting pane on the right side of your screen.
Under 'Axis Options', look for the 'Axis Type' section. Change the selection from 'Automatic' or 'Date axis' to 'Text axis'.
Click anywhere outside the chart to apply the changes. The chart should now display all categories correctly.
Verify Source Data Range and Unhide Data
Sometimes charts are configured to ignore data in hidden rows and columns, or the data range selection was accidentally truncated.
Clear Data Formatting and Recreate the Chart
If adjusting the axis settings does not work, the underlying cell formatting might be corrupted and recreating the chart from clean data is necessary.
Use WPS Spreadsheet for Accurate Data Visualization
Avoid frustrating chart formatting bugs by using WPS Spreadsheet. It intelligently recognizes data types and offers a highly compatible, user-friendly interface for creating professional charts without dropping data points.
- 1. Open your dataset in WPS Spreadsheet: Launch WPS Office and open your existing .xlsx file containing the data.
- 2. Select your complete data range: Highlight all the rows and columns you want to include in your chart.
- 3. Insert the chart: Navigate to the 'Insert' tab on the top ribbon and click 'Chart'. Choose your preferred chart type.
- 4. Customize axis settings: Double-click the horizontal axis to open the properties pane, where you can ensure the axis type is set to display all text categories correctly.
Frequently Asked Questions
Why does Excel automatically change my horizontal axis to a Date axis?
Excel's automatic formatting tries to detect dates in your category labels. If it finds numbers or text combinations that resemble dates (like '1-4' or 'Mar-22'), it applies a Date axis. This can cluster or hide data points that don't fit a standard chronological scale.
Will hidden rows in my spreadsheet affect my chart's data points?
Yes, by default, Excel charts do not display data from hidden rows or columns. You must either unhide the spreadsheet data or adjust the chart's 'Hidden and Empty Cells' settings to show the hidden data points.
Does upgrading Windows affect how my Excel charts display?
While a Windows upgrade shouldn't directly alter your Excel files, it can sometimes reset your default system region and date formats. This resetting might cause Excel to misinterpret your existing chart data as dates upon reopening the file.
How do I force the chart to show every label on the horizontal axis?
You can force Excel to show all labels by right-clicking the horizontal axis, selecting 'Format Axis', navigating to 'Labels', and setting the 'Specify interval unit' to 1. This ensures no categories are skipped for spacing purposes.
