NCalculating Project Information in Microsoft Excel

h t 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 w 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.

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

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.

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. h 13 In the Export Filter box, click any filter you want to apply to the tasks or resources pte you're exporting.

3 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).

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).

Select Base Table for Field Mapping | X |

| Task tables:

Baseline

A

Cost

Delay

Earned Value

Earned Value Cost Indicators

Earned Value Schedule Indicators

Entry

Export

Hyperlink

PA PERT Entry

■ V

1 OK 1 Cancel

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. co

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 J2

workbook column heading (see Figure 13-7). You can change the Excel column head- ° ing here if you want (for example, from Baseline Cost to Task Budget).

Export Wizard - Task Mapping

Map Tasks Data

Destination worksheet name:

Export filter:

|Task_Tablel | All Tasks

Verify or edit Project's assumptions for how you want to map the data,

|Task_Tablel | All Tasks

Verify or edit Project's assumptions for how you want to map the data,

1

From: Microsoft Office Project Field

To: Excel Field

Data Type

Name

Name

Text

Cost

Cost

Text

Duration

Duration

Text

Actual Cost

Actual_Cost

Text

Actual Duration

Actual_Duration

Insert Row Delete Row Base on Table...

Move

Insert Row Delete Row Base on Table...

Project:

Name Cost

Duration

Actual Cost

Actual Duration

Excel:

Name Cost

¡Duration

|Actual_Cost

Actual_Duration

Initial New Produdj $2,950.00

19.25 days

j $2,950.00

19.25 days

Preview:

New product oppoi $150.00

10.25 days

!$150.00

10.25 days

< a

Describe new procj $700.00

12.5 days

Help

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. 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.

Project Management Made Easy

Project Management Made Easy

What you need to know about… Project Management Made Easy! Project management consists of more than just a large building project and can encompass small projects as well. No matter what the size of your project, you need to have some sort of project management. How you manage your project has everything to do with its outcome.

Get My Free Ebook


Post a comment