Work Breakdown Structure
Create a WBS for your project plan using Excel® | Updated 8/18/2021
A Work Breakdown Structure (WBS) describes and orders the tasks or
activities for a process or project plan. The most common method for defining
a WBS is the outlining method in which tasks are defined using a hierarchical
structure and numbered using common outlining such as 1, 1.1, 1.2, 1.2.1,
1.2.2, etc. The WBS template on this page is a simple worksheet that
automates WBS numbering for you. You just need to select the Level from a
drop down list in Excel.
Advertisement
Many of the project management templates by [Link] use a WBS
approach for defining tasks. See the templates listed at the bottom of this
page for examples.
Work Breakdown Structure Template
for Excel
Description
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 [Link] 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.
Why Use Automated WBS Numbering?
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.
Indenting the Task Descriptions
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.