How to Fix Incorrect Time Values on an Excel Chart Y-Axis
Question details
The user needs to correct unusual or incorrect time values displaying on the Y-axis of an Excel chart.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Creating or editing a chart with time-based data plotted on the Y-axis.
- Observed behavior
- The Y-axis displays time at unusual intervals (e.g., 0.2 representing 4 hours and 48 minutes) because Excel stores time as a decimal fraction of a 24-hour day.
Ensure your source data is correctly formatted as Time before creating the chart, as text-based times will not plot properly on the axis.
Adjust the Major Unit of the Y-Axis
Change the axis units to specific decimal values that correspond to standard hour intervals.
Excel stores 24 hours as the decimal value 1. Therefore, dates are stored as serial numbers and times as decimal fractions of a day. When using time values without a date, midnight is stored as 0 and noon as 0.5.
A default major-axis unit of 0.2 represents 4 hours and 48 minutes, which can make the chart difficult to read. You can manually set the major unit to a better fraction, such as 0.25, to fix the displayed intervals.
Right-click the Y-axis on your Excel chart and choose 'Format Axis' from the context menu.
In the Format Axis pane on the right side of the screen, navigate to the 'Axis Options' section to find the Units settings.
Change the Major unit value to 0.25 (or another appropriate decimal fraction) to display even time intervals.
Easily Format Chart Axes with WPS Spreadsheet
WPS Spreadsheet provides highly intuitive tools for formatting chart axes, allowing you to easily adjust time intervals and numerical values with full compatibility.
- 1. Open your data in WPS: Launch WPS Spreadsheet and open your workbook containing the chart.
- 2. Access Axis Properties: Double-click the Y-axis of your chart to open the Object Formatting pane on the right.
- 3. Adjust the Units: Under Axis Options, input your desired decimal for the Major unit (e.g., 0.25) to fix the time intervals.
Frequently Asked Questions
Why does my Excel chart Y-axis show decimals instead of times?
Excel stores times as decimal fractions of a 24-hour day (where 1 equals 24 hours). If the axis is not explicitly formatted as Time or the units are set automatically, it may display the underlying decimal value instead of a readable clock time.
How do I change the Y-axis format back to standard time?
Right-click the axis, select Format Axis, scroll down to the Number section in the pane, and change the Category from General or Number to Time. You can then select your preferred time format code.
What decimal represents 1 hour in Excel?
Because 1 day (24 hours) is equal to 1, a single hour is represented by 1/24, which is approximately 0.0416667 in Excel. Setting your major unit to this value will create 1-hour intervals on your chart.
