Full Text

If you know why you’re reading this already, skip to the code below the fold. 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:

1      Build a spaceship

1.1      Read wikipedia article on spaceships to determine required supplies

1.2      Buy spaceship supplies

1.3      Assemble spaceship

1.3.1      Attach top part to middle part

1.3.2      Attach middle part to thruster thingie

1.3.3      Paint spaceship a neat-o color

1.4      Set spaceship upright (facing sky)

2      Fly around in spaceship

3      Discover strange new worlds

4      Return home 

4.1      Point spaceship at Earth

4.2      Land spaceship on Earth

4.3      Park spaceship in designated parking space
 

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. Free code after the fold.

Update September 8 2009: Here is a sample spreadsheet, complete with the code and a command button, for those who want something ready-to-use. Also, I am pleased to learn that this post was featured on the Daily Dose of Excel blog on June 10.

Update June 1 2009: I discovered (and have added to the code) the most effective trick ever for speeding up Excel macros: Application.ScreenUpdating = False. 

Update April 6 2009: Added “format as text” line into the marco to prevent truncation of tasks ending in 0 (1.10, etc. — thanks Nick!) and fixed minor problem with parent-task formatting.

Notes about the macro:

  1. Tested in Excel 2003 and 2007 for Windows.
  2. 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: Excel tasks
  3. Indenting and “outdenting” is done with this control in Excel
    Excel indent icon
  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.

VBA Code:

Sub WBSNumbering

'Renumber tasks on a project plan
'Associate this code with a button or other control on your spreadsheet

'Layout Assumptions:
'Row 1 contains column headings
'Column A contains WBS numbers
'Column B contains Task description, with appropriate indentation
'Some text (here we assume "END OF PROJECT") delimits the end of the task list

    On Error Resume Next

    'Hide page breaks and disable screen updating (speeds up processing)
    Application.ScreenUpdating = False
    ActiveSheet.DisplayPageBreaks = False
    'Format WBS column as text (so zeros are not truncated)
    ActiveSheet.Range("A:A").NumberFormat = "@"
    Dim r As Long                   'Row counter
    Dim depth As Long               'How many "decimal" places for each task
    Dim wbsarray() As Long          'Master array holds counters for each WBS level
    Dim basenum As Long             'Whole number sequencing variable
    Dim wbs As String               'The WBS string for each task
    Dim aloop As Long               'General purpose For/Next loop counter

    r = 2                           'Starting row
    basenum = 0                     'Initialize whole numbers
    ReDim wbsarray(0 To 0) As Long  'Initialize WBS ennumeration array

    'Loop through cells with project tasks and generate WBS
    Do While Cells(r, 2) <> "END OF PROJECT"

        'Ignore empty tasks in column B
        If Cells(r, 2) <> "" Then

           'Skip hidden rows
            If Rows(r).EntireRow.Hidden = False Then

                'Get indentation level of task in col B
                depth = Cells(r, 2).IndentLevel

                'Case if no depth (whole number master task)
                If depth = 0 Then

                    'increment WBS base number
                    basenum = basenum + 1
                    wbs = CStr(basenum)
                    ReDim wbsarray(0 To 0)

                'Case if task has WBS depth (is a subtask, sub-subtask, etc.)
                Else

                    'Resize the WBS array according to current depth
                    ReDim Preserve wbsarray(0 To depth) As Long

                    'Repurpose depth to refer to array size; arrays start at 0
                    depth = depth - 1

                    'Case if this is the first subtask
                    If wbsarray(depth) <> 0 Then

                        wbsarray(depth) = wbsarray(depth) + 1

                    'Case if we are incrementing a subtask
                    Else

                        wbsarray(depth) = 1

                    End If

                    'Only ennumerate WBS as deep as the indentation calls for;
                    'so we clear previous stored values for deeper levels
                    If wbsarray(depth + 1) <> 0 Then
                        For aloop = depth + 1 To UBound(wbsarray)
                            wbsarray(aloop) = 0
                        Next aloop
                    End If

                    'Assign contents of array to WBS string
                    wbs = CStr(basenum)

                    For aloop = 0 To depth
                        wbs = wbs & "." & CStr(wbsarray(aloop))
                    Next aloop

                End If

                'Populate target cell with WBS number
                Cells(r, 1).Value = wbs

                'Get rid of annoying "number stored as text" error
                Cells(r, 1).Errors(xlNumberAsText).Ignore = True

                'Apply text format: next row is deeper than current
                If Cells(r + 1, 2).IndentLevel > Cells(r, 2).IndentLevel Then

                    Cells(r, 1).Font.Bold = True
                    Cells(r, 2).Font.Bold = True
                'Else (next row is same/shallower than current) no format
                Else
                    Cells(r, 1).Font.Bold = False
                    Cells(r, 2).Font.Bold = False
                End If
                'Special formatting for master (whole number) tasks)
                If Cells(r, 2).IndentLevel = 0 Then
                    Cells(r, 1).Font.Bold = True
                    Cells(r, 2).Font.Bold = True
                    'Add whatever other formatting you want here

                End If

            End If

        End If

    'Go to the next row
    r = r + 1

    Loop

End Sub
  • Google Bookmarks
  • Reddit
  • Facebook
  • LinkedIn
  • Twitter
Posted in Code  |  70 Comments

70 Responses to “Project-Style (WBS) Numbering in MS Excel”

  • Nick Leeder says:

    Hi,
    This macro works brilliantly except for one slight flaw with the numbering. When you get to ten sub items (or multiples of ten) e.g. 7.10, because of the way it is set up on numbers, it returns 7.1, thus duplicating the the first sub item (7.1).
    Any ideas on how you stop the macro or excel doing this?

  • Jeremy says:

    Hi Nick — I am amazed that someone found a use for this so quickly and I’m really glad it works for you. Thanks for the question; try formatting the WBS column as text and then re-running the numbering macro.

    When I get a chance I’ll go back and add that action into the macro; I should have included it either as an assumption or in the code. As you might have seen, the macro already removes “Number Stored As Text” errors because it’s assuming that the column is in text format.

    Update — I think I fixed this. Let me know if the new macro successfully eliminates this issue.

  • Dan says:

    Thanks so much for this. It really helped out on a project at work.

  • Scott says:

    Very nice code! The next phase, it seems to me, is to run through and set row groupings based on the WBS, so that the whole thing really does become a expandable/collapsable project plan.

  • Brent says:

    I agree with Scott…any ideas on how to auto-group (outline)? I’ll do some googling and see if I can see if someone else has done something like this.

  • Jeremy says:

    Thanks Scott & Brent. I think that’s a good suggestion. I was looking into how to do it and I think the easiest way is Group & Outline (http://office.microsoft.com/en-us/excel/HP052016411033.aspx) but it’s kind of ugly so, as usual, I’m going to see if I can find a more complicated solution. Pretty busy right now but this is on my to-do list. If one of you figures it out first, please let me know and I can post an update with your contribution.

  • Mark says:

    This a great tool but not having a programming background are there any steps for me as a layman to take to program this onto my computer? May be a stupid question… not really sure.
    Thanks

  • Chris says:

    Thank you very much for posting this, especially the xlsm download. Your contribution is going to be a key factor in the success of a project that I am working on.

    Have you looked at an Earned Value Management supplement to this macro?

    Thanks again

  • Martin says:

    AWESOME!!! Thank you very much.

  • Ellerd says:

    Great macro. One question left, i also don’t have any knowledge of programming. What do i need to delete or modify in the Macro, if i only want that level 1 items will be bold?

  • Jeremy says:

    Cheers Chris, Martin, and Ellerd.

    Chris – my long-term goal is to develop this into a much more comprehensive project management tool. Earned value management would definitely be a nice feature so I’ll add that to the list. Unfortunately life is getting in the way of my little programming projects these days and I probably won’t be making much progress on this for a while. Collapsible task groups are the next thing on the list.

    Ellerd – first, unbold all your current lines on the project. Then comment out what appear above as lines 95-103 in the code above and re-run the macro. To comment out a line, just put a single quote mark at the beginning, like you see on line 94 (once commented it will turn green). You could also just remove those lines if it’s easier.

    I think that will accomplish what you’re after. If not, let me know.

  • Guy says:

    Jeremy,

    Thanks a ton for this macro. I would like for it to “Do Unitl” if finds two blank cells in succession. I can make it work if only looking for first blank cell with Not IsEmpty(Cells(r, 3)). I have seen this before but cannot figure it out.

    Thanks in advanced.

    Guy

  • Tony Wright says:

    Brilliant piece of work – many thanks for making it available to us non-VBA types.

    Tony

  • Etienne says:

    Hi Mark, from a real dummy,
    Tried http://j.modjeska.us/data/wbs_numbering.xlsm, what next? Extracted the files does ot seem to work?

    Hope I dont insult you.

    Regards

  • Jeremy says:

    Guy – Only 6 months late here with your answer, but here it is anyway because I think it’s a good idea.

    This will eliminate the need for an “END OF PROJECT” flag, and instead it will stop numbering when it encounters two successive blank rows. This assumes your task descriptions are in Column B. If your task descriptions are in a different column, change the number 2 in the code below to the number corresponding to the desired column (C=3, D=4, etc.). I think the IsEmpty function might be Office 2007+ only, so YMMV on other versions.

    Do While Not (IsEmpty(Cells(r, 2)) And IsEmpty(Cells(r + 1, 2)))

    Hope that helps.

    Etienne – Now sure what you’re asking, but the file is for Excel 2007. It may not work on other versions.

  • Tom says:

    I downloaded this expecting a .xls or .xlsx file but got a zip file which expanded to a lot of files, but none that are Excel files. What am I missing? I guess I could retype the macro by hand from the screenshot but that seems error-prone. I have Excel 2007. In any case, thanks for making this available.

  • Jeremy says:

    Tom: The only file linked here is a .xlsm (Macro-enabled Excel file). I don’t know what zip file you’re talking about.

  • Jun says:

    Hi, just learning how to use VBA… How do I calculate “depth” if instead of indenting the subtasks as you described, I put each subtask into a new column? Any advice would be appreciated. Thank you!

  • Jeremy says:

    Jun – I don’t have a full solution for you, but the following code should return the last used or “deepest” column in a given row. Replace the code on line 40 with this:

    depth = Cells(r, 2).End(xlToRight).Column

    This should return depth as a number corresponding to the last used column (A=1, B=2, etc). It’s going to consider *any* data in a column, so if there are static columns beyond the task descriptions (dates, resource names, etc), you’ll need to subtract those from “depth.”

    Be careful with this method. Unless you put your task descriptions in the right-most column you could end up limiting your available subtask depth.

  • Jun says:

    I naively tried just changing line 40 with what you provided for me. It runs into an infinite loop. No clue why… (I did try using your code using indents for subtasks and it totally works! Great code!!) Do think you help me come up with what I am missing? Thank you.

  • Jun says:

    Sorry, it is not an infinite loop. When I stepped through, the new line 40 gave me depth=16,000.

  • Eve says:

    Hello Jermey….
    This is great :) yet when i tried it the numbering started not from the fisrt row that has my data yet it started with the below row, i.e. not row number 2 but 3. in addition the nubering starts with 0.1 not 1!!!! thats really odd! thought itried it to your example and it works just fine!!!

    On the other hand, i type my data, the main one, on a cell in one column, and the sub ones on a different column, lets say: Main points are located in col. B and the sub are located on col. C… etc for other sub points. so no indentation happens.

    So actually what I thought of is, since I enter the data in this way; I’m trying to do whenever there is a non blank data in a certain cell so this is a main point and shall take the first level of numbering (i.e. 1), while if its blank then those belong to the data that are supposedly sub points of the main one (1.1, 1.2, 1.3… etc, 2.1, 2.2,… etc).

    I know I have to increase the numbering of the sub as well as the main one whenever a nonblank data occurs. Yet I cant do it in the simple if function, nor working for me when I VB script it! not to mention I’m not that good in scripting or VB!

    I really have to get this done but I need a help from you, after all, you seem to be very expert in this :D

    Please advise and help me out.

    I’ll be waiting for your assistance, and if possible your solution.

    Many thanks in advance and wish you a joyful day.

    Regards,
    Eve

  • sandie says:

    thanks for this, the company i work for wont invest in Project, so this is the next best thing, cheers

  • Jared says:

    Great code!

    What would it take to be able to shift the cells down (insert a row above) without having the numbering column header become numbered.

    Renumbering makes the 1st entry be “2”.

    Thanks

  • Jared says:

    Also,

    The format forces some unwanted “bolds”. What would it take to remove these formatting changes?

    Thanks

  • Chris says:

    Great Macro indeed. I’m looking for a similar macro which, instead of creating numbering, automatically creates row based groups.

    Any ideas are greatly appreciated.

    Chris

  • Jeff says:

    Hi Jeremy,

    Just curios if this code can be adapted to add a “.” to the end of the numbers?

    1.
    1.1.
    1.2.
    etc.,

  • Jeremy says:

    Hey y’all. Sorry for the delayed response. Life is busy again! Here are some completely untested and probably wrong answers …

    Eve & Jared: In the linked sample spreadsheet, line 27 should say “r = 3”. That is the starting row. If you want it to start numbering at a different row, change the “3” to whatever row ought to contain WBS 1.0.

    Also Jared: Again on the linked sample, bolding is happening on lines 98-108. You can safely remove / comment out the lines that say “Cells(r,#).Font.Bold = True”.

    Chris: Not sure what you’re looking for. You can explain further here if you want, although you might have more luck searching / asking somewhere like http://www.ozgrid.com/forum/. They are much smarter over there than I am.

    Jeff: Sure. A safe way to append a period after the FULL wbs name is assigned is to insert a line right before the wbs string is written to the cell. So, look for line 89 which should be a comment that says “Populate target cell with WBS number” and right after that comment (i.e., right BEFORE the line that says “Cells(r,1).Value = wbs”) insert a line that says: wbs = wbs & “.”

    Good luck & happy coding to all.

  • Justice Steele says:

    Hello!
    I love your tool and I’ve had it in my toolbox since last year. I revisited it yesterday and ran into a problem. Renumbering causes Excel to freeze, go into “Not Responding” mode, and no renumbering occurs. I’m running 2007 but it looks like you’ve tested it already. I enable macros . . . and I’m not sure what to do next. I tried to download the tool again but when I look in the zip file I do not see the Excel file.
    Any suggestions? I’m not a developer so my ability to understand and tweak the code is limited, although I follow instructions well.
    Thanks,
    Justice

  • Jeremy says:

    Justice – you’re the second person to reference a Zip file, but I don’t know what you’re referring to. The only attachment I have posted related to this code is the sample spreadsheet in .XLSM (Excel 2007 Macro-Enabled) format: http://j.modjeska.us/data/wbs_numbering.xlsm. Maybe someone else is redistributing this code in another format? If so, I obviously can’t vouch for anyone else’s code or files.

    The “not responding” error is most likely due to Excel being in an infinite loop. That may mean that you’ve accidentally deleted the “END OF PROJECT” text from column B. If you don’t like “END OF PROJECT” you can change it to something else (see lines 30-31 in code sample above), but you need to have some kind of flag at the end of column B so the code knows when to stop. Hope that helps. If it’s something else, let me know, or send me your spreadsheet and I’ll take a look.

  • Justice Steele says:

    Thank you!
    I deleted End of Project.
    It’s so weird – when I click on your link it still gives a zip folder. The name of the folder is wbs_numbering.zip.
    I can download the folder and email it to you for inspection if you’d like.
    Thanks,
    Justice

  • Jeremy says:

    Justice — my apologies. Apparently MS Office files randomly turning into Zip files is a known issue (http://www.webdeveloper.com/forum/showthread.php?threadid=162526). Unfortunately I have no way to know if I’ve fixed it since I am unable to replicate the problem on any of my computers or any of my friends’ computers, so maybe just try changing the extension to “.xlsm”? Alternatively, here is an actual Zip file — maybe this will work properly: http://j.modjeska.us/data/wbs_numbering.xlsm.zip.

  • Justice Steele says:

    Thank you – the actual Zip file works perfectly.

  • George says:

    What can I say… Thank you, Thank you, Thank you, for creating and sharing this Excel code to generate WBS numbering.

  • Don Francis says:

    This is great code. I found it as I was looking for the opposite outcome. This I will use, but can you tell me if you have come acrossed any code that will:

    Take a WBS currently with a numbering scheme and a flat tasks list and index the tasks based on the WBS number. Example: 3.4 will indent 1 tab; 3.4.5 will indent 2 tabs, etc?

    I can get a WBS out of Project for Excel, but it does not come acrossed with the indenting, however the WBS numbers come acrossed in their own cell.

    Any help will be appreciated.

    Thanks,

    Don

  • Patch says:

    Hi there
    As a non-VBA expert this seems to me to be an impressive bit of code, but almost too complicated for what I need. How easy would it be to have something similar, but instead of the outline level being determined by the level of indentation, for it to be determined by an index number in another column?

    The reason for this is that I already have a long list of tasks and I don’t want to have to manually set the indentation level for each of them.
    Instead I would like to insert a new column as column A (‘Outline Level’) and be able to put numbers in there to determine the outline level for each row (i.e. 1=X; 2 = X.X; 3 = X.X.X, etc).
    I’m sort of assuming that would be quite an easy change but have no idea how to go about it myself… any suggestions?

    Many thanks.

    Patch

  • Cristián says:

    Hi Jeremy

    Excellent Job!
    It´s very useful for WBS and for accounting design too.
    I would like to know if you have developed another Excell Macro for Project Management.
    Thank you so much for sharing.

    Cristián
    Buenos Aires – Argentina

  • Jarps says:

    Hi Jeremy
    A few months ago, I have created a gantt template in excel for a specific project. In spite of using MSProject more frequently in my work, I needed in that specific project to compile in a single file several types of data, including a sort of project management. Your code of wbs was a good increment to my template. Thank you very much for that. Now if you or anyone else want to see a simplified example of that please visit this link http://exceleoutrascoisas.blogspot.com/2011/07/excel-gantt-template.html and send me your feedback.

    Jarps
    Porto, Portugal

  • Paddy says:

    Hi Jeremy,

    just wanted to let you know that your script is still being looked after!
    I altered the script a little bit and stored both the wbs input and output in arrays, in order to populate a userform with the results.

    Originally I was looking for a script that could help me build a tree à la Windows Explorer (the one that sows up when clicking the + next to a folder), but the wbs code is much clearer and certainly more professional.

    Thanks again
    Paddy

  • Jeremy says:

    Outstanding. Thanks Paddy! So glad this is still useful.

  • Ryan says:

    Jeremy,

    Thanks much for posting this code…I just used it as the basis for an excel file that is the source document for a “self-building” power point presentation. This gave me the start I needed in excel to order tasks and subtasks that could then be turned into slides and subslides in ppt. Thanks!

    -Ryan

  • Kristian says:

    Many thanks for posting this. Exactly what I was looking for.
    Really makes my project plan easier to read (and maintain).

    (I changed basenum to start at 1.)

    Now to get spaceship supplies …

    -Kristian

  • Ava says:

    Thank you! Exactly what I was looking for for easily generating a proposal outline template. Microsoft should probably hire you to write this into excel’s coding, I can’t believe excel doesn’t come with this functionality out of the box. Again, THANK YOU, you’ve helped me and a lot of other people look good ;)

  • Manochehr says:

    That is great Jeremy,

    Is it possible also to write a function to outline the list based on WBS numbering?
    Can anyone guide me how to it also.

    Manochehr

  • Manochehr says:

    Another question is that: Is it possible to auto grouping the list based on WBS numbering in excel?

  • Steve says:

    Thanks for the doing this! I can’t seem to use the macro on more than one sheet in a workbook. Is there something I need to do to get that to work? Thanks again!

  • Another thanks! Works like a charm and it saved me a few hours of time. I’m using it directly in Excel to create a project schedule worksheet with a Gantt chart (stacked column chart). The y-axis will contain the task name, now with the WBS number concatenated to it (in parenthesis).

  • Jamie says:

    I’d like to be able to have tasks with 1 indent (1.1, 1.2, etc) bolded. Right now the master tasks with a 0 indent bold, and any tasks that have sub-tasks will bold as well. I have tried a few things but can’t figure it out (I’m a novice at best). Would you mind providing me with the script to do this? Thank you!

  • Ricky Bell says:

    Works great! I added to it if anyone is interested. Mine is based off the user’s selection (As long as they select 2 columns). It uses your numbering code then I added code to group the rows and use the built in + and – excel uses for grouping.

    http://www.rickybell.com/temp/excelgrouping.zip

    Also, mine ignores the zero indent items (client request).

    Thanks again for the help!

Leave a Reply