Microsoft Excel Formulas Tutorial

Introduction

• Presenter: Kevin (Microsoft Employee)
• Focus: Using formulas in Microsoft Excel
• Applicable versions: Works on any version of Excel (2013, 2016, 2019, Office 365)

Basic Operations

• Starting a Formula:

  • Begin with = sign
  • Example: Adding 1 + 1
    • Type: =1+1 or =B1+C1 (using cell references)

• Addition/Subtraction/Multiplication/Division:

  • Addition: =B1 + C1
  • Subtraction: =B2 - C2
  • Multiplication: =B3 * C3 (use * for multiplication)
  • Division: =B4 / C4 (use / for division)

Using the SUM Formula

• SUM:
  • Use to add multiple values efficiently
  • Example: =SUM(B1:B5)
  • Can also use mouse to select range

Other Functions

• MIN and MAX:

  • Minimum: =MIN(range)
  • Maximum: =MAX(range)

• COUNT and COUNTA:

  • COUNT: Counts cells with numbers
    • Example: =COUNT(range)
  • COUNTA: Counts all non-empty cells
    • Example: =COUNTA(range)

• AVERAGE and MEDIAN:

  • Average: =AVERAGE(range)
  • Median: =MEDIAN(range)

• CONCATENATE:

  • Joins two or more values
  • Example: =CONCATENATE(A1, " ", B1) (add space between values)

Conditional Functions

• IF Statement:

  • Structure: =IF(condition, true_value, false_value)
  • Example: =IF(A1="smile", "yay", "boo")

• COUNTIF:

  • Counts cells that meet criteria
  • Example: =COUNTIF(range, criteria)

Advanced Functions

• VLOOKUP:

  • Looks up a value in one column and returns a value in another
  • Example: =VLOOKUP(lookup_value, table_array, col_index, [range_lookup])

• SUMIF:

  • Adds values based on criteria
  • Example: =SUMIF(range, criteria, sum_range)

Date and Time

• Current Time:
  • Use =NOW() to get the current date and time

Conclusion

• Encourage viewers to like and subscribe for more tutorials
• Invite comments for future topics
• Thank you and goodbye!