Lesson 6 · Excel: Zero to Expert
Advanced Excel: LET, LAMBDA & Modern Formulas (Full Course) | Excel 106
Sheet: Intro Workbook: advancedexcel.xlsx. Download it from this page to see the finished result, or start a new blank workbook and build it step by step…
- CourseExcel: Zero to Expert
- Lesson6 of 7
- Video1 h 22 min
- FormatExcel workbook · step-by-step guide
What you'll learn
- Why modern Excel formulas change everything
- LET: naming parts of a formula
- LAMBDA: build your own functions
- LAMBDA helpers: MAP, BYROW, BYCOL, REDUCE, SCAN and MAKEARRAY
- Array shaping: VSTACK, HSTACK, TAKE, DROP and more
- Text power: TEXTSPLIT, TEXTBEFORE, TEXTAFTER and friends
- GROUPBY and PIVOTBY: summaries with formulas
- Dependent dropdown lists
Files for this lesson
Open the workbook in Excel (Microsoft 365 or Excel 2021) and follow along with the video.
- advanced_excel.xlsx50.6 KB
📝 Step-by-step guide
Advanced Excel: LET, LAMBDA and Modern Formulas#
Sheet: Intro
Workbook: advanced_excel.xlsx. Download it from this page to see the finished result, or start a new blank workbook and build it step by step with the video. Each section below lists exactly what to click and type.
Why modern Excel formulas change everything#
In A1 type
Office supplies orderFormat A1: bold, font size 16
Starting in A3, type these rows (one value per cell, moving right):
|
Item|Price|Qty| |---|---|---| |Notebook|4.5|12|
Starting in the next empty row, type these rows (one value per cell, moving right):
|
Pen|1.2|40| |---|---|---| |Stapler|15|3| |Folder|2.8|25| |Marker|3.5|18|
Starting in the next empty row, type these rows (one value per cell, moving right):
|
Calculator|24|2| |---|---|---|Format A3:C3: bold
- In D3 type
Line total - In D4 type the formula
=B4*C4 - Fill D4:D9 down (drag the fill handle, or select it and press Ctrl+D)
- Select D10 and click AutoSum (Alt+=)
The line totals are fifty-four, forty-eight, forty-five, seventy, sixty-three, forty-eight, and the total order comes to three hundred and twenty-eight dollars. It works, but we needed seven formulas and an extra column just to get one number.
- In F3 type
One formula - In F4 type the formula
=SUM(B4:B9*C4:C9)
Exactly the same answer, three hundred and twenty-eight dollars, from one single formula and no helper column. Excel multiplied the six prices by the six quantities, pair by pair, and then added up the six results. That's array maths, and it's the foundation of everything we do today.
- In H3 type
Spilled - In H4 type the formula
=B4:B9*C4:C9 - In J3 type
Big orders - In J4 type the formula
=FILTER(A4:A9, B4:B9*C4:C9>50)
The formula in H4 spilled down into six cells. You can see the thin blue border around the spill range. If I click H6, the formula bar shows the formula greyed out, because it really lives in H4. And the FILTER in J4 lists Notebook, Folder and Marker. One formula did the calculation and the filtering together.
- Clear D4:D10 (Delete key)
The AutoSum that depended on it now shows zero, but our one-formula answer in F4 still shows three hundred and twenty-eight dollars, because it never needed the helper column. That's the classic weakness of helper columns. Someone deletes or sorts one, and a total somewhere else quietly changes.
LET: naming parts of a formula#
Sheet: LET
In A1 type
LET: name the partsFormat A1: bold, font size 16
Starting in A3, type these rows (one value per cell, moving right):
|
Warm-up|=LET(x, 5, y, 3, x*y+x)| |---|---|
Five times three is fifteen, plus five is twenty. The names x and y only exist inside this one formula. They don't clash with anything else in the workbook.
Starting in A5, type these rows (one value per cell, moving right):
|
Rep|Sales|Target| |---|---|---| |Anna|14200|12000| |Ben|9800|11000| |Chloe|16750|15000|
Starting in the next empty row, type these rows (one value per cell, moving right):
|
Dev|12100|12500| |---|---|---| |Ella|8900|9000| |Finn|13400|11500|Format A5:C5: bold
Starting in E5, type these rows (one value per cell, moving right):
|
Version|Team bonus| |---|---|Starting in E6, type these rows (one value per cell, moving right):
|
Messy|=IF(SUM(B6:B11)>SUM(C6:C11), (SUM(B6:B11)-SUM(C6:C11))*0.05, 0)| |---|---|
It works. Sales total seventy-five thousand, one hundred and fifty dollars, targets total seventy-one thousand dollars, so the team is four thousand, one hundred and fifty dollars over target, and five percent of that is two hundred and seven dollars fifty. But look at that formula in the formula bar. The same SUM appears twice, and so does the other one. It's hard to read, and if the range changes you have to fix it in four places.
Starting in E7, type these rows (one value per cell, moving right):
|
Step 1|=LET(total, SUM(B6:B11), IF(total>SUM(C6:C11), (total-SUM(C6:C11))*0.05, 0))| |---|---|Starting in E8, type these rows (one value per cell, moving right):
|
Step 2|=LET(total, SUM(B6:B11), target, SUM(C6:C11), IF(total>target, (total-target)*0.05, 0))| |---|---|Starting in E9, type these rows (one value per cell, moving right):
|
Step 3|=LET(total, SUM(B6:B11), target, SUM(C6:C11), extra, total-target, rate, 0.05, IF(extra>0, extra*rate, 0))| |---|---|
- Format F6:F9: number format currency
- AutoFit the width of column E:F (double-click its right edge)
All four versions give exactly the same answer, two hundred and seven dollars fifty. But the step three version explains itself. Anyone can read it, and if the rate changes to ten percent, there's one obvious place to change it. And because each SUM is now calculated only once, the LET versions do less work, which adds up on a big model.
- In D5 type
Status - In D6 type the formula
=LET(sales, B6, target, C6, pct, sales/target, IF(pct>=1, "Hit", "Missed by " & TEXT(1-pct, "0%"))) - Fill D6:D11 down (drag the fill handle, or select it and press Ctrl+D)
- AutoFit the width of column D:D (double-click its right edge)
Anna, Chloe and Finn hit their targets. Ben missed by eleven percent, Dev by three percent, and Ella by just one percent. Notice how the names make the logic obvious. You don't have to remember what B6 and C6 mean, because the formula tells you.
Starting in A13, type these rows (one value per cell, moving right):
|
Typo|=LET(total, SUM(B6:B11), totl*2)| |---|---|Starting in A15, type these rows (one value per cell, moving right):
|
Tidy name|=LET(raw, " mary JONES ", clean, TRIM(raw), PROPER(clean))| |---|---|
The typo gives the hash NAME error, because Excel has never heard of totl. When you see hash NAME inside a LET, check your spelling first. The other common mistake is forgetting that names and values come in pairs. If Excel complains that you've entered too few arguments, you've probably left out a value. And down in B15, the messy name has become Mary Jones, with the extra spaces gone and proper capitals.
LAMBDA: build your own functions#
Sheet: LAMBDA
In A1 type
LAMBDA: your own functionsFormat A1: bold, font size 16
Starting in A3, type these rows (one value per cell, moving right):
|
Test call|=LAMBDA(x, x*1.1)(100)| |---|---|Starting in A4, type these rows (one value per cell, moving right):
|
No call|=LAMBDA(x, x*1.1)| |---|---|
With the test value, we get one hundred and ten. The extra brackets at the end call the LAMBDA, the same way you'd give a number to a function. Without them, cell B4 shows the hash CALC error. That's Excel saying: you've given me a function, but no values to run it on. That's normal, and it's a good reminder that a LAMBDA needs to be called or named.
- Formulas → Name Manager → New: name
ADDGST, Refers to=LAMBDA(price, price*1.1)
Starting in A6, type these rows (one value per cell, moving right):
|
Item|Price|With GST| |---|---|---| |Coffee machine|350| | |Kettle|45.5| | |Toaster|89| |
Starting in the next empty row, type these rows (one value per cell, moving right):
|
Mug|9.9| |---|---| |Blender|120|In C7 type the formula
=ADDGST(B7)Fill C7:C11 down (drag the fill handle, or select it and press Ctrl+D)
The coffee machine becomes three hundred and eighty-five dollars, the kettle fifty dollars five, the toaster ninety-seven dollars ninety, the mug ten dollars eighty-nine and the blender one hundred and thirty-two dollars. That's our own function, behaving exactly like a built-in one.
In E6 type
SpilledIn E7 type the formula
=ADDGST(B7:B11)Starting in A12, type these rows (one value per cell, moving right):
|
Total|| =SUM(ADDGST(B7:B11))| |---|---|
- Format B7:E12: number format currency
- AutoFit the width of column A:E (double-click its right edge)
One formula in E7 spills all five prices with GST, and the total in C12 is six hundred and seventy-five dollars eighty-four. Here's the real power. If GST ever changed, you'd edit the LAMBDA once in the Name Manager, and every formula that uses ADDGST would update throughout the whole workbook.
Starting in G3, type these rows (one value per cell, moving right):
|
BMI test|=LAMBDA(kg, metres, ROUND(kg/metres^2, 1))(70, 1.75)| |---|---|
Seventy divided by one point seven five squared is twenty-two point nine. That matches what you'd get on any BMI calculator, so the test passes.
Formulas → Name Manager → New: name
BMI, Refers to=LAMBDA(kg, metres, ROUND(kg/metres^2, 1))Starting in G6, type these rows (one value per cell, moving right):
|
Name|Kg|Metres|BMI| |---|---|---|---| |Sam|82|1.8| | |Ruby|58|1.65| |
Starting in the next empty row, type these rows (one value per cell, moving right):
|
Leo|95|1.88| |---|---|---|In J7 type the formula
=BMI(H7, I7)Fill J7:J9 down (drag the fill handle, or select it and press Ctrl+D)
Sam's BMI is twenty-five point three, Ruby's is twenty-one point three and Leo's is twenty-six point nine.
Formulas → Name Manager → New: name
COMMISSION, Refers to=LAMBDA(sales, IFS(sales>=20000, sales*0.08, sales>=10000, sales*0.05, TRUE, sales*0.02))Starting in A14, type these rows (one value per cell, moving right):
|
Rep|Sales|Commission| |---|---|---| |Ava|8500| | |Noah|12000| |
Starting in the next empty row, type these rows (one value per cell, moving right):
|
Zoe|25000| |---|---| |Liam|19999| |Mia|31000|In C15 type the formula
=COMMISSION(B15)
- Fill C15:C19 down (drag the fill handle, or select it and press Ctrl+D)
- Format B15:C19: number format currency
Ava earns one hundred and seventy dollars, Noah six hundred dollars, Zoe two thousand dollars, Liam nine hundred and ninety-nine dollars ninety-five and Mia two thousand, four hundred and eighty dollars. Look at Liam. One dollar more in sales would have moved him into the top tier. Business rules like this are exactly what LAMBDA is for. The rule lives in one place, it has a clear name, and nobody can accidentally break it in row seventeen.
Formulas → Name Manager → New: name
ADDTAX, Refers to=LAMBDA(amount, [rate], amount*(1+IF(ISOMITTED(rate), 0.1, rate)))Starting in G14, type these rows (one value per cell, moving right):
|
Optional rate|Result| |---|---|Starting in G15, type these rows (one value per cell, moving right):
|
Default|=ADDTAX(200)| |---|---|
Starting in G16, type these rows (one value per cell, moving right):
|
Fifteen percent|=ADDTAX(200, 0.15)| |---|---|Starting in G17, type these rows (one value per cell, moving right):
|
Zero rate|=ADDTAX(200, 0)| |---|---|AutoFit the width of column G:H (double-click its right edge)
With no rate we get two hundred and twenty, using the ten percent default. With fifteen percent we get two hundred and thirty. And with a rate of zero we get exactly two hundred. That's the clever part of ISOMITTED. Zero is a real value that you chose, which is different from leaving the rate out.
LAMBDA helpers: MAP, BYROW, BYCOL, REDUCE, SCAN and MAKEARRAY#
Sheet: Helpers
In A1 type
LAMBDA helpersFormat A1: bold, font size 16
Starting in A3, type these rows (one value per cell, moving right):
|
Student|Test 1|Test 2|Test 3|Average|Best of 1 and 2| |---|---|---|---|---|---| |Amy|72|85|90| | |
Starting in the next empty row, type these rows (one value per cell, moving right):
|
Ravi|64|58|71| |---|---|---|---| |Zara|91|94|88| |Kai|45|62|55| |Nina|83|77|96|
Starting in the next empty row, type these rows (one value per cell, moving right):
|
Owen|58|49|66| |---|---|---|---|Format A3:F3: bold, wrap text
- In H4 type the formula
=MAX(B4:B9, C4:C9) - Clear H4 (Delete key)
- In F4 type the formula
=MAP(B4:B9, C4:C9, LAMBDA(first, second, MAX(first, second)))
The plain MAX gave ninety-four, the highest score anywhere in both columns, because MAX always squashes everything into one answer. MAP gives one answer per student: eighty-five, sixty-four, ninety-four, sixty-two, eighty-three, fifty-eight. That's the key use of MAP. It's for functions like MAX, AND and OR that would otherwise collapse an array into a single value.
- In E4 type the formula
=BYROW(B4:D9, LAMBDA(scores, ROUND(AVERAGE(scores), 1)))
Amy averages eighty-two point three, Ravi sixty-four point three, Zara ninety-one, Kai fifty-four, Nina eighty-five point three and Owen fifty-seven point seven. One formula, six averages, and if we add a fourth test column we just widen the range.
Starting in A10, type these rows (one value per cell, moving right):
|
Top score|=BYCOL(B4:D9, LAMBDA(scores, MAX(scores)))| |---|---|Starting in A11, type these rows (one value per cell, moving right):
|
Lowest|=BYCOL(B4:D9, LAMBDA(scores, MIN(scores)))| |---|---|Format A10:A11: bold
The top scores are ninety-one, ninety-four, ninety-six, and the lowest scores are forty-five, forty-nine, fifty-five. Both results spill across to the right, one value under each test. Here's a shortcut worth knowing: for simple cases you can just give the function name, like BYCOL of the range, comma, MAX, with no LAMBDA at all. Excel understands that too.
Starting in A13, type these rows (one value per cell, moving right):
|
Scores of 90+|=REDUCE(0, B4:D9, LAMBDA(tally, score, IF(score>=90, tally+1, tally)))| |---|---|
There are four scores of ninety or above: Amy's ninety, Zara's ninety-one and ninety-four, and Nina's ninety-six. Yes, COUNTIF could do this particular job. But REDUCE can carry any logic you like through an array, which COUNTIF can't.
Starting in H3, type these rows (one value per cell, moving right):
|
Month|Sales|Running total| |---|---|---| |Jan|4200| | |Feb|3900| |
Starting in the next empty row, type these rows (one value per cell, moving right):
|
Mar|5100| |---|---| |Apr|4700| |May|5600|
Starting in the next empty row, type these rows (one value per cell, moving right):
|
Jun|6100| |---|---|In J4 type the formula
=SCAN(0, I4:I9, LAMBDA(total, sale, total+sale))Format H3:J3: bold, wrap text
The running total climbs four thousand, two hundred, eight thousand, one hundred, thirteen thousand, two hundred, seventeen thousand, nine hundred, twenty-three thousand, five hundred, twenty-nine thousand, six hundred. REDUCE would only have given us the final twenty-nine thousand, six hundred. SCAN shows every step, which is exactly what a running total is.
- In A15 type
Times table - In A16 type the formula
=MAKEARRAY(5, 5, LAMBDA(rw, cl, rw*cl))
A five by five times table, from one times one in the top left to five times five, twenty-five, in the bottom right. A quick warning about names: you can't call a parameter just r or c, because Excel reserves those letters for its R1C1 reference style. And don't use names that look like cell references, such as t1. That's why I used first, second, rw and cl.
Array shaping: VSTACK, HSTACK, TAKE, DROP and more#
Sheet: Shaping
In A1 type
Shaping arraysFormat A1: bold, font size 16
Starting in A3, type these rows (one value per cell, moving right):
|
Name|Score| |---|---| |Ava|78|
Starting in the next empty row, type these rows (one value per cell, moving right):
|
Ben|64| |---|---| |Cara|91| |Dan|70|Starting in A9, type these rows (one value per cell, moving right):
|
Name|Score| |---|---|
Starting in the next empty row, type these rows (one value per cell, moving right):
|
Eli|85| |---|---| |Fay|59| |Gus|88|Format A3:B3,A9:B9: bold
- In D2 type
VSTACK - In D3 type the formula
=VSTACK(A4:B7, A10:B12)
All seven trainees in one list, January's four followed by February's three. If the lists were on different sheets, VSTACK would still join them. It's the formula version of copy and paste, except it updates itself.
- In G2 type
Top 3 - In G3 type the formula
=TAKE(SORT(D3#, 2, -1), 3) - In G6 type
The rest
- In G7 type the formula
=DROP(SORT(D3#, 2, -1), 3) - In G12 type
Last two - In G13 type the formula
=TAKE(D3#, -2)
The top three are Cara with ninety-one, Gus with eighty-eight and Eli with eighty-five. DROP gives everyone else: Ava, Dan, Ben, Fay. And TAKE minus two gives the last two rows of the stacked list, Fay and Gus. Remember that D3 hash means the whole spill range starting at D3. If the stacked list grows, everything built on D3 hash grows with it.
- In J2 type
Names - In J3 type the formula
=CHOOSECOLS(D3#, 1) - In L2 type
Best and worst - In L3 type the formula
=CHOOSEROWS(SORT(D3#, 2, -1), 1, -1)
Column J now lists the seven names. And CHOOSEROWS gives the best, Cara on ninety-one, and the lowest, Fay on fifty-nine.
- In A15 type
HSTACK - In A16 type the formula
=HSTACK(A4:A7, B4:B7, IF(B4:B7>=70, "Pass", "Retry"))
A brand new three column table: Ava passes, Ben needs a retry, and Cara and Dan pass. We built a table with a calculated column, without touching the original data.
Reshaping: TOCOL, TOROW, WRAPROWS and EXPAND#
Go to the Reshape sheet
In A3 type
RosterStarting in A4, type these rows (one value per cell, moving right):
|
Ava|Ben| | |---|---|---| |Cara|| Dan| |
Starting in the next empty row, type these rows (one value per cell, moving right):
|
Eli|Fay|Gus| |---|---|---|Format A3: bold
In E3 type
TOCOLIn E4 type the formula
=TOCOL(A4:C6, 1)
- In G3 type
By column - In G4 type the formula
=TOCOL(A4:C6, 1, TRUE)
Reading row by row we get Ava, Ben, Cara, Dan, Eli, Fay, Gus. Reading column by column we get Ava, Cara, Eli, Ben, Fay, Dan, Gus. Without the one, the empty slots would show up as zeros. That ignore argument is really handy for messy grids.
- In A12 type
TOROW - In A13 type the formula
=TOROW(A4:C6, 1) - In A15 type
WRAPROWS - In A16 type the formula
=WRAPROWS(E4#, 3, "")
The row in row thirteen holds the same seven names across seven columns. And WRAPROWS rebuilt a tidy three-wide grid: Ava, Ben, Cara, then Dan, Eli, Fay, and Gus on his own. Without that last argument, the two gaps would show hash N A. WRAPCOLS does the same thing going down columns, which is great for printing labels or seating plans.
- In I3 type
EXPAND - In I4 type the formula
=EXPAND(TAKE(E4#, 3), 4, 2, "-") - In L3 type the formula
=VSTACK("Team", SORT(TOCOL(A4:C6, 1), , -1))
EXPAND gave us a four by two block with dashes in the new cells. And in column L, one formula has built a headed list: Team, then Gus, Fay, Eli, Dan, Cara, Ben, Ava. That's the real skill, nesting these small functions to shape data exactly the way you need it.
Text power: TEXTSPLIT, TEXTBEFORE, TEXTAFTER and friends#
Sheet: Text
- In A1 type
Text power - Format A1: bold, font size 16
- In A3 type
Record
- AutoFit the width of column A:A (double-click its right edge)
Starting in C3, type these rows (one value per cell, moving right):
|
Name|City|State| |---|---|---|In C4 type the formula
=TEXTSPLIT(A4, "; ")Fill C4:C8 down (drag the fill handle, or select it and press Ctrl+D)
Format A3:E3: bold
Each record has spilled across three columns. Sarah's row reads Jones comma Sarah, Melbourne, VIC. The column delimiter can be anything: a comma, a dash, a space, or several characters like we used here.
Starting in G3, type these rows (one value per cell, moving right):
|
Surname|First|State| |---|---|---|In G4 type the formula
=TEXTBEFORE(A4:A8, ",")In H4 type the formula
=TEXTAFTER(TEXTBEFORE(A4:A8, ";"), ", ")
- In I4 type the formula
=TEXTAFTER(A4:A8, "; ", -1) - Format G3:I3: bold
Surnames: Jones, Nguyen, Patel, Brown, Wilson. First names: Sarah, Tom, Asha, Liam, Kate. And the states: three in VIC, one NSW, one QLD. That minus one trick is gold. Counting from the end means it works even when some records have more pieces than others.
- In A10 type
Apples,3;Pears,5;Plums,2;Figs,4 - In A12 type the formula
=TEXTSPLIT(A10, ",", ";") - In D10 type
Customers in VIC - In D11 type the formula
=TEXTJOIN(", ", TRUE, FILTER(H4:H8, I4:I8="VIC"))
The one cell has become a proper four by two table: apples three, pears five, plums two and figs four. Notice the numbers sit on the left. TEXTSPLIT always returns text, so wrap it in VALUE if you need to do maths with them. And D11 reads Sarah, Asha, Kate. FILTER found the matching names and TEXTJOIN turned them into one tidy sentence, perfect for a report heading or an email.
Starting in A17, type these rows (one value per cell, moving right):
|
Code|Valid?| |---|---|
- In B18 type the formula
=REGEXTEST(A18:A21, "^[A-Z]{3}-[0-9]{4}$")
ABC dash one two three four is TRUE. AB dash one two three is FALSE, too short. XYZ dash nine eight seven six is TRUE. And lowercase abc is FALSE, because REGEXTEST is case sensitive unless you tell it otherwise.
Starting in D17, type these rows (one value per cell, moving right):
|
Phone|Digits only| |---|---|In E18 type the formula
=REGEXREPLACE(D18:D19, "[^0-9]", "")
Starting in G17, type these rows (one value per cell, moving right):
|
Message|Order no.| |---|---|In G18 type
Order 4521 shipped 3 days agoIn H18 type the formula
=REGEXEXTRACT(G18, "[0-9]+")AutoFit the width of column D:H (double-click its right edge)
Both phone numbers are now plain digits, zero two nine eight seven six five four three two, and zero four one two three four five six seven eight, with the leading zero kept because the result is text. And REGEXEXTRACT found four five two one, the order number, ignoring the three later in the sentence because it returns the first match.
GROUPBY and PIVOTBY: summaries with formulas#
Sheet: GroupBy
In A1 type
Sales summaries with formulasFormat A1: bold, font size 16
Starting in A3, type these rows (one value per cell, moving right):
|
Region|Category|Sales| |---|---|---| |North|Furniture|1200|
Starting in the next empty row, type these rows (one value per cell, moving right):
|
South|Tech|2450| |---|---|---| |East|Furniture|980| |West|Tech|3100| |North|Tech|1875|
Starting in the next empty row, type these rows (one value per cell, moving right):
|
South|Furniture|1430| |---|---|---| |East|Tech|2210| |West|Furniture|760| |North|Furniture|640|
Starting in the next empty row, type these rows (one value per cell, moving right):
|
South|Tech|1520| |---|---|---| |East|Tech|1340| |West|Furniture|1105|
- Table: range A3:C15, name Orders
- In E2 type
Sum by region - In E3 type the formula
=GROUPBY(Orders[Region], Orders[Sales], SUM)
One formula has produced a complete summary, sorted A to Z: East four thousand, five hundred and thirty, North three thousand, seven hundred and fifteen, South five thousand, four hundred and West four thousand, nine hundred and sixty-five. And it even added a Total row at the bottom, eighteen thousand, six hundred and ten, which matches the sum of every order.
- In H2 type
Sorted - In H3 type the formula
=GROUPBY(Orders[Region], Orders[Sales], SUM, 0, 1, -2)
Now the biggest region comes first: South five thousand, four hundred, West four thousand, nine hundred and sixty-five, East four thousand, five hundred and thirty, North three thousand, seven hundred and fifteen. The Total stays at the bottom where it belongs. Negative numbers sort descending, positive numbers sort ascending, and the number itself is the column to sort by.
- In E9 type
Region and category - In E10 type the formula
=GROUPBY(Orders[[Region]:[Category]], Orders[Sales], SUM, 0, 1)
Each region is now broken down by category. East Furniture is nine hundred and eighty, East Tech three thousand, five hundred and fifty, North Furniture one thousand, eight hundred and forty, and so on down to West Tech at three thousand, one hundred. If you change that last one to a two, GROUPBY adds a subtotal for each region as well.
- In I9 type
PIVOTBY - In I10 type the formula
=PIVOTBY(Orders[Region], Orders[Category], Orders[Sales], SUM) - Format F3:F7,I3:I7,G10:G18,J11:L15: number format $#,##0
- AutoFit the width of column E:L (double-click its right edge)
A grid with regions down the side and Furniture and Tech across the top, with totals both ways. Furniture totals six thousand, one hundred and fifteen and Tech totals twelve thousand, four hundred and ninety-five, which add up to the same eighteen thousand, six hundred and ten. For example, West sold one thousand, eight hundred and sixty-five of furniture and three thousand, one hundred of tech.
- In E20 type
Average by category - In E21 type the formula
=GROUPBY(Orders[Category], Orders[Sales], AVERAGE) - Format F21:F23: number format $#,##0
The average furniture order is one thousand and nineteen dollars, the average tech order is two thousand and eighty-three dollars, and the Total row now shows the average of all twelve orders, one thousand, five hundred and fifty-one dollars. Tech orders are much bigger. You can also use COUNT, MAX, MIN, or even PERCENTOF to show each group's share of the total.
- Pivot: source Orders, sheet Pivot, rows ['Region'], values [['Sales', 'sum', 'Total Sales', '$#,##0']]
The PivotTable shows the same totals: East four thousand, five hundred and thirty, North three thousand, seven hundred and fifteen, South five thousand, four hundred, West four thousand, nine hundred and sixty-five, and a Grand Total of eighteen thousand, six hundred and ten. So far, they agree.
Starting in A16, type these rows (one value per cell, moving right):
|
West|Tech|900| |---|---|---|
The GROUPBY results changed instantly. North is now four thousand, five hundred and fifteen, West is now five thousand, eight hundred and sixty-five, and the Total is twenty thousand, three hundred and ten. And the sorted version has re-ordered itself: West, South, East, North. Nobody pressed refresh.
- Go to the Pivot sheet
- On the ribbon choose Data > Refresh All
After the refresh it agrees again: North four thousand, five hundred and fifteen, West five thousand, eight hundred and sixty-five, Grand Total twenty thousand, three hundred and ten.
Dependent dropdown lists#
Sheet: Dropdowns
In A1 type
Dropdown listsFormat A1: bold, font size 16
Starting in A3, type these rows (one value per cell, moving right):
|
Category|Product| |---|---| |Fruit|Apple|
Starting in the next empty row, type these rows (one value per cell, moving right):
|
Fruit|Banana| |---|---| |Vegetable|Carrot| |Dairy|Milk| |Fruit|Mango|
Starting in the next empty row, type these rows (one value per cell, moving right):
|
Vegetable|Potato| |---|---| |Dairy|Cheese| |Vegetable|Spinach| |Dairy|Yoghurt|
Starting in the next empty row, type these rows (one value per cell, moving right):
|
Vegetable|Pumpkin| |---|---|Format A3:B3: bold
- In D3 type
Size - Validation: range E3, list ['Small', 'Medium', 'Large'], input_message Choose a size
- In E3 type
Medium
If someone tries to type something that isn't on the list, like Huge, Excel shows an error message and refuses it. That's the whole point of validation: clean, consistent inputs.
- In G3 type
Categories - In G4 type the formula
=SORT(UNIQUE(A4:A13)) - In D5 type
Category
- Validation: range E5, list =$G$4#
- In E5 type
Fruit
The category list is Dairy, Fruit, Vegetable. If a new category is ever added to the product list, it appears in the dropdown by itself.
- In I3 type
Products - In I4 type the formula
=SORT(FILTER(B4:B13, A4:A13=E5)) - In D7 type
Product
- Validation: range E7, list =$I$4#
- In E7 type
Mango - Format D3:D7,G3,I3: bold
Because E5 says Fruit, the product list shows only Apple, Banana, Mango, and that's exactly what the second dropdown offers.
Starting in D9, type these rows (one value per cell, moving right):
|
Check|=IF(ISNUMBER(XMATCH(E7, I4#)), "OK", "Pick a product")| |---|---|Conditional formatting on E9: highlight cells containing “Pick”
In E5 type
Vegetable
The product list has changed to Carrot, Potato, Pumpkin, Spinach, so the second dropdown now offers vegetables. But E7 still says Mango, which isn't a vegetable, so our check in E9 has turned red and says Pick a product.
- In E7 type
Potato
And the check says OK again. Dependent dropdowns plus a check like this make your forms almost impossible to get wrong.
Error-proofing and auditing#
Sheet: Errors
In A1 type
Error-proofingFormat A1: bold, font size 16
Starting in A3, type these rows (one value per cell, moving right):
|
Code|Item|Price|Has pro?| |---|---|---|---| |P101|Laptop Pro 14|2199| |
Starting in the next empty row, type these rows (one value per cell, moving right):
|
P102|Wireless Mouse|49| |---|---|---| |P103|Pro Keyboard|159| |P104|USB Cable|15| |P105|Monitor Stand|89|
- Format A3:D3: bold
- AutoFit the width of column A:C (double-click its right edge)
Starting in F3, type these rows (one value per cell, moving right):
|
Code|XLOOKUP|IFNA|IFERROR typo|IFNA typo| |---|---|---|---|---|
- In G4 type the formula
=XLOOKUP(F4:F5, A4:A8, C4:C8) - In H4 type the formula
=IFNA(XLOOKUP(F4:F5, A4:A8, C4:C8), "Not found")
The plain lookup returns forty-nine for the mouse and hash N A for the missing code. IFNA turns only that hash N A into Not found, and leaves the forty-nine alone.
- In I4 type the formula
=IFERROR(XLOKUP(F4:F5, A4:A8, C4:C8), "Not found") - In J4 type the formula
=IFNA(XLOKUP(F4:F5, A4:A8, C4:C8), "Not found") - AutoFit the width of column F:J (double-click its right edge)
Look at the IFERROR version. It just says Not found, even for P one oh two which definitely exists. IFERROR swallowed the hash NAME error from the typo and hid a real bug. The IFNA version shows hash NAME, so we can see straight away that something is broken. That's the rule I follow: use IFNA for lookups, because a missing item is the only error you expect. Save IFERROR for when you truly want to catch every kind of error.
- In D4 type the formula
=SEARCH("pro", B4:B8)
For the Laptop Pro, pro starts at character eight. For the Pro Keyboard, it's character one. But for the other three items we get hash VALUE, because SEARCH errors when it can't find the text.
- In D4 type the formula
=ISNUMBER(SEARCH("pro", B4:B8)) - In A10 type
Pro items - In A11 type the formula
=FILTER(B4:B8, ISNUMBER(SEARCH("pro", B4:B8)))
Now column D reads TRUE, FALSE, TRUE, FALSE, FALSE. And the FILTER in A11 lists the Laptop Pro fourteen and the Pro Keyboard. SEARCH ignores capitals, so pro matched Pro. If you need an exact case match, use FIND instead.
- Click H4
- On the ribbon choose Formulas > Trace Precedents
- On the ribbon choose Formulas > Remove Arrows
- Press Ctrl+Backtick
- Press Ctrl+Backtick
Project: a formula-driven sales report#
Sheet: Raw
- Paste: at A1, rows [['Date', 'Details', 'Rep', 'Units', 'Unit Price'], ['2-Jan-2026', ' West -Keyboard ', 'lucy park', 6, 85], ['3-Jan-2026', ' SOUTH - MONITOR ', 'Jack ryan', 12, 380], ['4-Jan-2026', ' east -LAPTOP ', ' Omar HADDAD', 6, 1450], ['6-Jan-2026', ' north -mouse ', 'PRIYA shah', 4, 45], ['7-Jan-2026', 'east - Monitor', ' omar HADDAD', 2, 380], ['9-Jan-2026', 'EAST - Webcam', ' Omar HADDAD', 7, 95], ['10-Jan-2026', ' South - Laptop ', ' jack Ryan', 5, 1450], ['12-Jan-2026', ' NORTH - MONITOR', ' priya Shah', 11, 380], ['13-Jan-2026', 'west -mouse', ' ben Ortiz', 5, 45], ['15-Jan-2026', 'west -Dock ', ' BEN ORTIZ', 10, 260], ['16-Jan-2026', 'South - DOCK ', 'mei LIN', 9, 260], ['17-Jan-2026', ' north - Tablet ', ' tom Baker', 4, 720], ['19-Jan-2026', ' North - TABLET', ' Priya Shah', 5, 720], ['20-Jan-2026', 'East - KEYBOARD', ' Omar Haddad', 6, 85], ['22-Jan-2026', 'EAST - keyboard ', ' OMAR HADDAD', 9, 85], ['23-Jan-2026', ' South - keyboard ', ' mei lin', 5, 85], ['25-Jan-2026', 'south - headset', ' mei Lin', 6, 150], ['26-Jan-2026', 'south - Tablet ', 'jack ryan', 3, 720], ['28-Jan-2026', 'east - Monitor ', 'OMAR haddad', 5, 380], ['29-Jan-2026', ' east - DOCK ', 'omar Haddad', 5, 260], ['31-Jan-2026', 'south -Keyboard ', 'jack Ryan', 7, 85], ['1-Feb-2026', 'North - DOCK ', 'PRIYA Shah', 1, 260], ['2-Feb-2026', 'north - Keyboard ', 'tom baker', 12, 85], ['4-Feb-2026', ' East -tablet', ' SARA Cole', 4, 720], ['5-Feb-2026', ' SOUTH - Headset ', 'jack ryan', 3, 150], ['7-Feb-2026', 'West -Dock', ' LUCY Park', 7, 260], ['8-Feb-2026', 'EAST - tablet ', 'Sara COLE', 1, 720], ['10-Feb-2026', 'west - Monitor', 'lucy park', 8, 380], ['11-Feb-2026', ' north -Tablet', 'Priya Shah', 1, 720], ['13-Feb-2026', ' East -Headset ', ' Sara cole', 4, 150], ['14-Feb-2026', ' west - Tablet ', 'Lucy park', 4, 720], ['15-Feb-2026', ' South -Tablet', ' Jack RYAN', 5, 720], ['17-Feb-2026', ' SOUTH - Webcam', 'jack RYAN', 7, 95], ['18-Feb-2026', 'East - monitor ', ' SARA cole', 5, 380], ['20-Feb-2026', ' North -Monitor ', ' Priya SHAH', 11, 380], ['21-Feb-2026', ' West - MONITOR', ' LUCY park', 10, 380], ['23-Feb-2026', 'west -LAPTOP ', ' LUCY PARK', 4, 1450], ['24-Feb-2026', ' south - keyboard ', 'Mei lin', 5, 85], ['26-Feb-2026', ' NORTH - Dock', 'Priya shah', 7, 260], ['27-Feb-2026', ' east -monitor', 'sara Cole', 8, 380], ['1-Mar-2026', 'NORTH -laptop ', ' priya Shah', 2, 1450], ['2-Mar-2026', 'EAST - Keyboard', ' sara Cole', 7, 85], ['3-Mar-2026', ' West - Headset', 'lucy park', 7, 150], ['5-Mar-2026', ' West - mouse ', 'BEN ORTIZ', 1, 45], ['6-Mar-2026', ' North - Monitor', ' Tom Baker', 8, 380], ['8-Mar-2026', ' north - Monitor ', ' priya SHAH', 8, 380], ['9-Mar-2026', ' West - MONITOR', ' Lucy Park', 12, 380], ['11-Mar-2026', ' WEST - dock ', 'LUCY Park', 9, 260], ['12-Mar-2026', 'South - Mouse ', ' JACK Ryan', 7, 45], ['14-Mar-2026', 'East -Webcam ', 'OMAR Haddad', 6, 95], ['15-Mar-2026', ' East - Dock ', ' omar HADDAD', 11, 260], ['16-Mar-2026', ' SOUTH -dock ', ' Mei Lin', 1, 260], ['18-Mar-2026', ' north -tablet ', ' PRIYA SHAH', 3, 720], ['19-Mar-2026', 'north - Monitor ', ' Priya SHAH', 6, 380], ['21-Mar-2026', ' West - Laptop ', ' BEN ORTIZ', 1, 1450], ['22-Mar-2026', ' South - Laptop ', ' JACK ryan', 4, 1450], ['24-Mar-2026', ' west - dock ', 'Lucy PARK', 9, 260], ['25-Mar-2026', ' North - mouse ', 'Tom Baker', 6, 45], ['27-Mar-2026', 'South - Webcam ', ' Mei LIN', 7, 95], ['28-Mar-2026', ' north -DOCK ', 'Tom Baker', 12, 260]]
- AutoFit the width of column A:E (double-click its right edge)
- Format A1:E1: bold
- Click B2
Cell B2 holds the details, and the formula bar shows the text exactly as exported, with spaces in odd places and nothing at all between the dash and Keyboard. The rep in C2 is lucy park, all lowercase, with three spaces in the middle. Scroll down and you'll see SOUTH and MONITOR in capitals, east in lowercase, and every combination in between. If we built a PivotTable on this, we'd get the same region several times. So, first job: clean it.
Step 1: Clean the data with LET#
Sheet: Clean
Starting in A1, type these rows (one value per cell, moving right):
|
Date|Region|Product|Rep|Units|Price|Revenue| |---|---|---|---|---|---|---|Format A1:G1: bold, font colour White, Background 1, fill Blue, Accent 1
In I1 type
TestIn I2 type the formula
=LET(parts, TEXTSPLIT(Raw!B2, "-"), PROPER(TRIM(parts)))
It works. The messy text has become two clean values, West and Keyboard, spilled into I2 and J2. TRIM removed the spaces at both ends, and PROPER gave each word a capital first letter.
- Clear I1:I2 (Delete key)
- In A2 type the formula
=Raw!A2:A61 - Format A2:A61: number format short date
- In B2 type the formula
=LET(raw, Raw!B2:B61, PROPER(TRIM(TEXTBEFORE(raw, "-")))) - In C2 type the formula
=LET(raw, Raw!B2:B61, PROPER(TRIM(TEXTAFTER(raw, "-"))))
Two formulas, and sixty clean regions and sixty clean products. West, South, East, North, all spelled the same way, every time. And the products, Keyboard, Monitor, Laptop and so on.
- In D2 type the formula
=PROPER(TRIM(Raw!C2:C61)) - In E2 type the formula
=Raw!D2:D61 - In F2 type the formula
=Raw!E2:E61 - In G2 type the formula
=E2#*F2#
- Format F2:G61: number format currency
- AutoFit the width of column A:G (double-click its right edge)
- Click A2 and choose View → Freeze Panes
The first rep is now Lucy Park, properly capitalised with one space. Her order was six keyboards at eighty-five dollars, so the revenue is five hundred and ten dollars. The second order, Jack Ryan in the South, is twelve monitors at three hundred and eighty, which comes to four thousand, five hundred and sixty dollars.
Starting in I1, type these rows (one value per cell, moving right):
|
Check|Result| |---|---|Starting in I2, type these rows (one value per cell, moving right):
|
Rows|=ROWS(G2#)| |---|---| |Regions|=ROWS(UNIQUE(B2#))| |Products|=ROWS(UNIQUE(C2#))|
Starting in the next empty row, type these rows (one value per cell, moving right):
|
Reps|=ROWS(UNIQUE(D2#))| |---|---| |Revenue|=SUM(G2#)|Format J6: number format currency
Format I1:J1: bold
Sixty rows, four regions, eight products and eight reps, exactly what we expect. Two reps per region. And total revenue of one hundred and twenty-six thousand, two hundred and eighty-five dollars. If the cleaning had missed something, like a stray space, we'd see five regions instead of four. This little table is our early-warning system.
Step 2: Custom LAMBDA functions#
Sheet: Report
- In A1 type
Sales Report: January to March 2026 - In J3 type the formula
=LAMBDA(new, old, (new-old)/old)(110, 100) - In J4 type the formula
=LAMBDA(growth, IFS(growth>=0.1, "A", growth>=0, "B", TRUE, "C"))(0.05)
J3 shows nought point one, which is ten percent, and J4 shows B. Both tests pass.
- Formulas → Name Manager → New: name
PCTCHANGE, Refers to=LAMBDA(new, old, (new-old)/old) - Formulas → Name Manager → New: name
GRADE, Refers to=LAMBDA(growth, IFS(growth>=0.1, "A", growth>=0, "B", TRUE, "C")) - Clear J3:J4 (Delete key)
Step 3: Headline numbers and the region summary#
Starting in A3, type these rows (one value per cell, moving right):
|
Total revenue|=SUM(Clean!G2#)| |---|---| |Orders|=ROWS(Clean!G2#)| |Units sold|=SUM(Clean!E2#)| |Average order|=B3/B4|
Total revenue is one hundred and twenty-six thousand, two hundred and eighty-five dollars, from sixty orders and three hundred and sixty-six units, which makes the average order two thousand, one hundred and four dollars seventy-five.
Starting in D3, type these rows (one value per cell, moving right):
|
Region|Last year| |---|---| |North|31000| |South|30800|
Starting in the next empty row, type these rows (one value per cell, moving right):
|
East|26000| |---|---| |West|28500|
Altogether last year was one hundred and sixteen thousand, three hundred dollars.
Starting in A9, type these rows (one value per cell, moving right):
|
Region|Revenue|Growth|Grade| |---|---|---|---|In A10 type the formula
=GROUPBY(Clean!B2#, Clean!G2#, SUM, 0, 1, -2)
North is our biggest region with thirty-five thousand, six hundred and fifty dollars, then West with thirty-two thousand, four hundred and sixty dollars, South with thirty thousand, four hundred and ten dollars, and East with twenty-seven thousand, seven hundred and sixty-five dollars. The Total, one hundred and twenty-six thousand, two hundred and eighty-five dollars, matches our headline number exactly, which is a nice check.
- In C10 type the formula
=LET(summary, DROP(A10#, -1), regions, CHOOSECOLS(summary, 1), sales, CHOOSECOLS(summary, 2), last, XLOOKUP(regions, D4:D7, E4:E7), PCTCHANGE(sales, last)) - In C14 type the formula
=PCTCHANGE(B14, SUM(E4:E7)) - Format C10:C14: number format percent
- Format C10:C14: more decimals (1)
North grew by fifteen percent, West by thirteen point nine percent and East by six point eight percent. But South went backwards, down one point three percent. Overall, the business grew eight point six percent compared with last year. And because the growth formula reads its regions from the GROUPBY, the growth figures stay lined up with the right region, even when the sort order changes.
- In D10 type the formula
=MAP(C10#, LAMBDA(g, GRADE(g))) - In D14 type the formula
=GRADE(C14) - Format D10:D14: align center
North gets an A and West gets an A, both above ten percent. East gets a B. South gets a C. And the business overall gets a B. That's the payoff of custom functions. The report reads like a business document, PCTCHANGE and GRADE, not a wall of nested IFs.
Step 4: Top five products#
Starting in G9, type these rows (one value per cell, moving right):
|
Product|Revenue| |---|---|In G10 type the formula
=TAKE(SORT(GROUPBY(Clean!C2#, Clean!G2#, SUM, 0, 0), 2, -1), 5)
The top five products are Monitor with forty thousand, two hundred and eighty dollars, Laptop with thirty-one thousand, nine hundred dollars, Tablet with twenty-one thousand, six hundred dollars, Dock with twenty-one thousand and sixty dollars, and Keyboard with four thousand, eight hundred and forty-five dollars. Monitors beat laptops, even though laptops cost almost four times as much. We sold a lot more monitors. And look at the gap between Dock in fourth and Keyboard in fifth. The big four products make up most of our revenue.
Starting in J9, type these rows (one value per cell, moving right):
|
Rep|Revenue|Share| |---|---|---|In J10 type the formula
=GROUPBY(Clean!D2#, Clean!G2#, SUM, 0, 0, -2)In L10 type the formula
=CHOOSECOLS(J10#, 2)/B3
- Format K10:K17: number format $#,##0
- Format L10:L17: number format percent, more decimals (1)
Lucy Park tops the board with twenty-eight thousand, one hundred and forty dollars, which is twenty-two point three percent of all revenue. Jack Ryan and Priya Shah are neck and neck in second and third, just seventy-five dollars apart. At the bottom, Ben Ortiz has four thousand, three hundred and twenty dollars, only three point four percent. Remember, Ben Ortiz works in the West with Lucy Park, so the West's strong result is really one rep's result. That's the kind of story formulas can uncover.
Step 5: A chart#
- Select A10:B13 and insert a column chart titled “Revenue by region”
The chart shows the four regions from tallest to shortest, North, West, South, East, because the GROUPBY is sorted. If the sales change, the GROUPBY re-sorts and the chart follows.
Step 6: Formatting the report#
- Format A1: bold, font size 18, font colour Blue, Accent 1
- Format A9:D9: bold, font colour White, Background 1, fill Blue, Accent 1
- Format G9:H9: bold, font colour White, Background 1, fill Blue, Accent 1
- Format J9:L9: bold, font colour White, Background 1, fill Blue, Accent 1
- Format D3:E3: bold, font colour White, Background 1, fill Gray, Accent 3
- Format A3:A6: bold
- Format B3: number format currency
- Format B4:B5: number format #,##0
- Format B6: number format currency
- Format E4:E7: number format $#,##0
- Format B10:B14: number format $#,##0
- Format H10:H14: number format $#,##0
- Format A14:D14: bold, double bottom border
- Conditional formatting on B10:B13: highlight data_bar
- Conditional formatting on D10:D14: highlight cells containing “C”
- Format A:A: column width 16
- AutoFit the width of column B:L (double-click its right edge)
- On the ribbon choose View > Gridlines
South's C grade is now highlighted in red. A manager can see the whole story in a few seconds: revenue is up overall, North and West are flying, and South needs attention.
Step 7: A region explorer with a dynamic dropdown#
Sheet: Explorer
- In A1 type
Region explorer - Format A1: bold, font size 18, font colour Blue, Accent 1
- In J2 type
Regions - In J3 type the formula
=SORT(UNIQUE(Clean!B2#))
- In A3 type
Choose a region - Validation: range B3, list =$J$3#, input_message Pick a region from the list
- In B3 type
West - Format A3:B3: bold, fill Gold, Accent 4
The dropdown offers East, North, South and West, and because the list comes from the clean data, a new region would appear automatically.
Starting in A5, type these rows (one value per cell, moving right):
|
Revenue|=SUM(FILTER(Clean!G2#, Clean!B2#=B3))| |---|---| |Orders|=COUNTIF(Clean!B2#, B3)| |Last year|=XLOOKUP(B3, Report!D4:D7, Report!E4:E7)| |Growth|=PCTCHANGE(B5, B7)|
Starting in the next empty row, type these rows (one value per cell, moving right):
|
Grade|=GRADE(B8)| |---|---| |Reps|=TEXTJOIN(", ", TRUE, SORT(UNIQUE(FILTER(Clean!D2#, Clean!B2#=B3))))|Format B5: number format currency
Format B7: number format currency
- Format B8: number format percent, more decimals (1)
- Format A5:A10: bold
West has thirty-two thousand, four hundred and sixty dollars in revenue from fourteen orders. Last year was twenty-eight thousand, five hundred dollars, so growth is thirteen point nine percent, which earns a grade of A. And the reps are Ben Ortiz and Lucy Park.
Starting in A12, type these rows (one value per cell, moving right):
|
Product|Revenue| |---|---|In A13 type the formula
=GROUPBY(Clean!C2#, Clean!G2#, SUM, 0, 1, -2, Clean!B2#=B3)Format B13:B21: number format $#,##0
In the West, Monitor leads with eleven thousand, four hundred dollars, then Dock with nine thousand, one hundred dollars and Laptop with seven thousand, two hundred and fifty dollars. The West sells seven different products, and the Total of thirty-two thousand, four hundred and sixty dollars matches the revenue at the top.
Starting in D12, type these rows (one value per cell, moving right):
|
Date|Rep|Revenue| |---|---|---|In D13 type the formula
=TAKE(SORT(FILTER(HSTACK(Clean!A2#, Clean!D2#, Clean!G2#), Clean!B2#=B3), 3, -1), 5)Format D13:D17: number format short date
- Format F13:F17: number format $#,##0
- Format A12:B12,D12:F12: bold, font colour White, Background 1, fill Blue, Accent 1
- AutoFit the width of column B:F (double-click its right edge)
The biggest West order was five thousand, eight hundred dollars on the twenty-third of February, sold by Lucy Park. In fact, Lucy Park made all five of the biggest West orders. That's a useful insight a manager might want to follow up.
- In B3 type
South
Everything has updated at once. South has thirty thousand, four hundred and ten dollars from fifteen orders. Last year was thirty thousand, eight hundred dollars, so growth is minus one point three percent, and the grade is C. The reps are Jack Ryan and Mei Lin. In the product table, Laptop leads the South with thirteen thousand and fifty dollars, and the biggest single order was seven thousand, two hundred and fifty dollars on the tenth of January, by Jack Ryan. Not one formula was touched. We changed one input cell, and FILTER, GROUPBY, XLOOKUP, TEXTJOIN and our own LAMBDA functions all recalculated together.
- Go to the Report sheet
- Press Ctrl+Home
- Press Ctrl+S
Series recap and what's next#
Found this useful?
All lessons, notebooks and datasets here are free. If they helped you, a coffee keeps new lessons coming.



