Extracting data from Microsoft Project 2010 to Microsoft Excel

I am a project manager by profession and I have to use Microsoft Project 2010 most of the time. After I schedule the tasks, I need to send the data to my colleagues and co-workers. But many times they don’t have Microsoft Project. I wanted to know if there is a way I can transfer the data in other softwares, most preferably it would be Microsoft Excel. Can anybody help me with it?

To successfully transfer data in MS Excel you have to:

1) Click on file, and go into Save As
2) Under Save as Type, select Web Page
3) Click on Save which will start an Export Wizard.
4) In this Wizard, select Existing Map.
5) Now click on ‘Export to HTML using standard template’.
6) Click on next and then it will be converted.
7) Find the file and change its extension to .xls.

Now the file will open in MS Excel. But this method won’t work in versions before Microsoft Project 2007.

One way is to try copy pasting the data from MS Project to MS Excel. This is easy, but most of the time it fails due to different outline structure. You can also try this website which converts the MS project file into Excel file or in other formats specified. It is efficient as the structure of outline, font, and data is maintained. But it only works for predefined fields. Custom fields get deleted.

As mentioned in the second post, the default wizard is best for this purpose. It lets you to decide which fields are to be extracted and which to be not. MS Excel 2003 & 2007 had a tool by name ‘Analyze Timescale’. Using it you can import data from MS project into MS Excel. I haven’t used MS office 2010, so I can’t tell you if this tool is compatible with MS Project 2010. But again in very rare cases, it might not retain outline structure.

A feature was introduce in MS Project 2007 which also is present in MS Project 2010 called as ‘Visual Reports’. It is found under reports tab. With this tool you can export your data in form of reports in MS Excel. It provides many templates for creating reports. It can be easily managed in Excel using Pivot Table as well.

Another way of exporting data from MS Project to MS Excel is to create a Project Web App file of your data and load it on the Project Server. Once done, it can be imported into MS Excel. It maintains the structure of outlines and data. It is an easy method but requires MS Project Server to be installed on the system. MS Project Server lacks format for timephased data. Sometimes importing data in Excel might be requiring creating new fields.

You can use macros in Excel to efficiently import data without and changes in structure. It will require a good knowledge of macros as it involves a process of creating a bridge between two softwares.