# What is the correct use of MODE()

#### spcalan

##### Well-known Member
I have a column ( 100 rows ) and I need to know what number shows up the most times.

Is mode the correct formula?

I keep getting #NUM! when I use:

=mode(a:a)

### Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

#### VoG

##### Legend
Your formula returns the correct results for me using Excel 2007. Perhaps avoid using whole column references in an earlier version

=MODE(A1:A20)

#### roentgen

##### Board Regular
I think you need to be more specific with your range. =MODE(A1:A100) would work. Selecting the whole column, I assume, tries to rationalise all the blank cells to a number, hence the #NUM! error.

#### stanleydgromjr

##### Banned
roentgen,

This is one way:

Excel Workbook
ABCD
1113
2223
3333
4144
5251
6361
71
82
93
104
114
124
134
145
156
Sheet1

The formula in cell D1 (copied down to the last entry in column C - the unique numbers in column A):
=COUNTIF(A:A,C1)

Have a great day,
Stan

#### mortgageman

##### Well-known Member
Your formula returns the correct results for me using Excel 2007. Perhaps avoid using whole column references in an earlier version

=MODE(A1:A20)
I was curious so I just checked as well - I have version 2003. I tried it with the whole column ref and also got a #NUM error. Quite frankly, whole column refs scare me - it seems dangerous and plain lazy to use them. I think it is worthwhile knowing what range you are dealing with.

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,168,018
Messages
5,856,840
Members
431,836
Latest member
jj0911

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back