Part of the free Module 9: Advanced Charts and Infographics · Lesson 8 of 8 · Full Excel course
Updated on 5 September 2026 · Works in Excel 365, 2021, 2019 and 2016. The built-in Funnel chart type needs Excel 2019 or 365; the stacked bar method works in every version.
A funnel chart in Excel shows how a quantity shrinks as it passes through the stages of a process, such as leads turning into won deals. Each stage is a horizontal bar, centred under the one above it, so the shape narrows towards the bottom. Excel 2019 and 365 include a ready-made Funnel chart type; every other version can build the same picture from a stacked bar chart.
When to use a funnel chart
Use a funnel when the stages are sequential and every item at one stage came from the stage before it: a sales pipeline, a recruitment process or a checkout journey. The value of the chart is the drop between stages, not the totals, so always show the conversion rate next to each bar.
Do not use a funnel for categories that are simply different sizes, such as sales by region. Those are not stages, and a sorted bar chart reads better.
Sample data
Type this table into a blank sheet: stage names in column A, counts in column B, headings in row 1 and data in rows 2 to 6. Both methods below use it.
| Stage (column A) | Count (column B) |
|---|---|
| Leads | 1200 |
| Qualified | 800 |
| Proposal | 450 |
| Negotiation | 220 |
| Won | 120 |
Method 1: the built-in Funnel chart (Excel 365 and 2019)
This is the fastest route and needs no helper columns.
- Select A1:B6.
- Go to Insert > Charts > Insert Waterfall, Funnel, Stock, Surface or Radar Chart (the small icon that looks like a waterfall).
- Choose Funnel. Excel draws centred bars with the first stage at the top.
- Click the chart, then Chart Elements (plus sign) > Data Labels to show the counts.
- Use Chart Design > Change Colours or click one bar twice to recolour a single stage.
The built-in funnel is deliberately simple: no gap width control, no second series and no percentage option, so conversion rates still have to come from a cell range.
Method 2: a stacked bar funnel for any version, including 2016
Excel 2016 has no Funnel chart type, so build one from a stacked bar and a hidden spacer series. The spacer pushes each bar to the right by half of the missing width, which centres it.
Helper columns and exact formulas
| Cell | Formula | Purpose |
|---|---|---|
| C1 | Spacer (typed heading) | Invisible series that centres the bars |
| C2, filled to C6 | =(MAX($B$2:$B$6)-B2)/2 |
Half the gap between this stage and the widest stage |
| D3, filled to D6 | =B3/B2 |
Conversion rate from the previous stage |
| E2, filled to E6 | =B2/$B$2 |
Percent of the top stage |
Leave D2 empty and format columns D and E as percentages with one decimal place.
Build the chart
- Select A1:C6 (stage, count and spacer).
- Go to Insert > Charts > Insert Column or Bar Chart > Stacked Bar.
- Right click the chart, choose Select Data, click Spacer in the legend entries list and press Move Up so the spacer is plotted first. Click OK.
- Click the spacer bars once to select the series, then press Ctrl+1 and set Fill > No fill and Border > No line. The bars now float in the middle.
- Click the vertical axis, press Ctrl+1 and tick Axis Options > Categories in reverse order so Leads sits at the top.
- Select the Count series, press Ctrl+1 and set Series Options > Gap Width to a value between 0% and 10%. The bars now touch, which gives the solid funnel outline.
- Add labels: with the Count series selected, use Chart Elements > Data Labels > Inside End.
- Right click a label, choose Format Data Labels, tick Value From Cells and point the range at D2:D6, then tick Value as well. Each bar now shows its count and its conversion rate. Value From Cells needs Excel 2013 or later.
- Delete the legend, the gridlines and the horizontal axis, then set a chart title such as Sales pipeline, Q3.
Because the spacer is a formula, the funnel re-centres itself the moment a count changes.
Built-in funnel compared with the stacked bar method
| Point | Built-in Funnel chart | Stacked bar funnel |
|---|---|---|
| Excel versions | 2019, 2021 and 365 only | All versions, including 2016 and 2013 |
| Build time | Two clicks | Roughly five minutes |
| Helper columns | None | One spacer column, plus percentage columns |
| Colour per stage | Yes, click a bar twice | Yes, click a bar twice |
| Gap width control | No | Yes |
| Conversion labels | Manual or from cells | Value From Cells, updates automatically |
Worked example
Using the sample table, add the helper columns and read the result:
| Stage | Count | Spacer | Conversion | Percent of top |
|---|---|---|---|---|
| Leads | 1200 | 0 | 100.0% | |
| Qualified | 800 | 200 | 66.7% | 66.7% |
| Proposal | 450 | 375 | 56.3% | 37.5% |
| Negotiation | 220 | 490 | 48.9% | 18.3% |
| Won | 120 | 540 | 54.5% | 10.0% |
The narrowest step is Proposal to Negotiation, where less than half the proposals move forward. That is what the chart is for: the pipeline converts 10% end to end, but the pricing conversation is where most deals are lost.
Tips and common mistakes
- Sort the stages, do not sort the values. Stages must stay in process order. Sorting by count hides the real drop.
- Keep the spacer first. If the funnel is flush against the axis, the spacer series is still second in Select Data; move it up.
- Use No fill, not white fill. A white spacer looks fine on a white sheet and wrong on a coloured dashboard.
- Show conversion, not just counts. Two funnels can look identical and have very different stage-to-stage rates.
- Do not add a 3-D effect. Depth distorts the widths, and comparing widths is the whole point.
- Limit the funnel to about seven stages. Beyond that the bottom bars become unreadable slivers.
- Feed it from a pivot table if you want slicers; the helper formulas still work against the pivot output.
Errors and how to fix them
| Problem | Cause | Fix |
|---|---|---|
| No Funnel option under Insert | Excel 2016 or older | Use the stacked bar method on this page |
| Bars start at the left edge | Spacer series is plotted second | Select Data > Spacer > Move Up |
| Last stage is at the top | Category axis not reversed | Tick Categories in reverse order |
| Gaps between the bars | Gap width too high | Set Gap Width to 0% to 10% |
| Spacer values shown as labels | Labels added to the whole chart | Select only the Count series before adding labels |
| Conversion labels do not refresh | Percentages typed as text | Use the =B3/B2 formula and Value From Cells |
| First conversion cell shows a divide error | Formula filled up into row 2 | Leave D2 empty; start the formula in D3 |
Practice exercise
- Type the five-stage sample table and build the stacked bar funnel from start to finish without looking back at the steps.
- Change Proposal from 450 to 700 and confirm the funnel re-centres on its own.
- Add a sixth stage, Onboarded, with 95, and extend the three helper formulas to row 7.
- Colour the Won bar green and every other stage a neutral grey.
- On Excel 365, build the same data as a built-in Funnel chart and note the formatting options you lose.
Key takeaways
- A funnel compares sequential stages, where each stage is a subset of the one above.
- Excel 2019 and 365 have a native Funnel chart under the Waterfall group; Excel 2016 does not.
- The universal method is a stacked bar with a spacer column,
=(MAX($B$2:$B$6)-B2)/2, set to No fill. - Reverse the category axis and drop the gap width to 0% to 10% for the classic outline.
- Conversion rate,
=B3/B2, is the number readers want; add it with Value From Cells. - Keep to about seven stages and never sort a funnel by value.
Related lessons
- Advanced Charts and Infographics (module hub)
- Waterfall chart in Excel
- Bullet chart in Excel
- Progress circle chart
- Stacked bar chart basics
- Pivot tables, for building the stage counts before you chart them
- See also, with a downloadable workbook and a video: Sales funnel infographic chart in Excel and Dynamic sales funnel chart with slicers
- Microsoft Support: Create a funnel chart
Frequently asked questions
How do I make a funnel chart in Excel 2016?
Excel 2016 has no Funnel chart type. Add a spacer column with =(MAX($B$2:$B$6)-B2)/2, insert a stacked bar chart from the stage, count and spacer columns, move the spacer series to first position, set it to No fill, reverse the category axis and reduce the gap width to about 5%.
Where is the funnel chart in Excel?
In Excel 2019, 2021 and 365 it lives on the Insert tab, in the Charts group, under the Waterfall, Funnel, Stock, Surface or Radar icon. Funnel is the second option in that gallery. If the icon does not offer it, your version predates the chart type.
Can a funnel chart show conversion rates?
Not on its own. Add a helper column with =B3/B2, format it as a percentage, then use Format Data Labels > Value From Cells and point at that column. The labels then show the count and the stage-to-stage conversion rate, and both update with the data.
Why are my funnel bars not centred?
Either the spacer series is plotted after the count series, or the spacer formula is missing the division by 2. Open Select Data, move Spacer to the top of the series list, then check that every spacer cell uses absolute references on the MAX range.
What is the difference between a funnel chart and a pyramid chart?
A funnel plots real values, so bar widths are proportional to the counts at each stage. A pyramid is usually a decorative shape with equal steps and no data behind the widths.
Want the finished version? Ready-made Excel chart and infographic templates are available at NextGenTemplates.com.