Learn advanced formulas in Excel and learn how to use tricks and practical applications to improve your data analysis.
Excel is a tool I use daily, both at work and in my personal life. It is an indispensable program that enables data management, report creation, and results analysis. While basic Excel functions are very useful, the real power lies in advanced formulas. In this article, I’ll present some of my favorite tricks and practical applications of advanced Excel formulas.
Introduction
Excel is a versatile tool that allows for advanced data analysis using various formulas. In this article, I’ll focus on a few key functions that are worth knowing to fully harness the potential of this program. I will discuss functions such as VLOOKUP, INDEX and MATCH, SUMPRODUCT, ARRAYFORMULA, and text functions. I will also provide practical examples of their use that can make working with data easier.
VLOOKUP
Description
The VLOOKUP function is one of the most commonly used functions in Excel. It allows you to search for a value in the first column of a table and return a value in the same row from another column.
Syntax
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])Example
Let’s say we have a table with customer data, where column A contains the customer ID and column B contains the customer’s last name. We want to find the last name of the customer with ID 123.
=VLOOKUP(123, A:B, 2, FALSE)INDEX and MATCH
Description
The INDEX and MATCH functions are more flexible than VLOOKUP and are often used together. INDEX returns a value from a specified row and column of a table, while MATCH returns the relative position of a value in a specified range.
Syntax
=INDEX(array, row_num, [column_num])=MATCH(lookup_value, lookup_array, [match_type])Example
We have the same table with customer data and want to find the last name of the customer with ID 123.
=INDEX(B:B, MATCH(123, A:A, 0))SUMPRODUCT
Description
SUMPRODUCT is an advanced function that can be used to perform various mathematical operations on a range of cells. It is most commonly used to multiply corresponding elements in two or more ranges and sum the results.
Syntax
=SUMPRODUCT(array1, [array2], ...)Example
We want to calculate the total cost of purchasing products. We have the quantity of products in column A and the unit price in column B.
=SUMPRODUCT(A:A, B:B)ARRAYFORMULA
Description
Array formulas allow you to perform operations on multiple values simultaneously. They are useful when you want to apply the same formula to an entire range of cells.
Syntax
{=formula}Example
We want to multiply values in two columns (A and B) and return the results in column C. Instead of entering the formula in each cell, we can use an array formula.
{=A1:A10 * B1:B10}Text Functions
Description
Text functions in Excel allow for manipulating text, which can be useful when working with data that contains strings of characters.
Example
Let’s say we have a column with full names (e.g., “John Doe”) and we want to extract just the first name.
LEFT
=LEFT(A1, FIND(" ", A1)-1)RIGHT
If we want to extract the last name, we can use a combination of RIGHT and LEN functions.
=RIGHT(A1, LEN(A1) - FIND(" ", A1))CONCATENATE and TEXTJOIN
If we want to combine multiple texts into one string, we can use the CONCATENATE or TEXTJOIN function.
=CONCATENATE(A1, " ", B1)=TEXTJOIN(" ", TRUE, A1, B1, C1)Useful Formulas List
IFERROR: =IFERROR(value, value_if_error)
COUNTIF: =COUNTIF(range, criteria)
SUMIF: =SUMIF(range, criteria, [sum_range])
AVERAGEIF: =AVERAGEIF(range, criteria, [average_range])
MINIFS: =MINIFS(min_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)
MAXIFS: =MAXIFS(max_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)
Conclusion
Advanced formulas in Excel are invaluable tools for daily data work. Functions such as VLOOKUP, INDEX and MATCH, SUMPRODUCT, ARRAYFORMULA, and various text functions allow for advanced data analysis and efficient work. Thanks to them, I can quickly and efficiently perform complex calculations and process large amounts of information. I encourage you to experiment with these formulas and discover their full potential.
Other interesting articles:
- Difficult interview questions or how to talk with the recruiter
- SQL for data analyst or how to get the insights from the data base
- GPT 4o and Gemini Advanced or how new AI models will change the world
Prefer to read in Polish? No problem!
That’s all on this topic. Analyze in peace!
Did you like this article 🙂?
Share it on Social Media 📱
>>> You can share it on LinkedIn and show that you learn something new every day.
>>> You can throw it on Facebook – and perhaps help a friend of yours who is looking for this.
>>> And remember to bookmark this page, you never know if it won’t come handy in in the future.
You prefer to watch 📺 – no problem
>>> Subscribe and watch my English channel on YouTube.
