Most Popular and Powerful Formulas in Excel

MS Excel is one of the most used data management applications globally. Its success is majorly due to the ease with which formulas can be created, allowing us to handle a lot of data and obtain the desired results. Excel has various functions that are ready to process your information to get the desired results. Familiarize yourself with each of them. Once you master various functions, you can create advanced MS Excel formulas that will help you become more efficient in using Excel. The article discusses the most popular formulas in Excel through practical examples of the use of formulas for Excel.

What is an Excel formula?

An Excel formula is a code that is entered into a cell. That code performs some calculations and returns a result displayed in the cell. MS Excel formulas must begin with the equal symbol ( = ). 

To understand these better, let’s look at some of the most popular formulas in Excel and their examples.

Content

• Sum 
• Average 
• Median 
• Min and Max
• Count
• Now
• Trim
• If
• Vlookup
• Hlookup
• Xlookup
• Percentage

Explore – MS Excel Courses

Sum 

The SUM function in Excel is among the most commonly used formulas. This formula adds the values ​​of the cells within it. Supports both separate cells and intervals. 

SUM formula –

=SUM(number1, [number2], [number3]……)

Where –

number1 (necessary argument) – The first item that we need to add

number2 (necessary argument) – The second item we need to add

number3 (optional argument) – The third item we need to add

Average 

The average formula returns the average arithmetic value of the arguments. This result is also known as the mean or arithmetic mean.

AVERAGE formula –

=AVERAGE (number1, [number2], …) 

Where –

number1 (necessary argument). The first number, cell reference, or range for which you want to calculate the average.

number 2 (optional argument). Additional numbers, cell references, or ranges you need to calculate the average. You can select up to 255 arguments.

Must Read – What is Excel?

Median 

MEDIAN function in Excel returns the median or middle value in a given data set. It is categorized under Excel Statistical functions.

MEDIAN Formula –

=MEDIAN(number1, [number2], …)

Where,

number1 (necessary argument): The first number to evaluate.

number 2 (optional argument): The second number to evaluate, and the count can go up to 255 arguments.

Min and Max

If instead of knowing the arithmetic mean, you want to know which is the highest value or the lowest value of a set, you have two formulas with predictable names: MAX and MIN. You can use them with separate cells or ranges of cells.

MIN Formula –

=MIN(number1, [number2], …)

MAX Formula –

=MAX(number1, [number2], …)

Where,

number1 is the necessary argument.

number2 is an optional argument.

Count 

The COUNT function in Excel returns a count of values. These values can be negative numbers, percentages, dates, times, fractions, and formulas that return numbers. The COUNT function ignores empty cells.

COUNT Formula –

=COUNT (value1, [value2], …)

Where,

value1 – (required argument) An item, cell reference, or range.

value2 – (optional argument) An item, cell reference, or range.

Must Explore – Free MS Excel Courses

Now 

The NOW function displays the current date and time on an Excel worksheet. 

=NOW()

It also calculates the +/- days from the current days.

=NOW()+7

(7 days from today’s date)

=NOW()-10 

(10 days before today’s date)

Trim 

The Trim function in Excel removes all the extra spaces from a word string. This function is handy if you have to clean the data with extra spaces. The TRIM formula only removes the text’s ASCII space character (32).

Trim Formula –

=TRIM(text)

If 

The IF function in Excel is part of the Logic function group and allows us to evaluate a condition to determine whether it is true or false. The IF function is very helpful in making decisions based on the result obtained in the logical test.

IF FUNCTION SYNTAX

IF statement can have two results, where –

The first result is if your comparison is True, 

The second result is if your comparison is False

For example,

We have a travel expense dataset. We want to find out if the budget planned exceeded the expenditure. So here, we will apply the IF formula –

=IF(C2>=B2,”Over budget”,”Under Budget”)

Where,

C2=Actual Expenditure

B2=Budgeted Amount

So, if the data in column C is greater than the data in column B, the result will be over budget; similarly, if the data in column C is less than column B, the result will be under budget.

Vlookup 

The VLOOKUP function looks at the values vertically in a data set. It means it looks for a specific value within a column of data and returns a value from a different column but the same row as the value found. This function is a part of the Search and Reference group.

VLOOKUP Formula –

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

We have four arguments in VLOOKUP.

Lookup_value (required): The value we are looking for. 

Table_array (required): Cell reference defines the range where the search will be performed.  

Col_index_num (required): Numeric value that indicates the column that we want as a result of finding a match. 

Range_lookup (optional): This parameter tells the function what type of search we want to perform, whether exact or approximate. 

Learn VLOOKUP through examples.

Hlookup 

The HLOOKUP function in Excel searches for a value within a row and returns the value found or a #N/A error if not found. This function is similar to the VLOOKUP function. HLOOKUP function can be used when you look for values in a row of some data table

HLOOKUP Formula –

=HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])

lookup_value (required): The value we are looking for.

table_array (required): The range containing the values must be a row.

row_index (required): The row number with the function’s value will return.

range_lookup (optional): The value should be FALSE if we want an exact match or TRUE for an approximate match.

Go through our HLOOKUP tutorial.

Xlookup 

XLOOKUP combines the functionalities of both VLOOKUP and HLOOKUP functions. It is an advanced function that searches for a value in a row or column of a table. XLOOKUP is not available in Excel 2016 and Excel 201.

XLOOKUP Formula –

=XLOOKUP(value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

Where,

value (required): The entry you are looking for can be composed of text, numbers, or placeholders. This is a mandatory argument.

lookup_array (required): The range or array in which we search the required information.  

return_array (required): The range we need to return the desired information. 

if_not_found (optional): If Excel doesn’t find a good match, it can return a text or value you provide. Otherwise, the program will display the message “#N/A.”  

match_mode (optional): Compare mode allows you to specify a match type.  

Read our XLOOKUP tutorial and understand through examples.

Percentage 

A percentage represents a proportion of a given part as a fraction of 100 equal parts. It is a mathematical entity and is a commonly used function in data in finance.

Percentage Formula –

 = Numerator/Denominator

Conclusion

We hope this article familiarized you with the most commonly used MS Excel formulas and functions. MS Excel has over 400 functions, so take your time and familiarize yourself with each. Once you master multiple functions, you can create advanced MS Excel formulas that will help you become more efficient in using Excel.