Learn how to import Microsoft Excel XLS and XLSX Data File into Microsoft Project. Many folks use Microsoft Excel as a tool used for organizing project data. Microsoft Project has several features that support importing this Excel project data into a Project schedule file.
Manual entry of project data into Microsoft Project from Microsoft Excel can be cumbersome. Importing the project data directly from Microsoft Excel is a much more efficient way. And, of course, Microsoft Project has several data types that support importing Microsoft Excel spreadsheet data.
Here we look at how to import Microsoft Excel XLS and XLSX files into Microsoft Project 2013.
In Figure 1 we have our demonstration project data.
Figure 1
This data is simply of list of project tasks and associated task information. The Microsoft Project import wizard defaults to importing Sheet1. All our project data is contained in Sheet1. For proper importing the Excel datasheet names, column headers, must be one word, or two words separated by the underline special character.
We want to create some custom fields, select the project tab, properties ribbon group, and custom fields, Figure 2.
Figure 2
In Figure 3 we define our first data type.
Figure 3
This is a unique task code that we plan to read in as text. In Figure 3 we choose the Text1 data field and select rename. We rename our Text1 data field Task_Name, Figure 3. We also add another data type in Text2 named Cost_Code. We continue and add a third data type. This data type we plan to make a list of skill trades. After renaming Text3 Trade, select lookup, Figure 4.
Figure 4
Select insert to add a new value. A list of our schedule trades is displayed in Figure 5.
Figure 5
Great! We have competed the definition of our text fields.
Continue in the custom field dialog and select cost from the type drop down menu, Figure 6.
Figure 6
Rename Cost1 Expense, Figure 7.
Figure 7
Proceed and select number from the type drop down menu, Figure 8.
Figure 8
Finally, we rename Number1, Value_Factor, Figure 9.
Figure 9
Now with all our data types defined we set up the schedule columns, as per Figure 10.
Figure 10
We are now ready to import our project data spreadsheet.
Select File and Open, Figure 11.
Figure 11
In the File system we find and open the file ‘project data.xlsx’. We could have also imported from an xls file, Figure 12.
Figure 12
When we select the xlsx file type the import wizard – map dialog pops up on the screen, Figure 13.
Figure 13
Toggle new map and select next. Map refers to the direct mapping between Excel data and Microsoft Project data. In the next screen select ‘append the data to the active project’ and click next, Figure 14.
Figure 14
In Figure 15, we select the task information to import along with the column headers.
Figure 15
Click next. Change the source work sheet name to Sheet1, Figure 16.
Figure 16
In Figure 17 we see our mapping, everything looks good.
Figure 17
When we finally select Finish, and the project data imports as per Figure 18.
Figure 18
Summary
It is possible to import an Excel XLS or XLSX files directly into Microsoft Project 2013. Microsoft Project supports several data types including: text, text including lookup attributes, costs, and numbers. Make sure the Excel spreadsheet column headings have no spaces.
The import procedure is to open a Microsoft Project file. Set up the customized fields and then column headings. Import the project data by appending to the open project. If fields were not properly mapped, the import wizard provides the option to edit how you want to map the data. So, you can import Microsoft Excel XLS and XLSX data files into Microsoft Project.