Top 20 Excel Formulas Everyone Should Know in 2026: Amazing

Hello Friends, in this blog post, we are going to explore the top 20 “Excel Formulas“.

As we know, Microsoft Excel is used for managing data, performing calculations,…

… analyzing information, and creating reports, and it is a widely used tool worldwide.

With the right Excel formula, you can make your work easier and save more time.

Excel formulas help you in many ways, such as calculating totals, finding specific information,…

… analyzing large datasets, automating repetitive tasks, and applying various conditions.

Using excel formula you can get far more quick result with full accuracy that is not usually possible when performed manually.

In this post, “Excel formulas,” we will explore the top 20 Excel formulas with examples that every beginner and professional should know in easy-to-understand language.

What Are Excel Formulas?

Excel formulas are expressions used to perform calculations or manipulate data in a worksheet. Every formula begins with an equal sign (=), followed by a function, cell reference, or mathematical operation.

For example:

=A1+B1

This formula adds the values in cells A1 and B1.

Excel provides hundreds of built-in functions, but learning a few essential formulas can help you handle most everyday spreadsheet tasks.

Top 20 Excel Formulas You Should Know

1. SUM() – Add Numbers Quickly

The SUM function adds numbers from individual cells or a range of cells. It is one of the most commonly used Excel formulas.

Syntax:

=SUM(number1, [number2], ...)

Example:

=SUM(A1:A10)

This formula adds all the numbers from cells A1 through A10.

When to use it:

• Calculate total sales.
• Add monthly expenses.
• Calculate total marks or revenue.
• Summarize financial reports.

Pro tip: Use SUM instead of manually adding multiple cells to reduce errors and save time.

2. AVERAGE() – Calculate the Average

The AVERAGE function calculates the arithmetic mean of a group of numbers.

Syntax:

=AVERAGE(number1, [number2], ...)

Example:

=AVERAGE(B2:B10)

This formula calculates the average of the numeric values in cells B2 through B10.

When to use it:

• Calculate students’ average marks.
• Find average monthly sales.
• Analyze average expenses.
• Measure average performance.

Note that AVERAGE ignores blank cells and text in referenced ranges, but includes zero values.

3. COUNT() – Count Numeric Cells

The COUNT function counts how many cells in a range contain numbers.

Syntax:

=COUNT(value1, [value2], ...)

Example:

=COUNT(A1:A20)

This formula counts the cells containing numeric values in the specified range.

When to use it:

• Count numerical entries.
• Check how many records contain numbers.
• Count completed numeric data entries.

Remember that COUNT does not count text entries. To count non-empty cells of any type, use COUNTA.

4. COUNTA() – Count Non-Empty Cells

COUNTA counts cells that contain data, including text, numbers, dates, and certain formula results.

Syntax:

=COUNTA(value1, [value2], ...)

Example:

=COUNTA(A2:A100)

This formula counts all non-empty cells in the range A2 through A100.

When to use it:

• Count customer names.
• Count employee records.
• Check how many cells contain entries.
• Track filled rows in a list.

5. IF() – Make Decisions Based on Conditions

The IF function returns one result when a condition is true and another when it is false.

Syntax:

=IF(logical_test, value_if_true, value_if_false)

Example:

=IF(B2>=40,"Pass","Fail")

If the value in B2 is 40 or higher, Excel displays “Pass.” Otherwise, it displays “Fail.”

When to use it:

• Assign pass or fail results.
• Identify profitable or unprofitable sales.
• Apply performance ratings.
• Create conditional status labels.

The IF function is especially useful for automating decisions in large spreadsheets.

6. SUMIF() – Add Values That Meet a Condition

SUMIF adds numbers only when the corresponding cells meet a specified condition.

Syntax:

=SUMIF(range, criteria, [sum_range])

Example:

=SUMIF(A2:A10,"East",B2:B10)

If column A contains regions and column B contains sales, this formula adds the sales associated with the East region.

When to use it:

• Calculate sales by region.
• Add expenses for a specific category.
• Calculate revenue for one product.
• Summarize transactions based on criteria.

7. COUNTIF() – Count Cells That Meet a Condition

COUNTIF counts the cells in a range that satisfy a particular condition.

Syntax:

=COUNTIF(range, criteria)

Example:

=COUNTIF(B2:B100,">=50")

This formula counts the cells containing numbers greater than or equal to 50.

You can also count text matches:

=COUNTIF(A2:A100,"Completed")

When to use it:

• Count completed tasks.
• Count employees in a department.
• Count orders above a certain value.
• Track records with a particular status.

8. SUMIFS() – Add Values Using Multiple Conditions

SUMIFS adds values that meet two or more criteria. It is useful when you need more control than SUMIF provides.

Syntax:

=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

Example:

=SUMIFS(C2:C100,A2:A100,"East",B2:B100,"Laptop")

This formula adds the values in column C where column A contains “East,” and column B contains “Laptop.”

When to use it:

• Calculate product sales by region.
• Analyze revenue by month and category.
• Total expenses for a specific department and period.
• Prepare detailed business reports.

9. COUNTIFS() – Count Using Multiple Conditions

COUNTIFS counts records that meet multiple criteria simultaneously.

Syntax:

=COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2], ...)

Example:

=COUNTIFS(A2:A100,"East",B2:B100,"Completed")

This formula counts records where the region is East and the status is Completed.

When to use it:

• Count orders by region and status.
• Count employees by department and location.
• Track tasks that meet multiple requirements.
• Analyze customer records.

10. VLOOKUP() – Find Data in a Table

VLOOKUP searches for a value in the first column of a table and returns a value from another column in the same row.

Syntax:

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

Example:

=VLOOKUP(E2,A2:C100,3,FALSE)

This formula searches for the value in E2 in the first column of the range A2:C100 and returns the corresponding value from the third column. FALSE requests an exact match.

When to use it:

• Find product prices using product IDs.
• Retrieve employee information.
• Match customer IDs with customer details.
• Look up values in structured tables.

Important: VLOOKUP searches from left to right, and the lookup value must be in the first column of the selected table range.

11. XLOOKUP() – A More Flexible Lookup Formula

XLOOKUP searches a range for a matching value and returns the corresponding value from another range. It supports exact matches by default and can look in either direction.

Syntax:

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found])

Example:

=XLOOKUP(E2,A2:A100,C2:C100,"Not Found")

This formula searches for E2 in column A and returns the corresponding value from column C. If there is no match, it displays “Not Found.”

When to use it:

• Find product prices.
• Retrieve employee names or IDs.
• Match records across columns.
• Handle missing lookup values with a custom message.

XLOOKUP is available in Microsoft 365 and supported newer Excel versions, but may not be available in older versions such as Excel 2016 or Excel 2019.

12. INDEX() – Return a Value From a Position

INDEX returns a value from a specified row and column within a range.

Syntax:

=INDEX(array, row_num, [column_num])

Example:

=INDEX(B2:B10,3)

This formula returns the third value in the range B2:B10.

When to use it:

• Retrieve values from large tables.
• Build flexible lookup formulas.
• Extract data by row or column position.
• Work with dynamic reports.

INDEX is particularly powerful when combined with MATCH.

13. MATCH() – Find the Position of a Value

MATCH returns the relative position of an item within a range.

Syntax:

=MATCH(lookup_value, lookup_array, [match_type])

Example:

=MATCH("Laptop",A2:A10,0)

This formula returns the position of the first exact match for “Laptop” in the range A2:A10.

When to use it:

• Find the position of a product.
• Locate a particular name in a list.
• Create dynamic lookup formulas.
• Identify the location of a value in a dataset.

The final argument 0 requests an exact match.

14. IFERROR() – Handle Formula Errors

IFERROR returns a specified alternative result when a formula produces an error. Otherwise, it returns the formula’s normal result.

Syntax:

=IFERROR(value, value_if_error)

Example:

=IFERROR(A2/B2,0)

If the division produces an error, Excel displays 0 instead. Otherwise, it displays the calculated result.

When to use it:

• Handle division errors.
• Display user-friendly lookup messages.
• Make reports easier to read.
• Prevent expected errors from cluttering a worksheet.

Use IFERROR carefully because it can also hide unexpected problems in your formulas.

15. CONCAT() – Combine Text From Multiple Cells

CONCAT combines text from multiple cells or text strings into one result.

Syntax:

=CONCAT(text1, [text2], ...)

Example:

=CONCAT(A2," ",B2)

If A2 contains “John” and B2 contains “Smith,” the result is “John Smith.”

When to use it:

• Combine first and last names.
• Create full names.
• Join text labels with cell values.
• Build product codes or descriptions.

For joining text with a separator across multiple cells, TEXTJOIN can also be useful.

16. LEFT() – Extract Characters From the Beginning

LEFT returns a specified number of characters from the beginning of a text string.

Syntax:

=LEFT(text, [num_chars])

Example:

=LEFT(A2,3)

If A2 contains “EXCEL2026,” the result is “EXC.”

When to use it:

• Extract product prefixes.
• Retrieve country or department codes.
• Separate the first few characters of an ID.
• Clean and organize text data.

17. RIGHT() – Extract Characters From the End

RIGHT returns a specified number of characters from the end of a text string.

Syntax:

=RIGHT(text, [num_chars])

Example:

=RIGHT(A2,4)

If A2 contains “INV2026,” the result is “2026.”

When to use it:

• Extract the final digits of an ID.
• Retrieve year codes.
• Separate suffixes from product codes.
• Process text-based identifiers.

18. LEN() – Count Characters in Text

LEN returns the number of characters in a text string, including spaces.

Syntax:

=LEN(text)

Example:

=LEN(A2)

If A2 contains “Excel Tips,” the result is 10 because the space is counted as a character.

When to use it:

• Check text length.
• Validate ID formats.
• Identify extra characters.
• Support data-cleaning tasks.

19. TODAY() – Display the Current Date

TODAY returns the current date according to the system date when Excel recalculates the worksheet.

Syntax:

=TODAY()

Example:

=TODAY()

Excel displays the current date in the cell, subject to its number format.

You can also calculate the number of days between a date and today:

=A2-TODAY()

If A2 contains a future date, the formula returns the number of days until that date.

When to use it:

• Track deadlines.
• Calculate days remaining.
• Monitor due dates.
• Create date-based reports.

Because TODAY is a volatile function, its result can update when the workbook recalculates.

20. TEXT() – Format Numbers and Dates as Text

TEXT converts a numeric value into text using a specified number format.

Syntax:

=TEXT(value, format_text)

Example:

=TEXT(A2,"dd-mmm-yyyy")

If A2 contains a valid Excel date, the formula returns text formatted like “15-Jan-2026.”

Another example:

=TEXT(B2,"$#,##0.00")

This formats the numeric value in B2 as text with a dollar sign and two decimal places.

When to use it:

• Display dates in a custom format.
• Combine formatted values with text.
• Create readable labels and summaries.
• Format numbers for reports.

Remember that TEXT returns text rather than a numeric value, so the result may not behave like a number in subsequent calculations.

Quick Reference: Top 20 Excel Formulas

Conclusion:

Friends, in this post, “Excel formulas,” we have fully understood the “Top 20 Excel Formulas“. Microsoft Excel is a fascinating tool or application that greatly helps users worldwide to accomplish tasks such as managing data, performing calculations, analyzing information, and creating reports. Using Excel, you can finish your work very easily in a short time. It gives you full accuracy in results and saves your important time to a great extent as it can manage your hours of work in a few minutes.

If you have any queries, you can write to us at support@a5theory.com; we will get back to you ASAP.

Hope! You will have enjoyed this post, “Top 20 Excel formulas“.

Please feel free to give your important feedback in the comment section below.

Have a great time!