Level 6 · Where does each field go?

Building a PivotTable is quick once you know which box each field belongs in. Getting that right is the whole skill, so it is worth practising on its own, without the clicking.

Say the question as a sentence

Most pivot requests fit one pattern: numbers by label by label, for a filter. Megan’s becomes “revenue, by category, by month, for one channel at a time”. Each part has a box:

Part of the sentence	Box	Here
the numbers	Values	Sum of line_total
first “by”	Rows	category
second “by”	Columns	month
“for one … at a time”	Filters	channel

Rows usually gets the field with more items (six categories), Columns the one with fewer (three months), so the table grows downwards and stays readable.

Filters and slicers

A field in Filters narrows the whole table without adding rows or columns: pick app and every number shows app orders only. A slicer is the same filter as a row of buttons, which is what “one click” means for someone in a meeting. In Excel: click inside the PivotTable → PivotTable Analyze → Insert Slicer → tick channel. One slicer can drive several PivotTables at once (Report Connections), which is how simple dashboards are wired. This practice sheet has the Filter box but no slicers.

A profit that moves with the table

A fixed-margin profit is line_total × 0.25. If you type it next to the pivot, it only matches the current filter and goes stale on the next refresh. Two ways keep it live:

• a helper column in the data, profit = line_total*0.25, added to Values as a Sum (the same trick you used for months in level 5);
• a calculated field: PivotTable Analyze → Fields, Items & Sets → Calculated Field, formula = line_total * 0.25.

Both follow every filter and slicer. A calculated field works on the summed values, which is fine for a fixed percentage but wrong for anything like an average of ratios, so the helper column is the safer habit.

Common mistakes

• The switch field in Rows. Three lines per category is a different table from a switch.
• Numbers in Rows. Every distinct amount becomes its own row.
• Summing labels. Sum of month or Sum of order_id adds up codes and gives nonsense.
• Hand-typed extras. Anything typed beside a pivot stops matching it after the next filter change or refresh.