How to Arrange Data for Chart

[Karen]

I charted the following data in a clustered column chart.

Level 1 Level 2 Level 3 Level 4
FY07 - Total 167 23 20 43 81
FY08 - Total 178 22 40 26 90

These are total department errors segregated by levels for each fiscal year.
There are 3 different departments involved. What I did was just total the
errors for each FY and each level. Now my boss wants to see how many errors
were performed within each level by Dept A, Dept B and Dept C and compare the
2 fiscal years. I hope I’m explaining this properly. How can I arrange the
raw data so I can chart this?

Thank you, Karen

 

[Jon Peltier]

blank Level 1 Level 2 Level 3 Level 4
FY07 23 20 43 81
FY08 22 40 26 90


Make sure the top left cell is blank. Make a clustered column chart. If you
make it by row, you will have one cluster for each FY, with each level
having a column in each cluster. If you make it by column, you will have a
cluster for each level, with one column per FY.

- Jon

 

[Karen]

Thank you so much for responding - I'm either not understanding what you
suggested or maybe I didn't explain myself correctly. I want to be able to
break down each department. For example: FY07, Level 1 has a total of 23.
There are 3 departments that are part of this total. Dept A made 5 errors,
Dept B made 12 errors and Dept C made 6 errors. I have to break down each
level and each fiscal year to compare FY07 vs. FY08. Is this a better
explanation? If so, how do I arrange this data?

Thanks again, Karen

 

[Jon Peltier]

That's one more subdivision than I understood. Do the same departments
contribute to all levels, or does each level contain different departments?

- Jon

 

[Karen]

I'm sorry for the lack of explanation Jon.

Yes, all three departments contribute to all levels. I want to compare each
level and each FY. Within the total of each level and FY, I want to see how
many errors each department made within that total.

An example:
FY07 - Level 1
Dept A = 25
Dept B = 50
Dept C = 75

FY08 - Level 1
Dept A = 35
Dept B = 20
Dept C = 15

FY07 - Level 2
Dept A = 10
Dept B = 5
Dept C = 20

FY08 - Level 2
Dept A = 20
Dept B = 15
Dept C = 10

and so on...

Thank you sooooooo much, Karen

 

[Jon Peltier]

Set up the data like this:

Year Level Department Score
FY07 Level 1 Dept A 25
FY07 Level 1 Dept B 50
FY07 Level 1 Dept C 75
FY08 Level 1 Dept A 35
FY08 Level 1 Dept B 20
FY08 Level 1 Dept C 15
FY07 Level 2 Dept A 10
FY07 Level 2 Dept B 5
FY07 Level 2 Dept C 20
FY08 Level 2 Dept A 20
FY08 Level 2 Dept B 15
FY08 Level 2 Dept C 10

Make a pivot table (Data menu). Drag the Year and Level fields into the Rows
area, the Department field into the Columns area, and the Score field into
the Data area. Make a pivot chart using the stacked column option. Depending
on the placement of Year and Level within the Rows area, you can achieve
different groupings in the chart.

- Jon

 

[Karen]

Thank you sooooooooooooooooo much Jon! It worked great! You are da bomb!
Have a wonderful day!
Karen