Chart & Visualization Issues

Create an Excel Chart Showing Activity by Time of Day

Aamir Naveed Akram Sep 28, 2026 869 views

Question details

The user wants to create a chart that visualizes the frequency of activities based on the time of day, accurately distinguishing between different hours and AM/PM.

How to Create an Excel Chart Showing Activity by Time of Day

Product: Excel
Device & OS: not provided
Scenario: Tracking and analyzing time-stamped data logs to identify peak activity periods or trends throughout the day.
Observed behavior: Needs an efficient method to combine date and time values and plot them on a chart without hitting data point limitations, while correctly grouping by time intervals.

Before you start

Ensure your date and time columns are stored as valid Excel date and time serial numbers rather than text, so the application can accurately calculate, sequence, and group them.

Solution 1Recommended

Create a PivotChart by Grouping Time Data

This is the most efficient and recommended method. It combines date and time, groups records by hour or minute, and generates a dynamic chart that avoids traditional 255-point limitations.

By utilizing PivotTables to summarize thousands of rows into hourly buckets, you prevent the chart from becoming overcrowded. Grouping dates naturally allows Excel to sort AM and PM periods correctly.

Combine Date and Time

Create a new column next to your data. Assuming column A contains dates and column B contains times, type the formula =A2+B2 in the first row. Press Enter, and drag the fill handle down to apply this formula to all rows.

Insert a PivotTable

Select your entire data range including headers. Navigate to the Insert tab on the ribbon and click PivotTable. Choose to place the PivotTable on a New Worksheet.

Configure and Group Data

In the PivotTable Fields pane, drag the new combined 'DateTime' field to the Rows area, and drag it again to the Values area. Ensure the Values area is set to 'Count'. Right-click any time value inside the Rows column of the PivotTable, select Group, and choose 'Hours' (and/or 'Minutes').

Insert the Chart

With a cell inside your grouped PivotTable selected, go to the Insert tab and choose either a Column chart or a Line chart. The resulting PivotChart will dynamically reflect your grouped time-of-day data.

Create a PivotChart by Grouping Time Data

Solution 2

Calculate Frequencies Manually Using COUNTIFS

If you prefer to avoid PivotTables, you can manually set up predefined time bins and use the COUNTIFS function to count activities occurring within specific time intervals.

Analyze Time Data with Ease

Quickly Visualize Time-Based Activity in WPS Spreadsheet

WPS Spreadsheet features robust PivotTable and charting tools, allowing you to easily group time-stamped data and create clear visualizations without performance lags.

1. Open your data file: Launch WPS Spreadsheet and open your .xlsx workbook containing the time logs.
2. Combine the data: Use the addition formula (=A2+B2) in a new column to seamlessly merge your date and time fields.
3. Summarize with a PivotTable: Select the data, go to the Insert tab, click PivotTable, and group the row labels by 'Hour'.
4. Insert your Chart: Highlight the summarized time blocks and click 'Insert Chart' to visualize the activity peaks.

Advanced PivotTable grouping for dates, hours, and minutes.Fully compatible with Microsoft Excel (.xlsx) file formats and formulas.Extensive library of customizable 2D and 3D chart types.Free and lightweight software with an intuitive, familiar interface.

microsoft office alternative - wps office

Frequently Asked Questions

Why is Excel not grouping my time values in the PivotTable?

This usually happens when time data is formatted as text instead of a valid numeric time format. To fix this, select your time column, go to the Data tab, and use the 'Text to Columns' wizard to convert the text back into proper time values.

Can I group my chart data by both dates and hours simultaneously?

Yes. When you right-click a time cell in your PivotTable and select Group, you can select multiple parameters simultaneously, such as 'Days' and 'Hours'. This allows for a multi-level axis in your chart, displaying days broken down by the hour.

How do I ensure AM and PM display correctly on the chart axis?

Right-click the axis on your chart and choose 'Format Axis'. Under the Number category, specify a custom format code like 'h:mm AM/PM'. This will force the axis labels to clearly display morning and afternoon distinctions.

Should I use a line chart or a column chart for time-of-day analysis?

A column chart is generally best if you want to emphasize distinct time blocks or total volume per hour. A line chart is more appropriate if your goal is to show the continuous flow or trend of activity over a full 24-hour cycle.

Excel & Data Problems