Effectively Using Excel for Analyzing MS Project Data

MS Project (MSP) is an excellent tool for preparing and tracking project schedules. Excel is also a great tool for analyzing any type of data. So, which one should you use for schedule analysis?

You probably know that MSP has a whole host of features for analyzing project schedules like Task Filters, Sorting, and Reports. It also provides different views like Resource Usage View and Tracking Gantt View for analyzing different aspects of a project schedule.

Are these features are enough? Can MS Excel help you in any way beyond those items?

The advantage of Excel is that it is more commonly used and can analyze any type of data. Microsoft introduced Visual Reports in MSP 2010, but as a whole, MSP still lacks universal appeal. In fact, these Visual Reports use Excel’s data analysis features to analyze MSP schedule data.

I have written this article to take you beyond MSP’s Visual Reports capabilities. In this article, you will learn how to exploit Excel’s pivot tables for analyzing MSP data. This article follows up on my previous article, where I covered the utility of Excel for scheduling and how to import Excel data in MSP.

MS Project Data and Excel Pivot tables

In order to use Excel’s pivot tables for analyzing MSP data, you will have to first export MSP data to MS Excel. This can be easily done by going to File > Save Project As File, and choosing Excel Workbook as the file type. As an alternative, if the MSP data is not too large, you can simply copy and paste from MSP’s Gantt Chart View to an Excel worksheet.

Let’s begin to understand pivot tables with the help of an example.

Refer to Figure I below. It shows the task hours of two resources in different months.

Figure I

Let’s assume that we want to see the month-wise task hours for each resource. Here, a pivot table becomes very useful.

A pivot table is made to calculate, summarize, and analyze any kind of data. You can use it for finding trends and variations in your data.

For the purpose of scheduling, we can use a pivot table to identify gaps in work hours, which can be further used to change duration estimates and modify plans.

Take the following steps to make a pivot table for the data presented in Figure I.

1.  Select the data rows in the Excel worksheet. Refer to Figure II below.

Figure II

2. Go to Insert menu and click on Pivot Table. Refer to Figure III below.

Figure III

3. Select the default values in the Pivot table dialog box and click OK. Refer to Figure III below.

Figure IV

4. You will see the Pivot Table settings form. Use it to select the fields that you want to use in your Pivot table. You can also use this form to drag and drop the fields and change their respective positions. Refer to Figure IV below.

Figure V

5. I have selected all the fields from our MSP data and used ‘Month’ as the main field. Refer to Figure VI below.

Figure VI

Viola! We now have structured data, which only took a few minutes of work. This can be used to identify the work hour gaps. If required, you can use this analysis tool to modify your plan.

Note: Older versions of MS Excel (pre 2013) sometimes show ‘blank’ instead of a no value. To eliminate this problem, you can follow the steps delineated in this article.

Conclusion

MS Project is a good tool for scheduling, but it has its limitations. MS Excel is a great complementary tool because it can be used to analyze different aspects of scheduling data. It can be used to find the problems and trends, which in turn can improve the project schedules.

In this article, I have shown you the power of Excel’s pivot tables with the help of a small example. You should use it analyze your regular project data. You can try and use data from the various views available in MSP.

Have you used MSP and Excel together in any way? Do you think MSP and Excel are complementary? What has been your experience so far? In what situations did you find pivot tables useful? I would love to hear about some of the use cases that you have tried.