ApplePark Ltd - Ben Howard MVP

Viewing Timephased Data in Visual Reports (Microsoft Project & Excel)

·

·

At a Glance: Yes—you can replicate Microsoft Project’s Task Usage view in Excel using the built-in Visual Reports engine (Report → Visual Reports → Assignment Usage). By exporting an Assignment Usage template to Excel and expanding the PivotTable time hierarchy, you get a clean timephased breakdown of Work, Actual Work, or Cost by Task and Resource.

I recently ran an MPUG session integrating Project with Excel.  Part of the presentation was using the Visual Reports feature and exporting the data to Excel, and I had one of those questions which caught me out (though it shouldn’t have, I’d just had a long day!).  So this post answers the question that was asked, which was basically “can I replicate the task usage view in Excel”, and the answer is, yes.  This blog post will show you how.

 

Selecting an Assignment Usage Visual Report in Microsoft Project

Within MS Project, select the visual reports icon, and then choose one of the views within the Assignment Usage tab (I based my report on the Baseline Work Report.

 

image

 

Alternatively, or create a new template using the Assignment Usage data type.

 

image

 

Formatting the Timephased PivotTable in Excel

Once you’ve exported the data to Excel, then you need to select the right data to display (1), turn off the subtotals (2) and hid column B (3)

 

image

 

You can find the files for this blog at https://1drv.ms/f/s!AoTVCKDbj8EAhcVGawgo7MQDWI0R9Q

Enjoy,  Ben.

For a complete overview of all three methods for moving data between Project and Excel, read our cornerstone guide on Best Practices for Integration of Microsoft Project and Excel. Want to see another practical way to use Microsoft Project’s Visual Reports in Excel? Check out Microsoft Project MVP Dale Howard’s walkthrough and video tutorial:

How to Create a Cash Flow Report in Microsoft Project - Dale Howard MVP Video

How to Create a Cash Flow Report in Microsoft Project

See how to export timephased cost and cumulative cost data from Microsoft Project into an Excel PivotTable and chart using the Visual Reports engine.

How to Export Microsoft Project to Excel - Dale Howard MVP Video

How to Export Microsoft Project to Excel

Learn how to export task, resource, and assignment data from Microsoft Project to Excel cleanly using export maps.

Topics: Excel & VBA