Best Excel Formulas in an hour - Beginner to PRO Masterclass

Introduction

This tutorial is designed to help you master essential Excel formulas that can significantly enhance your data management and analysis skills. By the end of this guide, you'll learn when and how to effectively use various Excel functions, particularly in business scenarios. Whether you’re a beginner or looking to refine your skills, this tutorial will cover practical applications of formulas that are commonly used in the workplace.

Step 1: Understanding COUNTIFS, SUMIFS, and AVERAGEIFS

These functions are essential for analyzing data based on specific criteria.

• COUNTIFS: Counts the number of cells that meet multiple criteria.
• SUMIFS: Sums up values that meet multiple criteria.
• AVERAGEIFS: Averages values based on multiple criteria.

Practical Example

• To calculate total salaries and headcounts by department:
  • Use =COUNTIFS(range, criteria) for headcount.
  • Use =SUMIFS(sum_range, criteria_range1, criteria1, criteria_range2, criteria2) for total salaries.
  • Use =AVERAGEIFS(average_range, criteria_range1, criteria1) for average salaries.

Step 2: Filtering Data with FILTER Function

Use the FILTER function to extract specific data.

Example

• To find all employees with salaries over $100,000:
  • Formula: =FILTER(array, condition)
  • E.g., =FILTER(A2:C100, B2:B100>100000)

Step 3: Selecting Specific Columns with CHOOSECOLS

The CHOOSECOLS function allows you to display only the necessary columns from a dataset.

Example

• To show only selected columns from the filtered data:
  • Formula: =CHOOSECOLS(FILTER(array, condition), col_index1, col_index2)

Step 4: Using MIN, MAX, SMALL, and LARGE Functions

These functions help identify extreme values in your data.

• MIN: Returns the smallest value.
• MAX: Returns the largest value.
• SMALL: Returns the k-th smallest value.
• LARGE: Returns the k-th largest value.

Example

• To find the lowest and highest salaries:
  • =MIN(range) for the lowest salary.
  • =MAX(range) for the highest salary.
  • To get top 5 salaries, use: =SORT(range, 1, FALSE) and =LARGE(range, k) for k-th largest.

Step 5: Listing Unique Values with UNIQUE and # Operator

Use the UNIQUE function to extract distinct values from a dataset.

Example

• To list all departments:
  • Formula: =UNIQUE(range)

Step 6: Mastering Lookup Functions

Learn how to retrieve data from large datasets using lookup functions.

• VLOOKUP: Searches for a value in the first column and returns a value in the same row from a specified column.
• XLOOKUP: A more versatile lookup function that can search in any direction.
• INDEX and MATCH: A powerful combination for flexible lookups.

Example

• To find employee details:
  • VLOOKUP: =VLOOKUP(lookup_value, table_array, col_index_num, FALSE)
  • XLOOKUP: =XLOOKUP(lookup_value, lookup_array, return_array)

Step 7: Handling Errors with IFERROR

Use the IFERROR function to manage errors in your formulas.

Example

• To return a custom message if a formula results in an error:
  • Formula: =IFERROR(your_formula, "Error Message")

Step 8: Complex Filtering with Multiple Conditions

Combine functions to filter data based on multiple criteria.

Example

• To list all female employees with salaries over $100,000:
  • Formula: =FILTER(array, (condition1)*(condition2))

Conclusion

This tutorial has introduced you to essential Excel functions and their practical applications in business scenarios. By mastering these formulas, you can streamline your data analysis tasks and enhance your overall productivity in Excel. For further practice, explore the sample files and homework problems provided in the linked resources. Happy Excel learning!