Show Averages With an Excel Pivot Table

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 1: Click in your data and go to 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.

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

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

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.

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

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

Format your Pivot Table Average number values

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.

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

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

STEP 4: Click OK.

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

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.

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:

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.

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.

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.

John Michaloudis

Founder & Chief Inspirational Officer

at

MyExcelOnline.com

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.