20 Powerful Excel Formulas That Everyone Should Know In 2024

Excel isn’t just about rows and columns; it’s a powerhouse of data manipulation and analysis. But are you harnessing its full potential?

In 2024, elevate your Excel game with these essential formulas. From simple lookups to complex calculations, these formulas will streamline your workflows, uncover insights, and transform you into an Excel ninja. Whether you’re a beginner or a seasoned pro, there’s always something new to learn.

Let’s unlock the magic of Excel together!

1. XLOOKUP (The Versatile Lookup Function)

What it does: XLOOKUP is a powerful replacement for VLOOKUP and HLOOKUP, offering more flexibility and ease of use. It searches for a specific value in a range or array and returns a corresponding value from another range or array.

Syntax:

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

• lookup_value: The value you’re searching for.
• lookup_array: The range or array where you’re searching.
• return_array: The range or array from which to return a value.
• if_not_found (optional): What to return if the lookup value isn’t found (default is #N/A).
• match_mode (optional): Specifies the type of match (0 for exact, 1 for exact or next smaller, -1 for exact or next larger).
• search_mode (optional): Specifies the search direction (1 for top-down, -1 for bottom-up, 2 for binary search in ascending order).

Real-life Example:

Imagine you have a product list with IDs and prices. You want to find the price of a product given its ID.

=XLOOKUP(B2, A2:A10, C2:C10, "Not Found")

This formula searches for the ID in B2 within the range A2:A10 and returns the corresponding price from C2:C10. If the ID isn’t found, it returns “Not Found.”

2. FILTER (Dynamic Data Filtering)

What it does: FILTER allows you to create dynamic arrays of data based on specific criteria. This is particularly useful for quickly extracting relevant information from larger datasets.

Syntax:

=FILTER(array, include, [if_empty])

• array: The range or array you want to filter.
• include: A logical expression that determines which rows to include (e.g., B2:B10=”East”).
• if_empty (optional): What to return if no rows match the criteria (default is #CALC!).

Real-life Example:

Let’s say you have a list of sales data with regions and amounts. You want to filter for sales only in the “East” region.

=FILTER(A2:C10, B2:B10="East", "No East Sales")

This formula creates a new array containing only the rows where the region is “East.” If there are no “East” sales, it returns “No East Sales.”

3. SUMIFS (Conditional Summing with Multiple Criteria)

What it does: SUMIFS allows you to sum values in a range based on multiple criteria. It’s a powerful tool for analyzing data where you need to consider several conditions simultaneously.

Syntax:

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

• sum_range: The range of cells you want to sum.
• criteria_range1: The range to evaluate against criteria1.
• criteria1: The criterion for criteria_range1.
• criteria_range2, criteria2,… (optional): Additional ranges and criteria to apply.

Real-life Example:

Imagine you have a sales dataset with regions, products, and sales amounts. You want to sum the sales of “Product A” in the “North” region.

=SUMIFS(C2:C10, A2:A10, "North", B2:B10, "Product A")

This formula sums the values in C2:C10 where the corresponding cells in A2:A10 equal “North” and the corresponding cells in B2:B10 equal “Product A.”

4. INDEX & MATCH (Flexible Data Retrieval)

What it does: The combination of INDEX and MATCH provides a flexible alternative to VLOOKUP and HLOOKUP. It allows you to look up values in any direction (horizontally or vertically) and doesn’t require the lookup column to be the leftmost column.

Syntax:

=INDEX(return_array, MATCH(lookup_value, lookup_array, [match_type]))

• return_array: The array or range from which to return a value.
• lookup_value: The value you’re searching for.
• lookup_array: The array or range in which to search for the lookup value.
• match_type (optional): Specifies the type of match (0 for exact, 1 for less than, -1 for greater than).

Real-life Example:

Using the same sales data as before, let’s say you want to find the product name corresponding to a specific sales amount.

=INDEX(B2:B10, MATCH(150, C2:C10, 0)) 

This formula first uses MATCH to find the position of the sales amount 150 in C2:C10. Then, INDEX retrieves the corresponding product name from B2:B10.

5. COUNTIFS (Conditional Counting with Multiple Criteria)

What it does: COUNTIFS is similar to SUMIFS, but instead of summing values, it counts the number of cells that meet multiple criteria. This is great for quickly assessing how many items in your data satisfy specific conditions.

Syntax:

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

• criteria_range1: The range to evaluate against criteria1.
• criteria1: The criterion for criteria_range1.
• criteria_range2, criteria2,… (optional): Additional ranges and criteria to apply.

Real-life Example:

Let’s use our sales data again. You want to know how many sales of “Product B” were made in the “West” region.

=COUNTIFS(A2:A10, "West", B2:B10, "Product B")

This formula counts the number of cells in A2:A10 that equal “West” and the corresponding cells in B2:B10 that equal “Product B.”

6. AVERAGEIFS (Conditional Averaging with Multiple Criteria)

What it does: AVERAGEIFS calculates the average of values in a range based on multiple criteria. It helps find average performance or trends within specific segments of your data.

Syntax:

=AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

• average_range: The range of cells to average.
• criteria_range1: The range to evaluate against criteria1.
• criteria1: The criterion for criteria_range1.
• criteria_range2, criteria2,… (optional): Additional ranges and criteria to apply.

Real-life Example:

Let’s find the average sales amount for “Product C” across all regions.

=AVERAGEIFS(C2:C10, B2:B10, "Product C")

This formula averages the values in C2:C10 where the corresponding cells in B2:B10 equal “Product C.”

7. VLOOKUP (The Classic Lookup Function)

What it does: VLOOKUP is a well-known function for looking up values in a table. It’s helpful for retrieving information from a database or a list organized in columns.

Syntax:

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

• lookup_value: The value you’re searching for.
• table_array: The range or table containing the data.
• col_index_num: The column number in table_array from which to return a value.
• range_lookup (optional): TRUE for approximate match, FALSE for exact match (default is TRUE).

Real-life Example:

Imagine you have an employee database with names and salaries. You want to find the salary of a specific employee.

=VLOOKUP("John Doe", A2:B10, 2, FALSE)

This formula searches for “John Doe” in the first column (A2:A10) and returns the corresponding salary from the second column (B2:B10).

8. HLOOKUP (Horizontal Lookup)

What it does: HLOOKUP is similar to VLOOKUP, but it searches for a value in the top row of a table and returns a value from a specified row below.

Syntax:

=HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])

• lookup_value: The value you’re searching for.
• table_array: The range or table containing the data.
• row_index_num: The row number in table_array from which to return a value.
• range_lookup (optional): TRUE for approximate match, FALSE for exact match (default is TRUE).

Real-life Example:

Consider a table with months in the top row and sales figures below. You want to find the sales for a particular month.

=HLOOKUP("March", A1:E5, 3, FALSE)

This formula searches for “March” in the first row (A1:E1) and returns the sales figure from the third row (A3:E3).

9. IF (Conditional Logic)

What it does: The IF function allows you to perform different calculations or display different results based on whether a condition is true or false.

Syntax:

=IF(logical_test, value_if_true, value_if_false)

• logical_test: The condition you want to test.
• value_if_true: The value to return if the condition is true.
• value_if_false: The value to return if the condition is false.

Real-life Example:

Let’s say you want to categorize sales as “High” if the amount is above $1000, “Medium” if between $500 and $1000, and “Low” if below $500.

=IF(C2>1000, "High", IF(C2>=500, "Medium", "Low"))

This formula checks if the sales amount in C2 is greater than 1000. If so, it returns “High.” If not, it checks if it’s greater than or equal to 500 and returns “Medium” if true, otherwise “Low.”

10. LEFT, RIGHT, MID (Text Manipulation)

What they do: These functions allow you to extract specific parts of text strings.

• LEFT: Returns the specified number of characters from the beginning of a text string.
• RIGHT: Returns the specified number of characters from the end of a text string.
• MID: Returns a specific number of characters from a text string, starting at a specified position.

Syntax:

=LEFT(text, num_chars)
=RIGHT(text, num_chars)
=MID(text, start_num, num_chars)

Real-life Example:

Suppose you have a list of product codes like “ABC-1234.” You want to extract the first three letters (the product category).

=LEFT(A2, 3)

This formula returns “ABC” from the cell A2.

11. TEXTJOIN (Combining Text with Delimiter)

What it does: TEXTJOIN combines text from multiple ranges or cells into one cell, using a specified delimiter between each text item.

Syntax:

=TEXTJOIN(delimiter, ignore_empty, text1, [text2], ...)

• delimiter: The character or text string to insert between text items.
• ignore_empty: TRUE to skip empty cells, FALSE to include them.
• text1, text2, …: The text items to join.

Real-life Example:

Suppose you have a first name and last name in separate columns and want to combine them into a full name.

=TEXTJOIN(" ", TRUE, A2, B2)

This formula joins the text in cells A2 and B2, separated by a space.

12. CONCATENATE (Joining Text)

What it does: CONCATENATE combines multiple text strings into one. It’s simpler than TEXTJOIN but doesn’t offer options for delimiters or ignoring empty cells.

Syntax:

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

• text1, text2, …: The text items to join.

Real-life Example:

You can use CONCATENATE to create a custom message.

=CONCATENATE("Hello ", A2, "! Welcome to our store.")

This formula combines “Hello,” the name in cell A2, and the rest of the message.

13. TRIM (Removing Extra Spaces)

What it does: TRIM removes leading, trailing, and extra spaces between words in a text string.

Syntax:

=TRIM(text)

• text: The text string to clean up.

Real-life Example:

Imagine you have messy data with extra spaces in some cells.

=TRIM(A2)

This formula removes any extra spaces in cell A2.

14. DATE (Creating Dates)

What it does: The DATE function allows you to create a date from separate year, month, and day values.

Syntax:

=DATE(year, month, day)

• year: The year (e.g., 2024).
• month: The month (1 for January, 2 for February, etc.).
• day: The day of the month (1 to 31).

Real-life Example:

Let’s say you have separate columns for year, month, and day, and you want to create a single date column.

=DATE(A2, B2, C2)

This formula combines the values in cells A2 (year), B2 (month), and C2 (day) to create a date.

15. NETWORKDAYS (Calculating Workdays)

What it does: NETWORKDAYS calculates the number of working days between two dates, excluding weekends and optionally specified holidays.

Syntax:

=NETWORKDAYS(start_date, end_date, [holidays])

• start_date: The start date.
• end_date: The end date.
• holidays (optional): A range or array of dates representing holidays to exclude.

Real-life Example:

You want to calculate the number of working days between January 1, 2024, and March 31, 2024, excluding public holidays listed in the range E2:E5.

=NETWORKDAYS("1/1/2024", "3/31/2024", E2:E5)

This formula returns the number of working days in that period, excluding weekends and any holidays listed in E2:E5.

16. MAX & MIN (Finding Highest and Lowest Values)

What they do: The MAX and MIN functions return the largest and smallest values in a range, respectively.

Syntax:

=MAX(number1, [number2], ...)
=MIN(number1, [number2], ...)

• number1, number2, …: The numbers or ranges from which to find the maximum or minimum.

Real-life Example:

Let’s say you have a list of sales figures. You want to find the highest and lowest sale amounts.

=MAX(C2:C10)  // Finds the highest sale amount
=MIN(C2:C10)  // Finds the lowest sale amount

These formulas will return the maximum and minimum values within the range C2:C10.

17. LARGE & SMALL (Finding Nth Largest/Smallest Value)

What they do: The LARGE and SMALL functions return the nth largest and nth smallest values in a range, respectively.

Syntax:

=LARGE(array, k)
=SMALL(array, k)

• array: The range of cells to consider.
• k: The position of the value to return (e.g., 1 for the largest, 2 for the second largest).

Real-life Example:

Suppose you want to find the second-highest and third-lowest sales amounts.

=LARGE(C2:C10, 2) // Returns the second-highest sale amount
=SMALL(C2:C10, 3) // Returns the third-lowest sale amount

18. SUMPRODUCT (Multiplying and Summing Arrays)

What it does: SUMPRODUCT multiplies corresponding components in the given arrays and returns the sum of those products.

Syntax:

=SUMPRODUCT(array1, [array2], [array3], ...)

• array1, array2, array3, …: The arrays whose components you want to multiply and then sum.

Real-life Example:

Imagine you have a list of products, quantities sold, and prices per unit. You want to calculate the total revenue.

=SUMPRODUCT(B2:B10, C2:C10) 

This formula multiplies the quantities in B2:B10 by the prices in C2:C10 for each product and sums those products to give the total revenue.

19. TRANSPOSE (Flipping Rows and Columns)

What it does: The TRANSPOSE function converts rows into columns and columns into rows. This is incredibly useful when you need to change the orientation of your data for better analysis or presentation.

Syntax (Array Formula):

{=TRANSPOSE(array)}

• array: The range of cells you want to transpose.

How to use:

1. Select the range where you want the transposed data to appear.
2. Type =TRANSPOSE( and select the range you want to transpose.
3. Press Ctrl+Shift+Enter to enter the formula as an array formula (the curly braces will appear automatically).

Real-life Example:

Imagine you have a list of sales data by month in a row (e.g., Jan, Feb, Mar). You want to turn this into a column.

1. Select a vertical range where the transposed data will fit.
2. Enter {=TRANSPOSE(A1:C1)} (assuming your data is in A1:C1).
3. Press Ctrl+Shift+Enter.

Now your data will appear in a column instead of a row.

20. IFERROR (Handling Errors Gracefully)

What it does: The IFERROR function allows you to display a custom message or perform an alternative calculation if a formula results in an error (e.g., #N/A, #VALUE!, #DIV/0!).

Syntax:

=IFERROR(value, value_if_error)

• value: The formula or calculation to check for errors.
• value_if_error: The value to return if an error is found.

Real-life Example:

Suppose you’re dividing sales figures by the number of customers, but some cells might contain zero customers (resulting in a #DIV/0! error).

=IFERROR(C2/D2, 0)

This formula divides the sales in C2 by the number of customers in D2. If D2 is zero, the formula will return 0 instead of an error.

A few certification courses That You Might Consider to take for mastering Excel

1. Excel Skills for Business: 

Whether you’re just starting or looking to level up, these courses have got you covered. Master the fundamentals, dive into automation, and even conquer advanced forecasting techniques. Imagine confidently crunching numbers, creating stunning visualizations, and making data-driven decisions that impress your colleagues!

2. Business Problem Solving: 

Want to take your problem-solving skills to the next level? These courses teach you to model complex business scenarios, analyze data like a detective, and use Excel’s powerful Solver tool to optimize your strategies. Become the go-to person for tackling those tricky business challenges!

3. Excel Skills for Data Analytics and Visualization:

 If you’re ready to dive into the world of data, this specialization is your ticket. Learn to clean and prep data, create eye-catching visuals, and even harness the power of Power Query, Power Pivot, and Power BI. Uncover hidden insights and communicate your findings like a data storyteller!

4. Excel/VBA for Creative Problem Solving: 

Ready to unleash the full potential of Excel? VBA (Visual Basic for Applications) is your secret weapon. Automate tedious tasks, build custom functions, and create interactive user forms. Turn Excel into your personal productivity powerhouse!

5. Everyday Excel: 

Looking for a comprehensive Excel education? This specialization covers everything from the basics to advanced techniques, all with a focus on real-world application. Tackle everyday challenges, master financial calculations, and even conquer complex projects like a pro.

6. Excel Skills for Business Forecasting: 

Want to predict the future of your business? These courses teach you to analyze time series data, build regression models, and even incorporate judgmental forecasting techniques. Make informed decisions and steer your business towards success!

7. A Comprehensive Excel Masterclass: 

This masterclass is your one-stop shop for Excel mastery. Deep dive into financial concepts, create stunning dashboards and even learn the game-changing XLOOKUP function. Elevate your Excel skills to a whole new level!

8. Data Analysis and Visualization Foundations: 

New to data analysis? This specialization guides you through the basics, from gathering data to creating impactful visualizations using Excel and IBM Cognos Analytics. Build a solid foundation and launch your data career!

Conclusion

Congratulations! You’ve just unlocked a treasure trove of Excel formulas. By mastering these functions, you’re not just manipulating data—you’re making informed decisions, automating tasks, and gaining a deeper understanding of your information.

Remember, practice makes perfect. So, open up a spreadsheet, experiment with these formulas, and watch your Excel skills soar. The world of data is yours to explore.