Mathew K Analytics

Lesson 5 · Excel: Zero to Expert

XLOOKUP, Dynamic Arrays & What-If Analysis in Excel (Full Course) | Excel 105

Sheet: Lookups Workbook: lookupswhatif.xlsx. Download it from this page to see the finished result, or start a new blank workbook and build it step by step…

⬇ Download workbook (.xlsx)

What you'll learn

Files for this lesson

Open the workbook in Excel (Microsoft 365 or Excel 2021) and follow along with the video.

📝 Step-by-step guide

Lookups, Dynamic Arrays and What-If Analysis#

Sheet: Lookups

Workbook: lookups_whatif.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 we need lookups#

  • In A1 type Office supplies price list and orders

  • Format A1: bold, font size 14

  • Starting in A3, type these rows (one value per cell, moving right):

    | Code | Product | Category | Price | |---|---|---|---| | P101 | Notebook A4 | Paper | 4.5 |

  • Starting in the next empty row, type these rows (one value per cell, moving right):

    | P102 | Gel Pens (box) | Writing | 12 | |---|---|---|---| | P103 | Stapler | Desk | 18.9 | | P104 | Printer Paper (ream) | Paper | 7.8 | | P105 | Highlighters (pack) | Writing | 6.4 | | P106 | Desk Lamp | Desk | 39 |

  • Starting in F3, type these rows (one value per cell, moving right):

    | Order | Customer | Code | Qty | |---|---|---|---| | 1001 | Bayside Dental | P104 | 10 | | 1002 | Koala Accounting | P102 | 3 | | 1003 | Bayside Dental | P106 | 2 | | 1004 | Harbour Legal | P101 | 25 |

  • Starting in the next empty row, type these rows (one value per cell, moving right):

    | 1005 | Koala Accounting | P105 | 6 | |---|---|---|---| | 1006 | Harbour Legal | P103 | 4 |

  • Format A3:D3,F3:I3: bold, font colour White, Background 1, fill Blue, Accent 1

  • Format D4:D9: number format currency

  • Format A3:I9: AutoFit columns

  • In J3 type Price
  • In J4 type 7.8
  • Format J3: bold
  • Clear J3:J4 (Delete key)

VLOOKUP exact match#

Sheet: VLOOKUP

  • Paste: at A3, rows [['Code', 'Product', 'Category', 'Price'], ['P101', 'Notebook A4', 'Paper', 4.5], ['P102', 'Gel Pens (box)', 'Writing', 12], ['P103', 'Stapler', 'Desk', 18.9], ['P104', 'Printer Paper (ream)', 'Paper', 7.8], ['P105', 'Highlighters (pack)', 'Writing', 6.4], ['P106', 'Desk Lamp', 'Desk', 39]]

  • Paste: at F3, rows [['Order', 'Customer', 'Code', 'Qty'], [1001, 'Bayside Dental', 'P104', 10], [1002, 'Koala Accounting', 'P102', 3], [1003, 'Bayside Dental', 'P106', 2], [1004, 'Harbour Legal', 'P101', 25], [1005, 'Koala Accounting', 'P105', 6], [1006, 'Harbour Legal', 'P103', 4]]

  • Starting in J3, type these rows (one value per cell, moving right):

    | Price | Product | Line total | |---|---|---|

  • Format A3:D3,F3:L3: bold, font colour White, Background 1, fill Blue, Accent 1

  • In J4 type the formula =VLOOKUP(H4,$A$4:$D$9,4,FALSE)
  • Fill J4:J9 down (drag the fill handle, or select it and press Ctrl+D)
  • Format J4:J9: number format currency

And there they are. Seven eighty for the printer paper, twelve dollars for the gel pens, thirty nine for the lamp, four fifty for the notebooks, six forty for the highlighters and eighteen ninety for the staplers. Click any cell and look at the formula bar: H4 changes on each row, but the table array stays locked on A4 to D9.

  • In K4 type the formula =VLOOKUP(H4,$A$4:$D$9,2,FALSE)
  • Fill K4:K9 down (drag the fill handle, or select it and press Ctrl+D)
  • In L4 type the formula =I4*J4
  • Fill L4:L9 down (drag the fill handle, or select it and press Ctrl+D)
  • Format L4:L9: number format currency
  • Format F3:L9: AutoFit columns

Now every order has its product name and price, and the line totals are seventy eight dollars, thirty six, seventy eight, one hundred and twelve fifty, thirty eight forty and seventy five sixty. With the line totals selected, the status bar shows a sum of four hundred and eighteen dollars fifty. All calculated from a code, with no typing of prices at all.

The limits of VLOOKUP#

  • Starting in A12, type these rows (one value per cell, moving right):

    | Look up code | P107 | |---|---|

  • Starting in A13, type these rows (one value per cell, moving right):

    | Exact match | =VLOOKUP(B12,A4:D9,4,FALSE) | |---|---|

  • Starting in A14, type these rows (one value per cell, moving right):

    | No 4th argument | =VLOOKUP(B12,A4:D9,4) | |---|---|

  • Format A12:A14: bold

  • Format A12:B14: AutoFit columns

The exact match gives hash N A, not available. That's honest: the code wasn't found. But without the fourth argument we get thirty nine dollars, the desk lamp's price. Completely wrong, with no warning. Approximate match assumes the list is sorted and returns the last value less than or equal to the one you searched for, and P one oh seven comes just after P one oh six.

Approximate match for brackets and tiers#

Sheet: Brackets

  • In A1 type Simplified tax brackets (example only)

  • Format A1: bold, font size 14

  • Starting in A3, type these rows (one value per cell, moving right):

    | Income from | Base tax | Rate | |---|---|---| | 0 | 0 | 0% | | 20000 | 0 | 15% |

  • Starting in the next empty row, type these rows (one value per cell, moving right):

    | 50000 | 4500 | 30% | |---|---|---| | 120000 | 25500 | 37% | | 180000 | 47700 | 45% |

  • Format A4:B8: number format $#,##0

  • Format A3:C3: bold, font colour White, Background 1, fill Green, Accent 6

  • Starting in E3, type these rows (one value per cell, moving right):

    | Income | Rate | Tax | |---|---|---|

  • In F4 type the formula =VLOOKUP(E4,$A$4:$C$8,3,TRUE)
  • Fill F4:F8 down (drag the fill handle, or select it and press Ctrl+D)
  • Format F4:F8: number format percent

Fifteen thousand gets zero percent. Forty two thousand gets fifteen percent. Eighty five thousand gets thirty, one fifty thousand gets thirty seven, and two fifty thousand gets forty five percent. For eighty five thousand, VLOOKUP passed fifty thousand, saw one hundred and twenty thousand was too big, and stopped on the fifty thousand row: the largest threshold not above the income.

  • In G4 type the formula =VLOOKUP(E4,$A$4:$C$8,2,TRUE)+(E4-VLOOKUP(E4,$A$4:$C$8,1,TRUE))*F4
  • Fill G4:G8 down (drag the fill handle, or select it and press Ctrl+D)
  • Format E4:E8,G4:G8: number format $#,##0
  • Format E3:G3: bold, font colour White, Background 1, fill Green, Accent 6
  • Format A3:G8: AutoFit columns

Fifteen thousand pays no tax. Forty two thousand pays three thousand three hundred, fifteen percent of the twenty two thousand above the threshold. Eighty five thousand pays fifteen thousand: the base of forty five hundred plus thirty percent of thirty five thousand. One hundred and fifty thousand pays thirty six thousand six hundred, and two hundred and fifty thousand pays seventy nine thousand two hundred.

HLOOKUP for horizontal tables#

  • Starting in A11, type these rows (one value per cell, moving right):

    | Weight (kg) | 0 | 1 | 5 | 10 | 20 | |---|---|---|---|---|---| | Shipping | 8.5 | 12 | 18 | 25 | 40 |

  • Format A11:A12: bold

  • Format B12:F12: number format currency

  • Starting in A14, type these rows (one value per cell, moving right):

    | Parcel weight | 7 | |---|---|

  • Starting in A15, type these rows (one value per cell, moving right):

    | Shipping cost | =HLOOKUP(B14,B11:F12,2,TRUE) | |---|---|

  • Format B15: number format currency

Seven kilograms falls in the five kilogram band, so shipping costs eighteen dollars.

  • In B14 type 12

Now it's twenty five dollars, from the ten kilogram band. The formula recalculated instantly.

INDEX and MATCH#

Sheet: IndexMatch

  • Paste: at A3, rows [['Code', 'Product', 'Category', 'Price'], ['P101', 'Notebook A4', 'Paper', 4.5], ['P102', 'Gel Pens (box)', 'Writing', 12], ['P103', 'Stapler', 'Desk', 18.9], ['P104', 'Printer Paper (ream)', 'Paper', 7.8], ['P105', 'Highlighters (pack)', 'Writing', 6.4], ['P106', 'Desk Lamp', 'Desk', 39]]

  • Format A3:D3: bold, font colour White, Background 1, fill Blue, Accent 1

  • Starting in F3, type these rows (one value per cell, moving right):

    | INDEX position 3 | =INDEX(D4:D9,3) | |---|---|

  • Starting in F4, type these rows (one value per cell, moving right):

    | MATCH P104 | =MATCH("P104",A4:A9,0) | |---|---|

INDEX returns eighteen ninety, the third price in the list, which belongs to the stapler. MATCH returns four, because P one oh four is the fourth code in the list.

  • Starting in F6, type these rows (one value per cell, moving right):

    | Code | P105 | |---|---|

  • Starting in F7, type these rows (one value per cell, moving right):

    | Price | =INDEX(D4:D9,MATCH(G6,A4:A9,0)) | |---|---|

  • Format G7: number format currency

Six dollars forty, the price of the highlighters. MATCH found P one oh five in position five, and INDEX fetched the fifth price. Read it from the inside out: MATCH finds where, INDEX fetches what.

  • Starting in F9, type these rows (one value per cell, moving right):

    | Product | Desk Lamp | |---|---|

  • Starting in F10, type these rows (one value per cell, moving right):

    | Its code | =INDEX(A4:A9,MATCH(G9,B4:B9,0)) | |---|---|

  • Format F3:F10: bold

  • Format A3:G10: AutoFit columns

P one oh six. We searched in the product column and returned from the code column on its left. And notice there's no column number to break. If someone inserts a column in the middle of the price list, the ranges in INDEX and MATCH simply move along with it.

Two-way lookup with two MATCHes#

  • Paste: at A13, rows [['Rep', 'Q1', 'Q2', 'Q3', 'Q4'], ['Ava', 12400, 15100, 13800, 16900], ['Ben', 9800, 11250, 12600, 10400], ['Chloe', 14300, 13900, 15700, 17200], ['Dev', 8700, 9900, 11100, 12500]]

  • Format A13:E13: bold, font colour White, Background 1, fill Orange, Accent 2

  • Format B14:E17: number format $#,##0

  • Starting in G13, type these rows (one value per cell, moving right):

    | Rep | Chloe | |---|---|

  • Starting in G14, type these rows (one value per cell, moving right):

    | Quarter | Q3 | |---|---|

  • Starting in G15, type these rows (one value per cell, moving right):

    | Sales | =INDEX(B14:E17,MATCH(H13,A14:A17,0),MATCH(H14,B13:E13,0)) | |---|---|

  • Format H15: number format $#,##0

  • Format G13:G15: bold

Fifteen thousand seven hundred. Chloe is in row three of the grid, Q3 is in column three, and INDEX returns the value where they cross. You can check it in the grid yourself.

  • In H13 type Ava
  • In H14 type Q4

Sixteen thousand nine hundred, Ava's fourth quarter. Change either input and the answer follows.

XLOOKUP#

Sheet: XLOOKUP

  • Paste: at A3, rows [['Code', 'Product', 'Category', 'Price'], ['P101', 'Notebook A4', 'Paper', 4.5], ['P102', 'Gel Pens (box)', 'Writing', 12], ['P103', 'Stapler', 'Desk', 18.9], ['P104', 'Printer Paper (ream)', 'Paper', 7.8], ['P105', 'Highlighters (pack)', 'Writing', 6.4], ['P106', 'Desk Lamp', 'Desk', 39]]

  • Format A3:D3: bold, font colour White, Background 1, fill Blue, Accent 1

  • Starting in F3, type these rows (one value per cell, moving right):

    | Code | P105 | |---|---|

  • Starting in F4, type these rows (one value per cell, moving right):

    | Price | =XLOOKUP(G3,A4:A9,D4:D9) | |---|---|

  • Format G4: number format currency

Six dollars forty, the highlighters. Notice how readable that formula is: find G3 in the codes, return the price. No column counting and no FALSE at the end.

  • Starting in F5, type these rows (one value per cell, moving right):

    | Missing code | =XLOOKUP("P109",A4:A9,D4:D9,"Not found") | |---|---|

  • Starting in F6, type these rows (one value per cell, moving right):

    | All details | =XLOOKUP(G3,A4:A9,B4:D9) | |---|---|

The missing code shows our friendly message, Not found, instead of hash N A. And one formula returned three values: Highlighters pack, Writing, and six point four, spilling into the cells to the right. That's a dynamic array, and we'll cover spilling later.

  • Starting in F8, type these rows (one value per cell, moving right):

    | Product | Stapler | |---|---|

  • Starting in F9, type these rows (one value per cell, moving right):

    | Its code | =XLOOKUP(G8,B4:B9,A4:A9) | |---|---|

  • In G3 type P106

  • Format F3:F9: bold

  • Format F3:I9: AutoFit columns

The stapler's code is P one oh three, found to the left with no fuss. And with P one oh six at the top, the price is now thirty nine dollars, and the details row shows Desk Lamp, Desk and thirty nine.

XLOOKUP for tiers: next smaller match#

  • Starting in A11, type these rows (one value per cell, moving right):

    | Sales from | Rate | |---|---| | 0 | 2% | | 10000 | 4% | | 25000 | 6% | | 50000 | 8% |

  • Starting in D11, type these rows (one value per cell, moving right):

    | Sales | Rate | Commission | |---|---|---|

  • In E12 type the formula =XLOOKUP(D12,$A$12:$A$15,$B$12:$B$15,,-1)
  • Fill E12:E15 down (drag the fill handle, or select it and press Ctrl+D)
  • In F12 type the formula =D12*E12
  • Fill F12:F15 down (drag the fill handle, or select it and press Ctrl+D)
  • Format E12:E15: number format percent
  • Format A12:A15,D12:D15,F12:F15: number format $#,##0
  • Format A11:B11,D11:F11: bold, font colour White, Background 1, fill Green, Accent 6

Eight and a half thousand earns two percent, so a hundred and seventy dollars commission. Exactly twenty five thousand is an exact match, so it gets six percent: fifteen hundred dollars. Thirty two thousand falls back to the twenty five thousand tier, six percent, which is one thousand nine hundred and twenty. And sixty one thousand earns eight percent, four thousand eight hundred and eighty.

Searching from last to first#

  • Paste: at A17, rows [['Order', 'Customer', 'Code', 'Qty'], [1001, 'Bayside Dental', 'P104', 10], [1002, 'Koala Accounting', 'P102', 3], [1003, 'Bayside Dental', 'P106', 2], [1004, 'Harbour Legal', 'P101', 25], [1005, 'Koala Accounting', 'P105', 6], [1006, 'Harbour Legal', 'P103', 4]]

  • Format A17:D17: bold, font colour White, Background 1, fill Blue, Accent 1

  • Starting in F17, type these rows (one value per cell, moving right):

    | Customer | Bayside Dental | |---|---|

  • Starting in F18, type these rows (one value per cell, moving right):

    | First order | =XLOOKUP(G17,B18:B23,A18:A23) | |---|---|

  • Starting in F19, type these rows (one value per cell, moving right):

    | Latest order | =XLOOKUP(G17,B18:B23,A18:A23,,0,-1) | |---|---|

  • Format F17:F19: bold

Bayside Dental's first order is ten oh one, and their latest order is ten oh three. Same data, same lookup, just searching in the opposite direction.

  • In G17 type Harbour Legal

First order ten oh four, latest order ten oh six. This is great for things like the most recent price paid, the last time a machine was serviced, or a customer's latest status.

Two-way XLOOKUP and XMATCH#

Sheet: TwoWay

  • Paste: at A3, rows [['Rep', 'Q1', 'Q2', 'Q3', 'Q4'], ['Ava', 12400, 15100, 13800, 16900], ['Ben', 9800, 11250, 12600, 10400], ['Chloe', 14300, 13900, 15700, 17200], ['Dev', 8700, 9900, 11100, 12500]]

  • Format A3:E3: bold, font colour White, Background 1, fill Orange, Accent 2

  • Format B4:E7: number format $#,##0

  • Starting in G3, type these rows (one value per cell, moving right):

    | Rep | Ben | |---|---|

  • Starting in G4, type these rows (one value per cell, moving right):

    | Quarter | Q2 | |---|---|

  • Starting in A9, type these rows (one value per cell, moving right):

    | Ben's row | =XLOOKUP(H3,A4:A7,B4:E7) | |---|---|

It returned Ben's whole row, spilled across four cells: nine thousand eight hundred, eleven thousand two fifty, twelve thousand six hundred and ten thousand four hundred. So now we just need to pick the Q2 value out of that row. That's the job of the outer XLOOKUP.

  • Clear A9:B9 (Delete key)

  • Starting in G5, type these rows (one value per cell, moving right):

    | Sales | =XLOOKUP(H4,B3:E3,XLOOKUP(H3,A4:A7,B4:E7)) | |---|---|

  • Format H5: number format $#,##0

Eleven thousand two hundred and fifty, Ben's Q2 sales. The inner XLOOKUP picks the row, the outer one picks the column.

XMATCH#

  • Starting in G7, type these rows (one value per cell, moving right):

    | Quarter position | =XMATCH(H4,B3:E3) | |---|---|

  • Starting in G8, type these rows (one value per cell, moving right):

    | Rep position | =XMATCH(H3,A4:A7) | |---|---|

  • Starting in G9, type these rows (one value per cell, moving right):

    | INDEX + XMATCH | =INDEX(B4:E7,XMATCH(H3,A4:A7),XMATCH(H4,B3:E3)) | |---|---|

  • Format H9: number format $#,##0

  • Format G3:G9: bold

  • Format G3:H9: AutoFit columns

Q2 is in position two, Ben is in position two, and INDEX with two XMATCHes gives the same eleven thousand two hundred and fifty as our nested XLOOKUP.

  • In H3 type Chloe
  • In H4 type Q4

Both methods now show seventeen thousand two hundred, and the positions are three for Chloe and four for Q4. Two different approaches, same answer. Use whichever one you find easier to read.

Handling #N/A, other sheets and tables#

Sheet: Errors

  • Starting in A3, type these rows (one value per cell, moving right):

    | Code | Qty | VLOOKUP | IFNA | |---|---|---|---| | P102 | 3 | | | | P106 | 2 | | | | P110 | 5 | | | | P104 | 10 | | | | P101 | 25 | | |

  • Format A3:D3: bold, font colour White, Background 1, fill Blue, Accent 1

  • In C4 type the formula =VLOOKUP(A4,Lookups!$A$4:$D$9,4,FALSE)
  • Fill C4:C8 down (drag the fill handle, or select it and press Ctrl+D)

Twelve, thirty nine, then hash N A for the bad code, then seven point eight and four point five. In the formula bar you can see Lookups, exclamation mark, then the range. Excel writes the same thing if you click across to the other sheet while typing a formula.

  • In D4 type the formula =IFNA(VLOOKUP(A4,Lookups!$A$4:$D$9,4,FALSE),0)
  • Fill D4:D8 down (drag the fill handle, or select it and press Ctrl+D)
  • Format C4:D8: number format currency

Now the bad code shows zero instead of hash N A, and everything else is unchanged. A sum of this column would work, while a sum of column C would be an error.

IFERROR: use it carefully#

  • Starting in F4, type these rows (one value per cell, moving right):

    | Wrong column | =VLOOKUP(A4,Lookups!$A$4:$D$9,5,FALSE) | |---|---|

  • Starting in F5, type these rows (one value per cell, moving right):

    | Hidden by IFERROR | =IFERROR(VLOOKUP(A4,Lookups!$A$4:$D$9,5,FALSE),0) | |---|---|

  • Format F4:F5: bold

  • Format F4:G5: AutoFit columns

The first shows hash REF, telling us the formula itself is broken, so we'd fix it. But IFERROR turned it into an innocent zero. In a column of a thousand prices, nobody might notice. So use IFNA for lookups, and IFERROR only when you really mean any error.

  • Clear F4:G5 (Delete key)

Lookups into tables#

  • Go to the Lookups sheet

  • Table: range A3:D9, name Products

  • Go to the Errors sheet

  • Starting in E3, type these rows (one value per cell, moving right):

    | Table price | Line total | |---|---|

  • In E4 type the formula =XLOOKUP(A4,Products[Code],Products[Price],0)
  • Fill E4:E8 down (drag the fill handle, or select it and press Ctrl+D)
  • In F4 type the formula =B4*E4
  • Fill F4:F8 down (drag the fill handle, or select it and press Ctrl+D)
  • Format E4:F8: number format currency
  • Format E3:F3: bold, font colour White, Background 1, fill Blue, Accent 1
  • Format A3:F8: AutoFit columns

Line totals of thirty six, seventy eight, zero for the bad code, seventy eight, and one hundred and twelve fifty. The status bar shows a sum of three hundred and four dollars fifty. And the formula reads almost like English.

  • Go to the Errors sheet

Every lookup for P one oh two now says twelve fifty, and the first line total went from thirty six to thirty seven fifty. Change a price once, in one place, and every lookup is right again.

  • Starting in the next empty row, type these rows (one value per cell, moving right):

    | Lookups!A10: P107 | Sticky Notes | Paper | 3.2 | |---|---|---|---|

  • Select C6:F6

Look at the difference. The two VLOOKUP columns still can't find it, hash N A and zero, because their fixed range A4 to D9 stops before row ten. But the table version finds three dollars twenty straight away, and the line total is sixteen dollars. That's why I point lookups at tables whenever I can.

Dynamic arrays and spilling#

Sheet: Data

  • Paste: at A3, rows [['Order', 'Region', 'Rep', 'Product', 'Units', 'Revenue'], [1001, 'North', 'Ava', 'Paper', 40, 312], [1002, 'South', 'Ben', 'Writing', 25, 300], [1003, 'East', 'Chloe', 'Desk', 8, 312], [1004, 'West', 'Dev', 'Paper', 55, 429], [1005, 'North', 'Ella', 'Writing', 30, 360], [1006, 'South', 'Ben', 'Desk', 12, 468], [1007, 'East', 'Chloe', 'Paper', 35, 273], [1008, 'North', 'Ava', 'Desk', 10, 390], [1009, 'West', 'Dev', 'Writing', 18, 216], [1010, 'East', 'Finn', 'Writing', 22, 264], [1011, 'South', 'Ben', 'Paper', 60, 468], [1012, 'North', 'Ella', 'Paper', 28, 218.4], [1013, 'West', 'Dev', 'Desk', 6, 234], [1014, 'East', 'Finn', 'Desk', 15, 585], [1015, 'South', 'Ben', 'Writing', 14, 168], [1016, 'North', 'Ava', 'Writing', 20, 240]]
  • Table: range A3:F19, name Sales
  • Format F4:F19: number format currency
  • In A1 type Sales data
  • Format A1: bold, font size 14

The spill concept#

Sheet: Spill

  • Starting in A2, type these rows (one value per cell, moving right):

    | Numbers | Times ten | |---|---|

  • In A3 type the formula =SEQUENCE(5)

  • Click A5

  • In B3 type the formula =A3#*10

Ten, twenty, thirty, forty and fifty. If the SEQUENCE grows, B3 grows too. The hash is the spilled range operator, so you never guess how long a list is.

  • In D5 type blocker
  • In D3 type the formula =SEQUENCE(5,1,100,10)
  • Click D3
  • Clear D5 (Delete key)

Immediately the formula spills: one hundred, one ten, one twenty, one thirty and one forty. So with hash SPILL, don't edit the formula. Find what's in the way, even a single space or merged cells, and move it.

  • In F2 type Grid
  • In F3 type the formula =SEQUENCE(4,3)
  • In J2 type Dice rolls
  • In J3 type the formula =RANDARRAY(5,1,1,6,TRUE)
  • Press F9

The grid counts from one to twelve, across each row and then down, filling F3 to H6. And the dice rolls changed when I pressed F9. Yours will differ from mine, because RANDARRAY is volatile: it recalculates whenever anything changes. Great for simulations, but to keep the values, copy and paste them as values.

SORT and SORTBY#

Sheet: Sort

  • Starting in A2, type these rows (one value per cell, moving right):

    | Rep | Product | Units | Revenue | |---|---|---|---|

  • In A3 type the formula =SORT(Sales[[Rep]:[Revenue]],4,-1)

  • Format A2:D2: bold, font colour White, Background 1, fill Blue, Accent 1

  • Format D3:D18: number format currency

All sixteen orders, highest revenue first. Finn's desk order at five hundred and eighty five dollars, then two of Ben's at four sixty eight each. At the bottom, Ben's writing order at one sixty eight. The original table hasn't moved. This is a live, sorted view.

  • Starting in F2, type these rows (one value per cell, moving right):

    | Region | Rep | Revenue | |---|---|---|

  • In F3 type the formula =SORTBY(Sales[[Region]:[Rep]],Sales[Region],1,Sales[Revenue],-1)

  • In H3 type the formula =SORTBY(Sales[Revenue],Sales[Region],1,Sales[Revenue],-1)

  • Format F2:H2: bold, font colour White, Background 1, fill Blue, Accent 1

  • Format H3:H18: number format currency

  • Format A2:H18: AutoFit columns

East comes first alphabetically, and within East, Finn's five eighty five is first, then Chloe's three twelve, Chloe's two seventy three and Finn's two sixty four. Then North, starting with Ava's three ninety, then South and West. A two-level sort in one live formula.

FILTER#

Sheet: Filter

  • Starting in A3, type these rows (one value per cell, moving right):

    | Region | North | |---|---|

  • Format B3: bold, fill Yellow

  • Starting in A5, type these rows (one value per cell, moving right):

    | Rep | Product | Units | Revenue | |---|---|---|---|

  • In A6 type the formula =FILTER(Sales[[Rep]:[Revenue]],Sales[Region]=B3,"No matches")

  • Format A5:D5: bold, font colour White, Background 1, fill Blue, Accent 1

  • Format D6:D10: number format currency

Five rows, all from the North region: Ava with paper for three twelve, Ella with writing for three sixty, Ava with desks for three ninety, Ella with paper for two eighteen forty, and Ava with writing for two forty. The condition Sales Region equals B3 is tested for each of the sixteen rows, and only the true ones come through.

  • In B3 type West
  • In B3 type Central
  • In B3 type North

FILTER with several conditions#

  • In A13 type North orders with 25+ units
  • In A14 type the formula =FILTER(Sales[[Rep]:[Revenue]],(Sales[Region]=B3)*(Sales[Units]>=25))
  • Format A13: bold
  • Format D14:D16: number format currency

Three rows: Ava's forty units of paper, Ella's thirty units of writing products, and Ella's twenty eight units of paper. Ava's desk order and her writing order had fewer than twenty five units, so they dropped out.

  • In F5 type North or East
  • In F6 type the formula =FILTER(Sales[[Rep]:[Revenue]],(Sales[Region]="North")+(Sales[Region]="East"))
  • Format F5: bold
  • Format I6:I14: number format currency
  • Format A5:I16: AutoFit columns

Nine rows: the five North orders and the four East orders, in their original order from the table, running from F6 down to I14. Ava, Chloe and Ella come first, and the last row is Ava with two hundred and forty dollars.

UNIQUE, CHOOSECOLS and TAKE#

Sheet: Unique

  • Starting in A3, type these rows (one value per cell, moving right):

    | Reps | | Regions | | Products | |---|---|---|

  • In A4 type the formula =UNIQUE(Sales[Rep])

  • In C4 type the formula =SORT(UNIQUE(Sales[Region]))

  • In E4 type the formula =COUNTA(UNIQUE(Sales[Product]))

  • Format A3:E3: bold, font colour White, Background 1, fill Blue, Accent 1

Six reps, in the order they first appear in the table: Ava, Ben, Chloe, Dev, Ella and Finn. Four regions, sorted: East, North, South and West. And three different product categories. COUNTA of UNIQUE is the standard way to count distinct values in Excel.

  • Starting in G3, type these rows (one value per cell, moving right):

    | Rep | Revenue | |---|---|

  • In G4 type the formula =CHOOSECOLS(Sales,3,6)

  • Format H4:H19: number format currency

Just the rep and revenue for all sixteen orders, side by side, even though they're not next to each other in the table. Ava, three twelve; Ben, three hundred; Chloe, three twelve, and so on.

  • In J3 type Top 3 orders
  • In J4 type the formula =TAKE(SORT(CHOOSECOLS(Sales,3,6),2,-1),3)
  • In J9 type North, biggest first
  • In J10 type the formula =SORT(FILTER(CHOOSECOLS(Sales,3,6),Sales[Region]="North"),2,-1)
  • Format K4:K6,K10:K14: number format currency
  • Format J3,J9: bold
  • Format G3:K19: AutoFit columns

The top three orders are Finn with five eighty five, then Ben twice, with four sixty eight each. And the North orders, sorted: Ava three ninety, Ella three sixty, Ava three twelve, Ava two forty, and Ella two eighteen forty.

A summary that builds itself#

Sheet: Summary

  • In A1 type Revenue by region

  • Format A1: bold, font size 14

  • Starting in A3, type these rows (one value per cell, moving right):

    | Region | Revenue | Orders | Share | |---|---|---|---|

  • In A4 type the formula =SORT(UNIQUE(Sales[Region]))

  • In B4 type the formula =SUMIFS(Sales[Revenue],Sales[Region],A4#)

  • In C4 type the formula =COUNTIFS(Sales[Region],A4#)

  • In D4 type the formula =B4#/SUM(B4#)

  • Format A3:D3: bold, font colour White, Background 1, fill Blue, Accent 1

  • Format B4:B9: number format currency

  • Format D4:D9: number format percent

  • Starting in F3, type these rows (one value per cell, moving right):

    | Total revenue | =SUM(B4#) | |---|---|

  • Format G3: number format currency

  • Format A3:G9: AutoFit columns

East has four orders worth fourteen hundred and thirty four dollars, twenty seven percent of the total. North has five orders worth fifteen hundred and twenty dollars forty, twenty nine percent. South has four orders, fourteen hundred and four dollars, twenty seven percent. And West has three orders, eight hundred and seventy nine dollars, seventeen percent. The total revenue is five thousand two hundred and thirty seven dollars forty.

  • Starting in the next empty row, type these rows (one value per cell, moving right):

    | Data!A20: 1017 | Central | Grace | Desk | 9 | 351 | |---|---|---|---|---|---|

  • Go to the Summary sheet

Central appears at the top, first alphabetically, with one order of three hundred and fifty one dollars, six percent of the total. The other shares adjusted, and the total is now five thousand five hundred and eighty eight dollars forty. Nobody edited a formula. A report that maintains itself.

What-if analysis: Goal Seek#

Sheet: WhatIf

  • In A1 type Product profit model

  • Format A1: bold, font size 14

  • Starting in A3, type these rows (one value per cell, moving right):

    | Unit price | 25 | |---|---| | Units sold | 1200 | | Cost per unit | 11 |

  • Starting in the next empty row, type these rows (one value per cell, moving right):

    | Fixed costs | 12000 | |---|---| | Profit | =B3*B4-B4*B5-B6 |

  • Format B3,B5:B7: number format currency

  • Format A3:A7: bold

  • Format B3:B6: fill Yellow

  • Format A3:B7: AutoFit columns

Revenue is thirty thousand dollars, variable costs are thirteen thousand two hundred, fixed costs are twelve thousand, so the profit is four thousand eight hundred dollars.

  • Click B7
  • On the ribbon choose Data > What-If Analysis > Goal Seek...
  • Type B7 then 0 then B4
  • Press Enter
  • Dialog ok: wait 2.5

Units sold changed to eight hundred and fifty seven point one four, and the profit is now zero. We can't sell part of a unit, so in practice we need eight hundred and fifty eight. Goal Seek gives the maths, and you add common sense.

  • In B4 type 1200
  • Click B7
  • On the ribbon choose Data > What-If Analysis > Goal Seek...
  • Type B7 then 10000 then B3
  • Press Enter
  • Dialog ok: wait 2.5

The price changed to twenty nine dollars thirty three, and the profit shows ten thousand dollars. Check: twenty nine thirty three minus eleven leaves eighteen thirty three a unit. Times twelve hundred is twenty two thousand, minus twelve thousand fixed costs, is ten thousand.

Data tables#

  • Starting in D3, type these rows (one value per cell, moving right):

    | Price | Profit | |---|---|

  • In E4 type the formula =B7

  • In D5 type 20

  • Format D3:E3: bold, font colour White, Background 1, fill Blue, Accent 1
  • Select D4:E10
  • Ribbon: path Data > What-If Analysis > Data Table...
  • Keys: keys tab
  • Type B3
  • Format D5:E10: number format currency
  • Format E4: number format currency
  • Format D3:E10: AutoFit columns

At twenty dollars we'd lose twelve hundred. At twenty two we make twelve hundred, then three thousand six hundred, six thousand, eight thousand four hundred, and at thirty dollars, ten thousand eight hundred. Click a result and you'll see TABLE in curly braces. Excel manages these cells, so you can't edit one alone. The top row shows ten thousand, the profit at today's price.

Scenario thinking#

Loans with PMT#

Sheet: Loan

  • In A1 type Car loan

  • Format A1: bold, font size 14

  • Starting in A3, type these rows (one value per cell, moving right):

    | Loan amount | 32000 | |---|---| | Annual rate | 6.9% |

  • Starting in the next empty row, type these rows (one value per cell, moving right):

    | Years | 5 | |---|---| | Monthly payment | =PMT(B4/12,B5*12,B3) |

  • Format B3,B6: number format currency

  • Format A3:A6: bold

Six hundred and thirty two dollars thirteen, but it's negative. That's not a mistake. Excel's financial functions think in cash flows. The bank gives you money, which is positive to you, so the payments going out are negative.

  • In B6 type the formula =PMT(B4/12,B5*12,-B3)

  • Starting in A7, type these rows (one value per cell, moving right):

    | Total paid | =B6*B5*12 | |---|---| | Total interest | =B7-B3 |

  • Format B7:B8: number format currency

  • Format A7:A8: bold

  • Format A3:B8: AutoFit columns

The payment is now a positive six hundred and thirty two dollars thirteen a month. Over five years we pay thirty seven thousand nine hundred and twenty seven dollars seventy eight in total, so the loan costs five thousand nine hundred and twenty seven dollars seventy eight in interest.

  • In B5 type 3
  • In B5 type 5

A loan repayment schedule#

Sheet: Schedule

  • In A1 type Equipment loan schedule

  • Format A1: bold, font size 14

  • Starting in A3, type these rows (one value per cell, moving right):

    | Loan | 50000 | |---|---| | Rate | 8% |

  • Starting in the next empty row, type these rows (one value per cell, moving right):

    | Years | 5 | |---|---| | Annual payment | =PMT(B4,B5,-B3) |

  • Format B3,B6: number format currency

  • Format A3:A6: bold

Each annual payment is twelve thousand five hundred and twenty two dollars eighty two.

  • Starting in D3, type these rows (one value per cell, moving right):

    | Year | Opening | Payment | Interest | Principal | Closing | |---|---|---|---|---|---|

  • Starting in E4, type these rows (one value per cell, moving right):

    | =B3 | =$B$6 | =E4*$B$4 | =F4-G4 | =E4-H4 | |---|---|---|---|---|

  • In E5 type the formula =I4

  • Fill E5:E8 down (drag the fill handle, or select it and press Ctrl+D)

  • Fill F4:I8 down (drag the fill handle, or select it and press Ctrl+D)

  • Starting in D9, type these rows (one value per cell, moving right):

    | Total | | =SUM(F4:F8) | =SUM(G4:G8) | =SUM(H4:H8) | |---|---|---|---|

  • Format E4:I9: number format currency

  • Format D3:I3: bold, font colour White, Background 1, fill Blue, Accent 1

  • Format D9:I9: bold, double bottom border

  • Format D3:I9: AutoFit columns

In year one the interest is four thousand dollars, eight percent of fifty thousand, so only eight thousand five hundred and twenty two eighty two reduces the loan. Each year the interest shrinks and more goes to principal. By year five the interest is just nine hundred and twenty seven sixty two. The closing balance after year five is exactly zero, which proves the schedule. Total interest is twelve thousand six hundred and fourteen dollars eleven.

  • Starting in K3, type these rows (one value per cell, moving right):

    | IPMT check | PPMT check | |---|---|

  • Starting in K4, type these rows (one value per cell, moving right):

    | =IPMT($B$4,D4,$B$5,-$B$3) | =PPMT($B$4,D4,$B$5,-$B$3) | |---|---|

  • Fill K4:L8 down (drag the fill handle, or select it and press Ctrl+D)

  • Format K4:L8: number format currency

  • Format K3:L3: bold, font colour White, Background 1, fill Gray, Accent 3

  • Format K3:L8: AutoFit columns

The IPMT column matches our interest column exactly, four thousand in year one down to nine twenty seven sixty two in year five, and PPMT matches the principal column. So you can build a schedule either way: with simple formulas that show the logic, or with IPMT and PPMT when you just need one period's figures.

Project: Bike shop order desk and finance planner#

Sheet: Catalogue

  • In A1 type Ridgeline Cycles catalogue

  • Format A1: bold, font size 16

  • Starting in A3, type these rows (one value per cell, moving right):

    | Code | Model | Type | Price | |---|---|---|---| | B01 | Trail 300 | Mountain | 1450 | | B02 | City Glide | Commuter | 890 |

  • Starting in the next empty row, type these rows (one value per cell, moving right):

    | B03 | Volt E-Commuter | E-bike | 3200 | |---|---|---|---| | B04 | Summit Pro | Mountain | 4100 | | B05 | Kids Rider | Kids | 420 | | B06 | Road Racer 7 | Road | 2650 |

  • Table: range A3:D9, name Bikes

  • Format D4:D9: number format currency

  • Starting in F3, type these rows (one value per cell, moving right):

    | From | Discount | |---|---| | 0 | 0% | | 2000 | 3% | | 5000 | 6% |

  • Starting in the next empty row, type these rows (one value per cell, moving right):

    | 10000 | 10% | |---|---|

  • Table: range F3:G7, name Tiers

  • Format F4:F7: number format $#,##0

  • Format A3:G9: AutoFit columns

The orders table#

Sheet: Orders

  • Paste: at A3, rows [['Order', 'Customer', 'Code', 'Qty'], [5001, 'Coastal Couriers', 'B03', 6], [5002, 'Bayview School', 'B05', 12], [5003, 'Greenway Council', 'B02', 8], [5004, 'Summit Adventures', 'B04', 3], [5005, 'Coastal Couriers', 'B02', 4], [5006, 'Bayview School', 'B02', 2], [5007, 'Summit Adventures', 'B01', 5], [5008, 'Greenway Council', 'B03', 2], [5009, 'Coastal Couriers', 'B06', 1], [5010, 'Summit Adventures', 'B06', 2], [5011, 'Bayview School', 'B01', 1], [5012, 'Greenway Council', 'B05', 3], [5013, 'Coastal Couriers', 'B03', 2], [5014, 'Summit Adventures', 'B04', 1]]

  • Table: range A3:D17, name Orders

  • In A1 type Ridgeline Cycles orders

  • Format A1: bold, font size 16

  • Starting in E3, type these rows (one value per cell, moving right):

    | Model | Price | Subtotal | Discount | Total | |---|---|---|---|---|

  • In E4 type the formula =XLOOKUP([@Code],Bikes[Code],Bikes[Model])
  • In F4 type the formula =XLOOKUP([@Code],Bikes[Code],Bikes[Price])
  • In G4 type the formula =[@Qty]*[@Price]

The first order is six Volt E-Commuters at thirty two hundred each, a subtotal of nineteen thousand two hundred dollars. The second is twelve Kids Riders at four twenty, which is five thousand and forty.

  • In H4 type the formula =XLOOKUP([@Subtotal],Tiers[From],Tiers[Discount],0,-1)
  • In I4 type the formula =[@Subtotal]*(1-[@Discount])
  • Format F4:G17,I4:I17: number format currency
  • Format H4:H17: number format percent
  • Format A3:I17: AutoFit columns

The nineteen thousand two hundred dollar order is above ten thousand, so ten percent off, leaving seventeen thousand two hundred and eighty. The five thousand and forty dollar school order just makes the five thousand tier: six percent off, four thousand seven hundred and thirty seven dollars sixty. And order five thousand and six, two City Glides for seventeen eighty, gets no discount.

  • Conditional formatting on I4:I17: highlight data_bar
  • Select I4:I17

The status bar shows a sum of seventy eight thousand one hundred dollars and ten cents, across fourteen orders. The longest data bar belongs to the Coastal Couriers e-bike order, and the Summit Pro order of eleven thousand and seventy is the second largest.

The customer desk#

Sheet: Desk

  • In A1 type Customer order desk
  • Format A1: bold, font size 16
  • In A3 type Customer
  • Validation: range B3, list ['Bayview School', 'Coastal Couriers', 'Greenway Council', 'Summit Adventures'], input_message Pick a customer
  • In B3 type Coastal Couriers
  • Format B3: bold, fill Yellow
  • Format A3: bold
  • Starting in A5, type these rows (one value per cell, moving right):

    | Order | Model | Qty | Total | |---|---|---|---|

  • In A6 type the formula =FILTER(CHOOSECOLS(Orders,1,5,4,9),Orders[Customer]=B3,"No orders")

  • Format A5:D5: bold, font colour White, Background 1, fill Blue, Accent 1

  • Format D6:D10: number format currency

Coastal Couriers have four orders: six Volt E-Commuters for seventeen thousand two hundred and eighty, four City Glides for three thousand four hundred and fifty three dollars twenty, one Road Racer for two thousand five hundred and seventy fifty, and two more Volt E-Commuters for six thousand and sixteen.

  • Starting in F3, type these rows (one value per cell, moving right):

    | Orders | =COUNTIFS(Orders[Customer],B3) | |---|---|

  • Starting in F4, type these rows (one value per cell, moving right):

    | Bikes | =SUMIFS(Orders[Qty],Orders[Customer],B3) | |---|---|

  • Starting in F5, type these rows (one value per cell, moving right):

    | Total spend | =SUMIFS(Orders[Total],Orders[Customer],B3) | |---|---|

  • Format G5: number format currency

  • Format F3:F5: bold

  • Format A5:G10: AutoFit columns

Four orders, thirteen bikes, and a total spend of twenty nine thousand three hundred and nineteen dollars seventy. Coastal Couriers is a very good customer.

  • In B3 type Summit Adventures

The whole desk updates. Summit Adventures also have four orders: three Summit Pros, five Trail three hundreds, two Road Racers and one more Summit Pro. That's eleven bikes and twenty six thousand eight hundred and forty four dollars.

  • In B3 type Coastal Couriers

  • Starting in A12, type these rows (one value per cell, moving right):

    | Model | Revenue | Units | |---|---|---|

  • In A13 type the formula =SORT(UNIQUE(Orders[Model]))

  • In B13 type the formula =SUMIFS(Orders[Total],Orders[Model],A13#)
  • In C13 type the formula =SUMIFS(Orders[Qty],Orders[Model],A13#)
  • Format A12:C12: bold, font colour White, Background 1, fill Green, Accent 6
  • Format B13:B18: number format currency

Six models, alphabetically. City Glide, eleven thousand nine hundred and twenty six dollars from fourteen bikes. Kids Rider, five thousand nine hundred and ninety seven sixty from fifteen. Road Racer seven, seven thousand five hundred and fifty two fifty from three. Summit Pro, fifteen thousand and forty seven from four. Trail three hundred, eight thousand two hundred and sixty five from six. And the star, Volt E-Commuter, twenty nine thousand three hundred and twelve dollars from ten bikes.

  • Starting in E12, type these rows (one value per cell, moving right):

    | Best seller | =XLOOKUP(MAX(B13#),B13#,A13#) | |---|---|

  • Format E12: bold

  • Select A12:B18 and insert a bar chart titled “Revenue by model”

The best seller is the Volt E-Commuter, and the chart makes it obvious: its bar is almost twice as long as the next one, the Summit Pro.

  • Starting in the next empty row, type these rows (one value per cell, moving right):

    | Orders!A18: 5015 | Bayview School | B03 | 4 | |---|---|---|---|

  • Select E18:I18

  • Go to the Desk sheet

The summary has updated by itself. The Volt E-Commuter now shows forty thousand eight hundred and thirty two dollars from fourteen bikes, and its bar in the chart has grown. Every other model is unchanged.

Finance options and Goal Seek#

Sheet: Finance

  • In A1 type Finance planner

  • Format A1: bold, font size 16

  • Starting in A3, type these rows (one value per cell, moving right):

    | Customer | =Desk!B3 | |---|---| | Purchase | =Desk!G5 | | Deposit | 3000 |

  • Starting in the next empty row, type these rows (one value per cell, moving right):

    | Loan amount | =B4-B5 | |---|---| | Annual rate | 7.5% | | Years | 3 | | Monthly payment | =PMT(B7/12,B8*12,-B6) | | Total interest | =B9*B8*12-B6 |

  • Format B4:B6,B9:B10: number format currency
  • Format A3:A10: bold
  • Format B5,B7:B8: fill Yellow
  • Format A3:B10: AutoFit columns

The purchase is twenty nine thousand three hundred and nineteen seventy. After the three thousand deposit, they borrow twenty six thousand three hundred and nineteen seventy. Over three years that's eight hundred and eighteen dollars seventy one a month, with three thousand one hundred and fifty three dollars seventy three in interest. That's well above their seven hundred dollar budget.

  • Starting in D3, type these rows (one value per cell, moving right):

    | Years | Monthly | Total interest | |---|---|---|

  • Starting in E4, type these rows (one value per cell, moving right):

    | =PMT($B$7/12,D4*12,-$B$6) | =E4*D4*12-$B$6 | |---|---|

  • Fill E4:F8 down (drag the fill handle, or select it and press Ctrl+D)

  • Format E4:F8: number format currency

  • Format D3:F3: bold, font colour White, Background 1, fill Blue, Accent 1

  • Format D3:F8: AutoFit columns

One year costs two thousand two hundred and eighty three forty three a month. Two years, eleven eighty four thirty eight. Three years, eight eighteen seventy one, matching our model. Four years, six thirty six thirty eight, and five years, five twenty seven thirty nine. But the interest climbs from about a thousand and eighty dollars to over five thousand three hundred. Lower payments, higher total cost.

  • Conditional formatting on E4:E8: highlight cells less than 750

Only four years and five years are highlighted at the moment. But the customer would rather finish paying in three years. So, how big would the deposit need to be?

  • Click B9
  • On the ribbon choose Data > What-If Analysis > Goal Seek...
  • Type B9 then 700 then B5
  • Press Enter
  • Dialog ok: wait 2.5

The deposit needed is six thousand eight hundred and sixteen dollars sixteen. That leaves a loan of twenty two thousand five hundred and three dollars fifty four, a payment of exactly seven hundred dollars a month, and total interest of two thousand six hundred and ninety six dollars forty six.

  • Select E4:F8

With the bigger deposit, three years is now seven hundred dollars a month, and it's highlighted green. Four years would be five forty four eleven, and five years four fifty ninety two. Two years is still over a thousand dollars, so that's not an option.

  • In H3 type Recommendation
  • In H4 type Deposit of about $6,820 with a 3 year plan
  • Format H3: bold, font size 12
  • Format H4: italic
  • Click A1

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.