Excel Pivot Table help displaying data field

genny

I have a large table of data, including the following important columns within it: Teacher, Class and Topic

This is generated from our large school database export, example data might be:

Mr Jones, Class 1A, Europe
Mr Smith, Class 1A, Russia
Mr Jones, Class 1B, Latin
Mr Smith, Class 1C, Art
Mr Jones, Class 1B, Europe
etc.

I want to create a pivot report with the column (field) heading as Class Name, Row (field) names as Teacher and the dData items as Topics, giving a grid of topics taught by teachers in classes. But I don't want any numeric totalling / sums in the data area at all, just the actual relevent topics listed in a cross-tabulated report. I can't seem to get the pivot table to do this, the only options are to count, sum etc.

e.g:

__________Class 1A__Class 1B__Class 1C
Mr Jones__Europe____Latin_____
____________________Europe____
Mr Smith__Russia______________Art




Is there an option to allow this in Excel 2003?

m5rcc

Excel Help would be a better place to ask this question IMHO

GunJack

Unfortunately the formatting won't let me paste it properly, but:-

if you have your table with column headers Teacher, Class and Subject, you can set the pivot as follows....in the layout box, put Teacher and Subject into the rows, Class in the columns, and count of subject in the data field. Then, double-click Teacher and in the box that opens, select no subtotals. Try it., and if you've done it right you'll have a table something like this (but better formatted, obviously

edit: ok, thought I had it, but the forum formatting squished it - it looked ok until I submitted the post :mad:

paddyrg

GunJack wrote: »

edit: ok, thought I had it, but the forum formatting squished it - it looked ok until I submitted the post :mad:

Have you tried the # ( code) tags around the text? It uses a proportional font so layout may be easier

genny

GunJack wrote: »

Unfortunately the formatting won't let me paste it properly, but:-

if you have your table with column headers Teacher, Class and Subject, you can set the pivot as follows....in the layout box, put Teacher and Subject into the rows, Class in the columns, and count of subject in the data field. Then, double-click Teacher and in the box that opens, select no subtotals. Try it., and if you've done it right you'll have a table something like this (but better formatted, obviously

edit: ok, thought I had it, but the forum formatting squished it - it looked ok until I submitted the post

Thank-you, definitely a bit nearer the solution. Reading around it appears you can't display text in the main data part of a pivot. So I still can't get rid of the numeric count in the grid, I too have problems showing what it's like here, but will try with code tags:

So now I have:

[FONT=Courier New]              Class[/FONT]
[FONT=Courier New]Teacher  Subject  Class 1A  Class 1B  Class 1C [/FONT]
[FONT=Courier New]Mr Jones Europe   1         1[/FONT]
[FONT=Courier New]       Latin              1[/FONT]
[FONT=Courier New]Mr Smith Art                          1[/FONT]
[FONT=Courier New]     Russia   1 [/FONT]
 

Where I'm really after:

 
[FONT=Courier New]      Class 1A  Class 1B  Class 1C[/FONT]
[FONT=Courier New]Mr Jones  Europe    Latin[/FONT]
[FONT=Courier New]                Europe[/FONT]
[FONT=Courier New]Mr Smith  Russia              Art[/FONT]
 

GunJack

no, you deffo can't do it like that in a pivot unfortunately

best is as you have it, which ain't so bad