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.

DOWNLOAD EXCEL WORKBOOK

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

AVERAGE

HELPFUL RESOURCE:

 

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

email

Pin on Pinterest

Pinterest

Share on Facebook

Facebook

Tweet about this on Twitter

Twitter

Share on LinkedIn

Linkedin

Share on Google+

Google+

Related Posts

Conditionally Formatting A Drop Down List In a previous post I showed you how to Create a Drop Down List in a Table.    We are now going to take this concept one level further and apply some conditional formatting to the drop down data validation list. This is useful if you want to highlight when a job is completed, c...
Find Errors with Go to Special Constants Say you have a data set and want to make sure that each column contains what it is supposed to. For example, say you have a column which contains Dates and you want to check that there are no cells which contain Text. You can easily check this by highlighting that column an...
Group By Month With Excel Pivot Tables Before I was a Pivot Table guru, I had to get individual rows of daily sales and group them into a report showing the monthly sales during the year. Grouping these would take a ton of effort using Formulas: Extracting the month and year from each transactional date; Th...
Sorting a Pivot Table There are a few ways that you can Sort a Pivot Table.  You can Sort the Row/Column Labels as well as Values within a Pivot Table.  Below I show you three quick ways... DOWNLOAD WORKBOOK Helpful Resource: