MS Excel is a generic tool that can be used in many situations. In terms of project management, it is an average tool for preparing project schedules, at best. It is generally believed that MS Project is a much better solution than Excel could ever be for project scheduling. This is because Excel has some inherent disadvantages for creating and maintaining project schedules.
Here is a quote from senior MPUG author, Keith Wilson, “Using Excel to manage project information doesn’t make sense, since Project is set up to increase productivity and effectiveness.” (Read his full article here.) I, too, believe that Excel is a cumbersome tool and has serious limitations in creating and maintaining schedules. However, there are times where it becomes necessary to use Excel in conjunction with MSP.
Let’s back up for a moment. MSP is an expensive tool and its licenses are costly. To save costs, many organizations buy licenses only for the project managers who are responsible for preparing and maintaining project schedules. This keeps costs under control, but leaves team members without a way to track a project’s progress and schedule, which can be detrimental for project delivery.
In such situations, it becomes necessary to use a generic tool like MS Excel along with MSP to maintain project schedules. In this scenario, the project manager can start by preparing a high level project schedule, and then ask team members to fill in their respective tasks. The team members can use Excel to create parts of the project schedule and share updated Excel sheets with the project manager.
The project manager can then use import functionality of MSP to integrate and merge the Excel data. This allows the project managers to concentrate on the overall schedule without wasting any time on mundane lower level activities. As you can see, importing data from Excel into MSP can be useful. Let’s look at the steps involved.
Steps to Import Project Data from Excel to MS Project
You can look at an article written by Ellen Lehnert to understand the basics of import functions. This article would be useful if you are looking to do a one-time import of Excel data in a new Project file. However, in most cases, PMs would need more than a one-time import as project scheduling and tracking is a complex task. You need a constant integration of MS Project and Excel data by importing and merging Excel data periodically into your Project file.
Let’s look at the steps to importing Excel data into an existing MSP file.
1. Open MS Project.
2. Click on File > Open.
3. Choose ‘Excel Workbook’ from the dropdown menu, instead of the default option. Refer to Figure I below.
Figure I
4. After select the Excel file, MSP will start an Import Wizard. Click on the ‘Next’ button to continue and follow the Wizard to import the Excel file. Refer to Figure II below.
Figure II
5. Select ‘New map’ and click ‘Next.’ This feature links the Excel columns to MSP. Refer to Figure III below.
Figure III
6. On the next screen, you can do one of the three things as shown in Figure IV. Here, we will merge data into an existing project. This way you can integrate team members’ data into your MSP file and will not need to re-enter the data.
Figure IV
7. Then, you can choose to import one of three types of data, Tasks, Resources, or Assignments. Refer to Figure V below.
Figure V
You will find an example in the section for importing Tasks from Excel to MSP.
8. On the next screen, map your Excel columns to appropriate MSP columns. Refer to Figure VI below.
Figure VI
This is the last step for importing the data. Simply, click on the ‘Next’ button and MSP will do its magic.
An Illustration for Importing Tasks
Let’s assume that you are a project manager, and you want to create a high level WBS giving liberty to you team members to define the lower level tasks. You have a high level project schedule defined in MSP. Refer to Figure VII.
Figure VII
You can export this schedule as an Excel file and provide it to your team members. You can also ask them to create lower level tasks in Excel and share their respective sheets to you.
Refer to Figure VIII below. This may be what the Excel sheet created by your team member looks like.
Figure VIII
Now follow the steps in the previous section. Project will automatically import the data into the correct columns and rows and your project schedule will begin to take shape. Refer to Figure X for updated MSP Gantt chart.
Figure IX
A Few Important Points to Note
You will have to ensure that names of Excel columns are the same as MSP Gantt Chart column names. Otherwise, you will need to map the columns yourself.
The number in Excel’s ID column should be synchronized with MSP to ensure that tasks are imported in the correct rows. If this is the case, imported data will come in the correct order.
The Project Start Date from MSP is automatically populated base on the Start Date of the first task. All the other successor dates (Start and Finish) are automatically calculated.
MSP outlines all imported tasks and summary task data is automatically calculated. In fact, MSP does a little more than just importing the tasks. It will also add a new related resource for you. Refer to Figure XI to see an example of a newly added resource.
Figure X
Conclusion
The importing of date to MS Project is a great tool for project managers. It not only saves on cost, but also saves a lot of time. In addition to merging your team members’ data, you can use the method I’ve described here to exchange information with your clients and vendors.
In this article, I have given an example of importing tasks from Excel, but, as stated, you can also import resources and assignments. Play around with importing other fields that are needed in your project.
What has been your experience with using MSP and Excel together? In what situations have you found this combination of tools useful?
I would love to hear your thoughts in the comments below.
