Formulas Basics

Who this is for

Beginner and early-career Data Analysts who need reliable spreadsheet formulas to clean data, compute KPIs, and build quick analyses in Excel or Google Sheets.

Prerequisites

• Comfort entering data in cells and selecting ranges
• Basic arithmetic (add, subtract, multiply, divide)
• Know how to insert a new sheet and rename it

Why this matters

As a Data Analyst, you will often:

• Calculate core KPIs like conversion rate, average order value, churn rate
• Clean data with simple checks (blanks, duplicates, outliers)
• Aggregate metrics by channel, region, or product
• Build quick prototypes before moving analysis to SQL or Python

Strong formula basics make these tasks fast, accurate, and repeatable.

Concept explained simply

A formula is an instruction you give a cell to compute a result using values from other cells. You start with an equals sign = and combine references (like A2), operators (+, -, *, /), and functions (like SUM, AVERAGE).

Mental model

Think of your sheet as a grid calculator. Each cell can pull values from other cells (references), combine them (operators), and apply built-in recipes (functions). When you copy a formula, references usually shift relative to the new position—unless you lock them.

Core building blocks: references and ranges

• Relative reference: A2 changes when copied (A2 becomes A3 when filled down)
• Absolute reference: $A$2 stays fixed when copied
• Mixed references: $A2 or A$2 lock either column or row
• Ranges: A2:A10 (vertical), B2:F2 (horizontal), B2:F10 (rectangle)
• Order of operations: Parentheses first, then exponent, multiply/divide, add/subtract (PEMDAS)

Essential functions for analysts

• SUM(range): total of numbers
• AVERAGE(range): mean of numbers
• COUNT(range) / COUNTA(range): count numbers / count non-blank cells
• COUNTIF(range, criteria): count matching condition (e.g., "Yes")
• SUMIF(range, criteria, sum_range): sum values matching a condition
• IF(test, value_if_true, value_if_false): branch logic
• ROUND(number, digits): control decimals
• VLOOKUP/XLOOKUP: fetch related values by key (XLOOKUP preferred when available)

Worked examples

Example 1: Total revenue

Data (rows 2–6):

Qty (A)  Price (B)
3        12.00
5        7.50
2        15.00
4        9.00
6        8.00

1. In C2: =A2*B2
2. Fill down to C6
3. Total in C7: =SUM(C2:C6)

Example 2: Flag low conversion rates

Data (rows 2–6): Visits in A, Signups in B. Conversion in C = B2/A2. Flag in D: "Review" if conversion < 3%.

1. In C2: =IF(A2=0, 0, B2/A2)
2. In D2: =IF(C2<0.03, "Review", "OK")
3. Format C as percent and fill down

Example 3: Sum revenue for a region

Data: Region in A, Revenue in B. Sum only "West".

Formula: =SUMIF(A2:A100, "West", B2:B100)

Practice: guided steps

1. Create a new sheet named "Basics"
2. Enter small data: quantities, prices, and regions (5–10 rows)
3. Compute row revenue, then total revenue
4. Add an IF-based flag for low/zero revenue
5. Use SUMIF to total revenue by one region

• I used at least one absolute reference ($)
• I filled formulas down without retyping
• I formatted percentages and currency

Exercises

Complete the exercises below. Open hints if stuck, then check the solution.

Common mistakes and self-check

• Forgetting the equals sign: every formula must start with =
• Wrong ranges: off-by-one errors (e.g., stopping at row 9 instead of 10). Self-check: click the colored range borders after selecting the formula.
• Relative references drifting: when copying, a cell like D1 becomes D2. Fix with $: $D$1. Self-check: toggle F4 (Excel) or add $ manually.
• Divide-by-zero: C2=B2/A2 crashes when A2=0. Guard with IF(A2=0,0,B2/A2).
• Text vs number: "10" (text) won’t sum. Self-check: use VALUE() or remove stray spaces and apostrophes.

Practical projects

• Weekly sales tracker: quantities, prices, row revenue, total revenue; add a SUMIF by product category
• Marketing funnel: visits, signups, conversion rate, IF flags for underperforming channels; highlight channels below target
• Support QA log: statuses by agent; COUNTIF and percentage of fails; top issues using simple filters

Learning path

• Now: Formula syntax, references, SUM/AVERAGE/IF/COUNTIF/SUMIF
• Next: Text functions (TRIM, LEFT/RIGHT, CONCAT), date functions (TODAY, EOMONTH), lookup functions (XLOOKUP, INDEX/MATCH)
• Later: Array formulas, pivot tables, named ranges, error handling (IFERROR)

Next steps

• Finish the two exercises below
• Take the Quick Test to check retention
• Apply formulas on a real small dataset (work or public sample) within 24 hours

Mini challenge

Build a one-sheet dashboard showing:

• Total revenue
• Revenue by region (use SUMIF)
• Overall conversion rate and an IF flag if below 2.5%

Quick test

Take the Quick Test below. Available to everyone; only logged-in users get saved progress.