Tabellab
Menu
Courses Guides Pricing Log in Start free

Row Labels: What a PivotTable Does, Rebuilt with UNIQUE and SORT

Lesson 1 of 815 minLab

All lessons
  1. Row Labels: What a PivotTable Does, Rebuilt with UNIQUE and SORTLab15 minFree
  2. Values: SUMIFS over the Spilled Labels (H2#)Lab15 min Pro
  3. Cross-Tabs: Rows × Columns with UNIQUE, TRANSPOSE and One SUMIFS GridLab18 min Pro
  4. Show Values As: % of Grand Total, % of Row and % of ColumnLab18 min Pro
  5. Count, Average, Max and Distinct Count: COUNTIFS, AVERAGEIFS, MAXIFSLab16 min Pro
  6. Report Filters: Criteria Cells, (All) and a Drill-Down ListLab17 min Pro
  7. Grouping Dates by Month and Quarter: EOMONTH and Text KeysLab17 min Pro
  8. Capstone: A Subtotal Report, a Summary that Ignores It, and a ChartLab18 min Pro

You are not signed in — the lab works, but your progress is not saved. Start free

Read first

The same thing with a real PivotTable

  • Click any cell inside the data and choose Insert > PivotTable (tip: first press Ctrl+T to turn the data into a Table, so new rows are picked up on refresh).
  • In the PivotTable Fields pane, drag Category into Rows. Excel lists each category once and sorts it A→Z.
  • Drag Region above Category in Rows to get a two-level outline. Report Layout > Show in Tabular Form puts the two fields side by side, like our two-column UNIQUE.
  • The browser grid in these labs has no PivotTable button. Everything a PivotTable shows can also be built with formulas, and that is what you practise here.
Loading the spreadsheet…
Next lesson →