How to Aggregate MS Excel Daily Data with Pivot Table
Introduction
If you have daily data that still makes sense when aggregated into weekly or monthly data, then you can accomplish that very easily in Microsoft Excel, thanks to the pivot table.
You can apply this method to, for example, a data of “trucks arrived” or “shirts bought” per day since the total aggregate amounts would still make sense for longer time units.
Pivot Table Caution
However, if for example your data is “average temperature per day,” then it of course does not make sense to aggregate such data since the temperature for a week is not the aggregate sum of daily temperatures but their arithmetic mean.
With that precautionary note, here is how you can accomplish this procedure.
Example of a Pivot Table
Let’s say this is what your daily data looks like:
(1) Select all the data. Click Pivot Table in the INSERT tab:
(2) In the Create Pivot Table dialog box, select Existing Worksheet and then click on a cell for insertion point:
(3) Click DATA to insert it in the VALUES quadrant of the Pivot Table and click DAYS to insert it in the ROWS quadrant. Your Pivot Table will be created automatically:
(4) Now, select any of the days and right-click. From the pop-up menu select GROUP. In the GROUPING dialog box select DAYS and “7” in the NUMBER OF DAYS drop-down list:
(5) Click OK to create your aggregate table for WEEKS:
NOTE: The method to create by MONTHS (or any other time period listed in the GROUPING screen) is the same. Just select any other time unit you like instead of days.
Conclusion
If you have daily data to aggregate into weekly or monthly data, you can accomplish that easily in Microsoft Excel, thanks to pivot tables.
MORE INFO
How to Count the Number of Days with an Incident and Chart with Running Averages in MS Excel
Use ISNA Function to Handle “Not Available” Data in a MS Excel Table
How to Use SUMIF Conditional SUM Function in MS Excel
https://technicalcommunicationcenter.com2013/04/03/how-to-find-the-workday-a-specific-number-of-days-before-of-after-a-start-date-in-ms-excel-by-using-workday-intl-function/
https://technicalcommunicationcenter.com2014/05/14/how-to-use-sumif-conditional-sum-function-in-ms-excel-2010/
https://technicalcommunicationcenter.com2013/04/15/how-to-extract-text-or-numbers-from-character-delimited-cell-content-in-ms-excel/
https://technicalcommunicationcenter.com2016/10/19/how-to-count-the-number-of-days-with-an-incident-and-chart-with-running-averages-in-ms-excel/
