Excel Funnel Chart (Table of Contents)
Excel Funnel Chart
Ideally, the funnel chart indicates a process, which starts at 100% and ends with a lower percentage where it can be seen that in which stages the fall out happens and at what rate.
Various places where funnel chart can be used are:
- Sales Process: In this process, the funnel chart starts with the sales leads on top, then down to the qualified leads, and then hot leads and at last closed leads to the bottom. Any business loses some number of potential deals at each step in the sales process. This is represented by the narrowing sections as we move from the top section, which is the widest to the bottom section, which is the narrowest section.
- Website Visitor Trends: We could also use funnel chart to display website visitor trends reflecting numbers of visitors who hits to the homepage at the extreme top with the widest area and the other areas will be smaller, like the downloads or the people adding product in the cart.
- Order Fulfilment Funnel Chart: This chart could reflect initiated orders on top, canceled orders, returned orders, and at the extreme bottom orders delivered to the satisfied customers.
How to Create a Funnel Chart in Excel?
There are various ways to create a funnel chart in excel. One of them is using REPT function.
We can use the REPT function, which stands for REPEAT, the syntax of which is as follows:
It takes two arguments.
- Text: This is the text, which we want to write repeatedly.
- Number_times: For this argument, we need to specify the number of times for which we want to repeat the text.
Let us learn to create Funnel Chart in Excel using some examples.
Funnel Chart – Example #1
Use the REPT function to create a Funnel Chart
Suppose, we have following data for order fulfillment process for an organization doing E-Business.
4.9 (1,353 ratings)
We will create a Funnel Chart to display this data.
- We need to use the REPT function in the following way. We can see that we have divided the value of B3 by 5 so that the size of the text does not exceed too much.
- After entering the function, we find the output as follows.
- As the output is not formatted in the way, we want. We need to change the font of the cell as ‘Playbill’ using the ‘Font’ text box in the ‘Font’ Group in ‘Home’ tab.
- We want the color of the line to be green and alignment be center. We will make necessary changes using the command available in the ‘Font’ and ‘Alignment’ group in the ‘Home’ tab.
- We just need to copy and paste the same formula and formatting on E3: E7.
Funnel Chart is ready. Whenever we will change the value in the table. This chart will reflect dynamically.
Funnel Chart – Example #2
Suppose, we have the same data as above and we want to create a funnel chart that is more attractive.
We will create the funnel chart using 3-D 100% Stacked Column Chart. Steps are:
- Select the data range A2: B7.
- Click on the arrow located at the bottom right corner of the ‘Charts’ group in the ‘Insert’ tab.
- Click on ‘All Chart’, then choose ‘Column’ from the list on the left, click on ‘3-D 100% Stacked Column’ chart, choose the second chart and click on OK.
- The chart will look like as follows,
- When the chart is created, two new contextual tabs for chart tools (Design and Format) are opened. In Format tab, we need to choose ‘Legend’ from the list on the left side of ‘Format’ tab in ‘Current Selection’ group and press delete button on the keyboard to delete the legend.
- In the same way, we will delete ‘Vertical (Value) Axis Major Gridlines’, ‘Chart Title’, ‘Horizontal (Category) Axis’.
- Choose ‘Vertical (Value) Axis’ from the list so that the axis got selected in the chart. Choose ‘Format Axis’ from the contextual tab opened by right-clicking on the selection of the axis.
- Tick checkbox for ‘Values in reverse order’.
- Delete the ‘Vertical (Value) Axis’ by selecting the same and pressing the delete
- Select any of the series from the list.
- Right click on the series to get a contextual menu and choose ‘Format Data Series’ from the menu.
- Choose ‘Full Pyramid’ from the list.
- As we want to have some space in between the series, we can add rows for the same in the data as follows:
- Now we need to change the data source for a chart using the ‘Select Data’ command available in the ‘Data’ group in the ‘Design’ tab.
- We need to delete the selection and reselect the data by using the range selector as follows. And click on the ‘OK’ button.
- As we can see that, space, which we have specified in the data, is reflecting in the chart in different colors. However, we want the space to be transparent.
- To make the space transparent, we need to select the series by clicking on them and choosing ‘No Fill’.
We will do the same for the other 3 series.
- We will add data labels and series name for all series by right-clicking on the series and choosing ‘Add Data Labels’.
- We can also add series names by formatting data labels.
We can add Chart Title using ‘Add Chart Element’ command available in the ‘Chart Layouts’ group in the ‘Design’ tab.
Now our chart is ready.
Funnel Chart – Example #3
Suppose, we have the same data as above.
The above two methods can be used in 2007, 2010, 2016 version of the MS Excel but the method discussed in this example is only available in Excel 2016 i.e. Funnel Chart.
We can find out the command in the ‘Charts’ group in the ‘Insert’ tab.
Now to create the chart. The steps would be:
- Select Data A2: C7.
- Click on the ‘Funnel’ command available in the ‘Charts’ group in the ‘Insert’ tab.
- We will define the Chart Title and change the layout using command available in the ‘Chart Layouts’ group in ‘Design’
Now our chart is ready.
You can download this Funnel Chart Excel Template from here – Funnel Chart Excel Template
Things to remember about Funnel Chart in Excel
While taking the help of funnel chart to display the data graphically, we need to make sure that the process involves steps in which every previous step having a larger number than the next step so that the shape of the chart looks like a funnel.
Recommended Articles
This has been a guide to Funnel Chart in Excel. Here we discuss how to Create Funnel Chart in Excel with some examples and downloadable excel template. You may learn more about excel from the following articles –
Leave a Reply