A Pivot Table is the most powerful feature within Excel as it allows you to analyze your data in many different ways, all with a press of a button.

The Summarize Values By option allows you to choose a type of calculation (Sum, Count, Average, Max, Min, Count Numbers Product, StdDev, StdDevp, Var, Varp) to summarize data from the selected field.

As a default when you drop in a values field in the Values area of the Pivot Table it will Sum it for you and give yo a Sum of Values.

You can change this calculation to an Average very easily, which will show you the Average values for your data.


STEP 1: Click in your data and go to Insert > Pivot Table

insert pivot table

STEP 2: This will bring up the Create Pivot Table dialogue box and it will automatically select your data`s range or table.

In the Choose where you want the PivotTable report to be placed you can either choose a New Worksheet or an Existing Worksheet.

If you choose a New Worksheet it will place the Pivot Table in a brand new worksheet (e.g. Sheet2).

If you decide to put the Pivot Table in an Existing Worksheet, you will need to select the location by pressing the red arrow, choosing the cell where you want your Pivot Table to be placed and then pressing the ENTER key twice to confirm.

create pìvot table

STEP 3: You will now need to drag and drop the Fields in the different areas of your Pivot Table

pivot table field list2

STEP 4: Now that your Pivot Table is set up, you need to Right Click in any of the Pivot Table values and choose Summarize Values By > Average

summarize values by average

STEP 5: Now you have your Pivot Table report showing the Average Sales values per Region for each year:

avreage pivot table




If you like this Excel tip, please share itEmail this to someone


Pin on Pinterest

Share on Facebook

Tweet about this on Twitter

Share on LinkedIn

Share on Google+

Related Posts

Show The Percent of Parent Row Total With Excel Pi... Excel Pivot Tables have a lot of useful calculations under the SHOW VALUES AS option and one that can help you a lot is the PERCENT OF PARENT ROW TOTAL calculation.This is a new calculation in Excel 2010 and onwards.This option will immediately calculate the percentages f...
How to Calculate Percentage in Excel In calculating percentages in Excel, there are a lot of ways that you could do this:What is the percentage of a number? What is the percentage change of value i.e. The percentage increase or decrease What is the proportion against a total value?Let us go over ...
Remove Duplicates in an Excel Table When you have duplicates values within your Excel Table there is a quick and easy way to remove those values.The duplicate values could be all over your Excel Table and sometimes it takes valuable time trying to locate those duplicates and then deleting them.Not to worry,...
How to Use Flash Fill In Excel  Flash Fill in Excel is a new feature that was introduced in Excel 2013.It is very handy as Excel predicts the rest of your inputs based on the first entry that you have placed. Once its prediction is correct and you confirm it, it will fill the rest of the rows lite...