How to Create an Excel Chart with Time on the Y-Axis and Dates on the X-Axis
Question details
The user needs to create an Excel chart that plots the time of day on the Y-axis, dates on the X-axis, and displays separate categories for daily activities.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Visualizing a daily schedule to track occurrences or durations of activities such as sleep, feeding, or other daily events across multiple days.
- Observed behavior
- A schedule-style chart is required where a 24-hour time scale acts as the vertical axis, dates serve as the horizontal axis, and distinct activity categories are plotted.
Ensure your data is organized into clean columns including Date, Start Time, Finish Time, Duration, and Category before attempting to build the chart.
Use Power Query and Power Pivot to Build a Schedule Chart
Leverage Excel 365's data modeling tools to organize start and finish times, allowing you to create an accurate daily activity schedule chart.
This method is ideal for complex data sets where activities span different durations and categories. Power Query helps structure the data, while Power Pivot enables advanced charting capabilities.
Select your data table, navigate to the Data tab, and click 'From Table/Range' to open Power Query. Ensure your Date column is formatted as Date, and Start/Finish times are formatted as Time.
Close & Load the data to the Excel Data Model. Go to the Power Pivot tab and select 'Manage' to verify your data has been imported correctly.
From the Insert tab, select PivotChart. Use the Data Model as your source. Drag your 'Date' field to the Axis (Categories) area and your 'Category' field to the Legend (Series) area.
Right-click the vertical axis on your new chart and select 'Format Axis'. Set the Minimum Bound to 0.0 (midnight) and Maximum Bound to 1.0 (the following midnight), then change the Number format to Time.
Create a Scatter Chart with Custom Axis Formatting
Plot specific events using a Scatter Chart, which allows for exact X (Date) and Y (Time) coordinate mapping without requiring data models.
Create Time and Date Activity Charts Easily in WPS Spreadsheet
WPS Office offers highly intuitive charting tools that make it simple to plot dates and times without needing complex add-ins like Power Pivot. You can quickly format axes, customize data series, and track your daily schedules effortlessly.
- 1. Open Your Data: Launch WPS Spreadsheet and open the document containing your schedule data.
- 2. Select the Data Range: Highlight the columns containing your Dates, Times, and Activity Categories.
- 3. Insert the Chart: Go to the 'Insert' tab on the top ribbon, click on 'Chart', and select the appropriate chart type, such as a Scatter or Stacked Column chart.
- 4. Adjust Axis Formats: Double-click the vertical or horizontal axis to open the Format Object pane on the right. Under the 'Number' tab, easily switch the formats to Time or Date as required.
Frequently Asked Questions
Why does my Y-axis show decimals instead of time?
Excel stores dates and times as serial numbers, where 1 day equals 1.0 and 12:00 PM equals 0.5. To display time properly, right-click the axis, choose 'Format Axis', navigate to the 'Number' section, and select the 'Time' format.
Can I track multiple categories like sleep and feeding on the same chart?
Yes, you can add different categories as separate data series. When setting up your chart data, assign a new series for each category. This allows you to apply different colors or marker styles for easy visual differentiation.
How do I restrict the Y-axis to exactly 24 hours?
Right-click the Y-axis and select 'Format Axis'. In the Axis Options, set the Minimum bound to 0.0 (which represents midnight) and the Maximum bound to 1.0 (which represents midnight of the next day).
Why is my scatter chart grouping all dates into one line?
This happens when Excel fails to recognize the X-axis data as dates. Ensure your Date column is formatted correctly as 'Short Date'. You may need to right-click the X-axis, select 'Format Axis', and change the axis type from 'Text axis' to 'Date axis'.
