Excel Formulas and Functions You NEED to KNOW!

Overview

The video teaches beginner-friendly Excel formula and function skills using a sample dataset (Name, Department, Salary). It emphasizes how to build formulas correctly, explains common math operations, and then introduces several essential Excel functions (with shortcuts and guidance for conditional calculations).

Key Concepts: Building Excel Formulas

• Formulas start with =.
• You select cells using mouse or arrow keys, then press Enter to calculate.
• Formulas are dynamic: if input cells change, the result updates automatically.

Mathematical Operations in Excel

Excel supports standard math operators:

• Addition with +
• Subtraction with -
• Division with /
• Multiplication with * (asterisk required)
• Grouping using brackets/parentheses

Conceptually, you can wrap parts in parentheses to control the order of calculation.

Core “You Need These” Functions (with Use Cases)

1. SUM

   • Adds all values in a selected range (e.g., total salaries).
   • Demonstrates selecting ranges inside parentheses: SUM(range).

2. COUNT

   • Counts cells in a range that contain numbers only.
   • Example outcome: counting salary values works, but COUNT returns 0 for text (e.g., names).

3. COUNTA

   • Counts cells that are not empty (counts both numbers and text).

4. MIN

   • Finds the smallest value in a range (e.g., minimum salary).

5. MAX

   • Finds the largest value in a range (e.g., maximum salary).

How to Find/Insert Functions (Guides/Tutorial Features)

• Formulas tab → Insert Function

  • Helps beginners locate a function and understand what it does.
  • Example shown: searching for “add” and using suggestions for SUM.

• AutoSum

  • A faster way to insert common functions.
  • Provides a dropdown to choose function type (e.g., Sum, Average).
  • Supports a keyboard shortcut: Alt + = to quickly sum the intended range.
  • Works both vertically and horizontally.

• AutoSum caution

  • Auto-selected ranges may be wrong (the video corrects an Average range that included an extra cell).

Conditional Averages (More Advanced, Explained for Beginners)

Introduces:

• AVERAGEIF (single condition)
• AVERAGEIFS (multiple conditions)

Demonstration goal: average salary for employees in a specific department (e.g., Sales).

How the tutorial makes it beginner-friendly:

• Uses the function argument dialog via Insert Function to explain:
  • average_range = which cells contain the numbers to average (e.g., salary cells)
  • criteria_range = which cells are checked against the condition (e.g., department cells)
  • criteria = the condition value (e.g., "Sales")

Important detail:

• When criteria is text, Excel requires quotation marks, so the department like Sales becomes "Sales".

Callouts / Takeaways

• The focus is on correct syntax, especially:
  • Use * for multiplication
  • Use quotes for text criteria in formulas
• Beginner aids highlighted:
  • Insert Function dialog
  • AutoSum
  • Built-in function suggestions
• The video also mentions there are other, more complex function videos on the channel.

Main Speakers / Sources

• Speaker: The YouTube creator/instructor (narrator of the tutorial).
• Primary source of information: Excel functions and built-in UI tools shown in the tutorial (Formulas tab, Insert Function, AutoSum, function argument dialog).