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.

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

#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.

#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.

#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.

#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.

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

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.

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