Charts & PivotTables · ~2 min read

PivotTable Grouping and Calculated Fields

Group dates and numbers, add calculated fields and refresh.

Video link coming soon
What you will learn

Step by step

1. Months, not dates

Twelve dates make twelve rows in a PivotTable. Group them, and you get six clean months. Then let the PivotTable calculate a bonus for you.

You will group by month, add a calculated field, and see the one rule that makes calculated fields lie.

PivotTable Grouping and Calculated Fields: Months, not dates
Months, not dates

2. Build the PivotTable

First the PivotTable, built the usual way: Control and T, Enter, then Alt, N, V, T, and place it at F1.

Shortcut: Ctrl + T

PivotTable Grouping and Calculated Fields: Build the PivotTable
Build the PivotTable

3. Group by month

Tick Date. Excel groups the dates into months by itself: Jan to Jun.

Tick Sales. Six months, one total each. January to June, twenty two thousand seven hundred.

If Excel does not group them for you, right-click a date and choose Group, then Months.

PivotTable Grouping and Calculated Fields: Group by month
Group by month

4. A calculated field

Now a calculated field: a new column the PivotTable computes from its own fields. Open it with Alt, J, T, J, F.

Name it Commission. In the formula box, type equals Sales times zero point zero five, and click OK.

A new column, five percent of every month. It updates with every refresh, and nothing is added to your data.

Quick pause: if calculated fields are new to you, a like really helps the channel. Because the next part is where they go wrong.

=Sales*0.05
PivotTable Grouping and Calculated Fields: A calculated field
A calculated field

5. The totals trap

Change the rule: only sales over two thousand earn ten percent. Open the dialog again with Alt, J, T, J, F.

Name it Bonus, type equals IF, Sales greater than two thousand, Sales times zero point one, zero, and OK.

Look at February: a bonus of three hundred thirty. But February's sales were eighteen hundred and fifteen hundred. Neither is over two thousand.

A calculated field never sees single rows. It works on the month's total, three thousand three hundred. For rules per sale, add a column to the Table instead.

=IF(Sales>2000,Sales*0.1,0)
PivotTable Grouping and Calculated Fields: The totals trap
The totals trap

6. Recap

Recap: dates group into months, calculated fields add columns from totals, and rules that test single rows belong in the source Table.

In the next lesson, you will filter a PivotTable with buttons, using slicers.

Have you ever trusted a calculated field that was wrong? Tell me in the comments.

PivotTable Grouping and Calculated Fields: Recap
Recap
What do you want to learn next? Tell us in the comments on YouTube: your requests decide the next tutorials.

← Previous tutorial: PivotTable Basics in Excel (Step by Step)
Next tutorial: Excel Slicers: Filter PivotTables with Buttons →