funnel plot in excel

Mastering Funnel Plots in Excel: A Step-by-Step Guide

By Steven Lee
AD

Funnel plots are an invaluable tool in Excel, particularly for visualizing distribution and comparing means. They're great for presenting data in a way that's both informative and visually appealing. In this comprehensive guide, we'll dive into creating funnel plots in Excel, exploring their uses, understanding types, and even going through an example for practical application.

To begin, let's clarify what a funnel plot is. In essence, it's a type of visual display that shows a population's spread of data around a centre value, often shown as a 'funnel' due to its characteristic shape. The top of the funnel represents the spreads at smaller numbers, while the bottom shows larger numbers. This makes it an excellent tool for comparing groups or over time.

Understanding Funnel Plots in Excel

A key feature of Excel's funnel plot is its use of error bars, which enable you to display variation or uncertainty. Excel lets you create funnel plots using its built-in XY scatter plot function, with a little customization. Let's explore two main types of funnel plots in Excel.

AD
Funnel Chart In Excel
Funnel Chart In Excel
an excel chart with the sales and leads data displayed on it, along with other graphs
an excel chart with the sales and leads data displayed on it, along with other graphs
How to Create a Pipeline Report in Excel - 4 Steps - ExcelDemy
How to Create a Pipeline Report in Excel - 4 Steps - ExcelDemy

Funnel Plot for Simple Means

For basic means, you can create a funnel plot to display a single value (usually moyenne) with a standard deviation. This helps visualize the spread of data from the mean. Excel's error bars feature comes in handy here, allowing you to set the type to 'Percentage' or 'Standard Deviation'.

For instance, if your data is in cell range A1:A100, you would first calculate the mean (AVERAGE) and standard deviation (STDEV). Then, using an XY scatter plot, you can set Y values as 'Sheet1!$A$1:$A$100', X values as a constant (like '1x2x..

Funnel Chart and Graph Templates | Moqups
Funnel Chart and Graph Templates | Moqups
Sales Funnel Infographic Chart Created in Excel
Sales Funnel Infographic Chart Created in Excel
The Ultimate Guide to Create a Funnel Chart in Excel
The Ultimate Guide to Create a Funnel Chart in Excel

Funnel Plot for Comparing Groups

In this case, you'll instead use a funnel plot to visualize the difference between group means. This could be handy in comparing results between departments or regions, for example. To achieve this, you would calculate the means and standard deviations for each group and then plot from the smallest group up.

For example, let's assume data for two departments (Sales and HR) is in cells A1:C200 (Sales) and D1:F200 (HR), means in G1, I1, and standard deviations in H1, J1. Proceed with XY scatter as above. Ensure to adjust your error bar range to reflect the group data.

AD

Creating a Funnel Plot - Step-by-step

Now that we've understood the theory, let's dive into the practical aspect. Here's a step-by-step guide on creating a funnel plot in Excel:

Excel Funnel Chart - ExcelDemy
Excel Funnel Chart - ExcelDemy
25 Excel Tips, Formulas & Dashboard Ideas Every Analyst Should Know
25 Excel Tips, Formulas & Dashboard Ideas Every Analyst Should Know
Pivot Chart in Excel
Pivot Chart in Excel
Data Visualization & AI Analytics Blog | Plotly
Data Visualization & AI Analytics Blog | Plotly
excel guide
excel guide
Excel Roadmap for Data Analytics 2026 | Complete Beginner to Advanced Guide
Excel Roadmap for Data Analytics 2026 | Complete Beginner to Advanced Guide

Data Organization

Ensure your data is organized in a way that makes sense. For instance, in a single column or with group-specific columns. This will dictate how you create your plot.

Next, calculate your means and standard deviations. If comparing groups, remember to do this separately for each group.

AD

Creating the Plot

With data ready, create your XY scatter plot. Right-click on your plotted points to add error bars, setting the 'Error Amount' to reflect your standard deviation.

the most useful excel chart info sheet
the most useful excel chart info sheet

Customize your plot using fill colors, line widths, and labels. Want different colored funnels? You can as well separate them into different series.

AD