Function Problems

How to Find the 4th, 5th, and 6th Most Frequent Text Values in Excel

Tauseeq MagsiTauseeq Magsi Nov 4, 2026 869 views

Question details

The user wants to identify the fourth, fifth, and sixth most frequently occurring text values (such as English letters) within a dataset without using overly complex array formulas.

How to Find the 4th, 5th, and 6th Most Frequent Text Values in Excel
Product
Excel
Device & OS
not provided
Scenario
Analyzing non-numeric data to extract and rank text frequencies beyond the top three most frequent items.
Observed behavior
Standard functions like MODE only return the single most frequent number, making it difficult to extract the Nth most frequent text values natively.
Before you start

Ensure your text dataset does not contain unintended blank cells, trailing spaces, or inconsistent capitalization, as these can cause identical letters or words to be counted as separate unique values.

Solution 1Recommended

Create a Summary Table Using COUNTIF and RANK.EQ

This lightweight approach creates a simple summary table to count the occurrences of each text value and ranks them, allowing you to easily filter for the 4th, 5th, and 6th most frequent items.

While complex array formulas can extract the Nth most frequent text directly, they are often resource-intensive and difficult to maintain. Using a helper table with COUNTIF and RANK.EQ is a much simpler, cleaner, and faster solution for non-numeric data.

1
Extract unique values

Copy your entire column of text data and paste it into a new, empty column. Keep this new column selected, navigate to the Data tab on the ribbon, and click 'Remove Duplicates' to leave only a list of unique text entries.

2
Count frequencies

In the cell directly next to your first unique value, type the formula =COUNTIF(A:A, C2) (assuming your original data is in column A and your unique value is in C2). Drag this formula down to calculate the frequency for every unique text value.

3
Rank the counts

In the next adjacent column, type the formula =RANK.EQ(D2, D:D) (assuming your counts are in column D). Drag this formula down. This assigns a numerical rank based on how high the count is.

4
Filter the results

Select your new summary table headers, go to the Data tab, and click 'Filter'. Click the filter drop-down arrow on the Rank column and select only the numbers 4, 5, and 6 to display your desired results.

Create a Summary Table Using COUNTIF and RANK.EQ
Handling Tied Frequencies: If multiple text values appear the exact same number of times, RANK.EQ will assign them the same rank (e.g., two items tied for 4th will both be ranked 4, and the next item will be ranked 6).
Efficient Data Analysis

Find Most Frequent Values Easily with WPS Spreadsheet

WPS Spreadsheet provides intuitive data analysis tools, including advanced PivotTables, instant Duplicate Removal, and over 400 built-in functions. It makes counting, ranking, and extracting the most frequent text values incredibly easy without slowing down your computer.

  1. 1. Open your data in WPS Spreadsheet: Launch WPS Office and open your spreadsheet containing the text data.
  2. 2. Create a Pivot Table: Select your data column, click 'Insert' from the top ribbon, and choose 'PivotTable'.
  3. 3. Drag and drop fields: Drag your text field into both the 'Rows' box and the 'Values' box to instantly calculate the frequency of each unique entry.
  4. 4. Sort to find Nth values: Right-click the values column, choose 'Sort' > 'Descending', and easily view your 4th, 5th, and 6th most frequent text values.
Fully compatible with Microsoft Excel (.xlsx) formats and standard formulas like COUNTIF and RANK.EQ.Lightweight architecture ensures fast calculation speeds even when processing thousands of text entries.Intuitive Pivot Table interface for instant frequency counting and sorting without typing a single formula.Free to use, offering a seamless and familiar interface for all your data analysis needs.
QA img-9

Frequently Asked Questions

Can I use the MODE function to find the most frequent text in Excel?

No, the MODE, MODE.SNGL, and MODE.MULT functions only work with numeric values. They will return an error if you attempt to use them on text data like English letters or names. You must use COUNTIF or a Pivot Table for text.

Is there a single formula to find the 4th most frequent text value?

Yes, but it requires a complex array formula using INDEX, MATCH, COUNTIF, and LARGE functions. For example: =INDEX(A:A, MATCH(LARGE(COUNTIF(A:A, A:A), N), COUNTIF(A:A, A:A), 0)). However, this is highly resource-intensive and not recommended for large datasets.

How do I break ties when ranking the most frequent text values?

If using the RANK.EQ method, you can break ties by adding a tiny sequential fraction to your counts. For example, instead of ranking the raw count, rank the result of =COUNTIF(A:A, C2) + (ROW()/100000). This ensures every count is technically unique, preventing duplicate ranks.