Pivot Table Issues

How to Count Employee Names in Excel Using a PivotTable

Phi Hung Vo Sep 25, 2026 869 views

Question details

The user needs an automated way to find out how many times each employee's name appears in a list using a PivotTable, avoiding the manual process of counting and removing duplicate rows.

How to Count Employee Names in Excel Using a PivotTable

Product: Excel
Device & OS: not provided
Scenario: Analyzing a training attendance or course registration list to determine how frequently each employee participated.
Observed behavior: Requires a method to generate a unique list of employees alongside their occurrence count directly from raw source data.

Before you start

Ensure your source data has clear column headers, such as 'Employee Name', and remove any empty rows within the dataset so the PivotTable can generate accurately.

Solution 1Recommended

Use a PivotTable to Count Employee Occurrences

This is the fastest and most reliable method to extract a unique list of names and their respective counts without altering your original data.

PivotTables excel at summarizing categorical data. By placing a text field into the Values area, the system automatically defaults to counting the instances, giving you a duplicate-free summary instantly.

Select your source data

Highlight the entire range of cells containing your employee list, making sure to include the column header (e.g., 'Employee Name').

Insert the PivotTable

Go to the 'Insert' tab on the top ribbon and click 'PivotTable'. Choose whether you want to place the PivotTable on a New Worksheet or an Existing Worksheet, then click 'OK'.

Configure the Rows area

In the PivotTable Fields pane on the right side of your screen, click and drag the 'Employee Name' field into the 'Rows' area. This immediately creates a list of unique names on your sheet.

Configure the Values area

Drag the exact same 'Employee Name' field into the 'Values' area. Excel will automatically apply the 'Count of Employee Name' calculation, displaying the number of times each employee appears next to their name.

Use a PivotTable to Count Employee Occurrences

Dynamic Data Counting: If you add more data to your list later, you do not need to rebuild the table. Simply right-click anywhere inside the PivotTable and select 'Refresh' to update the counts.

Solution 2

Alternative: Use UNIQUE and COUNTIF Functions

If you prefer using formulas instead of a PivotTable, you can extract unique names and count them using built-in Excel functions.

Analyze Data Easily

Count Names and Summarize Data Faster with WPS Spreadsheet

WPS Spreadsheet offers powerful, user-friendly PivotTable features that allow you to summarize data, count text occurrences, and filter large datasets in seconds without complex formulas.

1. Open your dataset: Launch WPS Spreadsheet and open the file containing your employee list.
2. Insert a PivotTable: Select your data range, navigate to the 'Insert' tab on the ribbon, and click 'PivotTable'.
3. Count the names instantly: Drag the employee name field into both the 'Rows' and 'Values' sections of the PivotTable field list to instantly get your unique counts.

Create PivotTables easily with an intuitive drag-and-drop interface perfect for counting names.Fully compatible with Microsoft Excel formats (.xlsx, .xls, .csv).Includes advanced sorting and filtering options for quick data analysis.Lightweight software that handles large datasets smoothly without lagging.

microsoft office alternative - wps office

Frequently Asked Questions

Why does my PivotTable show a 'Sum' instead of 'Count' for names?

If your employee names column contains numbers or is formatted incorrectly as numerical data, the PivotTable might default to 'Sum'. To fix this, click on the field in the 'Values' area, select 'Value Field Settings', and change the calculation type from 'Sum' to 'Count'.

How do I filter out certain employees from the PivotTable count?

You can filter the data directly within the PivotTable. Click the drop-down arrow next to 'Row Labels' in the PivotTable, and uncheck the names of the employees you want to exclude from the view. The counts will automatically adjust.

Will the PivotTable update automatically if I add new employee names to the list?

No, PivotTables do not update automatically. After adding new data to your source list, right-click anywhere inside the PivotTable and select 'Refresh' to update the counts. Converting your source data into an official Table (Ctrl+T) before creating the PivotTable ensures new rows are included during the refresh.

Excel & Data Problems