Top 32 Excel formulas

Microsoft Excel is the most essential and important tool for each and every business as it helps in handling and manipulating data collected by the companies. It also helps in prediction of the data and can do alot more. Are you wondering how a simple spreadsheet can do so much for a business? Or you must be thinking how the manipulation and prediction of the data is done? Then, you have reached the right place. 

Microsoft Excel is a tricky as well as a simple tool for analyzing the data. It works on many formulas and functions which makes it easy to analyze and makes it a bit tricky to manipulate. One needs to have a good understanding of the formulas and should know how to use them at correct place where the particular formula is required. Formulas works on providing the calculation to the company just on one click. We have to understand the capabilities of the different formulas to apply them on the given data. Let’s start with all the formulas which a business needs. Here are the top 30 excel formulas that an e-commerce owner should know:

1. SUM

SUM is the most basic and foremost formula which is needed by all E-commerce businesses to add the data. SUM adds the values of the selected column or range with no time. 

For example: SUM(A1:D1)- It will add the values of A1,B1,C1 and D1.

2. MAX & MIN

E-commerce businesses usually have a long list of customers and selling items. MAX and MIN formulas helps the e-commerce business to easily find the costliest or the cheapest items. It also helps the business to find the customer with highest invoice or the lowest invoice.

For example: MAX(A1:A12)- This will give the maximum number between A1 and A12.

MIN(B4:B10)- gives minimum number between B4 and B10.

3. =IF

This formula tells whether a condition is true or not. With high amount of data, this formula is frequently used by e-commerce business to check whether a customer meets the discount criteria, whether a customer bought anything previously etc. 

For example: =IF(B6>=1000, “Yes”,” No”)- will tell the amount of B6 is greater than 1000 or not. 

4. =SUMIF

As we saw how SUM formula works and adds all the values of a particular range, SUMIF formula works similar but is an advanced version of SUM. If the business wants to calculate the total of the values after applying some condition, SUMIF formula does that for us. 

For example: SUMIF(A2:A10>20)- this will sum all the values between A2 and A10 which are greater than 20.

5. =TRIM

This works on beautifying the data. There’s a high need of this formula when the data is copied from elsewhere and is pasted in excel sheet. It helps in removing the extra spaces and beautifies the data. 

For example: =TRIM(A6)- The extra spaces will be removed from A6.

6. =CONCATENATE 

This formula helps in combining two cells. E-commerce business needs this formula as it helps them in combining things in less time. It helps in saving time.

For example: CONCATENATE(first-name, last-name)- This will combine first and last name of all the customers.

7. =PROPER

=PROPER is very useful formula for huge data as it organizes the data in proper cases. It converts the first letter of every cell to upper case. E-commerce businesses have large amount of data which can be unorganized due to many reasons. This formula helps them to organize the data. 

For example: =PROPER(D9)- It will convert the first letter of D9 to upper case. 

8. =TODAY

This formula automatically updates the current date and helps the business to reduce the mistakes regarding the dates. 

For example: =TODAY()- It updates the current date.

9. =EVEN & =ODD

These formulas round off the entered number to the nearest even and odd number. E-commerce business needs this formula for rounding of the prices, invoices and for many other purposes.

For example: =EVEN(A1)- It round of the number in A1 to its nearest even number.

=ODD(A1)- It round of the number in A1 to its nearest odd number.

10. =AVERAGE

AVERAGE formula helps the businesses to find averages in one click. E-commerce businesses need to calculate averages of various things like average number of investors, average sales etc. 

For example: =AVERAGE(A3:A22)- shows the average value of the numbers between A3 and A22.

11. =COUNT

COUNT formula counts the number of given values by skipping the empty entries and non numeric entries which tells the businesses about the skipped or empty entries. 

For example: If the operator put 5 cells as empty, The output of =COUNT(A2:A20) will be 12.

12. =COUNTA

COUNTA formula is similar to COUNT formula but it only skips the null or empty values and counts all the numeric and non numeric values. 

For example: If the operator put 2 strings and 3 empty values. The output of =COUNTA(A2:A20) will be 15.

13. =MODE

This formula returns the value with highest frequency and helps the businesses for analyzing various things.

For example: =MODE(A2:A15) will return the number with highest frequency between A2 and A15.

14. =LEN

LEN formula tells the length of a particular string or a character. It helps every business for calculating length of passwords, descriptions etc. 

For example: =LEN(A6) will return the length of the string in A6.

15. AND & OR

They work as the if formula as it check a condition if it proves true it returns pass, if not it returns false. But the formulas cannot be used by there own and are not independent.

For example: =IF(AND(B1<200, B2>100),”pass”, “fail”)

16. =NOW

This formula is similar to TODAY formula. It automatically updates date and time on one click. It helps e-commerce business to categorize and search the dates of data entry.

For example: =NOW() updates the current date and time.

17. =LEFT

It extracts the characters from a string and paste it in a cell. We have to put the string and the number of characters from left side which we want to take in the parentheses. It helps businesses to save time by extracting characters in case of semi duplicate entries. 

For example: A1= name,  =LEFT(A1,1) will paste n into another cell. 

18. =MID

=MID formula is similar to =LEFT formula and works on the same purpose and principle of extracting elements from a string to save time. It takes the elements from the middle of the string or a character. We have to input the string, a starting position and the number of characters we want. 

For example: A1=FirstName, =MID(A1,6,4) will return Name. 

19. =RIGHT

As the name suggests, this formula also helps in extraction of the elements of a string. It extracts characters from a string from right side. We have to input the string and number of characters we want to extract in the parentheses.

For example: A1= abc1234, =RIGHT(A1,4) will return 1234 as the output.

20. VLOOKUP

VLOOKUP is a very essential and important formula for each and every business as it searches and works with database. It looks for the common characters and returns the matching strings and save time o finding similar matches. It starts looking and matching strings and array from right most of the input. 

For example: =VLOOKUP(A2, B:C,3,FALSE)- will help you find the matching input. 

21. XLOOKUP

XLOOKUP formula looks and search for the entered array or a particular string in the data and returns the matched array or the closest array found in the data. This formula helps the businesses to search the entries when needed very easily. 

For example: =XLOOKUP(A1,B2:B10,C2:C10) will search A1 in between B2 and B10.

22. =YEAR

It tell the year of the entry and is of great use to businesses to quickly find and search the year of a particular entry. It creates a simple procedure to check and find the year of a particular entry.

For example: =YEAR(A2) will tell the year in which the operator entered or filled the cell A2.

23. =IFERROR

It is highly useful formula as it helps you to eliminate errors. If you think an error can come in case of doing any calculation during the entries, you can simply use IFERROR formula to return 0 instead of an error. 

For example: =IFERROR(A1/A2,0)

24. =TEXTJOIN

This formula is derived from the formula of CONCATENATE and works on the same purpose. People faced problems in CONCATENATE as CONCATENATE can only join 2 cells on a call. But, TEXTJOIN combines more than two cells on a single call. We have just input the starting cell, TRUE/FALSE to include empty cells or not and the ending cell. 

For example: =TEXTJOIN(A4,FALSE,E4) will combine all the cells (A4,B4,C4,D4 and E4) 

25. =CONVERT

This formula converts one unit to other unit. If you want to convert 1kg to grams, this formula will do that for you in a simple and effective way. 

For example: =CONVERT(A1,kg,g) will convert the value of A1 into grams.

26. =OFFSET

OFFSET formula helps in referring to a particular cell. It also helps in inserting or deleting rows and columns. We can point to a cell or a column with OFFSET formula. 

For example: OFFSET(A1,2,3,1,2) points to C2 and D3.

27. REPT

REPT formula repeats a particular value n number of times. It helps businesses to save time by replying strings with less time. E-commerce businesses need to enter duplicate values in the spreadsheet again and again which consumes a lot of times. This formula helps them to save time.

For example: If a customer bought a car and another customer bought the same car. The operator needs to enter the same name of the car and price. He/She will call the REPT formula by =REPT(A2,1).

28. =DOLLARDE

DOLLARDE formula works on conversation of various units into decimals. Businesses often use this formula for converting time into decimal. It looks complex but is very simple to use. 

For example: We know that 2:30 hours can be called as 2.5 hours in decimal value. We can convert it by =DOLLARDE(2:30,60). It will return 2.5 as output. 

29. DOLLARFR

DOLLARFR formula is the opposite of DOLLARDE formula and works on converting decimal value back into the actual unit. We can convert the decimal value back into time. 

For example: If we take the same example of 2:30 hours, we can convert it back by =DOLLARFR(2.5,60) will return 2:30 hours as the output.

30. =ISNUMBER

This formula is used to check a cell contains a numeric value or not. It returns the value as TRUE and FALSE. If the particular cell contains a number or a formula which returns a number, it will return TRUE as output otherwise it will return FALSE. 

For example: A6=100+2, =ISNUMBER(A6) will return TRUE as 100+2 is numeric formula and will result in a number. 

31. ISNA

ISNA formula checks the value N/A. It returns a logical value of TRUE or FALSE. If the value of the particular cell is found to be N/A, it returns TRUE as output otherwise it returns FALSE. Businesses use this formula to find the values which are not available.

For example: =ISNA(A1) will give TRUE as output if found to be N/A. If not, the output will be FALSE. 

32. EDATE

The formula EDATE is highly used by the businesses for scheduling and other purposes. It returns a date on the very day of the month in future as well as in past. It helps the businesses to calculate some important dates like expiry dates etc. 

For example: =EDATE(A3,2) where A3 tells the start date and 2 is the number of months from the start date. 

The use of excel in E-commerce businesses sometimes confuses the operators and can be sometimes frustrating, but these formulas make this a simple and easy job for operators. Using formulas in excel is important to reduce the errors and unavoidable mistakes. Using formulas becomes a habit when you practice applying them more and more. Hope this article helped you to get a deeper understanding of excel formulas.

Thank you!