Cannot grasp best way to set up Pivot table Chart that has many years for column headings.

[Ronanm]

Hi

I am trying to work out the best way to set up a Pivot Chart using the following headings.... There are 800 students (rows)

<TABLE style="WIDTH: 581pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=774 x:str><COLGROUP><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 95pt; mso-width-source: userset; mso-width-alt: 4644" width=127><COL style="WIDTH: 46pt; mso-width-source: userset; mso-width-alt: 2230" width=61><COL style="WIDTH: 56pt; mso-width-source: userset; mso-width-alt: 2706" width=74><COL style="WIDTH: 48pt" span=7 width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 12.75pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 height=17 width=64><?xml:namespace prefix = v ns = "urn:schemas-microsoft-com:vml" /><v:shapetype id=_x0000_t201 path="m,l,21600r21600,l21600,xe" o:spt="201" coordsize="21600,21600"><v:stroke joinstyle="miter"></v:stroke><vath o:connecttype="rect" fillok="f" strokeok="f" o:extrusionok="f" shadowok="f"></vath><?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o:lock shapetype="t" v:ext="edit"></o:lock></v:shapetype><v:shape style="Z-INDEX: 1; POSITION: absolute; MARGIN-TOP: 0px; WIDTH: 48pt; HEIGHT: 12.75pt; VISIBILITY: hidden; MARGIN-LEFT: 0px" id=_x0000_s1025 type="#_x0000_t201" o:insetmode="auto" stroked="f"><o:lock v:ext="edit" text="t" rotation="t"></o:lock></v:shape><v:shape style="Z-INDEX: 2; POSITION: absolute; MARGIN-TOP: 0px; WIDTH: 48pt; HEIGHT: 12.75pt; VISIBILITY: hidden; MARGIN-LEFT: 0px" id=_x0000_s1026 type="#_x0000_t201" o:insetmode="auto" stroked="f"><o:lock v:ext="edit" text="t" rotation="t"></o:lock></v:shape><v:shape style="Z-INDEX: 3; POSITION: absolute; MARGIN-TOP: 0px; WIDTH: 48pt; HEIGHT: 12.75pt; VISIBILITY: hidden; MARGIN-LEFT: 0px" id=_x0000_s1027 type="#_x0000_t201" o:insetmode="auto" stroked="f"><o:lock v:ext="edit" text="t" rotation="t"></o:lock></v:shape><v:shape style="Z-INDEX: 4; POSITION: absolute; MARGIN-TOP: 0px; WIDTH: 48pt; HEIGHT: 12.75pt; VISIBILITY: hidden; MARGIN-LEFT: 0px" id=_x0000_s1028 type="#_x0000_t201" o:insetmode="auto" stroked="f"><o:lock v:ext="edit" text="t" rotation="t"></o:lock></v:shape><v:shape style="Z-INDEX: 5; POSITION: absolute; MARGIN-TOP: 0px; WIDTH: 48pt; HEIGHT: 12.75pt; VISIBILITY: hidden; MARGIN-LEFT: 0px" id=_x0000_s1029 type="#_x0000_t201" o:insetmode="auto" stroked="f"><o:lock v:ext="edit" text="t" rotation="t"></o:lock></v:shape><v:shape style="Z-INDEX: 6; POSITION: absolute; MARGIN-TOP: 0px; WIDTH: 48pt; HEIGHT: 12.75pt; VISIBILITY: hidden; MARGIN-LEFT: 0px" id=_x0000_s1030 type="#_x0000_t201" o:insetmode="auto" stroked="f"><o:lock v:ext="edit" text="t" rotation="t"></o:lock></v:shape><v:shape style="Z-INDEX: 7; POSITION: absolute; MARGIN-TOP: 0px; WIDTH: 48pt; HEIGHT: 12.75pt; VISIBILITY: hidden; MARGIN-LEFT: 0px" id=_x0000_s1031 type="#_x0000_t201" o:insetmode="auto" stroked="f"><o:lock v:ext="edit" text="t" rotation="t"></o:lock></v:shape><v:shape style="Z-INDEX: 8; POSITION: absolute; MARGIN-TOP: 0px; WIDTH: 48pt; HEIGHT: 12.75pt; VISIBILITY: hidden; MARGIN-LEFT: 0px" id=_x0000_s1032 type="#_x0000_t201" o:insetmode="auto" stroked="f"><o:lock v:ext="edit" text="t" rotation="t"></o:lock></v:shape><v:shape style="Z-INDEX: 9; POSITION: absolute; MARGIN-TOP: 0px; WIDTH: 48pt; HEIGHT: 12.75pt; VISIBILITY: hidden; MARGIN-LEFT: 0px" id=_x0000_s1033 type="#_x0000_t201" o:insetmode="auto" stroked="f"><o:lock v:ext="edit" text="t" rotation="t"></o:lock></v:shape><v:shape style="Z-INDEX: 10; POSITION: absolute; MARGIN-TOP: 0px; WIDTH: 48pt; HEIGHT: 12.75pt; VISIBILITY: hidden; MARGIN-LEFT: 0px" id=_x0000_s1034 type="#_x0000_t201" o:insetmode="auto" stroked="f"><o:lock v:ext="edit" text="t" rotation="t"></o:lock></v:shape><v:shape style="Z-INDEX: 11; POSITION: absolute; MARGIN-TOP: 0px; WIDTH: 48pt; HEIGHT: 12.75pt; VISIBILITY: hidden; MARGIN-LEFT: 0px" id=_x0000_s1035 type="#_x0000_t201" o:insetmode="auto" stroked="f"><o:lock v:ext="edit" text="t" rotation="t"></o:lock></v:shape>ID</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 95pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 width=127>Year of Entry</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 46pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 width=61>Class</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 56pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 width=74>Level</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 width=64 align=right x:num>2001</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 width=64 align=right x:num>2002</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 width=64 align=right x:num>2003</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 width=64 align=right x:num>2004</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 width=64 align=right x:num>2005</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 width=64 align=right x:num>2006</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 width=64 align=right x:num>2007</TD></TR></TBODY></TABLE>

Each Row is a unique Student, and the other data is as follows;
Year of Entry = 1998 - 2001
Class = Math, English, German etc...
Level = Honours, Advanced, Introduction

And for each of the "Year" columns (2001,2002 etc...), their could be grades such as Good, Exceeded, Outstanding, Average, No Rating.

So I think what I want to do is either have the User choose which "Year" of Grading to look at, or count how many "Good", "Exceeded" etc... for each of the "Years".

Thanks

Ronan

 

[jim may]

Post a representative sample of your Data table

 

[Ronanm]

Thanks Jim, I have a sample in MyDocuments, how do I upload?

Ronan

 

[jim may]

See my signature line for 2 options

 

[Ronanm]

Thanks Jim. I have just found a place to share files. So it's at http://www.box.net/shared/z6t90z324v

Thanks

Ronan

 

[jim may]

FIRST, I HTML Maker Sw DOES NOT PERMIT Over 11 Columns, so here's the First 11 -- Then afterwards I pasted Only My Column M (The Totals) - But anyway, I only grabbed only your FIRST 5 IDs to Test.

I had to Rearrange the Data Table you posted - It is not in PT datatable format needed to prepare my example. Does what I show below look like what you want?

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L
1
2
3	Count of Score					Yr
4	ID	Year of Entry	Class	Level	Score	2001	2002	2003	2004	2005	2006	2007
5	6	2000	Math	Advanced	Average			1
6					Exceeded		1		1
7					Good	1
8	8	1996	French	Introduction	Exceeded		1		1
9					Good					1	1	1
10					Outstanding	1
11	11	2000	Math	Introduction	Exceeded	1	1	1
12					Good					1	1	1
13					No Rating				1
14	12	1998	English	Advanced	Average	1
15					Exceeded			1	1		1	1
16	16	2000	Math	Honours	Exceeded			1	1	1
17					Good	1	1				1	1
18	Grand Total					5	4	4	5	3	4	4
Sheet4

Excel 2007

Excel Workbook
	M
1
2
3
4	Grand Total
5	1
6	2
7	1
8	2
9	3
10	1
11	3
12	3
13	1
14	1
15	4
16	3
17	4
18	29
Sheet4

Excel 2007

 

[Ronanm]

Many thanks Jim

Hmmn. Yes, looks like what I'm looking for. It's the "It is not in PT datatable format needed to prepare " bit that worries me ).
I will look into "preparing" the data first so that it's Pivot friendly.

Thanks

Ronan

 

[jim may]

Here's my sample Data Table upon which the PT is Based; Good Luck

Excel Workbook
	A	B	C	D	E	F
1	ID	Year of Entry	Class	Level	Yr	Score
2	6	2000	Math	Advanced	2001	Good
3	6	2000	Math	Advanced	2002	Exceeded
4	6	2000	Math	Advanced	2003	Average
5	6	2000	Math	Advanced	2004	Exceeded
6	8	1996	French	Introduction	2001	Outstanding
7	8	1996	French	Introduction	2002	Exceeded
8	8	1996	French	Introduction	2004	Exceeded
9	8	1996	French	Introduction	2005	Good
10	8	1996	French	Introduction	2006	Good
11	8	1996	French	Introduction	2007	Good
12	11	2000	Math	Introduction	2001	Exceeded
13	11	2000	Math	Introduction	2002	Exceeded
14	11	2000	Math	Introduction	2003	Exceeded
15	11	2000	Math	Introduction	2004	No Rating
16	11	2000	Math	Introduction	2005	Good
17	11	2000	Math	Introduction	2006	Good
18	11	2000	Math	Introduction	2007	Good
19	12	1998	English	Advanced	2001	Average
20	12	1998	English	Advanced	2003	Exceeded
21	12	1998	English	Advanced	2004	Exceeded
22	12	1998	English	Advanced	2006	Exceeded
23	12	1998	English	Advanced	2007	Exceeded
24	16	2000	Math	Honours	2001	Good
25	16	2000	Math	Honours	2002	Good
26	16	2000	Math	Honours	2003	Exceeded
27	16	2000	Math	Honours	2004	Exceeded
28	16	2000	Math	Honours	2005	Exceeded
29	16	2000	Math	Honours	2006	Good
30	16	2000	Math	Honours	2007	Good
Sheet3

Excel 2007

 

[Ronanm]

Jim

Great. I'll have a new sheet, which "look up" up the data sheet, and have it repeating the records as required. Many thanks. Appreciate it



Ronan