Excel Formulas to Sort Duplicates by Frequency of Occurrence

Managing repetitive data, such as tracking transactions from standard funding sources like municipal grants or angel investors, often leads to cluttered spreadsheets where critical trends remain hidden. Standard sorting tools fail to highlight which entities appear most frequently. Mastering a dynamic Excel frequency formula grants analysts immediate visual clarity, allowing you to instantly isolate and rank recurring donor IDs by their volume of occurrence.

Stipulation: This advanced sorting method requires Excel 365 or Excel 2021 to support dynamic array formulas.

Below, we will walk through the exact SORTBY, UNIQUE, and COUNTIF formula structure to seamlessly prioritize your high-frequency data.

When working with large datasets in Excel, identifying and analyzing recurring data is a common task. Whether you are analyzing customer feedback categories, tracking product sales, or auditing error logs, simply finding duplicates is rarely enough. To extract meaningful insights, you often need to list those duplicate values and sort them by how frequently they occur.

Historically, achieving this required complex VBA scripts or cumbersome helper columns. However, with the introduction of modern dynamic array formulas in Excel 365 and Excel 2021, you can now build a dynamic, self-sorting list of duplicates sorted by frequency using a single, elegant formula. In this comprehensive guide, we will explore how to construct these formulas for both modern and legacy versions of Excel, handle ties, and filter out unwanted empty spaces.

Method 1: The Modern Excel 365 Way (Dynamic Arrays)

If you are using Excel 365 or Excel 2021, you have access to dynamic array functions like UNIQUE, SORTBY, and FILTER. These functions allow you to write a formula in a single cell, and the results will automatically "spill" down into the adjacent cells.

The Core Formula

To extract a unique list of items from a range and sort them by frequency (from most frequent to least frequent), use the following formula:

=SORTBY(UNIQUE(A2:A15), COUNTIF(A2:A15, UNIQUE(A2:A15)), -1)

How It Works Step-by-Step

To understand why this formula is so powerful, let's break it down into its constituent parts from the inside out:

• UNIQUE(A2:A15): This function looks at your raw data range (A2:A15) and extracts a distinct list, stripping away all duplicate entries.
• COUNTIF(A2:A15, UNIQUE(A2:A15)): This is the engine of our frequency counter. Instead of counting a single criteria, we pass the unique list of items as the criteria. This tells Excel to count how many times each unique item appears in the original range, returning an array of counts.
• SORTBY(..., ..., -1): Finally, the SORTBY function takes the unique list generated in step one and sorts it based on the array of counts generated in step two. The -1 argument specifies that the sort order should be descending (highest frequency to lowest).

Example Scenario

Imagine you have a list of fruit orders in column A. Applying our formula in column C yields the following results:

Method 2: Displaying the Frequency Count Alongside the Sorted List

While seeing the sorted list of items is helpful, you will often want to display the actual frequency counts next to those items. You can achieve this using adjacent columns, but we can do it dynamically inside a single cell using the LET and HSTACK functions.

The Single-Cell Multi-Column Formula

Enter the following formula into an empty cell where you want your summary table to start:

=LET(
    items, A2:A15,
    uniq, UNIQUE(items),
    freq, COUNTIF(items, uniq),
    summary, HSTACK(uniq, freq),
    SORT(summary, 2, -1)
)

Understanding the LET Formula Structure

The LET function allows us to define variables within our formula, improving both readability and calculation performance:

• items: We define our source range (A2:A15).
• uniq: We extract the unique values from our items.
• freq: We calculate the frequency of each unique item.
• summary: We use HSTACK (Horizontal Stack) to combine our unique items side-by-side with their corresponding frequencies.
• SORT(summary, 2, -1): We sort our newly created two-column table by its second column (the frequencies) in descending order (-1).

Handling Edge Cases: Blanks and Ties

In real-world scenarios, datasets are rarely perfectly clean. Two common issues arise: blank cells in the source data and tie-breaks (items with the exact same frequency).

1. Excluding Blank Cells

If your source range contains blank cells, the UNIQUE function will return a 0 or an empty row in your sorted list. To prevent this, integrate the FILTER function to clean your range before processing:

=LET(
    clean_range, FILTER(A2:A15, A2:A15 <> ""),
    uniq, UNIQUE(clean_range),
    SORTBY(uniq, COUNTIF(clean_range, uniq), -1)
)

2. Sorting Ties Alphabetically

If "Apple" and "Orange" both appear exactly three times, Excel's default sorting behavior might look disorganized. You can add a secondary sorting rule to sort tie-breaker items alphabetically:

=LET(
    items, FILTER(A2:A15, A2:A15 <> ""),
    uniq, UNIQUE(items),
    freq, COUNTIF(items, uniq),
    SORTBY(uniq, freq, -1, uniq, 1)
)

In this modification, SORTBY first sorts by frequency in descending order (-1), and then sorts the unique list itself alphabetically in ascending order (1).

Method 3: Legacy Excel Approach (Excel 2019 and Older)

If you are using an older version of Excel that does not support dynamic array formulas, you will need to use a combination of a helper column and traditional formulas to achieve this result.

Step 1: Create a Unique List

First, copy your raw data column and paste it into a new column. Select the pasted data, go to the Data tab on your Excel ribbon, and click Remove Duplicates. This gives you a static list of unique items (e.g., in column C, starting at C2).

Step 2: Calculate the Frequency

In the adjacent column (column D), count the occurrences using a standard COUNTIF formula. In cell D2, enter:

=COUNTIF($A$2:$A$15, C2)

Drag this formula down to match the length of your unique list.

Step 3: Extract the Sorted List via Formula

If you want the sorting to happen automatically via formulas rather than manually using the "Sort" button, you can use a combination of INDEX, MATCH, and LARGE. In cell E2, enter the following array formula (press Ctrl + Shift + Enter if you are on Excel 2016 or older):

=INDEX($C$2:$C$8, MATCH(LARGE($D$2:$D$8 - ROW($D$2:$D$8)/1000000, ROWS($E$2:E2)), $D$2:$D$8 - ROW($D$2:$D$8)/1000000, 0))

Note: The subtraction of ROW(...)/1000000 acts as a tiny tie-breaker mechanism to ensure that duplicate frequency counts do not cause the MATCH function to return the same item multiple times.

Summary of Best Practices

• Use Excel Tables: Always convert your raw data range into an official Excel Table (Ctrl + T). This allows your formulas to use structured references (e.g., Table1[Products]), meaning your frequency sorting calculations will automatically update and expand as you add new rows of data.
• Performance Considerations: While dynamic arrays are incredibly efficient, performing heavy calculation functions like COUNTIF inside large datasets (100,000+ rows) can slow down your workbook. If you notice performance lag, consider using a Pivot Table sorted by "Value" as an alternative approach for extremely large data sets.

By mastering these dynamic array formulas, you can turn raw, repetitive data lists into structured, prioritized insights instantly, keeping your Excel dashboards clean, automated, and highly professional.