Excel for Analysis and Decision Models
Build and check Excel workbooks that others can trust, from reviewing an inherited report to a decision model. You work in a real workbook right here in your browser. The data is from Raket Parts, a fictional maker of rocket parts.
You will be able to
- trace a figure back to its source
- spot formulas that hide mistakes
- reconcile two figures that should agree
- build a model someone else can review
Before you trust a workbook
Trace an inherited report to its source, challenge weak controls and log what you find.
Lookups that tell you when they fail
Find matching rows, total them by condition and reconcile to the ledger.
Dates, text and live lists
Clean dates and text, build live exception lists and age what is overdue.
Registers other people can fill in
Tables, dropdown lists and warnings that keep a shared register clean.
PivotTables and summaries
Summaries you can rebuild and reconcile.
Power Query: repeatable data preparation
Prepare data the same way every time.
Reconciliation and control checks
Prove two figures agree, or show where they do not.
Designing an auditable decision model
Inputs, calculations and outputs that others can follow.
Scenarios, sensitivity and what-if
Test how a result moves when the inputs change.
Driver-based forecasts, budgets and capacity models
Build forecasts from the drivers behind them.
Presenting results
Show the answer clearly, with its limits.
Review, hand-over and sharing
Leave a workbook someone else can trust.
Practice assessment
Put the whole Path together on a fresh case.
Excel for Analysis / Module 1
Before you trust a workbook
Teun inherits a cost report built by a colleague who has left. Before signing it, he follows its headline to the sources and records where the workbook could be wrong.
Sections
Excel for Analysis / Module 2
Formulas you can copy
A formula that is right in one cell can be wrong in the next. Bram Wijnstra, Head of Sales, builds price, margin and discount formulas on the July 2025 order lines that stay right when copied, and errors that say what went wrong.
This module builds on module 1. If you are new to this, start with module 1.
Sections
Excel for Analysis / Module 3
Lookups that tell you when they fail
A lookup that silently returns the wrong row is worse than one that fails. Teun Veldhorst needs supplier categories and spend totals he can prove. He learns lookups that show their failures and totals that reconcile to the ledger.
This module builds on module 2. If you are new to this, start with module 2.
Sections
Excel for Analysis / Module 4
Dates, text and live lists
Dates that are really text, labels that hide codes, lists that must update themselves. Teun gets an overdue ageing of 12,377 real invoices that he can refresh in seconds by changing one date.
This module builds on module 3. If you are new to this, start with module 3.
Sections
Excel for Analysis / Module 5
Registers other people can fill in
A register is only useful if everyone fills it in the same way. Mira Dageraad owns the supplier risk register, and she learns to build one that grows, guides and flags. This module uses the supplier register only.
This module builds on module 4. If you are new to this, start with module 4.
Sections
Excel for Analysis / Module 1 / 1.1
1.1 Check Teun’s report against its source
Section 1.1 complete
Next: 1.2 Don’t trust the headline.
Excel for Analysis / Module 1 / 1.4
1.4 Find what hides
Section 1.4 complete
Next: 1.5 Log what you found.
Excel for Analysis / Module 1 / 1.5
1.5 Log what you found
Section 1.5 complete
Next: the module check.
Continue to the module check Recap this section Back to module 1
Excel for Analysis / Module 1 / Check
Module 1 check
Module 1 complete
Next: Module 2, Formulas you can copy.
Excel for Analysis / Module 2 / 2.1
2.1 References that move and references that stay
Section 2.1 complete
Next: 2.2 Names and calculation order.
Excel for Analysis / Module 2 / 2.2
2.2 Names and calculation order
Section 2.2 complete
Next: 2.3 Decisions with IF and IFS.
Excel for Analysis / Module 2 / 2.3
2.3 Decisions with IF and IFS
Section 2.3 complete
Next: 2.4 Errors that tell the truth.
Excel for Analysis / Module 2 / 2.4
2.4 Errors that tell the truth
Section 2.4 complete
Next: 2.5 A calculation you can test.
Excel for Analysis / Module 2 / 2.5
2.5 A calculation you can test
Section 2.5 complete
Next: the module check.
Continue to the module check Recap this section Back to module 2
Excel for Analysis / Module 2 / Check
Module 2 check
Module 2 complete
Next: Module 3, Lookups that tell you when they fail.
Excel for Analysis / Module 3 / 3.1
3.1 What a lookup does with bad keys
Section 3.1 complete
Next: 3.2 XLOOKUP and INDEX with MATCH.
Excel for Analysis / Module 3 / 3.2
3.2 XLOOKUP and INDEX with MATCH
Section 3.2 complete
Next: 3.3 Bands and the boundary.
Excel for Analysis / Module 3 / 3.3
3.3 Bands and the boundary
Section 3.3 complete
Next: 3.4 SUMIFS and COUNTIFS.
Excel for Analysis / Module 3 / 3.4
3.4 SUMIFS and COUNTIFS
Section 3.4 complete
Next: 3.5 Reconcile to the ledger.
Excel for Analysis / Module 3 / 3.5
3.5 Reconcile to the ledger
Section 3.5 complete
Next: the module check.
Continue to the module check Recap this section Back to module 3
Excel for Analysis / Module 3 / Check
Module 3 check
Module 3 complete
Next: Module 4, Dates, text and live lists.
Excel for Analysis / Module 4 / 4.1
4.1 Text that looks like a date
Section 4.1 complete
Next: 4.2 Month ends and working days.
Excel for Analysis / Module 4 / 4.3
4.3 Clean text
Section 4.3 complete
Next: 4.4 Lists that update themselves.
Excel for Analysis / Module 4 / 4.4
4.4 Lists that update themselves
Section 4.4 complete
Next: 4.5 Overdue ageing by bucket.
Excel for Analysis / Module 4 / 4.5
4.5 Overdue ageing by bucket
Section 4.5 complete
Next: the module check.
Continue to the module check Recap this section Back to module 4
Excel for Analysis / Module 4 / Check
Module 4 check
Module 4 complete
Next: Module 5, Registers other people can fill in.
Excel for Analysis / Module 5 / 5.2
5.2 Excel Tables
Section 5.2 complete
Next: 5.3 Validation that guides.
Excel for Analysis / Module 5 / 5.3
5.3 Validation that guides
Section 5.3 complete
Next: 5.4 Flags people notice.
Excel for Analysis / Module 5 / 5.4
5.4 Flags people notice
Section 5.4 complete
Next: 5.5 Build the register.
Excel for Analysis / Module 5 / 5.5
5.5 Build the register
Section 5.5 complete
Next: the module check.
Continue to the module check Recap this section Back to module 5
Excel for Analysis / Module 5 / Check
Module 5 check
Module 5 complete
Next: module 6, PivotTables and summaries, coming soon.
Excel for Analysis / Module 1 / Recap
Recap card
1.1 Check Teun’s report against its source
Check Teun’s report against its source
- Check the report against its source in the workbook.
- One row is one journal line. Check rows and positive amounts.
Syntax
=SUM(source_range)
Example
=ROWS(GL_source!B2:B201)
Common mistake
Trusting net alone. Equal missing debit and credit lines can cancel.
Excel for Analysis / Module 1 / Recap
Recap card
1.2 Don’t trust the headline
Don’t trust the headline
- A typed override breaks the source link.
- A self-comparison can be zero even when the underlying amount is wrong.
Syntax
=SUM(source_range)
Example
=SUM(GL_source!G2:G201)
Common mistake
Accepting PASS as proof. Inspect what the flag actually tests.
Excel for Analysis / Module 1 / Recap
Recap card
1.3 See the formulas
See the formulas
- Show formulas and trace inputs and consequences.
- Evaluate intermediate results and inspect ranges with F2.
Syntax
=SUM(source_range)
Example
=SUM(GL_source!G2:G201)+GL_source!G2
Common mistake
Confusing a link with a correct link. Check the source range and its business meaning.
Excel for Analysis / Module 1 / Recap
Recap card
1.4 Find what hides
Find what hides
- Locate constants, hidden sheets, wrong columns and incomplete ranges.
- Compare formula patterns and independently calculated row values.
Syntax
=SUM(source_range)
Example
=SUM(GL_source!G2:G201)
Common mistake
Treating all constants as faults. Genuine inputs need a source; calculated outputs need intact links.
Excel for Analysis / Module 1 / Recap
Recap card
1.5 Log what you found
Log what you found
- Log cell, risk, independent evidence, impact and fix.
- Repairing the headline alone does not repair the whole workbook.
Syntax
=SUM(source_range)
Example
=SUM(GL_source!G2:G201)-Report!B3
Common mistake
Comparing a report with itself. Use original source evidence and distinguish net from debits.
Excel for Analysis / Module 1 / Recap
Recap card
Module 1: Before you trust a workbook
Check Teun’s report against its source
- Check the report against its source in the workbook.
- One row is one journal line. Check rows and positive amounts.
Syntax
=SUM(source_range)
Example
=ROWS(GL_source!B2:B201)
Common mistake
Trusting net alone. Equal missing debit and credit lines can cancel.
Don’t trust the headline
- A typed override breaks the source link.
- A self-comparison can be zero even when the underlying amount is wrong.
Syntax
=SUM(source_range)
Example
=SUM(GL_source!G2:G201)
Common mistake
Accepting PASS as proof. Inspect what the flag actually tests.
See the formulas
- Show formulas and trace inputs and consequences.
- Evaluate intermediate results and inspect ranges with F2.
Syntax
=SUM(source_range)
Example
=SUM(GL_source!G2:G201)+GL_source!G2
Common mistake
Confusing a link with a correct link. Check the source range and its business meaning.
Find what hides
- Locate constants, hidden sheets, wrong columns and incomplete ranges.
- Compare formula patterns and independently calculated row values.
Syntax
=SUM(source_range)
Example
=SUM(GL_source!G2:G201)
Common mistake
Treating all constants as faults. Genuine inputs need a source; calculated outputs need intact links.
Log what you found
- Log cell, risk, independent evidence, impact and fix.
- Repairing the headline alone does not repair the whole workbook.
Syntax
=SUM(source_range)
Example
=SUM(GL_source!G2:G201)-Report!B3
Common mistake
Comparing a report with itself. Use original source evidence and distinguish net from debits.
Excel for Analysis / Module 2 / Recap
Recap card
2.1 References that move and references that stay
References that move and references that stay
- A relative reference moves with the copy. A $ pins the column, the row, or both.
- F4 cycles B5, $B$5, B$5, $B5. On a Mac: Cmd+T.
- Check a grid in its far corner, not in its first cell.
Syntax
=$price_column_row*(1-rate_column$row)
Example
=$B5*(1-D$4)
Common mistake
Trusting the first cell. A formula can be right where you typed it and wrong everywhere you copied it.
Excel for Analysis / Module 2 / Recap
Recap card
2.2 Names and calculation order
Names and calculation order
- A name labels a cell and never moves: =B5*(1-Discount).
- Brackets, then ^, then * and /, then + and -.
- Your locale may need ; between arguments.
Syntax
=(price-cost)/price
Example
=(B5-C5)/B5
Common mistake
The margin trap. =B5-C5/B5 divides first and gives a price, not a margin.
Excel for Analysis / Module 2 / Recap
Recap card
2.3 Decisions with IF and IFS
Decisions with IF and IFS
- IF(test, if true, if false). Text goes in quotes.
- AND needs all tests true; OR needs one.
- IFS takes the first true test, so start with the highest band.
Syntax
=IFS(test1,result1,test2,result2,TRUE,other)
Example
=IFS(H2>=20000,"Large",H2>=5000,"Medium",TRUE,"Small")
Common mistake
The edge. > leaves out the edge value; “or more” needs >=.
Excel for Analysis / Module 2 / Recap
Recap card
2.4 Errors that tell the truth
Errors that tell the truth
- #N/A no match, #DIV/0! divide by zero, #VALUE! wrong type, #REF! deleted cell, #NAME? unknown word.
- Label the gap with IFNA, then count it with COUNTIF.
Syntax
=IFNA(lookup,"No cost")
Example
=IF(I2="No cost","No cost",H2-E2*I2)
Common mistake
IFERROR(…, 0). It turns every missing cost into €0 and makes the margin look perfect.
Excel for Analysis / Module 2 / Recap
Recap card
2.5 A calculation you can test
A calculation you can test
- Write the tests from the rule before the formula.
- Test both sides of every edge, and every exception.
- Done means every test shows TRUE.
Syntax
=IF(exception,0,IFS(top band,rate,next band,rate,TRUE,0))
Example
=IF(OR(D2="BTO",D2="FC"),0,IFS(E2>=25,5%,E2>=10,2%,TRUE,0))
Common mistake
Editing the expected value. If a test fails, fix the formula, not the test.
Excel for Analysis / Module 2 / Recap
Recap card
Module 2: Formulas you can copy
References that move and references that stay
- A relative reference moves with the copy. A $ pins the column, the row, or both.
- F4 cycles B5, $B$5, B$5, $B5. On a Mac: Cmd+T.
- Check a grid in its far corner, not in its first cell.
Syntax
=$price_column_row*(1-rate_column$row)
Example
=$B5*(1-D$4)
Common mistake
Trusting the first cell. A formula can be right where you typed it and wrong everywhere you copied it.
Names and calculation order
- A name labels a cell and never moves: =B5*(1-Discount).
- Brackets, then ^, then * and /, then + and -.
- Your locale may need ; between arguments.
Syntax
=(price-cost)/price
Example
=(B5-C5)/B5
Common mistake
The margin trap. =B5-C5/B5 divides first and gives a price, not a margin.
Decisions with IF and IFS
- IF(test, if true, if false). Text goes in quotes.
- AND needs all tests true; OR needs one.
- IFS takes the first true test, so start with the highest band.
Syntax
=IFS(test1,result1,test2,result2,TRUE,other)
Example
=IFS(H2>=20000,"Large",H2>=5000,"Medium",TRUE,"Small")
Common mistake
The edge. > leaves out the edge value; “or more” needs >=.
Errors that tell the truth
- #N/A no match, #DIV/0! divide by zero, #VALUE! wrong type, #REF! deleted cell, #NAME? unknown word.
- Label the gap with IFNA, then count it with COUNTIF.
Syntax
=IFNA(lookup,"No cost")
Example
=IF(I2="No cost","No cost",H2-E2*I2)
Common mistake
IFERROR(…, 0). It turns every missing cost into €0 and makes the margin look perfect.
A calculation you can test
- Write the tests from the rule before the formula.
- Test both sides of every edge, and every exception.
- Done means every test shows TRUE.
Syntax
=IF(exception,0,IFS(top band,rate,next band,rate,TRUE,0))
Example
=IF(OR(D2="BTO",D2="FC"),0,IFS(E2>=25,5%,E2>=10,2%,TRUE,0))
Common mistake
Editing the expected value. If a test fails, fix the formula, not the test.
Excel for Analysis / Module 3 / Recap
Recap card
3.1 What a lookup does with bad keys
What a lookup does with bad keys
- A lookup finds a key and returns the value beside it.
- A duplicate key returns the first match silently. A missing key returns #N/A.
- Older Excel: INDEX with MATCH behaves the same way.
Syntax
=XLOOKUP(key, key_list, return_list)
Example
=XLOOKUP(B2,Suppliers!$A$2:$A$36,Suppliers!$B$2:$B$36)
Common mistake
Expecting a warning. Excel never flags a duplicate key. Check the list yourself before you trust the result.
Excel for Analysis / Module 3 / Recap
Recap card
3.2 XLOOKUP and INDEX with MATCH
XLOOKUP and INDEX with MATCH
- XLOOKUP with "Not found" makes every failure visible and countable.
- COUNTIF finds duplicate keys. TRIM removes trailing spaces.
- Older Excel: INDEX with MATCH(...,0), wrapped in IFNA.
Syntax
=XLOOKUP(key, key_list, return_list, "Not found")
Example
=XLOOKUP(B2,Suppliers!$D$2:$D$35,Suppliers!$B$2:$B$35,"Not found")
Common mistake
Leaving out the match type. MATCH without 0 guesses a nearby row. Always type 0 for keys.
Excel for Analysis / Module 3 / Recap
Recap card
3.3 Bands and the boundary
Bands and the boundary
- Match mode -1 takes the largest lower bound not above the value.
- A value on a bound belongs to the band that starts there.
- Guard empty amounts with IF. Older Excel: MATCH(...,1) on sorted bounds.
Syntax
=XLOOKUP(value, lower_bounds, bands, "Not found", -1)
Example
=IF(I2="","No amount",XLOOKUP(I2,Bands!$A$4:$A$8,Bands!$B$4:$B$8,"Not found",-1))
Common mistake
Reading the label, not the rule. "1,000 to 10,000" hides the boundary. Write "under 10,000" and test a value on the bound.
Excel for Analysis / Module 3 / Recap
Recap card
3.4 SUMIFS and COUNTIFS
SUMIFS and COUNTIFS
- SUMIFS: sum range first, then one range and criterion per condition.
- Dates: ">="&start and "<"&next start, so each row lands in one period.
- Mixed references let one formula fill a whole grid.
Syntax
=SUMIFS(sum_range, range1, criterion1, range2, criterion2)
Example
=SUMIFS(Workings!$D$2:$D$201,Workings!$C$2:$C$201,$A6,Workings!$E$2:$E$201,">="&B$3,Workings!$E$2:$E$201,"<"&B$4)
Common mistake
Closing a period with <=. An invoice on the next month's first day lands in two months. Use < the next start.
Excel for Analysis / Module 3 / Recap
Recap card
3.5 Reconcile to the ledger
Reconcile to the ledger
- Reconcile the summary to an independent source: the ledger.
- Keep a Not found row so unmatched spend stays in the total.
- Explain every residual: currency, duplicates or timing.
Syntax
=summary_total-ledger_total
Example
=-SUMIFS(Ledger!G2:G2456,Ledger!F2:F2456,"X-GL-2100",Ledger!C2:C2456,"SUPPLIER_INVOICE")
Common mistake
Plugging the residual. A typed adjustment hides the cause. Find it, size it and log it.
Excel for Analysis / Module 3 / Recap
Recap card
Module 3: Lookups that tell you when they fail
What a lookup does with bad keys
- A lookup finds a key and returns the value beside it.
- A duplicate key returns the first match silently. A missing key returns #N/A.
- Older Excel: INDEX with MATCH behaves the same way.
Syntax
=XLOOKUP(key, key_list, return_list)
Example
=XLOOKUP(B2,Suppliers!$A$2:$A$36,Suppliers!$B$2:$B$36)
Common mistake
Expecting a warning. Excel never flags a duplicate key. Check the list yourself before you trust the result.
XLOOKUP and INDEX with MATCH
- XLOOKUP with "Not found" makes every failure visible and countable.
- COUNTIF finds duplicate keys. TRIM removes trailing spaces.
- Older Excel: INDEX with MATCH(...,0), wrapped in IFNA.
Syntax
=XLOOKUP(key, key_list, return_list, "Not found")
Example
=XLOOKUP(B2,Suppliers!$D$2:$D$35,Suppliers!$B$2:$B$35,"Not found")
Common mistake
Leaving out the match type. MATCH without 0 guesses a nearby row. Always type 0 for keys.
Bands and the boundary
- Match mode -1 takes the largest lower bound not above the value.
- A value on a bound belongs to the band that starts there.
- Guard empty amounts with IF. Older Excel: MATCH(...,1) on sorted bounds.
Syntax
=XLOOKUP(value, lower_bounds, bands, "Not found", -1)
Example
=IF(I2="","No amount",XLOOKUP(I2,Bands!$A$4:$A$8,Bands!$B$4:$B$8,"Not found",-1))
Common mistake
Reading the label, not the rule. "1,000 to 10,000" hides the boundary. Write "under 10,000" and test a value on the bound.
SUMIFS and COUNTIFS
- SUMIFS: sum range first, then one range and criterion per condition.
- Dates: ">="&start and "<"&next start, so each row lands in one period.
- Mixed references let one formula fill a whole grid.
Syntax
=SUMIFS(sum_range, range1, criterion1, range2, criterion2)
Example
=SUMIFS(Workings!$D$2:$D$201,Workings!$C$2:$C$201,$A6,Workings!$E$2:$E$201,">="&B$3,Workings!$E$2:$E$201,"<"&B$4)
Common mistake
Closing a period with <=. An invoice on the next month's first day lands in two months. Use < the next start.
Reconcile to the ledger
- Reconcile the summary to an independent source: the ledger.
- Keep a Not found row so unmatched spend stays in the total.
- Explain every residual: currency, duplicates or timing.
Syntax
=summary_total-ledger_total
Example
=-SUMIFS(Ledger!G2:G2456,Ledger!F2:F2456,"X-GL-2100",Ledger!C2:C2456,"SUPPLIER_INVOICE")
Common mistake
Plugging the residual. A typed adjustment hides the cause. Find it, size it and log it.
Excel for Analysis / Module 4 / Recap
Recap card
4.1 Text that looks like a date
Text that looks like a date
- A real date is a serial number. ISNUMBER says TRUE.
- Text dates sort by their first characters, and SUM skips text amounts.
- Rebuild dates with DATE, RIGHT, MID and LEFT; amounts with VALUE and SUBSTITUTE.
Syntax
=DATE(RIGHT(text,4),MID(text,4,2),LEFT(text,2))
Example
=VALUE(SUBSTITUTE(E2,",","."))
Common mistake
Ageing text that looks like a date. An A to Z sort put only 2 of the 10 oldest invoices in the top 10.
Excel for Analysis / Module 4 / Recap
Recap card
4.2 Month ends and working days
Month ends and working days
- Days overdue = AsOf minus due date. Never TODAY().
- EOMONTH finds month ends, EDATE adds months, WORKDAY adds working days.
- NETWORKDAYS needs the Holidays list.
Syntax
=NETWORKDAYS(start,end,Holidays)
Example
=COUNTIFS(D2:D12378,">="&EOMONTH(AsOf,-2)+1,D2:D12378,"<="&EOMONTH(AsOf,-1))
Common mistake
Leaving out the holidays. X-INV-002099 showed 167 working days late instead of 163.
Excel for Analysis / Module 4 / Recap
Recap card
4.3 Clean text
Clean text
- TRIM before you match or split.
- TEXTSPLIT, TEXTBEFORE and TEXTAFTER need Microsoft 365 or Excel 2024. MID with FIND works everywhere.
- TEXT is for labels, never for sums.
Syntax
=TEXTBEFORE(TEXTAFTER(text,"("),")")Example
=MID(B2,FIND("(",B2)+1,2)Common mistake
Invisible spaces. 12 export refs looked right and failed the lookup.
Excel for Analysis / Module 4 / Recap
Recap card
4.4 Lists that update themselves
Lists that update themselves
- FILTER returns every row that passes. * is AND, + is OR.
- SORT, UNIQUE and SEQUENCE shape a spill. Refer to it as A4#.
- LET names the steps. #SPILL! means cells are in the way.
Syntax
=FILTER(range,(condition1)*(condition2),"None")
Example
=FILTER(Invoices!A2:H12378,(Invoices!H2:H12378>90)*(Invoices!G2:G12378>0),"None")
Common mistake
Using + for AND. The list grew from 452 to 10,374 rows.
Excel for Analysis / Module 4 / Recap
Recap card
4.5 Overdue ageing by bucket
Overdue ageing by bucket
- Edges meet but never overlap: 0 or fewer, 1 to 30, 31 to 60, 61 to 90, over 90.
- SUMIFS on days overdue for amounts, COUNTIFS for counts.
- The buckets tie to the open total with a zero residual.
Syntax
=SUMIFS(open,days,">="&low,days,"<="&high)
Example
=SUMIFS(Invoices!G2:G12378,Invoices!H2:H12378,">=31",Invoices!H2:H12378,"<=60")
Common mistake
Overlapping edges. 20 open invoices sit on day 30; >=30 in two buckets counts €66,456.57 twice.
Excel for Analysis / Module 4 / Recap
Recap card
Module 4: Dates, text and live lists
Text that looks like a date
- A real date is a serial number. ISNUMBER says TRUE.
- Text dates sort by their first characters, and SUM skips text amounts.
- Rebuild dates with DATE, RIGHT, MID and LEFT; amounts with VALUE and SUBSTITUTE.
Syntax
=DATE(RIGHT(text,4),MID(text,4,2),LEFT(text,2))
Example
=VALUE(SUBSTITUTE(E2,",","."))
Common mistake
Ageing text that looks like a date. An A to Z sort put only 2 of the 10 oldest invoices in the top 10.
Month ends and working days
- Days overdue = AsOf minus due date. Never TODAY().
- EOMONTH finds month ends, EDATE adds months, WORKDAY adds working days.
- NETWORKDAYS needs the Holidays list.
Syntax
=NETWORKDAYS(start,end,Holidays)
Example
=COUNTIFS(D2:D12378,">="&EOMONTH(AsOf,-2)+1,D2:D12378,"<="&EOMONTH(AsOf,-1))
Common mistake
Leaving out the holidays. X-INV-002099 showed 167 working days late instead of 163.
Clean text
- TRIM before you match or split.
- TEXTSPLIT, TEXTBEFORE and TEXTAFTER need Microsoft 365 or Excel 2024. MID with FIND works everywhere.
- TEXT is for labels, never for sums.
Syntax
=TEXTBEFORE(TEXTAFTER(text,"("),")")Example
=MID(B2,FIND("(",B2)+1,2)Common mistake
Invisible spaces. 12 export refs looked right and failed the lookup.
Lists that update themselves
- FILTER returns every row that passes. * is AND, + is OR.
- SORT, UNIQUE and SEQUENCE shape a spill. Refer to it as A4#.
- LET names the steps. #SPILL! means cells are in the way.
Syntax
=FILTER(range,(condition1)*(condition2),"None")
Example
=FILTER(Invoices!A2:H12378,(Invoices!H2:H12378>90)*(Invoices!G2:G12378>0),"None")
Common mistake
Using + for AND. The list grew from 452 to 10,374 rows.
Overdue ageing by bucket
- Edges meet but never overlap: 0 or fewer, 1 to 30, 31 to 60, 61 to 90, over 90.
- SUMIFS on days overdue for amounts, COUNTIFS for counts.
- The buckets tie to the open total with a zero residual.
Syntax
=SUMIFS(open,days,">="&low,days,"<="&high)
Example
=SUMIFS(Invoices!G2:G12378,Invoices!H2:H12378,">=31",Invoices!H2:H12378,"<=60")
Common mistake
Overlapping edges. 20 open invoices sit on day 30; >=30 in two buckets counts €66,456.57 twice.
Excel for Analysis / Module 5 / Recap
Recap card
5.1 Ranges that stop growing
Ranges that stop growing
- A range like F2:F36 is a fixed address: a first and a last cell.
- Rows pasted below it are left out with no warning: €40,000.00 of practice spend vanished.
Syntax
=SUM(first_cell:last_cell)
Example
=SUM(F2:F36)
Common mistake
Trusting a total after adding rows. Check where the range ends before you read the total.
Excel for Analysis / Module 5 / Recap
Recap card
5.2 Excel Tables
Excel Tables
- Ctrl+T makes a Table. Name it in Table Design, Table Name.
- Suppliers[Spend_12m] grows with the Table. [@Spend_12m] means this row.
Syntax
=SUM(Table[Column])
Example
=SUM(Suppliers[Spend_12m])
Common mistake
Pasting under the Total row. Rows below a Total row stay outside the Table. Paste in the first row directly under the data.
Excel for Analysis / Module 5 / Recap
Recap card
5.3 Validation that guides
Validation that guides
- Data, Data Validation, List, Source =StatusList, with a Stop alert.
- Circle Invalid Data finds old values the rule never checked: 5 in the Register.
- A dependent list uses names from Create from Selection and =INDIRECT($B2).
Syntax
=INDIRECT(cell_holding_a_name)
Example
=INDIRECT($B2)
Common mistake
Thinking validation cleans old data. It only guards new typing. Circle the old values and fix them.
Excel for Analysis / Module 5 / Recap
Recap card
5.4 Flags people notice
Flags people notice
- A formula rule like =$G2<AsOf colours each row by its own date.
- A text flag column carries the meaning, so COUNTIFS can count it: 6 overdue under the annual rule.
Syntax
=COUNTIFS(flag_range,"Overdue")
Example
=COUNTIFS(I2:I36,"Overdue")
Common mistake
Fixing the row with $. =$G$2<AsOf makes every row copy row 2. Fix only the column.
Excel for Analysis / Module 5 / Recap
Recap card
5.5 Build the register
Build the register
- Keep policy in a table: review months by rating in Lists!C2:D4.
- Next review is EDATE of the last review plus the looked-up months: 16 overdue, 6 of them High.
Syntax
=EDATE(last_date,XLOOKUP(rating,ratings,months))
Example
=EDATE([@Last_review],XLOOKUP([@Rating],Lists!$C$2:$C$4,Lists!$D$2:$D$4))
Common mistake
Typing the months into each formula. When the policy changes, typed 3s and 12s stay behind. Look them up.
Excel for Analysis / Module 5 / Recap
Recap card
Module 5: Registers other people can fill in
Ranges that stop growing
- A range like F2:F36 is a fixed address: a first and a last cell.
- Rows pasted below it are left out with no warning: €40,000.00 of practice spend vanished.
Syntax
=SUM(first_cell:last_cell)
Example
=SUM(F2:F36)
Common mistake
Trusting a total after adding rows. Check where the range ends before you read the total.
Excel Tables
- Ctrl+T makes a Table. Name it in Table Design, Table Name.
- Suppliers[Spend_12m] grows with the Table. [@Spend_12m] means this row.
Syntax
=SUM(Table[Column])
Example
=SUM(Suppliers[Spend_12m])
Common mistake
Pasting under the Total row. Rows below a Total row stay outside the Table. Paste in the first row directly under the data.
Validation that guides
- Data, Data Validation, List, Source =StatusList, with a Stop alert.
- Circle Invalid Data finds old values the rule never checked: 5 in the Register.
- A dependent list uses names from Create from Selection and =INDIRECT($B2).
Syntax
=INDIRECT(cell_holding_a_name)
Example
=INDIRECT($B2)
Common mistake
Thinking validation cleans old data. It only guards new typing. Circle the old values and fix them.
Flags people notice
- A formula rule like =$G2<AsOf colours each row by its own date.
- A text flag column carries the meaning, so COUNTIFS can count it: 6 overdue under the annual rule.
Syntax
=COUNTIFS(flag_range,"Overdue")
Example
=COUNTIFS(I2:I36,"Overdue")
Common mistake
Fixing the row with $. =$G$2<AsOf makes every row copy row 2. Fix only the column.
Build the register
- Keep policy in a table: review months by rating in Lists!C2:D4.
- Next review is EDATE of the last review plus the looked-up months: 16 overdue, 6 of them High.
Syntax
=EDATE(last_date,XLOOKUP(rating,ratings,months))
Example
=EDATE([@Last_review],XLOOKUP([@Rating],Lists!$C$2:$C$4,Lists!$D$2:$D$4))
Common mistake
Typing the months into each formula. When the policy changes, typed 3s and 12s stay behind. Look them up.