How to group data by half an hour or 15 minutes in Excel PivotTable?
In Excel PivotTable, you can group data by one hour, but in some cases, you just want to group data by half an hour or 15 minutes, how can you handle it? Here I introduce some formulas to help you.
Easily Combine multiple sheets/Workbook into one Single sheet or Workbook
|To combinne multiples sheets or workbooks into one sheet or workbook may be edious in Excel, but with the Combine function in Kutools for Excel, you can combine merge dozens of sheets/workbooks into one sheet or workbook, also, you can consolidate the sheets into one by several clicks only. Click for 60 days free trial!|
|Kutools for Excel: with more than 200 handy Excel add-ins, free to try with no limitation in 60 days.|
Take group data by half an hour as example.
1. Select the adjacent cell to the data, type this formula =MOD(INT(C2/(1/48))*(1/48),1), C2 is the time data, the drag fill handle down to the cells which need this formula.
Note: Please format the formula cells as Time formatting.
2. Select the data including the helper formula column, click Insert > PivotTable, in the Create PivotTable dialog, select one destination to place the PivotTable.
3. Click OK, the PivotTable Fields pane is displayed, add the formula column and other columns into Rows section as you need, and add the column you want to calculate into Values section.
Now the data has been grouped into half an hour.
1. To group by 15 minutes, use this formula =FLOOR(C2,TIME(0,15,0)) in the helper column.
2. You can change the calculation in Value Field Settings dialog.
Tabbed browsing & editing multiple Excel workbooks/Word documents as Firefox, Chrome, Internet Explore 10!
You may be familiar to view multiple webpages in Firefox/Chrome/IE, and switch between them by clicking corresponding tabs easily. Here, Office Tab supports similar processing, which allow you to browse multiple Excel workbooks or Word documents in one Excel window or Word window, and easily switch between them by clicking their tabs. Click for free 45-day trial of Office Tab!