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.
Select the first cell of the column containing the numbers you wish to analyze.
Video of the Day
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.
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.
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.
Select an empty cell where you would like for the mode result to appear.
Click on the "Formulas" button. Select "More Functions," and then "Statistical," which will open a drop-down list.
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.
Select "OK" and the result for the mode will appear. The formula will also appear in the bar at the top of the worksheet.