Top Excel Formulas That You Should Know

Don’t know anything about formulas in Microsoft? Know about the top and most used excel formulas to make your work easier.

Microsoft Excel is one of the most used programs by all businesses. It makes working with data easier with its many fabulous functions. By working on this program, you can get insights and generate reports to understand the data and the business. 

There are several amazing built-in functions and features that the app offers and formulas are one of those features that makes all kinds of tasks super easy to do. 

What is MS Excel Formulas?

In MS Excel, formulas are an expression that operates on values in a range of cells. With these formulas, you can do all kinds of calculations on it without any hassle. There are many formulas that one can make use of to do their work but let us now look at the top most used formulas.

Most Popular Excel Formulas To Know

Excel formulas list that you need to know about for better operations on the program.

1. SUM

As the name suggests, the SUM function gives the total of the selected range of cell values. It does a mathematical operation which is addition. Here is how it is done:

Fig: Sum function in Excel

To find the total amount of sales for every unit, type in the function “=SUM(C2:C4)”. This automatically adds up to 300, 385, and 480. The result is stored in C5. 

2. AVERAGE

This function focuses on calculating the average of the selected range of cell values. In the example below, to find the avg of the total sales, you have to simply type in “AVERAGE(C2, C3, C4)”. 

Fig: Average function in Excel

It automatically calculates the average, and you can store the result in your desired location.

3. COUNT

This function counts the total number of cells in a range that contains a number. It does not include the cell, which is blank, and the ones that hold data in any other format apart from numeric. 

Fig: Microsoft Excel Function – Count

As seen above, we are counting from C1 to C4, ideally four cells. But since the COUNT function takes only the cells with numerical values into consideration, the answer is 3 as the cell containing “Total Sales” is omitted here. 

4. SUBTOTAL

The SUBTOTAL() function returns the subtotal in a database. You can select either average, count, sum, min, max, min, and others. 

Fig: Subtotal function in Excel

In the example above, the subtotal calculation is done on cells ranging from A2 to A4. The function used is “=SUBTOTAL(1, A2: A4), in the subtotal list “1” refers to average. Hence, the above function will give the average of A2: A4 and the answer to it is 11, which is stored in C5.

Similarly, “=SUBTOTAL(4, A2: A4)” selects the cell with the maximum value from A2 to A4, which is 12. Incorporating “4” in the function provides the maximum result. 

Fig: Count function in Excel

5. MODULUS

The MOD() function works on returning the remainder when a particular number is divided by a divisor.

• In the first example, we have divided 10 by 3. The remainder is calculated using the function “=MOD(A2,3)”. The result is stored in B2. We can also directly type “=MOD(10,3)” as it will give the same answer. 

Fig: Modulus function in Excel

• Similarly, here, we have divided 12 by 4. The remainder is 0, which is stored in B3. 

Fig: Modulus function in Excel

6. POWER

The function “Power()” returns the result of a number raised to a certain power. 

Fig: Power function in Excel

In the example above, to find the power of 10 stored in A2 raised to 3, we have to type “= POWER (A2,3)”. This is how the power function works in Excel.

7. REPLACE

As the name suggests, the REPLACE() function replaces the part of a text string with a different text string. 

The syntax is “=REPLACE(old_text, start_num, num_chars, new_text)”. Here, start_num refers to the index position you want to start replacing the characters with. Next, num_chars indicates the number of characters you want to replace. 

Let’s have a look at the ways we can use this function.

• Here, we are replacing A101 with B101 by typing “=REPLACE(A15,1,1,”B”)”. 

Fig: Replace function in Excel

• Next, we are replacing A102 with A2102 by typing “=REPLACE(A16,1,1, “A2”)”. 

Fig: Replace function in Excel

• Finally, we are replacing Adam with Saam by typing “=REPLACE(A17,1,2, “Sa”)”. 

Fig: Replace function in Excel

8. LEFT, RIGHT, AND MID

The LEFT() function gives the number of characters from the start of a text string. Meanwhile, the MID() function returns the characters from the middle of a text string, and the right() function returns the number of characters from the end of a text string.  

• In the example below, we use the function left to obtain the leftmost word on the sentence in cell A5.

Fig: Left function in Excel

Shown below is an example using the mid function.

Fig: Mid function in Excel

• Here, we have an example of the right function.

Fig: Right function in Excel

9. UPPER, LOWER, AND PROPER

This function converts any text string to uppercase. In contrast, the LOWER() function converts any text string to lowercase. The PROPER() function converts any text string to proper case, i.e., the first letter in each word will be in uppercase, and all the others will be in lowercase.

• We have converted the text in A6 to a full uppercase one in A7.

Fig: Upper function in Excel

• Text in A6 is converted to a full lowercase one, as seen in A7.

Fig: Lower function in Excel

• Text in A6 is converted to a clean and proper format in A7.

Fig: Proper function in Excel

10. TODAY

The TODAY() function in Excel provides the current system date.

Fig: Today function in Excel

The function DAY() is used to return the day of the month. It will be a number between 1 to 31. 1 is the first day of the month, and 31 is the last day of the month.

Fig: Day function in Excel

The MONTH() function returns the month, a number from 1 to 12, where 1 is January and 12 in December.

 Fig: Month function in Excel

The YEAR() function, as the name suggests, returns the year from a date value.

Fig: Year function in Excel

11. TIME

The TIME() function converts hours, minutes, and seconds given as numbers to an Excel serial number, formatted with a time format.

Fig: Time function in Excel

12. HOUR, MINUTE, SECOND

The HOUR() function generates the hour from a time value as a number from 0 to 23. Here, 0 means 12 AM, and 23 is 11 PM.

Fig: Hour function in Excel

The function MINUTE(), returns the minute from a time value as a number from 0 to 59.

Fig: Minute function in Excel

The SECOND() function returns the second from a time value as a number from 0 to 59.

Fig: Second function in Excel

13. VLOOKUP

This stands for the vertical lookup that is responsible for looking for a particular value in the leftmost column of a table. It then returns a value in the same row from a column you specify. 

Below are the arguments for the VLOOKUP function:

lookup_value – This is the value that you have to look for in the first column of a table.

table – This indicates the table from which the value is retrieved.

col_index – The column in the table from the value is to be retrieved.

range_lookup – [optional] TRUE = approximate match (default). FALSE = exact match.

We will use the below table to learn how the VLOOKUP function works.

If you wanted to find the department to which Stuart belongs, you could use the VLOOKUP function as shown below:

Fig: Vlookup function in Excel

Here, the A11 cell has the lookup value, A2: E7 is the table array, 3 is the column index number with information about departments, and 0 is the range lookup. 

If you hit enter, it will return “Marketing”, indicating that Stuart is from the marketing department.

14. IF Formula

The IF() function checks a given condition and returns a particular value if it is TRUE. It will return another value if the condition is FALSE.

In the below example, we want to check if the value in cell A2 is greater than 5. If it’s greater than 5, the function will return “Yes 4 is greater”, else it will return “No”.

                     Fig: If function in Excel,

In this case, it will return ‘No’ since 4 is not greater than 5.

‘IFERROR’ is another function that is popularly used. This function returns a value if an expression evaluates to an error, or else it will return the value of the expression.

Suppose you want to divide 10 by 0. This is an invalid expression, as you can’t divide a number by zero. It will result in an error. 

The above function will return “Cannot divide”.

How Lio can Help You

Lio is a great platform that can help entrepreneurs, homemakers, students, businessmen, managers, shop owners and many others. This mobile application helps to organize business data and present them in an eye-catching manner. 

Lio is a great platform for small business owners and can track a wholesome record of employee information for better employee management, customer data, etc. You can handle those data with ease.

If you want to be a professional, then you must save your time, you need to learn to arrange all the business strategies in one place. In that case, Lio can be your partner.

Entrepreneurs can also allow multiple authorized users of their office to access the information from various locations within minutes.

Lio is definitely for the win and using it for your business is only going to make your journey smooth and easy to track.

Step 1: Select the Language you want to work on. Lio on Android

Step 2: Create your account using your Phone Number or Email Id.

Verify the OTP and you are good to go.

Step 3: Select a template in which you want to add your data.

Add your Data with our Free Cloud Storage.

Step 4: All Done? Share and Collaborate with your contacts.