How to Plot Dates and Times in an Excel XY Scatter Chart
Question details
The user needs to create an XY scatter chart plotting dates on the horizontal axis and times on the vertical axis.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Creating a time-versus-date chart using a scatter plot for data visualization.
- Observed behavior
- Because Excel stores dates and times as serial values, the data columns must be manually assigned to the X and Y axes and formatted correctly to display as dates and times rather than raw numbers.
Ensure your date and time data are formatted correctly as distinct values in separate columns, rather than as text strings, so the spreadsheet can interpret them as serial numbers.
Configure Data Series and Format Axes for Date and Time
Manually select your X and Y data ranges for the scatter chart, and then format the axes to display the serial values correctly as dates and times.
Excel treats dates and times as numeric serial values (e.g., a date is a whole number of days since 1900, and time is a fractional portion of a day). By explicitly mapping these columns to the X and Y axes and applying the correct number formatting, you can generate an accurate time-versus-date visualization.
Click a blank cell, go to the Insert tab, and choose XY Scatter Chart from the charts group.
Click on the blank chart, navigate to the Chart Design tab, and click on 'Select Data'.
Click 'Add' or 'Edit' under the Legend Entries (Series) section. Set the Series X values to your Date range and the Series Y values to your Time range, then click OK.
Right-click the horizontal (X) axis on the chart and select 'Format Axis'. Under Number formatting, change the category to 'Date'.
Right-click the vertical (Y) axis and select 'Format Axis'. Under Number formatting, change the category to 'Time'.
Plot Scatter Charts Easily with WPS Spreadsheet
WPS Spreadsheet provides powerful, intuitive charting tools to seamlessly plot complex XY scatter charts, automatically handle date and time serial values, and customize visual elements—all within a free, high-performance office suite.
- 1. Open data in WPS: Launch WPS Spreadsheet and open your workbook containing the date and time columns.
- 2. Insert Scatter Chart: Navigate to the Insert tab, select 'Chart', and choose the Scatter plot option.
- 3. Assign data ranges: Right-click the chart area, choose 'Select Data', and map your Date column to the X-axis and Time column to the Y-axis.
- 4. Format axes easily: Double-click each axis to open the properties pane on the right, and apply Date and Time number formats to display your data properly.
Frequently Asked Questions
Why does my scatter chart show random numbers instead of dates?
Spreadsheet software stores dates as numeric serial numbers (e.g., 44000). To display them properly, right-click the axis showing the numbers, choose 'Format Axis', expand the Number section, and change the format category to 'Date'.
How do I add error bars to my scatter chart to improve visibility?
Click your scatter chart, navigate to the Chart Elements menu (the plus icon or on the Chart Design tab), and check 'Error Bars'. You can then select 'More Options' to format them as positive unit-length bars.
Can I plot both date and time on a single axis?
Yes. If your dataset combines date and time into a single cell, you can plot it on one axis. Simply assign that range to either the X or Y axis, right-click to format it, and apply a custom number format like 'mm/dd/yyyy hh:mm'.
