Pivot Table Help

[Deb G.]

Here is a small sample of a spreadsheet that I am trying to create a pivot table from:

DEPARTMENT Q1 Q2 Q3 Q4
Quality 1 3 1 8
Quality 1 3 1 2
IT 1 2 1 3
IT 2 1 2 2


I need to know how many times (per department) a rating of 1 through 8 is given for each question. In my example, Quality has a rating of "1" twice for question 1. I think I have this set up wrong to make a pivot table because no matter which way I try, I don't get the results I want. Can anyone help with this one?

THANKS!

 

[Parra]

First Sort your data by department.
Next
Go Data, Subtotals
Choose change in each Rating
Use Count Function
Add Subtotal to Rating

This will give you a subtotal for each rating for each department. If I did not explain myself well let me know, but give it a try.

 

[lenze]

Try setting your database up with three fields.

Department Question Rating

This will allow you to create an effective PT. See Example

Book1
	A	B	C	D
4	DEPARTMENT	QUESTION	RATING
5	Quality	1	1
6	Quality	2	3
7	Quality	3	6
8	Quality	4	1
9	Quality	1	1
10	Quality	2	4
11	IT	1	3
12	IT	2	3
13	IT	1	3
...

Also, see the article here

http://support.microsoft.com/default.aspx?scid=kb;en-us;Q213920

 

[lenze]

Parra's Sub Total suggestion might work very well also. Another thing to consider, if it would be TOO much work to redo your database would be using DFUNCTIONS. They can be adapted to your current configuration.

GGood Luck

 

[Mark W.]

As suggested by lenze above you should convert your data into 1st Normal Form (1NF) to fully leverage Excel PivotTables...

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1	DEPARTMENT	QUESTION	RATING		CountofRATING		RATING
2	Quality	Q1	1		DEPARTMENT	QUESTION	1	2	3	8	GrandTotal
3	Quality	Q2	3		IT	Q1	1	1			2
4	Quality	Q3	1			Q2	1	1			2
5	Quality	Q4	8			Q3	1	1			2
6	Quality	Q1	1			Q4		1	1		2
7	Quality	Q2	3		Quality	Q1	2				2
8	Quality	Q3	1			Q2			2		2
9	Quality	Q4	2			Q3	2				2
10	IT	Q1	1			Q4		1		1	2
11	IT	Q2	2		GrandTotal		7	5	3	1	16
12	IT	Q3	1
13	IT	Q4	3
14	IT	Q1	2
15	IT	Q2	1
16	IT	Q3	2
17	IT	Q4	2
18
Sheet2

This message was edited by Mark W. on 2002-09-05 09:12

 

[Deb G.]

Sorry I didn't get a chance to reply from work today. The solutions, as usual, are bang on. Thanks to all for your expert input!

Deb