Archer Dynamics

The Problem

Here’s the problem: MAX() gives you one value. But in the real world—tracking highest equipment costs, identifying top error frequencies, finding resource hogs—you need the podium finishers, not just the gold medal.

Easily Find Top Three Values in Excel Column

The clean solution is literally a single command and nothing else: LARGE(). This one will look at your array and find the highest value, the number behind comma just says whether it is the 1st largest, 2nd, largest, 3rd largest, etc. No sorting, no helper columns, just:

1st: =LARGE(A:A,1)
2nd: =LARGE(A:A,2)
3rd: =LARGE(A:A,3)

Note:
This works on numbers only and not on text, obviously, since we are looking the top largest values it has to be numeric. So, be aware and ensure your data is in numeric format and not text disguised as numbers.

Want the whole finishers’ podium in one formula? In newer Excel M365 you can use a combination of SORT() and UNIQUE() and do something like this:

=SORT(UNIQUE(A:A),,-1)

How this works is no rocket science: UNIQUE() grabs unique values and strips away duplicates, SORT then has 3 parameters: 1/ the result of the UNIQUE is our data, then sort_index – which we leave blank here, because we sort by the column itself, therefore 1, and the last one is -1 – i.e. sort in descending order from highest to lowest number. All you need to do is grab the top 3 rows. Done.

Finding top 3 most frequent values

This is where things get interesting. I suppose you could use COUNTIF() and count unique values in separate columns, but you could easily use LET() – this is such a crazy function I will have to write an article about it some time later – this one straddles the border between excel formulas and programming scripts. Love it!

=LET( 
    data,A:A,
    unique,UNIQUE(data),
    counts,COUNTIF(data,unique),
    SORTBY(unique,counts,-1) 
)

A few words about this solution. I’ll dive into LET() later, but for now, all you need to know that the first two rows of the script define data, i.e. data = column M, unique = result of UNIQUE(data), third one brings them together and the last row executes a sort in descending order (hence the -1) and prints it out as the final product of this whole script.

DNS has 5 records.
Firewall has 4 records.
Database has 3 records.
Webserver has 2 records.
Single formula, no helper columns, no interim results, no pivot tables. Instant result! LET() got it right the first time around…

What problem is this solving?

How can this be helpful? When I try to debug my DNS server and have a long list of data logs, searching for a specific repetitive value, the most prevalent one, is a good place to start.

Need Help?

Need help streamlining your processes or solving tricky business problems? I offer one-on-one consultations to get you unstuck fast. Book a free consultation with me today at goarcherdynamics.com.

Want more practical tips and workflow hacks? I publish them regularly on my blog — check it out and subscribe for newsletter updates: goarcherdynamics.com