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.
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
So the final data after applying the formula will look like
Once we have these two columns of data, then select the data and create a Stacked Bar Chart.
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 represented.so 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”
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%
When we reduce the gap width to 0% the resultant graph will be
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
So the resultant chart will be a funnel chart!!
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…