Funnel Chart in Excel

In this post we will learn how to create funnel chart in Excel.

What is funnel chart? 

Funnel charts are a type of chart often used to represent stages in the sales process and shows the amount of potential revenue/value for each stage. A funnel chart displays values as progressively decreasing proportions amounting to 100 percent in total. Ideally the funnel chart shows a process that starts at 100% and ends with a lower percentage

Now lets see how to create funnel chart in Excel

Steps to create Funnel chart in Excel: The funnel chart in excel can be created in simple steps using a stacked bar chart.

Funnel chart in Excel Step 1: Process the Data & Create a Stacked Bar Chart

We will be using the data which depicts various stages in e-commerce and the number of customers for each stage. The data is shown below.

Stage Customer count
Awareness(Stage-1) 4000
Consideration(Stage-2) 3200
Preference/Intent(Stage-3) 2000
Purchase(Stage-4) 500
Repurchase(Stage-5) 50

Insert a column between stage and customer count and apply a formula, which finds maximum number in the “customer count” in the data series and subtract the number in the current data row and then divides by 2. The Formula is shown below

funnel chart in excel 1

So the final data after applying the formula will look like

funnel chart in excel 2

Once we have these two columns of data, then select the data and create a Stacked Bar Chart.

funnel chart in excel 2a

Funnel chart in Excel Step 2 :Reverse the Stacked Bar chart

Usually the chart created will be the opposite order on the way the data is we have to reverse the chart

  • Now, right click on the vertical axis of the chart and select format axis.
  • Under the Axis Options click the checkbox that says “Categories in reverse order”

funnel chart in excel 3

The categories will now be listed in the same order of the data.

Funnel chart in Excel Step 3: Remove the gap between bars

Next we have to reduce the gap width to 0% so that there won’t be any gap between the bars which will make the bars to look funnel like

  • Right click on any of the bar and select “format data series” option
  • Under the series option change the gap width to 0%

funnel chart in excel 4

When we reduce the gap width to 0% the resultant graph will be

funnel chart in excel 4a

Funnel chart in Excel Step 4: Remove the Blue set of stacked bars

Now we have to remove the blue set of stacked bars to create a complete funnel chart. We will make it invisible to the reader, and only the actual funnel data will appear.

  • Now, right click on any of the blue bars choose “format data series” option.
  • Under the bucket icon change the “FILL” to “No fill” and “BORDER” to “No line” as shown below

funnel chart in excel 5

So the resultant chart will be a funnel chart!!

funnel chart in excel 6

Funnel chart in Excel Step 5: clean up the chart for presentation

Now remove all the legends, axis notations, add title, add data labels, change color etc and make it appealing for presentation. So the final chart will be looking like…

funnel chart in excel 7

previous small funnel chart in excel                                                                                                           next small funnel chart in excel

DataScience Made Simple