Ready to explore the top 25 Excel formulas? Well, everyone knows about the Excel software, but how many of us are skilled at making the best use of all the features of Microsoft Excel? Excel comes off as intimidating to many first-time users, but with little practice and a good understanding of all the features, anyone can master Excel.
Today in the business world, Microsoft Excel is a viable and effective tool. Excelling at Microsoft Excel is a skill that everyone trying to make big in any industry should possess. An Excel spreadsheet is your go-to application to understand and determine large chunks of data. From creating graphs or charts or performing complex calculations, Excel makes everything possible.
What are Excel Formulas?
Formulas make it easier to use Microsoft Excel. There are built-in formulas in Excel that perform specific tasks, such as calculating the sum or average of cells.
Further in the blog, we have drafted a list of excel formulas:
- SUM
- AVERAGE
- MAX
- MIN
- COUNT
- COUNTIF
- IF
- VLOOKUP
- HLOOKUP
- CONCATENATE
- LEFT
- RIGHT
- MID
- TRIM
- LEN
- UPPER
- LOWER
- PROPER
- ROUND
- ROUNDUP
- ROUNDDOWN
- MOD
- TODAY
- NOW
- REPLACE
1. Sum
The SUM formula allows you to add a range of cells and find the total. This can be counted as one of the basic Excel formulas.
Formula =SUM(num1,num2,…)
To calculate the sum of a range of cells from A1 to A10 type =SUM(A1:A10) in a cell. Now press Enter to get the sum of the values in the range.
2. AVERAGE
This formula calculates the average value of a range of cells.
Formula =AVERAGE(num1,num2,..)
For example, to calculate the average of cells A1 to A10, select cell A11 ad type =AVERAGE(A1:A10) and press enter.
3. MAX
The MAX MS Excel formula makes it easier to calculate the highest value in a range of cells.
Formula =MAX(num1, num2,….)
For instance, If you quickly want to find the highest value in cells C12 through C20, enter MAX(C12:C20) in a cell. Press Enter to get the maximum value among the cells.
4. MIN
Minimum being the lowest, this MS Excel formula detects the lowest value in a range of cells.
Formula=MIN(num1, num2,….)
To find the lowest value in the same cell range of C12 to C20, enter =MIN(C12:C20) in an empty cell and press Enter.
5. COUNT
The COUNT MS Excel formula counts the number of cells in a range that contain numbers.
Formula =COUNT(num1,num2,..)
If you want to count cells or an array of numbers in a range of cells, say from A2 to A15, select cell A16 and enter =COUNT(A2:A15). Only the cells with numbers are counted, and the rest get omitted.
6. COUNTIF
The COUNTIF formula counts the number of cells in a range based on criteria.
Formula =COUNTIF(num1,num2,..)
For example, let’s start by picking out a certain criterion – count the number of cells in the cell range D1 to D20 that have a value greater than 5. In an empty cell type =COUNTIF(D1:D20,”>5″) and press Enter.
7. If
The IF formula in Excel is able to perform a logical test. This test can return values based on the credibility of the test. It allows you to evaluate whether the value or calculation that you want to return “Yes” if the logical_test evaluates it to be TRUE. On the other hand, you can set the return as “No” if the logical_test evaluates it to be FALSE.
Formula =IF (Logical_test, [value_if_true], [value_if_false]
For instance, if you type =IF (A1 >13, “Yes”, “No”) and press enter. Based on the condition, if the value in A1 is greater than 13, it will display “Yes”; otherwise, it will display “No”.
8. Vlookup
The term VLOOKUP is short for “vertical lookup”. This formula is useful if you have a table of data from which you have to find a specific value.
Formula =VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup])
For example, there is a table with the names of students going for a picnic in column A and their corresponding grades are listed in column B. You want to find the grade of a specific student. The student name you are looking for is in cell G2, and your table is in the range A6:B10. Enter the VLOOKUP formula =VLOOKUP (G2, A6:B10, 2, FALSE) and press Enter.
VLOOKUP formula will search for the value in cell G2 (the student name) in column A of the table (A6:B10) and return the corresponding value from column B (the grade) in the same row.
9. HLOOKUP
The HLOOKUP formula searches for a value in a row.
Formula: HLOOKUP (lookup_value, table_array, row_index_num, [range_lookup])
For example, you have a table with grocery names in row 1 and their prices in row 2, and you want to find the price of “Sugar”, enter HLOOKUP(“Sugar”, A1:D2,2, FALSE).
10. CONCATENATE
The CONCATENATE formula allows you to combine two or more words in different cells into one.
Formula =CONCATENATE (TEXT1, TEXT2,…)
Let’s say you have the word “Good” in cell A1 and the word “Morning” in cell B1. To merge the two words you could use the formula CONCATENATE (A1,” “, B1) and press enter.
11. Left
The LEFT formula is used to extract a specified number of characters from the beginning (left side) of a text sequence.
Formula=LEFT (text, num_chars)
For instance, you have a list of selected candidates’ names in column A, and you want to extract the first three characters from each name. In cell B1 type, =LEFT (A1, 3) and press Enter.
The result is displayed in cell B1. Copy the formula downwards for the other names in column A.
12. Right
Similar to the LEFT formula, the RIGHT formula is beneficial to extract a specified number of characters from the right side of a text sequence.
Formula=RIGHT (text, num_chars)
Now let’s understand how the RIGHT formula works.
For instance, you have a list of hotel room numbers in column A, and you want to extract the last three digits from each number. In cell B1, type=RIGHT (A1, 4) and press Enter. The result is displayed in cell B1.
13. Mid
Let’s understand how to remove required characters from the middle of a text.
Formula=MID (text, start_num, num_chars)
Here’s an example to demonstrate how the MID formula works:
In column E, there is a sequence of dates, and you want to extract the month from each date. In cell F1, type =MID (E1, FIND (“@”, E1) + 1, LEN (E1) – FIND (“@”, E1)). If you press Enter, you will get the month name.
14. Trim
The TRIM formula is used to remove leading and trailing spaces. It’s particularly helpful to avoid any discrepancies when dealing with data that might have extra spaces at the beginning or end.
Formula =TRIM (text)
Let’s say you have a list of ingredient measurements in column A. Now the measurements have some extra spaces at the beginning or end. In column B, select cell B1 and type =TRIM (A1) and press Enter.
15. Len
Let’s determine the length or number of characters by using the LEN ms excel formula. This formula calculates spaces and punctuation marks too.
Formula=LEN(text)
For example, you have a list of names in column A, some with surnames and some without surnames. You aim to find the length of each name. In cell B1, enter the LEN formula as =LEN (A1).
16. Upper
Do you want to convert everything into uppercase? The simple ms excel formula to do this is =UPPER (text)
For example, there is a list of company names in cell A1 written in lowercase letters to be converted into uppercase. In cell B1, type=UPPER (A1) and press enter.
The result would be displayed in cell B1. You can then copy the formula down to apply it to other cells if needed.
17. Lower
To convert into lowercase, you can use the LOWER formula.
Formula=LOWER (text)
For instance, there is the word “PLANT” in cell A1, and you want to convert it to lowercase. In cell B1, you can enter =LOWER (A1). This will convert the text in cell A1 to lowercase and display the result in cell B1 as “plant”.
18. Proper
The PROPER formula capitalizes the first letter. It is commonly used to format names or titles.
Formula=PROPER (text)
For instance, you have the name of a state “goa” in cell A1. Now you have to capitalize the first letter. In cell B1, you can enter =PROPER (A1) and press Enter. This changes “goa” to “Goa” in cell B1.
19. Round
The ROUND formula is used to round a number to a specified number of decimal places. It adjusts a given number to the nearest value based on the specified decimal place precision.
Formula=ROUND (number, num_digits)
For instance, if there is the number 2.24896 in cell A1, and you want to round it to two decimal places. In cell B1, type =ROUND (A1, 2). The number is displayed as 2.24 in cell B1.
20. Round up
The ROUNDUP formula is used to round a number up to a specified number of decimal places. It can also be rounded to the nearest whole number. It always rounds a number away from zero (upward).
Formula=ROUNDUP (number, num_digits)
If there is the number 3.1937 in cell A1, and to round it up to two decimals in cell B1, type =ROUNDUP (A1, 2). This formula will round up the number in cell A1 to two decimal places and display the result in cell B1 as 3.20.
The ROUNDUP function always rounds a number up, regardless of the decimal portion.
21. Rounddown
The ROUNDOWN formula is used to round a number down to a specified number of decimal places or the nearest whole number. It always rounds a number downwards. The ROUNDDOWN formula is =ROUNDDOWN (number, num_digits)
For instance, there is a number 3.1937 in cell A1, and you want to round it down to two decimal places. In cell B1, enter =ROUNDDOWN (A1, 2). This formula will round down the number in cell A1 to two decimal places and display the result in cell B1 as 3.19.
22. Mod
The MOD formula is used to calculate the remainder when one number is divided by another. It returns the remainder value after division.
Formula=MOD (number, divisor)
There is the number 20 in cell A1 and you want to find the remainder when it is divided by 3. In cell B1, enter =MOD (A1, 3)
The number is divided in cell A1 by 3, and returns the remainder, which is 2, in cell B1.
23. Today
This formula is to return the current date. It does not require any arguments or parameters. When the TODAY formula is used in a cell, it will dynamically update the current date each time the worksheet is recalculated or opened.
Formula=TODAY ()
For instance, if you enter =TODAY () in cell A1, the current date will get displayed in that cell. Each day, the date will update automatically to reflect the current date.
The TODAY function is commonly used in various scenarios, such as tracking project deadlines, calculating the age based on the current date and a birthdate, or performing date-based calculations.
24. Now
The NOW formula calculates the current date and time. It does not require any updates or parameters. It automatically updates the date and time.
Formula =NOW ()
25. Replace
This formula is used to replace a certain text or number with an alternate value. The proper value has to be mentioned below.
Formula: =REPLACE(old_text, start_num, num_chars, new_text)
Final Thoughts
To sum up, one can say that basic Excel formulas are resourceful. The frequent updates of MS Excel make the application more advanced. Every field is switching to using Excel, so being well-versed in the basics of Excel is helpful in the long run. The next time you open an Excel spreadsheet, navigate through the application with confidence rather than distaste.
If you are someone who is looking for basic Excel training or an advance level Excel course in Dubai, DG Training is your ideal destination.
FAQs
An excel formula is useful to perform calculations and automate tasks. All excel formulas start with an equal sign (=). Simple tasks such as addition or subtraction, for a range of cells, becomes easier with the help of formulas.
It’s not difficult to create a formula in excel. The user has to select the preferred cell and enter the equal sign (=), further type the required formula in the cell.
Excel spreadsheet consists of numerous cells and rows. Each cell has a specific address, which helps the user to easily access the required cell. Excel users largely rely on cell reference when using formulas.
