Top Excel Formulas With Examples (Free Cheat Sheet)

Introduction

Microsoft Excel is one of the most widely used tools for working with data, whether you are tracking budgets, building reports, or analyzing business performance. From simple calculations to structured reporting, Excel helps you turn raw numbers into insights quickly.

One of the biggest reasons Excel is so powerful is its formulas and functions, which let you automate calculations and reduce manual effort. In this guide, you will learn the top Excel formulas and functions everyone should know, along with clear examples you can reuse in real spreadsheets.

What Are Excel Formulas and Functions?

In Microsoft Excel, formulas and functions help you calculate and analyze data quickly. A formula is any calculation you write in a cell that starts with an equals sign, like multiplying two cells. A function is a built-in Excel formula (with a name like SUM or PRODUCT) that does the calculation for you, especially when the logic gets more complex.

Excel Formula vs Function

If you aim to build a stronger foundation in analytics beyond Excel, consider exploring a data analytics certification to deepen your expertise in data-driven decision-making.

Excel Formulas and Functions Cheat Sheet

These are the Excel formulas and functions you will use most often as you start working with spreadsheets. Use this cheat sheet as a quick reference while practicing, so you can calculate totals, apply simple logic, and analyze data faster.

Top Excel Formulas and Functions

Mathematical and Calculation Formulas

There are plenty of Excel formulas and functions depending on what kind of operation you want to perform on the dataset. We will look into the formulas and functions on mathematical operations, character-text functions, data and time, sumif-countif, and few lookup functions. 

1. SUM

The SUM() function, as the name suggests, gives the total of the selected range of cell values. It performs the mathematical operation which is addition. Here’s an example of it below:

=SUM(C2:C4)

Fig: Sum function in Excel

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

2. AVERAGE

The AVERAGE() function focuses on calculating the average of the selected range of cell values. As seen from the below example, 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

The function COUNT() counts the total number of cells in a range that contains a number. It does not include the blank cell and the ones that hold data in formats format other than numeric. 

=COUNT(C1:C4)

Fig: Microsoft Excel Function - Count

As seen above, here, 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. 

If you are required to count all the cells with numerical values, text, and any other data format, you must use the function ‘COUNTA()’. However, COUNTA() does not count any blank cells.

To count the number of blank cells present in a range of cells, COUNTBLANK() is used.

4. SUBTOTAL

Moving ahead, let’s now understand how the subtotal function works. The SUBTOTAL() function returns the subtotal in a database. Depending on what you want, you can select either average, count, sum, min, max, min, and others. Let’s have a look at two such examples.

Fig: Subtotal function in Excel

In the example above, we have performed the subtotal calculation on cells ranging from A2 to A4. As you can see, 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)”

This 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. Let’s now have a look at the examples below for better understanding.

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

      =MOD(A2,3)

Fig: Modulus function in Excel

• Similarly, here, we have divided 12 by 4. The remainder is 0 is, 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. Let’s have a look at the examples shown below:

Fig: Power function in Excel

As you can see 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. CEILING

Next, we have the ceiling function. The CEILING() function rounds a number up to its nearest multiple of significance.

Fig: Ceiling function in Excel

=CEILING(A2,1)

The nearest highest multiple of 5 for 35.316 is 40.

Want to stand out in this competitive market? Join our Data Analyst program, and take the first step towards a rewarding career in Data analysis! 📈

8. FLOOR

Contrary to the Ceiling function, the floor function rounds a number down to the nearest multiple of significance.

Fig: Floor function in Excel

=FLOOR(A2,1)

The nearest lowest multiple of 5 for 35.316 is 35.

9. MIN

The MIN() function returns the smallest numeric value from a selected range of cells.

=MIN(C2:C10)

Use MIN when you need the lowest sales value, smallest score, or minimum time from a list.

10. MAX

The MAX() function returns the largest numeric value from a selected range of cells.

=MAX(C2:C10)

Fig: Max function in Excel

Use MAX when you need the highest sales value, maximum score, or maximum time from a list.

11. ROUND

The ROUND() function rounds a number to a specified number of digits.

=ROUND(A2,2)

Fig: Round function in Excel

For example, if A2 contains 35.316, ROUND(A2,2) returns 35.32.

12. ROUNDUP

The ROUNDUP() function rounds a number up, away from zero, to a specified number of digits.

=ROUNDUP(A2,2)

Fig: Roundup function in Excel

Use ROUNDUP when you want to avoid underestimating values like prices, taxes, or buffers.

13. ROUNDDOWN

The ROUNDDOWN() function rounds a number down, toward zero, to a specified number of digits.

 =ROUNDDOWN(A2,2)

Fig: Rounddown function in Excel

Use ROUNDDOWN when you want conservative rounding for thresholds or truncation.

Text Cleanup and Formatting Formulas

14. CONCATENATE

The Concatenate function merges or joins several text strings into one text string. Given below are the different ways to perform this function.

• In this example, we have operated with the syntax:

  =CONCATENATE(A25, " ", B25)

Fig: Concatenate function in Excel

• In this example, we have operated with the syntax: 

        "=CONCATENATE(A27&" "&B27)"

Fig: Concatenate function in Excel

Those were the two ways to implement the concatenation operation in Excel.

15. LEN

The function LEN() returns the total number of characters in a string. So, it will count the overall characters, including spaces and special characters. Given below is an example of the Len function.

Fig: Len function in Excel

16. REPLACE

As the name suggests, the REPLACE() function works on replacing a 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

17. SUBSTITUTE

The SUBSTITUTE() function replaces the existing text with a new text in a text string.

The syntax is “=SUBSTITUTE(text, old_text, new_text, [instance_num])”.

Here, [instance_num] refers to the index position of the present text more than once.

Given below are a few examples of this function:

• Here, we are substituting “I like” with “He likes” by typing:

        “=SUBSTITUTE(A20, "I like","He likes")” 

Fig: Substitute function in Excel

• Next, we are substituting the second 2010 that occurs in the original text in cell A21 with 2016 by typing “=SUBSTITUTE(A21,2010, 2016,2)”.

Fig: Substitute function in Excel

• Now, we are replacing both the 2010s in the original text with 2016 by typing “=SUBSTITUTE(A22,2010,2016)”.

Fig: Substitute function in Excel

18. LEFT, RIGHT, 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, given a starting position and length. Finally, the right() function returns the number of characters from the end of a text string.

Let’s understand these functions with a few examples.

• In the example below, we use the function left to obtain the leftmost word in 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

19. UPPER, LOWER, PROPER

The UPPER() 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 other will be in lowercase.

Let’s understand this better with the following examples:

• Here, we have converted the text in A6 to a full uppercase one in A7.

Fig: Upper function in Excel

• Now, we have converted the text in A6 to a full lowercase one, as seen in A7.

Fig: Lower function in Excel

• Finally, we have converted the improper text in A6 to a clean and proper format in A7.

Fig: Proper function in Excel

20. TRIM

The TRIM() function removes extra spaces from text. It keeps single spaces between words and removes leading and trailing spaces.

=TRIM(A2)

Fig: Trim function in Excel

Use TRIM when copied data has inconsistent spacing.

21. CLEAN

The CLEAN() function removes non-printable characters from text.

=CLEAN(A2)

Fig: Clean function in Excel

Use CLEAN when imported data contains hidden characters that break matching or formatting.

22. CONCAT

The CONCAT() function combines text from multiple cells into one text string.

=CONCAT(A2," ",B2)

Fig: Concat function in Excel

CONCAT is a modern alternative to CONCATENATE for combining text.

23. TEXTJOIN

The TEXTJOIN() function combines text from a range and lets you specify a delimiter. It can also ignore empty cells.

=TEXTJOIN(", ",TRUE,A2:A6)

Fig: Textjoin function in Excel

Use TEXTJOIN when you want a clean comma-separated list from multiple cells.

24. TEXT

The TEXT() function formats a number or date and returns it as text using a format code.

=TEXT(A2,"0.00")

Fig: Text function in Excel

Use TEXT when you want consistent formatting inside a combined string, such as reports or labels.

Dates and Time Formulas

Now, let us hop into exploring some date and time formulas in Excel.

25. NOW()

The NOW() function in Excel gives the current system date and time.

Fig: Now function in Excel

The result of the NOW() function will change based on your system date and time.

26. 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 and 31. 1 is the first day of the month, 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 is 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

27. TIME()

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

Fig: Time function in Excel

Designed for aspiring analysts and professionals looking to elevate their careers, Simplilearn's Data Analyst Course equips you with hands-on experience and tools to ace business challenges.

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

29. DATEDIF

The DATEDIF() function provides the difference between two dates in terms of years, months, or days.

Below is an example of a DATEDIF function where we calculate the current age of a person based on two given dates, the date of birth and today’s date.

Fig: Datedif function in Excel

Conditional Totals and Counting

Now, let’s skim through a few conditional functions in Excel that are popularly used to analyze data and create reports.

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

31. INDEX-MATCH

The INDEX-MATCH function is used to return a value in a column to the left. With VLOOKUP, you're stuck returning an appraisal from a column to the right. Another reason to use index-match instead of VLOOKUP is that VLOOKUP needs more processing power from Excel. This is because it needs to evaluate the entire table array that you've selected. With INDEX-MATCH, Excel only has to consider the lookup column and the return column.

Using the table below, let’s see how you can find the city where Jenson resides.

Fig: Index-Match function in Excel

Now, let’s find the department of Zampa.

32. COUNTIF

The function COUNTIF() is used to count the total number of cells within a range that meet the given condition. 

Below is a coronavirus sample dataset with information regarding the coronavirus cases and deaths in each country and region.

Let’s find the number of times Afghanistan is present in the table.

Fig: Countif function in Excel

The COUNTIFS function counts the number of cells specified by a given set of conditions.

If you want to count the number of days in which the cases in India have been greater than 100. Here is how you can use the COUNTIFS function.

33. SUMIF

The SUMIF() function adds the cells specified by a given condition or criteria.

Below is the coronavirus dataset, using which we will find the total number of cases in India till 3rd Jun 2020. (Our dataset has information from 31st Dec 2020 to 3rd Jun 2020).

 Fig: Sumif function in Excel

The SUMIFS() function adds the cells specified by a given set of conditions or criteria.

Let’s find the total cases in France on those days when the deaths have been less than 100. 

34. AVERAGEIF

The AVERAGEIF() function calculates the average of cells that meet a single condition.

=AVERAGEIF(B2:B20,">=80",C2:C20)

Fig: Averageif function in Excel

Use AVERAGEIF when you need an average for only the rows that match a criterion, such as a region, category, or score threshold.

35. AND

The AND() function checks multiple conditions and returns TRUE only if all conditions are TRUE.

=AND(A2>5,B2<10)

Fig: And function in Excel

AND is commonly used inside IF to apply multiple checks in a single decision.

36. OR

The OR() function checks multiple conditions and returns TRUE if any one condition is TRUE.

=OR(A2="Yes",B2="Yes")

Fig: Or function in Excel

OR is useful when meeting any one condition is enough to return a result.

37. IFS

The IFS() function checks multiple conditions and returns a value for the first TRUE condition.

=IFS(A2>=90,"A", A2>=75, "B", A2>=60, "C", TRUE ,"D")

Fig: Ifs function in Excel

Use IFS when you would otherwise need multiple nested IF statements.

Lookup and Matching

38. Index and Match

This is an Advanced Excel function. The MATCH function is designed to return the position of a value in a specified range, while the INDEX function returns a specific value present in a uni-dimensional range.

The MATCH function returns the position of the ID you are looking for. The INDEX function will return the value of the salary corresponding to the position.

Did You Know? 🔍

Microsoft Excel has garnered an incredible global user base, estimated over 1.5 billion users. (Source: Scott Max)

39. VLOOKUP

Next up in this article is the VLOOKUP() function. 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:

Arguments	Description
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, A11 contains the lookup value; A2:E7 is the table array; 3 is the column index for department information; and 0 is the range lookup.

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

40. HLOOKUP

Similar to VLOOKUP, we have another function called HLOOKUP() or horizontal lookup. The function HLOOKUP looks for a value in the top row of a table or array of benefits. It gives the value in the same column from a row you specify.

Below are the arguments for the HLOOKUP function:

Arguments	Description
lookup_value	This indicates the value to lookup
table	This is the table from which you have to retrieve data
row_index	This is the row number from which to retrieve data
range_lookup	[optional] This is a boolean to indicate an exact match or approximate match. The default value is TRUE, meaning an approximate match.

Given the below table, let’s see how you can find the city of Jenson using HLOOKUP.

Fig: Hlookup function in Excel

Here, H23 has the lookup value, i.e., Jenson, G1:M5 is the table array, 4 is the row index number, 0 is for an approximate match.

Once you hit enter, it will return “New York”.

Excel is just the starting point for most analytics careers. To expand into statistical modeling, dashboards, business insights, and data strategy, a Data Analyst Course can provide structured, industry-relevant training.

Conclusion

Excel is a really powerful spreadsheet application for data analysis and reporting. After reading this article, you will have learned the important Excel formulas and functions that will help you perform your tasks better and faster. We looked at numeric, text, data-time, and advanced Excel formulas and functions. Needless to say, Excel knowledge goes a long way in shaping many careers.

Looking to advance your career in Data Analytics or Business Analytics? Simplilearn's Data Analyst Course and Business Analyst can help you achieve your goals! These comprehensive programs are designed to provide you with the skills and knowledge needed to excel in the field. Led by industry experts, the courses offer hands-on training, real-world projects, and personalized mentorship.

You can also learn Excel formulas by watching our well-researched video. Watch now!

FAQs

1. What are the basic formulas in Excel?

Basic formulas in Excel include arithmetic operations like addition, subtraction, multiplication, and division.

For example, SUM, AVERAGE, COUNT, and PRODUCT.

2. What are MS Excel formulas and functions?

MS Excel formulas and functions are expressions used to perform calculations or manipulate data in Excel. Formulas start with an equal sign (=) and can contain functions, mathematical operations, cell references, and constants.

3. How to write a formula in Excel?

To write a formula in Excel, start with an equal sign (=), followed by the formula expression. For example, to add two numbers in cells A1 and B1, write "=A1+B1" in another cell.

4. What are basic Excel skills?

Basic Excel skills include:

• Entering data
• Formatting cells
• Using basic formulas and functions
• Creating simple charts
• Sorting and filtering data
• Understanding cell references

5. What is VLOOKUP in Excel?

VLOOKUP is a function in Excel used to search for a value in the first column of a table range and return a related value from a specified column. It's commonly used for data lookup and retrieval.

6. What is a formula in Excel?

An Excel formula is a mathematical expression that works with values in a specific range of cells. These formulas yield a result, even if it's an error. They empower you to carry out addition, subtraction, multiplication, and division calculations within Excel.

7. What are the 5 important formulas in Excel?

Some important Excel formulas include:

1. SUM: The SUM formula adds the values in a range of cells
2. AVERAGE: The AVERAGE formula averages the values in a range of cells
3. COUNT: The COUNT formula calculates the quantity of cells that hold numeric values within a designated range of cells
4. IF: The IF formula assesses a condition and provides a specific output if the condition is true, or an alternative output if the condition is false
5. VLOOKUP: The VLOOKUP formula searches for a value in a table and returns the corresponding value from another column in the table

8. What is the difference between a formula and a function in Excel?

• A formula in Excel is a user-defined equation that performs calculations using cell references, operators, and values (e.g., =A1+B1)
• A function is a pre-built formula Excel provides to perform specific tasks, such as SUM(A1:A5). Functions simplify complex calculations, while formulas offer customization

9. How can I troubleshoot errors in my Excel formulas?

To troubleshoot Excel formula errors, check for typos, missing parentheses, or incorrect cell references. You should also verify data types and make sure functions are used correctly.

10. How do I update formulas when adding or deleting rows/columns?

To update formulas when adding or deleting rows or columns, use structured references or dynamic ranges like tables in Excel. Functions like SUM, AVERAGE, and VLOOKUP automatically adjust, but for fixed cell references (e.g., $A$1), update them manually or use relative references.

11. Can I use multiple functions in a single formula?

Yes, you can use multiple functions in a single formula by nesting them or combining them with operators. This will help you with complex calculations and data processing within a single formula.

12. Are Excel formulas the same in all versions of Excel? 

Excel formulas are mostly consistent across versions, but some functions may be updated, renamed, or newly introduced in later versions. You must note that compatibility issues can arise when using newer formulas in older versions.

13. Are there FREE resources to learn Excel?

Yes, there are free resources to learn Excel. The Introduction to MS Excel course from SkillUp by Simplilearn offers free training on Excel basics, including formulas, functions, and data management. It's a great way to build foundational Excel skills at zero cost.