Author: Ellen Lehnert

Ellen Lehnert, PMP, Microsoft Project MVP, MCP, is a independent consultant and trainer on Microsoft Project and Project Server. She has taught Microsoft Project over 400 times and is the author of  MS Project 2010 and 2013 published courseware. Ellen is also a contributor and tech editor for many reference books, a developer for the Microsoft Project certification tests and is a frequent meeting speaker for Microsoft, MPUG and PMI. Contact Ellen at ellen@lehnertcs.com.

Ask the Expert: Import Excel Data into Project – Tips & Troubleshooting

MS Project’s import/export function is not as clear cut as it seems, but if you follow some of the tips below the procedure should work more smoothly for you. The design of the data, the wording of the Excel tabs, column headings, and options all need to be considered to have import/export working in a way that will allow you to achieve your goals. The information below applies to MS Project 2010, 2013, 2016, and MS Project On-Line Import Wizard. Options: There are option settings and formatting in Excel that can inhibit the import function from working correctly. When the import doesn’t work, unfortunately, you will not see an error message. You will just notice that the import did not bring in the data. To ensure that the import works correctly, set the options as shown below. Once set, they will remain an MS Project setting regardless of the project you are working with. Set the Legacy settings. File → Options → Trust Center → Trust Center Settings → Legacy Formats → select the 3rd option as shown below. You should note several things regarding data design of MS Project and how the import/export function works: MS Project consists of three major sections of data (Task, Resources, and Assignments). The Import can import only to one of the data areas at a time. The Excel spreadsheet should be designed to only consist of data for import to Task, Resources, or Assignments. FAQ:  Can I import resource work updates from Excel to MS Project? No. To accomplish this type of import, the data needs to update all three data areas. Therefore, the import function is not designed to perform this type of update. Most solutions for this requirement are custom written. A web search should reveal several solutions. Format of Excel file: The Import Wizard will default to importing from Sheet1. If your data in the Excel file is contained in another sheet, you will need to adjust the import map to be aware of the data location. In Excel, datasheet names and column header names must be one word without special characters. Underlines are the best characters to use. Spaces will not work in the name format. Short names work the best. Selected columns may be imported and mapped to MS Project columns of like data format. For example, text to text, number to number, etc. Bring dates into date fields. Have a solid block of data without blank lines. The import wizard will stop at the first blank line. The MS Project file: Create any customized fields before starting the import process. Make sure the customized fields are of the same data type as the fields that will be imported from Excel. When possible, create the customized field names to match the names in the Excel file. For Task imports, create a custom table in MS Project that mirrors the Excel table. Doing this will allow for a direct copy and paste of the data, and the import wizard will not be needed. The Import Wizard: Before you start the Import Wizard: Be aware of which Excel file fields will be mapped to which MS Project file fields before starting the Import Wizard. Not all fields must be imported from the Excel worksheet into MS Project. When an import map is created, you will have the opportunity to pick which fields you will import. Make sure you know which Excel file tab names will be imported into which data areas within MS Project. Know if your import file has column headers. If it does, you should indicate that information when creating the import map. Blank lines in the Excel file will be imported as blank lines in the project file. Start and Finish dates when imported into the Start and Finish fields in Project will create Start No Earlier Than or Finish No Earlier Than constraints for the tasks. Remember that indicating that a task is completed should be updated into the Actual Finish column. If you will be using a previously created Import map, the map must reside in the Global.mpt file on the machine you are working on. If the map is not located within the Global.mpt file, the Import Wizard will not display the map as a selection choice. By default, Import Maps when created are added to the local Global.mpt. If you want the map to stay with the file, use the Organizer to copy the map into the global for the file. Running the Import Wizard: Open the file you would like to import the data into or create a blank file. File → Open → Files of type → Excel workbook – Locate the file and click on it, Click on Open. The Import Wizard will start. Click If you have previously created a map, you can use the existing map. If this is a new import, select New Map. Click Select if the import will be bringing the data in a new file, appending an existing open file, or merging the data with existing data. If you select “New Project” a new file will be opened using default option settings and default blank project file. If you select “Append” the imported data will be placed at the end of the current open file. If you select “Merge” a merge key must be contained in the MS Project file and in the imported Excel data. The merge key is one field and must be specified at the time the import map is created. Click  Select data types: Select whether the data will be directed toward Task, Resources, and/or Assignment data. One, two or all three may be selected, but each one will only import one sheet at a time. Select whether the imported file has column headers. If you have column headers in the Excel file, this information will be used to match field names from the import file to field names in the Project file. The header row will not be considered valid content data and will not be imported. If the imported data does not contain headers, column positioning will be matched with the fields on the import map. Click Next. Select the source worksheet. Default is None or If the sheet is recognized (ie: correct sheet name format), the data fields will appear with preview at the bottom of the view. The left side of the view will have the fields in the imported file. Select field values on the right side of the view to map these values to the MS Project fields. Click Next to save the new map OR click Finish to start the import. If you selected Next, click on Save Map. You will be asked to assign the new map a name. Click Save to save the map. Click Finish to start the import. If the import does not occur properly or at all, re-check that you’ve followed the tips above. MS Project Export: When a column with a symbol (like the Indicators column) is exported to Excel, the export will produce a numeric code and not the symbol you see in MS Project. Using the import/export wizard will not maintain the WBS structure, but if you copy and paste a Task table from MS Project to Excel, the WBS structure will remain.   Related Content Webinars (watch for free now!): Exporting and Importing Data into Project, Excel and Outlook Project and Excel Integration – the application dream team! Articles: Microsoft® Excel Keyboard Shortcuts 8 Places where Microsoft Excel Scores for Project Management A Free Project Pipeline Tracker for Excel 6 Practical Scenarios Where Excel Import is Useful Build KPIs with Project Online and Excel 2013

A Few More Classroom Tales….

One of my favorite PM stories from the classroom was that someone wrote on a class evaluation that I taught a “supper” class. I happened to see it and chuckled. He looked at me questioningly, and I pointed to the word. He then fixed it to say that I had taught a “souper” class. Another funny moment occurred when I asked a class when you should reset a project baseline. The head of the IT department in the class said “every Friday.” I asked him if he was ever late on a project and he said, “No!” Duh!!!! Related Content Webinars (watch for free now!): From Task Manager to People Manager – The Next Generation of Project Managers Collaborative Project Management – Process & Leadership Articles: Three Activities That Help Create an Authentic Workplace Ten Project Management Truths Communication: 5 Ways to Improve Your Project’s Lessons Learned

Plan for the Finish — Manage from the Start

Oftentimes people want to plan a project from the finish date and manage it from the start date. The benefit of doing so is that the project manager can flag the tasks with critical goal dates using deadlines and will be able to manage the project to achieve the goal dates. Here’s how. To plan the project from the ending date, follow these steps: Go to Project | Project Information. Set the plan to Schedule from Project Finish Date. Enter the finish date for the project. (I’d add a few days for cushion if possible.) Enter tasks, relationships, etc. to build the project schedule. Mark milestones (or key tasks) with a deadline. To manage the project from the start date: Go to Project | Project Information. Set the plan to Schedule from Project Start Date. Enter the start date. The last step is to remove the “As Late as Possible” task constraint for all tasks. Select all tasks by clicking in the box above the task numbers on the left side of the view. Click on Task | Information | Advance tab. Change all constraints to “As Soon as Possible” and click OK.   Image courtesy of Andrew Hurley — CC 2.0  

The Case of the Clueless Management

  At one organization where I worked, the CIO had seen something on the value of earned value. They brought me in to teach a two-day Microsoft Project class to their engineers without mentioning that this was now part of their goals. Every engineer on that team within a few months of the class was supposed to produce EV on projects that were two years late at a schedule performance index level of .95. The engineers had never heard of Project, had no project management background, and knew nothing about work breakdown structure or even EV prior to the class. EV was never mentioned in the class. Definitely one of those cases where management expected people to do far more than they were trained for. In another company, Microsoft has given the client all sorts of free coding to create dashboards. But they have no valid data to feed the dashboards, so they’ll sit there for now. Compounding the problem, the company doesn’t see the need for training but still expects to see results soon. A few weeks ago I was on the phone with a prospect who wanted Microsoft Project installed. And then he expected the software to create the projects and update them automatically without the project managers having to spend more than five minutes a week on the effort. Nor did he want training, process or governance. The software should just know what needed to be done. My response: “If you purchase a fancy stove, will it cook dinner for you?” Photo courtesy of Craig Sunter

Disconnecting a Project from a Shared Resource Pool

If you have connected a project with an external resource pool and you wish to disconnect the project from the pool there are two steps you will need to take. While the connection is a single-step process, disconnection is a two-step process. The disconnection should be performed in this order particularly if the tasks have been tracked. Once the project is disconnected from the pool, the resources and tracking will remain in the project file. Step 1: In the Resource Pool file click on: Resource > Resource Pool > Share Resources > Project name > Break Link. Step 2: Open the resource pool that was being used by the project. Open the project schedule and click on: Resource > Share Resources > Use own resources > OK.

3 Tips: Autofilter, Entry Bar and the Timeline View

1. Use the autofilter (the small down arrow to the right of the column name) on the Start and Finish columns to see tasks by timeframe. Then filter for specific resources. Apply outline level 1 to collapse the detail. 2. Here’s a useful option: File > Options > Display. The lowest option is “Entry Bar.” Clicking it will show help in field editing and will appear below the ribbon bar. 3. The Timeline view is best when shown using Summary and Milestone tasks only.

4 Tips for Working Smarter in Microsoft Project

If you use Microsoft Project to manage your projects, you know how important it is to work efficiently and effectively. Luckily, there are a few tips and tricks you can use to work smarter, not harder. Customize your Quick Access bar: The Quick Access bar is a customizable toolbar that sits above or below the ribbon. By moving it below the ribbon, you can fit more buttons on it. To customize the bar, right-click on an existing button and select “Add to Quick Access Bar” or “Remove” to remove a button. Use the right buttons: Some of the most useful buttons to add to the Quick Access bar are Scroll to Task, Print Preview, Outline, Clear Filter, Clear Group, Publish (for Project Server users only), Publish as PDF, Timescale, and Open. These buttons will save you time and help you work more efficiently. Use keyboard shortcuts: Keyboard shortcuts can be a huge time-saver in Microsoft Project. For example, the best keystroke shortcut ever is F3, which will clear any applied filter. Use Project Templates: If you frequently work on similar projects, you can save time by creating a project template. This will allow you to quickly set up new projects with the same tasks, resources, and schedules. By implementing these tips, you can become a more efficient Microsoft Project user and spend less time on tedious tasks, giving you more time to focus on your project’s success.

Webinar: Learn from the MVP: Tips & Tricks with Project 2010/2013

Project Management Institute (PMI)® Professional Development Units (PDUs): This webinar is eligible for 1 Category A PMI® PDUs in the Technical Project Management talent triangle category. If you are claiming this session, you must submit it to your MPUG Webinar History after it has been completed in its entirety. Event Description:  Have you ever wondered what is the best way to see which tasks are scheduled to take place next week across multiple projects? How can you group task data using specific resource fields? What is the easiest way to create a quick customized TimeLine report for project phases?  How can you tell if your project ending date is really accurate?  During this session, these questions and more will be answered. This session is designed to show you suggestions that you can apply to your schedules to enhance MS Project 2010/2013 immediate usage. Speaker Bio:  Ellen Lehnert With over 20 years of corporate training experience, Ellen Lehnert, MVP, PMP, MCT, MCP, is a consultant/trainer for Microsoft Project and Microsoft Project Server. She’s author of  “Managing Projects with Microsoft Project 2010 Desktop courseware,”, technical editor and contributor for several MS Project reference books, and frequent meeting speaker. Ellen brings a unique combination of licensed teacher and programming background to the classes she teaches as well as process development, installations, and consulting. Contact her at ellen@lehnertcs.com or at LehnertCS. Have you watched this webinar recording? Tell MPUG viewers what you think! [WPCR_INSERT]

  • 1
  • 2
  • 7