Exceljet

Quick, clean, and to the point

Pivot table count with percentage

To display data in categories with a count and percentage breakdown, you can use a pivot table. In the example shown, the field "Last" has been added as a value field twice – once to show count, once to show percentage. The pivot table shows the count of employees in each department along with a percentage breakdown.

Fields

The pivot table shown is based on two fields: Department and Last. The Department field is configured as a Row field, and the Last field is a Value field, added twice:

Pivot table count with percentage field configuration

The Last field has been added twice as a value field. The first instance has been renamed "Count", and set summarize by count:

Pivot table count with percentage count settings

The second instance has been renamed to "%". The summarize value setting is also Count, Show Values As is set to percentage of grand total:

Pivot table count with percentage - percentage settings

Pivot table count with percentage - percent of grand total

Steps

  1. Create a pivot table
  2. Add Department as a Row field
  3. Add Last as a Value field
    1. Rename to "Count"
    2. Summarize by Count
  4. Add Last as a Value field
    1. Rename to "%"
    2. Summarize by Count
    3. Display Percent of Grand Total
    4. Change number formatting to percentage

Pivot Table Training

If you use Excel, but don't know how to use Pivot Tables, you're missing out...wasting time trying to do things that a Pivot Table can do for you automatically. Core Pivot is a step-by-step Excel video course that will teach you everything you need to know to use this powerful tool. With a small investment, Pivot Tables will pay you back again and again. See details here.