How to quickly sum hourly data to daily in Excel?
Sum hourly data to daily with PIVOTTABLE
Sum hourly data to daily with Kutools for Excel
|In some cases, you may want to sum the values based on the same data, instead of find the same data one by one and sum them manually, Kutools for Excel's Advanced Combine Rows utility can quickly combine and do calculations based on values.|
- Reuse Anything: Add the most used or complex formulas, charts and anything else to your favorites, and quickly reuse them in the future.
- More than 20 text features: Extract Number from Text String; Extract or Remove Part of Texts; Convert Numbers and Currencies to English Words.
- Merge Tools: Multiple Workbooks and Sheets into One; Merge Multiple Cells/Rows/Columns Without Losing Data; Merge Duplicate Rows and Sum.
- Split Tools: Split Data into Multiple Sheets Based on Value; One Workbook to Multiple Excel, PDF or CSV Files; One Column to Multiple Columns.
- Paste Skipping Hidden/Filtered Rows; Count And Sum by Background Color; Send Personalized Emails to Multiple Recipients in Bulk.
- Super Filter: Create advanced filter schemes and apply to any sheets; Sort by week, day, frequency and more; Filter by bold, formulas, comment...
- More than 300 powerful features; Works with Office 2007-2019 and 365; Supports all languages; Easy deploying in your enterprise or organization.
To quickly sum data by daily, you can apply PivotTable.
1. Select all the data range and click Insert > PivotTable ( > PivotTable), and in the popping dialog, check the New Worksheet or Existing Worksheet option as you need under Choose where you want the PivotTable report to be placed section. See screenshot:
2. Click OK, and a PivotTable Fields pane pops out, and drag Date and Time label to Rows section, drag Hourly Value to Values section, and a PivotTable has been created. See screenshot:
3. Right click one data under Row Labels in the created PivotTable, and click Group to open Grouping dialog, and select Days from the By pane. See screenshot:
4. Click OK. Now the hourly data has been sum daily. See screenshot:
Tip: If you want to do other calculations to the hourly data, you can go to the Values section and click at arrow-down to select Value Field Settings to change the calculation in the Value Field Settings dialog. See screenshot:
If you have Kutools for Excel, the steps on summing hourly data to daily will be much easier with its Advanced Combine Rows utility.
|Kutools for Excel, with more than 120 handy Excel functions, enhance your working efficiency and save your working time.|
After free installing Kutools for Excel, please do as below:
1. Select the date and time data, and click Home tab, and go to Number group and select Short Date from the drop down list to convert the date and time to date. See screenshot:
2. Select the data range and click Kutools > Content > Advanced Combine Rows. See screenshot:
3. In the Advanced Combine Rows dialog, select Date and Time column, and click Primary Key to set it as Key, and then click Hourly Value column and go to Calculate list to specify the calculation you want to do. See screenshot:
4. Click Ok. The hourly data has been summed by daily.
Tip: Prevent losing the original data, you can paste one copy before applying Advanced Combine Rows.
You are guest
or post as a guest, but your post won't be published automatically.
- To post as a guest, your comment is unpublished.· 1 years agoThanks - nice tip!
- To post as a guest, your comment is unpublished.· 3 years agoThanks a lot.. I saved my whole day :-)
- To post as a guest, your comment is unpublished.· 3 years agoThanks a lot.....it really helped me.