# Pivot Table Help

#### Deb G.

##### Board Regular
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!

### Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
First Sort your data by department.
Next
Go Data, Subtotals
Choose change in each Rating
Use Count Function

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.

Try setting your database up with three fields.

Department Question Rating

This will allow you to create an effective PT. See Example
Book1
ABCD
4DEPARTMENTQUESTIONRATING
5Quality11
6Quality23
7Quality36
8Quality41
9Quality11
10Quality24
11IT13
12IT23
13IT13
...

Also, see the article here

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

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

As suggested by lenze above you should convert your data into 1st Normal Form (1NF) to fully leverage Excel PivotTables...
Book1
ABCDEFGHIJKL
1DEPARTMENTQUESTIONRATINGCountofRATINGRATING
2QualityQ11DEPARTMENTQUESTION1238GrandTotal
3QualityQ23ITQ1112
4QualityQ31Q2112
5QualityQ48Q3112
6QualityQ11Q4112
7QualityQ23QualityQ122
8QualityQ31Q222
9QualityQ42Q322
10ITQ11Q4112
11ITQ22GrandTotal753116
12ITQ31
13ITQ43
14ITQ12
15ITQ21
16ITQ32
17ITQ42
18
Sheet2

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

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

Replies
0
Views
203
Replies
3
Views
152
Replies
3
Views
470
Replies
1
Views
574
Replies
0
Views
288

1,203,483
Messages
6,055,675
Members
444,807
Latest member
RustyExcel

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back