How to Group Data by Hour in Excel

Why Grouping Data by Time is Essential

In data analysis, summarizing data based on time periods is often more informative than viewing individual records. When dealing with operational statistics—like website traffic, call center volumes, or hourly production counts—grouping by the hour isolates micro-trends that might be missed when aggregating by day or week. This allows analysts to pinpoint precisely when key events occur, such as identifying the busiest hour for sales or the slowest period for customer engagement.

The ability to accurately segment data using the Group function in Excel is a significant efficiency booster. Instead of manually creating calculated columns using complex formulas to extract the hour from a timestamp, the Pivot Table automates this process cleanly and dynamically. This automated approach saves considerable time and minimizes the risk of calculation errors, leading to more robust and trustworthy analytical outputs. Furthermore, grouped data forms a perfect foundation for creating dynamic charts and visualizations.

Before diving into the technical execution, it is paramount that your source data is structured correctly. Ensure that the time column is formatted appropriately as a date or time data type within Excel, as the Pivot Table grouping function relies on recognizing these specific formats. If your time data is stored merely as text, the grouping feature will fail to recognize the temporal hierarchy, requiring an intermediate step to convert the column using functions like TIMEVALUE or DATEVALUE. Assuming proper formatting, we can now proceed to structure the data for analysis.

Step 1: Preparing Your Source Dataset

The first critical step in any data analysis workflow is ensuring the integrity and structure of your source dataset. For this specific tutorial, we will establish a simple, representative dataset illustrating total sales recorded at various times throughout a specific period. This dataset includes two primary columns: the timestamp of the transaction (labeled “Time”) and the corresponding metric (labeled “Sales”).

For demonstration purposes, let’s create a small but detailed table in a new Excel worksheet. Notice how the ‘Time’ column contains specific timestamps. It is crucial that these cells are formatted as a recognized Time or Date/Time format for the grouping functionality to work correctly. If they are not, you must select the column and adjust the formatting via the ‘Home’ tab under the ‘Number’ group.

The following illustration shows the structure of our sample data. This arrangement, featuring clear headers and consistent data types, is the optimal prerequisite for creating an effective Pivot Table. We are aiming to condense these eleven individual sales records into aggregated totals for each corresponding hour.

Step 2: Initiating the Pivot Table Process

With our sales data properly prepared, the next phase involves initiating the Pivot Table creation. The Pivot Table acts as the engine for summarizing our records, making the subsequent hourly grouping straightforward. Begin by selecting the entire range of data you wish to analyze. In our example, this corresponds to the range spanning from cell A1 (including the headers) through B12.

Once the range is highlighted, navigate to the Insert tab located on the top ribbon of the Excel interface. Within the leftmost group of tools, you will find the Pivot Table command. Clicking this will launch the ‘Create PivotTable’ dialog box. This window prompts you to confirm your data selection and, more importantly, choose where the resulting Pivot Table should be placed.

For ease of visibility and continued analysis, we recommend placing the Pivot Table adjacent to your source data, ideally on the existing worksheet. In this scenario, we choose the ‘Existing Worksheet’ option and specify cell D1 as the starting location for the report. Confirming these settings by clicking OK generates the basic framework of the Pivot Table in the specified location, and simultaneously opens the Pivot Table Fields pane on the right side of the screen, preparing us for field configuration.

Step 3: Configuring Fields within the Pivot Table

The configuration of fields within the Pivot Table Fields pane is crucial for defining how your data will be summarized. We need to tell Excel which column contains the values we want to summarize and which column provides the basis for our groupings (the time variable). Properly assigning these roles ensures that the initial non-grouped summary table accurately reflects the relationship between time and sales.

Start by identifying the time variable. Drag the Time field from the top section of the Fields pane down into the Rows area. This action defines the unique time entries as the primary identifiers along the left side of the resultant table. Next, identify the metric you wish to summarize, which is the Sales field in our example. Drag the Sales field into the Values area.

By default, when numerical data is placed in the Values area, Excel performs a summarization—usually the Sum. If you needed a count, average, or maximum instead, you would click on the field name in the Values area and select ‘Value Field Settings’ to adjust the aggregation function. For this tutorial, we are interested in the total sales per time entry, so ‘Sum of Sales’ is the correct default setting. Upon completing this configuration, the initial Pivot Table will appear, showing the sum of sales corresponding to every individual time entry in the source dataset.

Step 4: Executing the Hourly Grouping Function

This step is the core of the tutorial: applying the Grouping feature to consolidate the individual timestamps into hourly bins. The Group function is context-aware; when applied to a date/time field, it automatically offers relevant chronological grouping options such as Seconds, Minutes, Hours, Days, Months, Quarters, and Years. To begin, click anywhere within the time values listed in the Row Labels column of the Pivot Table (e.g., cell D2 or D3). Critically, ensure you are right-clicking a value within the table itself, not the header.

Upon right-clicking, a context menu will appear. Select the Group option from this menu. This action invokes the ‘Grouping’ dialog box, which presents the available options for aggregating the time data. Excel will often automatically detect the start and end times of your dataset, displaying them in the ‘Starting at’ and ‘Ending at’ fields, which should generally be left unchanged unless you specifically need to restrict the analysis window.

Within the ‘By’ box, you must deselect any default selections (like Days or Months, if they appeared) and specifically select Hours. If you were analyzing data that spanned multiple days and needed the hour groups to reset each day, you would also select Days simultaneously (a concept we will revisit later). For simple hourly summation across a single day or consecutive time period, selecting only Hours is sufficient. Clicking OK then applies the grouping logic, instantly transforming the detailed table into an aggregated summary based on 60-minute intervals.

Reviewing and Utilizing the Grouped Results

Once the grouping is applied, the Pivot Table structure changes dramatically. The individual time stamps disappear from the row headers, replaced by clean, consolidated hourly ranges. Each row now represents the total aggregation of sales that occurred within that specific hour, providing a powerful snapshot of activity trends.

The resulting table clearly shows the sum of sales grouped by the hour, which is the desired outcome of our data analysis effort. For instance, you can immediately observe which hour interval (e.g., 8:00 AM to 9:00 AM) generated the highest total sales volume versus the lowest. This level of aggregation is instrumental for operational decision-making, such as scheduling staff or predicting resource demands.

It is important to understand the flexibility of the Group function. If your analysis requires a more granular view—perhaps grouping by 15-minute intervals—you can revisit the ‘Grouping’ dialogue box. Although the dialogue primarily shows standard time units, you can modify the ‘Number of Days’ setting (which works counter-intuitively for hours) or select ‘Minutes’ and specify the number of minutes (e.g., 15). Furthermore, if your dataset spans multiple days and you need the grouping to reflect both the Day and the Hour simultaneously (meaning 8 AM Monday is distinct from 8 AM Tuesday), you must select both Days and Hours in the ‘By’ selection box. This hierarchical grouping ensures that the Pivot Table first groups by day, and then nests the hourly summaries underneath each respective day, offering maximum analytical depth.