I often find it necessary to output sections of Project Schedules in a format that can be opened and edited by team members who are not comfortable with or do not have access to Microsoft Project. In most organizations, Microsoft Excel is a perfect solution, and Microsoft has conveniently included the ability to save as excel workbook from Project. However, the formatting of the export file is lost, with the most problematic aspect being the loss of indentation level of all tasks, masking the task groups and relationships. There is a simple solution. In about 5 minutes you can create a macro which will correct the indentation and greatly increase the legibility of the exported schedule.

Exporting from Microsoft Project to Excel

1. In the Main Menu go to File -> Save As and select "Microsoft Excel Workbook" as the Type
2. In the Wizard, select "Project Excel Template" and select Finish to save the file

Recording a Macro to correct the Formatting

  1. Open the Excel file and go to Tools -> Macro -> Record New Macro
  2. Provide a name for the macro (no spaces) and store in "Personal Macro Workbook"
  3. Highlight the "Name" Column and insert a column
  4. In the first data cell of this new column, enter the following formula
    =REPT(" ",(H2-1))&C2
  5. Duplicate this forumla to all rows by dragging on the bottom right corner of the cell
  6. Hide the original "Name" column, and rename this new column as "Name"
  7. Make any other desired formatting changes and end the process, Tools > Macro > Stop Recording

You can run this macro in the future by going to Tools -> Macro -> Macros and selecting the macro from your personal.xls.