How to Create a Funnel Chart In Excel

In the world of data visualization, funnel charts have become a go-to tool for illustrating stages in a process, especially when tracking sales or conversions. Learning how to create a funnel chart in Excel can give you a powerful way to visualize data that shows progression and drop-off through various stages. If you're preparing a funnel report in Excel for a business presentation or simply looking to enhance your data analysis skills, this guide will walk you through the steps to create a compelling funnel chart Excel.

With Excel's versatile tools, crafting a funnel graph Excel becomes straightforward, enabling you to present your data clearly and effectively. Let's get into the process of creating an Excel funnel chart and discover how it can transform your data presentation!

What is a Funnel Chart in Excel

A funnel chart in Excel is a type of chart that shows how data progresses through different stages in a process. Imagine a funnel shape: it's wide at the top and narrow at the bottom. Each section of the funnel represents a stage, and the size of each section shows how much data is at that stage. For example, in a sales process, the top of the funnel might show the total number of potential customers, and each stage below shows how many customers move to the next step, like making a contact or a purchase.

How To Create a Sales Funnel Chart in Excel

Step 1: Create Dataset

In this step, we will create our dataset for preparing a random sales funnel chart. For this, we will be adding the following columns Sales Stage, Spacer, and Number Of Customer. The spacer will center each of the horizontal bars of the chart depending on the largest data value.

Note: Spacer will simply align the data bars in centre alignment. For this we need to find the maximum data value in the dataset and we will subtract the current data value then we will divide it by 2.

Above, we can see the spacer formula. (You will need to change the column and rows according to your own data). After that, we need to put the same formula for all the rows for which we will hold and drag it below to all rows.

Step 2: Create Stacked Bar Chart

In this step, we will select our data and insert a stacked bar chart. For this , Select Data > Insert > Charts > Stacked Bar Chart.

Step 3: Once we insert the stacked bar it will look something like this.

Step 4: Reverse the Chart Categories

Once we will insert our chart, it shows the categories in reverse order from the one which is present in our dataset. So, we will reverse the order once. In order to reverse the chart categories, we need to select the vertical axis (the one with category names).

Step 5: Perform Right Click and Choose Format Axis

After we click on the vertical axis we need to do a right-click and choose Format Axis.

Step 6: Click on the Check Box

Once we click on the Format axis option, it will open a right-side pane for axis formatting. We need to click on the checkbox for "Categories in reverse order".

Step 7: Stack Bar Reversed

This will reverse the stacked bar graph depending on categories order and it will match to same as the one present in dataset.

Step 8: Reduce Bars Gap Width To Zero

In this step, we will reduce the gap between each bar to zero. For this, we need to select any bar and do a right-click and choose Format Data Series.

Step 9: Once we click on Format Data Series, the excel will open a side pane where we need to reduce the Gap Width to 0%.

Step 10: Gap Reduced

The above option will reduce the gap between each bar to 0 and our graph will look something like this.

Step 11: Change The Spacer Bar Color

In this step, we will change the spacer(blue) color to the chart background color. For this, Select Region (blue color region) > Right-Click > Format Data Series.

Step 12: Remove Background

It will open a side pane where we are required to choose No Fill. It will remove the background color.

Step 13: Review Chart

After all the above steps, our chart now looks something like this.

How to Format Sales Funnel Chart in Excel

Step 1: Formatting Chart

In this step, we will format the chart to enhance its representational view. For this, we will do the following operations (You can update your chart depending upon your own needs).

• Add title to the chart.
• Remove the vertical grid lines from the chart area.

Step 2: Remove background and Add data labels

Remove the legends.

Add data labels to the funnel bars and center them.

Step 3: Remove the horizontal value axis.

Select the horizontal value axis and deleted

Step 4: Data Labels

Now, we are required to remove the data labels for the spacer region and remove the data labels by clicking plus icon beside the chart.

Step 5: Change Borders

Once we are done with all the above steps, we will finally Select Chart Region > Right-Click > Format Data Series and add borders, and change color accordingly. 

Output

Conclusion

Learning the creation of an Excel funnel chart can significantly enhance your data presentation skills. By following the steps outlined in this article, you can easily create a funnel graph Excel that effectively visualizes data flow through different stages. If you're tracking sales processes, customer journeys, or any other funnel-based metrics, an Excel funnel chart provides a clear and insightful way to display your data.

Creating a funnel report in Excel not only helps in understanding the drop-off points and bottlenecks but also aids in making data-driven decisions. As you continue to explore the capabilities of funnel charts Excel, you'll find that this powerful tool can transform how you present and interpret your data, making it more accessible and impactful for your audience.