A P6 user working on a large project schedule, approximately 9,000 tasks, asked how to export schedule data to Excel over a short period to limit the number of tasks exported. Here we take a look at this.
Microsoft Excel provides a common platform for gathering project progress data on large projects with many team members having differing scheduling proficiencies. You want to export schedule data to Excel in a format that allows non-scheduling team members to report their progress in Excel efficiently. And in a way that allows the scheduler to import this information back into the P6 Professional schedule.
We demonstrated this process for a schedule, a lump sum cost-loaded schedule, and a lump sum cost-loaded schedule for Physical Percent Complete Type (PCT) defined progress. Links to these articles are as follows:
In these articles, we demonstrated how the scheduler can utilize Excel and have team members unfamiliar with P6 provide schedule progress in Excel using the most prominent attributes compatible with a P6 Professional progress definition.
On large projects, schedulers must make changes or updates to a small, isolated portion of the schedule, like surgeons make small incisions with scalpels. How do you export only a small portion of the schedule’s tasks to Excel to provide your non-scheduling team members with a platform to make pertinent updates to them? This is possible.
This article demonstrates a quick tip on how to take a portion or period of a project and export schedule data to Excel to support progress updates on large projects.
You might think that P6 Professional would export only those tasks to Excel that currently appear on the screen and in residual memory. But no, P6 Professional exports all tasks associated with the schedule. However, a filter that limits the tasks exported to Excel can be applied during the export process. Our quick tip below shows how to export schedule data to Excel.
Demonstration
In Figure 1, we have our demonstration project schedule.
This schedule is too extensive to display on one screen. We want only those tasks that are in progress or planned to start in the next three weeks to export to Excel.
What Doesn’t Work
Let’s first demonstrate what doesn’t work when trying to export schedule data to Excel; this is a filter for the screen display.
We begin by selecting the Filter By feature, Figure 2.
In the Filters dialog box, we select the ‘Three Week Look Ahead’ filter and click the modify button, Figure 3.
Figure 4 displays the filter to capture in-progress tasks and those starting in the next three-week period.
For a primer on the Filter feature and particularly the matching criterion within a filter, refer to the article at the following link:
Continuing, as shown in Figure 5, we click Cancel to exit the Filter dialog box, select the Three Week Look Ahead filter, and click OK.
Only tasks captured by this Three Week Look Ahead filter are displayed on the screen in Figure 6.
Now we proceed to export these tasks to Excel.
We click File | Export and toggle the Spreadsheet – (XLSX) export type, Figure 7.
We toggle export only activities and activity relationships to simplify the demonstration, as shown in Figure 8.
We toggle to export our Mars Probe project schedule, Figure 9.
We then click + Add to create a new export template, Figure 10.
We specify the activity options we want to export, as shown in Figure 11.
Refer to the Excel Export articles mentioned previously for the most suitable column options for your non-scheduling team members to report progress in Excel, and in a way that is compatible with P6 Professional.
We click OK, and then in Figure 12, Next.
As shown in Figure 13, we click the ellipse button to choose a folder for exporting the schedule and click Next.
Finally, we click Finish, Figure 14.
Looking at the task tab in the Excel output in Figure 15, we see more tasks than our filter would allow.
So, our screen display filter did not work to limit task output for our export to Excel. The question remains: how do we export only those tasks requiring updates in the next three weeks?
What Works
Let’s demonstrate what works: how to export schedule data to Excel…that is, only a small portion or a short time frame of a large project.
If we again choose File | Export and repeat the steps leading up to the Excel Export dialog, we can make the necessary changes to our Standard Export Template when we select it and click Modify, as shown in Figure 16.
This time in the Modify Template dialogue, we choose the Filter tab, select the ‘Three Week Look Ahead’ filter previously displayed, and click OK, as shown in Figure 17.
Then, with the modified Standard Export Template selected, we click Next, Figure 18.
We again use the ellipse to choose our file location, and click Next, Figure 19.
We complete the export by clicking Next, Figure 20.
Figure 21 shows the Excel spreadsheet listing only those tasks in progress or starting in the next three weeks.
Our Modify [Export] Template active filter works to restrict the Excel-exported tasks to only those captured by our ‘Three Week Look Ahead’ filter criteria.
Summary
As demonstrated, exporting only those tasks requiring updating within a period is feasible. However, the screen display filter does not help to restrict the number of tasks when you export schedule data to Excel. We can filter tasks exported to Excel only within the Modify [Export] Template dialogue, where we can select an existing filter or create a new one and then select it.
This quick tip on how to export schedule data to Excel, is particularly helpful on large projects with thousands of tasks. So embedded in the Modify [Export] Template dialogue is the ability to restrict tasks exported to Excel to only those that meet your filter criteria. These criteria can be for a specified time frame, as demonstrated. So, the scheduler can focus on tasks solely pertinent to a performance period.