Pivot Table Query

The_Hurricane

Hi All,

I've two quick pivot table queries and this is something that has always confused me. I've setup a quick dummy spreadseet at the moment with 5 colums, one for name and four for varuous types of tests taken and I've populated the table with the test results.

Now my query is when I pivot this I have put the test results into the value field however they appear under the heading sum of test 1 and sum of test 2 etc. When I attempt to rename the headings to simply "Test 1"etc the error message 'PivotTable field names already exists' appears and the only way around this is to leave a space at the start of the name - this is my first issue why does this message appear when the name has not been used before?

My second and final query is when I rename the value headings with the space at the start of each name - if I select them on the pivot menu to remove it and then re-add it, it reappears under the name "Sum of Test 1", how can I prevent this?

Thanks in advance.

WTFH

The reason you get the "error" is because the original data column title was Test 1. You can't have a field and a calculated result on that field having the same name.
The reason it changes to "Sum of Test 1" is because you can change that to be Sum, Average, Count, Max, Min ... etc and it lets you know what calculation it's actually performing.
You could have one set of calculations being the Sum, another being the Count, etc.

rmg1

The short answwer is you can't.
If you display the pivot table field list and click on one of the field in the Values box, you can rename it there. You still can't use the same name as the field it refers to though.
As for stopping it reverting back to the original (i.e. "Sum of....") when you remove and reinsert the field, I don't think that can be done without code.

The_Hurricane

WTFH wrote: »

The reason you get the "error" is because the original data column title was Test 1. You can't have a field and a calculated result on that field having the same name.
The reason it changes to "Sum of Test 1" is because you can change that to be Sum, Average, Count, Max, Min ... etc and it lets you know what calculation it's actually performing.
You could have one set of calculations being the Sum, another being the Count, etc.

Is this only the case for calculated values?

WTFH

Pivot tables are only there for calculating values!

The_Hurricane

What about columns that just have people's names in them - are these excempt from being changed?

WTFH

You can't display the names in the calculate output. You can display them in the headings, but not in the calculation.

Based on the questions you've asked over the last few days, I'd recommend you go on a training course, buy a book, or get some online training