How to build a funnel analysis spreadsheet that actually helps

by

TL;DR

You can build a working funnel analysis spreadsheet in about two hours using Google Sheets or Microsoft Excel, a CSV export from your analytics or CRM tool, and some basic formulas. The result is a reusable file that shows you where users drop off at each stage, lets you filter by date range or segment, and flags underperforming steps with color. No SQL required, no paid tools beyond what you already have.

What You Need Before You Start

  • A Google account (free tier works fine) or Excel 2019 / Microsoft 365
  • Raw event or stage data exported as CSV. your data needs at minimum: a user ID column, a stage name or number column, and a timestamp
  • At least 500 rows of data. smaller sets produce conversion rates that are statistically meaningless
  • A clear definition of your funnel stages written down before you open the spreadsheet. if you skip this you will redefine the funnel three times mid-build
  • Optional: Mixpanel or Amplitude free tier, if you want to cross-check your numbers against a dedicated tool
  • Optional: a pivot table refresher. Google’s own support doc at support.google.com/docs covers the basics in under ten minutes

Step 1: Define Your Funnel Stages on Paper First

Open a blank doc, not the spreadsheet. Write down every stage a user passes through from first touch to conversion. Be ruthless about what counts as a stage. A stage is a discrete action you can verify with data, not a feeling. For a SaaS signup funnel that might look like: Visited Landing Page, Started Signup, Confirmed Email, Completed Onboarding, Made First Payment.

Keep it to five to seven stages. More than that and the chart becomes unreadable. Fewer than four and you are not learning anything you did not already know.

Once you have your list, assign each stage a number (1 through N). This number becomes a sort key later. Write the exact column names from your CSV export next to each stage so you know exactly how they map.

You should now see a short numbered list you can keep open next to your spreadsheet for the rest of this build.

Step 2: Import and Clean Your Raw Data

Create a new spreadsheet. Name the first tab raw_data. Import your CSV using File > Import in Google Sheets or Data > From Text/CSV in Excel.

Once imported, you need three clean columns at minimum: user_id, stage, and event_timestamp. Delete every column you do not need. Excess columns slow down formulas and make the file confusing to share.

Check for duplicates. A user completing the same stage twice inflates your top-of-funnel count and breaks your conversion math. In Google Sheets, use:

=COUNTIFS($A$2:$A$10000,A2,$B$2:$B$10000,B2)

Paste that in a helper column and filter for any value greater than 1. Delete the duplicate rows or keep only the earliest timestamp per user per stage, whichever matches your definition of “completed.”

You should now see a clean flat table with no duplicate user-stage pairs and three reliable columns.

Step 3: Build the Conversion Summary Table

Create a second tab called funnel_summary. This is where the analysis lives. Set up a table with four columns: Stage, Stage Number, Users Reached, Conversion Rate from Previous.

In the Users Reached column, use COUNTIF against your raw_data tab to count distinct users at each stage. For stage 1 (Visited Landing Page) in cell C2:

=COUNTIF(raw_data!$B:$B,"Visited Landing Page")

Repeat for each stage, changing the stage name string to match your data. Do not use a generic COUNTA here. you want stage-specific counts, not row counts.

For Conversion Rate from Previous in D3 onward:

=IFERROR(C3/C2,0)

Format that column as percentage with one decimal place. Row D2 (the first stage) gets 100% by definition.

You should now see a small table showing how many users reached each stage and the step-by-step drop-off rate.

Step 4: Add an Overall Funnel Conversion Column

Add a fifth column: Conversion Rate from Top. This tells you the overall yield of each stage relative to the very first stage, not just compared to the step before it.

In E2:

=1

In E3 downward:

=IFERROR(C3/$C$2,0)

This is the number most stakeholders actually care about. a step-by-step rate of 70% sounds decent. an overall rate of 12% from top to bottom tells a very different story.

Lock the denominator with the $C$2 absolute reference or the formula breaks when you copy it down.

You should now see two conversion columns. one showing stage-to-stage rate, one showing cumulative yield from the top of funnel.

Step 5: Write Drop-Off Formulas and Flag Weak Stages

Add two more columns: Users Lost and Drop-Off Flag.

Users Lost is straightforward:

=C2-C3

Copy down for each stage pair.

For Drop-Off Flag, you want to automatically highlight any stage where the step-by-step conversion rate falls below your threshold. If your baseline expectation is 60% conversion between stages, use:

=IF(D3<0.6,"REVIEW","OK")

Adjust 0.6 to match your business context. an e-commerce checkout funnel and a B2B demo-request funnel have wildly different benchmarks, so set this number based on your historical average, not a generic best practice.

You should now see a column that tells you at a glance which steps need attention without manually scanning numbers.

Step 6: Apply Conditional Formatting for Visual Scanning

Select the Conversion Rate from Previous column. In Google Sheets go to Format > Conditional Formatting. Set a color scale: red for low values, green for high. Use the min as 0% and max as 100%.

Do the same for the Drop-Off Flag column: red fill for “REVIEW,” green fill for “OK.”

This takes two minutes but saves everyone on the team from re-reading raw numbers each time they open the file. a color gradient communicates magnitude instantly.

Also bold your header row and freeze it (View > Freeze > 1 row). Stakeholders scroll. Frozen headers mean the columns never lose their labels.

You should now see a table that communicates the story visually, not just numerically.

Step 7: Build a Date-Range Filter

Create a third tab called controls. Put two cells labeled Start Date and End Date. These are the only inputs your team will touch.

Back in raw_data, add a helper column In Range using:

=AND(C2>='controls'!$B$1,C2<='controls'!$B$2)

Where column C is your timestamp. Format column C as a date first or the comparison will fail.

Now update your COUNTIF formulas in funnel_summary to use COUNTIFS instead, adding the In Range column as a second condition:

=COUNTIFS(raw_data!$B:$B,"Visited Landing Page",raw_data!$D:$D,TRUE)

Now changing the two dates in controls refreshes the entire funnel summary automatically. this is the feature that makes the spreadsheet actually usable week over week, not just once.

You should now see the funnel numbers update when you change the date range in the controls tab.

Step 8: Create the Funnel Bar Chart

Select the Stage and Users Reached columns in funnel_summary. Insert a bar chart. In Google Sheets: Insert > Chart > Bar chart. In Excel: Insert > Bar or Column Chart > Clustered Bar.

Sort the bars in descending order so the widest bar (top of funnel) sits at the top. this gives the classic funnel shape without using a specialized chart type.

Add data labels showing the percentage values from your Conversion Rate from Top column. Most stakeholders read the chart, not the table. the labels do the translation for them.

Remove gridlines and the legend. neither adds information here. reduce chart ink to the minimum needed.

You should now see a clean bar chart that visually represents where volume shrinks across your funnel. this is your screenshot-worthy output for deck slides and Slack updates.

Step 9: Add a Segment Breakdown Row

At the bottom of funnel_summary, add a section for one key segment. common choices: traffic source, device type, pricing plan, or country. pick the one your team argues about most.

Add a Segment input cell in your controls tab. Then write a COUNTIFS that adds a third condition pulling from whichever segment column exists in your raw data:

=COUNTIFS(raw_data!$B:$B,"Visited Landing Page",raw_data!$D:$D,TRUE,raw_data!$E:$E,controls!$B$3)

This lets you compare your overall funnel against a specific segment side by side, which is where most actionable insights live. you are not looking for where the whole funnel is weak. you are looking for which segment behaves differently from the average.

You should now see a second mini-funnel table below the main one that refreshes when you type a segment value into the controls tab.

Common Mistakes To Avoid

  • Counting rows instead of distinct users. if a user hits the same stage twice your top-of-funnel number is inflated and every downstream conversion rate is wrong. always deduplicate on user_id plus stage before counting.
  • Not locking absolute references. forgetting the $ in formulas like $C$2 means your conversion rates silently break when you insert rows. check every formula after adding new stages.
  • Mixing session-level and user-level data. if your export includes one row per session rather than per user, your funnel measures sessions, not people. that is a different metric. know which one you exported.
  • Setting a single date range and never changing it. the date filter exists to let you compare periods. run this week against last week, or this month against the same month last year, at minimum before drawing conclusions.
  • Using generic stage names in formulas. if your raw data says “visited_landing_page” and your COUNTIF searches for “Visited Landing Page” the result is zero. exact string matching is case-sensitive in some environments. use a lookup table or standardize your stage names during the cleaning step.
  • Sharing the file without protecting the controls tab. team members will overwrite formulas in the main table by accident. protect all tabs except controls using Tools > Protect Sheet before sharing.

When To Level Up

The spreadsheet approach works well up to around 50,000 rows and three to four segments. past that, Google Sheets becomes slow and Excel pivot refreshes take long enough to break focus.

You also hit a ceiling on cohort analysis. comparing users who signed up in week one versus week four requires either complex COUNTIFS arrays that are hard to audit or a proper analytics tool.

The other signal to watch for is frequency. if you are running this analysis more than twice a week, the time cost of maintaining the file probably exceeds the cost of a starter plan on a dedicated funnel tool.

When you hit any of those limits, the data analysis tools section covers purpose-built options across different budget ranges. the Mixpanel vs Amplitude comparison and the funnel analysis tools compared posts are good starting points for scoping what to move to next.

Frequently Asked Questions

Do I need to know SQL to build this?
No. this entire build uses COUNTIFS, IFERROR, and conditional formatting. if you can write a basic IF formula, you have enough skills to follow each step. SQL becomes useful only when your data lives in a warehouse and the CSV export path stops being practical.

Can I use this with data from Google Analytics 4?
Yes, but GA4’s standard exports do not include user-level event data in a format that maps cleanly to funnel stages. you will need to either use the GA4 exploration funnel report directly, or pull raw event data via BigQuery if you have that integration enabled.

How often should I refresh the data?
For most growth teams, weekly is enough for decision-making. daily refreshes are useful during active experiments or post-launch monitoring. the controls tab date filter means you can run any time period without rebuilding the file.

What if my funnel is not strictly linear?
Non-linear funnels, where users can skip stages or re-enter, are harder to model in a spreadsheet. the safest approach is to define each stage as “ever reached” rather than “reached in sequence.” that means a user who jumps from stage 1 to stage 4 still counts at stages 2 and 3 if you set your COUNTIFS to check for any prior stage completion rather than strict ordering.

How do I present this to a non-technical stakeholder?
Lead with the bar chart and the two or three stages with the worst conversion rates. hide the raw data and formulas tabs before sharing. most stakeholders need the insight, not the method. a one-slide summary showing overall funnel yield and the top drop-off stage usually gets you to a decision faster than walking through the full spreadsheet.

Bottom Line

Building a funnel analysis spreadsheet takes about two hours if your data is already exported and reasonably clean. you define your stages on paper first, import and deduplicate your raw data, then build a summary table with COUNTIFS formulas that feed a date-controlled conversion view and a bar chart. add conditional formatting, lock your references, and protect the formula tabs before sharing. the result is a reusable tool your whole team can update by changing two date inputs and one segment value. it is not a permanent solution for high-volume analysis, but it is the fastest way to get from raw data to a conversation about where your funnel actually breaks. for what to do once you outgrow it, browse the full data analysis tools guide or check the cohort analysis spreadsheet walkthrough as a natural next step.