Microsoft Excel is an application that is an immensely great tool for working with data. It is widely used by many industries for saving data, generating reports and getting business insights.
One of the advantages of Excel over other similar data applications is that it allows you to create Excel formulas, allowing you to perform calculations, searches and data analysis among others. In this article, we will show you the 40 most useful Excel formulas and functions you could use.
Before we go into details, let’s first define what formula and function are in Excel.
Formula – Excel formula is an expression that calculates the values in a selected range of cells. Formulas allow you to perform simple calculations such as addition, subtraction and averages as well as manipulate time and date values and more.
Function – Excel function is a predefined formula that is already available in Excel. Functions can perform complex calculations that can’t be done manually. In Excel, the names of the functions reflect their intended operation.
Oftentimes, formulas and functions are used interchangeably.
There is another term that is very familiar to Excel formulas, and that is “function”. The two words, “formulas” and “functions” are sometimes interchangeable. They are closely related, but yet different. A formula begins with an equal sign. Meanwhile, functions are used to perform complex calculations that cannot be done manually. Functions in Excel have names that reflect their intended use.
1. Addition
The SUM function gives you the total of the values in a selected range. As the name suggests, it does the mathematical calculation of addition.
Basic formula:
=SUM(cell range)
In the above example, to get the sum of Revenue, the formula is =SUM(D3:D7). This formula will automatically add up all the values from D3 to D7, and the total will be saved in D9.
You may also be able to get the sum of values by using the plus operation. Example =1600+2500. Of course this is only practical to use if you are adding only a few values.
2. Subtraction
There is no named function for subtraction in Excel but you will be able to do subtraction by using the minus sign (-).
Basic formula:
=cell1 - cell2 or value1 - value2
In the example, to get the difference between $1,600 and $100, the formula is =D3-E3. Of course, you can subtract more numbers from $1,600, you just have to add the minus sign like -D3-E3-E4-E5.
Alternatively, you can also type in the formula =$1600-$100.
3. COUNT
The COUNT function lets you count the number of cells in a range that contain a number(s). It’s important to note that it does not count cells with no values or cells that have values that are not in numeric format.
Basic formula:
=COUNT(cell range)
In the example, the formula =COUNT(E3:E7) should return an answer 5 which is the number of cells from E3 to E7.
If we use the COUNT function for A3:A7, the resulting value will be 0 since the cells in this range contain non-numeric values.
4. COUNTA
In the example we provided for the COUNT function, the formula did not work for A3:A7 because those cells contained texts aside from the numbers. In this case, we should use the COUNTA function.
The COUNTA function is ideal for counting the number of cells where each cell may have non-numeric values. However, COUNTA will not count blank cells.
Basic formula:
=COUNTA(cell range)5. COUNTBLANK
If you want to count the number of cells that are blank in a given range, the function to use is COUNTBLANK.
Basic formula:
=COUNTBLANK(cell range)6. AVERAGE
The AVERAGE function allows you to get the average of values in a given range.
Basic formula:
=AVERAGE(cell range)
In the above example, the formula will return a value of 110 which is the average of the values in the selected range.
7. MEDIAN
Median is the middle value of a dataset or numbers if arranged in ascending order (from lowest to highest)
Basic formula:
=MEDIAN(cell range)
In the formula =MEDIAN(B3:B7), the resulting value is 90.
8. MAX
The MAX function allows you to get the largest value in a set of numbers within a range.
Basic formula:
=MAX(cell range)Example
To get the largest number in the selected range which is 250, the formula is =MAX(B3:B7).
9. MIN
The MIN function is the opposite of MAX as this one lets you get the lowest value within a given range. In the example, you can get the minimum or lowest number which is 50, the formula is MIN(B3:B7)
Basic formula:
=MIN(cell range)CONCATENATE is a formula that you can use to combine the value from two or more cells into one cell.
Basic formula:
10. CONCATENATE
=CONCATENATE(text1, text2)Alternatively, you can also use the CONCATENATE operator &, example =text1&text2
To separate the combined values with a space, you need to add the space character (” “) in the formula. In the above example, the formula should be =CONCATENATE(A3,” “,B3)
11. TRIM
If your correct Excel formulas are returning a bunch of errors, one of the things you need to check is the extra spaces in the referenced cells.
There are various ways on how you can use the TRIM function to remove unwanted spaces but we will just discuss here the easiest one.
Basic formula:
=TRIM(cell1)
12. YEAR
If you want to pick up the year only in a value that contains the complete date, the function to use is YEAR.
Basic formula:
=YEAR(cell1)
13. MONTH
In the above example, if you want to pick up the month only, the function to use is MONTH.
Basic formula:
=MONTH(cell1)14. WEEKNUM
The WEEKNUM function will give you the week number of a specific date. It will return a value from 1 to 52 representing the weeks of the year.
Basic formula:
=WEEKNUM(cell1)15. MOD
The MOD function returns a value that represents the remainder when a number is divided by a particular divisor.
Basic formula:
=MOD(dividend, divisor)
For example, if you want to know the remainder when 100 will be divided by 8, the formula is =MOD(100,8). This formula will return a value of 4.
16. POWER
The POWER function returns a value representing the result when a number is raised to a certain power.
Basic formula:
=POWER(number, power the number is raised to)
For example, if you want to know how much 10 raised to the power of 5 is, the formula is =POWER(10,5). It will give you 100,000 as the result.
17. CEILING
The CEILING function rounds a number to the nearest highest multiple specified in the formula.
Basic formula:
=CEILING(value1, multiple)
For example, if you want to round 95.67 to the nearest multiple of 10, the formula is =CEILING(95.67,10) and the resulting value will be 100.
18. FLOOR
The FLOOR function is the opposite of the CEILING function in that it rounds the number to the nearest lowest multiple specified in the formula.
If we get the FLOOR of 95.67 in a multiple of 10, it will give us the value of 90.
Basic formula:
=FLOOR(value1, multiple)19. LEN
The LEN function gives you the total number of characters which include the spaces and special characters in a string within a cell.
Basic formula:
=LEN(cell1)
For example, if you want to know the total number of characters in A2, the formula is =LEN(A2). It will give you the value 13 as a result.
20. SUBSTITUTE
Use the SUBSTITUTE function if you want to replace an existing text in a string with a new text.
Basic formula:
=SUBSTITUTE(reference cell, "old_text", "new_text") or =SUBSTITUTE(reference cell, old numeric value, new numeric value)Code language: JavaScript (javascript)
In the example, if you want to substitute PN with SN, the formula is =SUBSTITUTE(A3,”PN”,”SN”)
21. LEFT
This function gives you the characters starting from the left of a text string up to a certain number as stated in the formula.
Basic formula:
=LEFT(reference cell, number of characters from the left)Code language: JavaScript (javascript)
In the above example, if you want to extract the first 4 characters from the left of the product code, the formula is =LEFT(A3,4).
22. MID
Use the MID function if you want to get the characters in the middle of a text string.
Basic formula:
=MID(reference cell, starting position, length of characters to pick up)
In the example, we would like to get the numeric values in the middle of a string in A3. To do that, the formula is =MID(A3,6,4).
The number 6 here represents the position of the first character that we want to pick up, in this case the character right after the space.
The number 4, on the other hand, represents the total number of characters we want to pick up which is 4.
23. RIGHT
The RIGHT function gives you the characters from the end of the string.
Basic formula:
=RIGHT(reference cell, number of characters you want to pick up)
To get the last 5 characters in the string of values in A3, the formula is =RIGHT(A3,5).
24. UPPER
The UPPER function lets you convert the text values in a given cell into upper case.
Basic formula:
=UPPER(cell1)
25. LOWER
The LOWER function is the opposite of the UPPER function. It converts text values into lower case.
Basic formula:
=LOWER(cell1)26. PROPER
If you want to convert the text in a particular cell into proper format, use the PROPER function.
Basic formula:
=PROPER(cell1)
In the above example, we converted the value in B3 into proper format using the formula =PROPER(B3).
27. NOW
Use the NOW function if you want to indicate the current system date and time.
Basic formula:
=NOW()
28. TODAY
The TODAY function provides the current system date.
Basic formula:
=TODAY()
29. TIME
The TIME function will convert hour, minute, and second provided as numbers to a value formatted as time.
Basic formula:
=TIME(hour, minute, second)
In the example above, we converted 5, 16 and 20 to hour, minute and second, respectively, using the formula =TIME(5,16,20).
30. DATEDIF
The DATEDIF function lets you get the difference between two dates in terms of days, months, or years.
Basic formula:
=DATEDIF(first_date, second_date, "d"). You can replace <em>d </em>with <em>m </em>or <em>y </em>if you need the difference to be indicated in months or years, respectively.Code language: HTML, XML (xml)If you want to get the age of a person, simply get the difference between the year he was born and the current date. In the above example, we simply got the difference between B2 and B3 in terms of years using the formula =DATEDIF(B2,B3,”y”).
On the other hand, if you want to check the difference between two dates in terms of days, just replace “y” with “d”.
In the above example, we want to know the number of days to go before the event. Using the formula =DATEDIF(B3,B4,”d”), we were able to know that there are 161 days left before the event.
31. IF
The IF function is used to check a given condition and return a value if it is TRUE and another value if it is FALSE.
Basic formula:
=IF(logical test, "value_if_true", "value_if_false")Code language: JavaScript (javascript)
In the example above, if the value in column A is greater than 80, we want a return value “Meets quota” otherwise “No”. For A3, the formula is =IF(A3>80,”Meets quota”,”No”).
32. IFERROR
The IFERROR function returns a specific value or text if a formula or expression leads to an error.
Basic formula:
=IFERROR(value, value_if_error)
Suppose you want to divide values in column A by values in column B. If you will divide A3 by B3, you will get a return value of #DIV/0! since you cannot divide a number by zero. However, if you don’t want error messages like this to appear, but instead you want a value that says “cannot divide”, you can use the formula =IFERROR(A3/B3,”cannot divide”).
33. VLOOKUP
This advanced function is widely used for data reporting. VLOOKUP stands for vertical lookup that allows you to find a particular value in the leftmost column then returns a value in the same row from the column you specified.
Basic formula:
=VLOOKUP(lookup_value, table_array, col_index_num, range_lookup)It’s easier to understand the basic formula of VLOOKUP if you understand the following arguments:
- lookup_value – The lookup_value can be a specific value or a reference cell.
- table_array – The table_array is used for looking up the lookup_value. It is a range of cells where the VLOOKUP will find the lookup_value. For the table_array, you can use a table, a named range or names in the argument instead of using cell references.
- col_index_num – The col_index_num is the column in the table_array from where the value is to be retrieved.
- range_lookup – This is optional. You can indicate TRUE if you want the approximate match and FALSE if you want the exact match.
Let’s say you want to know how many “PN 003” products were sold, using columns A and B as your reference. The formula to use is =VLOOKUP(E3,A:B,2,FALSE).
34. HLOOKUP
HLOOKUP is very similar to VLOOKUP. However, the search is done horizontally and not vertically. This function looks for a value in the topmost row of a table array and gives you the value in the same column in the row you specified.
Basic formula:
=HLOOKUP(lookup_value, table_array, row_index_num, range_lookup)Arguments used in HLOOKUP:
- lookup_value – This is the value to look up
- table_array – This is the table array where you would need to retrieve data
- row_index_num – The row from which to retrieve data
- range_lookup – This is optional and the default is TRUE which means approximate match. If you want the exact match, indicate FALSE.
In the example above, we are looking for the city where a particular person (B7) lives. Using the formula =HLOOKUP(B7,A1:F3,3,FALSE), we can see that Frank Johnson is from Chicago.
35. COUNTIF
Use COUNTIF to count the number of cells in a given range that meet a given condition.
Basic formula:
=COUNTIF(range, criteria)
In the example, we want to know how many sellers are selling grapes, so we count the number of times “grapes” was listed within the range. Using the formula =COUNTIF(A3:A15,”grapes”), we get the value 6.
36. SUMIF
Use SUMIF to add the total values in a given range that meets a given condition.
Basic formula:
=SUMIF(range, criteria, sum_range)
In the example, we want to know the total volume of grapes sold. To do this, we will add the volume sold for grapes using the formula =SUMIF(A:A,”grapes”,C:C). It will give us a result of 30.
37. SUMIFS
The SUMIFS is similar to the SUMIF function. However, SUMIFS allows you to use more than one criteria.
Basic formula:
=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)
In the above example, we added another set of criteria which is volume sold greater than 5, so this time we want to know how many grapes were sold that are greater than 5.
Using the formula =SUMIFS(C:C,A:A,”grapes”,C:C,”>5″), we can get the value 16 – it adds up the values in rows 9 and 15 because those two rows are the only ones with volume sold greater than 5.
38. AND
AND returns TRUE if all the given conditions are met and FALSE if otherwise. It is rarely used on its own, but instead, is commonly used in conjunction with other functions such as the IF function.
Basic formula:
=AND(criteria1, criteria2, return_value_if_true, return_value_if_false)
In our example, we want to know if each seller meets their target volume sold which is volume sold greater than 5. Using the formula =IF(AND(C3>5, D3>5), “Meets target”, “No”), we can see that only Ross, Carla, Hance, Lance and Van meet the target.
39. OR
The OR function is similar to the ADD function in that it is also normally used with other functions. The only difference is that the OR function will return TRUE if any of the criteria is met, unlike AND wherein all the criteria should be met to return TRUE.
Basic formula:
=OR(criteria1, criteria2, return_value_if_true, return_value_if_false)
Using the same example we used for the AND function, only that we change AND to OR signifying that a seller Meets target if he or she sold more than 5 at any day. Using the formula =IF(OR(C3>5, D3>5), “Meets target”, “No”), we can see that there are only 3 sellers who did not meet the criteria.
40. WORKDAY
The WORKDAY function allows you to know the future date after adding a specific number of days to a particular date.
Basic formula:
=WORKDAY(start_date, days, [holidays])Note: For the days, add the total workdays to be added. This should exclude holidays and weekends).
In this example, using the formula =WORKDAY(B3,10), we’ve identified the completion date to be July 6, 2022 after adding 10 working days and without counting Saturday and Sunday.
Summary
Excel is a powerful spreadsheet application that allows you to use a wide range of formulas and functions. We hope that our list could help you optimize the use of Excel for your data analysis and reporting.