Pinterest Pixel

Show Averages With an Excel Pivot Table

John Michaloudis
When working with large datasets in Excel, I often find myself needing to summarize information quickly and efficiently.
One of my go-to tools for this is the Pivot Table.

While many people use Pivot Tables to calculate sums or counts, they can also be incredibly useful for showing averages.

In this post, I'll walk you through the process of displaying averages in a Pivot Table, simplifying your data analysis and helping you extract meaningful insights from your spreadsheets.

Let’s dive in!

Key Takeaways

  • A Pivot Table in Excel has a robust feature called ‘Summarize Values By,’ which allows users to show the average of a dataset, amongst other calculations like Sum, Count, Max, Min, and more. This function can be easily utilized, changing the default Sum calculation to Average, which will provide the average values for the data within a Pivot Table.
  • 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.

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.

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

 

Advanced Pivot Table Techniques

Creating Custom Calculations with DAX Measures

Understanding the ins and outs of Excel’s Pivot Tables can transform heaps of data into insightful reports. One powerful feature involves using Data Analysis Expressions (DAX) to create custom calculations, providing you with deeper analysis. For instance, explicit measures, such as Total Sales and Daily Average, allow you to tailor your data precisely to your needs. To get started, you simply right-click on the Table name in the Pivot Table Field List, select “Add Measure,” and input your formula like so: =SUM([Amount]) for Total Sales. This explicit mention spells out exactly what the calculation should be, and once created, these measures are easily reusable in other formulas or pivot tables, saving time and adding efficiency.

Analyzing Trends with Date Groupings and Slicers

Diving deeper into your data’s story often means understanding the trends over time. Fortunately, Excel’s date grouping feature is just what you need to unravel this narrative. You can break down your data into years, quarters, and months by simply adding the Date field to the Rows area of your Pivot Table. Right-click on the dates and select “Group”, then choose the intervals that matter to you. By turning on subtotals, you’ll get a snapshot of each time segment’s performance at a glance.

Moreover, throw in the power of slicers, and you’ve got a dynamic tool that simplifies your interaction with complex data sets. Slicers can filter the data in your Pivot Table to hone in on specific periods or categories, providing an interactive element for your report.

When you group dates, consider also the fiscal calendar using the Calendar Table if your company’s financial year doesn’t align with the calendar year. It ensures your trends align with financial reporting standards.

Use One Slicer for Two Excel Pivot Tables

Visualizing Data with Pivot Charts

Integrating Pivot Charts to Display Averages

Visualizing averages in your data becomes a breeze with the integration of Pivot Charts. Adding a Pivot Chart to display averages can help reveal key insights and trends that might not be as identifiable in raw data formats. To incorporate an average line in your Pivot Chart, you can follow these steps:

  1. Add a column next to your source data, naming it “Average.”
  2. Enter the formula =AVERAGE($C$2:$C$15) to calculate the average, drag down the fill handle to apply it to your data range.
  3. Refresh your Pivot Chart by clicking the “Refresh” button on the Analyze tab.
  4. Add the Average field to the Values section of your PivotChart Fields, then select “Add to Values”.
  5. Right-click the new average field in the Values area, and choose “Change Series Chart Type.”
  6. In the combo chart options, adjust the Sum of Average field to display as a line chart.

By now, your Average line should be visible on your Pivot Chart, providing a clear and immediate visual average across your data range. Here’s a sample of how a Pivot Chart looks like:

Printing a Pivot Chart

Optimizing Pivot Table Performance

Navigating Common Pitfalls with Pivot Tables

Pivot Tables are incredibly powerful, but they’re not without their quirks. Knowing how to navigate around common stumbling blocks ensures your experience with Pivot Tables remains frustration-free.

First up, remember that data cleanliness is next to Pivot godliness. Messy source data often leads to inaccurate Pivot Tables. This means ensuring your dataset is free from blank rows, correct data types are used, and consistent formatting is maintained. Avoid using multiple headers, and make sure each column in your data set has a unique name.

Another trap users fall into is not refreshing their Pivot Tables after data changes. Pivot Tables don’t automatically update when their source data does; you must remember to refresh them by right-clicking and selecting “Refresh,” or by using the refresh button on the PivotTable Tools | Analyze tab.

Sometimes, you might end up with duplicate values in your Pivot Table. This typically happens if your original data has duplicates or if you’ve accidentally counted values more than once within the data structure. A careful review and use of the “Remove Duplicates” feature in Excel’s Data tab can prevent this issue.

If your data range grows over time, you also need to expand the data range of your Pivot Table source. Otherwise, it won’t include new data in the analysis. You can sidestep this issue by converting your range into a table using the Table feature in Excel, ensuring the Pivot Table includes all current and future data without the need to manually adjust the range.

Remove Duplicates in an Excel Table

Speeding Up Your Pivot Table Calculations

If you’re working with particularly large datasets, you may find your Pivot Table calculations taking longer than ideal. However, several strategies can help speed up these calculations and keep your workflow zipping along.

  • Limit Data Source Range: Avoid pulling in more data than necessary. Tailor your data source range closely to the data you need to analyze. This reduces the load on your Pivot Table and speeds up re-calculation times.
  • Use Excel Tables as Data Source: Converting your range to an Excel Table not only makes dynamic data range updates but they are also optimized for performance in Pivot Tables.
  • Avoid Unnecessary Calculated Fields: Each calculated field can slightly slow down your Pivot Table. Review and ensure that you only include essential calculations in your Pivot Table.
  • Defer Layout Updates: When setting up or making significant adjustments to your Pivot Table, turn off the ‘PivotTable Options > Defer Layout Update’ feature. This allows you to make multiple changes without Excel recalculating the Pivot Table after each change.
  • Pivot Table Options: In the ‘PivotTable Options’ dialog box, under the Data tab, deselect ‘Save source data with file’ and select ‘None’ for Number of items to retain per field. This reduces the file size and in turn can speed up calculation time.
  • Multiple Pivot Tables: If you’re working with several Pivot Tables from the same source data, use the ‘PivotTable Options > Data’ to enable the ‘Share this data model’ option. This allows them to share cache and improve performance.

Following these strategies can significantly enhance your experience by making your Pivot Table interactions much snappier.

FAQs

How Do I Get Started with Pivot Tables in Excel?

To kick off your journey with Pivot Tables in Excel, dive in by gathering some data you’d like to analyze. Once you’ve got your data:

  1. Click any single cell inside the data set.
  2. On the ribbon, go to the Insert tab and click on the PivotTable icon.
  3. In the Create PivotTable dialog box, Excel will automatically select the data for your PivotTable. You can also specify a particular range if needed.
  4. Choose where to place your PivotTable – in a new worksheet or an existing one, then click OK.

Voila! You’ve created your first Pivot Table. Now, it’s time to explore by dragging and dropping fields to different parts of the PivotTable to start analyzing your data.

Running Total In % with an Excel Pivot Table

What Are the Best Practices for Showing Averages in a Pivot Table?

When it comes to displaying averages in a Pivot Table effectively:

  1. Ensure your data is prepared correctly—clean and free from duplicate rows or irrelevant columns.
  2. Add your data to the Values area of the Pivot Table and choose the ‘Average’ function instead of the default ‘Sum’.
  3. Double-check that the field you are averaging is not inadvertently counting blank or text values as zero, which would skew your average.
  4. Consider using a separate field for the count of data points if you need to reference how many entries contribute to the average.

It’s about maintaining data integrity and presenting your info in such a way that the ‘story’ behind the numbers becomes clear and actionable.

Conclusion

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

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

If you like this Excel tip, please share it


Founder & Chief Inspirational Officer

at

John Michaloudis is a former accountant and finance analyst at General Electric, a Microsoft MVP since 2020, an Amazon #1 bestselling author of 4 Microsoft Excel books and teacher of Microsoft Excel & Office over at his flagship MyExcelOnline Academy Online Course.

See also  Autofill Formulas in an Excel Table

Steps To Follow

30+ Excel & Office Courses
One Dollar Trial

$1 Trial for 30 days

Access for $1

Cancel Anytime

Star 30 Days - Full Access Star

One Dollar Trial

$1 Trial for 30 days!

Access for $1

Cancel Anytime

One Dollar Trial
  • Get FULL ACCESS to all our Excel & Office courses, bonuses, and support for just USD $1 today! Enjoy 30 days of learning and expert help.
  • You can CANCEL ANYTIME — no strings attached! Even if it’s on day 29, you won’t be charged again.
  • You'll get to keep all our downloadable Excel E-Books, Workbooks, Templates, and Cheat Sheets - yours to enjoy FOREVER!
  • Practice Workbooks
  • Certificates of Completion
  • 5 Amazing Bonuses
Satisfaction Guaranteed
Accepted paymend methods
Secure checkout

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