Eshrak Kader
Certainly, having a clear overview of the leads and sales in a sales pipeline can improve sales and conversion rates. However, this can lead to paying large sums of money for complex software. Keeping this in mind, this article demonstrates the step-by-step process of how to build a sample sales pipeline in Excel.
Download Practice Workbook
What Is a Sales Pipeline?
First and foremost, let’s get ourselves up to speed with a quick explanation of what a sales pipeline is. As the name implies, it illustrates the current stage of the buying process for sales prospects.
4 Steps to Build a Sample Sales Pipeline in Excel
In this case, the screenshot below is an overview of the article, which represents how to build a sample sales pipeline in Excel. In fact, in the following sections, we’ll glance at each step with the appropriate illustrations. Henceforth, let’s begin.
Here, we have used the Microsoft Excel 365 version; you may use any other version according to your convenience.
📌 Step 1: Create a List of Stages for the Deal
- First, go to the “Stages” worksheet >> enter the various stages of a deal, for instance, we’ve entered 4 stages of a deal.
Read More: How to Create Pipeline Report in Excel (With Easy Steps)
📌 Step 2: Enter Basic Information
- Second, move to the “Sales Pipeline” worksheet >> insert the data in the “Deal” and “Creation Date” columns.
- Next, observe the steps in real time in the animated GIF below to enter the “Stages” into a Data Validation list.
- Then, select a stage for each deal by clicking the drop-down.
- Eventually, enter the “Stage” for each “Deal” and the results should resemble the picture below.
Read More: How to Create Project Pipeline in Excel (2 Suitable Methods)
📌 Step 3: Calculate Value Total Value
- Third, enter the “Type”, “Location”, “Value”, and “Win Probability” information in their respective columns >> compute the “Expected Value” using the expression below.
=G5*H5
Here, the G5 and H5 cells refer to the “Value” and “Win Probability” values.
- Following this, enter the “Close Date” if the deal has been completed >> calculate the “Total Value” with the SUM function.
=SUM(G5:G10)
For example, the G5:G10 range indicates the “Value” column.
- Afterward, obtain the “Total Expected Value” value using the equation below.
=SUM(I5:I10)
In this situation, the I5:I10 array represents the “Expected Value” column.
Read More: How to Create Commercial Pipeline in Excel (With Easy Steps)
📌 Step 4: Generate Funnel Chart
- Fourth, navigate to the “Funnel Chart” worksheet >> jump to the C5 cell >> insert the SUMIF function into the Formula Bar.
=SUMIF('Sales Pipeline'!$D$5:$D$10,B5,'Sales Pipeline'!$G$5:$G$10)
- SUMIF(‘Sales Pipeline’!$D$5:$D$10,B5,’Sales Pipeline’!$G$5:$G$10) → adds the cells specified by a given criteria or condition. Here, D5:D10 is the range argument that refers to the “Stage” column in the “Sales Pipeline” worksheet. Then, B5 cell represents the criteria argument “Lead” to apply within the given range. Lastly, G5:G10 is the optional sum_range argument that indicates the values to sum within the range.
- Output → $150,000
📃 Note: Please make sure to use Absolute Cell Reference by pressing the F4 key on your keyboard.
- At this point, select the B4 cell >> proceed to the Insert tab >> insert Funnel chart.
- Following this, select the chart >> hit the CTRL + 1 keys >> choose Solid line border >> enter a color of your choice, here it is “black”.
- Not long after, left-click select the bars >> choose Solid line border and “black” color >> set width to 0.5pt.
- Last but not least, right-click on the bars >> select a Fill Color, for example, “light blue” >> change the chart title to “Sample Sales Pipeline”.
Finally, the results should resemble the figure given below.
Practice Section
We have provided a Practice section on the right side of each sheet so you can practice yourself. Please make sure to do it by yourself.
Conclusion
In short, this tutorial explores all the ins and outs of how to build a sample sales pipeline in Excel. Now, we hope all the steps mentioned above will prompt you to apply them to your Excel spreadsheets more effectively. In addition, feel free to leave any questions or feedback in the comment section. Or, you can check out our other articles related to Excel functions on ExcelDemy.
