How to Find the Most Common Numbers in an Excel Program

When analyzing data in a spreadsheet, it may be necessary to find the mode, or the value that occurs most often within the data set. In Microsoft Excel, the filter function can be used to determine the mode for data in a specific column. The mode can be found using a formula, which returns the most frequently occurring number. Another option is to use a filter to sort by the most frequently occurring value and then counting the number of instances.

...
There are two main ways to determine the most commonly occurring number in an Excel spreadsheet.

Data Filter

Step

Select the first cell of the column containing the numbers you wish to analyze.

Step

From the menu, select "Data," then "Filter" and then "Auto Filter." This will apply a drop-down arrow to the the right side of the cell.

Step

Click the drop-down arrow and select "Top 10." In the window that appears, change the number in the second box to the number of items you want to view. For example, if you wish to see the top three numbers, enter the number "3." Select "OK" to apply the filter.

Step

The data will be sorted to show the most popular numbers in order of decreasing frequency. Count the number of rows that contain each value you are interested in knowing to determine the number of times that specific value is used.

"MODE" Function

Step

Select an empty cell where you would like for the mode result to appear.

Step

Click on the "Formulas" button. Select "More Functions," and then "Statistical," which will open a drop-down list.

Step

Select "MODE" to bring up the dialog box. Select the cells from which you want to determine the mode using the drag select function. This will enter the range into the dialog box.

Step

Select "OK" and the result for the mode will appear. The formula will also appear in the bar at the top of the worksheet.