Lesson#155: 25 most useful Excel Functions everyone needs to know

Hello readers! In this post, we shall learn about the 25 most useful Excel Functions everyone needs to know. Newcomers who start to learn Microsoft Excel gets easily confused with hundreds of excel formulas and functions. So today we shall summarize and make a shortlist of the 25 most useful Excel Functions which are required for everyone.

Excel SUM function

This is a very basic Mathematical function required for everyone using Excel. Excel SUM function sums a particular specified range or sets of numbers. But it only sums numerical values. See the examples.

SUM(number1,number2, ...)

Excel COUNT function

Excel COUNT function is also a basic function that counts a specified range of values. It only counts numerical values. Here is an example.

COUNT(value1, [value2], ...)

Excel AVERAGE function

The Excel AVERAGE function is used to calculate the average value of numerical values within a specified range. See how to do it.

AVERAGE(number1, [number2], ...)

Excel SUMIF function

Excel SUMIF function makes the sum of numerical values within a specified range if they meet the desired criteria. And this function is very much useful for all Excel users.

SUMIF(range,criteria,sum_range)

Excel COUNTIF function

Same as the SUMIF function, COUNTIF also counts the numerical values within a specified range if they meet the desired criteria. And this function is very much useful for all Excel users.

COUNTIF(range, criteria)

It is counting numbers below 1000 within this specified range.

See how useful is Excel COUNTIF function.

Excel SUMIFS function

Excel SUMIFS function allows the sum of numerical values within a specified range if they meet not only one but multiple desired criteria.

SUMIFS(sum_range,criteria_range1,criteria1,criteria_range2,criteria2…)

Excel COUNTIFS function

Excel COUNTIFS function counts numerical values within a specified range if they meet not only one but multiple desired criteria.

COUNTIFS(range1, criteria1, [range2], [criteria2], ...)

Read posts about the COUNTIFS function.

Excel IF function

This one is the most useful and primary logical function. Excel IF function is for the logical test. It checks whether a condition is met and then returns a value if TRUE or another value if FALSE.

IF(logic_test, value_if true, value_if_false)

Click here to see posts related to function IF

Excel VLOOKUP function

VLOOKUP is one of the most discussed and useful functions for your daily spreadsheet work. Excel VLOOKUP function to look up and retrieve data from a specific column in an array.

VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

Excel SUMPRODUCT function

SUMPRODUCT function multiplies ranges or arrays together and then returns the sum of products.

SUMPRODUCT(array1, [array2], [array3], …)

Excel SUBTOTAL function

Excel SUBTOTAL function is very good for Excel users when you are using Filter on your spreadsheet data. It returns COUNT, SUM, AVERAGE, etc. values only on filtered entities. The SUBTOTAL function has the feature to exclude or include values in hidden rows.

SUBTOTAL(function_num, ref1, ref2, ...)

Excel ROUND function

Excel ROUND function rounds numbers to a given number of digits after or before the decimal.

ROUND(number,num_digits)

=ROUND(2.3456,2) = 2.35

=ROUND(2.3126,2) = 2.31

=ROUND(12.789,1) = 12.8

=ROUND(12.789,-1) = 10

Excel TODAY function

This small function is very useful. Excel TODAY function returns the current date value in date format.

TODAY( )

See here how useful is Excel TODAY function.

Excel NOW function

Just like TODAY, Excel NOW functions also return the current time in time format.

Excel IFERROR function

The Excel IFERROR function is a very useful function. It is for customizing the result when the value returns an error.

IFERROR(value, value_if_error)

Read other posts about the IFERROR function.

Excel LEFT, RIGHT, MID functions

These three Text functions are very useful in Excel

Excel LEFT function which returns the specified number of characters from the start of a text string.

LEFT(text,num_chars)

Excel RIGHT function which returns the specified number of characters from the end of a text string.

RIGHT(text,num_chars)

Excel MID function which returns the specified number of characters from the middle of a text string.

MID(text, start_num, num_chars)

Excel SUBSTITUTE function

Excel SUBSTITUTE function substitutes new_text for old_text in a text string. Use SUBSTITUTE when you want to replace specific text in a text string.

SUBSTITUTE(text,old_text,new_text,instance_num)

Example:

Case 1: When it doesn’t require to use of instance_num

Case 2: When you require the instance_num but you don’t put it and the SUBSTITUTE function changes all old_text with new_text

Case 3: When you require the instance_num and you put it then the SUBSTITUTE function changes only that occurrence of old_text with new_text

See more posts about the Excel SUBSTITUTE function.

Excel OFFSET function

Excel OFFSET function creates an offset to the original cell and returns the value from the offset cell. Positive offset in rows is downward, negative is upward, positive offset in columns is towards the right, and negative is toward left.

OFFSET(reference,rows,cols,height,width)

See more examples of the function OFFSET.

Excel UPPER, LOWER, PROPER functions

The UPPER function returns all characters of a text in uppercase.

UPPER(text)

The LOWER function returns all characters of a text in lowercase.

LOWER(text)

The PROPER function returns a text by making all characters in it lowercase and the first characters of all words in uppercase.

PROPER(text)

Excel DATEDIF function

This is a hidden but very useful function in Excel. Excel DATEDIF function returns the difference between two date values in years, months, or days.

DATEDIF(start_date, end_date, unit)

Unit	Return value
“Y”	The difference in complete years
“M”	The difference in complete months
“D”	Difference in days
“MD”	Remaining days, after complete months
“YM”	The remaining months, after complete years
“YD”	Remaining days, after complete years

Short Tips#0017: How to calculate your age with DATEDIF?

Excel LEN function

This function returns the length of the text value. Simply it will tell you how many characters are in the sentence or word.

LEN(text)

Excel INDEX–MATCH functions

These are very useful combinations of two functions. It can make your lookup much better in a spreadsheet. It removes the limitation of VLOOKUP and HLOOKUP functions.

Excel INDEX function returns a value or reference of the cell at the intersection of a particular row and column, in a given range.

INDEX(array,row_num,column_num)

The Excel MATCH function searches for a specified item in a range of cells and then returns the relative position of that item in the range.

MATCH(lookup_value,lookup_array,match_type)

A combination of these two functions gives you satisfactory lookup results. Here I have discussed INDEX-MATCH in another post.

Lesson#41: Combination of INDEX and MATCH function is better than any other lookup function

Excel AND, OR, NOT functions

The AND function is required to use more than one logical condition at one time. If all the logical conditions are true then it returns TRUE or returns FALSE

AND(logical1, [logical2], …)

Excel OR function is required to use any of the more than one logical conditions has to be TRUE. If none of the conditions is TRUE then returns FALSE.

OR (logical1, [logical2], …)

Excel NOT function just reverse the logical value.

NOT(logical)

Excel ROW function

The Excel ROW function returns the row number for reference.

ROW(reference)

See more examples of using the Excel ROW function.

Excel COLUMN function

The Excel COLUMN function returns the column number for reference.

COLUMN(reference)

See more posts about the Excel COLUMN function.

So friends here I have discussed just not only 25 but more than 25 most useful Excel Functions which you always need to keep in your head when working in Excel. You shall get a full description, application, and video tutorials about each function here.

So keep following my blog and subscribe to the YouTube channel also. It shall keep me motivated to write more Excel things for you.