Pivot Table Issues

How to Use an Excel Pivot Table to Count and Filter Unique Values

Anum Mushtaq Oct 16, 2026 868 views

Question details

The user wants to replicate the WPS Office 'Export List' feature in Microsoft Excel to filter values and count how many times each value occurs.

How to Use an Excel Pivot Table Like the WPS Office Export List

Product: Microsoft Excel
Device & OS: not provided
Scenario: Summarizing dataset values to find unique entries and their frequency counts.
Observed behavior: The user needs a method to generate a new worksheet that lists unique data values alongside their total number of occurrences, mimicking specialized data export tools.

Before you start

Ensure your dataset is organized in a tabular format with clear column headers, and remove any completely blank rows to guarantee accurate counting.

Solution 1Recommended

Create an Excel Pivot Table to Count Value Occurrences

Use Microsoft Excel's Pivot Table functionality to extract unique values from a column and count their frequency automatically.

A Pivot Table is the most efficient way to summarize occurrences in Excel without needing complex formulas like COUNTIF. It is fully supported in Excel for Windows, Mac, and the web.

Select the source data

Highlight the entire data range, including the column headers, that contains the values you want to summarize.

Insert a PivotTable

Navigate to the 'Insert' tab on the Excel ribbon and click on the 'PivotTable' button.

Choose placement

In the prompt window, select 'New Worksheet' to keep your summary separate from the raw data, then click 'OK'.

Configure the Rows area

In the PivotTable Fields pane on the right, drag the target column name into the 'Rows' area. This will display a list of unique values.

Configure the Values area

Drag the exact same target column into the 'Values' area. Excel will automatically apply the 'Count' calculation if it contains text. You can now use the drop-down arrows on the row labels to apply filters.

Create an Excel Pivot Table to Count Value Occurrences

Mobile App Limitation: Creating new Pivot Tables is currently not supported in the Excel mobile applications for iOS and Android. You must use the desktop or web versions.

Advanced Data Summarization

Count and Summarize Data Easily with WPS Spreadsheet

WPS Spreadsheet provides highly intuitive Pivot Table features and advanced data extraction tools. You can rapidly count occurrences, filter unique values, and generate summary worksheets with just a few clicks.

1. Open your dataset: Launch WPS Spreadsheet and open the document containing your data.
2. Insert a PivotTable: Select your data range, click the 'Insert' tab, and select 'PivotTable'.
3. Set up the summary fields: Drag the column you wish to analyze into the 'Rows' box to extract distinct entries.
4. Generate counts: Drag the same column into the 'Values' box to automatically generate the frequency count for each entry.

100% compatibility with Microsoft Excel (.xlsx) file formats and Pivot Table structures.Intuitive drag-and-drop interface for quick data summarization and counting.Lightweight software that runs smoothly on Windows, Mac, Linux, iOS, and Android.Free built-in data analysis tools that save time compared to writing manual formulas.

Frequently Asked Questions

Why is my Pivot Table showing 'Sum' instead of 'Count'?

If your source column contains numerical data, Excel defaults to summing the values instead of counting them. 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 update the count if I add new data to my original list?

Pivot Tables do not update automatically. To include new data, right-click anywhere inside the Pivot Table and choose 'Refresh'. If you added rows outside the original data range, you may need to go to 'PivotTable Analyze' > 'Change Data Source' first.

Can I filter out blank rows from my Pivot Table results?

Yes. Click the filter drop-down arrow next to the 'Row Labels' header in your Pivot Table, uncheck the box next to '(blank)', and click OK to hide empty entries from your count.

Excel & Data Problems