Step1
There are many instances when finding the mode might be useful.
Maybe you want to find the most frequent final score from a set of this sports season's final scores. Maybe you want to find the most common birthday from a set of friends birthdays. Or you might have a list of products you have sold and want to know which item was most popular.
Several mathematical functions you may be familiar with are mean, median, range, and mode. We will be finding the mode by using an Excel filter.
Step2
In this example, we have an Excel 2003 worksheet which has a column containing the following numbers:
2
7
11
3
2
4
11
2
11
4
11
11
Step3
Put your cursor over the first cell and click to select it (i.e. the cell with number 2 at the top of the list).
Step4
From the top menu, select Data > Filter > Auto Filter.
This will automatically select all numbers in the column and apply a filter.
Step5
The first cell now has a small square with an arrow, indicating a filter is enabled.
Step6
Click the arrow and select (Top 10) from the drop down box.
Step7
A Top 10 pop-up window appears.
Step8
In the second selector box, change the number 10 to the number 1 and click OK.
This will find the top 1 number that occurs most frequently.
Step9
Now the column has been filtered to show only the most popular number, as follows:
11
11
11
11
11
Step10
Number 11 is the most popular number on this list.
In addition, you can see that the number 11 occurred five times, which is more than any other number in the set.
Excel Filter
Authority 
Comments
dougharris said
on 12/11/2007 Ok, I guess I have too much time on my hands :^). I hope I'm not bugging you, and this may sound stupid to you, but here goes. What I'm doing is a lottery spreadsheet. Every day I enter that day's drawing. It's a 5 number lottery, so there's 5 columns of numbers. I want to find which number appears the most from each column. I froze the 5 columns so when I do the data sort, it doesn't screw up what I have. I copied and pasted the 5 columns into the unfrozen part of the sheet. Then I do the Data filter. All I'm looking for is one number that appears the most in the column. Is there a way to get just one number instead of a list of numbers? Like you had 11 as the number that appeared the most, but it gives every instance of the number 11. Know what I mean? By-the-way, I use Open Office Calc, but it's very similar to Excel.