Work Breakdown Structure

The main feature of this template is the formula used to create the WBS numbering based on the chosen Level. The Level uses a drop down list in Excel created via Data Validation, but you can also manually enter the Level number. I developed the formula for the WBS numbering originally for the latest version of the Gantt Chart Template Pro spreadsheet.

In this template I also added a conditional formatting rule to automatically highlight (shade) the Level 1 tasks. See "How to Highlight an Entire Row" in my conditional formatting article.

Note: The spreadsheets and code on Vertex42.com are copyrighted, so if you use the WBS numbering formula in any file other than the templates created by Vertex42, you need to reference this page after getting permission.

One of the main benefits of using a formula to create the outlining is that when you need to insert a task or move tasks around, all the numbering will automatically update. If you have ever used Word to create an outline, it's the same idea. When you insert a task in between to others you want the numbering to update automatically.

The only problem I've found with using automated numbering is that if you want to sort tasks by some other column, then the WBS numbers will change. You can use the built-in Filter feature in Excel, or the Outlining and Grouping feature, but avoid sorting rows if you are using a formula to define the WBS number.

If you want to use sorting: AFTER you have completely defined your work breakdown structure, then you could get rid of the formulas by copying the WBS and using Paste Special > Values to paste the WBS number values in place of the formulas. THEN, you can sort to your heart's content.

It's possible to use conditional formatting to automatically indent the task descriptions based on the Level, but this spreadsheet does not do that. Instead, you can manually adjust the indent using the Indent buttons in the Home tab. If you want to add automatic indenting, the trick is to define a series of custom number formats such as ". "@ for Level 2, ". . "@ for Level 3, and ". . . "@ for Level 4 and use a formula for the rule such as =($B6=3) where $B6 is a reference to the Level column.

Instead of using a separate column for the WBS number, you could create a list in which the WBS numbering is part of the Task Description, like the example below. The benefit of this approach is that no extra indenting is necessary.

1. A Level 1 Task
1.1. A Level 2 Task
1.1.2. A Level 3 Task
1.1.3. A Level 3 Task
1.2. A Level 2 Task

The easiest way I've found to create this type of list outside of Excel (so that you don't have to manually edit all the numbers) is to use Microsoft Word and create a list using the Multi-Level List option in the Home tab.