Excel Flashcards

  1. SUM() → Adds numbers.

    • =SUM(A1:A10) → Adds values from A1 to A10.

  2. AVERAGE() → Finds the mean.

    • =AVERAGE(B1:B5) → Average of B1–B5.

  3. IF() → Returns one value if true, another if false.

    • =IF(C2>70,"Pass","Fail")

  4. TODAY() → Returns today’s date.

    • =TODAY()

  5. CONCATENATE() / & → Joins text.

    • =A1 & " " & B1 → Joins first and last name.

🔹 Intermediate Functions

  1. VLOOKUP() → Finds a value in a table.

    • =VLOOKUP(101, A2:D20, 3, FALSE) → Finds ID 101 in column A, returns value from column 3.

  2. INDEX() + MATCH() → More flexible lookups.

    • =INDEX(C2:C10, MATCH("John", A2:A10, 0)) → Finds John in A and returns value from C.

  3. SUMIF() → Adds numbers based on condition.

    • =SUMIF(A1:A10, ">100", B1:B10) → Adds B values where A > 100.

🔹 Advanced Functions

  1. XLOOKUP() (newer Excel) → Easier replacement for VLOOKUP.

    • =XLOOKUP("John", A2:A10, B2:B10, "Not Found")

  2. PivotTables (Tool) → Summarize data quickly.

  • Drag & drop fields to calculate totals, averages, counts across categories.

✅ With just these 10, you can cover:

  • Data basics (SUM, AVERAGE, IF).

  • Date/Text handling (TODAY, CONCATENATE).

  • Analysis & lookups (VLOOKUP, INDEX+MATCH, SUMIF, XLOOKUP).

  • Advanced analysis (PivotTables).