Follow Us :

DG institution | Training and development in Dubai logo

Courses

25 Must-Know Formulas to Excel at Microsoft Excel

25 Must-Know Formulas to Excel at Microsoft Excel
Feature image for our blog - 25 must-know formulas to excel at micro-soft Excel

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
 
Let’s dive deeper into them.

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.

COUNT

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.

COUNTIF

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.

IF

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”.

VLOOKUP

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.

HLOOKUP

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).

CONCATENATE

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 advance level Excel course in Dubai, DG Training is your ideal destination.

FAQs

What is an excel formula?

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.

What are the steps to create a formula in excel?

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.

Define cell references.

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.

Published: April 21, 2023

Last updated: December 27, 2023

Enroll into our Courses

Dr. Shahryar Banan for Digi Training

Dr. Shahryar Banan

Dr. Shahryar Banan is an eminent English educator based in Dubai, renowned for his extensive expertise. With a PhD in TEFL and 14 years of teaching experience, he excels in instructing English as a foreign language and crafting dynamic course curricula, including specialized offerings like Business English. As a sought-after IELTS and PTE instructor, Dr. Banan has guided numerous students to success in language proficiency exams. He holds a prestigious Cambridge

CELTA certificate and contributes to language assessment as a Cambridge OET interlocutor. His dedication to language education in Dubai has established him as a trusted authority, empowering learners with invaluable language skills.

Enquire Now

As CEO of Digi Institution, it is a privilege and an honor to speak for you and convey my most sincere thanks for your involvement in our educational institution. Digi Institution’s purpose is to be a learning lighthouse, delivering a comprehensive selection of courses that will help individuals cultivate their professional as well as personal interests.

I am a firm believer that education is the foundation of personal success and development. The key opens doors to new possibilities, broadens perceptions, and ignites desire. Our educational institution is committed to cultivating this study culture and ensuring that every student who walks through our doors has an exceptional educational experience.

We have confidence in our distinguished teaching staff who are specialists in their professions and passionate about passing on their knowledge to our students. They serve as dedicated coaches who go above and above to offer advice, support, and inspiration. Our faculty members are constantly looking for new ways to teach, ensuring that our courses stay dynamic and relevant to the shifting requirements of businesses and society.

At Digi Institution, we understand that education involves more than just four walls. Our goal is to create an enjoyable and fascinating place to work that encourages innovative thinking, critical thinking, and teamwork. Learners are inspired to explore their hobbies, challenge their limits, and achieve their full potential with bright class discussions, linking projects, and extracurricular activities.

It is essential to be flexible and nimble in the modern age. In addition to business and management, we offer programs in technology, the arts and humanities, and health sciences. Our constant goal is to adjust our ways to match the changing labor market needs and to increase our students’ abilities to thrive in their chosen fields.

Furthermore, we think that learning constitutes an ongoing process. The commitment we make for our students does not end with graduation. Our mission is to create in them a lifelong love of studying and a quest for knowledge. Through possibilities for professional progression, skill refinement, and promotion, our training and development programs ensure that our students remain at the cutting-edge of their industry.