Scotty
Pin Me

Use Excel to Perform Critical Path Calculations

written by: Rupen Sharma, PMP • edited by: Michele McDonough • updated: 6/22/2010

Are you planning a small project and need to identify the Critical Path to create a project schedule? Are you teaching a course on Project Management and need your students to understand the Critical Path concept? Microsoft Excel 2007 might be the best software for such situations.

  • slide 1 of 5

    Introduction

    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
  • slide 2 of 5

    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.)PrecedenceDiagram Paths To learn how to construct a Precedence Diagram and how to use the Critical Path Method, read:

  • slide 3 of 5

    Step 2: Prepare the Excel 2007 Spreadsheet

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

  • slide 4 of 5

    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.Excel Step2 SUMPRODUCT 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.Excel Step2 Max 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.Excel Step2 Conditional As you can see from the image above, Path 3 is the Critical Path.

  • slide 5 of 5

    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.