DAY 1: Excel - Formulas & Basic Functions

Guru Charan Pathalla

DAY 1: Excel - Formulas & Basic Functions

Learning Objectives: - Understand Excel interface and navigation - Master cell references (Relative, Absolute, Mixed) - Learn basic arithmetic and formulas - Use SUM, AVERAGE, COUNT, MAX, MIN functions

Topics Covered (60 min):

1. Excel Interface Basics - Ribbon, tabs, quick access toolbar - Workbooks, worksheets, cells - Navigation shortcuts (Ctrl+Arrow, Ctrl+Home/End)

2. Cell References - Relative (A1): Changes when copied - Example: =A1B1 becomes =A2B2 when copied down - Absolute ($A1) :  *  * Fixedwhencopied − Example :  = A1*B1(B1stayssameforallrows) −  *  * Mixed(A1 or A$1): Lock row OR column - $A1: Column fixed, row changes - A$1: Row fixed, column changes

3. Basic Functions - =SUM(A1:A10) - Adds all numbers in range - =AVERAGE(B1:B10) - Calculates mean - =COUNT(C1:C10) - Counts numeric values - =MAX(D1:D10) - Finds highest value - =MIN(D1:D10) - Finds lowest value

Video Resources:

  1. Excel Formulas and Functions - Tutorial for Beginners (11:42)
    https://www.youtube.com/watch?v=ZwiQ0W5lTEg

  2. Excel for Beginners: Master Formulas, Functions & Cell Referencing (13:31)
    https://www.youtube.com/watch?v=BJWYPFO1X7M

  3. Excel Full Course 2025 | Excel Tutorial For Beginners (27:41)
    https://www.youtube.com/watch?v=ZBQLpHPvi6M

AI Learning Prompts (Concept Understanding Only):

Understanding Cell References:

I'm learning Excel cell references. Please explain:

1. What is the difference between relative (A1), absolute ($A$1), and mixed ($A1, A$1) cell references?
2. Give me 3 real-world scenarios where I should use each type
3. Show me what happens when I copy a formula with each reference type to different cells
4. Provide examples using a simple price × quantity calculation

Please explain in beginner-friendly language with step-by-step examples.

Understanding Basic Functions:

Explain Excel's basic functions for a beginner:

1. How do SUM, AVERAGE, COUNT, MAX, and MIN functions work?
2. What is the exact syntax for each function?
3. Give me examples using employee salary data (different from sales data)
4. What are common mistakes beginners make with these functions?
5. When would I use COUNT vs COUNTA?

Provide simple examples that help me understand the concepts.

Understanding Formula Logic:

Help me understand how Excel formulas work:

1. Why do formulas start with = sign?
2. What is the order of operations in Excel (PEMDAS)?
3. How do I reference cells from different sheets?
4. Explain the concept of cell ranges (A1:A10)
5. Show me examples using student test scores (not sales data)

Use simple examples to explain each concept.

Guided Practice (60 min):

📝 How to approach Guided Practice:

  • Follow step-by-step: Work through each exercise in order
  • Use provided resources: Reference the video tutorials and AI prompts if you get stuck
  • Verify your work: Check your results match expected outcomes
  • Take notes: Document key learnings and shortcuts you discover
  • Ask for help: Use AI assistants to clarify concepts you don't understand

Using Superstore Sales dataset - Excel Practice

📝 Setup Instructions:

  1. Open Microsoft Excel or Google Sheets
  2. Download or create a Superstore Sales dataset with columns: Order ID, Product, Sales, Cost, Quantity, Discount
  3. Ensure your data has headers in row 1
  4. Save the file as "Day01_Practice.xlsx"

Exercise 1: Calculate Total Sales for All Orders

Step-by-Step Instructions:

  1. Look for the "Sales" column in your dataset (e.g., column D)
  2. Click on an empty cell below your data (e.g., D102 if your data ends at row 100)
  3. Type a label "Total Sales:" in the cell to the left (C102)
  4. In cell D102, type the formula: =SUM(D2:D100) (adjust D100 to your last data row)
  5. Press Enter to execute the formula

💡 Tips:

  • Quick way to find last row: Press Ctrl + ↓ (Down Arrow) from the header cell
  • Alternative: Type =SUM(, then click and drag to select all sales values, then type )
  • You can also use =SUM(D:D) to sum the entire column (Excel ignores text headers)

✅ Expected Result: You should see a large number representing total sales (typically in thousands or millions depending on your dataset)

🔄 SQL Equivalent: See how the same calculation works in SQL!

Exercise 2: Find Average Discount Across All Transactions

Step-by-Step Instructions:

  1. Locate the "Discount" column (look for values like 0.0, 0.15, 0.20)
  2. Click on an empty cell (e.g., F102)
  3. Type "Average Discount:" in the cell to the left (E102)
  4. In cell F102, type: =AVERAGE(F2:F100) (replace F and row numbers with your data)
  5. Press Enter
  6. Format as percentage: Right-click the cell → "Format Cells" → "Percentage" → Set to 2 decimal places → Click OK

💡 Tips:

  • Discounts are usually decimals (0.15 = 15% off)
  • Keyboard shortcut for percentage format: Ctrl + Shift + %
  • If some cells are blank, AVERAGE automatically ignores them

✅ Expected Result: Typically 10-15% average discount in sales data

🔄 SQL Equivalent: See how the same calculation works in SQL!

Exercise 3: Count Total Number of Orders in Dataset

Step-by-Step Instructions:

  1. Choose any column with data in every row (like Order ID or Sales)
  2. Click on an empty cell (e.g., B104)
  3. Type "Total Orders:" in cell A104
  4. In cell B104, type: =COUNT(D2:D100) (using Sales column as example)
  5. Press Enter

💡 Tips:

  • COUNT() only counts cells with numbers
  • If your Order ID column has text, use =COUNTA(A2:A100) instead (counts non-empty cells)
  • Alternative: =ROWS(D2:D100) counts the number of rows in a range
  • Double-check your result matches your row count!

✅ Expected Result: A number matching your data rows (if you have 98 rows of data, you should see 98)

🔄 SQL Equivalent: See how the same calculation works in SQL!

Exercise 4: Find Maximum and Minimum Order Quantities

Step-by-Step for Maximum:

  1. Locate the "Quantity" column (usually contains numbers 1-15)
  2. Click on an empty cell (e.g., G102)
  3. Type "Max Quantity:" in F102
  4. In cell G102, type: =MAX(G2:G100) (replace G with your quantity column letter)
  5. Press Enter

Step-by-Step for Minimum:

  1. Click on the cell directly below (G103)
  2. Type "Min Quantity:" in F103
  3. In cell G103, type: =MIN(G2:G100)
  4. Press Enter

💡 Tips:

  • Time-saver: Copy the MAX formula (Ctrl+C), paste below (Ctrl+V), then just change "MAX" to "MIN"
  • If your minimum shows 0, you might have blank cells in your range
  • These functions help you quickly understand the range of your data

✅ Expected Result: Max quantity typically 5-15, Min quantity usually 1-2

🔄 SQL Equivalent: See how the same calculation works in SQL!

Exercise 5: Calculate Profit for Each Order (Relative References)

Step-by-Step Instructions:

  1. Find your "Sales" and "Cost" columns (e.g., D and E)
  2. Click on the first cell of an empty column (e.g., if your last column is H, click I1)
  3. Type the header "Calculated Profit" in I1
  4. Click on cell I2 (first data row)
  5. Type the formula: =D2-E2 (Sales minus Cost)
  6. Press Enter
  7. Copy the formula down to all rows:
    • Click on I2 again to select it
    • Move your mouse to the bottom-right corner of the cell until you see a small black plus sign (fill handle)
    • Double-click the fill handle (Excel auto-fills to the last row with adjacent data)
    • OR drag the fill handle down to your last row
  8. Watch Excel automatically calculate profit for every single row!

💡 Tips:

  • This demonstrates relative cell references - the formula automatically adjusts for each row
  • After copying, click random cells (I10, I50, etc.) to verify: formulas should show =D10-E10, =D50-E50, etc.
  • Format as currency: Select the profit column → Press Ctrl + Shift + $
  • Some profits may be negative (losses) - this is normal!

✅ Expected Result: A complete column of profit values, with the formula automatically adjusted for each row

🔄 SQL Equivalent: See how to display profit alongside other columns in SQL!

🎯 Practice Goal Checkpoint:

By completing these 5 exercises, you should now be able to:

  • Use SUM, AVERAGE, COUNT, MAX, and MIN functions confidently
  • Understand how relative cell references work when copying formulas
  • Navigate Excel efficiently using keyboard shortcuts
  • Format cells appropriately (currency, percentage)
  • Verify your results make sense

Take a 5-minute break, then move on to Independent Practice!

🎯 Interactive SQL Playground

💻 Learn by Doing - SQL Practice Right Here!

Use this interactive SQL editor to practice queries on sample Superstore data. Start with SELECT * FROM Orders and modify it based on the exercises!

💡 Try these example queries:

  • SELECT * FROM Orders LIMIT 10 - View first 10 orders
  • SELECT SUM(Sales) FROM Orders - Calculate total sales (like Excel SUM)
  • SELECT AVG(Discount) FROM Orders - Find average discount
  • SELECT COUNT(*) FROM Orders - Count total orders
  • SELECT MAX(Sales), MIN(Sales) FROM Orders - Find highest and lowest sales
  • SELECT Region, SUM(Sales) as TotalSales FROM Orders GROUP BY Region - Sales by region
  • SELECT * FROM Orders WHERE Sales > 100 ORDER BY Sales DESC - Orders over $100

Tip: Click any example query above to load it into the editor! Press Ctrl+Enter to run queries quickly.

Independent Practice (60 min):

💡 Hints for Independent Practice:

  • Start simple: Begin with basic variations before attempting complex modifications
  • Break it down: Divide complex problems into smaller, manageable steps
  • Test incrementally: Verify each step works before moving to the next
  • Use documentation: Refer to official docs or tutorials when needed
  • Experiment: Try different approaches to find what works best

Solve these 8 problems yourself - practice makes perfect!

  1. Create 15% tax formula using absolute reference for tax rate
  2. Find total quantity of products sold
  3. Calculate average profit margin (Profit/Sales)
  4. Count how many products have discount > 0
  5. Find highest profit amount in dataset
  6. Create commission calculator: 5% if sales<500, else 8%
  7. Calculate range (MAX-MIN) of order quantities
  8. Sum all profits for East region only

Daily Assignment (60 min):

Expected Outcomes:

✓ Understand when to use relative vs absolute references
✓ Write formulas with basic functions confidently
✓ Complete practice problems with 80%+ accuracy
✓ Submit: Day01_Excel_Practice.xlsx

📝 Day 1 Quick Quiz - Test Your Knowledge!

Q1. What does the $ symbol do in an Excel cell reference like $A$1?

Q2. Which function would you use to add all values in cells A1 through A10?

Q3. What is the F4 key shortcut used for in Excel?

Q4. Which formula correctly references cell B5 with an absolute reference?

Q5. What does the formula =AVERAGE(A1:A5) calculate?

Have feedback or questions?