Project Management

Work Breakdown Structure Template in Excel: WBS Codes, Work Packages and Rolled-Up Costs

How to build a work breakdown structure (WBS) template in Excel: the 100% rule, outline codes 1, 1.1, 1.1.1, work packages, formulas for level and rolled-up cost, a WBS dictionary and a food truck launch example.

Published 3 min read

A work breakdown structure (WBS) splits the entire scope of a project into smaller and smaller pieces, level by level, until each piece can be assigned to one person, estimated in time and cost, and checked when it is done. In Excel, a WBS is a table with outline codes (1, 1.1, 1.1.1) from which you can calculate each item’s level, roll costs up each branch and feed the Gantt chart. This guide covers the rules, the template and a worked example.

Three rules of a good WBS

  • The 100% rule: the children of an item add up to exactly the parent, no more and no less. If it is not in the WBS, it is not in the project.
  • Deliverables, not activities: “Permits approved” rather than “apply for permits”. Each branch is something you can hand over.
  • Stop at the work package: the lowest level has one owner, an estimate and a definition of done. A common rule of thumb puts a work package between 8 and 80 hours of effort.

Template columns

Column Example Note
WBS code 1.2 Enter as text so Excel does not turn it into a number
Item Kitchen fit-out A noun that describes the result
Level 2 Calculated by formula
Work package Yes / No “Yes” at the lowest level
Owner Maria Work packages only
Hours, cost $22,000 Work packages only
Definition of done Equipment installed and passed inspection What “finished” means

Formulas

  • Level (number of dots plus one): =LEN([@Code])-LEN(SUBSTITUTE([@Code],".",""))+1
  • Indented item name: =REPT(" ",[@Level]-1)&[@Item]
  • Rolled-up cost of a branch, summing only work packages to avoid double counting: =IF([@Package]="Yes",[@Cost],SUMIFS([Cost],[Code],[@Code]&".*",[Package],"Yes"))
  • 100% check: the sum of all work packages must equal the project budget.

Example: launching a food truck

Code Item Cost
1 Truck and equipment $40,000
1.1 Truck purchase deposit $18,000
1.2 Kitchen fit-out $22,000
2 Permits and licenses $2,000
2.1 Business and health permits approved $1,500
2.2 Inspection passed $500
3 Branding $5,300
3.1 Truck wrap $4,500
3.2 Menu boards and signage $800
4 Opening inventory $3,200
5 Launch event and marketing $1,500
Project total $52,000

The WBS shows that the truck and equipment take about 77% of the budget ($40,000 of $52,000), so that is the branch to get at least two quotes for. Permits cost little but can block the launch entirely, which is why they get their own branch with a clear definition of done. Permit requirements vary by city and county.

The WBS dictionary

For larger projects, add a “Dictionary” tab: for each work package, a description, the deliverable, the owner, assumptions and acceptance criteria. It prevents misunderstandings such as “I thought the generator was part of the kitchen fit-out”.

How the WBS connects to other tools

More guides are in Project Management.

Frequently asked questions

How is a WBS different from a task list?

A task list collects separate actions; a WBS organizes the whole scope by level and follows the 100% rule, so you can estimate and control the budget with it.

How many levels should a WBS have?

Two or three are enough for small projects. Break an item down further when it is too big to assign or estimate.

Should the WBS include dates?

No. The WBS defines what is delivered; the schedule, built from the work packages, defines when.

Does it work in Google Sheets?

Yes. LEN, SUBSTITUTE, REPT and SUMIFS behave the same way.