Creating a Critical Path Template
Microsoft Excel doesn’t have a component that can help you identify the critical path of your project. However, with a little customization you can use Microsoft Excel to identify the critical path for small projects. For larger projects, you should consider purchasing project management software, such as Microsoft Project. This article will help you create a critical path template using Excel. The steps to use Excel for critical path calculations are:
- Create a Precedence Diagram
- Prepare the Excel Spreadsheet
- Identify the Critical Path
- Create a Gantt Chart
Step 1: Create a Precedence Diagram
A Precedence Diagram provides the flow of project activities from the start to the end of the project. In this article, we’ll use the Precedence Diagram shown below as an example. (Click any image for a larger view.)
[caption id="" align=“aligncenter” width=“600”]
To learn how to construct a Precedence Diagram and how to use the Critical Path Method, read:
Step 2: Prepare the Excel Spreadsheet
For CPM computations, you need three main headings: Activities List, Duration of Activities, and possible activity paths. Open Excel and enter the headings as shown in the image below.
[caption id="" align=“aligncenter” width=“600”] Open Excel and follow the directions[/caption]
Next, under the Activities heading, enter a list of activities, as shown below.
[caption id="" align=“aligncenter” width=“600”] Putting headings in for activities[/caption]
Under the Duration heading, enter the duration of each activity. For example, the Purchase Plot activity (cell B2) will take five days. Therefore, enter 5 in cell B5. Similarly, enter the duration of each activity as shown in the image below.
[caption id="" align=“aligncenter” width=“600”] Adding durations for our activities[/caption]
Now, input the paths identified under the Paths heading. To do this, input 1 for all activities that are in Path 1. For example, Path 1 has the Purchase Plot, Select Design, Purchase Wood, and Assemble Shed activities. Therefore, you will input 1 in cells B8, C8, D8, and J8. For all activities that are not in Path 1, enter 0. Therefore, cells E8, F8, G8, H8, and I8 will contain 0. Refer to the image below for the inputted data as per the Precedence Diagram.
[caption id="" align=“aligncenter” width=“600”] Create your paths[/caption]
Step 3: Identify the Critical Path
The Critical Path is the longest path in the Precedence Diagram. To compute the longest path, you will first need to calculate the duration of each path. To do this, use the SUMPRODUCT function. The image below highlights the SUMPRODUCT function to calculate each path.
[caption id="" align=“aligncenter” width=“600”] Use the SUMPRODUCT function[/caption]
You can identify the Critical Path by simply identifying the longest path manually or you can use the MAX function to do it automatically. When there are many paths, it is more efficient to identify the Critical Path automatically. To do this, first identify the longest path by using the MAX function, as highlighted in the image below.
[caption id="" align=“aligncenter” width=“600”] Use the MAX function to find the longest path[/caption]
Now, enter a conditional IF statement, that can compare the value of the Path Duration to the value of the Longest Path. If there is a match, then Column L should automatically show the term “Critical”. You can also use Excel’s conditional formatting feature to make the critical cell turn red. The image below shows the conditional IF statement.
[caption id="" align=“aligncenter” width=“600”] Conditional formatting can be used to spruce up your chart[/caption]
As you can see from the image above, Path 3 is the Critical Path.
Step 4: Create a Gantt Chart
After you’ve identified the Critical Path, you can do loads of fun stuff with it. For example, you can calculate the float (slack) for each activity, update the risk register with risk response strategies for activities on the Critical Path, and create a project schedule. Gantt charts provide a visual of your project schedule. To learn more about Gantt Charts and how to use them, read Using Excel to Create a Gantt Chart. If you’re looking for sample forms and downloadable templates, check out Bright Hub’s resource guide Over 50 Free Project Management Templates and Sample Forms.
- Screenshots taken by author.
- Further reference