How to Fix Excel Chart Axis Minimum and Maximum Values Reverting to Auto
Question details
The user is unable to manually set the minimum, maximum, or major-unit values for a scatter chart axis, as the values instantly reset to automatic scaling.
- Product
- Microsoft Excel 2019
- Device & OS
- not provided
- Scenario
- Adjusting the axis bounds in an Excel scatter chart to properly visualize data for a thesis or professional document.
- Observed behavior
- Manually entered numeric bounds in the Format Axis pane revert back to 'Auto' immediately, preventing custom scaling and breaking the intended data presentation.
Verify that your chart's source data consists entirely of valid, non-corrupted numeric values. If any cells in the data range are formatted as text or contain errors, Excel may force the axis into automatic scaling.
Verify Data Formatting and Recreate the Scatter Chart
Checking for hidden data formatting issues and generating a fresh chart is the most reliable way to resolve persistent axis scaling glitches.
Often, Excel axis limits refuse to lock because the underlying data contains text strings, invisible spaces, or formatting inconsistencies. Cleaning the data and building a new chart can bypass internal file corruption.
Select the data range used for your scatter chart. Right-click and choose 'Format Cells', then ensure the 'Number' format is applied. Remove any text entries or error values like #N/A.
Click on the problematic scatter chart and press the Delete key to remove it from your workbook entirely.
Highlight your clean numeric data, navigate to the 'Insert' tab on the ribbon, click the 'Scatter' chart icon, and select your preferred chart style.
Right-click the X or Y axis on the new chart and select 'Format Axis'. In the Axis Options pane, type your desired numeric values into the Minimum and Maximum fields and press Enter.
Perform an Office Installation Repair
If the axis reverting issue occurs across all workbooks and new files, your Office 2019 installation may be corrupted and needs repairing.
Save as ODS Workaround
Temporarily saving your file in an alternative format can sometimes bypass persistent native Excel chart bugs.
Use WPS Office to Seamlessly Format Chart Axes
If Microsoft Excel continues to frustrate you with bugs like chart axes reverting to Auto, consider switching to WPS Office. It provides a robust, lightweight, and highly compatible spreadsheet tool that handles complex scatter charts flawlessly, ensuring your professional work isn't delayed by software glitches.
- 1. Download and Install: Get the free WPS Office suite from the official website and complete the quick installation.
- 2. Open Your Excel File: Launch WPS Spreadsheet and open your existing .xlsx workbook containing the scatter chart.
- 3. Adjust Axis Seamlessly: Double-click the chart axis to open the Axis Properties pane and enter your custom minimum and maximum bounds without them resetting.
Frequently Asked Questions
Why does my Excel chart axis keep changing to Auto?
This usually occurs if the chart's source data contains text strings or error values instead of pure numbers. It can also be caused by specific software glitches or file corruption in Excel 2019.
How do I lock the axis limits in an Excel scatter chart?
Right-click the axis, select 'Format Axis', and manually type a numeric value for the Minimum and Maximum bounds in the Axis Options pane. Once typed, press Enter. The 'Auto' tag should change to a 'Reset' button, indicating the limits are locked.
Does saving my Excel file as ODS fix chart bugs?
Saving as an OpenDocument Spreadsheet (.ods) can sometimes act as a workaround. It strips out certain Excel-specific formatting glitches, allowing you to edit the chart bounds using alternative office software if Excel itself is failing.
How do I change the major units on my scatter chart axis?
Right-click the axis you want to modify and select 'Format Axis'. Look for the 'Units' section in the Axis Options menu, and enter your desired numeric increment in the 'Major' field.
