Pivot Tables

(This is not going to format right, I know it.

COLUMN

ROW

DATA

all in pretty (and useless and annoying boxes).

I have category in Row and Details in the Data bit as I want to sum up totals. Can't get sums of data in either Row or Column fields.

There must be a way of doing this?

(Incidentally Kate, how are you logging on without a PC? Have you a fancy phone? Or did you get that ILX chip planted in your brain??!)

You drop your category by which you want to total into either the row or the column. If you want subtotals by different categories, you can put a category in each.

Put what it is you want to count or total or work with into the Data bit. You can double/right click on it to specify exactly what you want to do - make it a count, a total, a percent or whatever.

What I have is this:
Person
Lara Sum Apples 5
Sum Pears 4

Kate Sum Apples 6
Sum Pears 3

What I want is this:

Person Sum of Apples Sum of Pears
Lara 5 4

Kate 6 3

Currently I have Person in ROW and Sum of Apples and Sum of Pears in Data. Where should I have them to get the layout I want?

Person Detail
Kate Apple
Kate Apple
Kate Apple
Kate Apple
Kate Apple
Kate Pear
Kate Pear
Kate Pear
Kate Pear
Lara Apple
Lara Apple
Lara Apple
Lara Apple
Lara Apple
Lara Apple
Lara Pear
Lara Pear
Lara Pear

Put "person" into ROW put "detail" into COLUMN then put "count of data" into DATA

And you should get
Person - Apples - Pears - Grand Total (per person)
Kate - 5 - 4 - 9
Lara - 6 - 3 - 9
Grand Total (per fruit) - 11 - 7 - 18

Am I getting warmer? Or if your data is formatted differently, let me know and I'll work it out from there.

Person Apple Pear Banana Lithium Spray
Lara 5 4 5 3
Kate 6 3 2 2

etc etc.

I am too square (and tired) to go the hipster/geek way.

I will have another play in the office tomorrow. Kate, thank you so much for your help. You've kept me sane. Any more words of wisdom related to the my data layout very much appreciated.

Can it be done ladies and gentlemen? Can it be done?

Get it back so it looks like that. Here's something which seems to work for me, but I don't know why and I don't know what I'm doing, so I can't guarantee it'll help:
- go to the PivotTable toolbar (if it's hidden then right-click anywhere on your PivotTable and select Show PivotTable toolbar)
- press the http://www.briarcliff.edu/cis/Excel/009-Pivot-Tables_files/image010.jpg Format Report button
- choose any of the first few AutoFormat report styles (Report 1 is fine)
- don't panic about the garish colours, they'll go away in a minute (I hope. If not, you can undo all of it)
- go back to the http://www.briarcliff.edu/cis/Excel/009-Pivot-Tables_files/image010.jpg Format Report button, scroll down to the bottom of the list and choose PivotTable Classic

I don't know why this does what it does or why you have to set it to Report 1 and not go straight to PivotTable Classic, I don't know whether there's an easier way (but there must be), and I don't know if it'll work for you, but it works for me.

The first question is, in this case, are you sure that what you want is a Pivot Table? Your data already contains totals per person, so it might be easier to put a manual sum of all fruit at the bottom of every column? That is, if all you want is the totals.

If you want to do charts and graphs off your pivot table, then reverse your axises. (Axes?) It won't *look* exactly the way you want it (unless you tilt your head) but it will behave the way you want it to.

Your layout would be "person" in COLUMN and DATA would have "sum of apples" and "sum of pears".

The table will be the wrong way around, but what you will have is:

data - Kate - Lara - Grand Total
Sum of Apple - 5 - 3 - 11
Sum of Pear - 4 - 3 - 7

(I'm just trying to figure out why it won't give totals per person that way - probably because there is nothing in the ROW field. Grrr, if I were doing this on a PC, I would know all the right clicks to get at it, but I can't seem to find them on this Mac.)

So listing the caregories across the columns is not an option. Similarly if I have the categories in the Rows, I get three rows per category (one for each sum) so I have approx 2,400 rows, which is huge. I need to be able to display the data in a more efficient manner.

Beyond using a pivot table, what else would you suggest?

The data is like this to be honest

Category Pear Apple Banana Fruit ID

Lara 4 3 7 A
Kate 1 3 8 B
Kate 4 6 7 C
Lara 7 4 6 D

So because each category (i.e. Lara or Kate has a unique Fruit ID or whatever) I thought using a Pivot Table was the best way of displaying the data. Perhaps Pivot Tables weren't invented to look pretty?

Let me have a go - this is really good procrastination material, because I feel like I'm actually learning good job skills instead of just mucking about on the internet.

Sort all your data by category. From your Data menu, select Subtotals. At each change in "category", use function "sum". Add subtotal to, select "apple" and "pear" and "banana" and deselect the others. Say "Yes" to replace current subtotals, and click "summary below data" for a nice summing up bit at the bottom.

Now you will have even more rows than you had before, but panic not. In the left hand column which has now appeared next to your data, click the little minus signs to hide the data and leave your subtotals. (Level 2 is the one you want to hide - if you click Level 1, all your data will disappear except your grand totals!)

Category - apple - pear - banana
Kate Total - 8 - 9 - 14
Lara Total - 8 - 7 - 14
Grand Total - 16 - 16 - 28