Top 10 Excel Functions Everybody Should Know
As a data analyst, Excel is one of the most powerful tools at your disposal. Whether you're analyzing large datasets or automating repetitive tasks, knowing the right functions can save you hours of manual work and help you make sense of your data efficiently.
Lookup Functions
Lookup functions are way more versatile than I have time to get into today, but they are one of my personal favorite tips. They can help you filter your data, bring in new elements, and most importantly help you join different data sets. Below we will just go over the basics.
1. VLOOKUP
VLOOKUP 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. This is especially useful when combining information from multiple sheets or tables.
=VLOOKUP(A2, Sheet2!A:B, 2, FALSE)2. INDEX MATCH
INDEX and MATCH together provide greater flexibility than VLOOKUP. INDEX returns the value of a cell at a specified row and column, and MATCH finds the position of a value in a column or row.
=INDEX(B:B, MATCH(A2, A:A, 0))3. XLOOKUP
XLOOKUP is an upgrade to both VLOOKUP and INDEX MATCH. It searches a value in one column and returns a corresponding value from another, regardless of whether the search column is to the left or right.
=XLOOKUP(A2, B:B, C:C)Text Functions
4. CONCATENATE / TEXTJOIN
CONCATENATE joins multiple cells into one. TEXTJOIN is its successor, and it's useful for combining first and last names or creating custom text strings.
=TEXTJOIN(" ", TRUE, A2, B2)Conditional Functions
Below are just some of the basics of conditional functions, my favorite thing to do is make a massive nested if function so that I can create a new feature in my data set based on data in it. They get complicated but once you understand how they work they are breeze!
5. IF / Nested IFs
IF performs logical tests and returns different values based on whether the test evaluates TRUE or FALSE. Nested IF statements allow stacking multiple conditions.
=IF(A2>100, "High", "Low")6. SUMIF / SUMIFS
SUMIF adds up values that meet a condition, while SUMIFS allows for multiple conditions. It's useful when aggregating data based on specific criteria.
=SUMIF(A:A, ">100", B:B)Error Handling Functions
7. IFERROR
IFERROR returns a custom value when a formula results in an error, making error handling clean and readable.
=IFERROR(VLOOKUP(A2, B:C, 2, FALSE), "Not Found")Math Functions
8. SUMPRODUCT
SUMPRODUCT multiplies ranges or arrays together and sums the products. It's great for weighted calculations.
=SUMPRODUCT(A2:A10, B2:B10)9. RANK
RANK gives the rank of a number within a dataset, either in ascending or descending order.
=RANK(A2, A:A)Bonus: Date Functions
10. TODAY / NOW
TODAY returns the current date, while NOW returns the current date and time, helping with date-related tasks.
=TODAY()