How to Import Monthly Excel Costs into Microsoft Project Cost Resources
Question details
The user needs to transfer monthly cost data from an Excel spreadsheet into Microsoft Project cost resources, and accurately determine resource start and finish dates by identifying the first and last nonzero monthly values.
- Product
- Microsoft Excel and Microsoft Project
- Device & OS
- not provided
- Scenario
- Importing structured monthly financial data from Excel to MS Project while automatically filtering out trailing zeros to set accurate resource active dates.
- Observed behavior
- When importing monthly costs directly, MS Project may include trailing zeros, extending the active dates incorrectly. The goal is to map columns properly and extract first/last nonzero months as the effective start and finish dates.
Ensure your Excel spreadsheet is formatted cleanly with one row per cost resource and distinct columns for each month's data before attempting any data transformation.
Use Power Query to Transform Data for MS Project
Use Excel's Power Query to automatically unpivot data, filter out zero values, and calculate the exact start and finish dates for each resource.
Power Query is the most reliable method for reshaping cross-tabular data (like months across columns) into a flat format that Microsoft Project can easily digest. It allows you to dynamically find the first and last nonzero values without manual calculation.
Select your data range in Excel, navigate to the Data tab, and click 'From Table/Range' to open the Power Query Editor.
Select the Resource Name column, right-click its header, and choose 'Unpivot Other Columns'. This transforms your monthly columns into 'Attribute' (Month) and 'Value' (Cost) rows.
Click the filter dropdown on the 'Value' column and uncheck '0' to remove any months with zero costs. This ensures trailing and leading zeros are excluded.
Group the data by Resource Name. Add an aggregation for 'Minimum' on the Attribute column (Start Date) and 'Maximum' on the Attribute column (Finish Date).
Click 'Close & Load' to return the formatted data to Excel. Open Microsoft Project, go to File > Open, select your Excel file, and use the Import Wizard to map the new Start Date, Finish Date, and Cost fields.
Use a VBA Macro to Identify Nonzero Dates
Write a custom VBA script to automatically loop through rows and find the start and finish dates based on nonzero entries.
Prepare Your Project Data with WPS Office
While Microsoft Project is required for final resource scheduling, preparing and cleaning your monthly cost data is seamless in WPS Spreadsheet. WPS Office is a free, lightweight alternative that handles large datasets effortlessly before you export them to Project.
- 1. Install WPS Office: Download and install the free WPS Office suite on your device.
- 2. Open your cost spreadsheet: Launch WPS Spreadsheet and open your existing monthly Excel cost data.
- 3. Prepare for import: Use built-in formulas or data tools to clean trailing zeros and determine your start/finish dates before importing the file into MS Project.
Frequently Asked Questions
Can I directly copy and paste monthly costs from Excel to MS Project?
Yes, you can manually copy and paste data into the Timephased grid in MS Project's Task Usage or Resource Usage views. However, using an import map or Power Query is highly recommended because it ensures start and finish dates are accurately calculated based on nonzero values.
Why do my cost resources show trailing zeros in MS Project?
Trailing zeros appear when a resource is assigned for the entire project duration but has no actual costs in later months. By calculating the actual finish date (last nonzero month) in Excel beforehand, you prevent MS Project from distributing blank values across inactive periods.
Does MS Project have a built-in Excel import wizard?
Yes, MS Project includes an Import Wizard that allows you to map Excel columns directly to Project fields. However, the wizard does not automatically filter out zero-value months, which is why formatting your data in Excel or WPS Spreadsheet first is necessary.
