I’m a software engineer by education. In 1997 I was working for a software development company, managing small software projects with the help of a small team. In the same year, I was introduced to Microsoft Project. In 1997 Project wasn’t particularly popular, even though it had been around for a few years. It had a limited set of features, but I found it useful. In fact, I instantly got hitched to it. It was a great tool then and it has become immensely better over the years. Today, it is much more intuitive to use and has more powerful features.
Throughout my career, I have met project managers who show reluctance in using Project. But there are also many who regularly use it. Yet somehow, they don’t take full advantage of its power. Even after so many years of being around, Project has many helpful features that are seldom used.
One such feature was mentioned in my previous article, “8 Places where Microsoft Excel Scores for Project Management.” In that article I talked about four features of Project that help in using Excel and Microsoft Project together, including importing Excel data into Project. Let’s drill down on that topic to solve some project management use cases.
Pick up more great integration advice during MPUG’s upcoming “Project Integration Month” taking place all through July. Check out the online MPUG member training sessions and free vendor showcase sessions here.
Import Excel Data into Microsoft Project
If you’re not acquainted with the basics of Excel import, then take a few minutes to read Ellen Lehnert’s excellent article, “Ask the Expert: Import Excel Data into Project — Tips & Troubleshooting,” which provides a good introduction of the Excel Import feature.
If you have already used Excel Import, then skip the following section and jump to the “Use Cases” section.
A Brief Overview of Excel Import
You can import Excel data by going to File | Open in Project. When you choose File Type as Excel and select an Excel data file, Project’s Import Wizard starts. The Import Wizard runs through several screens. These screens are shown in the following figures.
Figure 1
Figure 2
Figure 3
Figure 4
Figure 5
Figure 6
In Ellen’s article, which provides details of importing task data into a new Project file, you’ll find a basic introduction to all the wizard screens. But to fully exploit Excel Import, you need to do more. The power of this feature comes when you import data on an ongoing basis and merge it with an existing Project file. Let’s discus how to take advantage of Excel Import to solve some practical problems.
Use Case #1: Integrating Excel Schedule Data into a Project Schedule
As a project manager you want to make a Project work breakdown structure (WBS) but leave the detailed scheduling to the team members, who don’t have access to the Project.
You have been made a project manager for a project named “Universe” by your sponsor. He has assigned you a team of two dedicated team members: “Superman” and “Spiderman.”
You’ve decomposed the project into four major work packages: “Milky Way,” “Andromeda,” “Black Eye” and “Cartwheel.” You have asked your team members to develop a detailed schedule of work packages. The team members have come back with four Excel sheets with detailed work schedules (one for each work package), as shown in the following figures:
Figure 7
Figure 8
Figure 9
Figure 10
You want to integrate schedule data available in the Excel sheets with your Project schedule.
A simple solution is copy and pasting Excel data into Project. But that’s not an elegant solution. Moreover, it’s time consuming and prone to errors. So, let’s do the Excel import.
Step 1. Create a Project file that looks like the following figure. This is our original project schedule. Notice that, I have intentionally left blank rows after each work package. These blank rows will be populated by Excel data. As such blank rows don’t affect Project.
Figure 11
Step 2. Import the first Excel file. In the Import Wizard choose New Map (figure 3 above), Merge… (figure 4) and Tasks (figure 5).
You’ll notice that Project automatically maps the Excel column names to Project (Gantt Chart) column names. This only happens if the Excel file column names are the same as that in Project. Otherwise, you would need to map the columns yourself.
Step 3. Use ID as the “Merge Key” and click Finish. Voila! You have imported the data.
Figure 12
You’ll notice a few important things:
- Imported data comes in the correct order;
- Since we didn’t have date-related data in the Excel file, the Project Start Date is automatically copied to the Start Date of the first task;
- All the other dates (Start and Finish) are automatically calculated;
- All the tasks are properly outlined; and
- The data for summary tasks is automatically calculated.
If you go to the Resource Sheet in Microsoft Project, you’ll notice that a new resource has been added:
Figure 13
Now you know the power of Excel Import. The Excel data was imported as desired without any significant effort. You can play around with it by importing different fields as required for your project.
Step 4. Repeat steps two through four and import other Excel files. Refer to the following figures for the final Microsoft Project schedule.
Figure 14
Figure 15
Use Case #2: Integrate a Client Schedule into Your Project File
To integrate a client or vendor’s schedule into your Project file, follow the same steps that you followed in use case #1. The data given to you by your client or vendor might be different, but the procedure for import will remain same.
Use Case #3: Share Resources with Other Departments
Say you want to share resources with other departments in your company. The organization’s resource manager has assigned you two more team members, but their availability is limited to 50 percent. Their names are “Batman” and “Ironman.” The resource manager has also given you an Excel sheet with resource data, which you want to add to your project schedule:
Figure 16
Once again, the simple solution of copy and paste will work. But in reality resource data might be huge and much more complex. So let’s do another Excel Import.
Step 1. Open your old Microsoft Project file.
Step 2. Import the Excel file, which was given to you by your organization’s resource manager. In the import wizard choose New Map (figure 3), Append… (figure 4) and Resources (figure 5).
You’ll notice that Project automatically maps the Excel column names to Project (Resource Sheet) column names. This only happens if the Excel file column names are the same as the ones in Project. Otherwise, you would need to map the columns.
Step 3. Click Finish. The new resources will be added at the bottom of the resource sheet. The following figure shows the final Project schedule.
Figure 17
Once again no manual effort was required and the Excel data was imported seamlessly.
Use Case #4: Assigning Shared Resources to Project Tasks
In this scenario you want to assign shared resources to the project tasks. Batman and Ironman have been added to the project schedule, but now they need to be assigned tasks. You want to assign work packages Black Eye and Cartwheel to these resources. Superman (your senior resource) prepares tasks assignments in an Excel sheet and gives it to you. You want to import this sheet into your Project schedule.
You’ll follow the same basic steps to import assignments from the Excel sheet with one minor difference: In the import wizard you’ll choose New Map, Merge… and Assignments.
Use Case #5: Sharing Cost Data
There are four resources assigned to your project, but you haven’t added their hourly rates. Your accounting department maintains a strict control over cost-related data of the resources. They share the relevant data in Excel files. You want to import hourly rates from the Excel sheet into your Project file.
In this case you’d choose New Map, Merge… and Resources in the import wizard. When you complete this import, you’ll see the updated costs for each task in the Gantt Chart view.
Use Case #6: Tracking the Schedule with Actual Data
Your team shares timesheets and tasks statuses on a weekly basis. Since they don’t have access to Project, they share the actual data with you in Excel sheets. You want to import this data and track the project on a weekly basis.
By now, you’re an expert in Excel Import. You know how to handle this situation. So, I don’t want to say anything here.
When you complete this import, you should open the Tracking Gantt Chart View and notice the changes.
The Practical Outcomes of Excel Imports
There are many features in Project that are either little used or ill-understood. Excel Import is one of them. In this article I’ve covered six possibilities where Excel Import can be used for practical purposes. I’m sure there are many more you can come up with yourself.
I encourage you to keep exploring Microsoft Project features you haven’t given much attention to. As I learned back in 1997, Project is a wonderful tool with a great set of features that will increase your productivity.
Do you use Excel Import in your projects? Share your experiences in the comments below.
