11 lesser-known but useful Excel functions ( P1 )

11 lesser-known but useful Excel functions: Microsoft Excel provides hundreds of functions, so there are definitely a few that you may not be aware of. These unique functions have specific purposes that you’ll be excited to learn and use.

FLOOR and CEILING for rounding

You can use the mathematical functions FLOOR and CEILING to round towards or away from zero to a specified multiple. Use FLOOR to round down and CEILING to round up.

The syntax for each is FLOOR(value, multiple) and CEILING(value, multiple) where both arguments are required.

To round 4.4 down to the nearest multiple of 2, you would use the following formula:

=FLOOR(4.4,2)

To round 5.6 up to the nearest multiple of 2, you would use the following formula:

=CEILING(5.6,2)

MODE.SNGL to find repeated values

Originally just a simple MODE function, Microsoft created an improved version of this statistical function for better accuracy. Use MODE.SNGL to find frequently repeated numbers in a range or array of cells.

The syntax is MODE.SNGL(array1, array2, …) where only the first argument is required. You can use numbers, names, arrays, or cell references that contain numbers. Use optional arguments for additional ranges of cells.

Here, we find the most frequently repeated number appearing in cells A1 to A5.

=MODE.SNGL(A1:A5)

To find a recurring number in A1 to A5 and C1 to C5, you would use the following formula:

=MODE.SNGL(A1:A5, C1:C5)

CONVERT to switch from one measurement to another

For useful technical functionality, you can use CONVERT to change a value from one measurement system to another.

The syntax is CONVERT(value, from, to) where you’ll need all three arguments. For the from and to arguments, you’ll use abbreviations. Check Microsoft’s Support website for the abbreviations you need for weight and mass, distance, time, pressure, force, energy, power, magnetism, temperature, volume, area, information, and speed.

To convert the value in cell A1 from degrees Celsius to degrees Fahrenheit, you would use the following formula:

=CONVERT(A1,"C","F")

To convert the value in cell B1 from centimeters to inches, you would use the following formula:

=CONVERT(B1,"cm","in")

DELTA to check for equal or unequal values

Another useful technical function is DELTA. With it, you’ll use the delta Kronecker function to check if two values are equal or not. Unlike the EXACT function, the result is 1 (true) or 0 (false).

The syntax is DELTA(value1, value2) where only the first argument is required and can be a number or cell reference. If you leave the second argument blank, Excel assumes it to be zero.

To check the values in cell A1 and B1, you would enter the following formula:

=DELTA(A1,B1)

To check the values 2 and -2, you would use the following formula:

=DELTA(2,-2)

GESTEP to check for greater than or equal to threshold

Another useful technical function you may find handy is GESTEP, which allows you to check if a value is greater than or equal to a step (threshold). The result is 1 (true) or 0 (false).

The syntax is GESTEP(value, step) where only the first argument is required and can be a number or cell reference. If you leave the second argument blank, Excel uses zero.

To check the value in cell A1 against a step of 4, you would use the following formula:

=GESTEP(A1,4)

To check the value 10 against a step of 12, you would use the following formula:

=GESTEP(10,12)

11 lesser known but useful Excel functions