Excel Formulas: Basic And Advanced MS Excel Formulas With Examples

Microsoft Excel was initially made for Macintosh computers in 1985, and then in 1987, it was made available for Windows. It was initially created as a spreadsheet tool for personal computers under the name Multiplan.

Excel is a popular spreadsheet program of Microsoft Office that contains several boxes known as cells that are used for various applications. This program is more than just a tool to enter names and numbers. This is a widely used software application that helps to generate reports and perform various calculations like addition, subtraction, multiplication, and division.

One of the best features of Microsoft Excel is the Excel sheet formulas. So today let’s take a look at various MS Excel formulas – some basics and also a few key advanced Excel formulas.

MS Excel Formulas List: Basic And Advanced

To execute different calculations of data in a spreadsheet, you can utilize a variety of basic and advanced formulas.

Basic Excel Formulas List

Here is a list of some basic formulas.

1. SUM

This function is used to calculate the total values in different cells. This is one of the most basic formulas used in an excel sheet.

Formula –

=SUM( value 1, value 2, value 3, etc.). 

Press Enter and the cell will give you the total of the selected values. You can also use this function to calculate the total of a list of numbers. 

For example:  If you want to calculate the total value of all the numbers from cell A1 all the way to cell A11, then you can type in: =SUM (A1: A11).

Also Read: Excel Shortcut Keys

2. PERCENTAGE

Excel makes it easy for us to calculate the percentage of a value. This can be done by entering the cells that you want to find the percentage for in this format: A1/B1. This will give you a decimal value which can be converted into a percentage value by highlighting the cell.

Click on the ‘Home’ tab, and select “Percentage” from the dropdown menu to use this formula.
For Example: B1/15% -Finds the value 15%of B1

3. SUBTRACTION

There are two different ways to subtract values in an Excel sheet:

• Use the SUM formula: You can subtract two different values in the cells by writing = SUM(A1, – C1) and pressing enter. 
• Use the formula directly ‘=A1-B1.’ Here entering this format will also give you the difference between the two values.

For example – If A1 is 20 and C1 is 8 then entering this format will give you the result 12.

4. MULTIPLICATION

To use the multiplication formula in an Excel sheet, you simply need to type in:  =A1*C1 and press Enter. This will give you the multiplied value of the two cells. You have to use an asterisk (*) to multiply two values.

For example – If A1 is 10 and C1 is 5 then using this formula =A1*C1 will give you the value 50.

5. DIVISION

You can use the division formula in an Excel sheet by typing in: =A1/D1 and pressing Enter. You need to use the slash (/) to divide the values in the cells.

For example – If you want to divide the value of A1 which is 20 by the value of D1 which is 10 then by entering the formula you will get the value 2.

6. AVERAGE

This formula will give you the average of the values in a cell. 

For example – Type in AVERAGE (number 1, [number 2], [number 3], number 4]….) and press Enter to get the result.

Also Read: AVERAGE Formula in Excel

7. COUNT

This formula is used to count only the numeric values present in a cell. 

Formula – =COUNT(value 1, [value 2], [value 3], [value 4],…)

For example – To count columns you can type in, COUNT(A: A), and to count rows you can type in COUNT (A1:B1).

8. MIN 

It helps in finding the minimum number in a range of values, either in a row or a column.

Formula –

=MIN( number 1, [number 2], [number 3], [number 4],…)

For example – To find the minimum number between columns A and B, you can write:

=MIN (A1, B12) and press Enter.

9. MAX

This will help you to find the maximum number in a range of values, either in a row or a column.

Formula –

=MAX( number 1, [number 2], [number 3],…)

For example – To find the maximum number between columns B and D, you can type in:

=MAX (B2, D11) and press Enter.

10. TRIM

This formula ensures that there is no space between texts in a row or a column. It helps to remove any extra space that can give errors.

Formula – =TRIM(TEXT)

For example – When you type in: =TRIM(A1) and press Enter it helps to remove any unnecessary space in the value in cell A1.

Here is the basic excel formulas list along with examples.

Formula	Description	Example
SUM	Adds a range of numbers together.	=SUM(A1:A5) adds the values in cells A1 through A5.
AVERAGE	Calculates the average of a range of numbers.	=AVERAGE(A1:A5) calculates the average of the values in cells A1 through A5.
COUNT	Counts the number of cells in a range that contain numbers.	=COUNT(A1:A5) counts the number of cells in the range A1 through A5 that contain numbers.
MAX	Finds the maximum value in a range of numbers.	=MAX(A1:A5) finds the highest value in the range A1 through A5.
MIN	Finds the minimum value in a range of numbers.	=MIN(A1:A5) finds the lowest value in the range A1 through A5.
PERCENTAGE	Calculates the percentage of a number.	=B1 / 15% –Finds the value  15%of B1  by calculating.
DIVIDE	Divides one number by another number.	=D1 / 2 – Finds the value of D1 divided by 2.
MULTIPLY	Multiplies two or more numbers together.	=A1 * B1 – Multiplies A1 with B1.
CONCATENATE	Joins two or more text strings into one.	=CONCATENATE(“Hello “,”world!”) joins the two text strings to create the sentence “Hello world!”.
LEFT	Returns a specified number of characters from the beginning of a text string.	=LEFT(A1,3) returns the first three characters of the text string in cell A1.
RIGHT	Returns a specified number of characters from the end of a text string.	=RIGHT(A1,3) returns the last three characters of the text string in cell A1.
MID	Returns a specified number of characters from the middle of a text string.	=MID(A1,3,5) returns five characters starting from the third character of the text string in cell A1.
ROUND	Rounds a number to a specified number of decimal places.	=ROUND(A1,2) rounds the value in cell A1 to two decimal places.
ROUNDUP	Rounds a number up to a specified number of decimal places.	=ROUNDUP(A1,2) rounds the value in cell A1 up.
ROUNDDOWN	Rounds a number down to a specified number of decimal places.	=ROUNDDOWN(A1,2) rounds the value in cell A1 down.
TRIM	Removes excess spaces from a text string.	=TRIM(A1) removes excess spaces from the text string in cell A1.
LEN	Returns the number of characters in a text string.	=LEN(A1) returns the number of characters in the text string in cell A1.
NOW	Returns the current date and time.	=NOW() returns the current date and time.
TODAY	Returns the current date.	=TODAY() returns the current date.
DATE	Returns the date that corresponds to a specified set of year, month, and day values.	=DATE(2022,12,31) returns December 31, 2022.
EOMONTH	Returns the last day of the month that is a specified number of months before or after a given date.	=EOMONTH(A1,3) returns the last day of the month.

Advanced Excel Formulas List

Here is a list of some advanced Excel sheet formulas

1. VLOOKUP

This function is used to pull data within a large segment of information to another table. It is one of the most used formulas in MS Excel and is very helpful. You can type in VLOOKUP in the insert function tab along with the specified row or column.

Also Read: VLOOKUP Formula in Excel

2. INDEX

The INDEX formula is an alternative to VLOOKUP. You can use this formula to get the value of a cell by specifying the row and column number.

For example –

INDEX (A3:D4, MATCH(C13, A3: A9,0)) and press Enter.

3. MATCH

This formula is used to return the specified row number or column number when a specific string is in the range. You can use this formula along with the INDEX formula to overcome the limitations of the VLOOKUP formula.

For example –

MATCH(A11, A3:A9) and press Enter to get a particular value.

4. IF AND

This is used to find the true or false information within a parameter set. The function finds out if more than one piece of information fits the criteria. If the value is matched with the multiple criteria set, then the value is returned as TRUE. If the value doesn’t match the multiple criteria, the value returned is FALSE.

5. IF OR 

This formula requires all the criteria to be matched for the value to return as TRUE. In this OR formula, only one criterion can be matched for the value to return as TRUE.

6. SUMIF

This function helps you look for only a certain set of information that matches all your criteria while filtering out the rest. You can enter the criteria in the Wizard box and press Enter.

7. CONCATENATE

This helps to pull information from selected data. It is a really helpful tool for financial analysts who work on financial analysis or modeling. This formula uses multiple variants to join information from different cells.

8. LEFT and RIGHT 

This formula is similar to the CONCATENATE function and is used to pull data from a specific side.

The LEFT function helps you to pull data from the left side of a selected column that matches the variables in the command box. The RIGHT function similarly pulls data from the right side in a selected column that comes under the set variable.

9. OFFSET

This function when combined with SUM or AVERAGE is really helpful in terms of calculations. 

For example –

=SUM(A4:OFFSET(A4,0, E2-1))

10. CHOOSE

This formula allows you to pick between specific numbers between different options. This is used when there are more than two outcomes within the specified condition.

For example –

=CHOOSE(choice, option1, option2, option3, option 4, option 5) and press Enter.

Here is the advanced Excel formulas list along with examples.

Formula	Description	Example
INDEX	Returns the value of a cell in a specified row and column of a range	=INDEX(A1:B5,2,2) returns the value in the second row and second column of the range A1:B5
MATCH	Searches for a specified value in a range, and returns the position of that value within the range	=MATCH(“Apple”,A1:A5,0) searches for the value “Apple” within the range A1:A5, and returns its position within the range
VLOOKUP	Searches for a specified value in the first column of a range, and returns the value in the same row of a specified column	=VLOOKUP(“Apple”,A1:B5,2,0) searches for the value “Apple” within the first column of the range A1:B5, and returns the value in the second column of the same row
HLOOKUP	Searches for a specified value in the first row of a range, and returns the value in the same column of a specified row	=HLOOKUP(“Apple”,A1:B5,2,0) searches for the value “Apple” within the first row of the range A1:B5, and returns the value in the second row of the same column
IFERROR	Checks if a formula returns an error, and returns a specified value if it does	=IFERROR(A1/B1,”Error”) divides the value in cell A1 by the value in cell B1, and returns “Error” if the formula results in an error
SUMIF	Adds the values in a range that meet a specified criteria	=SUMIF(A1:A5,”>10″) adds the values in the range A1:A5 that are greater than 10
COUNTIF	Counts the number of cells in a range that meet a specified criteria	=COUNTIF(A1:A5,”<10″) counts the number of cells in the range A1:A5 that are less than 10
AVERAGEIF	Calculates the average of a range that meets a specified criteria	=AVERAGEIF(A1:A5,”>10″) calculates the average of the values in the range A1:A5 that are greater than 10
SUMIFS	Adds the values in a range that meet multiple specified criteria	=SUMIFS(A1:A5,B1:B5,”Red”,C1:C5,”>10″) adds the values in the range A1:A5 that are in rows where column B contains “Red” and column C is greater than 10
COUNTIFS	Counts the number of cells in a range that meet multiple specified criteria	=COUNTIFS(A1:A5,”<10″,B1:B5,”Green”) counts the number of cells in the range A1:A5 that are less than 10 and in rows where column B contains “Green”
AVERAGEIFS	Calculates the average of a range that meets multiple specified criteria	=AVERAGEIFS(A1:A5,B1:B5,”Red”,C1:C5,”>10″) calculates the average of the values in the range A1:A5 that are in rows where column B contains “Red” and column C is greater than 10
CONCAT	Joins two or more text strings into one, with a specified separator	`=CONCAT(A1:A5,”,)

Conclusion

Microsoft Excel is a robust spreadsheet application that has been around for more than three decades. With additional features and capabilities added throughout time, it has developed into a vital tool for corporations, researchers, financial analysts, and regular people alike. Excel has made it possible for users to carry out intricate calculations, evaluate data, and make informed decisions through its useful basic and advanced Excel formulas, user-friendly functions, and customizable charts and graphs. Excel continues to be a popular option due to its adaptability and accessibility.

FAQs