Exporting to Microsoft Excel in Project 2010
Synopsis: Sometimes you need to process project information in a way that Project 2010 itself does not support. In this article, we look at the facilities in exporting project data to Microsoft Excel.
Project 2010 includes flexible facilities for both the import and export of data. Wizard features are provided to assist with both of these. They use import and export maps to both make the process easier and readily repeatable.
Here is a schedule for a building project with the Cost table shown.
Let’s see how to export the cost information to MS Excel.
First, note that there are important Excel options to check and potentially set if you are dealing with older (legacy) and non-default file types, such as those used in older versions of MS Excel.
Click on File to enter Backstage View. Select Options. Then, select Trust Center.
Click the Trust Center Settings… button and then select Legacy Formats.
You can select for first option if you do not intend to use older or non-default formats such as CSV or Excel 97-2003. Alternatively select one of the other 2 options. Choosing the last option means that you will not be prompted to confirm the use of an older or non-default format. I’m staying with the Prompt option. Save your choice by clicking OK.
Now, let’s do the export. Let’s choose Excel 97-2003 format. If you’re not already in Backstage View, click File to get there. Then click Save As. Click the dropdown next to Save As Type….
Select Excel 97-2003 Workbook (*.xls) as the type. Leave the File Name at its default value. The dialog now looks like this.
Click Save.
Because I left the option above at Prompt, I see this warning.
Click Yes and continue. The first screen of the Export Wizard appears.
Click Next.
The first choice is whether to export all of the data in the project file, or just selected items. On this occasion we’ll export it all, so let’s choose Project Excel Template. Make that selection, then click Finish.
The resulting Excel workbook looks like this.
Note that there are 3 tabs in the workbook. The first lists the tasks, the second the resources and the third the assignments. I can now work on all of this data in MS Excel in whatever way I need to.
Now, let’s see what happens if we make a different selection in the wizard. This time, we’ll choose Selected Data. Click Next.
To only export selected data MS Project needs a map of the data and how it will be exported. It’s possible to use an existing map or to create a new one. Let’s use an existing map. Make that selection and then click Next.
There are several standard maps to choose from. Select Cost data by task and then click Finish. Here is the resulting workbook.
There is a single worksheet with the name Task_Costs. This contains the information that is held in the Cost table in Project 2010.
Making a different selection of existing template would result in different data being exported to the Excel workbook.
If you select New map instead of Use existing map above, not only can you choose whether to export task data, resource data or assignment data, but you can also choose which specific fields to export and how to name them in the resulting Excel workbook.
Simon Calder
Chris “Simon” Calder was working as a Project Manager in IT for one of Los Angeles’ most prestigious cultural institutions, LACMA.He taught himself to use Microsoft Project from a giant textbook and hated every moment of it. Online learning was in its infancy then, but he spotted an opportunity and made an online MS Project course - the rest, as they say, is history!
