Importing and Exporting with Excel | Microsoft Office Project 2003 Inside Out

You can import information from Excel into Microsoft Project. You can also export information from Microsoft Project into Excel. When you import information, you're bringing information in a foreign file format (for example, the Excel .xls file format) into the current application (for example, Microsoft Project, which uses the .mpp file format). When you export information, you're saving information in the current application in a different file format, so that it can be easily opened by another application. In both cases, the information will look as if it were created originally in the target application.

Importing from Excel to Microsoft Project

Using two templates specifically designed for integration, importing task, resource, and even assignment information from Excel to Microsoft Project is a breeze . With these templates, the most commonly used fields are present and the fields are already recognized and mapped in Microsoft Project. All you have to do is open the Excel file and make a couple of choices, and the data is imported into your project plan.

One template is designed just for task information being imported into the default Entry table of the Gantt Chart. Another template allows for more detailed project information, including resources and assignments.

If a team member or other stakeholder has created project information in Excel without using one of these templates, never fear. You can still import an Excel workbook into Microsoft Project the "old way."

Importing a Project Task List from Excel

You can make your project plan more accurate and inclusive by integrating the suggestions and concrete input of team members and stakeholders into the project plan. Specifically, you can have others on the team create a task list from an Excel workbook and quickly incorporate it into Microsoft Project. Using the Microsoft Project Task List template in Excel, introduced in Project 2002, this process becomes seamless.

For example, suppose that the marketing department is suggesting an addition to the project plan that will provide more detail for their test marketing efforts. They want to develop a list of detailed tasks in Excel. You have them use the Microsoft Project Task List Template in Excel, as follows :

In Microsoft Excel, click File, New.
In the New Workbook task pane, click General Templates.
In the Templates dialog box, click the Spreadsheet Solutions tab.
Click Microsoft Project Task List Import Template and then click OK.

A new file is created that contains columns that correspond to the default Gantt Chart in Microsoft Project.
Enter the task information in the Task_Table sheet (see Figure 17-17).

Figure 17-17: Share the Excel Task List template with your team to help build your project plan.

The columns in this Excel template are specifically designed for integration with Microsoft Project. For example, the ID, Duration, Start, and Deadline fields are all set up to flow in to the Microsoft Project fields in the correct format and data type.

Also, the second worksheet, labeled either "Info_Table" or "Microsoft Project," contains a brief explanation of how Microsoft Project can use and augment the template.
When you finish entering task information, click Save on the Standard toolbar and give the file a name .

Make sure that you're saving the new task list as an Excel workbook (*.xls), not overwriting the template (*.xlt).
Close the file.

Troubleshooting: You can't find the Excel Task List template

There might be two reasons why the Task List template is not available in the Templates dialog box.

You might be working with Microsoft Excel 2000 or earlier. For these versions, the template is stored in a different location. In Excel, click File, New. Click the 1033 or Spreadsheet solutions tab. Double-click Microsoft Project Task List Import Template.

If you still can't find it, click File, Open. Browse to the Office template directory, which is typically \Program Files\Microsoft Office\Templates\1033. In the Files Of Type list, click Templates. Double-click the Tasklist.xlt file.

The other reason why you might not see the Task List template is that the computer you're working on might not have Microsoft Project installed on it. The Task List template is installed with Microsoft Project. If you have Microsoft Excel installed on the same computer as Microsoft Project, those templates then become available in Excel.

On a computer running Microsoft Project and Excel, use Windows Explorer to find the template file. Its typical location is \Program Files\Microsoft Office\Templates\1033. Copy the file to a disk or network location so you can make it available for use on other computers. This is particularly useful when you want to provide the template to various team members and other stakeholders to use Excel to build parts of the project plan.

At this point, the new Excel task list is ready to be imported into Microsoft Project, as follows:

In Microsoft Project, open the project plan into which you want to import the Excel task list.
On the Standard toolbar, click Open.
In the Open dialog box, browse to the location on your computer or network where the Excel task list is saved.
In the Files Of Type list, click Microsoft Excel Workbooks (*.xls).

The task list workbook appears in the list of folders and files.
Click the task list workbook and then click Open.

The Import Wizard appears.
On the Welcome page of the Import Wizard, click Next.
On the Data Type page, click Project Excel Template and then click Next.
On the Import Mode page, specify whether you want to import the file as a new project, append the tasks to the currently active project, or merge the data into the active project. Click Finish.

The tasks are imported into Microsoft Project as you specified (see Figure 17-18).

Figure 17-18: Task information from the Excel template is imported into Microsoft Project.

If you scroll to the right, you see that the resources have all been placed in the proper column, but the Predecessors column is empty. The imported tasks still need to be organized, outlined, and linked.

Cross-References

For more information about inserting and organizing tasks, see Chapter 3, "Starting a New Project."

Inside Out: Remove the SNET constraints

All tasks imported from the Excel task list template come in to the project plan with a Start No Earlier Than (SNET) constraint. If a date was entered in the workbook's Start field, that becomes the SNET constraint date. If no date was entered, the SNET date is today's date.

Be sure to review those imported start dates. If there are any dates entered besides today's date, the originator might have intended a SNET constraint. Check with the originator. Because the column is included in the template, originators might be led to believe they're supposed to enter a start date, not realizing that Microsoft Project figures it out for them.

To provide for the best scheduling flexibility, you need to change those imposed (and arbitrary) date constraints to the most flexible As Soon As Possible constraint. To change the constraints all at once, do the following:

In the Gantt Chart, select the names of all tasks with date constraints.

You can select the Start column if you're changing all tasks in the project to an ASAP constraint.

If you want to change only certain tasks, select their row headings while holding the Ctrl key.
On the Standard toolbar, click Task Information.
In the Multiple Task Information dialog box, click the Advanced tab. In the Constraint Type box, click As Soon As Possible.

When asking others to enter task information using the Excel Task List template, ask them to enter any important finish dates in the Deadline column and any important start dates in the Notes column. This way, you can change all the SNET constraints to ASAP without having to examine every imported data to see whether it's a real date constraint.

Cross-References

For more information about setting constraints, see "Scheduling Tasks to Achieve Specific Dates".

Importing Detailed Project Information from Excel

If certain team members or other project stakeholders are doing more than just building a task list in Excel, have them use the Microsoft Project Import Export Template. This template can be used in Excel to build a project with tasks, resources, and assignments. Then you have a properly formatted worksheet for many of the essential elements of a plan for importing into Microsoft Project.

The standard Excel importing process involves mapping the Excel columns to the corresponding Microsoft Project columns to ensure that the right information ends up in the right locations in your Resource Sheet. The Microsoft Project Plan Import Export Template is set up to enter more detailed resource information in the format needed by Microsoft Project. To use this template, make sure that Excel is installed on the same computer as Microsoft Project and then follow these steps:

In Microsoft Excel, click File, New.
In the New Workbook task pane, click General Templates.
In the Templates dialog box, click the Spreadsheet Solutions tab.

Click Microsoft Project Plan Import Export Template and then click OK.

Note

If the Template is not in the Templates dialog box (which might be the case if you're working with Excel 2000 or earlier), close the dialog box and then click File, Open. Browse to the Office template directory, which is typically \Program Files\Microsoft Office\Templates\1033. In the Files Of Type list, click Templates. Double-click the Projplan.xlt file.

The template creates a new file with columns that correspond to many commonly used fields in Microsoft Project. There is one worksheet each for tasks, resources, and assignments. A fourth worksheet, labeled Info_Table, provides general information on how to use the template.

The data fields are set up so that when you fill in the worksheets and export them into Microsoft Project, you don't need to map your data. If you decide to add data in extra columns (fields), Microsoft Project will map that data to an appropriate field in the Microsoft Project data tables for you.

At the bottom of the workbook window, click the tab for the worksheet you want to use.
Enter the data in the fields you want. You don't have to use all the fields in all the sheets; just the ones you need (see Figure 17-19).

Figure 17-19: Use the Microsoft Project Plan Import Export Template to develop task, resource, and assignment information in Excel.
When finished, click Save on the Standard toolbar, and give the file a name.

Make sure you're saving the new file as an Excel Workbook (*.xls), rather than overwriting the template (*.xlt).
Close the file.

This workbook will become the source file when you import the data into Microsoft Project.

To import the information from this new Excel workbook into Microsoft Project, do the following:

On the Standard toolbar in Microsoft Project, click Open.
Go to the location on your computer or network where the Excel workbook is saved.
In the Files Of Type list, click Microsoft Excel Workbooks (*.xls).

The workbook appears in the list of folders and files.
Click the workbook file and then click Open.

The Import Wizard appears.
Read the Welcome page and then click Next.
On the Data Type page, click Project Excel Template and then click Next.
On the Import Mode page, specify whether you want to import the file as a new project, append the resources to the currently active project, or merge the data into the active project.
Click the Finish button.

The new project opens with all the data populated from the template (see Figure 17-20)

Figure 17-20: Using the template, you can import the project information from Excel to Microsoft Project without having to map individual fields.

Importing from Excel without a Template

If team members or other project stakeholders created an Excel workbook containing project information before they knew of the existence of the templates, you can still import Excel worksheets.

To do this, follow these steps:

On the Standard toolbar in Microsoft Project, click Open.
Go to the location on your computer or network where the Excel workbook is saved.
In the Files Of Type list, click Microsoft Excel Workbooks (*.xls).

The workbook appears in the list of folders and files.
Click the workbook file and then click Open.

The Import Wizard appears.
Read the Welcome page and then click Next.
On the Map page, select New Map and then click Next.
On the Import Mode page, specify whether you want to import the file as a new project, append the resources to the currently active project, or merge the data into the active project. Click Next.
On the Map Options page, select the types of data you want to import. You can select multiple types if you need to. If your workbook file includes column headings, select the Import Includes Headers check box. Click Next.
On the Mapping page, under Source Worksheet Name, select the sheet that contains the data you're importing, even if it's just Sheet1 .

The fields from the Excel sheet appear in the From: Excel Field column. If there is an obvious match to a Microsoft Project field, it appears in the To: Microsoft Office Project Field column. If Microsoft Project could not figure out a match, the field indicates (not mapped) .
For any fields that are not mapped, click in the box and select the Microsoft Project field in which you want to store the corresponding imported field. Scroll down through the entire table and make sure that all the Excel fields you want to import are mapped to a Microsoft Project field.

The Preview area shows a sample of how your table data is mapped (see Figure 17-21).

Figure 17-21: Use the Mapping page to specify the Microsoft Project fields that are to contain each category of incoming Excel data.
When finished mapping, click Next.

If you selected multiple types of data to import on the Map Options page (for example, Tasks and Resources), another Mapping page will appear. You can potentially work through a Task Mapping, Resource Mapping, and Assignment Mapping page.
On the End Of Map Definition page, specify whether you want to save the map for future use. Then click Finish.

The project opens with all the data imported from the workbook in the fields you specified.

Exporting from Project to Excel

Suppose that your company's accounting department wants to analyze your project cost information in conjunction with those of other projects taking place throughout the company. The department uses Excel to analyze project cost data, so you'll need to export your Microsoft Project information to an Excel workbook.

When exporting from Microsoft Project, you use the Export Wizard introduced in Project 2002. With the Export Wizard, you can export information you select or export key information from the entire project.

Exporting Selected Data to Excel

To export only information you choose, follow these steps:

In Microsoft Project, open the project plan that contains the project information you want to export.
Click File, Save As.

The Save As dialog box appears.
Browse to the drive and folder where you want to save your information.
In the Save As File Type list, click Microsoft Excel Workbook (*.xls).
In the File Name box, enter a name for your new Excel file.

By default, the project file name is adopted with the .xls extension.
Click Save.

The first page of the Export Wizard appears.
On the Welcome page of the wizard, click Next.
On the Data page, be sure that the Selected Data option is selected and then click Next.
On the Map page, be sure that the New Map option is selected and then click Next.
On the Map Options page, select the check box for each type of data you want to map and export: Tasks, Resources, and Assignments. Under Microsoft Excel Options, select the Export Includes Headers check box. Click Next.
On the Mapping page of the wizard, in the Destination Worksheet Name box, you can enter a name for the target worksheet within the workbook you're creating.

You can also just use the default name provided; for example, Task_Table1.
In the Export Filter box, click any filter you want to apply to the tasks or resources you're exporting.
In the table, specify how each field of data should be defined when it is exported to Excel.

You can change the column heading for a given field, for example, from Duration to Task Length . The Data Type field shows how the field will be exported, for example, as text, currency, date, and so on.

Use the command buttons below the table to help with the process. If you're not interested in editing each field individually, click the Base On Table button. The Select Base Table For Field Mapping dialog box appears (see Figure 17-22).

Figure 17-22: Select the Project table that contains the fields you want to use as your export data source.

Click the Microsoft Project table you're exporting and then click OK. The fields that define that table appear in the Mapping table.

A preview of the data is shown as it will appear in Excel in the Preview area at the bottom of the Mapping page. Use the scroll bar to view all the columns. When ready, click Next.
On the End Of Map Definition page, click the Save Map button if you expect to export this same information again. Otherwise, click Finish.

Your specified project data is exported and saved as a complete Excel workbook in the exact layout you defined.

To open and review your exported project data in Microsoft Excel, follow these steps:

In Microsoft Excel, open the Excel file that you created by exporting project data.

Your project data appears in the workbook, using the tasks (or resources or assignments), filter, and table you selected in the export process (see Figure 17-23).

Figure 17-23: View and manipulate your project data in Microsoft Excel.
Resize and reformat the date fields, as needed.

Note the Smart Tags on some cells and the formatting of durations such as "53.d."
Click File, Save.

A message appears, indicating that the file is in the Excel 5.0/95 format. To ensure compatibility with older versions of Excel, Microsoft Project exports to the Microsoft Excel 5.0/Excel 95 file format. When you open the new Excel file and save it the first time, Excel prompts you to update the format to the current version you have installed on your computer, which might be Microsoft Excel 2000 or Excel XP, for example.
Click Yes to save your Excel file in the latest Excel format.

Updating the format ensures that you can use the latest features of your current Excel version on your exported project data.

Exporting Complete Project Data to Excel

You can export more complete information about tasks, resources, and assignments. With this method, Microsoft Project organizes data into Task, Resource, and Assignment data types, which in turn are presented in their own separate worksheets in the single Excel workbook. To create a complete Excel workbook of project information from your project file, follow these steps:

In Microsoft Project, open the project whose information you want to export to Excel.
Click File, Save As.
Browse to the drive and folder where you want to save your information.
In the Save As File Type list, click Microsoft Excel Workbook (*.xls).
In the File Name box, enter a name for the Excel file and then click Save.
On the Export Wizard Welcome page, click Next.
On the Data page, click Project Excel Template and then click Finish.

Your data is saved as a complete Excel workbook.

To open and review your exported project data in Microsoft Excel, follow these steps:

In Microsoft Excel, open the Excel file.

The workbook is created with four worksheets of discrete information: Task_Table, Resource_Table, and Assignment_Table. Info_Table provides general instructions for using this workbook (see Figure 17-24).

Figure 17-24: By exporting tasks, resources, and assignments to Excel, separate worksheets are created to hold key task, resource, and assignment information from the selected project.
Resize and reformat the date fields, as necessary.
Click File, Save.

A message appears, indicating that the file is in the Excel 5.0/95 format (see Figure 17-25). To ensure compatibility with older versions of Excel, Microsoft Project exports to the Microsoft Excel 5.0/Excel 95 file format. When you open the new Excel file and save it the first time, Excel prompts you to update the format to the current version you have installed on your computer, which might be Microsoft Excel 2000 or Excel XP, for example.

Figure 17-25: Click Yes to upgrade the exported project information to your current installed version of Excel.
Click Yes to save your Excel file in the Excel format you have installed on your computer.

Updating the format ensures that you can use the latest features of your current Excel version on your exported project data.

Exporting Timephased Information to Excel

New Feature In previous versions of Microsoft Project, if you wanted to work with timephased task, resource, or assignment information in another application, you had to copy and paste it. However, in Microsoft Project 2003, there's now a method for exporting timephased information to Microsoft Excel. To do this, follow these steps:

In Microsoft Project, open the project plan whose timephased data you want to export to Excel.
Display a task view if you want to export timephased task information. Display a resource view to export timephased resource information.
If you want to export data only for selected tasks or resources, display and select those items.

Use the Ctrl or Shift keys to select multiple tasks or resources.
Click View, Toolbars, Analysis to display the Analysis toolbar.
On the Analysis toolbar, click Analyze Timescaled Data In Excel.
In the first page of the Analyze Timescaled Data Wizard, select the Entire Project or Currently Selected Tasks (or Currently Selected Resources) to specify which set of timephased data is to be exported. Click Next.

The second page of the wizard appears, showing the list of all available timephased fields for either tasks or resources, depending on whether you're showing a task or resource view.
Under Available Fields, select the timephased fields you want to export; for example, Actual Cost, Baseline Cost, and Cost. Click Add to move the selected fields to the Fields To Export box.

Use the Ctrl or Shift keys to select multiple fields at one time. You can select fields and click Add as many times as you need to.
When finished adding fields to the Fields To Export box, click Next.
In the third page of the wizard, enter the date range and time unit you want to use for the exported data. Then click Next.

When choosing your date range and time unit, keep in mind that Excel can display only 256 columns of data.
In the fourth page of the wizard, specify whether you want the timescaled data to be graphed in Excel. Then click Next.
In the fifth page of the wizard, click the Export Data button.

Microsoft Excel starts up, and the selected timephased data is exported. This might take several minutes, depending on the amount of data to be exported and the speed of your computer. This export works with Microsoft Excel version 5.0 or later.

If the data you chose results in more than 256 columns of timephased data, an error message appears. Excel can only display up to 256 columns of data. To fit your data into fewer columns, you can specify a shorter date range or a different timescale ; for example, weeks instead of days. Or you can simply have the export process cut off the data at 256 columns.
Review your timephased data in Excel.

When the export is complete, Excel appears and shows your data. If you chose to chart the data, the Timescaled Data Graph appears first.

Click the Timescaled Data worksheet tab at the bottom of the Excel workbook to review the data in an Excel worksheet.

The timescaled data in Excel is initially exported from Microsoft Project as a text (.txt) file and saved in a temporary folder. To retain this timephased information in the Excel format, follow these steps:

In Excel, click File, Save As.
In the Save As Type box, click Microsoft Excel Workbook (*.xls).
In the File Name box, enter a name for the new file. Its default name is TimeData.xls .
Use the Save In box and Up One Level button to browse to the drive and folder where you want to save the new workbook.
Click Save.