Microsoft Project Tutorial: Import Excel Into MS Project

If you’ve worked with external vendors at any time, I’m sure you’ve noticed the variety of formats vendors use for a project schedule. In some cases, it is a formal MS Project schedule. In other cases, it is a set of dates described in an email or developed in every non-MS Project user’s favorite tool—Excel or a spreadsheet variant.

The following is a brief tutorial on importing an Excel spreadsheet project schedule into Microsoft Project.

For the past four years, I’ve worked for companies that outsource most of their IT work to external vendors using fixed-price contracts. One challenge in jointly delivering a project with an external vendor is obtaining the vendor project schedule in a format that can be integrated with Microsoft Project.

The obvious solution is to have the vendor provide its Microsoft Project schedule; some vendors are reluctant to hand over their detailed schedule because it contains cost data, notes, custom macros, and other private data. If you’ve worked with outsourcing vendors, you’re familiar with some vendors who don’t consistently use Microsoft Project as a scheduling tool.

In my case, I typically receive an Excel file with tasks and start and finish dates. Ironically, my vendor extracts this information from his Microsoft Project schedule and provides an Excel file I can scroll through to find key milestones and due dates. Faced with the poor usability in scanning hundreds of tasks using Excel, I developed an import map that will properly import the Excel sheet that builds the task hierarchy in Microsoft Project.

When you export data from Microsoft Project into Excel, the data file doesn’t maintain the hierarchy. Creating the hierarchy in Excel usually involves grouping and indenting or using a custom macro. When you import an Excel file into Microsoft Project, it also lacks any of the indenting (Figure 1) and summary tasks that make Microsoft Project a valuable roll-up tool.

Build the Excel to Project import map

My solution was to develop an import map that includes the key fields in the table below

To build this map in Microsoft Project:

1. Open a sample Microsoft Project schedule. (It helps if you have a completed project schedule so the final export will have meaningful data.)
2. Go to File | Save As.
3. Select the Microsoft Excel Workbook (*.xls) as the Save as Type and click Save.
4. Click Next and leave Selected Data as the option.
5. Click New Map.
6. Select the Tasks checkbox (Figure 2).

• Click the Microsoft Office Project field and select the fields in the table above.
• Click the Next button.
• Click Save Map and Save It as an Excel MPP Map.
• Click the Finish button.

The Excel extract will now contain the key fields to build the project hierarchy.

In this case, I had to build the export map for the vendor so they could export their Microsoft Project data into a format I could use to import the file. Once the vendor had this map in their Microsoft Project file, they could easily save an Excel file using this extract. It ensured the vendor’s confidential data was kept confidential, while the critical data that I needed to understand milestones and start and finish dates for crucial tasks could be imported into my Microsoft Project schedule.

Import Excel Into Microsoft Project Wizard

Once the vendor provided a file using this format, their schedule could easily be imported into Microsoft Project by following these steps:

1. Start Microsoft Project with a blank project schedule.
2. In Microsoft Project, go to File | Open.
3. Change the Files of the Type combo box to Microsoft Excel (*.xls).
4. Select the extract file and click Open.
5. Click the Next button.
6. Select Use Existing Map.
7. Select the Excel MPP map.
8. Select Append the Data to the Active Project (Figure 3).

The result is a properly formatted Microsoft Project file that contains the vendor’s project schedule. Once the schedules are converted, I insert them as subprojects in the master project schedule.

Before I came up with this solution, I would import the schedule as a new project; I ran into calculation issues because the % Complete field is calculated and doesn’t consistently convert.

Applying imports to other project schedules

You can apply these same map concepts to other schedules that lack the Outline Level, but you’ll need to build the Outline Level manually. Depending on the level of granularity required, you might want the vendor’s essential tasks and milestones instead of the entire project schedule. The main benefit is that once the vendor and project activities are defined in one integrated view, you can quickly identify late tasks and analyze the critical path.

In fixed-price outsourcing projects, you may outsource the work to another supplier and establish penalties for failing to meet milestones. From a financial viewpoint, it makes sense because a fixed-price contract puts all the risk on the vendor; however, effective project managers collaborate with all their team members (vendors, customers, and internal team members) to deliver their projects. A key to being a successful PM is tracking an integrated project schedule so the team can collectively understand progress.

If the vendor doesn’t provide all their scheduled data, this Excel MPP map will help you integrate the needed data.

Suggested articles: The Pros and Cons of Using MS Project Software | Microsoft Project Pricing Plans & Costs Guide