Top 29 Excel formulas you should know

[ad_1]

excel formulas

  1. What are Excel Formulas? 
  2. Excel Formulas and Functions
  3. Conclusion

What are Excel Formulas? 

A formula in Microsoft Excel is used to perform mathematical calculations. It is an expression that operates on the values in a cell or range of cells. These formulas return some values as a result, even if it is an error either in the formula or the values. Excel formulas are very useful and help you to perform a wide range of calculations, whether they are simple mathematical calculations such as Addition, Subtraction, Multiplication, Division, or high-level calculations like exponentiation, finding averages, calculating percentages, performing conditional calculations, etc. For example, =A1+A2+A3 is a formula that finds the sum of values of the cells A1, A2, and A3. 

There’s another thing that is very helpful in Microsoft Excel, and that is functions. Functions are predefined formulas that are very useful to perform various calculations by the use of specific values such as arguments. Functions in excel can be used to perform simple as well as complex calculations that are very tough to be done manually. Functions in excel have predefined names to reflect their intended use. 

Excel functions and formulas are very helpful in performing tasks efficiently and saving time. The two words that we just said, ‘Functions’ and ‘Formulas,’ are sometimes interchangeable and closely related. But these are different as formulas are an expression that we use to perform any calculation while, on the other hand, functions are predefined functions that can be used for calculations. To write a formula, we need to start with an equal sign. 

Also Read: Top 6 Free Online Excel Courses With Certificate

Excel Formulas and Functions

In Microsoft Excel, there are a large number of formulas and functions that depend on the type of its use. The formulas and functions are very useful and save our time in performing those calculations manually. Functions make our lives easier to perform calculations without doing any manual calculations. For instance, we used a formula in the upper section to add values of the cells A1, A2, and A3 manually by placing a + sign between them. And if we use the function to add them, we just need to use the =SUM() function, and inside the parentheses, we need to incorporate the cell numbers only. We can give a range of cells, or we can also enter specific cells inside the parentheses. It will work the same for both cases and return the result of the addition of those values. 

In this section, we will see some mostly used and common formulas and functions for mathematical operations, conditional calculations, time-based calculations, and other important functions. 

Let us see some important formulas and functions in Microsoft Excel: 

1. Multiplication

Performing multiplication operations in Microsoft Excel is very easy. But to do that, we need to create a formula. As we just discussed, we need to add an equal sign before we write any formula, therefore you need to add an equal sign first and the remaining formula after that. For instance, we can use the ‘*’ operator to perform multiplication between values of any two or more cells. 

For e.g.

= A1*B1

In the above example, A1 and B1 are two cells and the ‘*’ operator is used to perform multiplication among them. To use the Multiplication Function, we need to use the PRODUCT function. 

For e.g.

= PRODUCT(A1,A2)

In the above example, we used the PRODUCT function after an equal sign that will perform multiplication between the cell values incorporated inside parentheses. 

2. Division

To do this task, we need to use the forward-slash (/) in our formula. After using the equal sign, we need to use two cell references or values in our formula bar in Microsoft Excel. After giving the first value or cell reference, you need to add ‘/’ (forward slash) in order to perform a division between those two values. 

E.g.

=A1/B1 or =8/4 will give 2 as a result.

In the above example, the formula will divide the value of the A1 Cell by the value of cell B1. It should be noted that there’s no DIVIDE function in Microsoft Excel. 

3. SUM

The name of the function is self-defining as it will add the values provided or the range of cell values and return the Sum of those values. To perform this function, you can add individual cell references, values, range of cells, or a mix of these three. 

The syntax of the SUM function in Excel is as follows:

“=SUM(num-1, num-2, ……, num-n)”

Where num-1 and num-2 are the first and second numbers that we want to be added and it can take up to n times such that num-n where n can be any positive number. The first argument num-1 is mandatory and the rest arguments are optional. 

For e.g.

=SUM(A2:A10)

The above example will return the addition of cell values from A2 to A10 where SUM is a function. 

Also Read: Excel Tips and Tricks to Take Your Career Ahead

4. Subtraction

To perform subtraction, we can directly enter the values or use cell references. We need to use the ‘-‘ sign between two or more values or cell references. The syntax for the Subtraction formula in excel is as follows:

=number1-number2

The equal sign is always mandatory whether you declare any function or formula, you always need to use the equal sign first. After that number 1 is the number from which you want to subtract any value. And number 2 is the value that you want to subtract from the first number. In this case, the values of both numbers are mandatory.

Let us see an example for directly entering the values for subtraction:

For e.g.

= 10-7

Here the ‘-‘ operator will subtract 7 from 10 and return the result as 3. To use cell references for subtraction, see the following example:

=A3-D3

Where the values of D3 will be subtracted from the value of A3 and the result will be returned to the desired cell where we put this formula. 

5. IF

The IF function in Excel is one of the most useful and most popular functions. It allows you to make logical comparisons between a value and the expected value. So the IF statement can have two results such as True or False. True is when the comparison is true and False when the comparison is False. 

Let us see the syntax to use this Function properly:

=IF(logical_condition, statement_if_true, statement_if_false)

In the above syntax, the logical statement is mandatory and it is that statement that we need to check in our worksheet or table. The second argument is the return statement that we want if the condition is true and this statement is mandatory. However, the last statement for the false condition is not mandatory. 

For e.g.

=IF(C2=”Yes”,1,2) 

Where IF (C2 = Yes, then return a 1, otherwise return a 2). So, if the value of cell C2 is Yes and it matches, it will return 1, else it will return 2. 

Also Read: Microsoft Excel Interview Questions

6. DATE

The DATE function in Excel can be used when we need to take three separate values and combine them to make a date. The DATE function returns the sequential serial number just like a particular date. 

For e.g.

=DATE(year, month, day)

In the above example, all three parameters such as Year, Month, and Day are mandatory and required. You need to provide cell references in place of Year, Month, and Day inside the parentheses. It will take values from those cells and return them in the form of a date. 

7. Array

Array formulas are very useful and powerful that enable us to perform complex calculations which often can’t be done in simple worksheet functions. In Excel, there are two types of Array formulas such as the formulas that perform various calculations to generate a single value in the result and some which require an array of values as an argument. 

For e.g. {=B2:B8*C2:C8}

In the above example, the curly brackets are used to show that it is an array. It can be applied after writing the Formula such as:

=B2:B8*C2:C8

After writing this formula, when we press CTRL + SHIFT + ENTER, it will add curly brackets making it an array. And It will execute correctly only when we use these key combinations. 

8. COUNT

The COUNT() function is used to count the number of cells for a range that contains a number. The count function doesn’t include blank cells and the cells that have values in another format than numeric. 

For e.g.

= COUNT(C1:C4)

In the above example, the COUNT function will count the number of cells that have numeric values and return the total number of cells in the desired cell. When we are needed to count all the cells with numerical values, text, and any other format of data, we can use COUNTA() and it will return the total number of cells excluding the empty cells only.  

9. COUNTIF

COUNTIF() Function is one of the useful statistical functions that count the number of cells with a given condition. If the condition meets, it counts the cells that contain that specific value. 

For e.g.

=COUNTIF(A2:A5, “apples”)

In the above example, the function COUNTIF counts the cells that contain the value ‘apples’ inside it. And returns the total number of cells that contains the value. For instance, if two of the cells between the range A2:A5 contain “apples” as values inside it, it will return 2 as a result. 

10. AVERAGE

The AVERAGE() function calculates the average of the range of cell values. This function is one of the most useful functions as whenever we need to calculate the Average of any range of cell values, we can directly calculate it with the help of this function. We don’t have to manually write all values and formulas. We just have to give the range of cells and it will take the values of that cell range and calculate the average. 

For e.g.

=AVERAGE(A2:A6)

The example above shows that the Average function will calculate the average value of the cell values ranging from A2 to A6. We can also give specific cell references inside the parentheses to find out the average of those cell values. For instance, if we want to calculate the average of only 3 cells such as C1, C2, and C3. Then we have to write like this: =AVERAGE(C1, C2, C3). And it will calculate the average of these three cell values. 

11. Percentage

There are different ways to calculate percentages in Excel. For example, you can calculate the percentage of correct answers in a test, the discounted price on any item using percentage and for other use cases. When it comes to calculating percentages, it is a two-step process. First, we need to format the cell to indicate the value is a per cent and after that, we need to write the Percentage formula inside the formula bar. 

For e.g.

=B2/C2

Here, after changing the format of the cell for percentage, the formula that we applied here is Dividing the value of B2 by the value of cell C2 and it will return the result in the form of a percentage. 

12. SUMIF

The SUMIF function is used to sum the values in a range of cells that meets the criteria we specified as an If condition. 

The syntax for SUMIF function is as follows:

=SUMIF(range_of_cells, logical_condition)

Here in the syntax, the range of cells is used to give the function a range which we want to be added and in the second argument, a Logical statement is given to perform the condition inside that range of cells and return the final result. 

For example, if we give a condition that the numbers that are larger than 5 are needed to add to a column, then we can use the following example:

For e.g. =SUMIF(A2:A20,”>5”)

In the above example, the SUMIF function will find the numbers in the range A2:A20 which are larger than 5 and will return the sum of those numbers in the desired cell. 

13. TRIM

The TRIM function removes all spaces from the text except the single spaces between words. The TRIM function is very useful when we have copied any text from other applications and want to remove irregular spacing from the text. The TRIM function makes it easier to remove irregular spacing from the text. 

The syntax of the TRIM function is as follows:

=TRIM(“   any string is provided   here”)

In the above syntax, as we can see the unwanted spaces are there in the string which need to be removed and to do that, we can use the TRIM function that will remove the unwanted spaces from the string and make our text in that string more clear. 

For e.g. = TRIM(“  First Quarter Sales “)

The above example will remove the unwanted space from the text and give the results as “First Quarter Sales”. We can specify the Cell reference or a range of cells from which we want to remove irregular spaces. 

14. LEFT, MID, and RIGHT

The LEFT() function is used to find the characters of a string from the left or start of a text string. The MID() function is used to get the character from the middle of a text string. Lastly, the RIGHT() function will give the result of characters from the end of a text string. To better understand these functions, you should carefully look at these examples:

For e.g.

=LEFT(‘Apple’,3)

The above example will return ‘App’ for 3 characters that we specified in our formula as 3. We can provide the cell references or range of cells in place of the text string.

=MID(‘Apple’, 2, 2)

The MID function will return ‘pp’. As the first argument 2 is taken to select the character and the second argument takes the next 2 characters from the text string and returns the result. 

=RIGHT(‘Apple’, 3)

For the RIGHT function, it takes 3 characters from the end of the string and returns as a result. For the above formula, it will return ‘ple’ as a result. 

15. VLOOKUP

VLOOKUP is a function that helps you to find things in a table or a range of cells by row. For example, when we need to find if a value exists in our table or not, then we use the VLOOKUP function. The VLOOKUP function takes various arguments such as if we are finding any value or if we want to return a corresponding value to that value we need to find in the table. So here, we will look at some examples to better understand this function. 

e.g.1 

=VLOOKUP(B3, B2:E7,2, FALSE)

In the above example, VLOOKUP finds the value of the B3 cell in the table ranging from B2:E7 and gives the corresponding value of B2 in the 2nd column, lastly FALSE is used to give the exact match of the value we are looking for. 

e.g.2 

=VLOOKUP(102,A2:D6,2,FALSE)

The 2nd example shows that the VLOOKUP function is searching for 102 within the table ranging from A2:D6 and will return the value from the 2nd column in the range if it finds an exact match in the table (False function is used). 

e.g.3

= IF(VLOOKUP(102,A1:E6,2,FALSE)=”Sousan”, “Location”, “Not found”)

In the above example, the LOOKUP function is used with the condition using the IF statement where the value ‘Sousan’ in the 2nd column is corresponding to the value 102 or not. If it is found at that location, it will return Located, otherwise, Not found.

Let us understand the VLOOKUP function in a very simple language. The budget of home supplies in the sheet has a serial number column that uniquely identifies any specific item in the budget. And suppose if you have that serial number of an item and you want to know the item description inside the table. At that time you can use the VLOOKUP function. 

16. RANDOMIZE

This function is used to return the random real numbers which are greater than or equal to 0 and less than 1. Whenever the sheet is calculated, it will return a new random number each time. The function RAND() is very useful when we need to find any random roll number of students or any other information in our worksheet. 

For e.g.

=RAND()

This function will return a random number between 0 and 1.

If we use =RAND()*100, it will return a number between 0 and 100. For recalculation in the worksheet or to get a new random number in our function, the F9 function key is used. It will run the function again returning a new value as a result for the specified range. 

17. INDEX-MATCH

This function is very useful to find a value in a column to the left. When we use VLOOKUP and get stuck while returning an appraisal from a column towards the right, you can use the INDEX-MATCH function instead of VLOOKUP which will consider the lookup column and the return column. 

For e.g.

=INDEX(A1:D11, MATCH(“America”, B1:B11,0),4)

In this example, we are looking for America in our table ranging from B1:B11 and suppose it is found in row 4 using the MATCH function. Then, INDEX will lookup for the argument and find the corresponding value in the 4th column as we used 4 as the last argument in the formula. So, it will return the value corresponding to America in that specific column. You should give it a try and find how it can be used effectively. 

18. HLOOKUP

This function searches for a value in the top row of the table or range of cells that we provided. It returns the value in the same column from the row that we specified in the table. The HLOOKUP() function is similar to LOOKUP(). The difference is just that in the HLOOKUP function, it searches for the value horizontally while in the VLOOKUP function, it searches for the value Vertically. 

The syntax of HLOOKUP function is as follows:

=HLOOKUP(lookup_value, array_table, index_no_of_row, [lookup_range])

Here in the syntax above, we can clearly see the arguments where all the arguments except lookup_range are mandatory and need to be given inside the parentheses. The lookup value is the value that we are looking for in the cells. The array table is the table of information in which our data is looked up. And index_no_of_row is the row number in the table array in which we are trying to find the lookup value. 

For e.g. =HLOOKUP(“Axles”, A1:C4, 2, TRUE)

The above example looks for ‘Axles’ in row 1 of the table and returns the value from row 2 which is in the same column of Axles. 

19. DATEDIF

This function returns the difference between two dates after calculating the difference based on days, months, or years. The Function becomes very useful when calculating the Age of people in a table. 

The syntax for the DATEDIF function is as follows:

=DATEDIF(start_date, end_date, unit)

In the above syntax, the arguments are mandatory where the start_date is the first date of a given period and may be entered as text strings inside quotation marks. End_date is the last or end date to calculate the number of days, months, or years between these two dates. The last Unit is used to get the result in a specific manner such as if we want to calculate only the years between two dates, we use “Y” as a unit or if we want to calculate the month’s difference between these dates, we need to use “M” for units. The same can be used for Days as “D”. Any combination of these can also be used as a single unit. 

Let us see an example below to better understand the concept of the DATEDIF function.

For e.g. =DATEDIF(A2, B2, “Y”)

Here in the example above cell A2 gives the starting date whereas B2 gives the end date and Y is the unit in which we want our result to be. Such that “Y” is used for Years, “M” can be used for Months, and “D” is used for Days in that period. 

20. TIME()

The TIME function returns the decimal number for any specific time. The result of this function formats the cell as a date even if the cell format was General before entering the function. 

The syntax for the TIME() function is as follows:

=TIME(hour, minute, second)

In the above syntax, all the three arguments are required where the numerical values for all three arguments vary from 0 to 32767 based on the time. 

For e.g.

=TIME(A3,B3,C3)

The TIME function will combine the cells in the format of TIME as Hours, Minutes, and Seconds. And the result will be a decimal number based on the time. 

21. TODAY()

When we need to use the current date in our worksheets, we can use the TODAY() function. It returns the serial number of the Current date where the serial number is the code for date-time used by Microsoft Excel for calculations of date and time. TODAY() function is also helpful to find the intervals. Let us see an example of the TODAY() function.

For e.g.

=TODAY()

The function will return the current date in the format MM/DD/YEAR

Another example can also be as follows: 

=TODAY() + 5

In this example, 5 days are added to the current date such as if the date of today is 4/18/2022, then 5 days will be added to this date and the resulting answer will be 4/23/2022.

22. SUBSTITUTE

This function is used to replace any existing text with a specific text in a text string. The SUBSTITUTE function takes two arguments, first is the text or cell reference with which we want to Substitute characters, second is the Old text that you want to replace and lastly New text that you want to replace the old text with. 

Let us see the syntax for using SUBSTITUTE function below:

=SUBSTITUTE(text,old_text,new_text,[instance])

In the above syntax, all the arguments except instance are mandatory where text is the cell reference in which we want to change the text, old text is the text which we want to be changed with the new text that we specified in the next argument. And lastly, the instance is to replace the text. 

For e.g.

= SUBSTITUTE(A2, “SALE”, “DISCOUNT”)

In the example, the text “SALE” in cell A2 will be replaced with the text “DISCOUNT”.

23. REPLACE

The REPLACE function replaces a part of a text string with a different string based on the number of characters that we specify with that string. 

The syntax for REPLACE function is as follows:

=REPLACE(old_text, start_num, num_of_chars, new_text)

In the above syntax, the old text is the text which we want to be replaced, we can also specify the cell reference over there. After that, the second argument takes the number of starting locations in the text and the third argument is the number of characters from the starting number of the text and lastly, the new text that we want to be replaced in place of that text from starting number up to the number of characters of next arguments. 

For e.g.

=REPLACE(A3,6,5,”*”)

In the above example, the function replaces five characters in the string from A3 Cell from the 6th character with a single ‘*’ and returns the result.

24. CONCATENATE

The CONCATENATE() function is used to join or merge several text strings into a single text string. 

The syntax for this function is as follows:

=CONCATENATE(text1, text2,…)

Where text1 and text2 are the arguments of two strings that we want to join or merge into a single string. We can also use more than two arguments or strings to join all of them in a single string. 

There are some different ways to perform this function described with some examples below:

e.g1.

=CONCATENATE(A12, “ ”, B12)

The example above will concatenate two strings from cells A12 and B12 and give the joined string as a result.

e.g2.

=CONCATENATE(A12&” “&B12)

The above example is another method to join two strings and give the new merged string as a result. 

25. CEILING

The CEILING function is very useful in that it returns rounded up numbers to the nearest multiple of significance. For instance, if we want to avoid decimal numbers in our table and take the round off of the numbers, we use the CEILING function that rounds the number to the relevant multiple of significance. When using the CEILING function, it rounds the number away from zero.

The syntax for the function CEILING is as follows:

=CEILING(number, significance)

In the above syntax, the argument number is that number which we want to be rounded and the significance is multiple to use when rounding. See the example below for a better understanding of the function. 

For e.g.

=CEILING(2.5,1)

The above example rounds 2.5 for the nearest multiple of 1 which is 3. 

26. FLOOR

The FLOOR function rounds the number down, from zero to the nearest multiple of significance. Let us better understand the use of the FLOOR function with the help of an example below:

For e.g.

=FLOOR(3.4,2)

In the above example, the FLOOR function rounds the number 3.4 down towards zero to the nearest multiple of 2 which is also 2. So, it will give 2 as a result. 

27. POWER

The Power function returns the result of exponentiation of a number to a certain power. For example, you need to find the result of 5 raised to the power of 2, then you need to use the Power function and give these two arguments that will return the square of 5 i.e. 25. Let us see an example below:

For e.g.

=POWER(5,2)

The above example will return a result of 25 as the power 2 of 5 is 25. We can find the power of any number whether it is in fraction, it always gives an accurate result when we use the POWER function. 

28. MODULUS

The MODULUS function returns the remainder of a number after division. The resulting number has the same sign as the divisor. It becomes very useful when we need to find the remainder of any number after division. Let us see an example below:

For e.g.

=MOD(5,2)

The MOD function will return a value of 1 because when we divide 5 by 2 it will give a remainder of 1 and the positive sign is because the divisor is also positive. 

29. LEN

The LEN() function is useful when we need to find the total number of characters in a string. So, the LEN() function counts the number of characters in the string and returns it as a result. It also counts spaces and special characters. The example below shows how the LEN() function can be used.

For e.g.

=LEN(A7)In the above example, the LEN function will count all the characters in the string in Cell A7 and return the total number of characters as a result. 

Conclusion

Microsoft Excel is a very useful and powerful application when we need to analyze data and reports for various purposes. The formulas and functions that we discussed in this article are of great importance in our daily lives, whether we want to do some simple calculations or analyze data or reports. The formulas and functions are of great use in our everyday life. In this article, we looked at text, numeric, date-time, and some advanced formulas and functions of Microsoft Excel. As you have seen the usefulness of these formulas and functions that we discussed in this article today, it will help you out whenever you are stuck in any calculations in Microsoft Excel. 

The functions and formulas in Excel enable users to perform simple and complex calculations like finding totals for a row or column of numbers etc. These formulas and functions become very useful in more complex situations such as calculating math problems, solving engineering maths, creating financial models, calculating mortgage payments, etc. So, this is all about Microsoft Excel Formulas and functions in this article. Hope you may have learned something new. 

If you found this article helpful, then share it with others too. 

[ad_2]

Leave a Comment