Level 2 · Check and build the web funnel in Excel

What the sheet holds

Priya’s export has one row per month and device for 2025, 36 rows in all:

column	holds
A, B	month and device
C	sessions
D	engaged sessions: 3 or more pages viewed
E	converted sessions

A funnel follows the same sessions through stages that narrow down: sessions, engaged, converted. The rates between stages show where people drop out.

Step 1: check before you build

An export is someone else’s query. A funnel has a built-in rule you can test on every row: a later stage can never be bigger than an earlier one. In this data a session only converts after at least three pages, so converted must be less than or equal to engaged:

G2   =IF(E2<=D2,"ok","check")

Fill it down to G37, then count the failures and total the traffic:

J2   =COUNTIF(G2:G37,"check")
J3   =SUM(C2:C37)

J2 should be 0. J3 is worth comparing with a number the reader already knows, here the 2025 total on the web dashboard. When both agree, the export holds the right rows.

Step 2: one SUMIFS for the whole table

The table needs nine totals: three devices by three measures. Because the table’s columns J, K, L sit in the same order as the data’s columns C, D, E, one formula can do all of them if each reference is locked exactly as much as it must be:

J6   =SUMIFS(C$2:C$37,$B$2:$B$37,$I6)

reference	lock	why
C$2:C$37	rows only	filled right, it becomes D and E
$B$2:$B$37	column and row	the device column never moves
$I6	column only	every cell reads its own row’s device

Then the rates, each a stage divided by the one before it or by all sessions:

M6   =K6/J6      engaged %
N6   =L6/J6      conversion %
O6   =L6/K6      engaged to converted

Reading the result

Mobile and desktop browse alike: 44.6% and 44.0% of sessions reach three pages. The gap opens at the last step, where 9.4% of engaged desktop sessions convert against 5.6% on mobile. That points the mobile team at the basket, sign-in and payment, not at the product pages.

The full analysis in SQL or pandas goes one step further: it removes anonymous sessions, which never convert in this data, and follows the rates year by year as traffic moves to mobile. The sheet covers 2025 only, so it cannot show that shift.

Pitfalls

• Averaging monthly rates gives quiet months the same weight as busy ones. Total, then divide.
• A fully locked sum range ($C$2:$C$37) repeats the first column across the table.
• Typing the device into the formula ("mobile") breaks the fill down.