Primavera P6 export to Excel. How to color WBS level automatically

After export activity table from Primavera P6 to Excel, it’s very hard to distinguish WBS level.

6262
primavera-p6-export-to-excel-how-to-identify-wbs-level-for-activity-1.jpg

I will show you how to color WBS level automatically so we can have report like in P6.

  • Step 1:

Follow this article to insert the "WBS Level" column : https://doduykhuong.com/2018/04/01/primavera-p6-export-to-excel-how-to-identify-wbs-level-for-activity/

6263
primavera-p6-export-to-excel-how-to-identify-wbs-level-for-activity-3.jpg

  • Step 2:

Go to Conditional Formatting -> Manage Rules

6264
primavera-p6-export-to-excel-how-to-color-wbs-level-automatically-1.jpg

Create a New rule. Select Use a formula to determine which cells to format

In the formula box enter : =IF($A2=0,TRUE)

Select a color for WBS Level 0.

6265
primavera-p6-export-to-excel-how-to-color-wbs-level-automatically-2.jpg

Click OK.

In the Applies to box, click the arrow icon to select the cells.

6266
primavera-p6-export-to-excel-how-to-color-wbs-level-automatically-3.jpg

We select nearly the whole table.

6267
primavera-p6-export-to-excel-how-to-color-wbs-level-automatically-4.jpg

Click OK.

Now every WBS Level 0 will have green color.

6268
primavera-p6-export-to-excel-how-to-color-wbs-level-automatically-5-1.jpg

Then we create Rule for other WBS Level: 1, 2, 3

6269
primavera-p6-export-to-excel-how-to-color-wbs-level-automatically-6.jpg

Now the report is very colorful :-)

6270
primavera-p6-export-to-excel-how-to-color-wbs-level-automatically-7.jpg

 

About the Author : Khuong Do

Khuong Do is a Project Planner / Scheduler in Civil Engineering, Transportation, and Oil & Gas Industry. He has worked as a Primavera Consultant Specialist since 2006 and willing to share his passion for Primavera through lots of Tips & Trick on his personal blog : https://doduykhuong.com

Comments

No worries, I read your other

No worries, I read your other article ..... only after I figured out the formula..... :)

I also use an "isblank" statement in the conditional formatting to distinguish from WBS heading and activity, ie all activities have the same formatting similar to what is shown in P6.  Only the "WBS Headings" are formatted.

=IF($A2=6,IF(ISBLANK($C2),TRUE))

Where "C" column is Activity ID

How do you input the WBS

How do you input the WBS values in the inserted column? manually? 

?  

?
 

Thanks Philip. I'm very happy

Thanks Philip. I'm very happy when you enjoy it :-)

Excellent post! Simplicity is

Excellent post! Simplicity is Beauty now, as is so often the case.

I presently run a macro to colour the row by WBS level, which means sometimes one needs to 'refresh' the colouring up. This method requires no VBA, and will apply as soon as the project calculates upon a change to any of the numbers in Column 'A'.

 

Really elegant solution!

Market Place

Primavera P6 and Microsoft Project books, on-line video training courses and training material available from an internationally recognised publisher and PMI accredited REP. Teach yourself using on-line or book based learning or run your own in-house or public PMI accredited courses.
See how the TIME - LOCATION - VIEW brings clearness to your work programmes
For 25+ years, APMX has been providing competency based project management training to Fortune 500 companies around the world, applying the principles of project based learning, designed to produce measurable results, generating a favorable “return on training investment”.