MODE Excel Function

There is a MODE function in Excel. It is in the Formulas tab. We can click Formulas → More Functions → Compatibility → MODE to access it.



And to use the latest version of the MODE(), MODE.SNGL() and MODE.MULT(), click Formulas → More Functions → Statistical.

The MODE function in Excel may not work because of the below reasons:

• The MODE() arguments are not numeric values.
• The MODE() arguments contain error values or texts that do not translate into numeric values.
• The source data set does not contain reoccurring data points.
• All data points in the given data set are text, logical values, or empty cells.

We can use the MODE() in Excel VBA using the below method:
Application.WorksheetFunction.Mode()

Let us see how to use it in Excel VBA with an example.

The table below shows a grocery items list and the corresponding count of units delivered in columns A and B.



Suppose we have to determine the mode of units delivered and display the result in cell E2. Then the steps are:

• Step 1: With the active sheet containing the above table, press the keys Alt + F11 to open the VBA Editor.



• Step 2: Choose the required VBAProject and select Insert → Module to open the Module1 window.





• Step 3: Enter the VBA code containing the MODE function in the Module1 window to determine the mode of units of grocery items delivered.

Sub mode_fn()
Range(“E2”) = Application.WorksheetFunction.Mode(Range(“B2:B6”))
End Sub



• Step 4: Now click on the Run Sub/UserForm icon in the top menu to execute the code.



And finally, if we open the active worksheet after the code gets executed, we will see the required mode value, 2000, in the target cell E2.



In the above example, the MODE() in Excel VBA checks the supplied array range for the most frequently occurring values. And as the value 2000 appears twice in the given cell range, B2:B6, the VBA code output is 2000.