How to Create a Chart with Uneven Timestamp Intervals in Excel
Question details
The user needs to visualize time-series data with irregular intervals without the software automatically grouping the data points by day.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Plotting measurements or events that occur at random or irregular times throughout a day or across multiple days.
- Observed behavior
- Standard column or line charts group dates on a categorical date axis, causing measurements from the same day to clump together instead of reflecting the actual elapsed time between data points.
Ensure your timestamp data is formatted correctly as numeric dates and times in Excel, and organize your data into two adjacent columns with timestamps on the left and measurements on the right.
Use an XY Scatter Chart to Preserve Actual Time Intervals
Scatter charts plot both the X and Y axes as numeric values, allowing irregular timestamps to be spaced proportionally according to the exact time they occurred.
By default, Excel's Column and Line charts treat the horizontal axis as a categorical timeline, sorting data into daily buckets. To respect the exact time gaps between your data points, an XY (Scatter) chart is required because it reads the timestamps as true numeric values.
Highlight the two columns containing your data. The column with your irregular timestamps should be on the left (X-axis) and the measurement values on the right (Y-axis).
Go to the 'Insert' tab on the Excel ribbon. Click on the 'Insert Scatter (X, Y) or Bubble Chart' icon and select 'Scatter with Straight Lines' or 'Scatter with Markers'.
Right-click the horizontal (X) axis on the newly created chart and select 'Format Axis'. Under the 'Number' section, select 'Date' or 'Time' to display the axis labels in your preferred timestamp format.
Change Axis Type to Text Axis (Sequential Spacing)
If you prefer a Line or Column chart and simply want the points evenly spaced regardless of the time elapsed, you can change the axis type.
Create Precise Scatter Charts with WPS Spreadsheet
WPS Spreadsheet provides intuitive and powerful charting tools, allowing you to easily plot uneven timestamp intervals using precise XY Scatter charts without the hassle of categorical axis limitations.
- 1. Select your data: Highlight the cells containing your uneven timestamp intervals and the corresponding data values.
- 2. Insert a Scatter Chart: Navigate to the 'Insert' tab, click on the 'Scatter' chart icon, and choose your preferred scatter chart style.
- 3. Format the Axis: Double-click the X-axis to open the formatting pane, and set the number format to display your desired date and time layout.
Frequently Asked Questions
Why are my Excel chart dates grouping together on the same day?
Column and line charts typically use a categorical 'Date axis', which automatically groups data points by day or month. To show exact, uneven times within the same day or across days, you must use an XY Scatter chart, which reads the timestamps as absolute numeric values.
Can I connect the dots on a Scatter chart with lines?
Yes. When inserting the chart from the 'Insert' tab, click the Scatter icon and select either 'Scatter with Straight Lines' or 'Scatter with Smooth Lines' to connect your chronological data points.
How do I change the time interval displayed on the horizontal axis?
Right-click the horizontal axis and select 'Format Axis'. Under 'Axis Options', you can adjust the 'Major' and 'Minor' bounds and units. In Excel, 1 unit equals 1 day (24 hours), so to set the major unit to 1 hour, you would input 0.041666 (which is 1 divided by 24).
