You're a project manager and you have a project that will last several months. Your project has about 10 resources assigned at various times of the project. Other project managers would also like to use some of those resources during times that overlap with your project. You need to quickly and easily figure out when your project resources are at their lowest usages so that you can work with the other project managers to determine which resources they could use.
While Microsoft Project provides Gantt charts that are an excellent way to visualize the distribution of tasks over time, they don’t do much to help you visualize the actual distribution of work. The Resource or Task Usage views help a little, but these are really just tables of numbers. You can use these tables to help you find the resource peaks and valleys, but it's easy to miss trends when you're looking at just a table of numbers.
Project provides a tool that will let you export this timescaled data from the Usage views to Excel. Once in Excel, though, the data isn't exactly perfect for charting. With a little adjustment to the data, you can graph this data for something more visual. I'll walk you through the wizard and give you step-by-step instructions on how to adjust the data in Excel to make it easier to graph.
To start the Analyze Timescaled Data in Excel Wizard, you must first make the Analysis Toolbar visible by right-clicking an existing toolbar and selecting Analysis from the list of toolbars. Then click the Analyze Timescaled Data In Excel button. This will bring up the dialog box shown in Figure A.
From here, you can export data about all resources or just the ones that are currently selected. For our example, we'll choose Entire Project and click Next. In Step 2, shown in Figure B, you're asked which values you wish to export.
You can select from any of the fields that are timescaled in Project. (You’ll see a lengthy list.) For our example, we'll pick just one field, Work, because it makes the graphing of the data across time for each resource easier. Then click Next.
Step 3, shown in Figure C, lets you define the timeframe for the export and the time unit to be used. The values will default to the earliest and latest dates on which any of the selected resources has data for the field selected in Step 2. The default unit is Days. For our example, we'll pick Weeks as the unit and leave the dates as they are. Click Next to continue.
Step 4, shown in Figure D, asks if you would like the wizard to graph the data in Excel for you. If you click Yes, Please, then it will create a graph of the total value of the field selected in Step 2 across time.
For our example, which will be making changes to the data table to allow for the graphing of information on a resource-by-resource basis, we cannot use this default graph. The changes made to the data table using this article will “break” the graph created by the wizard. For our example, we'll select No, Thanks and click Finish. Then in Step 5, click Export Data. The next screen is in Excel, as shown in Figure E.
We'll be making several adjustments to this data table to allow us to more easily graph the resource data across time. First, we'll delete Column B, as shown in Figure F.
Then we'll select the last two rows of data, as shown in Figure G, and delete them. The first of these rows will be the one with the word Total in column A.
Next, we'll be moving the data cells up one row to align them with the names of the resources. As you can see in Figure H, they're now aligned with an independent row just below each resource. The wizard puts the data in the sheet in this way to allow for multiple fields to be exported, but for our purposes we need to have the data aligned with the resource names.
To move the data, just select it and then drag the selection up one row. When you've finished, the table should have completely blank rows under each of the rows that have resource names, as shown in Figure I.
Then select each of these blank rows, right-click, and select Delete. This will leave you with a grid of data with resource names in column A with no blank rows. Next, you'll select the data table from cell A2 down to the lower-right corner of the table, as shown in Figure J.
Next, click the Chart Wizard toolbar button, which will allow you to pick the various chart options available in Excel to graph your data. For our example, we'll choose 3-D Line Chart. With a few adjustments to the Chart Depth settings, found by right-clicking one of the Series lines, selecting Format Data Series, and then selecting Options, you can see the resulting chart in Figure K.
I won't cover all of the options available to you in Excel charting here because that's a whole other subject. However, once you have the data formatted as shown in this article, you'll be able to create your charts or make further edits to the table to get your graphs the way you like them.