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!
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.
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.
Hello Jay,
I’m hoping to speak with you on a cost estimation project I’m working on, but want to automate the WBS in Excel. Would you be willing to speak with me so I can explain further?
Thank you and look forward to talking with you,
Brenda