Purpose:

You sent out an excel file of the look-ahead schedule to the team, and they've updated the progress using the excel sheet you sent out. On a big project, there may be hundreds of activities that may have made progress and it would be incredibly tedious to manually input those dates into P6; not to mention, time consuming. Instead of manually inserting the dates into P6, we'll explain how to do one mass import from excel into P6. 

Process Overview:

One note before we get started: We're going to use a few excel functions (TRIM and VLOOKUP) to help us run the import. If you're unfamiliar with these functions, I recommend finding a tutorial on them to better understand them as I'll only give a brief description on how they work here. However, if you don't know them, I'll explain them enough so that you'll still be able to complete the exercise.

Also, we're going to use "Physical % Complete" as the percent type. I'll do a separate lesson on what the different percent types mean, but for this lesson, just know that we'll be importing the remaining duration instead of the activity % complete.

Let's go over the steps that we'll run through to import progress from excel into P6.

1. Create Excel Template for Import - In order to import information into P6, P6 has certain column ID's and tab names in the excel file that it reads in order to make the import successful. We'll run an export from P6 to excel to create that template file for us to import the information back into P6.
2. Reformat the information from our excel look-ahead file to ensure it's in a format that is importable.
3. Run the VLOOKUP function for the correct WBS section to ensure the activity remains in the proper WBS band. Without this step, the activity would get moved to the top of the schedule because P6 would not know where the activity is located.
4. Copy the information into our template file and run the import.

Instructions

Step 1 - Create Template for Import:

In order to import the progress back into P6, we first need an excel file that P6 understands as importable. In order to get this, we'll first export a file from P6 into excel with the columns for the information we want to import. In our case, we specifically need "Activity ID, WBS, Actual Start, Actual Finish, and Remaining Duration". So, let's begin by opening up the project in P6 where you want to eventually import the progress into.

Once the file is opened, we'll go to "File > Export".

We'll choose the "Spreadsheet" radio button and choose "Next".

We only need to select "Activities" on the next page as the other information isn't necessary for this import. Click "Next" after you select "Activities".

Make sure the export button is selected on the file we want to export and press "Next".

Here's the important step. Where it says "Select Template", let's create our own template by clicking "Add". This will allow us to choose the columns that are relevant to this exercise.

After we click add, the following window pops up for modifying our template. Notice that I changed the "Template Name" to "Import Progress" which will allow me to save this template and reference it later if need be.

Also, notice the "Selected Options" that I chose. I have Activity ID, Activity Status, WBS Code, Actual Start, Actual Finish, and Remaining Duration. If you can't find these columns, use the carrot that I show on the left which has the "Find" feature which will allow you to search for these columns. Once your "Selected Options" table matches below, click "Ok".

After we clicked "Ok", it takes us back to the following window where we can click "Next".

On the next window, it will ask us where we want to place the file once we export it. In my case, I selected my desktop. If you click on the file location, a button will appear on the right with three dots "…". You can click that and browse another folder where you can place the file. Once you choose your destination, click "Next" and then "Finish".

Hopefully you get a response that says "The export was successful". If you did, click close and then search for where you placed the file and open it.

When you open up the file, it'll look something like the following:

 So now we have this table where we can add the progress to. In our case, the header names in Row 1 are what's important to us. We can actually delete all of the information from Row 3 to Row 15 and replace it with the information that's relevant to our import. But we'll worry about that in the next step.

Step 2 - Reformat Data for Import:

I created a sample "look-ahead spreadsheet" that was sent out to the team and they provided the progress back on the cream colored columns (F, G, and H).

So we can see that we have the Activity ID, Actual Start, Actual Finish, and Remaining Duration columns. Notice that we're missing the WBS information which we'll grab in step 3. But for now, we need to make sure that the format for importing the information is accurate. First, let's run the "TRIM" function on all of the Activities ID's in Column A. This will remove any unwanted spaces in the IDs. Notice below that I create a new column right of column A to run our TRIM function.

Copy that formula all the way down to trim all of the IDs in our table.

Now, let's wipe the table clean from the excel file that we exported from P6. Notice below that I deleted all of the information from rows 3 through 15.

We can now copy over the information from our look-ahead spreadsheet to the one that was exported from P6. The items that I'm copying over are the activity ID (that was trimmed), Actual Start, Actual Finish, and Remaining Duration.

So two pieces of information that are missing:

1. Activity Status - We can actually delete this column as it won't affect our import. All we need are the actual dates and the remaining duration and the status will automatically change based on that information.
2. WBS Code - We need this information because if we don't have it, P6 will move the activities to the top of the schedule and consider them to not have a WBS. In order to find the WBS Code, we'll run our VLOOKUP function in the next step.

Step 3 - VLOOKUP

In order to get the WBS Code for the activities, I like to use the VLOOKUP function. Let's add a new tab on the P6 export file as a place to dump some data from p6 into.

Now that we have a new sheet. Let's go to P6 and add columns just for Activity ID and WBS. Notice that I got rid of all of the other columns.

Let's copy and paste the activity IDs and the WBS from P6 into our new "Sheet1" tab.

You may not notice but we need to run our TRIM function on the Activity IDs as there is a space right before the "A". If we don't do this, when we run our VLOOKUP function, the IDs won't technically match and would prevent us from getting a successful result. So let's go ahead and trim the IDs.

Make sure to copy the formula all the way down.

Now, let's go back to our "TASK" tab where we can run our VLOOKUP function for the WBS Code. The image below shows us running the function. If you're unfamiliar with it, what we're saying is Lookup the value in A3 and compare it with the table in Sheet1 between B2 and C14. If you find a match in the first column, return the value in the second column. If you don't find a match, return a false value (which in this case is #N/A).

Just a special note, the $ signs where it says "Sheet1!$B$2:$C$14" mean that it will lock in those cells if I were to copy down the formula instead of shifting the table. I won't explain it more than that, but just make sure that the $ signs are present on both the rows and the columns in the array.

Once you've entered the formula, make sure to copy it all the way down.

After you've copied the formula all the way down, you'll want to highlight that same range (B3:B15) and copy and paste the values to the same location. We don't want the WBS to be in a formula format upon import so we'll just paste the value back to that location.

Once we paste the values, let's go ahead and delete Sheet1 as we no longer need it for our import. We're now ready to run our import! Go ahead and save the excel file and then close it.

Step 4 - Import

Let's take a look at the schedule file before we run the import. As you can see, we have no actual dates and all of the activities have not started yet.

Let's go to "File > Import" to run our import.

Choose the "Spreadsheet (XLSX)" radio button and click "Next". Then choose the file you want to import. Remember, I put mine on the desktop so I'll browse to that location and select the file. Once I find it, I'll click "Next".

We get to the "Import Project Options" section and we want to make sure our "Import Action" is set to "Update Existing Project" <-----This is really important!!!. Also, we need to select our project under the "Import To" selection box. Double click the box and browse to the project file for where we are importing the information to. Double click the project, then click "Next".

That'll take us to our summary page and we'll click "Finish".

WALA!!! Now, you can see below that it applied all of the actual dates along with the remaining duration changes we made.

Summary

You may be thinking to yourself "that was a big effort to go through when I could have just entered the dates and been done with it in less time". My response to that is, you have to weigh how much data you are going to import versus whether it is worth it to just hand-jam it or not. If I'm updating only a few activities, I'll just do it by hand. But if it gets to anything greater than 20 activities or so, then I'll just run the import. PLUS, when you do this all the time, you become really fast at it.

Happy Scheduling! Feel free to leave a comment below and let me know what you think.