These are the ONLY 15 functions you need to know in Excel (to get most things done)

Summary

This video tutorial guides viewers through using Excel to answer 15 real-world business questions with practical data analysis. Using a dataset of app downloads, uninstalls, and ratings from June to December 2021, the instructor demonstrates how to apply essential Excel functions, including SUMIFS, COUNTIFS, AVERAGEIFS, MAX, MIN, LARGE, XLOOKUP, INDEX-MATCH, and FILTER. Key techniques include calculating totals, averages, percentages, ranking values, and extracting specific data based on multiple criteria. The tutorial emphasizes dynamic formulas, handling dates, and solving complex queries efficiently, equipping users with versatile skills to analyze app performance and make data-driven business decisions.

Takeaways

• 😀 The video teaches how to use Excel to answer 15 business data analysis questions using real app download and rating data.
• 😀 SUMIFS, COUNTIFS, and AVERAGEIFS are the most powerful formulas highlighted for summing, counting, and averaging data based on multiple criteria.
• 😀 Using dynamic input cells for app names, letters, or months allows formulas to be reusable and adaptable without hardcoding values.
• 😀 Date-related calculations, including ranges, require careful use of comparison operators (>=, <=) and concatenation with cell references.
• 😀 MAX, LARGE, and MIN functions help identify the highest, second/third highest, and lowest values in a dataset, such as downloads per app or month.
• 😀 XLOOKUP and INDEX-MATCH are essential for retrieving corresponding values like app names or months associated with specific numerical metrics.
• 😀 FILTER is crucial when multiple results meet criteria, ensuring accurate retrieval instead of only the first match.
• 😀 Calculating percentages, such as 5-star rating percentage, involves dividing filtered sums of ratings by total filtered ratings.
• 😀 Structured references (e.g., Data[Column]) and Excel tables enhance clarity and automatically adjust ranges when new data is added.
• 😀 Special attention is needed for formula accuracy in scenarios where multiple rows meet the same criteria, especially when determining max/min values per month or app.
• 😀 Using Excel tables, combined formulas, and proper formatting allows business analysts to efficiently extract insights like top apps, monthly trends, and uninstall ratios.

Q & A

What is the purpose of using the SUMIFS function in Excel as described in the video?

The SUMIFS function is used to sum values in a column based on one or more specified conditions, such as summing downloads for a specific app or within a specific month range.

How can you calculate total downloads for apps whose names start with a specific letter?

You can use the SUMIFS function with a wildcard character. For example, to sum downloads for apps starting with 'C', use the formula `SUMIFS(downloads_column, app_column, "C*")` or link it to an input cell with `C9 & "*"`.

What is the difference between SUMIF and SUMIFS, and when should you use SUMIFS?

SUMIF handles only one condition at a time, while SUMIFS can handle multiple conditions simultaneously. Using SUMIFS is recommended for flexibility and efficiency, especially when analyzing data with multiple criteria.

How do you calculate the number of uninstalls in a specific month, like December 2021?

Use the SUMIFS function with the uninstall column as the sum range and the month column as the criteria range, linking the criteria to a cell with 'December 2021' or using the DATE function for precise date handling.

How can you count the number of months when downloads exceeded a certain threshold, like 8,000?

Use the COUNTIFS function with the downloads column as the criteria range and the condition ">8000". This counts the number of occurrences where monthly downloads exceed 8,000.

How is the 5-star rating percentage for an app calculated?

First, use SUMIFS to sum the 5-star ratings for the app, then SUMIFS to sum the total ratings. Divide the 5-star sum by the total ratings sum and format as a percentage to get the 5-star rating percentage.

How do you determine which month had the highest uninstall ratio?

Calculate the uninstall ratio for each month by dividing total uninstalls by total downloads using SUMIFS for each month. Then use the MAX function to find the highest ratio and XLOOKUP or INDEX-MATCH to identify the corresponding month.

What Excel functions are used to find the app with the most downloads in any month?

Use the MAX function to find the highest downloads and the LARGE function to find the second and third highest values. Then use XLOOKUP or INDEX-MATCH to identify the corresponding app and month.

How can you find the app with the most one-star ratings in a specific month like October 2021?

Use the MAXIFS function to get the highest one-star rating for October, then use the FILTER function to retrieve the app(s) that match both the month and rating criteria, ensuring accurate results even if multiple apps have the same rating.

What method is suggested to identify the least downloaded app for each month?

Use the MINIFS function to find the smallest number of downloads for each month. Then apply the FILTER function with two criteria: matching the month and matching the download number to retrieve the corresponding app name.

How can input cells be used to make formulas more dynamic in Excel?

Instead of hardcoding values, link formulas to input cells. This allows you to change criteria like app name or month in one place, and all linked formulas will automatically update their results.

Why is the FILTER function preferred over XLOOKUP or INDEX-MATCH in some scenarios?

FILTER is preferred when multiple matches may exist, as it can return all results meeting specified criteria. XLOOKUP and INDEX-MATCH return only the first match, which can lead to incorrect answers when multiple data points satisfy the condition.