Pivot Tables: Pivot table layout

You should just be able to change the format of the totals to normal and leave the percentages unchanged. Just right click the total cels and select format cells and choose what you like.

Interesting - I can change the format of any cell in a pivot table (using the toolbar formating icons) and nothing else changes.

Try using toolbar icons instead

HTH

Quote

Originally posted by kiwichico
Hi everyone,
This may be a very novice question, but I like to know if there is a way to display a table as column percentages but have the totals as raw numbers.
Thanks in advance.

Perhaps theer is misunderstanding of the question. Is your pivot table showing the entries as a percent of the column total and you want the total of that column to show as a rwaw number rather than as 100%? If so, I don't know how to do it in the same column.

Re: Pivot Tables: Pivot table layout

I'm also new to pivot tables and would like to know if I can get a percentages column to show in the column to the right of the actual data columns. The percentage figure would be of the grand total. I would also need a percentage of each column total as a pecentage of the grand total.

Re: Pivot Tables: Pivot table layout

Yes, see the attached. One of the options for a field in the data section is to show as a percent of the column.

Re: Pivot Tables: Pivot table layout

Derk,

I can't understand how the percentage column has been achieved in your pivot table. The data just shows a long series of "1"'s.

I tried changing the field settings for Percent to my data, but it changed the actual data which is not what I wanted.
I then inserted a column with just 1 in reach row as you have in yours, but then I couldn't work out how to produce it in the pivot table and then it didn't show up in the pivot table anyway.

If possible, I'd rather not have a percent field in my data area as the percentage element is only needed once data is produced in the pivot table and also only for certain data, not all of it.
Does the pivot table need a separate column just for percentages ?

If I had to have a percentage column, I could just hide it from users.

Can you tell me what I am doing wrong.

--------------------------------------
addendum

Ater I made the pivot table, I inserted a column next to a data column and tried to change the field settings for % but when I tried to select from the list of options that this column is as a percentage of the previous column, the bottom left box with chocies was "greyed out", stopping me from making a choice.

Re: Pivot Tables: Pivot table layout

The 1s were just a quick way to generate some data for the pivot table to process. If you look at the layout of the pivot table in the pivot table wizard, you will see that there are twoentries in the Data section. They both started out by dragging the b tile into the data section. The first b tile was given the default Sum. For the second, double click on it and then if the options are not shown, click on options. You will see that it has been set to show data as % of column. So the same field is shown in two different forms at the same time.

Re: Pivot Tables: Pivot table layout

what do you mean by 'b tile' please ??

Re: Pivot Tables: Pivot table layout

In my example the data column with the numbers was labeled b. In the pivot table wizard, the grey rectangles you can drag to the various areas are usually called tiles, and they are labeled with the headers of the data columns. So one of them has the label b.

Re: Pivot Tables: Pivot table layout

Yes, I've got it know.
But one thing still isn't how i'd like it.
I want the % columns to appear after each of the data columns. I can't seem to find a way to set the tabel in this way. The best I can do is to have the percentage shoing underneath each bit of data, so the column is showing alternate data, %, data, % and on.
I've tried dragging and moving around and all I get is undesirable results.

Re: Pivot Tables: Pivot table layout

You should be able to drag in the table to get the data fields to stretch horizontally instead of vertically. Click on the Data heading tile and drag slightly to the right until the little blue border (in the popup that shows when you start to drag) changes from vertical to horizontal. Can you attach your workbook?

Re: Pivot Tables: Pivot table layout

I thought I had it but now, when I tried to make the second column of data as "% of column" in options, the list in the bottom left box is greyed out so I can't choose anything.

Also, when I carried out the above move you recommended, there was another column for % but every cell had #N/A in them.

Re: Pivot Tables: Pivot table layout

Hard to say what the problem is without seeing your workbook. Can you attach it?

Re: Pivot Tables: Pivot table layout

Sample File attached as requested.

Thank you.

Re: Pivot Tables: Pivot table layout

The needed fix was to set the option for the % field to "% of column" rather than leaving it at the earlier option of "% of"

See the attached

Re: Pivot Tables: Pivot table layout

I did try that but it didn't work - but thanks for working it out for me.
Now, how do I get the percentages to appear as a column of data, instead of in rows ?
When I tried to move the percenta data to the column area, it produced massive percentage figures. I just don't understand why this is all so hard.
This is all just so tedious.

Re: Pivot Tables: Pivot table layout

Just drag the tile as described earlier. See attached.