How to Merge Excel Data into an Existing Microsoft Project File
Question details
The user needs to merge records from an Excel workbook into an active Microsoft Project schedule so that existing tasks are updated properly.
- Product
- Microsoft Project, Microsoft Excel
- Device & OS
- not provided
- Scenario
- Importing and mapping task updates or new data from an Excel workbook into an existing Microsoft Project schedule.
- Observed behavior
- The imported Excel records are being appended as new rows after the last project task instead of updating the existing tasks.
Ensure your Excel workbook contains a reliable unique identifier column, such as a Task ID, to accurately match your spreadsheet records with the existing Microsoft Project tasks.
Configure the Import Map and Merge Key
Use the Microsoft Project Import Wizard to map a unique Excel field to the Project ID, ensuring data merges into existing tasks rather than appending to the end.
When importing data from Excel to Microsoft Project, the application relies on an 'Import Map' to understand how spreadsheet columns correspond to project fields. If a unique merge key is not explicitly set, Project assumes all incoming data represents new tasks and simply appends them to the bottom of your list.
In Microsoft Project, navigate to 'File' > 'New' > 'New from Excel Workbook' (or go to 'File' > 'Open', change the file type to Excel Workbook, and select your file).
Once the Excel file is selected, the Import Wizard will launch. Click 'Next', and choose whether to create a new map or use an existing one.
When prompted for the import mode, select the 'Merge the data into the active project' radio button, then click 'Next'.
In the mapping step, carefully map the unique ID column from your Excel sheet to the corresponding ID field in Microsoft Project. Click the 'Merge Key' column for this field to set it to 'Yes'.
Review the preview window to confirm the columns align correctly. Once verified, click 'Finish' to execute the merge.
Prepare Your Project Data with WPS Office
While Microsoft Project manages your final schedule, preparing your raw task data is often easier in a spreadsheet. WPS Office provides a free, lightweight, and powerful alternative to Microsoft Excel, ensuring your .xlsx files are perfectly formatted before you merge them into Microsoft Project.
- 1. Download WPS Office: Install the free WPS Office suite on your computer.
- 2. Open Your Project Data: Launch WPS Spreadsheet and open the .xlsx file containing your project tasks.
- 3. Format and Save: Organize your columns, ensure your unique Task ID column is clearly labeled, and save the document for importing into MS Project.
Frequently Asked Questions
Why are my Excel tasks appearing at the bottom of my Microsoft Project file instead of updating?
This occurs when the import map lacks a defined 'Merge Key' (like a Task ID) or the 'Merge the data into the active project' option wasn't selected during the Import Wizard. Without these settings, Microsoft Project treats the imported data as entirely new tasks.
What is a merge key in Microsoft Project?
A merge key is a unique identifier, such as an ID number, that the software uses during the import process to accurately match records in your Excel file with the exact corresponding tasks in your active Project file.
Can I save my import mapping settings for future use?
Yes. At the end of the Import Wizard in Microsoft Project, you will be prompted with an option to save your newly created import map. This allows you to quickly reuse the exact same field mappings for future Excel imports without configuring them manually again.
