Latest posts by techwriter (see all)
- What is the Readability Index of Your Writing? - November 20, 2017
- Should Technical Writing be Boring? And if Yes, Why? - November 15, 2017
- How to Create a Custom-Designed Header in MS Word that Would be Available to All Other Word Documents - November 13, 2017
© Ugur Akinci
If you have daily data that still makes sense when aggregated into weekly or monthly data, then you can accomplish that very easily in MS Excel, thanks to pivot tables.
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.
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.
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 PivotTable 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.