Summary

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

Support Exceljet

Many websites (including Exceljet) have lost over half their traffic in the past year because of the crushing impact of AI on web searches. Exceljet is still at it, publishing free content each week. If you find Exceljet helpful, you can support the site and go ad-free with the Exceljet Lifetime Pass. One payment, no subscription, and ads are gone forever. Plus, you get our Excel Shortcuts video course as a thank you.

Get the Lifetime Pass