Lesson 5 of 10

Pivot tables

A pivot table is Excel's most powerful analyst tool: it takes thousands of rows and summarises them by any dimension you like, with drag and drop and no formulas. It has four areas:

Rows	The dimension to split by, such as region.
Columns	An optional second dimension for a cross-tab, such as month.
Values	The number to calculate and how: sum, average, count.
Filters	Narrows the data, such as the online channel only.

Try it on 144 rows of Nakhla's sales over six months. Notice below the table: the same summary in SQL. A pivot table is exactly a GROUP BY:

Rows Columns Values Aggregate Channel filter

Region	Sum · Sales
Riyadh	192,030
Jeddah	159,915
Dammam	111,825
Total	463,770

144 data rows included

The same summary in SQL

SELECT region,
       SUM(sales_amount) AS value
FROM sales
GROUP BY region
ORDER BY region;

Watch outBefore pivoting, convert the data into an Excel “Table” (Ctrl + T). New rows are then included automatically when you hit Refresh.