Using Pivot Tables for analysis

Excel PivotTables can be interesting and useful when you want to analyze Project earned value data. The PivotTable is an interactive table that summarizes large amounts of data in a cross-tabular format. When you use Project to create a PivotTable in Excel, you get two PivotTables in the same workbook: a Task PivotTable and a Resource PivotTable. The Task PivotTable shows resources, tasks to which the resources are assigned, and costs for the resource per task. The Resource PivotTable summarizes resources by showing work that is assigned to each resource and the total cost of each resource. In addition to the PivotTable worksheets, the same Excel workbook also includes two worksheets — Tasks and Resources — that Excel uses to create these two PivotTables.

To export Project information to create PivotTables in Excel, follow these steps:

1. Start in any view of your project.

2. Choose File O Save As to open the Save As dialog box.

3. Type a name for the Excel workbook that you want to create in the File name box. Don't worry about the extension — Project supplies it.

4. Select Microsoft Excel PivotTable from the Save as type list box.

5. Click Save to start the Export Mapping Wizard.

6. Click Next.

7. Choose Use existing map, and click Next.

8. Select Task and resource PivotTable, and click Finish, as shown in Figure 14-25.

Figure 14-25: The Map Selection portion of the Export Wizard dialog box.

Note You can continue clicking Next in the wizard, but to chart earned value in Excel, you usually don't need to make any changes in the remaining dialog boxes that the wizard presents.

The hourglass icon for the mouse pointer appears, indicating that you should wait while action takes place. You'll also hear action on your hard drive. To view the PivotTables and their source data, start Excel and open the file that you just created. The workbook contains four sheets that should resemble the sheets that are shown in Figures 14-26, 14-27, 14-28, and 14-29.

«1 Microsoft Excel - PnratTable.xls UISIQ

f|j File Edit View insert Farmat Iools ¡¡.Ha Wttirfow Help rvne a quaston -or hail - „ e d & a & © m a y * e, -\«-«*-1 % z - iia |u « -

iLafiatEa^^Ea 3 % TV Reply with Changes... End Review... „

iris! - 10 - B 1 U m B a S |f % , *A H iwW B? i® * & m

X

Al T £ Sum i '".( :: :

A

B C

D

E

F G H I J

7

Work *

Total

PivotTable

X

10 11 12

14 V It

17 13

18 20

(blank)

Bob Woock

71 ilrs

213D

PivotTable * & fill t J ^ 1

Bob Woock Total

2130

Deena Tanenblatt 149,5 hrs

1237.5

i

Deena Tan enblatt Total

1237.5

items to the PwotTable report

Do Lahr 156 hrs

1400

Do Lahr Total

1400

|—I Resource Group

Ellen Pect; Total

2Q2D

g Resource Name

Gasoline 10 hra

0

gwwrti QCost

Gasoiine Total

0

Intern |3S hrs

540

Intern Total

540

Joe Johnson tits

700

Joe Johnson Totel

700

Looq distance |Ü hrs

0

Lonq distance Total

0

Lurnber |G hrs

0

Lurnber Total

0

21 22

(blank) Total

3027.5

Grand Tolal

8027.5

I Add To I 'Row Area ▼

23

?4

-

< ► H\Tasks X Task PivotTable / Resoixces ^Resource PivotTable / h I M Ready MUM

Figure 14-26: The Resource PivotTable.

- LH

1® File Edit View insert Farmat Iools Data Window Help fmeaausstan-orhslji • „ e

D & a & © m a 7 * fe a - <r \« - «** | % * - ii a | u « -

iLafiatEa^^Ea 3 % TV Reply with Changes... End Review... .

in«! -10 - B 1 u m e a s |f % , *a H # w B? i® * & m

X

A1

•*■ fa Resource Group

A B

e

D

E

F

e

H

1 J

K

2

Resource

Resource Name

Work

C05t

Joe Johnson

20 hrs

$700.00

3

Deena Tanenblatt

49.5 hrs

{1237.50

i

Ellen Peck

76 hrs

Î2 020.00

5

Bob Woock

71 hrs

Ï3.I30CC

B

Do Lahr

SG hrs

Î1 ,400.CO

7

Intern

36 hrs

f640.BC to .00

e

Long distance

0 hrs

9

Lumber

0 hrs

JO .00

10

Gasoline

0 hrs

10.0]

11

12

13

1J

15

16

17

18

19

20

21

22

23

f1

1J

1 ►!

:< < ► n\Tasks 1 Task PivotTable \ Resources/ Resouice WfjtTsble / |i

MUM

Figure 14-27: The Resources sheet that Excel used to create the Resource PivotTable.

Figure 14-28: The Task PivotTable.

PivtTable *ls

- riiil gjQ Ftlp Filit View insert Farm at loo Is rrata Window Help

]SHiJ!i#El? * fe « - | « - « . | % z - | U « 100% -

gjQ Ftlp Filit View insert Farm at loo Is rrata Window Help

]SHiJ!i#El? * fe « - | « - « . | % z - | U « 100% -

A1

r Resource Group

A

BCD Resource Name Task Nam el Task Name2 BobWoock Conference Preplanning

Task Name3 Initial planning Initial planning

Task Name4 Determine budget

G

2

Resource Group

Duration 5 days

Start vmiii

3

Deena Tanenblatt Conference Preplanning

Invitation list

3 days

GMI6/;

4

Conference Preplanning

Initial planning

Send out invitations

days

oam/;

5

Deena Tanenblatt Conference Selection

Tbeme

,75 days

own/;

G

Intern Conference Selection

Site

.95 wks

0G/03/2C

7

Do Lahr Conference Selection

Site

0.95 wks

0G.TO/2C

8

Bob Woock Conference Selection

Keynote speaker

4.13 days

0G/03/2C

9

Deena Tanenblatt Conference Selection

Keynote speaker

4.13 days

0G.ro/2c

10

Ellen Peck Conference Selection

Keynote speaker

4.13 days

0G.ro/2C

11

Ellen Peck Conference Hire

Caterer

5 days

0&TOi

12

Ellen Peck Conference Hire

Bartenders

3 days

os/t6/;

13

Bob Woock Conference Hire

Security

2.5 days

G6TO/;

14

Joe Johnson Conference Hire

Security

2,5 days

06wn

15

Deena Tanenblatt Conference Hire

Photographers

.5 days

06wr.

1G

Intern Conference Hire

Photographers

.5 days

0M3i

17

Intern Conference Hire

Cleanup Crew

days

0 STO/i

18

Joe Johnson Conference Hire

Cleanup Crew

2 days

OBTOi

19

Ellen Peck Conference Plan Entertainment

Baseball Game

1 day

csmr.

20

Ellen Peck Conference Plan Entertainment

Opera

4 hrs

064)2/:

21

Do Lahr Conference Public Relations

Alert community

2 days

06/1 ocr

-

22

Do Lahr Conference Public Relations

Press release

1 day

06/10CC

23

Conference Rent Equipment

PA System

1 day

06/10/2C

74

Conference Events ask PivotTable / Resources / Resource PivotTable /

Baseball.Game

0 davs

07/22/;

h * ► h\ Tasks/ Ready

Mi

MUM

>11

Figure 14-29: The Tasks sheet that Excel used to create the Task PivotTable.

Tip You may need to widen columns in Excel to see all the data. Double-click the right border of the column letter to do so.

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


Responses

Post a comment