Foundations · Module 03

Spreadsheet Foundations.

Build the spreadsheet habits analysts rely on before writing SQL: formulas, cleaning, dates, validation, lookups, pivots and repeatable transformations.

Core idea

A spreadsheet is not just a grid. It is a small analytical system: clean the inputs, encode the business rule, validate the result, then summarize at the right grain.

Formula essentials

Start with the formulas analysts use every week.

CompanyDhakaCart DatasetDaily order export QuestionWhat happened in today's orders? ToolExcel or Google Sheets

Before dashboards and SQL, analysts often receive a spreadsheet and need a reliable answer in minutes. The first skill is knowing which formula pattern matches the question.

Interactive workbookOrders · 5 rows

#	A · Order	B · Amount	C · Status	D · Customer	E · District
2	O-1001	3200	Paid	Nusrat	Dhaka
3	O-1002	7600	Paid	Arif	Chattogram
4	O-1003		Pending	Mim	Dhaka
5	O-1004	11800	Refunded	Rafi	Sylhet
6	O-1005	9050	Paid	Tanjim	Dhaka

TotalsSUM · SUMIF · SUMIFS

Add everything, or only rows that meet one or more conditions.

CountingCOUNT · COUNTA · COUNTIF · COUNTIFS

Count numbers, populated cells, or rows that match business rules.

LogicIF · AND · OR · IFERROR

Classify rows and handle expected lookup or calculation errors cleanly.

CombineCONCAT · CONCATENATE · & · TEXTJOIN

Build labels, keys and readable strings from multiple columns.

Cleaning & dates

Standardize messy text and dates before analysis.

Why it matters

A lookup fails when one ID has an extra space. A monthly report breaks when half the dates are text. Cleaning is analysis infrastructure, not cosmetic work.

Raw → clean

+8801711223344→01711223344

Phone=RIGHT(A2,11)

Raw → clean

saJID hASAN →Sajid Hasan

Name=PROPER(TRIM(A2))

Raw → clean

ORD-98214-DHAKA→98214

Order ID=MID(A2,5,5)

Date formatting lab

One date, different business views.

Keep the underlying value as a real date. Change how it is displayed instead of turning every date into text.

Raw date Display format

Result04-Oct-2026

TRIMPROPERLEFTRIGHTMIDSUBSTITUTETEXTDATEVALUEYEARMONTH

Data quality

Find blanks, duplicates and invalid values before they become metrics.

Order_ID	Customer	District	Status	Amount
O-2101	Afsana	Dhaka	Paid	4200
O-2102		Sylhet	Pending	7800
O-2102	Riyad	Sylhet	Pending	7800
O-2103	Maliha		Complete
O-2104	Shuvo	Chattogram	Refunded	5100

Data validation

Prevent bad values before you have to clean them.

Use dropdowns for controlled fields such as order status, division or product category.

Try a validated status

Lookups

Bring customer details into the order table safely.

ID	Name	District	Phone
C101	Jamal Uddin	Gazipur	01711111111
C102	Shahida Heren	Dhaka	01822222222
C103	Tasfia	Chattogram	01933333333
C104	Rahim Khan	Sylhet	01644444444

Lookup sandbox Customer ID

=IFERROR(VLOOKUP(F2,A2:D5,2,FALSE),"Not Found")

Matched customerTasfiaChattogram · 01933333333

Learn VLOOKUP because old workbooks use it. Prefer XLOOKUP in modern Excel when available because it is clearer and safer.

Pivot tables

Build a summary by choosing the grain, measure and aggregation.

Mental model

A pivot is a fast GROUP BY. Decide what each row represents, what number you are measuring, and whether that number should be SUM, COUNT or AVERAGE.

Rows Second row Values Summarize by Filter division

Source data

Date	Division	Category	Sales
2026-08-04	Dhaka	Electronics	12000
2026-08-11	Dhaka	Fashion	5400
2026-08-19	Sylhet	Fashion	6900
2026-09-03	Chattogram	Electronics	9800
2026-09-09	Dhaka	Home	7300
2026-09-16	Sylhet	Electronics	11100
2026-10-02	Chattogram	Fashion	6100
2026-10-07	Dhaka	Electronics	14500

Pivot output

Repeatable cleaning

Use Power Query when the same cleanup arrives every month.

Raw monthly export loaded. The same store appears twice, dates use mixed formats, and Jan/Feb/Mar are separate columns.

Google Sheets QUERY

See the SQL idea before the SQL module.

QUERY editor

This is intentionally a focused simulator for the two patterns taught here—not a complete Google Visualization Query engine.

Spreadsheet filterWHERE

SortORDER BY

Pivot / SUMIFSGROUP BY + SUM()

VLOOKUP / XLOOKUPJOIN

You now know the spreadsheet version of the analytical operations. Module 04 teaches how to express the same ideas directly against database tables with SQL.

Mini case

Clean → validate → enrich → summarize.

Your deliverables

Standardize names, phone numbers and dates. Find blanks, duplicates and invalid status values. Enrich orders with customer information. Create a division/category sales pivot. Write two sentences explaining the result.

Practice outside the browser

Repeat the exercises in a real spreadsheet so the muscle memory transfers to Excel or Google Sheets.

XLSX workbookDownload ↘ Raw CSVDownload ↘