How to Create a 24-Hour Excel Chart with Proportional Time Spacing in WPS Office
Question details
The user needs to create a chart in WPS Office Spreadsheets that places event bars or points according to their actual time of day over 24 hours, rather than at equal category intervals.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- The user has event data recorded at irregular times and wants a 24-hour visual distribution based on real clock time.
- Observed behavior
- A standard category chart spaces event bars evenly, which misrepresents irregular time intervals.
Make sure the time column contains real Excel time values, such as 8:30 AM or the numeric fraction 0.354, and not plain text. Only numeric time values can be plotted correctly on an XY Scatter X-axis.
Create an XY Scatter Chart with a Time-Scaled X-Axis
Use this method when you need event markers positioned by actual time of day and a bar-style display is not required.
An XY Scatter chart uses a numeric X-axis, so Excel time values stored as fractions of a day are placed proportionally instead of as evenly spaced categories.
Open your worksheet in WPS Office Spreadsheets and select the columns that contain the time values and the event values.
Go to the Insert tab, click the Chart drop-down, and choose Scatter with Straight Lines and Markers or another Scatter style.
In the Chart Data or Select Data panel, place the time column under X values and the event column under Y values, then confirm.
Right-click the horizontal axis, choose Format Axis, set Axis Type to Time, and enter suitable Minimum and Maximum bounds such as 0 and 1 for a full 24-hour period.
Still in Format Axis, set Major Unit to 1/24 for hourly divisions or 1/48 for 30-minute divisions, and apply a Time number format.
Use a Helper Table with Regular Intervals for Bar-Style Charts
Choose this method if you must keep a bar or column chart look but still need proportional time placement.
Create a True 24-Hour Time Chart in WPS Office Spreadsheets
WPS Office Spreadsheets can directly insert and format an XY Scatter chart with a time-scaled X-axis, so you can position events at their actual clock times without switching to another Office suite.
- 1. Open your workbook in WPS Office Spreadsheets: Launch WPS Office, open the workbook containing the irregular time and event data, and review the time column format.
- 2. Insert an XY Scatter chart: Select the time and event value columns, click the Insert tab, choose Chart, and select XY Scatter.
- 3. Assign X and Y values: In the Chart Data panel, set the time column as X values and the event column as Y values.
- 4. Format the time-scaled axis: Right-click the X-axis, select Format Axis, and set Axis Type to Time with Minimum 0 and Maximum 1 to span 24 hours.
- 5. Save or export as an Excel-compatible file: Click Menu, choose Save As, and select .xlsx format to keep the chart compatible with Microsoft Excel.
Frequently Asked Questions
Why does my Excel bar chart space 8 AM and 8 PM the same distance apart?
A bar chart uses a category axis, so each category is a fixed slot. Use an XY Scatter chart and format the X-axis as time to make distance reflect actual clock time.
How do I set a 24-hour X-axis in WPS Spreadsheets?
Right-click the horizontal axis, choose Format Axis, set Axis Type to Time, then set Minimum to 0 and Maximum to 1 for a full day.
Can I create a 24-hour chart with bars instead of scatter points?
Yes. Build a helper table with fixed 5- or 10-minute intervals, use a lookup formula to retrieve the closest event value, and then insert a bar chart from that helper table.
What time format should my data use for a 24-hour chart?
Use real Excel time values such as 8:30 AM or the underlying numeric fractions. Text values will not plot correctly on a time-scaled axis.
Does WPS Office save this chart in Excel format?
Yes. Save the workbook as .xlsx, and the chart remains compatible with Microsoft Excel.
