Automated WBS in Excel

When you’re building a plan in Excel, especially with OnePager, automated population of data can be critical to gaining efficiency in the long-run.

Organizing, updating, visualizing, and generally making sense of your plan will all be much faster and easier if you have the data there from the beginning to leverage sorting and filtering capabilities built into Excel and OnePager.

Adding a WBS is another value that can be added to your Excel plan to help in this regard, however, having to do that manually may seem daunting.  Luckily, the internet provides when it comes to Excel knowledge and we found a very nice article and template previously posted online that can might provide you an excellent start!

WBS Excel

Jeremy Modjeska’s approach was to create a macro that would populate the WBS and auto-format some of the task names in Excel based on level of indent, per the example above.

Here is a link to his blog post where he describes his thinking and provides his code, along with a downloadable sample Excel file.

Without this level of organization, you will inevitably be looking at a large list of things.  While the large list can be useful, without a visual queue, it will be very difficult to tell where one group of activities/milestones ends and another begins.

I find the solution very helpful and hope you do to!

As always, comments and questions are welcome.

This entry was posted in Excel Tips, Project Reporting, Project Visualization and tagged , , , , by Jay. Bookmark the permalink.

About Jay

Devoted father of two, lover of mountains, entrepreneurism, and beer. Jay carries with him fourteen years of project management experience within the cable, telecom, construction, software development, and energy industries. The spectrum of projects and programs that Jay has managed throughout his career is broad and deep, enabling him to help clients implement Chronicle Graphics software in a multitude of applications. His employment history includes positions at Narvaes Construction, Leslie Brothers Construction, CSG Systems, Echostar Satellite Services, Comcast, and Level 3 Communications.

2 thoughts on “Automated WBS in Excel

  1. Hi Jay:
    I was looking at this Automated WBS in excel. I don’t really understand the advantage and how it would work if you had to reorganize, move activities around, etc. Is there a visual i could reference so i understand the advantages of this?

    • Karin,

      If you are planning to outdent/indent an existing task or change its position, the WBS may not be of great value, since the WBS number would need to change in accordance with the structural changes to your schedule. If your WBS is fluid, it probably won’t lend itself to great reporting.

      In the example that we’re citing from Jeremy’s blog, the WBS number is driving the indentations of the chart, not the other way around. If you are familiar with Microsoft Project, the WBS number changes as the structure of the schedule changes. In Jeremy’s macro, it’s the opposite, though it yields the same end result, as the two are linked.

      Some of our OnePager users leverage WBS for conditional formatting and for custom import filtering, so that’s where the value is, but again that would depend on a consistent WBS.

      If the WBS numbers are subject to change due to structural variations in the schedule, then there are easier ways to drive the look and feel of your OnePager chart that would be more robust and not susceptible to schedule variability.

Leave a Reply

Your email address will not be published. Required fields are marked *