Class Notes: Introduction to Excel Formulas and Functions

Objective

• Learn how to use Excel formulas and functions step-by-step.
• Understand the difference between formulas and functions.
• Avoid common mistakes and get tips for efficient data management.

---

1. Formulas in Excel

• Definition: Custom calculations using numbers, cell references, and mathematical operators.
• How to Enter: Always start with '='.
  • Example: =100+150
• Cell References: Use cell references (like B2 + B3) instead of typing numbers for dynamic calculations.
• Math Operators in Formulas:
  • Addition: +
  • Subtraction: -
  • Multiplication: *
  • Division: /

Example Calculations

• Sum of Cells: =B2+B3
• Difference: =B3-B2
• Double Value: =B2*2
• Ratio: =B3/B2

---

2. Functions in Excel

• Definition: Built-in tools provided by Excel to perform specific calculations more efficiently.
• Syntax: Start with =functionName(arguments)
  • Arguments are the values or cell ranges the function will use.

Common Functions

• SUM: Adds a range of numbers.
  • Usage: =SUM(B2:B8)
• AVERAGE: Calculates the mean.
  • Usage: =AVERAGE(B2:B8)
• MAX: Finds the highest number.
  • Usage: =MAX(B2:B8)
• MIN: Finds the lowest number.
  • Usage: =MIN(B2:B8)
• COUNT: Counts the number of entries.
  • Usage: =COUNT(B2:B8)
• IF: Applies logic to return different results based on a condition.
  • Syntax: =IF(logical_test, value_if_true, value_if_false)
  • Example: =IF(B8>300, "great job", "you're making too many YouTube videos")

---

3. Using the Function Library

• Location: Found in the 'Formulas' tab in Excel.
• Categories: Financial, logical, text, date & time, and more.
• Tip: Hover over a function in the library to see a description.

---

4. Relative vs Absolute References

• Relative Reference: Cell references update automatically when copied (e.g., B2).
• Absolute Reference: Cell references remain fixed when copied by adding $ (e.g., $B$2).
  • Shortcut: Press F4 to toggle $ in your references.
• Practical Example:
  • Copying a SUM formula to another cell without absolute references will update the referenced range.
  • Using $ keeps the reference fixed.

---

5. Quick Tips and Best Practices

• Excel displays the sum of highlighted cells in the toolbar (no formula needed).
• Using cell references in formulas allows calculations to update automatically when data changes.
• Functions make calculations with many cells manageable and less error-prone.
• Explore the formula bar and function wizard (fx icon) for step-by-step help.
• Learning absolute versus relative references helps avoid errors when copying formulas.
• Use the function library for exploring and learning new functions.

---

6. Summary

• Formulas: Custom, start with =, use numbers/cell references/operators.
• Functions: Pre-built, start with =FUNCTION(), save time with repetitive tasks.
• Knowing both is essential for efficient data analysis in Excel.

---

7. Further Learning

• Explore the Formulas tab and function library.
• Try creating formulas/functions with sample data.
• Practice using absolute and relative references.

---

If you have a specific function you want to learn about, leave a comment or request it for the next lesson/video!