Advanced Pivot Tables

Topic 14

Advanced Pivot Tables

Take your pivoting skills further. Learn how to present multiple Pivot Tables side-by-side on a single worksheet, link them to a single slicer across the entire workbook, and group numeric values (like salaries) into defined bracket ranges.

4 concepts Training file: StaffData.xlsx
Training File
Download StaffData.xlsx

Step 1 — Side-by-Side Pivot Tables

You are not limited to one Pivot Table per sheet. Placing tables side-by-side allows users to compare different dimensions of the same dataset (such as Building headcount vs. Department headcount) at a glance.

1
Create your first Pivot Table on a new sheet: Building in Rows, and Employee Name in Values (Count).
2
Return to the raw data sheet. Click Insert → PivotTable.
3
In the popup, choose Existing Worksheet. Click the location arrow, navigate to the summary sheet, and select a cell a few columns over (e.g. E1).
4
In this second Pivot Table, drag Department to Rows, and Employee Name to Values (Count).

The two summaries will render side-by-side on the same sheet (pivot8):

Side-by-Side Pivot Tables

Step 2 — Slicers and Report Connections

Slicers are visual filters. When you add a Slicer, it is initially linked only to the Pivot Table that was active when it was created.

1
Click inside the first Pivot Table (Building summary).
2
Go to PivotTable Analyze → Insert Slicer → check Status → OK.
3
Click "Full-time" on the slicer. You will observe that the first table filters to show only Full-time staff, but the second table (Department) remains unfiltered (pivot9).
Slicer Controlling One Table

To control both Pivot Tables with the same slicer, we use **Report Connections** (pivot10):

4
Right-click the Slicer title bar → select Report Connections...
5
In the dialog box, check the boxes next to both Pivot Tables → OK.
Report Connections Dialog
💡
Workbook-Level Connections (pivot11)
Slicers operate at the **Workbook level**, not the Sheet level. A single slicer on one tab can control Pivot Tables on completely different tabs, as long as they are generated from the **same dataset** (sharing the same Pivot Cache).

Once report connections are enabled, selecting "Full-time" on the slicer correctly filters both tables simultaneously (pivot11):

Slicer Controlling Both Tables

Step 3 — Grouping Numerical Data into Brackets

What if you need to classify staff into salary ranges? (e.g. how many make $1–$10,000, how many $10,001–$20,000, etc.)? Excel allows you to group numerical row labels into even bracket intervals instantly.

1
Create a new Pivot Table. Drag Salary to the Rows area, and Employee Name to the Values area (Count).
2
Right-click any numeric value under the Row Labels column → select Group...
3
In the Grouping window, configure the starting boundary, ending boundary, and step size:
• Starting at: 1 (By default, Excel inputs the smallest cell value, but you can override it).
• Ending at: 90000 (or keep it checked for auto-max).
• By: 10000 (the size of each bracket). Click OK.
Salary Grouping Brackets Pivot Table

Self-Check — Verify Your Advanced Pivot Output

Build the side-by-side pivot tables and grouped salary table inside StaffData.xlsx, then check your figures below.

Advanced Pivot Table Check Input numbers only
Summary Metric Your Value
Headcount in Logistics Department
Headcount in Manufacturing Department
Staff with salaries in the $1–$10,000 range
Staff with salaries in the $40,001–$50,000 range
Previous
Topic 13 — Pivot Tables