How to Modify P6 WBS Path Using Excel

One of the first important steps to create a detailed schedule is to create a proper work breakdown structure WBS path and to prepare a professional work breakdown structure.

Creating an Activity WBS Path in Excel can help you import your activities to your project schedule on P6 easily. Here’s how you can set it up:

Step 1: Copy your P6 schedule WBS path to your database Excel file

Open your Project XER file with your final WBS

Ready to take your skills to the next level? 🚀 Check out our Special Online Courses! 🚀
Gain valuable insights, boost your career, and learn from industry experts.

Planning Courses

From the Project tab choose WBS

Copy This WBS to your Excel in a new sheet.

Step 2: Create your guide table

At this stage, you must have created your data loading so far, from your data loading sheet insert a pivot table in a new sheet to outline the columns that will guide you to create your Activity Path.

First, you need to outline the columns for Activity ID and Activity Name.

Second, you need the columns that will refer to your WBS. In this case, your WBS highlighted is the building name, floor name, and type of work.

Step 3: Create your Activity WBS Path

Now, you can start creating your Activity Path. Add columns for the number of WBS levels you have in your schedule.

WBS L1:

This column represents your WBS Level 1, which corresponds to the project ID. Set it equal to Level 1 in your WBS sheet and press F4 to lock the reference and drag it to the end. This ensures that any changes made to the project ID in the WBS sheet will automatically update here as well.

FREE COURSE

Primavera Online Basic Course -English

Online Primavera P6 Course for Engineers & Construction Managers Learn how to use Oracle Primavera P6 to plan, schedule, monitor, and control…

5,455 students
WhatsApp support group
Certificate

Start free

WBS L2:

This column represents your WBS Level 2, which represents the construction phase, the code for construction level is “4” in the WBS Sheet, so make it equal 4 and drag.

WBS L3:

This column represents your WBS Level 3, which represents the building name, the code for your building is “1” in the WBS Sheet, so make it equal 1 and drag.

WBS L4:

At this level, we cannot simply fix and drag formulas due to varying levels within the WBS structure. Instead, we need a dynamic equation to adapt to our data.

This is where the SWITCH function comes into play. SWITCH simplifies the process by dynamically assigning the correct WBS code for each floor based on the provided data.

Use the following formula:

SWITCH= (D4, “floor name”, $WBS code$)

D4: Refers to the cell in your guide table representing the floor.
Floor name: Enter the specific floor name enclosed in quotation marks.
WBS code: Use the corresponding code for the floor from your WBS sheet.
$: Lock the reference using F4 to ensure consistency.

This method allows the formula to dynamically adjust based on your WBS structure.

Then, drag the formula down the column.

WBS L5:

you can do the same for this level as level 4, Use the following formula:

SWITCH= (E4, “Division”, $WBS code$)

E4: Refers to the cell in your guide table representing the Division of work.
Division: Enter the specific division name enclosed in quotation marks.
WBS code: Use the corresponding code for the floor from your WBS sheet.
$: Lock the reference using F4 to ensure consistency.

Then, drag the formula down the column.

WBS Path: This column helps you accurately position your activity within the correct location in your P6 schedule. It requires combining the project name and all subsequent WBS levels into a single code.

Use the following formula:

= WBS L1 & “.” & WBS L2 & “.” & WBS L3 … (continue to your final level).

This concatenated path ensures each activity is correctly mapped to its designated place in the P6.

After adding this equation, drag the formula down the column.

At this stage, you have everything needed to import the activities into your schedule: Activity ID, Activity Name, and Activity Path. Import the sheet into your schedule, and if any activity is not placed in the correct location, review your equations, make necessary adjustments, and re-import.

Now, your activities are properly organized within the correct WBS structure.

For more tips and tricks about WBS and scheduling, you can join our intermediate-level planning course.

Download the Resources of this Article