Project-Style (WBS) Numbering in MS Excel
If you know why you’re reading this already, skip to the code. Otherwise, here’s some explanation. In a project Work Breakdown Structure (WBS), tasks are organized into major tasks, sub-tasks, sub-sub-tasks, etc. as in the following example which is an actual project plan used by NASA:
Instead of sequentially numbering the tasks, we assign subtask numbers to those tasks that roll up under other tasks. Assemble spaceship is a subtask of Build a spaceship so it gets Build a spaceship‘s number (1) plus a subtask number (3, since it’s the third subtask) so its WBS number is 1.3. Attach top part to middle part is a subtask of Assemble spaceship, so it gets 1.3 plus a sub-subtask number (1.3.1), and so on. MS Project also bolds any items with subtasks.
Since this type of WBS or outline numbering functionality isn’t available in Excel, it requires a VBA macro.
Notes
- 1.
Here is a sample spreadsheet, complete with the code and a command button, for those who want something ready-to-use.
- 2.
Tested in Excel 2003 and 2007 for Windows.
- 3.
The macro is based on the concept that the indentation of a particular task in column B dictates its WBS “depth” (whether it’s a task, sub-task, sub-sub-task, etc.). WBS numbering will be created in column A.
- 4.
The macro renumbers everything at once, so after you add this macro to your project’s VB code, create a button or other control that runs the macro.
- 5.
It requires certain formatting. Read all of the formatting assumptions, commented at the top of the macro.