## Which Excel Formulas should you learn first?

Let’s face it. You already know the SUMs & COUNTs of Excel. But what should you learn next? There are more than 400 functions in Excel and most of them are useless for day-to-day situations. So, in this page, let me highlight the TOP 10 EXCEL FUNCTIONS for data analysis work.

## The TOP 10 Excel Functions

Here are the top 10 functions you should learn and master first.

## TOP 10 Excel functions – Video

If you want to understand what these functions are how to use them either read on or watch the below video.

Play Video

## 1. SUMIFS

Use SUMIFS to add up values that meet one or more conditions. Example uses:

• Total sales to England
• Total donations made to charity cause A in the last 7 days

SUMIFS Syntax

condition 1 range, condition 1,
condition 2 range, condition 2…)

SUMIFS example

=SUMIFS(A1:A10, B1:B10, “London”)
Adds up all values in A1:A10 where B1:B10 is London

• SUMIFS examples & explanation
• 15 SUMIFS examples – video

## 2. XLOOKUP

Use XLOOKUP to search for a value in a list and return corresponding value from another list. For example:

• Get due date for invoice number 934
• Find price for the product code PR023

XLOOKUP Syntax

XLOOKUP(value to find,
list to look in,
what do you want to get,

XLOOKUP example

=XLOOKUP(“Almond Choco”, Product[name], Product[price])
Finds the price of Almond Choco in the Product table.

• XLOOKUP examples & explanation
• XLOOKUP: GREAT Explanation – video

## 3. FILTER

Use FILTER to filter a list or table and see matching results for your criteria. This is a dynamic array function. That means, if there are more than one values, Excel automatically spills the values and shows them on the sheet. For example:

• List all sales records for “John”
• Find out which students have attended all sessions of Physics-301 class.

FILTER Syntax

what to show if no values are filtered)

FILTER example

=FILTER(Product, Product[price]>20)
Lists all the Product table rows where the price is more than 20.

• FILTER & Other new Dynamic Array functions
• FILTER – AWESOME explanation with practical examples – Video

## 4. COUNTIFS

Use COUNTIFS to count how many values meet one or more conditions in your data. For example:

• Number of times “Adam” exceeded \$1,000 order value
• How many recipes use Paprika?

COUNTIFS Syntax

COUNTIFS(condition 1 list, condition 1,
condition 2 list, condition 2…)

COUNTIFS example

Counts how many times Adam’s order value is more than 1000.

• Pattern Matching with COUNTIFS
• 15 SUMIFS & COUNTIFS Examples – Video

## 5. INDEX

Use INDEX to get a value in a list by specifying the position. INDEX formula returns the reference instead of a value. This makes it quite versatile. It is my favorite Excel function.

For example:

• What is the 12th item in the invoice list?
• Get all the values in 3rd column of the order data.

INDEX Syntax

INDEX(your data, row number, column number)

INDEX example

=INDEX(FILTER(Product[name], Product[price]>20), 3)
Returns the 3rd product name which has price more than 20. Here, INDEX is reading the output of FILTER.

• 7 reasons why you should learn INDEX
• INDEX formula – what is it and how to use it? – VIDEO

## 6. EDATE

Use EDATE to calculate a future or past date after a specified number of months.

For example:

• What is the date 7 months after project start date?
• What is the date exactly two years ago from today?

EDATE Syntax

EDATE(date, months)

EDATE example

=EDATE(TODAY(), -24)

Returns the date exactly two years ago from today.

• 10 tips & examples of Excel Date functions
• 3 Essential Date formulas for you – Video

## 7. UNIQUE

Use UNIQUE to remove any duplicates in your list. This is a Dynamic Array Function, so Excel will return and spill multiple values if needed.

For example:

• What products are sold?
• Which students have joined only one sports club?

UNIQUE Syntax

UNIQUE example

=UNIQUE(Order[Product])

Returns all the products from Order table. If a product appears multiple times, it will show up only once.

• UNIQUE & Other new Dynamic Array functions
• Dynamic Array functions explained – Video

## 8. TEXTJOIN

Use TEXTJOIN to combine a bunch of values with a specified delimiter.

For example:

• Combine all product names to one text value with comma delimited.
• All names of students who joined Maths 203 course in March 2022.

TEXTJOIN Syntax

TEXTJOIN(delimiter, ignore empty values?, your data)

TEXTJOIN example

=TEXTJOIN(“, “,TRUE,
FILTER(Enrollments[name], Enrollments[course]=”Maths-203″))

Combines all student names in the Maths-203 program in comma separated format.

• Excel’s TEXTJOIN function with 3 examples
• TEXTJOIN explanation – Video

## 9. SORT

Use SORT to sort a list or table in ascending or descending order. This is a Dynamic Array Function, so Excel will spill your outputs as needed.

For example:

• Sort the Orders table to show highest values on top.
• List students by department and name in alphabetical order.

SORT Syntax

SORT(your data, sort column, sort order)

SORT example

=SORT(Enrollments, {1,2}, {1,1})

Sorts the Enrollments table in the ascending (alphabetical) order by department and student name (columns 1 & 2 of the table).

• SORT & Other new Dynamic Array functions
• Dynamic Array functions explained – Video

## 10. IFERROR

Use IFERROR to stop error messages from showing up on the screen.

For example:

• Calculate sales commission but print 0 if there is an error.
• Get the 10th item of a filtered list, but show “not enough items” if there is an error.

IFERROR Syntax

IFERROR(your formula, error message to show)

IFERROR example

=IFERROR(INDEX(FILTER(Product[name], Product[price]>20), 10), “Not enough items”)

Tries to get the 10th product with price >20, but if there is an error, prints “Not enough items”.

• IFERROR formula – syntax, examples and ideas
• What to do when you have formula errors?

Change the values / formulas or use the data set to develop your own formulas.

## More on Formulas & Functions

If you are just starting out, I suggest focusing and mastering the above 10 functions first. But if you are ready to next level, then go thru the below articles & resources for more.

• 35 shortcuts & tricks to make you an #AWESOME Data Analyst
• 100+ Excel formula examples
• 15+ Examples of SUMIFS & COUNTIFS (video)
• COURSE: Excel formulas & other important skills for Data Analysis – Excel School

Happy learning.

The post Which Excel Formulas should you learn first? appeared first on Chandoo.org – Learn Excel, Power BI & Charting Online.

Original source: https://chandoo.org/wp/top-10-excel-functions/