PivotTables in depth

A sales log has collected 2,400 rows over two years: date, branch, product, revenue, cost. Management wants quarterly totals, each branch's share, month-on-month growth and the profit margin — as a report filtered with buttons. You learned the PivotTable basics earlier; now we move on to its professional tools.

Prepare the source

• One header row with a unique name for every column; no blank rows or columns, no merged cells.
• One data type per column: only real dates in the date column, only numbers in the amount column.
• Turn the source into an Excel Table with Ctrl+T and name it (Table Design › Table Name: Sales) — new rows will be included automatically on refresh.
• For a readable layout: Design › Layout › Report Layout › Show in Tabular Form and Repeat All Item Labels.

Grouping

1. 1
   Dates by month, quarter and year

   Drag Date to Rows — Microsoft 365 often groups it by itself. Manually: right-click a date › Group… › select Months, Quarters, Years › OK.

2. 2
   Numbers into bands

   Put Amount in Rows, right-click › Group… › Starting at 0, Ending at 3000, By 500. You get 0–499, 500–999… This shows how orders are distributed by size.

3. 3
   Text items into your own group

   Select Baku and Sumgait with Ctrl, right-click › Group — “Group1” appears; rename it “Absheron”. To undo, use Ungroup.

Show Values As and calculated fields

1. 1
   Create a calculated field

   PivotTable Analyze › Calculations › Fields, Items, & Sets › Calculated Field…. Name: Profit, Formula: =Revenue-Cost (add fields from the list with Insert Field) › Add.

2. 2
   Add a margin

   In the same dialog add a second field: Margin = =Profit/Revenue. Give it a percentage format in Values (Value Field Settings › Number Format › Percentage).

Slicers, timelines, PivotCharts and refresh

1. 1
   Add a slicer

   PivotTable Analyze › Filter › Insert Slicer › tick Branch and Product. Clicking a button filters, Ctrl+click picks several items, and the funnel icon at the top right clears the filter.

2. 2
   Add a timeline

   PivotTable Analyze › Filter › Insert Timeline › Date. Drag the bar to pick a period; switch between YEARS, QUARTERS, MONTHS and DAYS at the top right.

3. 3
   One slicer — several reports

   Select the slicer › Slicer › Slicer › Report Connections › tick every PivotTable built on the same source. Now one click filters the whole dashboard.

4. 4
   Build a PivotChart

   PivotTable Analyze › Tools › PivotChart (or Alt+F1 inside the PivotTable). The chart obeys the same filters; hide the field buttons with PivotChart Analyze › Show/Hide › Field Buttons.