Essential Excel Functions and Their Uses

Aug 22, 2024

Excel Functions Overview

1. Average Function

  • Usage: =AVERAGE(range)
    • Example: =AVERAGE(15, 27, 42, 93, 56, 84, 75, 33, 49)
    • Result: 53.125
  • Quick Calculation:
    • For two numbers: =AVERAGE(100, 200) → Result: 150
    • For three numbers: =AVERAGE(100, 200, 300) → Result: 200

2. Sum Function

  • Usage: =SUM(range)
    • Example: =SUM(range of numbers) → Result: 429
  • Quick Addition:
    • For two numbers: =SUM(50, 125) or =50 + 125 → Result: 175
    • For three numbers: =SUM(100, 200, 300) → Result: 600

3. Sum If Function

  • Usage: =SUMIF(range, criteria, sum_range)
    • Example:
      • To sum only Mazda vehicles:
      • =SUMIF(car_type_range, "Mazda", sum_range) → Result: 692
  • Dynamic Calculation:
    • Change criteria to Honda or Toyota → Automatically adjusts the sum.

4. Count Function

  • Usage: =COUNT(range)
    • Counts number of cells containing numbers.
    • Example: 10 cells with numbers.

5. Count A Function

  • Usage: =COUNTA(range)
    • Counts non-empty cells (both numbers and text).
  • Example: Count in a mixed list → Returns total of non-empty cells.

6. Count Blank Function

  • Usage: =COUNTBLANK(range)
    • Counts empty cells in a specified range.

7. Count If Function

  • Usage: =COUNTIF(range, criteria)
    • Example: Count how many times a specific car type appears.
  • Dynamic Counting:
    • Adjust criteria (e.g., Mazda or Toyota) and get updated counts.

8. Concatenate Function

  • Usage: =CONCATENATE(text1, text2)
    • Example: Combine first and last names with a space.
  • Detailed Usage:
    • =CONCATENATE(B2, " ", C2) → Adds a space between the names.

9. IF Function

  • Basic Usage: =IF(logical_test, value_if_true, value_if_false)
    • Example: Check if a cell contains "orange".
    • Dynamic calculations based on true/false criteria.
    • Example: If cell value > 30 → Calculate or return zero.

10. VLOOKUP Function

  • Usage: =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
    • Example for looking up name, email, phone number, revenue.
    • Adjust the column index number to get different data (e.g., 2 for email, 3 for phone).

11. Drop Down List

  • Usage: Create a dropdown for easy selection from a list.
    • Go to DataData Validation → Allow list → Enter names or select range.
  • Dynamic Updating: Selecting a name updates related information automatically.

Additional Resources

  • For more detailed videos, check the description for the Excel video tutorial playlist.
  • Other topics available: Algebra, Geometry, Trig, Precalculus, Chemistry, Physics.