Latest posts by techwriter (see all)
- Test Your Knowledge of 4 Basic Fonts – Drag & Drop - January 27, 2017
- How NOT to Design a Web Site - January 25, 2017
- Hazards of Poorly Written Technical Documentation - December 26, 2016
© 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.