How to Average by Day/Month/Quarter/Year with Pivot Table in Excel

This post will guide you how to average the values by day/Month/quarter/year in your worksheet in Excel. How do I calculate the average of your data by day/Month/quarter/year with pivot table in Excel.

Get the Averages by Day/Month/Quarter/Year with Pivot Table

Assuming that you have a list of data in range A1:B9, in which contain dates and sale values. And you want to calculate the sales average by Day/Month/Quarter/Year, you can create a pivot table based on your data, and then group them by day/Month/Quarter/Year to get the averages by Day/Month/Quarter/Year. Here are the steps:

#1 select the range of cells that you want to create pivot table based on.

average by month with pivottable1

#2 go to INSERT tab, click the PivotTable command under the Tables group. And the Create PivotTable dialog will appear.

average by month with pivottable2

#3 select Existing Worksheet radio button in the Create PivotTable dialog box, and select one cell reference as the destination to place the pivot table. Click Ok button. And the Pivot Table Fields pan will appear in the right side of window.

average by month with pivottable3

#4 choose Date and Sales fields to add to pivot table report. You will see that a pivot table is created based on the selected range of cells.

average by month with pivottable4

#5 select any cell in the date column in the existing Pivot table. Right click on it, and select Group… from the popup menu list. And the Grouping dialog will open.

average by month with pivottable5

#6 select Days or Months or Quarters or Years option from the By list box in the Grouping dialog box. Click Ok button. At this example, select Months option in By list box.

average by month with pivottable6

#7 you would notice that your pivot table has been grouped by Months starting with Jan as shown below:

average by month with pivottable7

Note: you can use the above steps to group dates by Days, quarters or years in the pivot table.

#8 right click on the Sum of Sales field, and select Summarize Values By from the popup menu list, and select Average.

average by month with pivottable8

#9 you would notice that the averages by month will be calculated in the pivot table.

average by month with pivottable9


Leave a Reply