25 Most Popular Excel Formulas & Functions

Excel formulas and functions are the building blocks of any spreadsheet work. Mastering them is crucial because it improves your efficiency and accuracy when working with data. It also helps streamline your workflow, automate repetitive tasks, and ensure consistency in data processing.

Whether you’re managing budgets, analysing data trends, or creating reports, understanding key Excel formulas and functions is essential for any business, big or small. This is why we have compiled an Excel Formulas & Functions list with some of the most popular formulas and functions for you to in your day-to-day operations!

25 Most Popular Excel Formulas & Functions

Below is a List of Excel Formulas & Functions, each with its syntax and a brief explanation of its purpose and common use cases. These formulas cover basic arithmetic, conditional logic, lookups, text manipulation, and more, reflecting widely used features in everyday Excel tasks.

1. SUM

• Syntax: =SUM(number1, [number2], …) – for example, =SUM(A1:A10).
• What it does: The SUM function adds up all the numbers in a given range of cells. It is one of the most basic and widely used functions, ideal for quickly totalling columns or rows of values (e.g. summing a list of expenses or sales).

2. AVERAGE

• Syntax: =AVERAGE(number1, [number2], …) – for example, =AVERAGE(A1:A10).
• What it does: AVERAGE calculates the arithmetic mean of a range of numbers. In practice, this function is used to find typical values, such as the average score of students or the average monthly sales in a year, by adding up the numbers and dividing by the count of values.

3. COUNT

• Syntax: =COUNT(value1, [value2], …) – for example, =COUNT(A1:A10).
• What it does: COUNT returns the number of cells in a range that contain numeric values. It ignores blanks or text. This is useful for counting entries like the number of numeric responses in a survey.

4. MAX

• Syntax: =MAX(number1, [number2], …) – for example, =MAX(A1:A10).
• What it does: MAX returns the largest numeric value from the specified range. It’s commonly used to find the maximum score, highest expense, or peak value in a dataset.

5. MIN

• Syntax: =MIN(number1, [number2], …) – for example, =MIN(A1:A10).
• What it does: The MIN function returns the smallest numeric value in a given range. It’s useful for identifying the minimum in a set of numbers – for instance, the lowest sales figure or smallest temperature in a dataset.

6. IF

• Syntax: =IF(logical_test, value_if_TRUE, value_if_FALSE).
• What it does: IF evaluates a condition and returns one value if the condition is TRUE and another if it is FALSE. This allows for conditional logic in spreadsheets. For example, you can use IF to label scores as “Pass” or “Fail” depending on a threshold, or to apply different calculations based on a criterion.

7. IFERROR

• Syntax: =IFERROR(value, value_if_error).
• What it does: IFERROR returns a specified result if an expression evaluates to an error, or the original value if no error occurs. It’s commonly used to clean up formulas that might produce errors (such as #DIV/0! or #N/A), by displaying a friendly message or alternative value.

8. VLOOKUP

• Syntax: =VLOOKUP(lookup_value, table_range, col_index, [range_lookup]).
• What it does: VLOOKUP searches for a value in the first column of a table and returns a value from another column in the same row. This is useful for looking up information in a structured list. For example, you can use VLOOKUP to find a product’s price by looking up the product name in a table where the first column is the product name and another column contains the price.

Using Excel Lookup with Multiple Criteria can sometimes be tricky, so we cover that in a separate article. Check it out!

9. XLOOKUP

• Syntax: =XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode]).
• What it does: XLOOKUP is a modern, more flexible replacement for VLOOKUP/HLOOKUP. It searches a range or array for a match and returns the corresponding item from a second range or array. XLOOKUP can search both vertically and horizontally, defaults to exact match, and has options for handling not-found results.

10. INDEX

• Syntax: =INDEX(array, row_num, [column_num])
• What it does: The INDEX function returns the value of an element in a table or array, given its row and column coordinates. Essentially, it retrieves a value by position. For instance, INDEX(A1:C10, 3, 2) will return the value at the 3rd row and 2nd column of the range A1:C10. INDEX is often used together with MATCH for more flexible lookups.

11. MATCH

• Syntax: =MATCH(lookup_value, lookup_array, [match_type]).
• What it does: MATCH searches for a value in a range and returns the position of that value within the range.For example, MATCH("Smith", A1:A100, 0) might return 57 if "Smith" is the 57th item in the list A1:A100 (match_type 0 finds an exact match). MATCH is frequently used with INDEX (as INDEX/MATCH) to perform lookups, where MATCH finds the row/position of a value and INDEX retrieves the value at that position.

12. SUMIF

• Syntax: =SUMIF(range, criteria, [sum_range]).
• What it does: SUMIF adds up the cells in a range that meet a single specified criterion. In other words, it computes a conditional sum. For example, you can sum sales only from the “West” region by using a SUMIF where the criteria is "Region = West". This function is very useful for aggregating data based on simple conditions.

For more ways to aggregate data, read our dedicated guide on Aggregating data in Excel.

13. SUMIFS

• Syntax: =SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …).
• What it does: SUMIFS adds all the cells that meet multiple criteria. It’s essentially an extension of SUMIF for more complex conditions. For example, you can sum orders that are from the “West” region and are above $500 at the same time. SUMIFS is useful for aggregating data based on several filters.

14. COUNTIF

• Syntax: =COUNTIF(range, criteria).
• What it does: COUNTIF counts the number of cells in a range that meet a given condition. It’s used for conditional counting. For instance, COUNTIF(B2:B100, ">50") would count how many numbers in the range B2:B100 are greater than 50. This is handy for tasks like counting how many sales were above a target, or how many entries match a specific category.

15. COUNTIFS

• Syntax: =COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2], …).
• What it does: COUNTIFS counts the number of cells that satisfy multiple criteria simultaneously. It works like an extension of COUNTIF for scenarios where you need to apply more than one condition. For example, you could count how many orders were made by “Alice” and were above $500 in value. This function is useful for multi-criteria counting, such as counting records that meet a combination of conditions in a dataset.

16. AVERAGEIF

• Syntax: =AVERAGEIF(range, criteria, [average_range]).
• What it does: AVERAGEIF calculates the average (mean) of cells that meet a single specified criterion. It’s useful for finding an average under a condition – for example, the average score of students who passed (scores above 60), or the average revenue from a specific product line. If the average_range is not provided, it averages the cells in the main range that meet the criteria.

17. AVERAGEIFS

• Syntax: =AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2], …).
• What it does: AVERAGEIFS returns the average of all cells that satisfy multiple criteria. In other words, it computes a conditional average based on more than one condition. For example, you could find the average salary of employees who are in the “Marketing” department and have more than 5 years of experience. This function helps in analysing data under several simultaneous filters.

18. CONCATENATE (or CONCAT)

• Syntax: =CONCATENATE(text1, [text2], …) – for example, =CONCATENATE(A1, " ", B1).
• What it does: CONCATENATE joins two or more text strings into one continuous string. It is commonly used to combine pieces of text or values from multiple cells. For instance, if first names are in column A and last names in column B, CONCATENATE(A1, " ", B1) will produce the full name by stitching them together with a space.

19. TRIM

• Syntax: =TRIM(text).
• What it does: TRIM removes extra spaces from text, leaving only single spaces between words. This is especially useful for cleaning up data that may have irregular spacing – for example, text copied from other sources or user inputs with accidental leading/trailing spaces. Using TRIM can prevent errors or mismatches caused by unseen spaces when comparing or processing text.

20. LEFT

• Syntax: =LEFT(text, [num_chars])
• What it does: LEFT returns the first character or characters of a text string, based on the number of characters you specify. For example, LEFT("Excel", 2) returns "Ex". It’s useful for extracting a prefix or code from the start of a text value (e.g. getting the area code from a phone number if the area code is the first N digits).

21. RIGHT

• Syntax: =RIGHT(text, [num_chars]).
• What it does: RIGHT returns the last character or characters in a text string, based on the number of characters specified. For instance, RIGHT("Financial", 4) returns "cial" (the last 4 letters). This function is helpful when you need to get a suffix of a text, such as the last 3 digits of an ID or the file extension from a filename.

22. MID

• Syntax: =MID(text, start_num, num_chars).
• What it does: MID extracts a substring from the middle of a text string; you specify the starting position and the length of the substring. For example, MID("ABCDEFG", 3, 2) returns "CD" (starting at the 3rd character and taking 2 characters). This is useful for pulling out codes or values embedded within a text string (like extracting the year from a code in the format "ID2023-XYZ").

23. AND

• Syntax: =AND(logical1, [logical2], …)
• What it does: AND checks multiple conditions and returns TRUE only if all the conditions are TRUE. If any condition is false, the result is FALSE. It’s often used in conjunction with IF or other formulas to require that several criteria are met. For example, AND(A1 > 0, B1 = "Yes") would yield TRUE only if both conditions (A1 positive, and B1 equals "Yes") are satisfied.

24. OR

• Syntax: =OR(logical1, [logical2], …).
• What it does: OR evaluates multiple conditions and returns TRUE if any one of the conditions is TRUE. It returns FALSE only if all conditions are false. This function is useful when you need to trigger an outcome if at least one of several criteria is met. For instance, OR(A1 > 100, B1 > 100) would return TRUE if either A1 or B1 is greater than 100. Like AND, it’s often used inside an IF formula to apply complex logic.

25. TODAY

• Syntax: =TODAY().
• What it does: TODAY returns the current date (according to your system’s date). It updates automatically every day when the spreadsheet recalculates. Common uses include tracking daily updates, calculating durations (e.g. =TODAY() - A1 to find the number of days since the date in A1), or stamping the current date in reports.

Want to master everything that Excel has to offer? Check out Future Savvy’s dedicated Excel courses that offer insights for both beginners and advanced users alike!

5 Essential Visualisation and Chart Tools

Excel isn’t just a number-cruncher—it’s packed with built-in visuals that turn raw rows into digestible stories. Below are some must-know Excel visualisation types and what they do.

Heat Map

Colour-coded data visualisation within cells. In Excel, a “heat map” usually refers to using cell background colours (often via conditional formatting) to represent values, where higher or lower values are indicated with more intense colours. This kind of visual immediately shows patterns, variances, and data densities by colour intensity.

Box and Whisker Plot

Statistical distribution chart. A Box and Whisker plot (or simply “Box plot”) depicts a dataset’s distribution – highlighting the median, quartiles, and any outliers. The “box” shows the interquartile range (middle 50% of data) and median, while “whiskers” extend to show the full range.

Pareto Chart

Sorted bar chart with cumulative line (80/20 rule analysis). A Pareto chart helps identify the most significant factors in a dataset by combining a sorted bar chart with a cumulative percentage line. The bars are ordered from largest to smallest, and the line shows the running total percentage.

Histogram

Frequency distribution chart. A Histogram shows how values in a dataset are distributed across defined “bins” or intervals. In simpler terms, it’s a column chart where each bar represents the count of values falling into a numeric range.

Scatter Plot

XY chart for relationships. A Scatter plot places data points on a two-dimensional graph with a horizontal X-axis and vertical Y-axis, effectively showing the relationship between two numeric variables. Each point’s position represents its values on the two axes.

You will find instructions on our blog about how to make some of these visuals. For instance, if you are interested in creating a histogram in Excel, make sure to see our short guide!

Excel vs. Power BI

Both Excel and Power BI are powerful tools in the data analysis arsenal, but they serve different purposes and play to different strengths.Excel is the go-to tool when you need tight, hands-on control over a reasonably sized dataset. For solo users or small teams juggling intricate formulas, financial models, and quick what-if tweaks, its cell-level editing and instant feedback make life a breeze.

As long as your data stays under roughly a million rows, Excel’s interface lets you tinker, pivot, and slice things exactly how you want. In short, grab Excel whenever the task demands detailed data manipulation, tailor-made calculations, and that familiar spreadsheet vibe.

Power BI steps in when you’re wrangling hefty, multi-source datasets and need polished, interactive dashboards the whole crew can explore. It ingests millions of rows, transforms them under the hood, and surfaces the story through dynamic visuals—maps, drill-downs, filters—refreshed automatically from live connections.

Because it’s cloud-hosted, you publish once and let teammates or execs slice and dice the numbers in real time without extra fuss. Bottom line: when scalable sharing, real-time updates, and eye-catching business insights are on the agenda, Power BI is your tool.

Excel’s rich set of formulas and functions – along with its newer visualisation capabilities – make it a powerhouse for anyone working with data. The Excel Formulas & Functions list above provides a practical guide that every professional for day-to-day productivity. From simple summations and averages to lookup formulas that pull out just the info you need, these functions turn Excel into a dynamic calculator and data analysis tool.

Future Savvy offers instructor-led courses, excellent for businesses looking to grow by improving their teams’ digital skills. Contact us today to learn more!