Most Popular and Powerful Formulas in Excel

Most Popular and Powerful Formulas in Excel

9 mins read35.7K Views Comment
clickHere
Rashmi
Rashmi Karan
Manager - Content
Updated on Dec 7, 2023 14:18 IST

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 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.

2021_09_Most-Popular-Formulas-in-Excel.jpg

Must Read – What is 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

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

2022_11_sum1.jpg
2021_09_sum-2.jpg

 

How to Multiply in Excel?
How to Multiply in Excel?
Learn how to multiply in Excel using different criteria. Understand through different examples.
What is Filter in Excel?
What is Filter in Excel?
Filter in Excel is a valuable tool to analyze data quickly. In this article, you will learn how to use filters in Excel.
COUNTIF in Excel – Shiksha Online
COUNTIF in Excel – Shiksha Online
Learn how to use the COUNTIF function in Excel using different criteria via different examples.

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.

2021_09_Average-q.jpg

Explore MS Excel Courses

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.

2021_09_Median.jpg

Min and Max

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

MIN Formula

=MIN(number1, [number2], …)

2021_09_Median-1.jpg

MAX Formula

=MAX(number1, [number2], …)

2021_09_Max.jpg

Where,

number1 is the necessary argument.

number2 is an optional argument.

What Are Excel Errors and How To Fix Them?
What Are Excel Errors and How To Fix Them?
Excel is a powerful tool for working with data, but it can also be frustrating when errors occur. While some errors are easy to fix, others can be more difficult...read more
150+ Keyboard Shortcuts In Excel
150+ Keyboard Shortcuts In Excel
Excel users who enter a lot of data with the keyboard can boost their productivity by learning Excel shortcut keys, which allow them to access the program’s commands much faster....read more
How to Use Excel Filter Shortcut?
How to Use Excel Filter Shortcut?
Excel Filter shortcuts are keyboard combinations that you can use to apply filters to your data quickly and easily. The article will explain different ways to use Excel Filter shortcuts,...read more

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.

2021_09_Count.jpg

Must Explore – Free MS Excel Courses

Now 

The NOW function is used to display the current date and time on an Excel worksheet. 

=NOW()

2021_09_Count-1.jpg

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

=NOW()+7

(7 days from today’s date)

2021_09_Now-2.jpg

=NOW()-10 

(10 days before today’s date)

2021_09_Now-2-1.jpg

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)

2021_09_Trim.jpg
How to Divide in Excel?
How to Divide in Excel?
Division is one of the four basic arithmetic operations, and it is a common operation performed in Excel. To divide in Excel, you can use the forward slash (/) operator....read more
How to Multiply in Excel?
How to Multiply in Excel?
Learn how to multiply in Excel using different criteria. Understand through different examples.
COUNTIF in Excel – Shiksha Online
COUNTIF in Excel – Shiksha Online
Learn how to use the COUNTIF function in Excel using different criteria via different examples.

If 

The IF function in Excel is part of the Logic function group and allows us to evaluate a condition to determine if it is true or false. The IF function is of great help 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 data in column C is greater than column  B, then the result will be Over budget; similarly, if data in column C is less than column  B, the result will be Under budget.

2021_09_Trim-1.jpg

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): Tells the function what type of search we want to perform, whether exact or approximate. 

Learn VLOOKUP through examples.

How to Split Cells in Excel?
How to Split Cells in Excel?
Splitting cells in Excel involves dividing the contents of a single cell into multiple cells. This can be incredibly useful for organizing and formatting data in a way that makes...read more
Absolute Reference in Excel – Shiksha Online
Absolute Reference in Excel – Shiksha Online
Absolute referencing is essential for creating robust and adaptable formulas in Excel. By understanding its uses and how to create it, you can ensure your calculations remain accurate and consistent...read more
COUNTIF in Excel – Shiksha Online
COUNTIF in Excel – Shiksha Online
Learn how to use the COUNTIF function in Excel using different criteria via different 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

2021_09_Percentage.jpg

Conclusion

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

FAQs

Explain the types of data formats available in Excel.

There are a total of eleven data formats in excel which are: Text: to treat anything within the cell as text. Be it a number or a symbol Currency: used for monetary calculations Accounting: to line up decimal points and currency symbols in columns Percentage: to display the cell value in the form of percentage Numbers: for displaying numbers Special: useful for database values and tracking Fraction: to display cell value in fraction format Scientific: to display cell value scientifically Custom: You can type the number format code by using existing codes as the starting point Time: This format is used for displaying dates and time as date values Date: This displays date and time serial values in form of data values

Explain some functions as per their use in Excel.

The following functions have been categorized as per their usage in Excel: Math and Financial Functions - RAND(), GCD, SQRT, DEGREE Time and Date functions - DATEVALUE(), NOW(), WEEKDAY(NOW()) Logical Functions - TRUE, FALSE, IF, AND Index Match - INDEX MATCH and VLOOKUP Pivot tables

What is the use of a pivot table in MS Excel?

A pivot table is a tool in MS Excel which allows quick summarization of a large quantity of data. It can automatically perform count, sort, total or average of the data which is stored in a spreadsheet. It displays the result in another spreadsheet and allows linking external data sources to Excel. This tool is very helpful in saving time.

What is the use of the 'IF' function in MS Excel?

IF function is used for logic testing. This function checks if a certain condition is true or false. In case, the condition is true, then, it will provide the result accordingly and if the condition is false, the result will be different. =IF (Logical test, value if true, value if false)

Where should you use WEEKDAY()?

WEEKDAY function is useful when we need to get the day of the week as the output.

Why should one use formulas in Excel?

Formulas help in speeding up calculations and provide the final result within seconds as these automate the calculation process for entries that are being included further. Complex calculations can also be done swiftly through formulas.

What is the use of 'What If' in Excel?

The 'What If' condition is used for changing the data in Microsoft Excel formulas to get a different answer. Suppose that you are buying a bike and you want to calculate the tax that will be levied on it. In this case, you will use the 'What If' function.

What does the 'AND' function do in Excel?

AND function is similar to the IF function as it performs a logical function. To check if the output is true or false, AND function evaluates at least one mathematical expression located in another cell within the spreadsheet. In case you want to see the output for more than one cell within a single cell, it is only possible through AND function.

Why do we use cell reference in calculations?

A cell reference is used for eliminating the need to insert the data again and again for calculation purposes. For any specific function, while writing a formula, you need to direct Excel to the exact location of that data. This location is known as cell reference. So, whenever a new value is added to the cell, it will calculate according to the reference cell formula.

What are function arguments?

Arguments are the inputs that are required for any function to return a result. Arguments appear inside parentheses and are separated by commas. The format will be =FUNCTIONNAME (argument 1, argument 2, argument 3...).

Name 5 Text MS Excel formulas and define their usage?

The five following text-based MS Excel formulas are as follows: CHAR: To ger character from a number CODE: To get the code for a character DOLLAR: To convert a number into text in the currency format EXACT: For comparing two text strings NUMBERVALUE: To convert text into numbers with custom separators

About the Author
author-image
Rashmi Karan
Manager - Content

Rashmi is a postgraduate in Biotechnology with a flair for research-oriented work and has an experience of over 13 years in content creation and social media handling. She has a diversified writing portfolio and aim... Read Full Bio

Comments