'%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 Create an Excel Chart with Dates and Times
Question details
The user needs to create a chart that accurately plots dates on the horizontal axis and times on the vertical axis.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Visualizing time-based data across different dates, such as tracking daily sunrise and sunset times.
- Observed behavior
- The user struggled to plot the data correctly until they separated the data into two series, utilized an XY scatter chart, and correctly applied date and time formatting to the respective axes via the chart formatting panes.
Ensure your source data has dates and times organized in separate columns, and verify that the cells are formatted as proper 'Date' and 'Time' values rather than plain text.
Use an XY Scatter Chart and Format the Axes
The most effective way to plot dates against times is to use an XY Scatter chart, ensuring your data is split into distinct series and both axes are customized with the correct number formats.
When plotting two continuous numerical scales like dates and times, standard Line or Column charts may treat the horizontal axis as simple text categories. An XY Scatter chart correctly interprets both axes as values, allowing for precise data plotting.
Ensure you have two distinct columns for your data: one for Dates (X values) and one for Times (Y values). Highlight both columns.
Navigate to the 'Insert' tab on the ribbon, click the 'Scatter (X, Y) or Bubble Chart' icon, and select a standard Scatter chart with markers.
Right-click the horizontal axis and select 'Format Axis'. In the formatting pane, scroll down to 'Number', select 'Date' from the Category dropdown, and choose your preferred date format.
Right-click the vertical axis and select 'Format Axis'. Go to the 'Number' section, select 'Time' from the Category dropdown, and pick a suitable time format.
If you have a third column (like day length), add it to the chart. Right-click this new data series, select 'Format Data Series', and under Series Options, check the 'Secondary Axis' box.
Create Date and Time Charts Effortlessly with WPS Office
WPS Spreadsheet offers a seamless and highly intuitive interface for creating complex data visualizations, including XY scatter charts with custom date and time axes. It fully supports Microsoft Excel files, allowing you to edit and format charts without any compatibility issues.
- 1. Open Data: Open your dataset containing dates and times in WPS Spreadsheet.
- 2. Insert Chart: Highlight the date and time columns, go to the 'Insert' tab, and click 'Scatter Chart'.
- 3. Format Axes: Double-click the horizontal and vertical axes to open the properties pane on the right side of the screen.
- 4. Apply Formats: Under the Number section in the properties pane, assign 'Date' to the X-axis and 'Time' to the Y-axis.
Frequently Asked Questions
Why are my dates and times showing up as random numbers on the chart axis?
Spreadsheet software stores dates and times as serial numbers (e.g., 44000 for a date, 0.5 for 12:00 PM). If your axis displays these raw numbers, you need to right-click the axis, select 'Format Axis', and change the Number format category to 'Date' or 'Time'.
Can I use a Line chart instead of an XY Scatter chart for dates and times?
While it is possible to use a Line chart, an XY Scatter chart is strongly recommended for plotting precise time values against dates. Line charts often treat the horizontal axis as evenly spaced categorical data, which can distort the visual representation if your dates are not sequential or evenly spaced.
How do I add a secondary axis for additional time data?
To add a secondary axis, add your new data series to the chart first. Then, right-click the specific data points of that new series, choose 'Format Data Series', and under Series Options, select 'Secondary Axis'. A new axis will appear on the right side of the chart.
How can I change the intervals between dates on the horizontal axis?
Right-click the horizontal axis and click 'Format Axis'. Under Axis Options, find the 'Major Units' setting. Change this value to adjust the interval you want displayed, such as typing '7' to show a label every week.
