How to Create a Weighted Sales Pipeline in Excel

Having a comprehensive picture of the leads and sales in a sales pipeline may undoubtedly boost conversion and sales rates. However, this may result in having to purchase expensive, sophisticated software. This article shows how to create a weighted sales pipeline in Excel using a step-by-step process that takes this into account.

overview of weighted sales pipeline excel

Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.

Introduction to Sales Pipeline

The Weighted Sales Pipeline acknowledges the fact that not all opportunities lead to sales. It is a more comprehensive sales forecasting tool that gives each opportunity a value based on where it is in the sales funnel.

Step-by-Step Procedures to Create a Weighted Sales Pipeline in Excel

This post will teach you how to use the SUM and SUMIF functions as well as Mathematical formulas to generate a weighted sales pipeline and how to create a funnel diagram. Now just go through the following instruction and the illustration step by step

Step 1: Create a Dataset with Proper Parameters

  • First, enter the various stages of a deal in the “Stages” worksheet. For example, we’ve entered 4 stages of a deal. Such as lead, opportunity, demonstration, and sale.

Stages of a deal

Read More: How to Create a Sales Pipeline Funnel in Excel (with Easy Steps)

Step 2: Insert Basic information

  • Secondly, Fill up the basic info of the various organizations on your worksheet. We entered 5 deals here.

creating weighted sales pipeline

  • Now, watch the animated GIF below to see how to enter the “Stages” into a Data Validation list in real-time.

 Data validation process

  • Next, Click the drop-down to choose a stage for each contract.

Selection of stage

  • Once the “Stage” for each “Deal” has been entered, the outcomes should look like the image below.

Overview of the table after selecting stages

Read More: How to Do Analysis Sales Pipeline in Excel (with Easy Steps)

Similar Readings

Step 3: Calculate the Total Value

Use the following expression to calculate the “Expected Value”.

  • Insert the following formula into cell I5.

Apply Mathematical formula to create weighted sales pipeline

  • The “Value” and “Win Probability” values are indicated by the G5 and H5 cells in this example.
  • Autofill the column up to cell I10 as in the image below.

Autofill Mathematical formula to create weighted sales pipeline

Calculation of total value

  • Hence, write down the below formula in the cell
  • Here, G5:G10 indicates the “Value” of the column.
  • Afterward, insert the following formula to obtain the Total Expected Value”.
  • In this case, the “Expected Value” column is represented by the I5:I10 array.

Calculation of total expected value

Read More: How to Create Pipeline Report in Excel (With Easy Steps)

Step 4: Create a Funnel Diagram

To generate a funnel diagram, first, go to the “Funnel Chart” worksheet >> go to cell C5 >> insert the SUMIF function in the Formula bar and write the following formula.

=SUMIF(‘Sales pipeline’!$D$5:$D$10,B5,’Sales pipeline’!$G$5:$G$10)

Calculation of weighted sales value by using SUMIF function

Formula Breakdown
  • SUMIF(‘Sales Pipeline’!$D$5:$D$10, B5,’ Sales Pipeline’!$G$5:$G$10) adds the cells that meet the criteria or conditions supplied. Here, the “Stage” column in the “Sales Pipeline” worksheet is referred to via the range argument D5:D10. Then, the “Lead” criteria argument to apply within the specified range is represented by the B5 cell. The final input, G5:G10, specifies the values to sum within the range and is optional.
  • The Output is $767000.


Please be sure that you press the F4 key on your keyboard to use Absolute Cell Reference.

  • Now, select cell B4 >> click Insert tab >> you will see the funnel logo, click on that logo and insert the Funnel Chart.

Insertion of Funnel Chart

  • Finally, the Funnel Chart will appear before you like the following image.

Funnel chart


You can use Ctrl + 1 key to customize the Funnel or use the Right Click of your mouse after selecting the Funnel Diagram. We have customized a Funnel Diagram like the image below.

Customized Funnel Chart

Read More: How to Build a Sample Sales Pipeline in Excel (with Easy Steps)

Practice Section

For your Practice, we’ve included a place marked “Practice Sheet” on the right side of each sheet. Don’t forget to complete it alone.

Practice worksheet



In essence, this lesson covers every aspect of creating a weighted sales pipeline in Excel. Now that you know all the methods, we hope you will apply them to your Excel spreadsheets more successfully. Please don’t hesitate to leave any comments or questions in the space below. Moreover, you can browse ExcelDemy’s other articles on Excel functions.

Related Articles

Mizbahul Abedin

Mizbahul Abedin

Hello! Welcome to my profile. Currently, I am working and doing research on Microsoft Excel and here I will be posting articles related to this. My last educational degree was BSc and my program was in Textile Engineering and management from Bangladesh University of Textiles. I am a Textile technology graduate with a great interest in research and development. I am very much passionate about my photography and won several prizes including an award from the Bangladesh tourism board.

We will be happy to hear your thoughts

Leave a reply

5 Excel Hacks You Never Knew

Genius tips to help you unlock Excel's hidden features