How to Create Project Pipeline in Excel (2 Suitable Methods)

Sometimes for business or institute purposes, you may need to create a project pipeline in Excel to track all your projects easily. So, if you are looking for how to create a project pipeline in Excel, then you have come to the right place. Today, in this article, I’m going to explain how to create a project pipeline using the Excel version of Microsoft 365 version.

What Is Project Pipeline?

A Project Pipeline is an essential tool for monitoring all the projects at once. This is the most important tool for a project portfolio manager who has to deal with lots of projects. Basically, in the Pipeline details information along with the current status of the projects should be stored. Additionally, you can say that a project pipeline acts like a project tracker.

How to Create Project Pipeline in Excel: 2 Suitable Methods

Here, I will demonstrate two suitable methods to create a project pipeline in Excel. A detailed explanation of the steps is given below.

1. Creating Project Pipeline Manually in Excel

Here, you can create a project pipeline manually in Excel. In addition, I will use some Excel features to make my project pipeline. I will use different formatting along with some calculations and charts to do this. The steps are given below.

Step 1: Create Table for Project Pipeline

Firstly, you may write all required terms which are important to track any of your projects in Excel. Here, I have written them in B4:L4 cells.
Secondly, make them Bold, increase the Font Size and change the Font Color.

Below, I have attached the sample image.

Now, select the data along with some blank rows.
Then, from the Insert tab >> choose Table.

Consequently, a dialog box of Create Table will appear.

Next, select the data for your table. Here, I selected the range B4:L15.
Make sure that “My table has headers” is marked.
Then, press OK.

At this time, you will see the following table. Where one design contextual tab named Table Design will appear within the top ribbon.

So, from this Table Design tab >> go to the Table Styles menu and select your preferred color for your table.

Finally, you will get your table.

Step 2: Format Cells of Some Columns

First, select these cells by pressing CTRL+C, where you have to use dates as cell values.
Then, from the Home tab >> go to the Number group >> from the Drop-Down Arrow >> choose More Number Formats.

Also, you can use the Context Menu bar or the Keyboard Shortcuts to open the Format Cells dialog box. In the case of using the Context Menu Bar, select the data range >> Right-Click on the data >> choose the Format Cells option.

Again, while using Keyboard Shortcuts, select the data range >> you need to press the CTRL+1 keys to open the Format Cells dialog box directly.

Lastly, a dialog box named Format Cells will appear.

Now, from that dialog box, you have to make sure that you are on the Number command.
Then, go to the Date option >> in the Type box >> choose your preferred format.
Finally, you have to press OK to get the changes.

So, you have created Date as the format for these particular cells.

Similarly, format the cells for budget as Currency.

Step 3: Use of Data Validation Tool

Then, create the following Summary table. Where you have to input all types of statuses that you need to express the situation of projects.

After that, I will use the Data Validation tool.

Firstly, select cells of the Status column where you want to insert only certain kinds of cell values.
Secondly, from the Data tab >> go to the Data Tools option.
Thirdly, from the Data Validation feature >> choose Data Validation… option.

At this time, a dialog box named Data Validation will appear.

Now, from the Settings menu >> choose List in the Allow: box.
Then, select references in the Source: box. Here, you must freeze your source using the Dollar sign ($) to copy Data validation. Otherwise, in every cell, the source may change to the next cell of that.
Lastly, press OK to make the changes.

Finally, you will see the drop-down arrow beside cell K5. And you can select any of the values.

Step 4: Calculation of Number of Projects and Their Duration

After that, write down the following formula in the C19 cell.

=COUNTIF(Table1[Status],"Complete")

In this formula, I have used the COUNTIF function to count the cells that have cell value as Complete.

Subsequently, press ENTER to get the result.

As there is no value in the Status column of Table1 so it shows 0 as output.

Then, use the following formula in the C20 cell.

=COUNTIF(Table1[Status],"At Risk")

Consequently, press ENTER.

Similarly, write the same formula for the other statuses.
Lastly, in the C24 cell use the following formula.

=SUM(C19:C23)

Here, I have used the SUM function to count the total project number.

Subsequently, press ENTER.

Now, write the formula in cell J5 to get the duration for starting the project.

=[@[Launch Date]]-[@[Execute Date]]

Then, press ENTER.

Here, I will copy the same formula using relative cell references. To do this, select cell J5. A square box will be shown in the bottom-right corner of cell J5, it is called the Fill Handle icon. Click the Fill Handle icon, hold it, and drag until you reach cell J15.
Then, release the Mouse button.

As a result, you will see all the copied formulas.

Here, you can increase or decrease the table size by dragging the Arrow situated at the bottom-right-most corner of the table according to your necessity.

Step 5: Insert Pie Chart for Project Pipeline

Firstly, you must select the cell values of the summary table. Here, I have selected the range B19:C23.
Secondly, from the Insert tab >> you need to select Insert Pie or Doughnut Chart.
Thirdly, from 2-D Pie >> you must choose Pie.

Lastly, you will see the following chart. As there have all 0 values so the chart is a blank one. Additionally, you may change the chart title.

Illustration of Using Project Pipeline in Excel

As you can see, here I have inserted the values according to the column title and the summary table along with the chart are auto-generated.

2. Use of Excel Template for Project Pipeline

The most interesting part is that Excel has templates for the project pipeline. So, you can easily use that. Now, let’s talk about how you will get the template.

Firstly, open an Excel workbook.
Secondly, go to the File tab from the top ribbon.
Thirdly, from the New menu >> choose More templates.

Then, from the window named New >> write “Project tracker” in the Search box >> then choose Project tracker.

At this time, a new window named Project tracker appears.

Here, click on the Create button.

After that, you will get the following workbook which has two worksheets named Project Tracker and Setup.

Then, go to the Setup worksheet.
After that, write your preferred Category name for projects and the Employee name of your company.

Now, according to that select your project category and employee. Also, insert your required values according to the column title. You can modify this as you wish. Every term of this template is editable.

Download Practice Workbook

You can download the practice workbook from here:

Create Project Pipeline Template.xlsx

Conclusion

I hope you found this article helpful. Here, I have described how to create a project pipeline in Excel. Please, drop comments, suggestions, or queries if you have any in the comment section below.

Get FREE Advanced Excel Exercises with Solutions!