Loading...
Quick Links

Ask the Teacher: Export the Task List to Excel and Keep the WBS Structure

I would like to export the task list in Microsoft Project to Excel and keep the WBS structure. A cut and paste loses the WBS structure. Is there a way to accomplish this?

Answer: Yes, you can do this. The answer comes from Greg H. of Wheaton, IL, who presented his solution at an MPA meeting years ago. This export can only be done on Microsoft Project 2002 and 2003, but not earlier or more recent versions of the product.

The first decision you have to make is what you would like to export. You can export any standard or custom tables or you can select individual fields to create your own export map. For the purpose of this example, we’ll export the task entry table to Excel, keeping the WBS format intact. Here’s an example of a direct export from Project to Excel.
Ask the Teacher: Export the Task List to Excel and Keep the WBS Structure

 

  1. Open the Microsoft Project file.
  2. Choose File | Save as and select the location where you wish to put the file.
  3. Enter a filename.
  4. Specify the Save as type as Web page (*.html, *.htm).
  5. Click on Save. The export wizard will start.
  6. Choose Next (to continue).
  7. We will select a new map for this example. Choose Next.
  8. Click on Tasks in the top area. Make sure that the Export Header row is selected in the bottom area, then click Next.
  9. For this example we will use the table Entry, so click on the buttom, “Base on Table,” and select Entry.
  10. Click OK to continue.
  11. Click Finish

You have created a .html file that looks like this:

Ask the Teacher: Export the Task List to Excel and Keep the WBS Structure

  1. Right-click on the file name in Windows Explorer. Rename the file by just changing the file ending to an .xls file. You will see this error message:

Ask the Teacher: Export the Task List to Excel and Keep the WBS Structure

  1. Click “yes” to make the change.

The result will be something like this:
Ask the Teacher: Export the Task List to Excel and Keep the WBS Structure
Give it a try!

Share This Post
16 Comments
  1. wow. I have been trying to figure this out for years. terrific solution. thank you so very much.

    Reply
  2. this doesn’t work for 2010 or 2013 project. this is for old project.
    There is no save as a HTM option in the new ms project.

    Reply
  3. You are correct. This article was written in 2008 and was for MSP 2007. In the current version you can use copy from MSP and paste to Excel achieve the WBS indentions in Excel.

    Reply
  4. Dear Team

    this is Rajaram,

    I export pile from MS-project to excel sheet, but in excel sheet showing the date with timing (17-07-2016 8:00:00 AM) tell me how to remove the date from excel sheet or how to export the file from MS – Project without time

    Regards
    S.Rajaram

    Reply
  5. You can’t control the export format. In Excel you can right click the date columns and select an alternative format.

    Reply
  6. Thank you for the guidance, but I need help on exporting the tasks and sub tasks.
    As on MS Project we create a task and a sub task where we can maximize and minimize or outline the task but when exported to excel that particular option is unavailable.

    Could you please help me on this??

    Regards

    Reply
  7. As of MS Project 2010 you could copy and paste a view from MSP to Excel and keep the outline structure. The export program will not keep the outline structure.

    Set up a table view and copy and paste it into Excel. Let me know if you still have a problem.

    I hope this helps.

    Reply
  8. What is the easiest dummy way to export project in to excel with keeping the formatting. It seems too hard to do it. Any one has any easy tips.

    Reply
  9. Thanks so much. The right think that I need is to export the information including the outline number for example:
    1 Project
    1.1 Definition
    1.1.1 Basis
    1.1.2 Details

    Etc.

    Reply
  10. If you are using MSP 2010 or higher you can put a table together the way you need the report and copy and paste the table into Excel. The outline structure will remain. You can include the outline number as a column in your table to export. I hope this helps.

    Reply
  11. New June 2018 whitepaper I found posted to CNET Download.com. There is a Linked in reference to the sites as well. Goes over many of your options and has an excel spreadsheet tool that you can use to clean up the formatting and date value problems you run into when using the Microsoft Project 2013/2016 SAVE AS>Excel Workbook wizard. This is a pretty good alternative and workaround. I tried it on the included test .mpp file to learn how to use the tool. Then tried it on my own 500+ line .MPP project file. Only took me minutes to clean up the formatting. Once you know how, it goes very fast. Whitepaper is also a step by step guide to using the excel tool provided with the. Whitepaper. Search CNET on WHITEPAPER & TOOLKIT – EXPORTING MICROSOFT PROJECT GANTT CHART DATA TO MICROSOFT EXCEL. URL is https://download.cnet.com/Excel-Export-from-MS-Project-Tool/3000-2077_4-77876847.html.

    Reply
  12. Oh, it is a free download of CNET DOWNLOAD.COM. Complete and fully functional.

    Reply
  13. I contacted the publisher/author of the above whitepaper and toolkit and asked him about why he did not simply use COPY AND PASTE. It is not mentioned in the whitepaper, but the whitepaper also pointed me to a bunch of solutions which also work very well that I did not know about – including a file conversion service. The whitepaper is very useful and covners a whole bunch of alternatives.

    The author replied to my copy and paste question as follows. His points are very good and I would have run into almost all of these issues he mentioned in the reply. His name is MASAMITSU, and he is based somewhere in Southern California:

    Q: “Why not just use ‘COPY AND PASTE’ directly from the open MS Project MPP file into a blank Excel XLSX file?”

    A: Copy and Paste works fine, especially on smaller Gantt Charts. Nothing wrong with using it, and I do use it as well.

    Depending upon what you want to grab, you are likely to still need to edit the final excel file to get it into the condition that you want,
    Especially with larger Gantt charts, and you have to exercise some caution.

    Many of my Gantt charts are over 600 lines long, and many are thousands of lines long, as I tend to be called in on contract to be a Senior Program Manager or an interim PMO manager, with a large, complex portfolio and several PMs working underneath me.

    I have run into these issues:

    1. Copy and Paste only grabs whatever is on your screen As-Is. Sometimes you want that, sometimes you don’t
    a. If your GANTT chart has collapsed summary tasks that have lines underneath them, those tasks will not be grabbed.
    You need to remember to go to the OUTLINE section of the toolbar and set SHOW ALL LEVELS before you copy and paste.
    2. Copy and Paste will not grab the TASK ID NUMBER.
    a. It is normally not a big thing to add the numbers in manually but you then need to quickly check to make sure that the task numbers
    You created reconcile with what is actually in the plan. This is not a big thing, either, but if you have hidden or collapsed a
    Summary task, the numbering can get out of sync.
    b. When you are using the SUBPROJECT feature of MS Project to create an Integrated Program Plan of interrelated projects being run in sequence or parallel,
    numbering and reconciling ID numbers becomes a more daunting task that can be a lot more error prone. If you manually add in ID numbers after
    a CUT and PASTE, you now have to reconcile them amongst what can be multiple project plans. Some of my initiatives have had as many as twelve separate subprojects,
    and often I have 3-10 PMs underneath me, each working 5-15 projects each.
    c. If you are importing through the MS Project Wizard, it will always grab the appropriate ID number for each task.
    3. You have the same issues with underlined fonts whether you COPY AND PASTE or bring it in through the SAVE AS Wizard.
    4. If you do not correctly pre-size the column widths in the target EXCEL file, word wrap will throw off cell sizes when you PASTE.
    When you bring in the data through the wizard, auto cell resizing is a click on the column width.
    5. Some centering and justifying in certain entries can still get thrown off when you paste, especially in the PREDECESSORS column
    6. Sometimes you want to bring in more than just the Gantt Chart data. You want to also bring in TASK USAGE SHEET and assignment info.
    a. Yes, you can cut and paste this as well but you often still end up editing the file here, too.
    7. I have also had PMs, Developer Leads, Infrastructure Leads, etc. working with me who do not have a personal copy of MS Project on their home systems.
    They might have had it at work, but not at home. Project is not cheap and when you have kids and dependents, it is not a simple thing to shell out the
    money and buy it. The whitepaper pointed out some options to them for MPP reader files and services they could use to convert the files in a pinch.
    The Excel tool enabled them to convert their Gantt charts themselves and send themselves EXCEL files for home, etc.

    If Copy and Paste works for you and your Gantt charts are small enough, use Copy and Paste. Nothing wrong with using it. Sometimes you need another option,
    and using Copy and paste still means that you have to edit the target file.

    Again, the reason for the whitepaper is that One Size does not fit all. It is not just a document that walks you through use of the tool.
    Sometimes there are very good reasons why we cannot use a particular solution, and unless you have a better idea of what your options and alternatives are, you can miss something.

    No, I did not create a section in the whitepaper on the use of Copy and Paste, because most of my team had tried it and circumstances led us to look for other solutions.
    It is not a bad idea to add this section when we revise the document.

    Reply
  14. If you note the date of the posting, MS Project 2010 was not available yet. The copy and paste option started with that software level. Not all articles have been updated for the current software levels.

    Since you can use copy and paste, it can only copy what it sees and paste what is copied.

    you might consider Goggling for more recent articles if you are not getting the results you want.

    Reply
  15. how the total ms project file with bar chart convert to excel

    Reply
  16. The Gantt chart graphic is only available in MS Project and does not export to Excel. You can export the data in the columns but not the graph.

    Reply

Leave a Reply

Your email address will not be published.

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

Please complete this equation so we know you’re not a robot. *

15 + = 21

Thanks for submitting your comment!
You must be logged in to comment.