Value fields and aggregation in Excel - Step-by-Step Guide

Introduction

When you have a list of numbers and want to see totals, averages, or counts, value fields and aggregation help you do that quickly. They let you summarize data in tables without writing formulas.

When you want to see the total sales for each product in a list.

When you need to count how many orders were made by each customer.

When you want to find the average score of students in different classes.

When you want to quickly sum expenses by category in a budget.

When you want to see the maximum or minimum value in a group of data.

Steps

Step 1: Select the data range including headers

- Excel worksheet

The data range is highlighted on the sheet

💡 Make sure your data has clear column headers

Step 2: Click Insert tab

- Ribbon at the top of Excel

Insert tab options appear

Step 3: Click PivotTable

- Tables group on the Insert tab

Create PivotTable dialog box opens

Step 4: Confirm data range and choose where to place the PivotTable

- Create PivotTable dialog box

A blank PivotTable appears on the chosen sheet

Step 5: Drag a field (column name) to the Values area in the PivotTable Fields pane

- PivotTable Fields pane on the right

Excel shows a summary number like sum or count in the PivotTable

💡 By default, numbers are summed and text fields are counted

Step 6: Click the dropdown arrow next to the field in Values area and select Value Field Settings

- PivotTable Fields pane under Values

Value Field Settings dialog box opens

Step 7: Choose an aggregation type like Sum, Count, Average, Max, or Min

- Value Field Settings dialog box

PivotTable updates to show the selected aggregation

Before vs After

Before

A list of sales data with columns: Product, Region, Sales Amount

After

A PivotTable showing total Sales Amount for each Product

Settings Reference

Value Field Settings

📍 PivotTable Fields pane under Values area, accessed by dropdown arrow next to field

Choose how to summarize the data in the value field

Default: Sum for numeric fields, Count for text fields

Number Format

📍 Value Field Settings dialog box → Number Format button

Format how the summarized numbers appear

Default: General

Common Mistakes

Dragging a text field to Values expecting a sum

Text fields cannot be summed, so Excel counts them instead

Use numeric fields for sum aggregation or change aggregation to Count if counting is desired

Not refreshing the PivotTable after data changes

PivotTable does not update automatically, so it shows old data

Right-click the PivotTable and select Refresh to update values

Summary

Value fields let you summarize data by adding, counting, or averaging numbers.

You add fields to the Values area in a PivotTable to see these summaries.

You can change how Excel aggregates data using Value Field Settings.