Level 3 · Average pages per visit, mobile only

Sum and Count are only two of the ways a PivotTable can summarise. For “how many pages does a visitor look at” you need an Average. And Thomas only wants mobile visitors, so the table needs a filter first.

The Filter box

The sheet holds all 46 sessions from Sunday, October 5, in A1:F47: 28 on mobile, 14 on desktop and 4 on tablets. A report filter keeps the records whose field has the value you pick and ignores the rest, without touching the data:

Box	Field
Filter	device → mobile
Rows	source
Values	pages_viewed, summarised by Average

The filter shows above the table, in the first row: device | mobile, then a blank row, then the PivotTable itself. Excel draws it the same way. Pick another device and every number below changes.

Average, and the Grand Total

source        Average of pages_viewed
direct              3.67
email               7.00
organic             2.80
paid                4.00
social              5.67
Grand Total         4.00

Each row divides that source’s pages by its number of sessions: the 6 direct sessions viewed 22 pages, so 22 / 6 = 3.67. The Grand Total is not the average of the five numbers above it (that would be 4.63). It is all mobile pages divided by all mobile sessions: 112 / 28 = 4.00. A PivotTable always recalculates the total from the records, which is what you want.

The 0.00 format on I4:I9 keeps the averages readable; in Excel you set it under Value Field Settings → Number Format, so it sticks when the pivot refreshes.

Small samples

Email leads with 7 pages per visit, from just 3 sessions. One enthusiastic visitor can move that number a lot. Before Thomas redesigns around it, add session_id as a second Values field with Count (in Excel you can drag several fields into Values) or check a full month. A PivotTable makes numbers look equally solid; they are not.

Common mistakes

• Averaging averages. Copying the five source averages into a cell and taking AVERAGE of them weighs a 3-session source like a 10-session one.
• Filtering the data instead. Deleting desktop rows from the sheet “to filter” destroys the data for the next question. Use the Filter box.
• Leaving Sum on. Sum of pages_viewed answers “how many pages in total”, not “per visit”.
• Forgetting the filter is on. Excel shows it only in that small cell above the table. Say “mobile only” in the title of anything you send.