Mohammad Shah Miran
Certainly, having a clear understanding of the sales pipeline funnel can help you to understand your potential customer’s behavior throughout the purchasing journey. Not to mention how you will benefit if you understand and create your own sales pipeline. So, in this article, we are going to discuss how to create sales pipeline funnel in Excel.
Download Practice Workbook
You can download and practice the dataset that we have used to prepare this article.
What Is Sales Pipeline Funnel?
From the perspective of a lead, the sales funnel considers the entire customer journey. The sales pipeline may vary in different ways so it might not always look the same way. For example, here we have created a sales pipeline funnel specifically for our today’s discussion.
As you can see from the image described above, the sales pipeline funnel can consist of five components such as identified, validated, qualified, proposal, and won.
Difference Between Sales Pipeline vs Sales Funnel
A sales pipeline depicts the metamorphosis of a consumer to become a customer whereas the sales funnel describes the number of prospects throughout the sales journey.
A sales pipeline examines all of the stages associated with sales, from customer acquisition to closing the deal. From this point of view, it could be seems staged but in terms of the sales funnel, it is all about the possibility that you should take into account to make a business deal.
3 Steps to Create a Sales Pipeline Funnel in Excel
Let’s assume we have a dataset, namely “Different Stages of Sales with Corresponding Values”. You can use any dataset suitable for you.
Here, we have used the Microsoft Excel 365 version; you may use any other version according to your convenience. Please leave a comment if any part of this article does not work in your version.
Step 01: Prepare the Dataset
To begin with the process, we need to make an adjustment to our dataset as provided earlier. Basically, we have to create an additional column named Helper Column so that there is enough space on each side of our pipeline funnel chart. Therefore, it will create an aesthetic look to the chart.
- First, write 0 in the C5 cell and then move on to the C6 cell. Write the following formula as provided below.
=(LARGE($D$4:$D$9,1)-D6)/2
⚡Formula Breakdown:
- To get the other value, drag the Fill Handle tool from C6 to C9.
Read More: How to Do Analysis Sales Pipeline in Excel (with Easy Steps)
Step 02: Generate Sales Pipeline Funnel Chart
Here we have come to the main part of our task. We are going to generate a sales pipeline funnel chart.
- Select the range of data starting from B5 to D9.
- Now move on to the Menu Bar. Click on Insert > Insert Column or Bar Chart > Stacked Bar.
- See the raw output of our given data below.
Read More: How to Build a Sample Sales Pipeline in Excel (with Easy Steps)
Step 03: Perform Post Processing of the Funnel
Now we need to make our chart tuned to look more like a funnel shape. Therefore, we will go through a detailed explanation conveying our priority for you so that you don’t feel obstacles if you don’t have any prior formatting experience.
- First, change the title of our chart, “Sales Pipeline Funnel” for instance.
- Select the Legend box.
- Do a right click on your mouse and select the Delete option to remove it.
- Now, select the Vertical Axis as shown below.
- Click on the right button of your mouse and select the Format Axis option.
- Apart from clicking your mouse, you can accomplish the same by pressing the CTRL+1 shortcut key on your keyboard.
- Thus a Format Axis pan will appear. Select Categories in reverse order from Axis Options.
- Now see the output as given below.
- Select the helper data in the chart.
- To remove our helper data, right-click on the mouse to select Format Data Series. As we said earlier, you can avoid this step by pressing the CTRL+1 shortcut key.
- From Fill & Line tab, make transparency to 100% to disappear the Helper columns data.
- Interesting to see, is not it?
- Now we need to fill the gap in between our horizontal bar, as it still does not look like a funnel chart.
- To do so, select any of the bars. After clicking the right button of your mouse, move to the Format Data Series option.
- From the Axis Options tab, make the Gap Width to 100%.
- Now see the output as given below.
- Further, you can change or modify the color according to your preference. We change our color from Orange to Blue here.
Read More: How to Create Pipeline Report in Excel (With Easy Steps)
Things to Remember
- It is worth mentioning that you might get confused while reducing the transparency of the Helper Data as you will find two Color and Transparency options. So before doing any kind of reduction make sure you choose the right one, at the top of the box under the Fill
Practice Section
We have provided a Practice section on the right side of the sheet so you can practice yourself. Please make sure to do it yourself.
Conclusion
In this article, we have discussed how to create a sales pipeline funnel in Excel. However, the dataset may vary based on different criteria associated with it. But this article will surely demonstrate an overall view of what the sales pipeline funnel can actually mean. Further, If you have any queries, feel free to comment below and we will get back to you soon. Also, you may follow our website, ExcelDemy, a one-stop Excel solution provider to explore more.
