'%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 Set a Custom Starting Value for an Excel Chart Axis
Question details
The user wants to set a custom minimum starting value (e.g., 21:00) on an Excel chart's vertical axis to group specific time ranges, but encounters issues with data points missing from the chart.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Creating a line chart to visualize events occurring across a specific time range, particularly when tracking times that cross over midnight into the next day.
- Observed behavior
- When setting the vertical axis minimum to 21:00, time values that fall before this minimum (like early morning hours without associated dates) are excluded from the plot entirely.
Ensure your source data uses full date and time formatting (e.g., 'MM/DD/YYYY HH:MM') rather than just isolated time values. This is critical for any dataset where recorded events span across midnight.
Format Source Data and Adjust Custom Axis Bounds
Fix missing data points by converting time-only values to full date-time values, then set the numeric equivalent of your starting time in the axis options.
Spreadsheet software stores dates and times as continuous serial numbers. A standalone time is calculated as a fraction of a 24-hour day (for example, 21:00 is exactly 0.875). If your events span past midnight into a new day, time-only data restarts at 0.0.
Because 0.0 is less than your 0.875 minimum axis setting, those early morning data points fall outside the chart bounds. Adding the date ensures the system calculates the next day as a higher numeric value.
Highlight the cells containing your time data, right-click, and select 'Format Cells'. Under the 'Number' tab, choose 'Custom' and apply a format like 'm/d/yyyy h:mm'. Ensure you type the appropriate date along with the time in those cells.
To find the numeric value for your axis, type your starting time (e.g., 21:00) into a blank cell. Change that cell's format to 'General' to see the decimal equivalent (which is 0.875 for 21:00).
Right-click the vertical axis on your chart and select 'Format Axis' from the context menu to open the formatting panel on the right side of your screen.
In the 'Format Axis' pane, under 'Axis Options', locate the 'Bounds' section. In the 'Minimum' field, input the decimal value you found earlier (e.g., 0.875) and press Enter to apply.
Easily Customize Chart Axes in WPS Spreadsheet
WPS Spreadsheet offers an intuitive formatting interface that makes handling complex date and time visualizations simple. You can easily adjust axis bounds, apply custom time formats, and build professional charts seamlessly.
- 1. Open your dataset in WPS Spreadsheet: Launch WPS Office, open your spreadsheet containing the timeline data, and click on the chart you wish to modify.
- 2. Access the axis formatting panel: Double-click on the vertical or horizontal axis you want to adjust. The 'Object Formatting' pane will appear on the right side.
- 3. Adjust the axis limits: Click on 'Axis' properties (the chart icon), expand 'Axis Options', and locate the 'Bounds' inputs.
- 4. Apply your custom minimum value: Enter your specific decimal time value in the 'Minimum' box (like 0.875 for 9:00 PM) to instantly update the chart visualization.
Frequently Asked Questions
How do I find the correct numeric decimal for a specific time in Excel?
Type your target time (e.g., '18:00' or '6:00 PM') into any blank cell. Select that cell, go to the Home tab, and change the Number Format dropdown from 'Time' to 'General'. Excel will instantly reveal the decimal value you need for your axis bounds.
Why are my chart data points dropping to the bottom after 11:59 PM?
When your source data only contains time without dates, the software considers all entries to be on 'day zero'. At midnight, the time resets to zero. If you set a custom minimum axis of 21:00 (0.875), a midnight value (0.000) falls outside the visible range. Adding the date to the cell fixes this logical drop.
Can I format the axis to show times without displaying the dates?
Yes. Even if your source data cells contain both dates and times (which is necessary for correct plotting), you can right-click the axis, select 'Format Axis', scroll down to 'Number', and apply a custom format code such as 'hh:mm'. This hides the date text visually on the chart while preserving the accurate background calculations.
