🎯 What You'll Learn
- Apply MAP function with LAMBDA to process multiple columns simultaneously
- Use UNIQUE and FILTER to extract specific data from large datasets
- Implement SORT and SORTBY to organize data dynamically
- Create reusable custom functions with LET and LAMBDA
- Transform data shapes with ROW, COLUMN, TOROW, and TOCOL
- Build loops with BYROW and BYCOL for row/column-wise calculations
📋 Before You Begin
- Basic Excel navigation and cell references
- Understanding of simple formulas (SUM, AVERAGE, IF)
- Knowledge of relative vs absolute cell references
1. MAP Function — The Column Processor
Perfect for showing how two columns interact. MAP takes two ranges and applies a LAMBDA function to each pair of values.
1. The "Total Price" Map
Multiplies quantities by unit prices to get total cost.
excel
=MAP(B2:B6, C2:C6, LAMBDA(qty, prc, qty * prc))
Arrow 1: B2:B6 (Quantities) → qty
Arrow 2: C2:C6 (Unit Prices) → prc
Arrow 2: C2:C6 (Unit Prices) → prc
2. The "Full Name" Map
Combines first and last names into a full name.
excel
=MAP(B2:B6, C2:C6, LAMBDA(first, last, first & " " & last))
3. The "Budget Check" Map
Compares actual spending against budget limits.
excel
=MAP(B2:B6, C2:C6, LAMBDA(spend, budget, spend > budget))
4. The "Growth Rate" Map
Calculates percentage change between two years.
excel
=MAP(B2:B6, C2:C6, LAMBDA(old, new, (new-old)/old))
View More MAP Examples
5. Student Grade: =MAP(B2:B6, C2:C6, LAMBDA(test1, test2, MAX(test1, test2))) — Finds higher of two test scores
6. Shipping Days: =MAP(B2:B6, C2:C6, LAMBDA(start, end, end - start)) — Subtracts dates to find duration
7. Commission: =MAP(B2:B6, C2:C6, LAMBDA(sales, rate, sales * rate)) — Applies commission rate to sales
8. Address Formatter: =MAP(B2:B6, C2:C6, LAMBDA(city, state, city & ", " & state)) — Joins City and State
9. Inventory Status: =MAP(B2:B6, C2:C6, LAMBDA(in_hand, min, IF(in_hand < min, "ORDER", "OK"))) — Checks if restock needed
10. Currency Converter: =MAP(B2:B6, C2:C6, LAMBDA(usd, rate, usd * rate)) — Multiplies by exchange rate
Why the Arrows Matter
Without the arrows, students often ask: "How does the formula know q1 is column B?"
First Range in the list → First Name in the LAMBDA.
Second Range in the list → Second Name in the LAMBDA.
Without the arrows, students often ask: "How does the formula know q1 is column B?"
First Range in the list → First Name in the LAMBDA.
Second Range in the list → Second Name in the LAMBDA.
2. UNIQUE & FILTER — The Data Cleaners
These functions look at a messy pile of data and extract exactly what you need.
- UNIQUE: Removes duplicates.
- FILTER: Pulls rows that meet a specific "Rule."
10 Practical Examples:
- Unique Cities: Get a list of every city your company has sold in (no repeats)
- Filter by Region: Show only "West Coast" sales
- Unique Students: List all students who signed up for at least one club
- Filter High Scorers: Show only students with a grade > 90
- Unique Categories: Extract all unique product categories from inventory
- Filter Out of Stock: Show only items where Quantity = 0
- Filter by Date: Show only orders from "Today"
- Unique Months: See which months had activity in a long list of dates
- Filter Specific Names: Pull every row where employee name is "Sarah"
- Combined: UNIQUE(FILTER(A:A, B:B="Active")) — List unique names of only active members
3. SORT & SORTBY — The Organizers
These take your "Spill" and put it in order (A-Z, High-Low).
- SORT: Sorts based on a column inside the range.
- SORTBY: Sorts a range based on a different list entirely.
10 Practical Examples:
- Alphabetical Names: SORT(A2:A10)
- Price High-to-Low: SORT(B2:C10, 2, -1) — Sorts by 2nd column, descending
- Recent Dates: Sort a task list by the "Due Date" column
- Rank by Score: Sort students by their final percentage
- Sort by Length: Use SORTBY to sort words by character count
- Heaviest First: Sort an equipment list by weight
- Store Location: Sort sales data by "Store ID"
- Priority Sort: Sort tasks by "Priority" number (1, 2, 3)
- Two-Level Sort: Sort by "Last Name," then "First Name"
- Randomize: SORTBY(A2:A10, RANDARRAY(9)) — Shuffle a list!
4. LET & LAMBDA — The Logic Builders
These make formulas cleaner and allow you to create your own "mini-apps."
- LET: Gives a "nickname" to a value so you don't have to type it twice.
- LAMBDA: Turns a math formula into a reusable custom function.
10 Practical Examples:
- The "Tax Calc" (LET): LET(price, A2, tax, 0.08, price + (price * tax))
- The "Profit Margin" (LET): Define Sales and Costs once, then subtract
- The "Full Name" (LAMBDA): Create a function that always joins First and Last
- Unit Converter: A LAMBDA that always converts Miles to Kilometers
- Clean Text: A LET that trims spaces and converts to lowercase in one go
- Commission Tier: A LET that stores percentage rate based on sales volume
- Overtime Calc: A LAMBDA that calculates hours over 40
- BMI Formula: Store weight and height as nicknames using LET
- Discount Logic: A LAMBDA that applies 10% if user is a "Member"
- Complex Math: Any formula where you type the same cell reference 3 times
5. ROW, COLUMN, TOROW, TOCOL — The Shapers
These change the "shape" of your data.
- TOROW / TOCOL: Takes a block (2D) and turns it into one long line (1D).
10 Practical Examples:
- One Column List: Take a 3x3 grid of names and make them one long list
- Horizontal Header: Take a vertical list of months and flip them into a top row
- Find Row Number: ROW(A5) tells you the cell is on line 5
- Striping: Use ISODD(ROW()) to create alternating colors
- Total Count: Use ROWS(A2:A50) to count how many items are in a list
- Grid to List: Take a weekly schedule (Mon-Fri) and turn into a single column
- List to Header: Turn a column of "Department Names" into a horizontal row
- Empty Row Checker: Use ROW to help find blank spaces
- Column ID: COLUMN(C1) returns 3
- Flatten Data: Using TOCOL to combine multiple columns into one master list
6. BYROW, BYCOL — The Loopers
Similar to MAP, but they process an entire row or entire column at once.
10 Practical Examples:
- Row Totals: BYROW(B2:D10, LAMBDA(row, SUM(row))) — Sums each row individually
- Row Average: Find the average grade for every student across 3 tests
- Column Max: BYCOL(B2:F10, LAMBDA(col, MAX(col))) — Find highest sale for each day
- Count Non-Blanks: Count how many tasks are finished per row
- Column Average: Find average sales for each different store
- Row Logic: Check if every cell in a row is "Complete"
- Row Search: Check if a specific keyword exists anywhere in that row
- Horizontal Min: Find the cheapest item in a row of different store prices
- Column Range: Calculate difference between Max and Min in a column
- Row Status: If sum of a row > 100, mark as "High Volume"
7. SEQUENCE, VSTACK, HSTACK — The Builders
These create data from scratch or glue pieces together.
- SEQUENCE: Generates numbers (1, 2, 3...)
- VSTACK: Glues Range A on top of Range B
- HSTACK: Glues Range A next to Range B
10 Practical Examples:
- Numbering: SEQUENCE(10) creates a 1-10 list instantly
- Calendar: SEQUENCE(7, 1) creates a week of days
- Combine Tables: VSTACK(Sales_Jan, Sales_Feb) puts Feb data under Jan
- Side-by-Side: HSTACK(Names, Scores) glues two lists together
- Odd Numbers: SEQUENCE(10, 1, 1, 2) — 1, 3, 5, 7...
- Year List: SEQUENCE(5, 1, 2024) — 2024, 2025, 2026...
- Adding Headers: VSTACK({"Name", "Score"}, A2:B10) — Glues header onto data
- Double List: HSTACK(A2:A10, A2:A10) — Duplicates a column
- Countdown: SEQUENCE(10, 1, 10, -1) — 10, 9, 8...
- Master Table: VSTACK(Store1, Store2, Store3) — Creates one giant list
Knowledge Check
1. What does MAP function do with two columns?
2. Which function removes duplicate values from a list?
3. What is the main purpose of LET function?
4. What does VSTACK function do?
5. In MAP(B2:B6, C2:C6, LAMBDA(qty, prc, qty * prc)), what does "qty" represent?
Key Takeaways
- MAP processes two columns simultaneously using LAMBDA — position determines parameter mapping
- UNIQUE extracts distinct values; FILTER pulls rows matching conditions
- SORT orders within a range; SORTBY orders based on another array
- LET improves readability by naming intermediate calculations
- LAMBDA creates reusable functions that can be called like built-in functions
- TOROW/TOCOL transform 2D ranges into 1D arrays
- BYROW/BYCOL apply LAMBDA to entire rows or columns at once
- SEQUENCE generates numeric sequences; VSTACK/HSTACK combine ranges