Excel – Count and percentage with Excel pivot table

microsoft excelmicrosoft-excel-2010pivot table

I currently have three sets of columns:

Several columns with values of either true or false to indicate if a certain action has taken place.
"Helper" columns which interpret TRUE as 1 and FALSE as 0 for the columns in #1. This column is displayed as a SUM value and is grouped by 1 level.
TOTAL column which displays the total number of records. This is a value column which uses a COUNT on the record title.

A sample table demonstrating the layout is below.

Is there a way to display the count of an individual as a percentage of the count?

            TOTAL Action1 Action2 Action3
Group  1    3     3       0       1
 Record 1A  1     1       0       0
 Record 1B  1     1       0       0
 Record 1C  1     1       0       1

Group  2    3     2       2       1
 Record 2A  1     0       1       0
 Record 2B  1     1       1       0
 Record 2C  1     1       0       1

Question
My end goal is to display the number of TRUEs for each action as both a SUM for the group (already done) and percentage of the group TOTAL. How would you create a column that displays for example Action1 SUM as a percentage of TOTAL count?

Best Answer

You can simply create a new column with the following formula:

= (CELL/CELL) * 100

So if the cell [Group 1 * TOTAL] is B2 and the cell [Group 1 * Action 1] is C2, then the formula should be:

=(C2/B2)*100

Related Solutions

Excel – Adding multiple value columns to a pivot table

Whenever you have more than one item to the Values box, Excel will need to show them side by side in some way.

The default is to show both values as side-by-side columns, that's why you're seeing this "value" item under column labels. It's there because you can drag it under rows labels to have them show on top of each other.

Example with 2 values as Columns:

Year    Average      Sum of 
       sale price    profit
2011        $50        $300
2012        $60        $300
Total       $55        $600

Edit: Here's how I would approach your example:

enter image description here

Or:

enter image description here

Excel – Cannot sort Excel Pivot Table by two or more columns

Use a calculated field

The best way I've found to achieve this is to add a calculated field to the table that produces the correct sort order, and then to hide that column.

The sort order field needs to work by scaling each sort column up or down so that no column's bit of the output sort value overlaps with any other.

So I scale OrderCount up by a big number, and total Value down by a big number:

= (OrderCount*1000) + (Value/1000)

For Jemaine's figures above, this will produce a value of 3000.18. Bret would be 3000.07, Murray/Brian 1000.09 etc - the correct order.

If I wanted to add a third sorting condition I'd have to scale that down by an even bigger number:

= (OrderCount*1000) + (Value/1000) + (3rdCondition/1000000)

Adding the Calculated field

To actually implement this in the pivot table, I need to add a column to my data table so that the calculated field can count orders. I've simply added an OrderCount column and set the value to 1 for every row:

Now we can add the calculated field. Select this option from the ribbon and enter the formula:

My pivot table now looks like this, sorted correctly.

Tidying Up

This SortOrder column might confuse other users, so I can just hide the whole column.

However, I often prefer not to hide columns (personal preference) so to make this column unobtrusive I can rename the column to (ie a blank space), and format the number column for the SortOrder values to a custom format of "";"";"". If Excel autoadjusts column widths this makes the column barely noticeable.

Best Answer

Related Solutions

Excel – Adding multiple value columns to a pivot table

Excel – Cannot sort Excel Pivot Table by two or more columns

Related Question