Pivot Table Two-Way Average: How to Build One

A two-way average in a pivot table shows the mean of a value field broken down by two dimensions at once, such as average sales by product and by region. To create one, drag one field into the Rows area, another into the Columns area, and the value you want to average into the Values area, then change its calculation to Average.

Formula

The average (arithmetic mean) is the sum of all values in a group divided by the count of those values. In a two-way pivot table, the formula applies separately to each intersection of row and column, so for a given row category R and column category C:

Average = (Sum of values where row = R and column = C) / (Count of values where row = R and column = C)

Here, the values are the numbers in the data source that you have dragged into the Values area. The row and column fields define the grouping. Each cell in the pivot table is one result of this formula for a specific pair of categories.

Worked Example

Suppose you have a table of sales data with three columns: Product, Region, and Sales Amount. The data has 12 rows, with products A and B and regions North and South. The default pivot table would show the following raw data (Sales Amount in dollars):

• Product A, North: 100, 120, 110 → sum 330, count 3, average 110
• Product A, South: 90, 130 → sum 220, count 2, average 110
• Product B, North: 80, 95, 105 → sum 280, count 3, average 93.33
• Product B, South: 70, 85, 75, 60 → sum 290, count 4, average 72.5

When you build the pivot table with Product in Rows, Region in Columns, and Sales Amount in Values set to Average, you get exactly those four averages. The Grand Total row and column show the overall average for each row and column, which is the sum of all values in that row or column divided by the count of those values, not the average of the cell averages.

Why Use a Two-Way Average Instead of a Single Average?

A single average (one row field, one column field, or just one value) gives you a broad view. A two-way average reveals interactions: how the average changes across two dimensions simultaneously. For example, average sales by product alone might hide that one product does well only in one region. By adding Region as a column, you see the pattern clearly. This is especially useful when you need to compare performance across categories, such as average test scores by class and by gender, or average delivery time by carrier and by day of week.

Two-way averages are common in business reports, academic analysis, and quality control. They help you spot trends that a single grouping would miss. They also make it easy to compare relative performance: you can see at a glance which combination has the highest or lowest average.

Step-by-Step: How to Create a Two-Way Average in Excel or Google Sheets

The exact menu names differ slightly between Excel and Google Sheets, but the logic is the same.

1. Select your data range, including headers.
2. Insert a pivot table (Excel: Insert > PivotTable; Google Sheets: Insert > Pivot table).
3. In the PivotTable Fields panel, drag the first category field (e.g., Product) to the Rows area.
4. Drag the second category field (e.g., Region) to the Columns area.
5. Drag the numeric value field (e.g., Sales Amount) to the Values area. It will default to Sum.
6. Click the dropdown arrow next to the value field in the Values area (or right-click the pivot table and choose Value Field Settings).
7. Select “Average� instead of “Sum�.
8. Click OK. The pivot table now shows the average for each row-column combination.

If you have multiple numeric fields, you can drag more than one to the Values area to see averages for each.

Common Mistakes and How to Avoid Them

• Using Sum instead of Average: The default is Sum. If you forget to change it, you’ll see totals, not averages. Always check the Value Field Settings.
• Including blank or text cells in the value field: Pivot tables ignore text in value fields, but they also ignore blank cells. If a cell is blank, it is not counted in the average. That can skew results if blanks represent zero. Decide whether a blank should be treated as zero and fill it accordingly.
• Misinterpreting Grand Totals: The Grand Total row and column show the average of all values in that row or column, not the average of the cell averages. If you have unequal counts per cell, the grand total will not equal the mean of the cell averages.
• Using the wrong field as the value: Make sure the field you drag to Values is numeric. If you drag a text field, the pivot table will show a count, not an average.
• Forgetting to refresh: If you change the source data, the pivot table does not update automatically. Right-click and select Refresh to update the averages.

How to Read a Two-Way Average Pivot Table

Each cell is the average for that specific combination of row and column. For example, if your row is “Product� and your column is “Region�, the cell at the intersection of “Product A� and “North� is the average sales for Product A in the North region. The row total (Grand Total column) is the average for that product across all regions. The column total (Grand Total row) is the average for that region across all products. The bottom-right cell is the overall average of all values in the data.

When comparing cells, keep in mind the number of underlying data points. A cell based on 2 values is less reliable than one based on 50. Some pivot tables allow you to add a count field to the same table to see the sample size. You can do this by dragging the same value field to the Values area again and setting it to Count.

Alternatives to a Two-Way Average

If you need more than two dimensions, you can add a third field to the Rows area (creating a hierarchical row layout) or to the Columns area (creating a multi-level column layout). You can also use a slicer or filter to focus on a subset of data. For more advanced analysis, you can use the AVERAGEIFS function in Excel or Google Sheets to calculate averages based on multiple criteria without a pivot table. For example, =AVERAGEIFS(SalesAmount, Product, "A", Region, "North") gives the same result as the corresponding pivot cell. However, a pivot table is often faster and more flexible for exploring data interactively.

Frequently Asked Questions

Why does my pivot table show a count instead of an average?
This happens when the field you dragged to the Values area is not numeric, or when you haven’t changed the calculation from Sum to Average. If the field is text, the pivot table counts the number of entries. Change the field to a numeric column, or right-click the value field and select Average.

Can I average two different columns in the same pivot table?
Yes. Drag both numeric fields to the Values area. Each will appear as a separate column in the pivot table. You can set one to Average and the other to Sum, or both to Average.

How do I show the number of data points behind each average?
Drag the same value field to the Values area again and set it to Count. You’ll see both the average and the count in the same pivot table, which helps you judge the reliability of each average.

What is the difference between a two-way average and a two-way sum?
A sum adds up all values in each group; an average divides that sum by the count of values. A two-way sum is useful for totals, while a two-way average is useful for comparing typical values across groups. The layout is identical; only the calculation changes.

Does a blank cell affect the average?
Yes. Pivot tables ignore blank cells in the value field, so they are not counted in the denominator. If a blank represents a real zero, you should fill it with 0 before creating the pivot table to get an accurate average.

Can I create a two-way average in Google Sheets?
Yes. The process is the same as in Excel: insert a pivot table, drag one field to Rows, another to Columns, and the numeric field to Values, then change the summarize by option to AVERAGE.

Related

• Show Battery Percentage macOS Big Sur: How-To
• Fuji Digital Camera Photo Recovery: How To
• Steam Framerate Estimator: How It Works

in t | Finish time | min | 50 | 0
in d | Distance | km | 10 | 0
out pace | Average pace | min/km | t / d | 2
out speed | Average speed | km/h | d / (t / 60) | 2