Use Excel to Perform Critical Path Calculations

Microsoft Excel 2007 neither has a component that can help you identify the critical path nor a Critical Path add-in. However, with a little customization you can use Microsoft Excel 2007 for identify in the critical path for small projects. For larger projects, you should consider purchasing project management software, such as Microsoft Project.

The steps to use Excel 2007 for critical path calculations are:

1. Create a Precedence Diagram
2. Prepare the Excel 2007 Spreadsheet
3. Identify the Critical Path
4. Create a Gantt Chart

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

To learn how to construct a Precedence Diagram and how to use the Critical Path Method, read:

• Constructing a Project Network Diagram
• Using the Critical Path Method (CPM)

For CPM computations, you need three main headings: Activities List, Duration of Activities, and possible activity paths. Open Excel 2007 and enter the headings as shown in the image below.

Next, under the Activities heading, enter a list of activities, as shown below.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.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.

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.

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.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 show the conditional IF statement.As you can see from the image above, Path 3 is the Critical Path.

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.