CodeVerse Creatives
Esc
Excel, SQL & Power BI Syllabus / Module 1 · Excel Fundamentals / Week 1

Week 1: Excel Fundamentals — Formulas, Functions & Clean Data

This course assumes nothing about your prior data experience — just that you can open a spreadsheet. This week builds the formula habits that every later week, and most analyst job descriptions, take for granted: absolute vs. relative references, the handful of functions that do 80% of the work, and the discipline of catching bad data before it reaches a report.

Module 1 of 12 Week 1 of 12 ~2–3 Hours Hands-on Exercise Included

By the end of this week, you'll be able to

  • Explain the difference between an absolute and a relative cell reference, and use both correctly
  • Write SUM, AVERAGEIFS, COUNTIFS and nested IF formulas confidently
  • Clean messy text/date data and stop bad entries with data validation
1 Cells, Ranges & References 2 Core Formulas 3 Conditional Logic with IF 4 Cleaning Text & Dates 5 Data Validation 6 Exercise 7 Knowledge Check

1. Cells, Ranges & Absolute vs. Relative References

Every Excel formula starts with = and refers to other cells by their address — B2, C2:C10, and so on. By default those addresses are relative: if you copy a formula from row 2 to row 3, Excel silently shifts every reference in it down one row too. That's usually exactly what you want — until it isn't.

excel
' In C2:
=A2*B2
' Copy C2 down to C3 -- Excel automatically rewrites it as:
=A3*B3

An absolute reference locks a row, a column, or both with a $, so copying the formula doesn't shift that part. This matters the moment a formula needs to point at one fixed cell — a tax rate, a target, a lookup table — while everything else around it still varies row by row.

excel
' Tax rate lives in F1 and should NEVER shift when copied
=A2*B2*$F$1

' $F  locks the column, F$1 locks the row, $F$1 locks both -- press F4 to cycle through them
#REF! and wildly wrong totals almost always trace back to this

If a copied formula suddenly points at the wrong cell, check whether a reference that should have been absolute was left relative (or vice versa) before you assume the formula itself is broken.

2. Core Formulas: SUM, AVERAGEIFS & COUNTIFS

SUM and AVERAGE cover the simple case. The moment you need a total or an average for a subset of rows — "total sales in the North region," "average order value for repeat customers" — reach for the IFS family instead of filtering the sheet by hand:

excel
' Total of column D, no conditions
=SUM(D2:D500)

' Average of column D, only rows where column B = "North"
=AVERAGEIFS(D2:D500, B2:B500, "North")

' Count of rows where region = "North" AND amount > 1000
=COUNTIFS(B2:B500, "North", D2:D500, ">1000")

' SUMIFS follows the same pattern: sum range first, then condition pairs
=SUMIFS(D2:D500, B2:B500, "North", C2:C500, "Electronics")

Notice the argument order is different between SUMIFS/COUNTIFS and the older single-condition SUMIF/COUNTIF — the IFS versions always take the range to summarize first, then condition/range pairs. Learn the IFS versions first; they scale to multiple conditions and the single-condition versions are just a special case.

3. Conditional Logic with Nested IF

IF(condition, value_if_true, value_if_false) branches a formula's result. Nesting one IF inside another lets you handle more than two outcomes — but past two or three levels deep, a formula gets hard to read, so know when to reach for it and when not to.

excel
' Single condition
=IF(D2>1000, "High Value", "Standard")

' Three tiers, nested
=IF(D2>5000, "Platinum", IF(D2>1000, "Gold", "Standard"))

' IFERROR wraps ANY formula to replace an error with something readable
=IFERROR(D2/E2, "N/A")
' Without it, a divide-by-zero anywhere on the sheet shows a raw #DIV/0! error
More than 2–3 nested IFs? Reach for IFS instead

=IFS(D2>5000,"Platinum", D2>1000,"Gold", TRUE,"Standard") reads top to bottom as plain condition/result pairs, with none of the closing-parenthesis counting that makes deeply nested IF formulas so easy to get wrong.

4. Cleaning Messy Text & Date Data

Real-world spreadsheets are rarely clean: extra spaces, inconsistent capitalization, and dates stored as text are the three problems you'll hit constantly before any analysis can even start.

excel
' Strip leading/trailing/extra internal spaces
=TRIM(A2)

' Standardize casing
=PROPER(A2)     ' "john smith" -> "John Smith"
=UPPER(A2)
=LOWER(A2)

' Join fields with a clean separator
=CONCAT(B2, " ", C2)          ' first + last name
' or with the newer, more flexible operator:
=B2 & " " & C2

' Pull a piece out of a longer string
=LEFT(A2, 3)                  ' first 3 characters
=RIGHT(A2, 4)                 ' last 4 characters
=MID(A2, 5, 3)                ' 3 characters starting at position 5

' Force text that LOOKS like a date into a real date value
=DATEVALUE(A2)

That last one matters more than it looks: a date typed or imported as text sorts alphabetically instead of chronologically, and breaks any date-based formula you try to run against it. =ISNUMBER(A2) is a quick way to check whether a cell that looks like a date actually behaves like one.

5. Data Validation: Stop Bad Data at the Source

Cleaning formulas fix data after the fact. Data Validation (Data tab → Data Validation) stops bad entries before they ever land in the sheet — the cheapest place to catch an error is at entry time, not three formulas later.

  • List — restrict a cell to a dropdown of allowed values (e.g. only "North", "South", "East", "West")
  • Whole number / Decimal — reject text typed into a field that must be numeric
  • Date — reject dates outside a valid range (no order dates in the future, for instance)
  • Custom — write your own formula-based rule for anything the built-in types don't cover
A dropdown list also standardizes downstream formulas

Free-typed entries like "North", "north " and "N." all mean the same thing to a human but are three different strings to COUNTIFS. A dropdown makes that impossible in the first place.

6. Hands-on Exercise

Hands-on

Clean and summarize a messy sales sheet

Build the exact workflow you'd use on a real raw export: clean it, validate it, then summarize it.

Requirements:

  1. Build a sheet with columns: Region, Product, Amount, Order Date — 20+ rows, deliberately including extra spaces, inconsistent casing (" north", "North ", "NORTH") and at least one date typed as text.
  2. Use TRIM and PROPER in a helper column to standardize Region, and DATEVALUE to fix the text date.
  3. Add a Data Validation dropdown on Region restricted to your four valid region names, so future entries can't drift again.
  4. Calculate total and average Amount per region with SUMIFS and AVERAGEIFS against your cleaned region column.
  5. Add a Tier column using nested IF (or IFS): "High Value" above 1000, "Standard" otherwise.
  6. Wrap one calculation that could divide by zero in IFERROR so it shows "N/A" instead of an error.
Hint

If SUMIFS returns 0 when you know matching rows exist, it's almost always a trailing-space or casing mismatch between the condition text and the actual cell values — summarize against your cleaned helper column, not the original messy one.

7. Knowledge Check

Four quick questions. Expand each to check your answer.

Q1

Why would you use $F$1 instead of F1 in a formula you're about to copy down a column?

By default, cell references are relative and shift when a formula is copied to a new row or column. $F$1 locks both the column and the row so that every copy of the formula still points at that one fixed cell — essential when a formula refers to a single constant, like a tax rate, that should never move.

Q2

What's the difference in argument order between SUMIF and SUMIFS?

SUMIF(range, criteria, sum_range) puts the condition range first. SUMIFS(sum_range, criteria_range1, criteria1, ...) puts the sum range first, then any number of condition/range pairs. Learning SUMIFS first avoids having to relearn the argument order once a single condition isn't enough.

Q3

A column of dates is sorting alphabetically instead of chronologically. What's the most likely cause?

The dates are almost certainly stored as text rather than real date values — often the case after importing from a CSV or another system. =ISNUMBER(cell) confirms it, and =DATEVALUE(cell) converts a text date into a real one that sorts and calculates correctly.

Q4

Why is a Data Validation dropdown better than just being careful when typing region names?

Formulas like COUNTIFS compare text exactly, so "North", "north " and "N." are three different values to Excel even though a person reads them as the same thing. A dropdown makes those inconsistent variants impossible to enter in the first place, instead of relying on cleanup formulas to catch them after the fact.

← Back to Full Syllabus Next Week 2: SQL Fundamentals