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

In this article, we will learn how to create a pipeline report in Excel. A pipeline report is very important for any company. The company can track how many potential deals are in its hands. Therefore, the pipeline report indicates the present and future sales condition of the company. You can easily create a pipeline report in Excel. So, without delay, let’s explore the solutions.

What Is Pipeline Report?

A pipeline report describes the overview of the present and future of a company. There are many types of pipeline reports. The sales pipeline report shows how sales are going for a company now and in the future. It indicates how many companies they have in their hands and how likely these deals are to happen. In summary, the sales pipeline report is a review of the available deals and their progression toward closing. In addition, there are other types of pipeline reports. The loan pipeline report shows how all open loan requests from clients are coming along. This report can also be used to show the progress of tasks after a loan has been made. Also, there is a type of sales pipeline called a commercial pipeline.

How to Create Pipeline Report in Excel: Step-by-Step Procedures

To demonstrate the steps for creating a pipeline report in Excel we will use a dataset of a vendor company. This company sells products to various well-known companies according to their demands. Now, we will take input about the sales progress of this company and will create a pipeline report accordingly.

STEP 1: Create a List of Stages for the Deal

Firstly, create a list of stages for deals.
Here, we have made a list of 4 stages.
The Lead stage indicates that the deal is in the primary stage.
The Opportunity stage denotes that we have made contact with that company and there is a good opportunity there to make a deal.
The Demonstration stage means that we have presented our deal to that company and waiting for the company’s response.
And, the Sale stage means that the deal is completed.

In the next step, we will insert deal information.
So, insert the company’s name in the Deal column.
And, in the Creation Date column, take input the dates when the information came into our database.
Then, we have added another column named Stage.
Now, in this Stage column, we will insert the condition of the deal.
Therefore, we will access the Stage column with previously made stages of a deal worksheet.

STEP 2: Enter Information for Creating Pipeline Report

In the following step, select the Stage column first.
For this reason, we have selected the range from D5:D10 cells.
After selection, go to the Data tab and click on Data Validation >> Data Validation from the Data Tools group.

Instantly, the Data Validation window will open up.
After that, select List from the drop-down menu below Allow field.

Now, write the following formula in Source:

=Stages!$B$5:$B$8

After that, press OK to proceed.

Here, Stages is the Excel sheet name of the Stages of a Deal dataset. We have to access the stages from the dataset. That’s why we have selected the range from B5 to B8 cells.

Now, in the Stage column, you can see a drop-down menu.
Clicking on the drop-down menu will show 4 options.
So, we have to select the option according to the progress.
As we have sold products to AAA Cooper company, we have selected Sale.

As a result, the Stage of AAA Cooper company is showing Sale.

Similarly, fill up the Stage column against the companies from the drop-down menu according to the progress.

Now, we will create the Type, Location, Value, Win Probability, Expected Value, and Close Date columns.
Then, insert the known information accordingly from the database.

STEP 3: Calculate Output

In the following step, we will calculate the Expected Value from the dataset.
So, write the following formula to calculate the expected value in the I5 cell

=G5*H5

Press Enter to calculate the result.

Here, the Expected Value comes from the multiplication of the Value and Win Probability of the company. That’s why we have multiplicated values of these 2 columns.

Now, drag down the Fill Handle to fill all the cells with the formula.

In the next step, we will sum up the Expected Value column to get Total Expected Value.
For this reason, write down the following formula in the I11 cell:

=SUM(I5:I10)

Now, press Enter to see the result.

After that, insert the closing date of the completed deals.

In the following step, return to the Stages worksheet.
Add a column named Value.
Here, we will calculate the amount in the Lead, Opportunity, Demonstration, and Sale levels.
Now, sum up the values in the Lead level.
To sum up, write down the following formula in the formula bar of the C5 cell:

=SUMIF('Pipeline Report'!$D$5:$D$10,B5,'Pipeline Report'!$G$5:$G$10)

Press Enter to watch the output.

This formula adds the cells specified by given criteria or conditions. Here, D5:D10 is the range argument that refers to the “Stage” column in the “Pipeline Report” worksheet. Before we made our pipeline report worksheet named Pipeline Report. Then, the 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.

Now, drag down the Fill Handle to fill all the cells with the formula.

STEP 4: Generate Funnel Chart

As a result, our result is ready.
After that, we will create a funnel chart from this result.

For making the funnel chart, go to the Insert tab first.
Then, select the following icon in the Charts group.
Instantly, a wizard will come out and select the Funnel chart from there.

As a result, we have successfully created the Funnel Chart in Excel.

Final Output

So, we have two outputs.
Firstly, we have created the following Pipeline report.

Next, we have made a Funnel Chart from the Pipeline Report.

Download Practice Workbook

To practice by yourself, download the following workbook.

Creating Pipeline Report.xlsx

Conclusion

In this article, we have demonstrated step-by-step procedures for creating a pipeline report in Excel. There is a practice workbook at the beginning of the article. Go ahead and give it a try. Last but not least, please use the comment section below to post any questions or make any suggestions you might have.

<< Go Back to Excel Sales Pipeline Templates | Excel Sales Templates | Excel Templates

Get FREE Advanced Excel Exercises with Solutions!