MS Project’s import/export function is not as clear cut as it seems, but if you follow some of the tips below the procedure should work more smoothly for you. The design of the data, the wording of the Excel tabs, column headings, and options all need to be considered to have import/export working in a way that will allow you to achieve your goals.

The information below applies to MS Project 2010, 2013, 2016, and MS Project On-Line Import Wizard.

Options:

There are option settings and formatting in Excel that can inhibit the import function from working correctly. When the import doesn’t work, unfortunately, you will not see an error message. You will just notice that the import did not bring in the data. To ensure that the import works correctly, set the options as shown below. Once set, they will remain an MS Project setting regardless of the project you are working with.

Set the Legacy settings.

File → Options → Trust Center → Trust Center Settings → Legacy Formats → select the 3^rd option as shown below.

You should note several things regarding data design of MS Project and how the import/export function works:

MS Project consists of three major sections of data (Task, Resources, and Assignments).
The Import can import only to one of the data areas at a time.
The Excel spreadsheet should be designed to only consist of data for import to Task, Resources, or Assignments.

FAQ: Can I import resource work updates from Excel to MS Project?

No. To accomplish this type of import, the data needs to update all three data areas. Therefore, the import function is not designed to perform this type of update. Most solutions for this requirement are custom written. A web search should reveal several solutions.

Format of Excel file:

The Import Wizard will default to importing from Sheet1. If your data in the Excel file is contained in another sheet, you will need to adjust the import map to be aware of the data location.
In Excel, datasheet names and column header names must be one word without special characters. Underlines are the best characters to use. Spaces will not work in the name format. Short names work the best.
Selected columns may be imported and mapped to MS Project columns of like data format. For example, text to text, number to number, etc. Bring dates into date fields.
Have a solid block of data without blank lines. The import wizard will stop at the first blank line.

The MS Project file:

Create any customized fields before starting the import process.
Make sure the customized fields are of the same data type as the fields that will be imported from Excel.
When possible, create the customized field names to match the names in the Excel file.
For Task imports, create a custom table in MS Project that mirrors the Excel table. Doing this will allow for a direct copy and paste of the data, and the import wizard will not be needed.

The Import Wizard:

Before you start the Import Wizard:

Be aware of which Excel file fields will be mapped to which MS Project file fields before starting the Import Wizard.
Not all fields must be imported from the Excel worksheet into MS Project. When an import map is created, you will have the opportunity to pick which fields you will import.
Make sure you know which Excel file tab names will be imported into which data areas within MS Project.
Know if your import file has column headers. If it does, you should indicate that information when creating the import map.
Blank lines in the Excel file will be imported as blank lines in the project file.
Start and Finish dates when imported into the Start and Finish fields in Project will create Start No Earlier Than or Finish No Earlier Than constraints for the tasks. Remember that indicating that a task is completed should be updated into the Actual Finish column.
If you will be using a previously created Import map, the map must reside in the Global.mpt file on the machine you are working on. If the map is not located within the Global.mpt file, the Import Wizard will not display the map as a selection choice. By default, Import Maps when created are added to the local Global.mpt. If you want the map to stay with the file, use the Organizer to copy the map into the global for the file.

Running the Import Wizard:

Open the file you would like to import the data into or create a blank file.
File → Open → Files of type → Excel workbook – Locate the file and click on it, Click on Open. The Import Wizard will start.
Click
If you have previously created a map, you can use the existing map. If this is a new import, select New Map.
Click
Select if the import will be bringing the data in a new file, appending an existing open file, or merging the data with existing data.
- If you select “New Project” a new file will be opened using default option settings and default blank project file.
- If you select “Append” the imported data will be placed at the end of the current open file.
- If you select “Merge” a merge key must be contained in the MS Project file and in the imported Excel data. The merge key is one field and must be specified at the time the import map is created.

Click
Select data types:
- Select whether the data will be directed toward Task, Resources, and/or Assignment data.
  One, two or all three may be selected, but each one will only import one sheet at a time.
- Select whether the imported file has column headers.
  - If you have column headers in the Excel file, this information will be used to match field names from the import file to field names in the Project file. The header row will not be considered valid content data and will not be imported.
  - If the imported data does not contain headers, column positioning will be matched with the fields on the import map.

Click Next.
Select the source worksheet. Default is None or
- If the sheet is recognized (ie: correct sheet name format), the data fields will appear with preview at the bottom of the view.
The left side of the view will have the fields in the imported file. Select field values on the right side of the view to map these values to the MS Project fields.
Click Next to save the new map OR click Finish to start the import.

If you selected Next, click on Save Map. You will be asked to assign the new map
a name.
Click Save to save the map.
Click Finish to start the import.

If the import does not occur properly or at all, re-check that you’ve followed the tips above.

MS Project Export:

When a column with a symbol (like the Indicators column) is exported to Excel, the export will produce a numeric code and not the symbol you see in MS Project.

Using the import/export wizard will not maintain the WBS structure, but if you copy and paste a Task table from MS Project to Excel, the WBS structure will remain.

Related Content

Webinars (watch for free now!):
Exporting and Importing Data into Project, Excel and Outlook
Project and Excel Integration – the application dream team!

Articles:
Microsoft® Excel Keyboard Shortcuts
8 Places where Microsoft Excel Scores for Project Management
A Free Project Pipeline Tracker for Excel
6 Practical Scenarios Where Excel Import is Useful
Build KPIs with Project Online and Excel 2013