Calculating Project Information in Microsoft Excel


Because Microsoft Excel is such a powerful tool for manipulating and calculating numeric data, in some cases you might find it beneficial to move some of the numeric data from Microsoft Project for more in-depth analyses in Excel.

For example, you can export project cost or earned value data to Excel. Or perhaps you want to crunch tracking data or assignment information. You can also create pivot tables and generate graphs such as S-curves to show project performance information.

Exporting information from Microsoft Project to Excel has been made particularly easy with the Excel Export Wizard, introduced with Project 2002.

Analyzing Numeric Project Data in Excel

Although you can export most information types from Microsoft Project to Excel, it's most useful to export numeric data for further analysis in Excel. Numeric data are any data that can be used in calculations and mathematical operations. Examples of such data include cost and work data. Work data (such as hours) can be converted to numeric fields, but are stored in Microsoft Project as text because of the unit names such as hours, days, or weeks. On the other hand, dates are not considered numeric data, even though they consist mostly of numbers .

When preparing to export information from Microsoft Project to Excel for numerical analysis, first decide which tasks, resources, and fields you want to export to Excel. You can export only selected or filtered tasks or resources to Excel. You can export the same fields you see in a given table ”such as the Earned Value, Cost, or Tracking table ”or you can export just three or four fields that you select. You can also export a large representation of all your project data with the different types of project, task, resource, and assignment data appearing in different worksheets within the Excel workbook.

Tip  

Calculating Project Information Using Custom Formulas       You can set up formulas to calculate specified data within Microsoft Project. You create a custom field, such as Cost1 or Number3, and then build the formula to operate on the data in or related to that field.

Cross-References  

For more information about creating a custom field containing a formula, see "Creating a Calculated Field".

Exporting Selected Data to Excel

To specify the project data you want to export and then send it to Excel, follow these steps:

  1. Apply the view that contains the task, resource, or assignment information you want to export.

    The table or fields applied to the view does not matter at this point because you choose the fields you want as part of the export process.

  2. If you want to export only certain tasks, resources, or assignments, select them using the Shift or Ctrl keys. If you want to export all tasks, all resources, or all assignments, click the Select All cell in the upper-left cell in the view, above the ID number.

    Later, as part of the export process, you can apply a task or resource filter if you want.

  3. Click File, Save As.

    The Save As dialog box appears.

  4. Browse to the drive and folder where you want to save your information.

  5. In the Save As Type box, select Microsoft Excel Workbook (*.xls).

  6. In the File Name box, enter a name for your new Excel file.

    By default, the project file name is adopted with the .xls extension.

  7. Click Save.

    The first page of the Export Wizard appears.

  8. On the Welcome page of the wizard, click Next.

  9. On the Export Wizard “ Data page, be sure that the Selected Data option is selected. Click Next.

  10. On the Export Wizard “ Map page, be sure that the New Map option is selected. Click Next.

  11. On the Map Options page of the wizard, select the check box for the type of data you want to map: Tasks, Resources, or Assignments. Under Microsoft Excel Options, select the Export Includes Headers check box (see Figure 13-4). Click Next.

    click to expand
    Figure 13-4: In the Export Wizard, select the type of data you're exporting to Excel.

  12. On the Mapping page of the wizard, in the Destination Worksheet Name box, you can enter a name for the destination worksheet within the workbook you're creating.

    You can also just use the default name provided; for example, Task_Table1.

  13. In the Export Filter box, click any filter you want to apply to the tasks or resources you're exporting.

  14. In the Verify Or Edit Microsoft Project's Assumptions For How You Want To Import The Data table, specify which project fields are to be exported and how they should be defined. In the From: Microsoft Office Project Field, click the arrow and then click the name of the field you want to export. Be sure to start with the Name field, to make sure the task name or resource name is exported. Type the first one or two characters of the field name to move to it quickly (see Figure 13-5).

    click to expand
    Figure 13-5: In the Mapping page, select the specific fields you want to export.

    Instead of entering each field individually, you can add all fields from a particular Microsoft Project table. Click the Base On Table button. The Select Base Table For Field Mapping dialog box appears (see Figure 13-6).


    Figure 13-6: Select the Project table that contains the fields you want to use as your export data source.

    Click the Microsoft Project table you want to export (for example, Earned Value or Cost) and then click OK. The fields that define that table appear in the Task Mapping table.

    As soon as you enter a field in the From column, its default equivalent appears in the To: Excel Field column, showing the name of the field as it will appear in the Excel workbook column heading (see Figure 13-7). You can change the Excel column heading here if you want (for example, from Baseline Cost to Task Budget).

    click to expand
    Figure 13-7: The Microsoft Project field, its Excel equivalent, and the data type as exported show in the table.

    Because you want to work with numeric project data, you can use this table to select only those fields containing numeric values. For example, you might select Name, Cost, and Duration.

    The Data Type column shows the data type of the field. Incidentally, these data all come in as Text fields, even though they might really be number, date, or currency fields in Microsoft Project. To simplify the export process, all fields are changed to text fields. Then you can easily convert the data type for any of these fields in Excel, which is especially important for numeric data on which you want to run calculations.

    Use the command buttons below the table to add and remove fields in the table. Use the Move up and down buttons to the right of the table to rearrange the order of the fields, which represents the order the columns will appear in Excel.

    The data is shown as it will appear in Excel in the Preview area at the bottom of the Mapping page. Use the scroll bar to view all the columns. When ready, click Next.

  15. On the End Of Map Definition page, click the Save Map button if you expect to export this same information again. Otherwise, click Finish.

    Your specified project data is exported to Excel in the exact layout you defined.

Troubleshooting: I can't find a field I want in the Mapping table
start example

The list of Microsoft Project fields changes depending on whether you're exporting tasks or resources. Tasks and resources have different fields associated with them.

If you're looking specifically for the BAC (Budgeted At Completion) earned value field, find and apply the Baseline Cost field instead ”they are the same field.

If you're looking for a Variance field, look for the specific type of variance ”for example, Cost Variance or earned value CV (Cost Variance). There's also Work Variance, SV (Schedule Variance), and VAC (Variance At Completion).

Cross-References  

Refer to Appendix B, "Field Reference" for a complete listing of all Microsoft Project fields.

end example
 

To open and review your exported project data in Excel, follow these steps:

  1. Start Microsoft Excel.

  2. Click File, Open.

  3. In the Open dialog box, browse to the drive and folder in which you saved your exported Excel workbook.

  4. Double-click the workbook.

    Your project data appears in the workbook using the tasks (or resources), filter, and table or fields you selected in the export process (see Figure 13-8).

    click to expand
    Figure 13-8: View and manipulate your project data in Excel.

  5. Adjust columns, change data types, and then set up formulas or charts as you wish to analyze this data further.

start sidebar
Change the Data Type in an Excel Column

After exporting your project information to Excel, if you need to, you can change the data type of a field of information from text, for example, to numbers. For example, Duration fields are exported as text. You can easily change that text to numbers so you can run calculations on them.

In Excel, select the column heading and then click Format, Cells . Click the Number tab if necessary. Under Category, select General, Number, or Currency, as appropriate (see Figure 13-9). Set any number attributes you want and then click OK.

click to expand
Figure 13-9: Use the Number tab in the Format Cells dialog box in Excel to change the data type or number format of a set of exported project fields.

Even though the Task Mapping page of the Microsoft Project Export Wizard says they're Text fields, currency fields (such as Cost and Actual Cost) and earned value currency fields (such as BCWS and VAC) are automatically formatted as currency in Excel.

end sidebar
 
Inside Out: Overwrite Excel format
start example

Suppose that you changed information or formatting in your new Excel workbook containing your exported project information, and now you're ready to save your changes. After you click the Save button, you might get a prompt asking whether you want to overwrite an older Excel format (see Figure 13-10).

click to expand
Figure 13-10: Click Yes to update the Excel 5.0 Workbook format to the latest Excel format.

To ensure compatibility with older versions of Excel, Microsoft Project exports to the Microsoft Excel 5.0/Excel 95 file format. When you go to save the new Excel file the first time, Excel prompts you to update the format to the current version you have installed on your computer, which might be Microsoft Excel 2000 or Excel XP … … …, for example.

Click Yes to update the workbook format, which ensures that you can use the latest features of your current Excel version on your exported project data.

end example
 

Exporting Task, Resource, and Assignment Data to Excel at Once

For certain kinds of analyses, you need to look at your project data by general type; that is, by task, resource, or assignment. You can export project information to Excel as a template . With this type of export, Microsoft Project organizes data into Task, Resource, and Assignment data types. Each data type is then presented with its relevant information in its own separate worksheet within the single Excel workbook.

To export task, resource, and assignment information from your project file to Excel all at once, follow these steps:

  1. In Microsoft Project, open the project whose complete information you want to analyze in Excel.

  2. Click File, Save As.

  3. Browse to the drive and folder in which you want to save your information.

  4. In the Save As Type box, click Microsoft Excel Workbook (*.xls).

  5. In the File Name box, enter a name for the Excel file.

  6. Click Save.

    The Export Wizard appears.

  7. On the Welcome page of the wizard, click Next.

  8. In the Export Wizard “ Data page, select the Project Excel Template option and then click Finish.

Open the new Excel workbook you just created. The workbook contains four worksheets of discrete information: Task_Table, Resource_Table, and Assignment_Table. Info_Table provides general instructions for using this workbook (see Figure 13-11).

click to expand
Figure 13-11: Separate worksheets are created to hold key task, resource, and assignment information from your project.
Tip  

Change the date format       When you export date fields from Microsoft Project to Excel, the Excel date fields also include the time. If you prefer, you can change the Excel date format. To do this, first click the column headings for the date fields; then click Format, Cells. The Format Cells dialog box appears. Click the Number tab. Under Category, click Date. Click the date format you prefer and then click OK.

Analyzing Timephased Project Data in Excel

In addition to exporting task, resource, and assignment field information to Excel, you can also export timephased data from Microsoft Project to Excel. To do this, follow these steps:

  1. In Microsoft Project, open the project plan whose timephased data you want to export to Excel.

  2. Display a task view if you want to export timephased task information. Display a resource view to export timephased resource information.

  3. If you want to export data only for selected tasks or resources, display and select those items. Use the Ctrl or Shift keys to select multiple tasks or resources.

  4. Click View, Toolbars, Analysis to display the Analysis toolbar.

  5. On the Analysis toolbar, click Analyze Timescaled Data In Excel.

  6. In the first page of the Analyze Timescaled Data Wizard, select the Entire Project or Currently Selected Tasks (or Currently Selected Resources) to specify which set of timephased data is to be exported. Click Next.

    The second page of the wizard appears, showing the list of all available timephased fields for either tasks or resources, depending on whether you're showing a task or resource view.

  7. Under Available Fields, select the timephased fields you want to export; for example, Actual Cost, Baseline Cost, and Cost. Click Add to move the selected fields to the Fields To Export box (see Figure 13-12).

    click to expand
    Figure 13-12: Select the timephased fields you want to export to Excel.

    Use the Ctrl or Shift keys to select multiple fields at one time. You can select fields and click Add as many times as you need to.

  8. When you finish adding fields to the Fields To Export box, click Next.

  9. In the third page of the wizard, enter the date range and time unit you want to use for the exported data (see Figure 13-13). Click Next.

    click to expand
    Figure 13-13: Enter the date range and time unit for the exported timephased data.

    When choosing your date range and time unit, keep in mind that Excel can display only 256 columns of data.

  10. In the fourth page of the wizard, specify whether you want the timescaled data to be graphed in Excel. Click Next.

  11. In the fifth page of the wizard, click the Export Data button.

    Microsoft Excel starts up, and the selected timephased data is exported. This process might take several minutes, depending on the amount of data to be exported and the speed of your computer. This export works with Microsoft Excel version 5.0 or later.

    If the data you have chosen results in more than 256 columns of timephased data, an error message appears. To fit your data into fewer columns, you can specify a shorter date range or a different timescale ; for example, weeks instead of days. Or you can simply have the export process cut off the data at 256 columns.

  12. Review your timephased data in Excel.

    When the export is complete, Excel appears, showing your data. If you chose to chart the data, the Timescaled Data Graph appears first (see Figure 13-14).

    click to expand
    Figure 13-14: Review the exported timephased fields in Excel as a line chart.

    Click the Timescaled Data worksheet tab at the bottom of the Excel workbook to review the data in an Excel worksheet (see Figure 13-15).

    click to expand
    Figure 13-15: Review the exported timephased fields in Excel as a worksheet.

The timescaled data in Excel is initially exported from Microsoft Project as a text (.txt) file and saved in a temporary folder. To retain this timephased information in the Excel format, follow these steps:

  1. In Excel, click File, Save As.

  2. In the Save As Type box, click Microsoft Excel Workbook (*.xls).

  3. In the File Name box, enter a name for the new file.

    Its default name is TimeData.xls .

  4. Use the Save In box and Up One Level button to browse to the drive and folder in which you want to save the new workbook.

  5. Click Save.

Analyzing Project Data with Crosstab Tables

Another technique for analyzing project data in Excel is to use crosstab tables of information, which are tabular presentations of intersecting information along vertical and horizontal fields of information. In Excel, these crosstabs are known as pivot tables . Pivot tables are a flexible way to reorganize data in comparative form, with one category of information being filtered and populated into another category of information. Microsoft Project has a built-in method for exporting project data into Excel pivot tables.

For example, you can use an Excel pivot table to analyze information regarding the relative cost performance of different groups of team members and to also see how that performance varies according to the different phases of your project.

To build an Excel pivot table, do the following:

  1. In Microsoft Project, open the project plan from which you want to create a Microsoft Excel pivot table.

  2. Click File, Save As.

  3. In the Save As dialog box, browse to the drive and folder in which you want to save the Excel pivot table file.

  4. In the Save As Type box, click Microsoft Excel PivotTable (*.xls).

  5. In the File Name box, enter a name for the new Excel pivot table file you'll be creating.

  6. Click Save.

    The first page of the Export Wizard appears.

  7. On the Welcome page, click Next.

  8. On the Map page, click the New Map option and then click Next.

  9. On the Map Options page, click Tasks, Resources, or Assignments to specify the type of data you're using to create the pivot table. Read the explanation on this page of how PivotTable mapping is done, and then click Next.

    The Task Mapping, Resource Mapping, or Assignment Mapping page appears, depending on the type of data you chose to export.

  10. If you want, specify the destination worksheet name and an export filter. Specify the field to be exported, either by selecting it individually in the table or by clicking Base On Table and selecting the project table that contains the fields you want to include. Click Finish.

    The project information is exported and saved in the Excel pivot table.

In Excel, open your newly created PivotTable workbook. You see that there are two worksheets: one contains the field data specified, and the other holds the actual pivot table, which you can now modify (see Figure 13-16 on the following page).

click to expand
Figure 13-16: Examine your project information in the Excel pivot table.

Your choices for working with the pivot table are extensive , including graphic charting of your results. There is also a handy floating PivotTable toolbar. You can change the preformatted pivot table to see different groupings and summaries.

Cross-References  

Refer to Excel's online Help for detailed information on working with your new pivot table.

Charting Project Data Using S-Curves

Suppose that you exported numerical project data into Excel, and the worksheets show you what you need to know. Now you want to share information ”for example, the actual cost of work for a task ”in a more graphical format. One popular format for graphing performance trends is the S-Curve graph . Use the charting and graphing capabilities of Excel to build an S-Curve graph of your project data, as follows :

  1. Export project information to Excel using the methods described earlier in this chapter.

  2. Open the Excel workbook you just created. Select the data you want charted, for example, Task Name and Cost.

    You can select multiple columns or rows at once by holding down the Ctrl key as you click them (see Figure 13-17).

    click to expand
    Figure 13-17: Select the data you want to be included in your S-Curve graph.

  3. On the Formatting toolbar in Excel, click Chart Wizard.

  4. In the first page of the Chart Wizard, click the Custom Types tab and then select the Smooth Lines chart type (see Figure 13-18). Click Next.

    click to expand
    Figure 13-18: Choose the Smooth Lines chart type to create an S-Curve graph.

  5. In the second page of the wizard, ensure that the Data Range contains the two columns you chose in Step 2.

    You should see the moving "marquee" around your selected columns in the worksheet behind the wizard. Use the Data Range selection button if needed.

  6. Click the Columns option and then click Next.

  7. In the third page of the wizard, make any changes you want to the chart formatting on the various tabs and then click Next.

  8. In the fourth and final page of the wizard, click As New Sheet. Leave the name as Chart1 and then click Finish.

    The S-Curve graph appears in a separate sheet of your workbook (see Figure 13-19). You can create a wide variety of Excel charts and graphs from different aspects of your Microsoft Project data.

    click to expand
    Figure 13-19: Review graphic representations of your project data in Microsoft Excel.




Microsoft Office Project 2003 Inside Out
Microsoft Office Project 2003 Inside Out
ISBN: 0735619581
EAN: 2147483647
Year: 2003
Pages: 268

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net