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.
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.
Explore – MS Excel Courses
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]……)
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
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], …)
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.
Explore MS Excel Courses
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], …)
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 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], …)
MAX Formula –
=MAX(number1, [number2], …)
number1 is the necessary argument.
number2 is an optional argument.
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], …)
value1 – (required argument) An item, cell reference, or range.
value2 – (optional argument) An item, cell reference, or range.
Must Explore – Free MS Excel Courses
The NOW function is used to display the current date and time on an Excel worksheet.
It also calculates the +/- days from the current days.
(7 days from today’s date)
(10 days before today’s date)
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 –
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
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”)
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.
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.
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 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])
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.
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 –
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.
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