Skip to main content

Kutools for Office — One Suite. Five Tools. Get More Done.

How to group time by hour in an Excel pivot table?

Author Kelly Last modified

For example, I have a table as below screenshot shown. Now I need to create a pivot table and group the time data by the hour in the pivot table, any idea? This article will show you the solution in detail!


Group time by hour in an Excel pivot table

Supposing you have a table as below screenshot shown, and you can follow below steps to create a pivot table in Excel, and group time data by hour easily.
sample data

1. Select the source data, and click Insert > PivotTable.

2. In the Create PivotTable dialog box, please select a destination range to place the pivot table, and click the OK button. See screenshot:
select a destination range to place the pivot table

3. In the PivotTable Fields pane, please drag and drop the Time field to the Rows section, and then drag and drop the Amount field to the Values section. See screenshot:
drag the column headers to fields separately

4. Now the pivot table is added. Right-click any time in the Row Labels column, and select Group in the context menu. See screenshot:
Right-click any time in the Row Labels column, and select Group

5. In the Grouping dialog box, please click to highlight Hours only in the By list box, and click the OK button. See screenshot:
click to highlight Hours in the dialog box

Now the time data is grouped by hours in the newly created pivot table. See screenshot:
the time data is grouped by hours

Note: If you need to group time data by days and hours simultaneously, please click to highlight Days and Hours in the By list box of the Grouping dialog box, and then click the OK button. See screenshot:
set options to group time data by days and hours simultaneously


Group time by half an hour in an Excel pivot table

If you need to group time by half an hour in a pivot table in Excel, you can try the amazing PivotTable Special Time Grouping tool, provided by Kutools for Excel, to achieve it easily.

Kutools for Excel offers over 300 advanced features to streamline complex tasks, boosting creativity and efficiency. Itegarate with AI capabilities, Kutools automates tasks with precision, making data management effortless. Detailed information of Kutools for Excel...         Free trial...

1. Select the data source that you will create a pivot table based on, and click Kutools Plus > Pivot Table > PivotTable Special Time Grouping.
click PivotTable Special Time Grouping feature of kutools

2. In the PivotTable Special Time Grouping dialog, you need to:
(1) Tick the Half an hour option in the Group by list box;
(2) Specify the time column that you will grouping data by;
(3) Specify the location you will place the new pivot table.
set options in the dialog box

3. Click the Ok button.

4. Then a pivot table is created with a Half an hour column added as rows. Go ahead to add the Amount column as values.
a pivot table is created with a Half an hour

So far, the pivot table has been created based on the selection, and data has been grouped by half an hour as above screenshot shown.

Related articles:

Best Office Productivity Tools

🤖 Kutools AI Aide: Revolutionize data analysis based on: Intelligent Execution   |  Generate Code  |  Create Custom Formulas  |  Analyze Data and Generate Charts  |  Invoke Kutools Functions
Popular Features: Find, Highlight or Identify Duplicates   |  Delete Blank Rows   |  Combine Columns or Cells without Losing Data   |   Round without Formula ...
Super Lookup: Multiple Criteria VLookup    Multiple Value VLookup  |   VLookup Across Multiple Sheets   |   Fuzzy Lookup ....
Advanced Drop-down List: Quickly Create Drop Down List   |  Dependent Drop Down List   |  Multi-select Drop Down List ....
Column Manager: Add a Specific Number of Columns  |  Move Columns  |  Toggle Visibility Status of Hidden Columns  |  Compare Ranges & Columns ...
Featured Features: Grid Focus   |  Design View   |   Big Formula Bar    Workbook & Sheet Manager   |  Resource Library (Auto Text)   |  Date Picker   |  Combine Worksheets   |  Encrypt/Decrypt Cells    Send Emails by List   |  Super Filter   |   Special Filter (filter bold/italic/strikethrough...) ...
Top 15 Toolsets12 Text Tools (Add Text, Remove Characters, ...)   |   50+ Chart Types (Gantt Chart, ...)   |   40+ Practical Formulas (Calculate age based on birthday, ...)   |   19 Insertion Tools (Insert QR Code, Insert Picture from Path, ...)   |   12 Conversion Tools (Numbers to Words, Currency Conversion, ...)   |   7 Merge & Split Tools (Advanced Combine Rows, Split Cells, ...)   |   ... and more
Use Kutools in your preferred language – supports English, Spanish, German, French, Chinese, and 40+ others!

Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time.  Click Here to Get The Feature You Need The Most...


Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!

All Kutools add-ins. One installer

Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.

Excel Word Outlook Tabs PowerPoint
  • All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
  • One installer, one license — set up in minutes (MSI-ready)
  • Works better together — streamlined productivity across Office apps
  • 30-day full-featured trial — no registration, no credit card
  • Best value — save vs buying individual add-in