EXCEL FORMULAS -CHEAT SHEET

Scenario: Running a Small Grocery Shop

BEGINNER

1. SUM

Story: Riya owns a shop and wants to know the total sales from Monday to Saturday.
Formula: =SUM(B2:B7)
Use: Adds up all the daily sales.

2. AVERAGE

Story: Riya wants to know the average sale per day.

Formula: =AVERAGE(B2:B7)
Use: Calculates the mean of the sales.

3. MIN

Story: Which day had the lowest sales?

Formula: =MIN(B2:B7)
Use: Finds the minimum value.

4. MAX

Story: Which day was the highest selling day?

Formula: =MAX(B2:B7)
Use: Finds the maximum value.

5. COUNT

Story: How many days have numeric sales data?

Formula: =COUNT(B2:B7)
Use: Counts only numbers.

6. COUNTA

Story: How many days have any data (text/number)?

Formula: =COUNTA(B2:B7)
Use: Counts non-empty cells.

7. IF

Story: If the sale is above ₹1000, show “Good”, else “Low”.

Formula: =IF(B2>1000, "Good", "Low")
8. RIGHT / LEFT / MID

Story: Her invoice codes are “INV2025A1”. She wants just the number part.
RIGHT: =RIGHT(A2,2) → A1
LEFT: =LEFT(A2,3) → INV
MID: =MID(A2,4,4) → 2025

9. LEN

Story: She wants to check how many characters are in a product ID.
Formula: =LEN(A2)

10. TRIM

Story: A customer name has extra spaces — “ Rahul ”.

Formula: =TRIM(A2)

11. PROPER / UPPER / LOWER

Story: She wants consistent formatting.

• =PROPER("raHUl") → Rahul
• =UPPER("rahul") → RAHUL
• =LOWER("RAHUL") → rahul

12. NOW / TODAY

Story: She prints bills with current date/time.

• =NOW() → 31-07-2025 10:45 AM

• =TODAY() → 31-07-2025

CUSTOMER RECORDS & OFFERS - INTERMEDIATE

13. COUNTIF

Story: Count customers who bought more than ₹500 items.

Formula: =COUNTIF(B2:B10, ">500")

14. SUMIF

Story: Total sales for “Fruits” category.

Formula: =SUMIF(A2:A10, "Fruits", B2:B10)

15. AVERAGEIF

Story: Average sale for “Vegetables”.

Formula: =AVERAGEIF(A2:A10, "Vegetables", B2:B10)
16. IFERROR

Story: Some formulas return errors when no data.

Formula: =IFERROR(A2/B2, "No data")

17. ISBLANK / ISNUMBER

Story: Check if sales data is missing or valid.

=ISBLANK(A2) → TRUE if empty

=ISNUMBER(A2) → TRUE if it’s a number

18. SEARCH / FIND

Story: Does product name contain “apple”?

=SEARCH("apple", A2) → Position

=FIND("apple", A2) → Case-sensitive

19. CONCATENATE / CONCAT / TEXTJOIN

Story: Combine first and last names.

• =CONCATENATE(A2, " ", B2)

• =CONCAT(A2, " ", B2)
• =TEXTJOIN(" ", TRUE, A2, B2)

20. TEXT

Story: Show price as ₹ format.

Formula: =TEXT(A2, "₹#,##0.00")

21. ROUND / ROUNDUP / ROUNDDOWN

Story: Round discount to nearest rupee.

• =ROUND(A2, 0)
• =ROUNDUP(A2, 0)
• =ROUNDDOWN(A2, 0)
 TRACKING EXPIRY & BILLING DATES

22. DATEDIF

Story: Days between bill date and today.

Formula: =DATEDIF(A2, TODAY(), "d")

23. YEAR / MONTH / DAY / WEEKDAY / WEEKNUM

Story: Analyze date patterns.

• =YEAR(A2)
• =MONTH(A2)
• =DAY(A2)
• =WEEKDAY(A2)
• =WEEKNUM(A2)

24. MOD

Story: Riya offers a freebie on every 5th customer.

Formula: =MOD(ROW(), 5)=0

25. RANK

Story: Rank the top-selling products.

Formula: =RANK(B2, B2:B10)

LINKING PRODUCTS & INVENTORY

ADVANCED

26. VLOOKUP

Story: Look up price by product name.

Formula: =VLOOKUP("Apple", A2:B10, 2, FALSE)

27. HLOOKUP

Story: Find product code in horizontal table.

Formula: =HLOOKUP("Apple", A1:D2, 2, FALSE)

28. XLOOKUP

Story: More flexible lookup than VLOOKUP.

Formula: =XLOOKUP("Apple", A2:A10, B2:B10)
29. INDEX / MATCH / INDEX+MATCH

Story: Find product price using row-column logic.

• =INDEX(B2:B10, MATCH("Apple", A2:A10, 0))

30. FILTER / SORT / UNIQUE

Story:

• FILTER: Get only items above ₹500 → =FILTER(B2:B10, B2:B10>500)

• SORT: Sort sales from highest → =SORT(B2:B10, -1)

• UNIQUE: Get unique product names → =UNIQUE(A2:A10)

31. INDIRECT / OFFSET

Story: Refer to a changing cell dynamically.

• =INDIRECT("B"&A2)

• =OFFSET(B2, 1, 0) → cell below B2

ADVANCED EXCEL MAGIC

32. ARRAYFORMULA

Story: Apply a formula to a full column in Google Sheets.

Formula: =ARRAYFORMULA(A2:A10*2)

33. SEQUENCE

Story: Create 1 to 10 automatically.

Formula: =SEQUENCE(10)

34. LET / LAMBDA

Story: Define a name once and reuse it.

• LET(x, A1*2, x+100)

• LAMBDA(x, x+10)(5) → 15
35. CHOOSE

Story: Show weekday names from number.

Formula: =CHOOSE(2, "Sun", "Mon", "Tue") → Mon

36. IFS / SWITCH

Story: Multiple product rating rules.

• IFS(A2>90, "Excellent", A2>70, "Good", TRUE, "Poor")

• SWITCH(A2, "A", 100, "B", 80, "C", 60)

37. TEXTSPLIT / TEXTBEFORE / TEXTAFTER

Story: Break “Apple - ₹150” into parts.

• =TEXTBEFORE(A2, " - ") → Apple

• =TEXTAFTER(A2, " - ") → ₹150

• =TEXTSPLIT(A2, " - ") → [Apple, ₹150]

Grocery Shop Dataset

Invoice Customer Product Category Quantity Unit Total Sale Date Product Discount Rating Notes
Code Name Name Price Sale ID

INV202501 Riya Sharma Apple Fruits 5 ₹30.00 ₹150.00 2025-07- PRD001 ₹10 A Apple - ₹150
25

INV202502 Amit Verma Tomato Vegetables 3 ₹20.00 ₹60.00 2025-07- PRD002 ₹5 B Fresh stock
26

INV202503 Neha Joshi Milk Dairy 2 ₹50.00 ₹100.00 2025-07- PRD003 ₹0 A

27

INV202504 Rahul Mehta Bread Bakery 1 ₹40.00 ₹40.00 2025-07- PRD004 ₹2 C Contains
28 gluten

INV202505 Sneha Kapoor Butter Dairy 4 ₹60.00 ₹240.00 2025-07- PRD005 ₹15 A
29

INV202506 Arjun Singh Rice Grains 10 ₹80.00 ₹800.00 2025-07- PRD006 ₹50 B Bulk order
30

INV202507 Meera Nair Coffee Beverages 1 ₹120.00 ₹120.00 2025-07- PRD007 ₹10 A Coffee - ₹120
31

INV202508 Vikram Desai Salt Essentials 2 ₹15.00 ₹30.00 2025-07- PRD008 ₹0 C

31

INV202509 Tanya Bhatia Eggs Dairy 12 ₹6.00 ₹72.00 2025-07- PRD009 ₹5 B Organic eggs
31

INV202510 Karan Tea Beverages 3 ₹25.00 ₹75.00 2025-07- PRD010 ₹3 A Tea - ₹75
Malhotra 31