How to Make a Funnel Chart in Excel for a Sales Pipeline

A funnel chart in Excel shows how many deals, leads or applicants make it through each stage of a process, from the wide top to the narrow bottom. It is the standard picture of a sales pipeline: many leads, fewer proposals, a handful of wins. This guide shows how to count each stage from a deal list with COUNTIF, add conversion rates, insert the chart and build a funnel in older versions of Excel.

What does a funnel chart show?#

A funnel chart is a set of centred horizontal bars, one per stage, sorted from largest to smallest. Each bar's width is the count at that stage, so the shape shows where you lose the most. A sharp narrowing between Proposal and Negotiation, for example, tells a sales team where to look.

Use a funnel when:

• The stages happen in order, and each item can only move forward.
• Each stage holds fewer items than the one before.
• You want to show drop-off at a glance, for a sales pipeline, a hiring process or a sign-up flow.

If the categories have no natural order, such as sales by region, use a sorted bar chart instead. The guide to bar charts in Excel covers that, and the chart basics guide helps you pick a type.

Step 1: Count the deals at each stage with COUNTIF#

A funnel counts how many deals reached at least each stage, not how many sit there today. A deal that is now at Won also passed through Lead, Qualified, Proposal and Negotiation. The easiest way to count that is to give each stage a number:

If your stage names are in G2:G6 (Lead, Qualified, Proposal, Negotiation, Won), column C can be a formula instead of typed numbers: =MATCH(B2,$G$2:$G$6,0).

Now build the summary. Put the stage names in F2:F6 and their numbers 1 to 5 in E2:E6. In H2, count every deal at that stage number or higher:

=COUNTIF($C$2:$C$9,">="&E2)

Fill it down to H6 (Windows: Ctrl+D; Mac: ⌘D). The results are 8, 6, 4, 2 and 1. The ">="&E2 part joins the operator to the number in E2, and the $ signs keep the deal range fixed as you fill. The COUNTIF function guide explains this criteria pattern.

To make a funnel for one salesperson, add a second condition with COUNTIFS:

=COUNTIFS($C$2:$C$9,">="&E2,$D$2:$D$9,"Ana")

For Proposal (E4 = 3), Ana's count is 3: D-101, D-103 and D-105. See the COUNTIFS function guide and the article on COUNTIFS and SUMIFS with several conditions for more.

Lost deals need care. If a deal is lost at Proposal, keep a "furthest stage reached" number of 3 for it, so it still counts in the top three bars.

Step 2: Add conversion rates#

The conversion rate is the share of deals that moved from one stage to the next. With the counts in H2:H6, type this in I3 and fill down:

=H3/H2

Format the column as a percentage (Windows: Ctrl+Shift+%; Mac: Control+Shift+%). Here is the summary:

The last column divides each count by the first stage with a locked reference: =H3/$H$2. That gives the overall conversion rate: 1 won deal out of 8 leads is 12.5%. The guide to percentage of total explains why the $ matters here.

Step 3: Insert the funnel chart#

Excel's built-in funnel chart needs Excel 2019 or later, or Microsoft 365, on Windows or Mac. To insert it:

1. Select the stage names and their counts, for example F1:F6 and H1:H6 (hold Ctrl on Windows or ⌘ on a Mac to select columns that aren't next to each other).
2. On the Insert tab, open the waterfall chart button (its menu also holds Funnel, Stock, Surface and Radar charts).
3. Choose Funnel.

Excel draws one centred bar per row, in the order of your rows. That is why the largest stage must be at the top. If Lead ends up at the bottom, sort the summary or reorder the rows; the funnel won't sort itself.

Step 4: Label and format the funnel#

A good funnel shows the count and the drop-off together. The built-in chart shows the value on each bar, so add the percentage to the stage name instead. In a spare column, type:

=F3&" ("&TEXT(I3,"0%")&")"

This gives Qualified (75%). Use these labels as the chart's categories through Chart Design > Select Data. Then:

• Give the chart a clear title, such as "Q3 pipeline: 8 leads to 1 win".
• Use one colour. Different colours per stage suggest different kinds of thing.
• Delete the gridlines and the legend; a funnel needs neither.

How to make a funnel chart in older Excel#

In Excel 2016 or earlier, build a funnel from a stacked bar chart with an invisible spacer:

1. Next to the counts, add a Spacer column: =(MAX($H$2:$H$6)-H2)/2. For Qualified that is (8 − 6) / 2 = 1.
2. Select the stage names, the spacer and the counts, with the spacer column first, and choose Insert > Bar > Stacked Bar.
3. Click the spacer bars and set Fill to No fill. The real bars now sit in the centre.
4. Right-click the vertical axis, choose Format Axis and tick Categories in reverse order, so Lead is at the top.

The result looks like a funnel and works in any version. Updating it is the same as the built-in chart: change the data and the bars follow.

Common funnel chart problems#

These are the issues that come up most:

1. The funnel is upside down. The rows are in the wrong order. Put the first stage at the top.
2. A lower stage is bigger than the one above. You counted where deals are now instead of how far they got. Use ">=" with a stage number.
3. Percentages over 100%. The divisions point at the wrong row. Stage-to-stage is the current row divided by the row above.
4. The chart doesn't include a new stage. The source range stops too early. Extend it in Select Data, or keep the summary in a table.
5. The Funnel option is missing. Your Excel version is older than 2019. Use the stacked bar method.

A funnel is usually one page of a bigger tracker. The guide to building a sales pipeline tracker in Excel shows the deal list, stage drop-downs and weighted values that feed it.

Practise it#

The chart is quick; the counting behind it is what tests check. Practise COUNTIFS and SUMIFS in a live spreadsheet: you type each formula and it checks it instantly. Then practise percentages and % change to get the conversion rates right, and try the data analyst Excel test when you're ready.