Pinterest Pixel

Show Averages With an Excel Pivot Table

A Pivot Table is the most powerful feature within Excel as it allows you to analyze your... read more

Download Excel Workbook
John Michaloudis
Posted on

Steps To Follow

Overview

Show Averages With an Excel Pivot Table | MyExcelOnline Show Averages With an Excel Pivot Table | MyExcelOnline

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. You can even show the Average in Pivot Table instead of Sum!

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 you a Sum of Values.

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

download excel workbookAVERAGE.xlsx

Step By Step Guide

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

Show Averages With an Excel 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.

See also  A PivotTable report cannot overlap another PivotTable report - Solution

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.

Show Averages With an Excel Pivot Table

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

  • Region field in the Row Area
  • Year field in Columns Area
  • Sales field in Values Area

Show Averages With an Excel Pivot Table

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

Show Averages With an Excel Pivot Table

OR,

You can simply click on the arrow next to the Sum of Sales field mentioned in the Values Area and select Value Field Setting.

See also  Data Bars, Color Scales & Icon Sets

Show Averages With an Excel Pivot Table

In the Value Field Setting dialog box, Select Average in the Summarize value by and Click OK.

Show Averages With an Excel Pivot Table

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

Show Averages With an Excel Pivot Table

Format your Pivot Table Average number values

Many a time, when you are dealing with averages the result may end up with a different mixture of decimal places for different values.

Show Averages With an Excel Pivot Table

It would be ideal to format the result for a better presentation. To do so:

STEP 1: Right Click on any value cell and select Value Field Setting.

Show Averages With an Excel Pivot Table

STEP 2: In the Value Field Setting dialog box, select Number Format.

Show Averages With an Excel Pivot Table

STEP 3: In the Format Cells dialog box, select Number under Category and type 2 for Decimal Places. Click OK.

Show Averages With an Excel Pivot Table

STEP 4: Click OK.

Show Averages With an Excel Pivot Table

All values in the Pivot Table will now have 2 decimal points. The numbers look more presentable and are easy to compare.

Show Averages With an Excel Pivot Table

Conclusion

In addition to displaying averages, you can even show different calculation types such as count, maximum, minimum, product, and much more.

See also  Microsoft Excel Online Course Private Access - 20+ Hours Beginner to Advanced Course

There are a lot more that you can do using Excel Pivot Table, Click here to know all about it!

Further Learning:

Make sure to download our FREE PDF on the 333 Excel keyboard Shortcuts here:

If you like this Excel tip, please share it
Show Averages With an Excel Pivot Table | MyExcelOnline Show Averages With an Excel Pivot Table | MyExcelOnline

Get Video Training

Advance your Microsoft Excel & Office Skills with the MyExcelOnline Academy!

Dramatically Reduce Repetition, Stress, and Overtime!
Exponentially Increase Your Chances of a Promotion, Pay Raise or New Job!

Learn in as little as 5 minutes a day or on your schedule.

Learn More!

Share to...