How to Create a Sales Funnel Chart In Excel?
A Funnel Chart is a type of chart which is used to represent how the data moves through a process. Usually, it is used to represent the different stages in the sales process and shows the amount of potential revenue for each stage. Funnel charts are widely used to represent the sales funnels, recruitment, and order fulfillment process. The funnel chart in sales (for example, In e-Commerce websites) gives the reader a better picture of how many customers start the search for a product and at the end put a purchase order. Given below is a random sales funnel chart for an e-commerce website.
The funnel chart is created with the help of a stacked bar chart. Below is the step-by-step implementation of funnel char for the random sales data.
Step By Step Implementation For A Funnel Chart
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.
Once we insert the stacked bar it will look something like this.
Step 3: 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).
After we click on the vertical axis we need to do a right-click and choose Format Axis.
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”.
This will reverse the stacked bar graph depending on categories order and it will match to same as the one present in dataset.
Step 3: 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.
Once we click on Format Data Series, the excel will open a side pane where we need to reduce the Gap Width to 0%.
The above option will reduce the gap between each bar to 0 and our graph will look something like this.
Step 4: 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.
It will open a side pane where we are required to choose No Fill. It will remove the background color.
After all the above steps, our chart now looks something like this.
Step 5: 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.
- Remove the legends.
- Add data labels to the funnel bars and center them.
- Remove the horizontal value axis. (Select the horizontal value axis and deleted)
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.
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
Contact Us