Pivot Table - Adding a % of Total Column along with Grand Totalcolumn

[mscirri]

I have a set of data with the follwing information

Employee Name Type
-------------------------------------------
Joe Single
Marc Double
Frank Single
Alex Single


My Pivot Table looks like this:

Count of Type Type
Employee Name 1-Single 2-Double Grand Total
Frank 53 14 67


I want it to look ike this:

Count of Type Type
Employee Name 1-Single 2-Double Grand Total Pct Singles
Frank 53 14 67 79.1%


I have tried everything. I need the column to be a part of the table
because the data changes. This table is going to be part of a
template. Is this even possible?

 

[Bill Ridgeway]

I have a set of data with the follwing information

Employee Name Type
-------------------------------------------
Joe Single
Marc Double
Frank Single
Alex Single


My Pivot Table looks like this:

Count of Type Type
Employee Name 1-Single 2-Double Grand Total
Frank 53 14 67


I want it to look ike this:

Count of Type Type
Employee Name 1-Single 2-Double Grand Total Pct Singles
Frank 53 14 67 79.1%


I have tried everything. I need the column to be a part of the table
because the data changes. This table is going to be part of a
template. Is this even possible?

You can use the column(s) to the right and below the pivot table to further
manipulate data in the Pivot table.

Bill Ridgeway
Computer Solutions

 

[mscirri]

You can use the column(s) to the right and below the pivot table to further
manipulate data in the Pivot table.

Bill Ridgeway
Computer Solutions- Hide quoted text -

- Show quoted text -

But as soon as someone adds a column to the Pivot Table the formulas
are all jacked up because they are not linked to the table and
formulas that reference Pivot Table data do not copy properly.

 

[Herbert Seidenberg]

In Layout, drag another "Type" into DATA.
DoubleClick on "Count of Type2"
Field Settings > Options > Show data as > % of row
http://www.freefilehosting.net/download/3bagd

 

[Marc S]

In Layout, drag another "Type" into DATA.
DoubleClick on "Count of Type2"
Field Settings > Options > Show data as > % of rowhttp://www.freefilehosting.net/download/3bagd

When I do that I get an error trying to double-click on Count of
Type2:

Cannot show or hide detail for this selection.
This message can appear when:
You are looking at either the highest or lowest level of detail for a
given dimension or field in a PivotTable report, and no more
information is available.
You are looking for data in an empty cell.
To solve the problem, add more fields to the report. For more
information about PivotTable reports, see the following topics:

 

[Herbert Seidenberg]

If you are in the Layout window, doubleclick "Count of Type2"
If you are at the finished PT, rightclick "Count of Type2"
Or leftclick "Count of Type2" on the PT,
view the PT toolbar and look for Field Settings.
Or open my previously attached xls file
and play with it.

 

[Marc S]

If you are in the Layout window, doubleclick "Count of Type2"
If you are at the finished PT, rightclick "Count of Type2"
Or leftclick "Count of Type2" on the PT,
view the PT toolbar and look for Field Settings.
Or open my previously attached xls file
and play with it.

That adds the percent to the row but not as a column.

 

[Roger Govier]

Hi
Not certain what you mean here.
Herbert has given you a great example.
If you are saying you want see

Single Double
Count % Count %
then hover over the cellH9 (Type) until you see a 4 sided arrow. Left click
and drag to the left dropping it on G9

If you are saying you want to see the Percentages below the Count, in the
same column, then drag Data to column F

 

[Marc S]

Hi
Not certain what you mean here.
Herbert has given you a great example.
If you are saying you want see

     Single                Double
Count     %            Count     %
then hover over the cellH9 (Type) until you see a 4 sided arrow. Left click
and drag to the left dropping it on G9

If you are saying you want to see the Percentages below the Count, in the
same column, then drag Data to column F

--
Regards
Roger Govier







- Show quoted text -

This is what I need:

Count of Type Type
Employee Name 1-Single 2-Double Grand Total Pct
Singles
Frank 53 14
67 79.1%


Right now I have the Pct of Total as a separate row for eac employee
and that is not what I want.

 

[Marc S]

This is what I need:

Count of Type   Type
Employee Name   Single   Double  Grand Total  Pct Singles
Frank                    53       14 67              79.1%

Hopefully this message formats correctly...

 

[Roger Govier]

Hi Marc

You cannot have anything in a Pivot Table to the right of Grand Total.
Grand Total is the last column.
You could create data in a column outside the Pivot Table, using the
GetPivotData function to extract the required data from the PT itself.

Take a look here for more information
http://www.contextures.com/xlPivot06.html