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

Get FREE Advanced Excel Exercises with Solutions!

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.

How to Create Project Pipeline in Excel

  • 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.

Create Table for Project Pipeline in Excel


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.

Format Cells for Some Columns of Project Pipeline in Excel

 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.

Use Data Validation Tool for Pipeline in Excel

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.

Apply Excel Formulas to Calculate Number of Projects and Duration for pipeline

  • 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.

Changing Table Size of Project Pipeline in Excel


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.

Insert Pie Chart for Project Pipeline in Excel

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.

An Example of How to Use Project Pipeline in Excel


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.

Use of Excel Template for Project Pipeline

  • 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.

How to Create Project Pipeline Using Excel Template

  • 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:


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.


Related Articles

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Musiha Mahfuza Mukta
Musiha Mahfuza Mukta

Musiha Mahfuza Mukta is a marine engineer who loves exploring Excel and VBA programming. To her, programming is a time-saving tool for dealing with data, files, and the internet. She's skilled in Rhino3D, Maxsurf, C++, MS Office, AutoCAD, and Excel & VBA, going beyond the basics. With a B.Sc in Naval Architecture & Marine Engineering from BUET, she's shifted gears and now works as a content developer. In this role, she creates techy content exclusively focused on Excel... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo