Sending Project data to Excel

Vertex42 The Excel Nexus

Professional Excel Templates

Get Instant Access

Using the Import/Export Wizard, you can easily send information to Excel. The wizard gives you the choice of creating a new map or using an existing map. Follow these steps to start the process of sending information to Excel:

1. Open the Project file that contains the information that you want to export.

2. Choose FileOSave As to open the Save As dialog box.

3. Type a name in the File name list box for the file that you want to export.


4. Open the Save as type list box, and select Microsoft Excel Workbook or Microsoft Excel PivotTable.

When you create an Excel PivotTable file. Project creates two sheets in the workbook for each type of data that you export. One sheet contains the data that is used in the PivotTable, and the other sheet contains the PivotTable. Project uses the last field in each map as the default field for the PivotTable, and all the other fields appear as rows in the PivotTable.


5. Click Save. Project starts the Export Wizard. Click Next.

6. Choose Selected Data, and click Next.

You can choose Project Excel Template to export the entire Project file to Excel.

7. Choose New map or Use existing map.

If you choose Use existing map, you see the Map Selection dialog box, as shown in Figure 26-1.



If you choose Use existing map, you see the Map Selection dialog box, as shown in Figure 26-1.

Figure 26-1: Use this dialog box to select the map that you want to use to export your data.

If you choose New map, you don't see the Map Selection dialog box. Instead, when you click Next, you see the Map Options dialog box.

8. Select a map, and click Next. The Export Wizard displays the Map Options dialog box.

9. Select the type of data to export, as shown in Figure 26-2. The boxes that you select determine which wizard screens will subsequently appear when you click Next.

The map you selected in Step 8 contains a predefined collection of data. The boxes you check in Step 9 identify the type of data to export within the predefined collection.

Tip If you want your Excel workbook to contain assignments that are listed under tasks or resources, similar to the Task Usage or Resource Usage views, select the Include * assignment rows in output check box.

Figure 26-2: Select the type of data to export in the Map Options dialog box.

10. When you click Next, you see one of the mapping dialog boxes. In this example, I selected Tasks in the Map Options dialog box. The Task Mapping dialog box appears, as shown in Figure 26-3, and includes the following functions:

Export Wizard - Task Mapping

Map Tasks Data Destination worksheet name: I Task_Tatíel

Export filter: I All Tasks

verify or edit Project's assumptions fc* how you want to map the data,



Microsoft Office Prolect

eld To: Excel Field

Data Type

% Wer

k Corrçfete

-Percent Work Comptete




Actual Finish




•Actual Work


i -i

Hove rtftjf fill f [rehtt Row I Refete Row [ Ba¿e on Table.., [

Hove rtjd a rtftjf fill f [rehtt Row I Refete Row [ Ba¿e on Table.., [

MS Project:

% Work Complete

Actual Finish

Actual Work


5ercent Work Con

Actuad Finish

Actual Work







10 hrs




Kl i


Figure 26-3: As you add fields in the Task Mapping dialog box, a preview of the Excel worksheet that you're creating appears at the bottom of the Task Mapping dialog box.

• Destination worksheet name: This box contains the name that Excel will assign to the sheet in the workbook. You can change this name.

• Export filter: Use this list box to select the tasks that you want to export. By default, Project assumes that you want to export all tasks, but you can export, for example, only completed tasks.

• From: Microsoft Office Project Field: Under this column, click (Click here to map a field) or any blank cell to add fields to export one at a time. After you click, you can use the list box arrow that appears to view a list of the fields that are available for exporting and to select a field.

• To: Excel Field: Select a field to export, and click the column next to the field that you added. Project suggests a column heading for the field in the Excel worksheet; you can change this heading.

• Data Type: You can't change the data type for the field in the destination program, which appears in this column.

• Add All: To quickly add all the fields in the Project file, click the Add All button.

You can export a maximum of 255 fields to Excel for each type of data. Project contains over 700 task fields and over 400 resource or assignment fields. So, if you choose Add All, you will have to delete some of the fields before Project will allow you to proceed to the next step in the wizard.

• Clear All: To remove all the fields that you added, click the Clear All button.

• Insert Row: If you decide to add a field between two existing fields, click the row that you want to appear below the new field. Then click the Insert Row button, and Project inserts a blank row above the selected row.

• Delete Row: To delete a field, click anywhere in the row that contains the field and click the Delete Row button.

• Base on Table: To add all the fields in a particular Project table, such as the Entry table or the Cost table, click the Base on Table button. Project displays the Select Base Table for Field Mapping dialog box, from which you can select a table. When you click OK, Project adds all fields that are contained in that table to the list of fields that you want to export.

• Move: You can use the Move buttons on the right side of the dialog box to reorder fields. Click the field that you want to move, and then click either the Move Up arrow or the Move Down arrow.

Note |f you selected the Resources and the Assignments check boxes as well as the

Tasks check box in the Map Options dialog box (shown previously in Figure 26-2), when you click Next, the Export Wizard displays additional boxes that are almost identical to the Task Mapping dialog box, but for each of these data types.

11. After you finish defining your map, you see the final box of the Export Wizard, as shown in Figure 26-4. If you elect to save your map by clicking the Save Map button, Project displays the Save Map dialog box, as shown in Figure 26-5. Provide a name for the new map in the Map name text box.

Export Wizard - End of Hap Definition


The Export Wizard tan all the hfcrmation t needs to firish exporting ycur data.

ack Finish to complete the operation.

Cr click ' jv'e Map f you wocfd lite to save these wzard settings as a map to use agfiir, In the tubure.

Figure 26-4: In the final dialog box of the Export Wizard, choose whether to save the map that you've defined.


Existing maps;

"Who Does What" report

Cost data by task

Defadt task information

Earned value information

Export to HTML ushg standard template

Resource "Export Tatfe" map

Task "Export Tabie" map

Task and resource RvotTable

Task list with embedded assignment rows

Top Levei Tasks list

ivjapname: l^1

Organizer,., | |



Figure 26-5: If you decide to save the map, provide a name for it in the Save Map dialog box, which displays a list of all existing maps.

Was this article helpful?

0 0
Project Management Made Easy

Project Management Made Easy

What you need to know about… Project Management Made Easy! Project management consists of more than just a large building project and can encompass small projects as well. No matter what the size of your project, you need to have some sort of project management. How you manage your project has everything to do with its outcome.

Get My Free Ebook

Post a comment