Excel Exercise · 6 free starter lessons included

Every Excel Concept Turned Into a Guided Lesson & Hands-On Simulator Exercise

Work through 91 structured lessons and 273 progressive simulator exercises across 16 skill modules — from cell arithmetic, SUMIFS, and XLOOKUP to PivotTables, dynamic FILTER arrays, and speed shortcuts. Read each lesson guide, solve real worksheets inside our live Excel 365 simulator, and lock in every takeaway.

Excel formulas and workbook drills
Your Mastery 0%
0/91
Lessons
0/273
Exercises
6
Unlocked
Try all 6 Free Starter Lessons (18 exercises) right now — create a free account to save your progress.

Free starter lessons (included for every visitor & free user)

Start Learning Risk-Free Across 6 Foundational Lessons (18 Interactive Exercises)

Getting Started in Excel

Foundations

Master cells, coordinates, live formula entry, and referencing values instead of typing static numbers.

1 lesson · 3 exercises
Free Beginner 0/3

Spreadsheet Coordinates & Live Formulas

Understand grid addresses, enter dynamic arithmetic with the = sign, and link values dynamically instead of typing static numbers.

=A2 + B2 | =B2 * C2 | =SUM(B2:B5)
Concept: Every Excel worksheet is an intersection of lettered columns and numbered rows (for example, C4 sits in Column C, Row 4). Beginning a cell entry with an equals sign (=) turns Excel from a static notebook into a live calculator that updates automatically whenever source figures change.
Key Rule: Always reference cell coordinates (=B2*C2) rather than retyping numbers (=15*4) so edits cascade through the sheet.
Pro Tip: While typing a formula after "=", click directly on any source cell in the grid to drop its address into your expression without typing it manually.
Mistake to Avoid: Forgetting the leading = sign causes Excel to display "B2+C2" as literal text instead of computing a result.
3 simulator exercises Open lesson in simulator

SUM & Conditional Totals

Core Math

Build everything from simple totals and AutoSum to multi-criteria SUMIFS and broken-formula debugging.

10 lessons · 30 exercises
Free Beginner 0/3

Totaling Ranges with the SUM Function

Add contiguous lists, combine multiple ranges inside a single SUM call, and total horizontal rows cleanly.

=SUM(number1, [number2], ...)
Concept: SUM adds every numeric value inside one or more ranges separated by commas. Using a colon range like B2:B6 instead of =B2+B3+B4+B5+B6 is shorter, less error-prone, and ignores descriptive text cells safely.
Key Rule: =SUM(B2:B7) adds a vertical block; =SUM(B2:E2) adds a horizontal row; =SUM(B2:B4, D2:D4) totals separate columns.
Pro Tip: Double-check range endpoints so header text or subtotal rows are not accidentally swept into your total.
Mistake to Avoid: Writing =SUM(B2+B6) only adds the first and last cells—use a colon (:) for ranges.
3 simulator exercises Open lesson in simulator
Pro Beginner 0/3

Whole-Column & Dynamic SUM Totals

Use whole-column references like =SUM(B:B), locked tax multipliers, and net totals alongside SUM.

=SUM(B:B) | =SUM(B2:B5)*(1+$E$1)
Concept: When transaction logs grow daily, summing fixed ranges requires updating formulas later. Whole-column references (B:B) or dollar-locked tax rates ($E$1) make summaries scalable and resilient.
Key Rule: Keep totals in a summary column (like D2) rather than inside Column B if you use =SUM(B:B) to avoid circular errors.
Pro Tip: Always avoid placing =SUM(B:B) inside Column B itself—that triggers a circular reference (#CIRC!).
Mistake to Avoid: Summing an entire column while placing the formula in the same column creates an infinite loop.
3 simulator exercises
Free Beginner 0/3

Instant Totals with AutoSum

Generate SUM formulas in one action using AutoSum (∑) and the classic Alt+= keyboard shortcut.

Ribbon ∑ AutoSum button OR Keyboard Shortcut: Alt + =
Concept: AutoSum automatically detects adjacent contiguous numbers directly above (or to the left of) your active cell and inserts a ready-to-confirm =SUM(...) formula.
Key Rule: Select the empty cell right below a column (or right of a row), press Alt+=, verify the range, and confirm.
Pro Tip: Select multiple empty total cells across a row at once before clicking AutoSum to drop SUM formulas across every column simultaneously.
Mistake to Avoid: Leaving an empty row above the total stops AutoSum from scanning higher cells—always check the range bounds.
3 simulator exercises Open lesson in simulator
Pro Beginner 0/3

Intro to SUMIF (Single Criterion)

Add only numbers that meet a condition using 2-argument and 3-argument SUMIF formulas.

=SUMIF(range, criteria, [sum_range])
Concept: SUMIF scans a criteria range and adds numbers matching one test. When the numbers themselves are tested (like "add everything over 500"), only 2 arguments are needed.
Key Rule: Wrap comparison operators inside quotes: =SUMIF(B2:B6, ">500") adds values strictly greater than 500.
Pro Tip: If your threshold lives in another cell like E1, concatenate the operator: ">"&E1.
Mistake to Avoid: Writing >500 without quotes produces a syntax error inside SUMIF criteria strings.
3 simulator exercises
Pro Intermediate 0/3

SUMIF & Aligned Sum Ranges

Prevent off-by-one shifts by aligning criteria_range and sum_range cleanly and linking criteria cells dynamically.

=SUMIF(A2:A10, D2, C2:C10)
Concept: In 3-argument SUMIF, Excel pairs row i of the criteria range with row i of sum_range. Keeping identical starting and ending rows prevents silent misalignment bugs.
Key Rule: Lock your data ranges ($A$2:$A$7, $C$2:$C$7) so copying a SUMIF down a summary table keeps scanning every row.
Pro Tip: Point criteria to a summary label cell (D2) instead of hardcoding "East" so one template calculates all regions.
Mistake to Avoid: Misaligned ranges like SUMIF(A2:A7, "East", C1:C6) sum the wrong rows silently.
3 simulator exercises
Pro Intermediate 0/3

SUMIFS Function (Multi-Criteria Totals)

Filter sums by two or more simultaneous conditions using SUMIFS argument order.

=SUMIFS(sum_range, criteria_range1, criteria1, criteria_range2, criteria2, ...)
Concept: Unlike SUMIF where sum_range comes last, SUMIFS puts sum_range FIRST, followed by paired criteria_range and criterion arguments. All conditions must be TRUE on a row (logical AND) for its value to be included.
Key Rule: Notice the reversed order: SUMIFS starts with what to add, then lists pairs of (where to check, what to match).
Pro Tip: Every range inside SUMIFS must share the exact same number of rows or columns, otherwise Excel returns #VALUE!.
Mistake to Avoid: Putting sum_range at the end like SUMIF—in SUMIFS, sum_range is always argument #1.
3 simulator exercises
Pro Intermediate 0/3

SUMIF Real-World Practice

Apply single-criterion SUMIF across SaaS billing, inventory reorders, and quarterly budgets.

=SUMIF(criteria_range, pattern, sum_range)
Concept: Fluency with SUMIF comes from quickly identifying the criteria column, the matching pattern, and the column being summed.
Key Rule: Use wildcards (* and ?) when labels contain regional tags or suffixes; use "<>" to isolate non-empty/non-matching records.
Pro Tip: When building expense dashboards, test SUMIF totals against a grand SUM to ensure no categories slipped past your spelling.
Mistake to Avoid: Mixing up criteria_range and sum_range column letters returns 0 with no visible error message.
3 simulator exercises
Pro Intermediate 0/3

SUMIFS Applied Scenarios

Tackle 3-criteria SUMIFS, cell-linked comparisons, and date-bounded revenue queries.

=SUMIFS(sum_range, rng1, crit1, rng2, crit2, rng3, crit3)
Concept: Complex reporting asks multiple questions simultaneously: "Revenue from Enterprise tier, in West region, after Q1." SUMIFS handles any number of range/criteria pairs concisely.
Key Rule: Pair ">="&StartCell and "<="&EndCell for clean dynamic date or number windows.
Pro Tip: Add one criterion pair at a time when building long SUMIFS so you can verify intermediate subtotals.
Mistake to Avoid: Expecting SUMIFS to act as OR—remember SUMIFS requires ALL criteria to match at once (AND logic).
3 simulator exercises
Pro Intermediate 0/3

Debugging Broken SUM & SUMIFS Formulas

Spot and repair off-by-one ranges, swapped SUMIF/SUMIFS arguments, and circular subtotal loops.

Inspect every range start/end coordinate + argument #1 of SUMIFS.
Concept: Most spreadsheet bugs do not throw loud error codes—they silently drop the last row, swap criteria vs sum ranges, or self-reference total cells.
Key Rule: Before trusting any SUM total, verify: (1) range reaches the last row, (2) SUMIFS leads with sum_range, (3) criteria ranges align row-for-row.
Pro Tip: Compare formula results against quick status-bar sums by highlighting the numeric range.
Mistake to Avoid: Leaving a copied formula as =SUM(B2:B4) after appending rows down to B7.
3 simulator exercises
Pro Intermediate 0/3

SUM Functions Skill Capstone

Demonstrate complete command of SUM, SUMIF, and SUMIFS in one integrated quarterly workbook.

=SUM(...) | =SUMIF(...) | =SUMIFS(...)
Concept: Analysts routinely combine unconditional totals (SUM), single-filter rollups (SUMIF), and multi-filter segment totals (SUMIFS) side by side on summary dashboards.
Key Rule: Choose the simplest tool that answers the question: SUM for everything, SUMIF for 1 rule, SUMIFS for 2+ rules.
Pro Tip: When multiple summary cells sit next to each other, keep criteria ranges consistent so results reconcile cleanly.
Mistake to Avoid: Hardcoding subtotals breaks reconciliation checks whenever source rows update.
3 simulator exercises

COUNT Family Mastery

Data Auditing

Quantify records cleanly with COUNT, COUNTA, COUNTBLANK, wildcards, and multi-condition COUNTIFS.

7 lessons · 21 exercises
Free Beginner 0/3

Counting Numeric Entries with COUNT

Understand what COUNT counts (numbers and dates) vs what it skips (text and blanks).

=COUNT(value1, [value2], ...)
Concept: COUNT counts only cells containing numeric values. Text labels, error messages, and empty cells are automatically ignored—making COUNT ideal for verifying how many numeric scores, prices, or completed scores exist.
Key Rule: Use =COUNT(B2:B7) on a score or price column; avoid using COUNT on text name columns (where it returns 0!).
Pro Tip: If a dataset mixes "N/A" text notes among numbers, COUNT cleanly tells you how many valid numbers remain.
Mistake to Avoid: Running =COUNT(A2:A7) on customer names and wondering why Excel returns 0—use COUNTA for text.
3 simulator exercises Open lesson in simulator
Pro Beginner 0/3

COUNTA & COUNTBLANK Auditing

Count non-empty cells (including text) with COUNTA and pinpoint missing data gaps with COUNTBLANK.

=COUNTA(range) | =COUNTBLANK(range)
Concept: While COUNT tracks only numbers, COUNTA counts EVERY populated cell (text, numbers, booleans, formulas) and COUNTBLANK counts genuinely empty cells for completeness audits.
Key Rule: COUNTA(range) + COUNTBLANK(range) equals total cells in that range—great for data completeness %!
Pro Tip: Run COUNTBLANK on required CRM fields (Email, Phone) right after importing data to catch missing info.
Mistake to Avoid: Using COUNTA when you actually want numeric items will accidentally count header or text notes too.
3 simulator exercises
Pro Beginner 0/3

COUNTIF Function (Conditional Counts)

Count rows matching exact text labels, inequality rules, or exclusions via COUNTIF.

=COUNTIF(range, criteria)
Concept: COUNTIF evaluates a single range against one criterion and tells you how many cells match—perfect for headcounts, ticket backlogs, and threshold tallies.
Key Rule: Keep comparison strings inside quotes (">=90", "<>Closed") or reference a cell directly (D2).
Pro Tip: Pointing COUNTIF to a label cell (=COUNTIF($B$2:$B$20, D2)) lets you fill down a frequency table fast.
Mistake to Avoid: Passing multiple ranges into COUNTIF fails—use COUNTIFS whenever you have 2 or more conditions.
3 simulator exercises
Pro Intermediate 0/3

COUNTIF + Wildcard Characters

Match partial text, prefixes, suffixes, and exact character-length codes using * and ? in COUNTIF.

=COUNTIF(range, "*word*") | =COUNTIF(range, "ID-???")
Concept: Wildcards turn COUNTIF into a pattern matcher: * matches any number of characters (including zero), while ? matches exactly one character.
Key Rule: Use "Pro*" for "starts with Pro", "*@gmail.com" for domains, "*error*" for substring search, and "A-??" for fixed length.
Pro Tip: If your data literally contains an asterisk or question mark, escape it with a tilde: "~*" or "~?".
Mistake to Avoid: Writing =COUNTIF(A2:A6, "gmail") looks for cells whose entire value is "gmail"—add * to match substrings!
3 simulator exercises
Pro Intermediate 0/3

COUNTIFS Function (Multi-Criteria Counts)

Count records satisfying 2, 3, or more simultaneous conditions cleanly with COUNTIFS.

=COUNTIFS(criteria_range1, criteria1, criteria_range2, criteria2, ...)
Concept: COUNTIFS extends COUNTIF to multiple columns: pair criteria_range1/criteria1, criteria_range2/criteria2, and so on. A row is counted only if ALL conditions pass together.
Key Rule: Use COUNTIFS for funnel metrics: e.g., leads from a campaign AND stage = "Qualified" AND deal size > 5000.
Pro Tip: All criteria ranges inside COUNTIFS must have identical dimensions (e.g., all 2:10).
Mistake to Avoid: Passing an odd number of arguments causes #VALUE!—every criteria range needs its condition.
3 simulator exercises
Pro Intermediate 0/3

COUNT Family Applied Practice

Choose accurately between COUNT, COUNTA, COUNTBLANK, COUNTIF, and COUNTIFS on messy operations datasets.

COUNT vs COUNTA vs COUNTBLANK vs COUNTIF vs COUNTIFS
Concept: Picking the right counting function depends on whether you are auditing numbers (COUNT), non-blank labels (COUNTA), blanks (COUNTBLANK), 1 rule (COUNTIF), or multiple rules (COUNTIFS).
Key Rule: Never use COUNT on text columns; use COUNTA for names/text and COUNTIFS for multi-field segments.
Pro Tip: Test edge cases like zeroes vs empty cells—COUNT counts 0 as a number, whereas COUNTBLANK ignores 0!
Mistake to Avoid: Expecting COUNTBLANK to count spaces (" ")—only genuinely empty cells count as blank.
3 simulator exercises
Pro Intermediate 0/3

COUNT Skill Summary

Build a complete multi-metric HR staffing audit combining COUNTBLANK, COUNTIF, and COUNTIFS.

=COUNTBLANK(...) | =COUNTIF(...) | =COUNTIFS(...)
Concept: Operational headcount trackers rely on complementary count formulas: missing compliance checks (COUNTBLANK), contract splits (COUNTIF), and filtered active headcount (COUNTIFS).
Key Rule: Verify every count by checking that segmented categories reconcile against COUNTA total rows.
Pro Tip: Store reusable filter labels in summary rows and reference them so adding new locations scales fast.
Mistake to Avoid: Including header row 1 inside COUNTA(A1:A7) inflates headcount by +1.
3 simulator exercises

Logical Decisions & Error Guards

Decision Logic

Create branching business rules with TRUE/FALSE, AND/OR/XOR, IF, IFS, SWITCH, IS functions, and nested logic.

7 lessons · 21 exercises
Pro Beginner 0/3

Boolean Comparisons: TRUE, FALSE & NOT

Compare numbers, text, and dates using comparison operators (=, >, <, <>) and invert booleans with NOT.

=B2>=100 | =A2=B2 | =NOT(C2="Blocked")
Concept: Every logical formula in Excel reduces down to either TRUE or FALSE. Writing comparisons like =B2>=100 or =C2<>D2 creates reusable boolean flags you can later feed into IF, AND, OR, or FILTER.
Key Rule: Use <> for "not equal to" and NOT(...) to flip any TRUE/FALSE condition cleanly.
Pro Tip: Boolean helper columns often make giant nested IF sheets twice as readable and fast.
Mistake to Avoid: Writing != instead of <> in Excel—Excel strictly uses <> for not-equal comparisons.
3 simulator exercises
Pro Beginner 0/3

Combining Tests with AND, OR & XOR

Require every check to pass (AND), at least one check (OR), or strictly one check (XOR).

=AND(cond1, cond2, ...) | =OR(cond1, cond2, ...) | =XOR(cond1, cond2)
Concept: Compound rules combine multiple tests: AND returns TRUE only if all conditions pass; OR returns TRUE if one or more conditions pass; XOR returns TRUE if an odd number (1 of 2) of tests pass.
Key Rule: Use AND/OR standalone for clean flags, or nest them inside argument 1 of IF().
Pro Tip: XOR is ideal for mutually exclusive rules like "eligible for Track A OR Track B, but not both simultaneously."
Mistake to Avoid: Writing =AND(B2 > 10 AND < 20) fails—you must repeat the cell ref in each argument: =AND(B2>10, B2<20).
3 simulator exercises
Free Beginner 0/3

Conditional Branching with IF

Return custom text labels, tiered calculations, and dynamic rates based on an IF test.

=IF(logical_test, value_if_true, value_if_false)
Concept: IF runs one logical test and branches: returning argument 2 when TRUE or argument 3 when FALSE. Return values can be text in quotes, numbers, or even formulas like B2*0.1.
Key Rule: Keep numbers unquoted when you want math outputs (100, B2*0.1) and quote text labels ("Pass", "Over").
Pro Tip: Put calculations directly inside IF branches (=IF(B2>1000, B2*0.1, 0)) so one formula checks and calculates.
Mistake to Avoid: Quoting numbers like "100" inside IF stores text instead of a numeric value.
3 simulator exercises Open lesson in simulator
Pro Intermediate 0/3

Compound IF Combinations (AND/OR + Nested IF)

Combine IF with AND/OR rules and multi-tier nested IF chains for nuanced grading & approvals.

=IF(AND(c1, c2), yes, no) | =IF(x>=90, "A", IF(x>=80, "B", "C"))
Concept: Most business rules combine conditions ("Tier 1 if Revenue > 10k AND Retention >= 95%, else ...") or branch three+ outcomes using nested IF.
Key Rule: When nesting IFs for numeric brackets, test strictly from highest to lowest (or lowest to highest) consistently.
Pro Tip: If a nested IF exceeds 3 layers, consider IFS() or a clean VLOOKUP bracket table.
Mistake to Avoid: Mismatching closing parentheses on nested IF chains—pair each IF(...) carefully at the end.
3 simulator exercises
Pro Intermediate 0/3

IS Functions & IFERROR Validation

Inspect cell data types and catch broken formulas safely using ISBLANK, ISNUMBER, ISTEXT, and ISERROR.

=ISBLANK(ref) | =ISNUMBER(val) | =ISTEXT(val) | =ISERROR(val)
Concept: Excel’s IS* family checks what kind of value sits inside a cell (blank, number, text, or error) without crashing—letting you wrap fragile lookups or validate user entries.
Key Rule: Pair IF with ISBLANK (=IF(ISBLANK(B2), "Missing", B2)) or ISERROR to build bullet-proof audit flags.
Pro Tip: Always wrap risky divisions or lookups with IFERROR(formula, fallback) for cleaner client-ready sheets.
Mistake to Avoid: Using =B2="" instead of ISBLANK behaves slightly differently on empty-string formulas vs truly untouched cells.
3 simulator exercises
Pro Intermediate 0/3

Clean Multi-Condition Tiers with IFS

Replace hard-to-read nested IF pyramids with flat test/result pairs via IFS.

=IFS(cond1, val1, cond2, val2, ..., TRUE, fallback)
Concept: IFS evaluates conditions sequentially in clean (test, result) pairs and stops at the very first TRUE condition—eliminating stacked closing parentheses.
Key Rule: Finish an IFS call with TRUE, "Default" so unmatched inputs return a friendly fallback instead of #N/A.
Pro Tip: Order IFS conditions from most restrictive/highest cutoff down to the broadest default.
Mistake to Avoid: Leaving out the final TRUE catch-all causes #N/A whenever a value fails all prior conditions.
3 simulator exercises
Pro Intermediate 0/3

Exact Value Mapping with SWITCH

Map short codes, plan IDs, and status numbers cleanly without repeating the cell reference using SWITCH.

=SWITCH(expression, val1, res1, val2, res2, ..., [default])
Concept: When checking ONE expression against exact discrete values (1->"Q1", 2->"Q2"), SWITCH avoids repeating B2=1, B2=2 over and over.
Key Rule: Include an optional final single default argument (no pair needed) to handle unknown codes gracefully.
Pro Tip: Use SWITCH for fixed lookup codes (airport codes, star ratings, shipping tiers) and IFS when comparing > / < intervals.
Mistake to Avoid: Trying to write ">100" inside SWITCH—SWITCH only tests exact equality against the leading expression.
3 simulator exercises

Modern & Classic Lookups

Lookups

Pull matching records across pricing tables, tax brackets, and rows with XLOOKUP, VLOOKUP, and HLOOKUP.

10 lessons · 30 exercises
Pro Intermediate 0/3

Intro to VLOOKUP (Exact Vertical Lookup)

Find a key down the leftmost column of a table and retrieve a corresponding value from another column.

=VLOOKUP(lookup_value, table_array, col_index_num, FALSE)
Concept: VLOOKUP scans down column 1 of table_array for lookup_value, then walks rightward to col_index_num and returns that cell. Always specify FALSE (or 0) as arg 4 for exact ID/name matches.
Key Rule: Lookup key MUST live in the first column of table_array; column 2 returns the second column of your selected table.
Pro Tip: Lock table_array with $ ($A$2:$C$10) whenever you copy a VLOOKUP down multiple rows.
Mistake to Avoid: Omitting FALSE defaults VLOOKUP to approximate match—which returns wrong matches on unsorted text keys!
3 simulator exercises
Pro Intermediate 0/3

VLOOKUP Function 2 (Error Guards & Multi-Table)

Protect VLOOKUP against missing keys (#N/A) using IFERROR/IFNA and handle shifted table start columns.

=IFERROR(VLOOKUP(id, B2:E7, 3, FALSE), "Not Found")
Concept: When a table does not start in Column A (e.g., B2:E7), VLOOKUP’s col_index_num counts from Column B (col 1) rather than Column A! Wrapping VLOOKUP with IFERROR replaces ugly #N/A errors cleanly.
Key Rule: Count col_index_num relative to the FIRST column of table_array—not column A of the worksheet.
Pro Tip: Prefer IFNA over IFERROR on VLOOKUP when you want to catch missing IDs while still noticing #REF! range bugs.
Mistake to Avoid: Counting from column A when table_array starts in column B returns the column to the right or #REF!.
3 simulator exercises
Pro Intermediate 0/3

VLOOKUP Practical Scenarios

Practice VLOOKUP across payroll, FX currency conversion, and product catalog lookups.

=VLOOKUP($E2, $A$2:$C$8, 3, FALSE)
Concept: Repeated VLOOKUP drills train instant recognition of lookup_value, table bounds, column index, and absolute vs relative references.
Key Rule: Lock table bounds with $ when preparing a formula to fill down multiple order rows.
Pro Tip: Always confirm your lookup key has no extra trailing spaces that block exact matches.
Mistake to Avoid: Forgetting FALSE on text codes—if codes aren’t sorted A–Z, approximate mode gives wrong rows.
3 simulator exercises
Pro Intermediate 0/3

More VLOOKUP Mastery (Wildcards & Fill)

Use exact-match wildcards (* and ?) inside VLOOKUP and fill locked VLOOKUP formulas down multiple rows.

=VLOOKUP("*"&E2&"*", $A$2:$B$6, 2, FALSE)
Concept: When exact IDs aren’t known in full, VLOOKUP with arg4 = FALSE supports * and ? wildcards on lookup_value. And locking the table ($A$2:$C$5) lets Ctrl+D copy formulas down cleanly.
Key Rule: Prepending/appending "*" lets partial search queries match full names inside VLOOKUP exact mode.
Pro Tip: Verify your first matched row is unique when using wildcards—VLOOKUP always returns the top-most match.
Mistake to Avoid: Leaving table bounds unlocked ($A$2:$B$6 missing $) causes range drift when copied downward!
3 simulator exercises
Pro Intermediate 0/3

VLOOKUP Approximate Match (Tiers & Brackets)

Use approximate match (TRUE) on sorted ascending bracket tables for commissions, grades, and bulk discounts.

=VLOOKUP(score, $A$2:$B$6, 2, TRUE)
Concept: Approximate match (arg 4 = TRUE or omitted) scans an ASCENDING first column and picks the largest bracket floor <= your lookup value—turning tiered rate schedules into tiny lookup tables.
Key Rule: First column MUST be sorted strictly lowest-to-highest (0, 50, 75, 90) so approximate match picks the right bracket.
Pro Tip: Prefer bracket VLOOKUP(..., TRUE) over 6 nested IFs whenever tax, shipping weight, or commission tiers change quarterly.
Mistake to Avoid: Forgetting to sort column 1 ascending breaks approximate VLOOKUP silently.
3 simulator exercises
Pro Intermediate 0/3

Horizontal Lookups with HLOOKUP

Search across the top header row of horizontal matrices and retrieve matching values from lower rows.

=HLOOKUP(lookup_value, table_array, row_index_num, [FALSE])
Concept: HLOOKUP is the horizontal twin of VLOOKUP: when headers run across row 1 (Q1, Q2, Q3, Q4) instead of down column A, HLOOKUP finds your key across the top row and drops down row_index_num rows.
Key Rule: Top row is always row 1 of table_array; row 2 is the first data row underneath.
Pro Tip: Budget models organized horizontally by month or quarter are prime spots for HLOOKUP.
Mistake to Avoid: Using VLOOKUP on a sideways horizontal table returns #N/A or wrong numbers.
3 simulator exercises
Pro Intermediate 0/3

HLOOKUP Function 2 (Brackets & Safety)

Apply approximate horizontal bracket lookups (TRUE) and wrap missing headers in IFERROR.

=HLOOKUP(qty, A1:E2, 2, TRUE) | =IFERROR(HLOOKUP(...), 0)
Concept: Like VLOOKUP, HLOOKUP supports approximate tier matching (arg 4 = TRUE) across ascending top rows, plus wildcards and IFERROR error guards.
Key Rule: Top row thresholds must run lowest-to-highest left-to-right for approximate HLOOKUP.
Pro Tip: If your HLOOKUP table starts on Row 2 (not Row 1), start table_array at A2:E4 and count row_index_num from Row 2!
Mistake to Avoid: Leaving row_index_num set to absolute sheet row numbers when table starts below Row 1.
3 simulator exercises
Pro Intermediate 0/3

Modern Lookups with XLOOKUP

Upgrade from VLOOKUP to XLOOKUP for leftward lookups, resilient column references, and built-in fallbacks.

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found])
Concept: XLOOKUP is Excel 365’s modern successor to both VLOOKUP and HLOOKUP. It defaults to exact match, uses separate lookup_array and return_array ranges (so lookups can go LEFT!), and includes a built-in if_not_found fallback.
Key Rule: Keep lookup_array and return_array aligned row-for-row; unlike VLOOKUP, inserting middle columns will never break column indices!
Pro Tip: Notice XLOOKUP needs NO "FALSE" flag—exact match is already the default.
Mistake to Avoid: Passing the whole table A2:D6 into arg 2 instead of a single lookup column triggers #VALUE!.
3 simulator exercises
Pro Intermediate 0/3

XLOOKUP Function 2 (Match Modes & Horizontal)

Configure XLOOKUP’s 5th match_mode argument (-1 exact/next smaller, 1 exact/next larger) and horizontal row arrays.

=XLOOKUP(val, look_rng, ret_rng, [if_not_found], [match_mode])
Concept: XLOOKUP’s optional 5th argument lets you match brackets without sorting headaches: match_mode = -1 picks exact or next smaller tier; match_mode = 1 picks exact or next larger tier!
Key Rule: To pass arg 5 when you don’t need a custom not-found message, leave arg 4 empty between commas: =XLOOKUP(E2, A2:A5, B2:B5, , -1).
Pro Tip: match_mode = 1 (next larger) solves capacity sizing problems ("pick smallest box size >= order weight") effortlessly!
Mistake to Avoid: Putting -1 in argument 4 instead of argument 5 causes XLOOKUP to return -1 as a missing message rather than match_mode.
3 simulator exercises
Pro Intermediate 0/3

XLOOKUP & Wildcard Mode (match_mode = 2)

Enable wildcard matching (* and ?) inside XLOOKUP using match_mode = 2.

=XLOOKUP("*keyword*", lookup_rng, return_rng, "Not Found", 2)
Concept: Unlike VLOOKUP, XLOOKUP treats * and ? literally by default—you MUST pass match_mode = 2 (arg 5) whenever using wildcard patterns!
Key Rule: Remember match_mode codes: 0 = exact (default), -1 = next smaller, 1 = next larger, 2 = wildcard match.
Pro Tip: Wrap search cells dynamically with "*"&E2&"*" and match_mode = 2 to build fast search bars.
Mistake to Avoid: Writing =XLOOKUP("PRO-*", A2:A5, B2:B5) without arg 5 = 2 searches for a literal asterisk and fails with #N/A!
3 simulator exercises

INDEX & MATCH Precision

Flexible Lookup

Decouple position lookup from return columns for leftward, exact, and threshold lookups.

6 lessons · 18 exercises
Pro Intermediate 0/3

Finding Positions with MATCH

Return the 1-based row or column index position of an item using MATCH with match_type 0, 1, and -1.

=MATCH(lookup_value, lookup_array, [match_type])
Concept: Unlike VLOOKUP which returns a cell value, MATCH tells you WHERE an item lives—returning its numeric 1-based position inside a row or column.
Key Rule: Use 0 for exact match (most common), 1 for <= on ascending lists, and -1 for >= on descending lists.
Pro Tip: MATCH alone answers "which row # is SKU-50?"—then you feed that row number into INDEX()!
Mistake to Avoid: Omitting 0 causes MATCH to assume sorted ascending order, returning wrong positions on unsorted text.
3 simulator exercises
Pro Intermediate 0/3

MATCH Practical Drills & Wildcards

Apply MATCH with wildcards (*), approximate ascending cutoffs (+1), and error checks with ISNA/IFNA.

=MATCH("*part*", A2:A10, 0) | =IFNA(MATCH(E2, A2:A6, 0), 0)
Concept: MATCH(val, range, 0) natively understands Excel wildcards (* and ?). Wrapping MATCH with IFERROR handles missing items cleanly.
Key Rule: Combine MATCH with IF(ISNUMBER(MATCH(...)), "In List", "Missing") for rapid list membership checks!
Pro Tip: Testing whether an ID appears in another list with =ISNUMBER(MATCH(id, list, 0)) is cleaner than COUNTIF on large blocks.
Mistake to Avoid: Searching across a 2D table (A2:B6) fails with #N/A—MATCH strictly accepts a single row or single column.
3 simulator exercises
Pro Intermediate 0/3

Retrieving by Coordinates with INDEX

Extract exact values from any 1D list or 2D grid by passing row_num and col_num to INDEX.

=INDEX(array, row_num, [column_num])
Concept: INDEX acts like GPS coordinates for a range: give it an array, a row number, and an optional column number, and it returns whatever value sits at that intersection.
Key Rule: For a 1D column, only row_num is needed: =INDEX(C2:C10, 3). For a 2D table, specify row and col: =INDEX(A2:D10, 3, 2).
Pro Tip: Notice how INDEX and MATCH are perfect partners: MATCH finds the coordinates, and INDEX fetches the value!
Mistake to Avoid: Asking for a row or column larger than the provided range triggers a #REF! error.
3 simulator exercises
Pro Intermediate 0/3

INDEX MATCH Combined Lookups

Combine INDEX and MATCH to unlock classic leftward, resilient, and 2D matrix lookups across every Excel version.

=INDEX(return_range, MATCH(key, lookup_range, 0))
Concept: Combining INDEX(return_col, MATCH(key, lookup_col, 0)) works across ALL Excel versions, looks left or right cleanly, and stays immune to inserted columns.
Key Rule: Notice return_range and lookup_range are separate aligned vectors (e.g. A2:A6 and C2:C6).
Pro Tip: You can nest TWO MATCH calls inside 2D INDEX: =INDEX(grid, MATCH(rowKey, rowHdrs, 0), MATCH(colKey, colHdrs, 0))!
Mistake to Avoid: Mismatching row bounds like INDEX(A2:A6, MATCH(key, C1:C6, 0)) shifts returned answers by 1 row.
3 simulator exercises
Pro Intermediate 0/3

INDEX MATCH Real-World Practice

Build resilient lookup pipelines handling errors, reverse extreme lookups, and horizontal grids.

=INDEX(names, MATCH(MAX(scores), scores, 0))
Concept: Pairing statistical functions (MAX/MIN) with MATCH lets INDEX return the LABEL of the top performer ("which rep had the highest revenue?"), not just the number itself.
Key Rule: Find the extreme number with MAX/MIN, locate its row with MATCH, and fetch the name with INDEX!
Pro Tip: Wrap INDEX MATCH with IFERROR(..., "Missing") in production dashboards.
Mistake to Avoid: Using MAX alone gives $42,000 when executives asked "WHICH product earned the most?".
3 simulator exercises
Pro Intermediate 0/3

Index Match Skill Summary

Synthesize approximate brackets, 2D matrices, and extreme lookups using INDEX & MATCH.

=INDEX(grid, MATCH(rKey, rCol, 0), MATCH(cKey, cRow, 0))
Concept: Mastery of INDEX MATCH gives you universal lookup capability across legacy and modern Excel workbooks: exact left/right, 2D intersections, ascending/descending approximate matches, and MAX/MIN identification.
Key Rule: Separate row matching and column matching carefully before plugging both into INDEX(matrix, r, c).
Pro Tip: Double-check table borders so header rows/columns align 1:1 with the data body rectangle.
Mistake to Avoid: Including corner cell A1 inside either lookup header vector offsets every coordinate by +1!
3 simulator exercises

Text Parsing & Search

String Extraction

Extract SKUs, domains, codes, and delimiters with LEN, LEFT, RIGHT, MID, FIND, SEARCH, TEXTBEFORE, and TEXTAFTER.

7 lessons · 21 exercises
Pro Beginner 0/3

LEN, LEFT, and RIGHT Text Extraction

Measure string character counts with LEN and slice fixed-length prefixes or suffixes using LEFT and RIGHT.

=LEN(text) | =LEFT(text, num_chars) | =RIGHT(text, num_chars)
Concept: Text IDs like "US-88412" or "2026-INV" follow predictable prefix/suffix layouts. LEN measures exact character count (including spaces), LEFT pulls the opening N characters, and RIGHT pulls the trailing N characters.
Key Rule: For fixed prefixes/suffixes use LEFT/RIGHT directly; for dynamic prefixes combine LEN with RIGHT/LEFT subtraction.
Pro Tip: Notice LEN("hello ") returns 6—extra trailing spaces show up immediately via LEN!
Mistake to Avoid: Expecting RIGHT("SKU-401", 3) to return numbers as numeric automatically—text slices stay text strings.
3 simulator exercises
Pro Beginner 0/3

Middle Slicing with MID

Extract substrings from any interior start position and length using the MID function.

=MID(text, start_num, num_chars)
Concept: When data lives inside the middle of a structured code (like year "2026" inside "PO-2026-89B"), LEFT and RIGHT cannot reach it cleanly—MID starts at start_num and reads num_chars forward.
Key Rule: Remember start_num is 1-based: character 1 is the very first character on the left.
Pro Tip: Count hyphen positions carefully: in "PO-2026-89", P=1, O=2, -=3, so 2 starts at position 4!
Mistake to Avoid: Passing end_num instead of num_chars—the 3rd argument is how many characters to take, NOT the ending index.
3 simulator exercises
Pro Intermediate 0/3

LEN, LEFT, RIGHT & MID Combo Practice

Combine all four slicing tools to validate code lengths, strip wrappers, and reassemble tokens.

=IF(LEN(A2)=8, "Valid", "Error") | =LEFT(A2,2)&"-"&RIGHT(A2,4)
Concept: Text parsing tasks rarely use just one function—analysts combine LEFT, MID, RIGHT, LEN, and IF together to parse, verify, and normalize codes.
Key Rule: Combine LEN with IF to validate SKU length formatting before parsing.
Pro Tip: Wrap messy text with TRIM() if leading/trailing spaces might skew LEN or RIGHT.
Mistake to Avoid: Hardcoding character lengths across variable-length text strings causes truncated tails.
3 simulator exercises
Pro Intermediate 0/3

Mastering Wildcard Characters (* and ?)

Choose precisely between multi-character * and single-character ? wildcards across SUMIF, COUNTIF, and SEARCH.

* = any sequence | ? = single char | ~? = literal question mark
Concept: Excel wildcards give you regex-like simplicity: * stands for zero or more of ANY character, ? stands for strictly ONE character, and ~ escapes literal * or ? characters.
Key Rule: Use "?" when position/length matters (e.g., "A?C" matches ABC but not ABBC) and "*" when length varies.
Pro Tip: Remember exact-match VLOOKUP/MATCH/XLOOKUP(mode 2) also understand * and ? natively!
Mistake to Avoid: Using * instead of ? when validating strict fixed-width codes lets malformed extra characters slip through.
3 simulator exercises
Pro Intermediate 0/3

Case-Sensitive Substring Locating with FIND

Locate delimiter indexes dynamically using FIND and feed them into LEFT or MID for variable-length parsing.

=FIND(find_text, within_text, [start_num])
Concept: FIND locates the exact 1-based index of a substring (for example, "@" inside an email address). Note two key traits: FIND is strictly CASE-SENSITIVE and does NOT accept wildcards.
Key Rule: Pair FIND with LEFT to extract everything before a delimiter: =LEFT(A2, FIND("@", A2)-1).
Pro Tip: Subtract 1 from FIND’s position inside LEFT so you don’t include the delimiter itself!
Mistake to Avoid: Checking FIND("PRO", A2) when text says "Pro"—case mismatch causes FIND to throw #VALUE!.
3 simulator exercises
Pro Intermediate 0/3

Case-Insensitive & Wildcard SEARCH

Contrast SEARCH vs FIND: match mixed-case text reliably and search using wildcards (* and ?).

=SEARCH(find_text, within_text, [start_num])
Concept: SEARCH works just like FIND—returning the 1-based position of a substring—except SEARCH ignores UPPER/lowercase differences AND supports * and ? wildcards!
Key Rule: Combine ISNUMBER(SEARCH("urgent", A2)) to build case-insensitive keyword detectors.
Pro Tip: Prefer SEARCH over FIND unless uppercase vs lowercase specifically changes meaning (e.g. Case-Sensitive Promo Codes).
Mistake to Avoid: Expecting SEARCH to fail on lowercase "cloud" inside "CLOUD Plan"—unlike FIND, SEARCH matches cleanly!
3 simulator exercises
Pro Intermediate 0/3

Modern Parsing with TEXTBEFORE & TEXTAFTER

Split paths, emails, and delimited strings cleanly without LEFT/FIND math using Excel 365 TEXTBEFORE & TEXTAFTER.

=TEXTBEFORE(text, delimiter, [instance_num]) | =TEXTAFTER(text, delimiter, [instance_num])
Concept: TEXTBEFORE and TEXTAFTER modernize text parsing: pass text, delimiter, and optional instance_num (positive from left, negative from right!) without subtracting 1 or calculating LEN.
Key Rule: Use instance_num = -1 on file paths or URLs (=TEXTAFTER(url, "/", -1)) to grab the final filename instantly!
Pro Tip: Nest TEXTAFTER inside TEXTBEFORE to pull the middle chunk between delimiters 1 and 2.
Mistake to Avoid: Passing instance_num 0 causes #VALUE!—always count from 1 (left) or -1 (right).
3 simulator exercises

Text Cleanup & Formatting

Data Hygiene

Standardize messy CRM exports using CONCAT, TEXTJOIN, UPPER, LOWER, PROPER, and SUBSTITUTE.

4 lessons · 12 exercises
Pro Beginner 0/3

Combining Strings with CONCAT & &

Merge first/last names, street fields, and ranges dynamically using CONCAT and ampersand (&).

=CONCAT(text1, [text2], ...) OR =A2 & " " & B2
Concept: CONCAT replaces legacy CONCATENATE and lets you combine individual cells, literal spaces (" "), or entire cell ranges in one string.
Key Rule: Remember to insert explicit space strings " " between name columns; Excel never adds spaces automatically.
Pro Tip: Notice CONCAT handles ranges like =CONCAT(A2:C2), whereas the older CONCATENATE required separate commas.
Mistake to Avoid: Writing =CONCAT(A2, B2) produces "JaneDoe" glued together without a space.
3 simulator exercises
Pro Intermediate 0/3

Delimiter Lists with TEXTJOIN

Join entire ranges with automatic delimiters while optionally skipping blank cells using TEXTJOIN.

=TEXTJOIN(delimiter, ignore_empty, text1, ...)
Concept: CONCAT runs values together with no delimiter; TEXTJOIN inserts your chosen delimiter (", ", " | ", "-") automatically between items AND can skip blank cells when ignore_empty = TRUE!
Key Rule: Pass TRUE as arg 2 to skip blank rows so you never end up with ugly trailing commas like "Red, , Blue".
Pro Tip: TEXTJOIN(", ", TRUE, B2:B10) turns an entire vertical tag column into a clean CSV list.
Mistake to Avoid: Passing FALSE in arg 2 when data contains gaps produces repeated delimiters ("A, , B").
3 simulator exercises
Pro Beginner 0/3

Case Standardization: UPPER, LOWER & PROPER

Clean inconsistent casing from imported CRM forms using UPPER, LOWER, PROPER, and TRIM.

=UPPER(text) | =LOWER(text) | =PROPER(text)
Concept: External form imports often arrive as "jOHN dOE" or "usa-east". UPPER switches to ALL CAPS, LOWER converts to lowercase (great for emails), and PROPER capitalizes the first letter of each word (Title Case).
Key Rule: Wrap messy text inside TRIM() too (=PROPER(TRIM(A2))) to remove double or trailing spaces in the same pass.
Pro Tip: State abbreviations (NY, TX) and SKU prefixes belong in UPPER; emails belong in LOWER; names belong in PROPER.
Mistake to Avoid: Applying PROPER to "USA" turns it into "Usa"—reserve PROPER for names/titles and UPPER for codes.
3 simulator exercises
Pro Intermediate 0/3

Find & Replace in Formulas with SUBSTITUTE

Swap characters, strip formatting dashes/spaces, or target specific instances with SUBSTITUTE.

=SUBSTITUTE(text, old_text, new_text, [instance_num])
Concept: SUBSTITUTE replaces matching case-sensitive text inside a formula without mutating the source cell. An optional 4th instance_num argument lets you replace ONLY the 1st, 2nd, etc. match!
Key Rule: To delete a character everywhere, pass "" as new_text: =SUBSTITUTE(A2, "-", "").
Pro Tip: Counting words? Notice LEN(TRIM(A2)) - LEN(SUBSTITUTE(TRIM(A2), " ", "")) + 1 measures spaces removed!
Mistake to Avoid: Expecting SUBSTITUTE to be case-insensitive—remember SUBSTITUTE matches exact casing of old_text.
3 simulator exercises

Statistical & Rounding Functions

Analytics

Summarize distributions and pricing tiers using AVERAGE, MIN/MAX, LARGE/SMALL, MEDIAN, MODE, FLOOR/CEILING, and ODD/EVEN.

7 lessons · 21 exercises
Pro Beginner 0/3

Calculating Means with AVERAGE & AVERAGEIF

Compute arithmetic means cleanly while seeing how AVERAGE skips blanks vs zeroes, plus conditional AVERAGEIF.

=AVERAGE(range) | =AVERAGEIF(crit_range, criteria, [avg_range])
Concept: AVERAGE sums numeric values and divides by COUNT (how many numbers exist). Critically, empty cells and text notes do NOT count toward the denominator—unlike cells containing 0.
Key Rule: Use AVERAGEIF to calculate mean score or deal size for one category without helper tabs.
Pro Tip: If a range contains zero numeric values, AVERAGE returns #DIV/0!—wrap with IFERROR if datasets can be empty.
Mistake to Avoid: Typing 0 for "not yet graded" pulls everyone’s average down; leave ungraded cells empty.
3 simulator exercises
Pro Intermediate 0/3

Extremes & Ranks: MIN, MAX, LARGE & SMALL

Identify top/bottom bounds with MIN & MAX, or retrieve exact k-th runner-up values with LARGE and SMALL.

=MIN(rng) | =MAX(rng) | =LARGE(rng, k) | =SMALL(rng, k)
Concept: MIN and MAX return the #1 smallest and largest numbers in a dataset. When you need the 2nd highest score, top-3 threshold, or 3rd fastest time, use LARGE(range, k) and SMALL(range, k).
Key Rule: Notice LARGE(rng, 1) equals MAX(rng), and SMALL(rng, 1) equals MIN(rng).
Pro Tip: Pair LARGE(rng, 3) inside SUMIF/SUMIFS (>=$E$2) to filter or sum the top 3 performers automatically!
Mistake to Avoid: Asking for k greater than the numeric count in the range returns #NUM!.
3 simulator exercises
Pro Intermediate 0/3

MIN, MAX, LARGE & SMALL Applied Practice

Combine top-k functions inside SUM, AVERAGE, and lookup filters to evaluate elite cohorts.

=SUMIF(rng, ">="&LARGE(rng, 3))
Concept: Analytical reports often combine ranked thresholds: "What is the average of our Top 3 months?" or "Sum everything at or above the 3rd largest score."
Key Rule: Remember ">= "&LARGE(B2:B7, 2) averages/sums the top two benchmarks dynamically.
Pro Tip: Double-check whether duplicate runner-up values appear when ranking top cohorts.
Mistake to Avoid: Using SMALL(range, 2) expecting 2nd largest instead of 2nd smallest.
3 simulator exercises
Pro Intermediate 0/3

Rounding to Multiples: FLOOR & CEILING

Snap prices, billing minutes, and pallet packs to exact increments using FLOOR and CEILING.

=FLOOR(number, significance) | =CEILING(number, significance)
Concept: Regular ROUND snaps to decimal digits (0.01), whereas FLOOR and CEILING round numbers DOWN or UP to the nearest multiple of any custom step (e.g., nearest 5, nearest 10, nearest 0.25).
Key Rule: Use CEILING(minutes, 15) when billing minimum 15-minute blocks; use FLOOR(price, 5) to enforce lower multiples.
Pro Tip: FLOOR.MATH and CEILING.MATH work cleanly with default significance 1 too.
Mistake to Avoid: Passing decimal digits like 2 into FLOOR(48.7, 2) rounds to even multiples of 2—NOT 2 decimal places!
3 simulator exercises
Pro Intermediate 0/3

Rounding Parity with ODD & EVEN

Round numbers away from zero cleanly to the next odd or even integer using ODD and EVEN.

=ODD(number) | =EVEN(number)
Concept: ODD rounds any value away from zero to the nearest odd integer (3.1 -> 5; -1.2 -> -3); EVEN rounds away from zero to the nearest even integer (2.1 -> 4; -2.1 -> -4).
Key Rule: Notice both functions ALWAYS move AWAY from zero (larger magnitude), even on decimals like 2.01 -> 4.
Pro Tip: EVEN helps round shipping carton counts up to paired boxes; ODD helps tie-breaker panel counts avoid even ties.
Mistake to Avoid: Expecting EVEN(2.1) to round down to 2—it always rounds AWAY from zero to 4!
3 simulator exercises
Pro Intermediate 0/3

Finding the True Middle with MEDIAN

Protect compensation and latency reporting against extreme outliers using MEDIAN.

=MEDIAN(number1, [number2], ...)
Concept: AVERAGE gets heavily skewed by one extreme executive salary or timeout spike. MEDIAN returns the middle value after sorting numerically (or the average of the middle two values when count is even).
Key Rule: Comparing AVERAGE vs MEDIAN immediately reveals whether high-end outliers skew your dataset.
Pro Tip: Home prices, salaries, and API response times should almost always pair MEDIAN alongside AVERAGE.
Mistake to Avoid: Expecting MEDIAN to require pre-sorting your spreadsheet—MEDIAN sorts values internally.
3 simulator exercises
Pro Intermediate 0/3

Most Common Value with MODE

Identify the most frequently occurring numeric measurement, rating, or price tier via MODE.

=MODE(number1, [number2], ...) | =MODE.SNGL(range)
Concept: MODE (and MODE.SNGL) finds which numeric value appears most frequently in a range. If every number appears only once, Excel returns #N/A.
Key Rule: Use MODE to spot the single most popular price point, shoe size, or star rating across survey responses.
Pro Tip: Wrap MODE in IFERROR(MODE(rng), "No Repeat") whenever datasets might have all distinct values.
Mistake to Avoid: Passing text labels into MODE fails—MODE strictly analyzes numeric occurrences.
3 simulator exercises

Dates, Timelines & Tenures

Date Math

Calculate project durations, SLA deadlines, and employee tenure with DATE, TODAY, DAYS, and DATEDIF.

5 lessons · 15 exercises
Pro Beginner 0/3

Constructing & Parsing Dates with DATE, YEAR, MONTH, DAY

Build valid calendar dates safely from separate year/month/day columns and extract date components cleanly.

=DATE(year, month, day) | =YEAR(date) | =MONTH(date) | =DAY(date)
Concept: Separating Year, Month, and Day columns makes formulas clumsy; DATE(year, month, day) combines three integers into a real Excel calendar date—even gracefully rolling over months like Month 13 into January next year!
Key Rule: Use DATE(YEAR(A2), MONTH(A2)+3, DAY(A2)) to shift any contract renewal forward by 3 months!
Pro Tip: Passing day 0 in DATE(2026, 4, 0) cleanly gives the last day of March (2026-03-31)!
Mistake to Avoid: Concatenating "2026"&"-"&"3"&"-"&"5" builds text, not a real sortable/calculable date value.
3 simulator exercises
Pro Beginner 0/3

Live System Dates with TODAY

Track live aging, due dates, and overdue flags automatically using Excel’s volatile TODAY() function.

=TODAY() | =TODAY()+30 | =TODAY()-A2
Concept: TODAY() takes zero arguments inside its parentheses and dynamically updates to the current system calendar date whenever the workbook recalculates.
Key Rule: Subtract an earlier invoice date from TODAY() to calculate days overdue continuously.
Pro Tip: Notice TODAY() always keeps empty parentheses ()—NEVER pass arguments inside TODAY().
Mistake to Avoid: Typing a hardcoded today date like "2026-11-17" makes aging trackers stale tomorrow.
3 simulator exercises
Pro Intermediate 0/3

Elapsed Tenure & Intervals with DATEDIF

Measure completed years ("Y"), completed months ("M"), total days ("D"), and remainder months ("YM") using DATEDIF.

=DATEDIF(start_date, end_date, "unit")
Concept: DATEDIF calculates exact calendar differences between start_date and end_date using unit codes: "Y" (completed years), "M" (completed months), "D" (days), "YM" (months beyond completed years), "MD" (days beyond completed months).
Key Rule: start_date ALWAYS comes FIRST and end_date SECOND! Reversing them triggers #NUM!.
Pro Tip: Combine "Y" and "YM" in text to print readable tenure like "3 yrs, 2 mos"!
Mistake to Avoid: Swapping start_date and end_date (unlike DAYS() which puts end_date first!) produces #NUM!.
3 simulator exercises
Pro Beginner 0/3

Exact Day Durations with DAYS

Subtract dates reliably using DAYS(end_date, start_date) for shipping durations, cycle times, and billing.

=DAYS(end_date, start_date)
Concept: DAYS returns the exact number of calendar days between two dates—even if dates arrived as text strings—and accepts end_date FIRST and start_date SECOND.
Key Rule: Watch the order carefully: DAYS takes (later_date, earlier_date) to give a positive count!
Pro Tip: Notice end - start subtraction (=B2-A2) gives the exact same integer on valid date cells.
Mistake to Avoid: Passing start_date first inside DAYS returns a negative day count (e.g., -14 instead of 14).
3 simulator exercises
Pro Intermediate 0/3

DATE Functions Skill Summary

Combine DATE, YEAR/MONTH, DAYS, and DATEDIF to power billing schedules, renewals, and project rollups.

=DATE(YEAR(d)+1, MONTH(d), DAY(d)) | =DAYS(d2, d1)
Concept: Date automation combines assembling dynamic deadlines (DATE), pulling calendar buckets (YEAR/MONTH), and measuring elapsed time (DAYS / DATEDIF).
Key Rule: Compute annual renewals by adding +1 to YEAR(A2) inside DATE()—preserving month and day cleanly.
Pro Tip: Always remember DAYS takes (end, start) while DATEDIF takes (start, end, unit)!
Mistake to Avoid: Adding +365 days for annual contract renewals breaks on leap years—use DATE(YEAR+1, MONTH, DAY).
3 simulator exercises

Filters & PivotTable Summaries

Tables & BI

Slice tabular datasets dynamically with Table Filters and multi-dimensional PivotTables.

4 lessons · 12 exercises
Pro Beginner 0/3

Interactive Filtering with Table Filters

Enable AutoFilter dropdowns from Data → Filter (or Ctrl+Shift+L) to isolate subsets without deleting rows.

Data tab → ▽ Filter (or Ctrl+Shift+L) → click header ▾ arrow → select criteria
Concept: Table Filters add dropdown toggles (▾) to every header cell so you can hide non-matching records temporarily without touching formulas or deleting source rows.
Key Rule: Activate Filter first, click the header arrow of the column you want to narrow, uncheck unwanted items, and press OK.
Pro Tip: Press Ctrl+Shift+L anytime inside a table to toggle filter arrows on or off instantaneously.
Mistake to Avoid: Deleting rows manually instead of filtering destroys permanent historical records.
3 simulator exercises
Pro Intermediate 0/3

PivotTables 1: Grouping & Summarizing Data

Build automated category rollups with Insert → PivotTable using Row Fields, Values, and aggregations.

Insert tab → 📊 PivotTable → set Rows Field + Values Field + Summarize By → Build PivotTable
Concept: A PivotTable summarizes hundreds of raw rows into a dynamic summary table in seconds—grouping distinct labels into Rows and aggregating metrics with SUM, COUNT, or AVERAGE without writing SUMIFS formulas.
Key Rule: Assign categorical labels (Region, Category, Rep) to Rows and numeric metrics (Revenue, Hours) to Values.
Pro Tip: Switch "Summarize Values By" from SUM to COUNT whenever you want quick frequency headcounts.
Mistake to Avoid: Placing unique IDs (Invoice #) in Rows creates zero grouping—group by repeated category columns!
3 simulator exercises
Pro Intermediate 0/3

PivotTables 2: 2D Matrices & Extreme Analytics

Add a Columns field for 2D cross-tabulated PivotTables and extract category MAX/MIN bounds.

Insert → PivotTable → set Rows + Columns + Values + Aggregation
Concept: Adding a Columns field alongside Rows turns a PivotTable into a 2D cross-tab matrix (for example, Category down the left and Region across the top)—plus MAX/MIN metrics identify peak outliers per group.
Key Rule: Assign your longer distinct list (Products/Reps) to Rows and short dimensions (Quarter/Tier) to Columns.
Pro Tip: Always leave Columns blank when you only want a clean 1D list—add Columns specifically for cross-tabs.
Mistake to Avoid: Putting a numeric Amount column into Rows/Columns explodes the matrix into dozens of columns.
3 simulator exercises
Pro Intermediate 0/3

Tables Skill Summary (Clean, Filter & Pivot)

Practice the complete tabular workflow: deduplicate records, apply filters, and generate a Pivot summary.

1. Data → Remove Duplicates | 2. Data → Filter | 3. Insert → PivotTable
Concept: Clean reporting requires three ordered steps: remove duplicate rows first (Data → Remove Duplicates), filter or inspect slices carefully (Data → Filter), and summarize clean records via PivotTable.
Key Rule: Always deduplicate BEFORE building totals or PivotTables so duplicate imports never double-count revenue!
Pro Tip: Notice Remove Duplicates preserves the first matching row and safely trims redundant copies below.
Mistake to Avoid: Building a PivotTable before removing duplicate export rows silently inflates sums.
3 simulator exercises

Dynamic Array Formulas

Excel 365

Build spilling live extracts and deduplicated lists automatically with UNIQUE and FILTER.

2 lessons · 6 exercises
Pro Intermediate 0/3

Dynamic Deduplication with UNIQUE

Extract live distinct lists that spill automatically into neighboring rows using UNIQUE in Excel 365.

=UNIQUE(array, [by_col], [exactly_once])
Concept: Instead of manually copying a column and running Remove Duplicates, Excel 365’s UNIQUE(array) dynamically returns every distinct entry—and automatically "spills" downstream into empty cells below!
Key Rule: Leave enough blank cells below your UNIQUE cell so the array can spill freely without triggering #SPILL!.
Pro Tip: Wrap UNIQUE inside COUNTA (=COUNTA(UNIQUE(A2:A20))) to count distinct customers or cities in a single cell!
Mistake to Avoid: Typing notes directly below a dynamic array formula blocks spilling and displays #SPILL!.
3 simulator exercises
Pro Advanced 0/3

Dynamic Record Extraction with FILTER

Build live formula-driven filtered lists with Excel 365 FILTER, including AND (*) compound criteria and fallbacks.

=FILTER(A2:B8, B2:B8="East", "No Matches")
Concept: FILTER(array, include, [if_empty]) dynamically returns all rows where an include boolean expression evaluates TRUE—updating instantaneously as source cells change!
Key Rule: For multi-condition AND filtering inside FILTER, multiply boolean parentheses: (B2:B7="East")*(C2:C7>500).
Pro Tip: Wrap FILTER with SUM or AVERAGE (=SUM(FILTER(C2:C7, B2:B7="East"))) for flexible dynamic aggregations.
Mistake to Avoid: Trying to pass AND(B2:B7="East", C2:C7>500) collapses all rows to 1 boolean! Multiply conditions with * instead.
3 simulator exercises

Worksheets & Conditional Formatting

Workbook Polish

Organize multi-tab workbooks, link sheets with Sheet!A1 refs, and highlight exceptions visually.

2 lessons · 6 exercises
Pro Intermediate 0/3

Multi-Tab Worksheets & Cross-Sheet References

Add and rename bottom worksheet tabs cleanly and write formulas pulling data across tabs with Sheet!Cell syntax.

=SUM(Orders!B2:B10) | =VLOOKUP(A2, Rates!A2:B5, 2, FALSE)
Concept: Clean workbooks separate raw tables, rate cards, and executive summaries across tabs. Adding a sheet (+), renaming tabs cleanly (double-click sheet tab), and referencing tabs with exclamation syntax (Rates!B2) connects data reliably.
Key Rule: Wrap sheet names containing spaces in single quotes: 'Q1 Sales'!B2.
Pro Tip: Double-click any bottom sheet tab in Deskademy Excel to rename it quickly.
Mistake to Avoid: Forgetting the exclamation mark "!" between sheet name and cell address returns #NAME?.
3 simulator exercises
Pro Intermediate 0/3

Visual Exception Rules with Conditional Formatting

Highlight high-value metrics, alert keywords, and duplicate IDs automatically via Conditional Formatting rules.

Home tab → 🎨 Conditional Formatting → set Applies-to Range + Rule Type + Value + Fill Style
Concept: Conditional Formatting applies automatic colors when cell values satisfy a dynamic rule (> threshold, contains text, or duplicate values)—making anomalies leap off the screen without reading every row.
Key Rule: Scope your range carefully (e.g., B2:B6 instead of entire sheet) so headers aren’t highlighted.
Pro Tip: Check Home → Conditional Formatting → Duplicate Values right before sending client rosters to catch accidental double entries.
Mistake to Avoid: Applying numeric > thresholds over text columns does nothing—target the numeric range cleanly.
3 simulator exercises

Speed & Formatting Shortcuts

Keyboard Flow

Replace slow mouse clicks with Bold, Number formats, Cut/Copy/Paste, Fill Down (Ctrl+D), Fill Right (Ctrl+R), and Data shortcuts.

6 lessons · 18 exercises
Free Beginner 0/3

Essential Workbook Productivity Shortcuts

Eliminate repetitive mouse travel using Ctrl+S (Save), Ctrl+Z (Undo), and F2 (Edit Active Cell).

F2 = Edit cell in place | Ctrl+Z = Undo | Ctrl+S = Save workbook
Concept: Every second moving from keyboard to mouse slows spreadsheet analysis. Mastering core workbook shortcuts (F2 to edit inside a cell without erasing it, Ctrl+Z to undo mistakes, Ctrl+S to save) builds automatic muscle memory.
Key Rule: Press F2 when you want to edit an existing formula instead of typing over it from scratch!
Pro Tip: Notice typing directly on a selected cell replaces its contents, whereas pressing F2 puts the cursor inside at the end.
Mistake to Avoid: Starting to type adjustments on a selected formula cell without pressing F2 first wipes the whole formula!
3 simulator exercises Open lesson in simulator
Pro Beginner 0/3

Text Formatting Shortcuts (Ctrl+B, Ctrl+I, Ctrl+U)

Format headers, notes, and KPI callouts instantly from the keyboard with Bold, Italic, and Underline shortcuts.

Ctrl+B = Bold | Ctrl+I = Italic | Ctrl+U = Underline
Concept: Visual hierarchy separates summary totals and headers from raw data. Rather than hunting Home ribbon icons per cell, select your target cell or range and toggle Bold (Ctrl+B), Italic (Ctrl+I), or Underline (Ctrl+U).
Key Rule: Select a whole header row or range BEFORE hitting Ctrl+B to style multiple cells in one keystroke!
Pro Tip: Pressing the same shortcut a second time toggles that formatting back off.
Mistake to Avoid: Leaving grid totals unbolded makes executives hunt for bottom-line figures.
3 simulator exercises
Pro Beginner 0/3

Number Formatting Shortcuts (Currency, %, Comma)

Format entire numeric ranges instantly with Ctrl+Shift+$ (Currency), Ctrl+Shift+% (Percent), and Ctrl+Shift+! (Number/Comma).

Ctrl+Shift+$ = Currency | Ctrl+Shift+% = Percentage | Ctrl+Shift+! = Comma Number
Concept: Raw spreadsheet floats like 0.245 or 18400 look ambiguous until formatted cleanly as 24.5% or $18,400.00. Three Shift+Number shortcuts handle 95% of corporate formatting work!
Key Rule: Format ranges in bulk by dragging B2:B6 first, then pressing Ctrl+Shift+$ once.
Pro Tip: Notice percentages live as decimals underneath (0.15 = 15.0%): never type 15 and then apply % (that becomes 1500%!).
Mistake to Avoid: Typing "$" manually in front of a number turns it into un-summable text—always enter numbers and apply Currency formatting!
3 simulator exercises
Pro Intermediate 0/3

Rapid Copying with Fill Down (Ctrl+D) & Fill Right (Ctrl+R)

Replicate formulas and patterns across rows and columns instantly while relative cell references shift automatically.

Ctrl+D = Fill Down selected range | Ctrl+R = Fill Right selected range
Concept: Never retype the same row formula 10 times! Select a starting formula cell PLUS empty cells directly below it (C2:C5) and press Ctrl+D (Fill Down)—Excel copies the formula down and shifts relative row references automatically! Ctrl+R does the exact same thing rightward across columns.
Key Rule: Lock constants with $ ($F$1) BEFORE filling down with Ctrl+D so tax/multiplier refs stay pinned!
Pro Tip: You can also select ONLY the cell directly below a formula and press Ctrl+D to copy down 1 row.
Mistake to Avoid: Highlighting ONLY empty cells C3:C5 without including the top source cell C2 gives nothing to copy down.
3 simulator exercises
Pro Beginner 0/3

Cut, Copy & Paste Workflows (Ctrl+X, Ctrl+C, Ctrl+V)

Relocate misplaced figures via Cut+Paste (Ctrl+X -> Ctrl+V) and duplicate reusable templates with Copy+Paste.

Ctrl+C = Copy | Ctrl+X = Cut (Move) | Ctrl+V = Paste
Concept: Ctrl+C copies a cell’s formula/value + formatting while leaving the original untouched; Ctrl+X cuts (moves) an entry completely from source to destination cleanly.
Key Rule: Always double-check whether you want to MOVE data (Ctrl+X -> clears source) or DUPLICATE it (Ctrl+C).
Pro Tip: Notice Cut + Paste is cleaner than Delete + Retype because it preserves cell formatting (like Currency $)!
Mistake to Avoid: Using Ctrl+C when moving a misplaced figure leaves duplicate phantom numbers behind.
3 simulator exercises
Pro Intermediate 0/3

Data Toolbar & Keyboard Workflows

Combine AutoSum (Alt+=), AutoFilter Toggle (Ctrl+Shift+L), and Sort A→Z rapidly on structured tables.

Alt+= (AutoSum) | Ctrl+Shift+L (Toggle Filter) | Data → Remove Duplicates
Concept: Data workflows blend keyboard triggers and quick ribbon actions: Alt+= totals numerical blocks, Ctrl+Shift+L activates table filtering, and Sort A→Z organizes labels without breaking row integrity.
Key Rule: Keep your active cell inside the target table before pressing Ctrl+Shift+L or clicking Sort/Remove Duplicates!
Pro Tip: Notice Excel sorts entire linked rows together cleanly when you click a single cell inside the column to sort by.
Mistake to Avoid: Highlighting only one single column before sorting breaks alignment between Names and Numbers!
3 simulator exercises

Rapid Range & Table Selection

Grid Mastery

Jump across large tables with Ctrl+Arrows, select contiguous ranges via Ctrl+Shift+Arrows, and select rows/columns instantly.

7 lessons · 21 exercises
Pro Beginner 0/3

Fast Grid Jumping with Arrow & Jump Keys

Navigate tables rapidly with Ctrl+Arrow jumps, Home, Ctrl+Home, and Ctrl+End instead of dragging scrollbars.

Ctrl+Arrow = Jump to data edge | Home = Col A | Ctrl+Home = A1 | Ctrl+End = Last populated cell
Concept: Holding Ctrl while pressing an Arrow key rockets your cursor to the boundary of the current contiguous data region! Ctrl+Home jumps directly to A1; Ctrl+End jumps to the bottom-right corner of populated cells.
Key Rule: Check the Name Box at the top-left of the formula bar to confirm exact arrival coordinates instantly.
Pro Tip: If a column has a blank gap inside it, Ctrl+Down stops right before the gap—an easy visual way to spot missing entries!
Mistake to Avoid: Holding Down-Arrow repeatedly across thousands of rows wastes minutes when one Ctrl+Down jump lands right on the footer.
3 simulator exercises
Pro Beginner 0/3

Cell Selection Shortcuts (Shift + Arrows)

Highlight exact multi-cell ranges cleanly from the keyboard using Shift + Arrow keys.

Shift+ArrowRight / Shift+ArrowDown / Shift+ArrowUp / Shift+ArrowLeft
Concept: Holding Shift while tapping any Arrow key extends your selection one cell at a time—allowing exact range highlights for formatting, clearing, or Fill Down without mouse slip-ups.
Key Rule: Hold Shift, tap Right or Down cleanly until the Name Box confirms your range bounds.
Pro Tip: Notice Status Bar bottom-right automatically computes live Sum, Average, and Count for any highlighted numeric selection!
Mistake to Avoid: Forgetting to hold Shift while arrowing moves only the active single cell instead of stretching a box.
3 simulator exercises
Pro Intermediate 0/3

Larger Cell Selections (Ctrl+Shift+Arrow)

Highlight whole columns, rows, and multi-column datasets in one keystroke with Ctrl+Shift+Arrow.

Ctrl+Shift+ArrowDown | Ctrl+Shift+ArrowRight | Ctrl+A (Whole Sheet/Table)
Concept: Combining Ctrl (jump to data boundary) + Shift (stretch selection) produces the ultimate selection combo: Ctrl+Shift+Down selects from your cursor down to the end of the column instantaneously!
Key Rule: Start at top-left (e.g. A1 or B2), tap Ctrl+Shift+Right then Ctrl+Shift+Down to highlight any whole table fast!
Pro Tip: Notice how pairing Ctrl+Shift+Down with Ctrl+Shift+$ formats hundreds of price rows in 2 seconds.
Mistake to Avoid: Holding Shift without Ctrl selects 1 row; holding Ctrl without Shift jumps without highlighting!
3 simulator exercises
Pro Beginner 0/3

Row & Column Selection Shortcuts (Shift+Space, Ctrl+Space)

Highlight full worksheet rows via Shift+Space, full columns via Ctrl+Space, and entire sheets via Ctrl+A.

Shift+Space = Entire Row | Ctrl+Space = Entire Column | Ctrl+A = Select All
Concept: To insert, delete, hide, or format an entire row or column quickly without clicking header strips: press Shift+Space for the full active ROW, Ctrl+Space for the full active COLUMN, and Ctrl+A to select everything!
Key Rule: Memory trick: Shift + Space = horizontal (wide Shift key selects wide Row); Ctrl + Space = vertical Column!
Pro Tip: Notice pressing Ctrl+Space while inside a column then hitting Ctrl+Shift+$ formats every number in that whole column!
Mistake to Avoid: Mixing up Shift+Space and Ctrl+Space selects a horizontal row when you wanted a vertical column.
3 simulator exercises
Pro Intermediate 0/3

Cell Selection Shortcuts 2 (Format + Select Flow)

Combine fast row/column selections with formatting actions like Bold headers and Currency columns.

Shift+Space -> Ctrl+B | Ctrl+Space -> Ctrl+Shift+$
Concept: Selection shortcuts become superpowers when chained directly into formatting or editing actions—selecting Row 1 and hitting Ctrl+B, or selecting Column C and hitting Ctrl+Shift+$.
Key Rule: Notice formatting actions apply automatically to EVERY cell currently inside your active selection!
Pro Tip: Check the bottom-right Excel Status Bar after every selection to verify Count and Sum silently.
Mistake to Avoid: Applying formatting cell-by-cell when a single selection + shortcut formats an entire column at once.
3 simulator exercises
Pro Intermediate 0/3

Larger Cell Selections 2 (Multi-Col & Edge Combos)

Practice diagonal table grabs, upward block selections, and clearing sub-ranges safely.

Ctrl+Shift+ArrowUp | Name Box: type B2:D6 + Enter | Ctrl+Shift+ArrowDown + Ctrl+Shift+ArrowRight
Concept: Bottom-to-top selection (Ctrl+Shift+ArrowUp) and Name Box jumps (typing B2:E6 + Enter) let analysts highlight mid-sheet data ranges cleanly even in massive workbooks.
Key Rule: Typing exact coordinates (B2:D6) into the top-left Name Box selects exact multi-hundred-row blocks without scrolling!
Pro Tip: Notice clicking the corner Name Box and typing C2:C50 + Enter selects huge ranges with zero mouse scrolling!
Mistake to Avoid: Including header row 1 when you want to clear or format only numeric body values.
3 simulator exercises
Pro Intermediate 0/3

Cell Selection Skill Summary

Combine navigation, selection, and formatting shortcuts fluently in one capstone speed drill.

Ctrl+Arrow + Ctrl+Shift+Arrow + Shift/Ctrl+Space + Ctrl+D / Ctrl+Shift+$
Concept: True Excel speed comes from chaining selection + action effortlessly: jump with Ctrl+Arrows, grab blocks with Ctrl+Shift+Arrows, isolate rows/cols with Spacebars, and apply Fill/Format shortcuts without lifting hands.
Key Rule: Navigate -> Select -> Transform -> Verify.
Pro Tip: Notice every shortcut works consistently across every sheet in a multi-tab workbook.
Mistake to Avoid: Leaving text input mode unfinished before hitting navigation chords—press Enter or Esc first!
3 simulator exercises

Real-World Case Projects & Exam Prep

Capstones

Combine formulas, cleanups, lookups, and summaries in end-to-end Finance, Hospital, Environmental, UFO, Wine, and Interview exams.

6 lessons · 18 exercises
Pro Advanced 0/3

Case Project: Real Estate Portfolio Analysis

Combine capitalization yield math, SUMIFS territory filtering, and tiered IF status flags across property listings.

=SUMIFS(...) | =IF(AND(yield>=0.06, occ>=0.9), "Acquire", "Pass")
Concept: Real estate underwriting combines property-level cap rates (Net Income / Purchase Price), portfolio exposure by neighborhood (SUMIFS), and deal-screening decisions (IF + AND).
Key Rule: Compute annual yield cleanly as Net Annual Rent / Purchase Price, then compare against hurdle rates.
Pro Tip: Group neighborhood totals with SUMIFS before approving capital concentration in a single metro.
Mistake to Avoid: Mixing monthly rent with annual cap-rate hurdles without multiplying by 12 skews yield 12x.
3 simulator exercises
Pro Advanced 0/3

Case Project: Hospital Administration Operations

Audit clinical ward capacity, average triage wait times, and insurance reimbursement lookups.

=AVERAGEIF(...) | =XLOOKUP(...) | =COUNTIFS(...)
Concept: Healthcare ops analysts monitor bed occupancy, triage wait alerts, and insurance billing lookups simultaneously under tight SLA rules.
Key Rule: Use COUNTIFS across Unit + Severity for staffing alerts, and XLOOKUP/VLOOKUP to map insurer codes to co-pay tiers.
Pro Tip: Wrap clinical lookups with IFERROR/XLOOKUP fallback ("Self-Pay Review") so unknown insurance plans get routed safely.
Mistake to Avoid: Including discharged patients in active bed-occupancy counts inflates ward capacity stats.
3 simulator exercises
Pro Advanced 0/3

Case Project: Wine Reviews Executive Dashboard

Build sommelier rating averages, top-tier bottle counts, and Pivot summaries for a vintage catalog.

=AVERAGEIFS(...) | =COUNTIFS(...) | Insert → PivotTable
Concept: Based on real-world hospitality datasets: summarize vintage scores by varietal (AVERAGEIFS), count cellar-worthy 92+ bottles under $40 (COUNTIFS), and build an executive Varietal PivotTable.
Key Rule: Pair quality floor (Score >= 92) with price ceiling (Price <= 40) to find "Best Value" gems via COUNTIFS.
Pro Tip: PivotTables pair effortlessly with review datasets to compare Points vs Total Bottle Inventory by Country.
Mistake to Avoid: Mixing Score column into Price criteria ranges produces silent 0 counts.
3 simulator exercises
Pro Advanced 0/3

Case Project: Environmental Trout Population Study

Analyze stream survey time-series data using DATE parsing, MEDIAN flow rates, and species SUMIFS.

=SUMIFS(...) | =MEDIAN(...) | =DAYS(end, start)
Concept: Environmental monitoring logs track date-stamped field samples across streams and species—requiring date duration checks (DAYS/YEAR), outlier-safe MEDIAN water metrics, and species-level SUMIFS catch totals.
Key Rule: Use MEDIAN on river flow rates to avoid flood-spike bias, and SUMIFS by Stream + Species.
Pro Tip: Pair YEAR() or date comparisons cleanly inside SUMIFS for multi-season environmental comparisons.
Mistake to Avoid: Using AVERAGE on skewed seasonal storm spikes masks typical stream conditions.
3 simulator exercises
Pro Advanced 0/3

Case Project: UFO Sightings Dynamic Analytics

Analyze messy crowdsourced sighting logs using UNIQUE shapes, FILTER extraction, and wildcard text search.

=UNIQUE(rng) | =FILTER(A2:A8, B2:B8="Disc") | =COUNTIF(rng, "*bright*")
Concept: Public sighting reports arrive messy and unstandardized—ideal for Excel 365 UNIQUE shape lists, dynamic FILTER pulls, and case-insensitive wildcard COUNTA/COUNTIF matching.
Key Rule: Use UNIQUE to audit messy category variations ("disc", "triangle", "orb"), then COUNTIF("*keyword*") to scan descriptions.
Pro Tip: Notice COUNTA(UNIQUE(...)) immediately tells investigators how many unique states/shapes appeared.
Mistake to Avoid: Searching free-text descriptions without wildcards (*word*) misses every longer eyewitness sentence.
3 simulator exercises
Pro Advanced 0/3

Case Project: Hiring Exam & MO-200 Prep Challenge

Tackle classic Excel employment assessment & Microsoft MO-200 certification questions under exam realism.

VLOOKUP/XLOOKUP + SUMIFS + TEXTBEFORE/LEFT + IF
Concept: Employer Excel screening tests (and the MO-200 exam) combine multi-function nesting: safe VLOOKUP/XLOOKUP + arithmetic, multi-condition SUMIFS/COUNTIFS, and text/date extraction.
Key Rule: Read exact requirements first: check whether tests ask for dynamic formulas referencing inputs, exact vs approx lookup mode, and error handling.
Pro Tip: Always double-check range alignment and locked $ references before submitting employment test sheets.
Mistake to Avoid: Hardcoding computed numbers or forgetting arg 4 = FALSE in VLOOKUP are the top causes of assessment failure.
3 simulator exercises
Deskademy Pro Required

Free and guest accounts include 6 complete Starter Lessons (18 hands-on exercises). Upgrade to Career Pro ($9/mo) or Bootcamp Pass ($49 once) to unlock all 91 Excel Exercise Lessons (273 exercises), all 50 Practice Library topics, 100 office simulations, and verified CV credentials.

Deskademy Assistant

Thinking…