Lessons Lesson 99

PIVOTBY: Rows and Columns in One Formula

About this lesson

PIVOTBY groups down the side and across the top at once. The aggregate goes where each row meets each column. It is GROUPBY with a second dimension, and it builds the table a pivot table builds.

By the end you can

  • build a two-way summary with one PIVOTBY.
  • turn the table on its side by swapping two arguments.
  • check a pivot against its own totals.

Practises PIVOTBY GROUPBY SUMIFS INDEX

By Ali, Founder, Excel Lessons

The idea

GROUPBY summarises by one thing. PIVOTBY summarises by two. The first argument runs down the side, the second across the top, and the aggregate fills the middle. Totals appear on both edges without being asked for. That is the table this module built by hand with SUMIFS, in one formula.

The top-left cell is empty, and it is meant to be. It sits above the row labels and to the left of the column labels, so it names neither. Swap the first two arguments and the table turns on its side: same numbers, read the other way.

The mistake to watch for

Reading the middle of a pivot by position and trusting it later. The table grows a row when a new region appears, and a column when a new product does. So the cell that held North and Bikes moves. Read the edges to check the middle. Every row total and every column total has to add up to the corner.

Where this comes up again

This lesson needs JavaScript to run. Everything below is the lesson in full, but you cannot type into the grid or be marked.

Type a formula

PIVOTBY takes rows, then columns, then values, then the aggregate.

In D2, type:

=PIVOTBY(A2:A6,B2:B6,C2:C6,SUM)

D2
Row A B C D E F G
1 Region Product Amount Sales by region and product
2 North Bikes 500
3 South Bikes 200
4 North Cars 300
5 South Cars 100
6 North Bikes 150
7 How many sales
8
9
10
11 Turned on its side

Every step

  1. 1

    PIVOTBY takes rows, then columns, then values, then the aggregate. In D2, type: =PIVOTBY(A2:A6,B2:B6,C2:C6,SUM).

    Hint. Region first, product second, amount third.

  2. 2

    INDEX reads the table by row and column. Read =INDEX(PIVOTBY(A2:A6,B2:B6,C2:C6,SUM),2,2) and say what it returns.

    Hint. North and Bikes.

  3. 3

    The corner is the total of everything. Read =INDEX(PIVOTBY(A2:A6,B2:B6,C2:C6,SUM),4,4) and say what it returns.

    Hint. The same grand total the GROUPBY gave.

  4. 4

    Counting instead of adding is one word. In D8, count the sales per region and product with PIVOTBY and COUNT.

    Hint. Same three ranges, COUNT instead of SUM.

  5. 5

    Swapping the first two arguments turns the table on its side. Leave the totals off with two zeros. In D12, build that table with PIVOTBY.

    Hint. Swap the first two arguments.