Suppose you want to move larger quantities of information between Project 2010 and Excel, or you don’t like having to jockey columns around to make sure data transfers to the right place. For these situations, you can import or export information from Project 2010 to Excel or from Excel to Project 2010. Importing information means bringing information from a foreign file format into the current application (for example, from the Excel XLSX file format to Project 2010, 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 opened by another application. In both cases, the information will look as though it were originally created in the destination application.
With two templates specifically designed for integration, Excel gives you a head start on importing information for tasks, resources, and even assignments from Excel to Project 2010. These templates include commonly used fields, and the fields are already mapped to the corresponding fields in Project 2010. All you have to do is open the Excel file and make a few choices, and the data is imported into your project plan.
One template is designed for importing basic task information into a 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 any Excel workbook into Project 2010 the “old way.”
Building a project plan doesn’t have to be a solitary activity. For example, you might ask others on the project team to create task lists in Excel for their portion of a project, and then you can incorporate their work into Project 2010. The Microsoft Project Task List Import template in Excel, introduced back in Project 2002, simplifies this process.
The standard Project 2010 importing process involves mapping the Excel columns to the corresponding Project 2010 columns to ensure that the right information ends up in the right places in your Gantt Chart task sheet. The Microsoft Project Task List Import template has columns set up to work with the Project 2010 Import Wizard to handle that aspect of the process for you. You can specify a few basic fields for each task, including the task name, duration, start date, deadline date, and resources.
Note
Although this template includes a Start column, consider leaving the Start fields blank. That way, if you are importing the tasks into an automatically scheduled project, you avoid creating date constraints for the imported tasks. You can fill in the Deadline fields with dates to indicate when the tasks should finish.
Suppose that the marketing department suggests an addition to the project plan that clarifies their test marketing efforts, but they want to develop the list of detailed tasks in Excel. Have them use the Microsoft Project Task List Template in Excel, as follows:
In Excel 2010, on the File tab, click New.
In the Backstage view, under Available Templates, click Sample Templates.
Click the Microsoft Project Task List Import Template.
Click the Create button.
The template opens to the Info_Table worksheet, which explains how to use the template.
After you read the template information, click the Task_Table tab at the lower-left corner of the worksheet.
The worksheet appears with columns that map to several standard Project 2010 fields. For example, the ID, Name, Duration, Start, Deadline, Resource Names, and Notes fields are all set up to work with their corresponding Project 2010 fields.
Enter the task information in the Task_Table sheet, as shown in Figure 18-14.
In the template, the columns are labeled so you know where to enter each piece of information. The columns are also formatted to fit the type of data they are to contain. Be sure to enter data valid for the data type that’s expected in each column. For example, the Start cells are already set up to display values as dates, but you still must type valid dates if you’re importing the tasks into an automatically scheduled project. Likewise, for an automatically scheduled project, the Duration field must contain values that represent a length of time. You can type a value, such as 2d or 2w, to represent the duration, but typing words or a date will generate an error when you try to import the file. If you’re importing the task sheet into a manually scheduled project, however, you can enter whatever you like into the Start or Duration fields, or you can leave them blank.
Note
To learn how to remove dates constraints, see the sidebar, Inside Out: Remove the SNET constraints.
When you finish entering task information, on the File tab, click Save.
Because the file is a template, the Save As dialog box sets the Save As Type box to Excel Workbook.
In the Save As dialog box, in the File Name box, type the name of the file, and then click Save.
At this point, the new Excel task list is ready to be imported into Project 2010.
Note
To learn how to import data from an Excel file into Project 2010, see Importing from Excel.
Note
As an alternative, you can save this file and provide it to your team members to use to enter tasks and task information. The template is typically located on your computer in the Program FilesMicrosoft OfficeTemplates1033 folder and is named Tasklist.xlt. You can copy this template to a folder you use for custom templates and then e-mail the file to your team members. Have them use this file to enter their task information, save the file, and then send it to you when they’re finished.
To open the template in Excel 2007, click the Microsoft Office button, and then click New. In the New Workbook dialog box, under Templates, click Installed Templates. Double-click the Microsoft Project Task List Import Template.
In Microsoft Excel 2003 or earlier, click File, New. In the task pane, click the On My Computer link, click the Spreadsheet Solutions tab, and then double-click the Microsoft Project Task List Import Template.
If some team members or project stakeholders are doing more than just building a task list in Excel, have them use the Microsoft Project Plan Import Export Template. This template is set up to hold more detailed resource information in the format needed by Project 2010. It includes worksheets for building a project with tasks, resources, and assignments. Each worksheet contains several columns for specifying more detailed information, such as the outline level and predecessors for tasks, and the maximum units and standard rate for resources.
To use this template, make sure that Excel is installed on the same computer as Project 2010 and then follow these steps:
In Excel 2010, on the File tab, click New.
Below the Available Templates heading in the Backstage view, click Sample Templates.
Click Microsoft Project Plan Import Export Template, and then click Create.
If you don’t see the template, close the dialog box. Then, on the File tab, click Open. Browse to the Office template directory, which is typically Program FilesMicrosoft OfficeTemplates1033, and then double-click the Projplan.xlt file.
The template creates a new file with one worksheet each for tasks, resources, and assignments. A fourth worksheet, labeled Info_Table, provides general information on how to use the template.
Each worksheet contains columns that correspond to many commonly used fields in Project 2010. The data fields are set up so that when you fill in the worksheets and import them into Project 2010, Project 2010 can map your data without additional instructions from you. If you decide to include additional columns (fields) of data, Project 2010 maps that data to appropriate fields in the Project 2010 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 18-15.)
When you are finished, on the File tab, click Save and give the file a name.
Make sure you save the file as an Excel workbook and don’t overwrite the template.
Close the file.
This workbook is ready to import into Project 2010.
You can import data from Excel whether you use one of the two Excel templates that Project 2010 provides or you create your own Excel workbook to collect exactly the fields of project information you want to import into Project 2010. In either of these situations, you use the Import Wizard to specify how to import the data into a Project 2010 file and how to map the columns in the workbook to fields in Project 2010.
The standard Microsoft Project import process involves mapping the columns in an Excel worksheet to the corresponding Project 2010 fields to ensure that the right information ends up in the right places in Project 2010. For example, you can set up an Excel workbook with worksheets to collect information about tasks, resources, and assignments. Then, you can import that information to create tasks and resources in a Project 2010 file, link the imported tasks, and then assign resources to them. To import data from an Excel workbook, follow these steps:
If you want to import information from Excel into an existing Project 2010 file, first open that Project file.
Then, to open the Excel file, in Project 2010, on the File tab, click Open.
In the Open dialog box, click the arrow on the button that indicates the current file type, (Microsoft Project Files by default), and then click Excel Workbook.
If the task list was created using Excel 2003 or earlier, change the file type being shown. To the right of the File Name box, click the arrow in the button labeled Excel Workbook, and then click Excel 97-2003 Workbook in the drop-down list.
Caution
If you don’t see Excel workbooks that you’ve created, the problem might be that they are saved in an earlier Excel format, such as the Excel 97-2003 Workbook format. Project 2010 sets a security option by default that keeps Project 2010 from opening or saving files created in Excel 97-2003 format. In Project 2010, on the File tab, click Options. In the Options dialog box, click Trust Center, and then click the Trust Center Settings button. In the Trust Center dialog box, click Legacy Formats. Select the Allow Loading Files With Legacy Or Non-Default File Formats option.
Browse to the folder where the Excel workbook is saved.
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, keep the New Map option selected, and then click Next.
If you are a veteran data importer, you might have maps that you created in the past. If the Excel workbook you want to import conforms to the settings in an existing map, select Use Existing Map, and then click Next. On the Map Selection page, select the name of the map you want to use, 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. (See Figure 18-16.)
If you built an entire task list in the Excel template, select As A New Project. To add new tasks to the end of the current file, select Append The Data To The Active Project. If you plan to insert the imported tasks somewhere within the current Project 2010 task list, select Merge The Data Into The Active Project.
On the Map Options page, select the check boxes (Tasks, Resources, and Assignments) for each type of data you want to import. If your workbook file includes column headings, select the Import Includes Headers check box, and then click Next.
On the Mapping page, under Source Worksheet Name, select the sheet that contains the first type of data you’re importing, even if it’s just Sheet1.
The fields from the selected Excel sheet appear in the From: Excel Field column. If there is an obvious match to a Project 2010 field, it appears in the To: Microsoft Project Field column. If Project 2010 cannot identify a matching field, the field name appears as (Not Mapped).
For any fields that are not mapped, click the box in the To: Microsoft Project Field column and select the Project 2010 field in which you want to store the corresponding imported field. Scroll through the entire table and make sure that all the Excel fields you want to import are mapped to a Project 2010 field.
The Preview area shows a sample of how your table data is mapped, as shown in Figure 18-17.
If you selected multiple types of data to import on the Map Options page (for example, tasks and resources), click Next to proceed to the Mapping page for the next data type. Repeat steps 10 and 11 for that data type.
You can potentially work through a Task Mapping, Resource Mapping, and Assignment Mapping page.
When you are finished mapping fields, click Next.
On the End Of Map Definition page, click Save Map if you want to save the map for future use.
In the Save Map dialog box, in the Map Name box, type a name for the map, and then click Save.
If you selected the option to import the data into a new project, Project 2010 creates a new project file and imports the data from the workbook into the fields you specified. Otherwise, Project 2010 imports the data into the active project into the fields you specified.
Variation on an Import Theme
Another way to import Excel information into a new Project 2010 file is to use the New From Excel Workbook function, as follows:
On the File tab, click New.
Under New From Existing, click New From Excel Workbook.
In the Open dialog box, browse to the folder that contains the Excel file you want to use as the basis for your new project plan.
If the workbook you want to use was created in Excel 2003 or earlier, click the arrow in the file type drop-down list (currently showing Excel Workbook), and then click Excel 97-2003 Workbook.
Double-click the workbook name.
The Project Import Wizard opens. Follow the same steps as listed earlier to work through the wizard.
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 need to export your Project 2010 information to an Excel workbook.
With the Export Wizard, you can export information you select or export key information from the entire project.
Inside Out: Remove the SNET constraints
If new tasks are set to Auto Scheduled, tasks imported with start dates from an Excel file come in to the Project 2010 file with Start No Earlier Than (SNET) constraints. If the Excel workbook’s Start field contains a date, 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 any dates besides today’s date are entered, check with the person who provided the task list to see if the SNET constraint was intentional. Unless you explain to the people who use an Excel workbook with a Start Date column that Project 2010 figures out start dates for them, they might think they’re supposed to enter a start date.
To make a schedule flexible so that Project 2010 can recalculate dates in response to inevitable changes, 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, click their row headings while holding down the Ctrl key.
On the Task tab, in the Properties group, click Information.
In the Multiple Task Information dialog box, click the Advanced tab. In the Constraint Type box, click As Soon As Possible.
When you ask others to enter task information, ask them to enter any important finish dates in another column, such as the Deadline column or the Notes column if they use the Microsoft Project Task List Import Template. By doing this, you can change all the SNET constraints to ASAP without having to examine every imported date to see whether it’s a real date constraint.
Note
For more information about setting constraints, see Scheduling Tasks to Achieve Specific Dates.
Although you can export most information types from Project 2010 to Excel, it’s most useful to export numeric data for further analysis in Excel. Numeric data is any data that can be used in calculations and mathematical operations, such as cost and work data. Work data (such as hours) can be converted to numeric fields, but it is stored in Project 2010 as text because of the unit labels such as hours, days, or weeks. On the other hand, dates are not considered numeric data, even though they consist mostly of numbers.
When preparing to export information from Project 2010 to Excel for numerical analysis, first decide which tasks, resources, and fields you want to export to Excel. You can export the data for your entire task list or resource list or the data for selected or filtered tasks or resources. You can export all the fields in a given table—such as the Earned Value, Cost, or Tracking table—or you can export just the fields that you select. You can also export a large representation of all your project data with the different types of project, task, resource, and assignment data appearing in different worksheets within an Excel workbook.
To specify the project data you want to export and then send it to Excel, follow these steps:
In Project 2010, open the project plan that contains the project information you want to export.
Apply the view that contains the task, resource, or assignment information you want to export.
The table or fields applied to the view do not matter at this point; as part of the export process you choose the fields you want.
If you want to export only certain tasks, resources, or assignments, select their rows by using the Shift or Ctrl keys. If you want to export all tasks, all resources, or all assignments, click the Select All cell in the upper-left cell in the view, above the ID number.
Later, as part of the export process, you can apply a task or resource filter if you want.
On the File tab, click 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 Type list, click Excel Workbook.
By default, Project 2010 fills in the File Name box with the name of the active project file.
In the File Name box, enter a name for your new Excel file, and then click Save.
The Export Wizard appears.
On the Welcome page of the wizard, click Next.
On the Export Wizard—Data page, be sure that the Selected Data option is selected, and then click Next.
On the Export Wizard—Map page, be sure that the New Map option is selected, and then click Next.
On the Export Wizard—Map Options page, select the check box for each type of data you want to map and export: Tasks, Resources, or Assignments. Under Microsoft Excel Options, be sure that the Export Includes Headers check box is selected (see Figure 18-18), and then click Next.
On the Mapping page of the wizard, in the Destination Worksheet Name box, you can enter a name for the destination 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.
If a Project 2010 table—for example, the Entry table, the Cost table, or the Earned Value table—includes most or all of the fields you want to export, click the Base On Table button.
The Select Base Table For Field Mapping dialog box appears, as shown in Figure 18-19.
Figure 18-19. Select the Project 2010 table that contains the fields you want to use as your export data source.
Click the Project 2010 table you want to export (for example, Earned Value or Cost), and then click OK. The fields that define that table appear in the data mapping grid under Verify Or Edit How You Want To Import The Data.
To modify the fields to export or to create a list of fields from scratch, add and remove fields in the data mapping grid one at a time, as well.
If you want to export just numeric project data, you can use this table to select only those fields containing numeric values. For example, you might select Name, Cost, and Duration. If you are building a data map from scratch rather than starting with a table, be sure to start with the Name field to include the task name, or the Resource Names field to make sure the resource name is exported.
To add a field, click in a blank row in the From: Microsoft Project Field column, and then click the arrow that appears. Select the field you want from the list that appears. (See Figure 18-20.) Depending on whether you are mapping task data, resource data, or assignment data, the drop-down list displays task, resource, or assignment fields. Type the first two or three characters of the field name to quickly move to it in the list. Press Enter or Tab to select the field. As soon as you enter a field in the From column, its default equivalent appears in the To: Excel Field column, showing the name of the field as it will appear in the Excel workbook column heading. If you want to change the Excel column heading, type the new heading here (for example, from Baseline Cost to Planned Cost).
To remove a field from the list, click the field name, and then click Delete Row.
In the table, specify how each field of data should be defined when it is exported to Excel.
The Data Type column shows the data type of the field. Incidentally, this data comes in as Text fields, even though it might really be number, date, or cost fields in Project 2010. To simplify the export process, all fields are changed to text fields. You can then easily convert the data type for any of these fields in Excel, which is especially important for numeric data on which you want to run calculations.
Use the Move Up and Move Down buttons to the right of the table to rearrange the order of the fields, which represents the order in which the columns will appear in Excel.
In the Preview area at the bottom of the Mapping page, a preview of the data shows how it will appear in Excel. Use the scroll bar to view all the columns.
When the map is the way you want it, click Next.
If you specified on the Map Options page that you want to export more than one type of data—tasks, resources, and assignments—another Export Wizard mapping page appears for the next data type; for example, the Export Wizard—Resource Mapping page. Repeat steps 11 through 16 for that data type.
Each additional type of data will result in a separate worksheet in the Excel workbook.
When you have defined the data maps for each type of data, the Export Wizard—End Of Map Definition page appears.
If you expect to export this same information again, click the Save Map button. In the Save Map dialog box, shown in Figure 18-21, give your map a name, and then click Save.
Figure 18-21. The Save Map dialog box shows all built-in export maps and any additional maps that you have created.
Your export map is now saved in the project global template (Global.mpt), so it’s available in any project file on your system.
Click Finish.
Your specified project data is exported and saved as a complete Excel workbook in the Excel 2010 format and in the exact layout you defined.
To open and review your exported project data in Excel, follow these steps:
In Excel, on the File tab, click Open.
In the Open dialog box, browse to the drive and folder in which you saved your exported Excel workbook.
Double-click the workbook.
Your project data appears in the workbook, using the tasks (or resources or assignments), filter, and table or fields you selected in the export process. (See Figure 18-22.)
Adjust columns, change data types, and do any additional reformatting needed. If you’re analyzing numerical data, you can now set up the formulas or charts you want.
Sometimes, option indicators appear on some cells. For example, you might see an indicator when the export transfers numbers to fields formatted as text. Click an indicator, and, if necessary, click a command to correct an error.
On the File tab, click Save.
Change the Data Type in an Excel Column
After exporting your project information to Excel, if you need to, you can change the data type of a field of information. For example, Resource Maximum Units fields are exported as text. You can easily change that text to numbers or percentages so that you can run calculations on them.
In Excel, select the column heading. On the Home tab, in the Cells group, click Format, and then click Format Cells. Click the Number tab if necessary. Under Category, select General, Number, or Currency, as appropriate. (See Figure 18-23.) Set any number attributes you want, and then click OK.
Figure 18-23. Use the Number tab in the Format Cells dialog box in Excel to change the data type or number format of a set of exported project fields.
Even though the Task Mapping page of the Microsoft Project Export Wizard says they’re text fields, currency fields (such as Cost and Actual Cost) and earned value currency fields (such as BCWS and VAC) are automatically formatted as currency in Excel.
Just as you can import information about tasks, resources, and assignments, the Export Wizard helps you export complete project information, organized by Task, Resource, and Assignment data types, which in turn are presented in their own separate worksheets in a single Excel workbook. However, this export procedure does not export timephased information, such as resource usage over time.
To create a complete Excel workbook of project information from your project file, follow these steps:
In Project 2010, open the project whose complete information you want to export to Excel.
On the File tab, click Save As.
Browse to the drive and folder where you want to save your information.
In the Save As Type list, click Excel Workbook.
In the File Name box, enter a name for the Excel file, and then click Save.
In the Export Wizard Welcome page, click Next.
In the Export Wizard—Data page, click Project Excel Template, and then click Finish.
Your data is saved as an Excel workbook in the Excel 2010 format.
To open and review your exported project data in Excel, follow these steps:
In Excel, open the Excel file.
The workbook contains three worksheets of discrete information: Task_Table, Resource_Table, and Assignment_Table, as shown in Figure 18-24.
Resize and reformat the date fields as necessary.
On the File tab, click Save.
Note
When you export date fields from Project 2010 to Excel, the Excel date fields also include the time. If you prefer, you can change the Excel date format. To do this, first click the column headings for the date fields. On the Home tab, in the Cells group, click Format, and then click Format Cells. In the Format Cells dialog box, click the Number tab if necessary. Under Category, click Date. Click the date format you prefer, and then click OK.
If you frequently export the same Project 2010 data to Excel—for example, to run a monthly report—saving the export map and reusing it each time is much easier than having to re-create the map each time you need it. At the end of the export map definition step in the export data process, you click the Save Map button and give your map a name. Then you can call up the map in the future when you need to export the same Project 2010 fields again. To do this, follow these steps:
Open the project that contains the data you want to export with your saved export map.
If you want to export only certain tasks, resources, or assignments, use the Shift or Ctrl keys to select their rows. If you want to export all tasks, all resources, or all assignments, click the Select All cell in the upper-left cell in the view, above the ID number.
On the File tab, click 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 Type box, select Excel Workbook.
In the File Name box, enter a name for your new Excel file, and then click Save.
On the Welcome page of the wizard, click Next.
On the Export Wizard—Data page, be sure that the Selected Data option is selected. Click Next.
On the Export Wizard—Map page, select Use Existing Map, and then click Next.
In the Export Wizard—Map Selection page, click your export map in the list (see Figure 18-25), and then click Next.
Figure 18-25. The Export Wizard—Map Selection page lists built-in export maps as well as any export maps you have previously saved.
The Export Wizard—Map Options page appears with selections as defined by your saved export map.
Click Next.
The Export Wizard—Task Mapping page appears with the fields defined as you saved them.
Click Finish.
Your project data is exported as an Excel file.
