Lesson 2 · Excel: Zero to Expert
Excel Formulas & Functions Full Course: IF, SUMIFS, Text & Dates | Excel 102
Sheet: Functions Workbook: formulasfunctions.xlsx. Download it from this page to see the finished result, or start a new blank workbook and build it step by…
- CourseExcel: Zero to Expert
- Lesson2 of 7
- Video1 h 19 min
- FormatExcel workbook · step-by-step guide
What you'll learn
Files for this lesson
Open the workbook in Excel (Microsoft 365 or Excel 2021) and follow along with the video.
- formulas_functions.xlsx32.2 KB
📝 Step-by-step guide
Formulas and Functions: Make Excel Think#
Sheet: Functions
Workbook: formulas_functions.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.
How functions work#
In A1 type
How functions workFormat A1: bold, font size 16
Starting in A3, type these rows (one value per cell, moving right):
|
Item|Price|Qty| |---|---|---|
Starting in the next empty row, type these rows (one value per cell, moving right):
|
Notebook|4.85|12| |---|---|---| |Pens|1.35|40| |Stapler|8.99|3| |Folder|2.45|25| |Marker|3.79|10|Make A3:C3 bold (Ctrl+B)
Starting in E3, type these rows (one value per cell, moving right):
|
Total qty|=SUM(C4:C8)| |---|---|Starting in E4, type these rows (one value per cell, moving right):
|
Average price|=AVERAGE(B4:B8)| |---|---|
We have ninety items in total, and the average price is four point two eight six. That average has three decimal places, which is a bit messy for money. We'll fix that in a moment with a second function.
IntelliSense: Excel helps you type#
- In F10 type the formula
=ROUND - Press Shift+9
- Press Esc
- Click F10
- Press Shift+F3
- Press Esc
Nesting: a function inside a function#
Starting in E5, type these rows (one value per cell, moving right):
|
Rounded average|=ROUND(AVERAGE(B4:B8),2)| |---|---|
Four point two nine. Excel works from the inside out. First it calculates the average, four point two eight six, and then it hands that to ROUND, which keeps two decimal places. Notice the brackets. Every open bracket needs its own close bracket. As you type, Excel colours each pair of brackets differently, so you can see which ones belong together.
Starting in E6, type these rows (one value per cell, moving right):
|
Order value|=SUMPRODUCT(B4:B8,C4:C8)| |---|---|Starting in E7, type these rows (one value per cell, moving right):
|
Price range|=max(b4:b8)-min(b4:b8)| |---|---|
The total value of our stock is two hundred and thirty eight dollars thirty two. That's every price times its quantity, all added up, in one function. The price range is seven point six four. And look at the formula bar for F7. Excel changed my lower case letters into capitals. That's a quick way to check your spelling. If a function name stays in lower case, Excel didn't recognise it.
Starting in E8, type these rows (one value per cell, moving right):
|
Prices counted|=COUNT(B4:B8)| |---|---|Starting in E9, type these rows (one value per cell, moving right):
|
Pi|=PI()| |---|---|
COUNT gives five, and PI gives three point one four one five nine and more digits.
- Format F4:F7: number format currency
- AutoFit the width of column E:F (double-click its right edge)
Now F4 shows four dollars twenty nine. But careful. Click it, and the formula bar still shows the AVERAGE formula, and the real value is still four point two eight six. Formatting only changes how a number looks. ROUND changes the number itself.
Comparisons: TRUE or FALSE#
Sheet: Logic
In A1 type
Student resultsFormat A1: bold, font size 16
Starting in A3, type these rows (one value per cell, moving right):
|
Student|Score|Homework|Session| |---|---|---|---|
Starting in the next empty row, type these rows (one value per cell, moving right):
|
Ava|82|Yes|M| |---|---|---|---| |Ben|45|No|A| |Chloe|67|Yes|E| |Daniel|91|Yes|M| |Ella|58|No|A| |Finn|73|No|E| |Grace|39|Yes|M| |Hugo|50|Yes|A|
Starting in A14, type these rows (one value per cell, moving right):
|
Comparison|||Result| |---|---|---|In A15 type
Ava scored over 80In D15 type the formula
=B4>80In A16 type
Ben scored 50 or moreIn D16 type the formula
=B5>=50
- In A17 type
Hugo scored exactly 50 - In D17 type the formula
=B11=50 - In A18 type
Ben skipped homework - In D18 type the formula
=C5<>"Yes"
TRUE, FALSE, TRUE, TRUE. Ava scored eighty two, so that's true. Ben scored forty five, so false. Hugo scored exactly fifty, true. And Ben's homework says No, which is not Yes, so true.
- In A19 type
Text is not case sensitive - In D19 type the formula
="apple"="APPLE" - In A20 type
NOT flips a result - In D20 type the formula
=NOT(B4>80)
The first one is TRUE. Excel's comparisons ignore upper and lower case, so apple and APPLE count as the same word. That's handy most of the time. And NOT gives FALSE, because Ava really did score over eighty, and NOT turns that TRUE into FALSE.
The IF function#
- In E3 type
Result - In E4 type the formula
=IF(B4>=50,"Pass","Fail") - Fill E4:E11 down (drag the fill handle, or select it and press Ctrl+D)
Ava, Chloe, Daniel, Ella, Finn and Hugo pass. Ben and Grace fail. That's six passes and two fails. Look at Hugo. He scored exactly fifty, and he passes, because we used greater than or equal to. If we had used just greater than, Hugo would have failed. Always think about the number right on the boundary.
Nested IF versus IFS#
- In F3 type
Grade IF - In F4 type the formula
=IF(B4>=85,"HD",IF(B4>=75,"D",IF(B4>=65,"C",IF(B4>=50,"P","N")))) - Fill F4:F11 down (drag the fill handle, or select it and press Ctrl+D)
Ava gets a D, Ben an N, Chloe a C, Daniel an H D, Ella a P, Finn a C, Grace an N, and Hugo a P. The order matters here. Excel checks the tests from left to right and stops at the first one that's true. Daniel's ninety one is at least eighty five, so he gets H D straight away, even though ninety one is also bigger than seventy five.
- In G3 type
Grade IFS - In G4 type the formula
=IFS(B4>=85,"HD",B4>=75,"D",B4>=65,"C",B4>=50,"P",TRUE,"N") - Fill G4:G11 down (drag the fill handle, or select it and press Ctrl+D)
- AutoFit the width of column B:G (double-click its right edge)
Exactly the same grades as the nested IF, column for column. But the IFS formula is shorter, flatter, and much easier to read and change later.
AND, OR and NOT#
- In H3 type
Merit - In H4 type the formula
=AND(B4>=70,C4="Yes") - Fill H4:H11 down (drag the fill handle, or select it and press Ctrl+D)
Only Ava and Daniel are TRUE. Finn scored seventy three, which is high enough, but he didn't do his homework, so AND says FALSE. Chloe did her homework, but sixty seven isn't seventy.
- In I3 type
Support - In I4 type the formula
=IF(OR(B4<50,C4="No"),"Check in","OK") - Fill I4:I11 down (drag the fill handle, or select it and press Ctrl+D)
Ben, Ella, Finn and Grace need a check in. Ben failed and skipped homework, so both conditions are true. Ella and Finn passed but skipped homework. Grace did her homework but failed. Any one reason is enough for OR.
SWITCH: match one value to a list#
- In J3 type
Session name - In J4 type the formula
=SWITCH(D4,"M","Morning","A","Afternoon","E","Evening","Unknown") - Fill J4:J11 down (drag the fill handle, or select it and press Ctrl+D)
- AutoFit the width of column H:J (double-click its right edge)
- Make A3:J3 bold (Ctrl+B)
Every code is now a word. Ava, Daniel and Grace are Morning. Ben, Ella and Hugo are Afternoon, and Chloe and Finn are Evening.
- In L3 type
Morning pass - In L4 type the formula
=IF(AND(E4="Pass",D4="M"),"Yes","No") - Fill L4:L11 down (drag the fill handle, or select it and press Ctrl+D)
Ava and Daniel say Yes. Grace is in the morning, but she failed, so she gets No. Everyone else is in another session.
Counting with conditions: COUNTIF#
Sheet: Counting
In A1 type
Sales ordersFormat A1: bold, font size 16
Starting in A3, type these rows (one value per cell, moving right):
|
Rep|Region|Product|Units|Sales|Note| |---|---|---|---|---|---|
Starting in the next empty row, type these rows (one value per cell, moving right):
|
Ann|North|Laptop Pro|3|4500|Online| |---|---|---|---|---|---| |Ben|South|Tablet|5|2250| | |Cara|North|Phone|8|5600|Repeat| |Dev|East|Laptop Air|2|2400| | |Ann|East|Tablet|4|1800| | |Ben|North|Phone|6|4200|Online|
Starting in the next empty row, type these rows (one value per cell, moving right):
|
Cara|South|Laptop Pro|1|1500| | |---|---|---|---|---|---| |Dev|South|Phone|3|2100| | |Ann|North|Laptop Air|5|6000|Repeat| |Ben|East|Tablet|7|3150|Online| |Cara|East|Phone|2|1400| | |Dev|North|Tablet|3|1350| |Make A3:F3 bold (Ctrl+B)
AutoFit the width of column B:F (double-click its right edge)
Starting in H3, type these rows (one value per cell, moving right):
|
Question|Answer| |---|---|Starting in H4, type these rows (one value per cell, moving right):
|
North orders|=COUNTIF(B4:B15,"North")| |---|---|
Five. Excel looked down the region column and counted every cell that says North. You can check by eye. Ann, Cara, Ben, Ann again and Dev.
Starting in H5, type these rows (one value per cell, moving right):
|
Sales over 3000|=COUNTIF(E4:E15,">3000")| |---|---|Starting in H6, type these rows (one value per cell, moving right):
|
Not North|=COUNTIF(B4:B15,"<>North")| |---|---|
Five sales were over three thousand. And seven orders were not from the North. Five plus seven is twelve, which is every order, so that checks out.
Starting in H7, type these rows (one value per cell, moving right):
|
Ann in North|=COUNTIFS(A4:A15,"Ann",B4:B15,"North")| |---|---|
Two. Ann made three sales in total, but only two of them were in the North. COUNTIFS only counts the rows where every condition is true, just like AND.
SUMIF, SUMIFS and AVERAGEIF#
Starting in H8, type these rows (one value per cell, moving right):
|
North sales|=SUMIF(B4:B15,"North",E4:E15)| |---|---|Starting in H9, type these rows (one value per cell, moving right):
|
Ben tablet sales|=SUMIFS(E4:E15,A4:A15,"Ben",C4:C15,"Tablet")| |---|---|
North sales total twenty one thousand six hundred and fifty dollars. And Ben's tablet sales are five thousand four hundred. That's two orders, twenty two fifty in the South plus thirty one fifty in the East.
Starting in H10, type these rows (one value per cell, moving right):
|
Average North sale|=AVERAGEIF(B4:B15,"North",E4:E15)| |---|---|Starting in H11, type these rows (one value per cell, moving right):
|
Average North laptop|=AVERAGEIFS(E4:E15,C4:C15,"Laptop*",B4:B15,"North")| |---|---|
The average North sale is four thousand three hundred and thirty dollars. That's the twenty one thousand six hundred and fifty from before, divided by five orders. And the average North laptop sale is five thousand two hundred and fifty. There were two, a Laptop Pro for forty five hundred, and a Laptop Air for six thousand.
Wildcards, COUNTA and COUNTBLANK#
Starting in H12, type these rows (one value per cell, moving right):
|
Laptop orders|=COUNTIF(C4:C15,"Laptop*")| |---|---|Starting in H13, type these rows (one value per cell, moving right):
|
Sales of Pro models|=SUMIFS(E4:E15,C4:C15,"*Pro")| |---|---|
Four laptop orders, two Pros and two Airs. And Pro models brought in six thousand dollars, the forty five hundred from Ann and fifteen hundred from Cara.
Starting in H14, type these rows (one value per cell, moving right):
|
Orders with a note|=COUNTA(F4:F15)| |---|---|Starting in H15, type these rows (one value per cell, moving right):
|
Orders without a note|=COUNTBLANK(F4:F15)| |---|---|AutoFit the width of column H:I (double-click its right edge)
Five orders have a note, and seven are blank. Five plus seven is twelve again. If we had used plain COUNT on the note column, we'd get zero, because none of those notes are numbers. That's a classic mix-up.
Pause and try it#
Starting in H22, type these rows (one value per cell, moving right):
|
Cara units|=SUMIFS(D4:D15,A4:A15,"Cara")| |---|---|Starting in H23, type these rows (one value per cell, moving right):
|
Phones over 2000|=COUNTIFS(C4:C15,"Phone",E4:E15,">2000")| |---|---|
Cara sold eleven units, eight, one and two. And three phone orders were over two thousand dollars. Cara's small East order of fourteen hundred doesn't make the cut. If you got those, you've really got the hang of it.
Criteria in cells#
Starting in H17, type these rows (one value per cell, moving right):
|
Region|North| |---|---|Starting in H18, type these rows (one value per cell, moving right):
|
Min sale|2000| |---|---|Starting in H19, type these rows (one value per cell, moving right):
|
Orders|=COUNTIFS(B4:B15,I17,E4:E15,">="&I18)| |---|---|Starting in H20, type these rows (one value per cell, moving right):
|
Sales|=SUMIFS(E4:E15,B4:B15,I17,E4:E15,">="&I18)| |---|---|Format I17:I18: fill Yellow
In the North there are four orders of two thousand or more, worth twenty thousand three hundred dollars. Dev's small order of thirteen fifty is left out.
- In I17 type
East
Instantly, two orders and five thousand five hundred and fifty dollars. That's Dev's twenty four hundred and Ben's thirty one fifty. The East orders under two thousand are left out.
Rounding: ROUND, ROUNDUP and ROUNDDOWN#
Sheet: Maths
In A1 type
Rounding and mathsFormat A1: bold, font size 16
Starting in A3, type these rows (one value per cell, moving right):
|
Value|ROUND 2|ROUND 0|ROUNDUP 0|ROUNDDOWN 0|INT|ABS| |---|---|---|---|---|---|---|
Starting in B4, type these rows (one value per cell, moving right):
|
=ROUND(A4,2)|=ROUND(A4,0)|=ROUNDUP(A4,0)|=ROUNDDOWN(A4,0)| |---|---|---|---|Fill B4:E8 down (drag the fill handle, or select it and press Ctrl+D)
Look at the first row. Pi rounds to three point one four, and to zero places it's three. ROUNDUP gives four, even though point one four is small, and ROUNDDOWN gives three. Two point five rounds to three, because Excel rounds a half up. ROUNDDOWN gives two. Minus seven point eight is interesting. ROUND gives minus eight. ROUNDUP also gives minus eight, because up means away from zero. And ROUNDDOWN gives minus seven, towards zero. And eighteen point four four nine rounds to eighteen point four five with two places, but just eighteen with zero places.
- In A10 type the formula
=ROUND(1234.5678,-2) - In A11 type the formula
=ROUNDUP(47/12,0)
One thousand two hundred. Minus one would round to the nearest ten, and minus three to the nearest thousand. Four boxes. Forty seven divided by twelve is three point nine two, but you can't ship part of a box, so ROUNDUP gives four.
INT, MOD and ABS#
Starting in F4, type these rows (one value per cell, moving right):
|
=INT(A4)|=ABS(A4)| |---|---|Fill F4:G8 down (drag the fill handle, or select it and press Ctrl+D)
AutoFit the width of column B:G (double-click its right edge)
Format A4: column width 12
INT gives three, two, twelve thirty four, minus eight, and eighteen. Look at the negative one. INT of minus seven point eight is minus eight, not minus seven, because INT always goes down, to the smaller number. That's different from ROUNDDOWN, which gave minus seven. ABS gives back every value as a positive. Minus seven point eight becomes seven point eight. ABS is handy when you want the size of a difference, and don't care whether it went up or down.
Starting in C10, type these rows (one value per cell, moving right):
|
Minutes|135| |---|---|Starting in C11, type these rows (one value per cell, moving right):
|
Hours|=INT(D10/60)| |---|---|Starting in C12, type these rows (one value per cell, moving right):
|
Leftover minutes|=MOD(D10,60)| |---|---|Starting in C13, type these rows (one value per cell, moving right):
|
Is 7 odd?|=MOD(7,2)| |---|---|
Two hours and fifteen minutes. Sixty goes into one thirty five twice, with fifteen left over. And MOD of seven and two is one. Any odd number leaves a remainder of one when divided by two, and an even number leaves zero.
Percentages and percentage change#
Starting in A15, type these rows (one value per cell, moving right):
|
Month|Sales|% of total|Change| |---|---|---|---| |Jan|12000| | |
Starting in the next empty row, type these rows (one value per cell, moving right):
|
Feb|13800| |---|---| |Mar|12420| |Apr|15525| |Total|=SUM(B16:B19)|Make A15:D15 bold (Ctrl+B)
- In C16 type the formula
=B16/B20 - Press F4
- Press Enter
- Fill C16:C19 down (drag the fill handle, or select it and press Ctrl+D)
- Format C16:C19: number format percent, more decimals (1)
The total is fifty three thousand seven hundred and forty five. January is twenty two point three percent of it, February twenty five point seven, March twenty three point one, and April twenty eight point nine. Select them, and the status bar shows they add up to one hundred percent.
- In D17 type the formula
=(B17-B16)/B16 - Fill D17:D19 down (drag the fill handle, or select it and press Ctrl+D)
- Format D17:D19: number format percent
- AutoFit the width of column C:D (double-click its right edge)
February is up fifteen percent. March is minus ten percent, a drop. And April is up twenty five percent. Those brackets are essential. Without them, Excel would divide first and subtract second, and you'd get a meaningless answer. New minus old, all over old.
Starting in F15, type these rows (one value per cell, moving right):
|
Price|250| |---|---|Starting in F16, type these rows (one value per cell, moving right):
|
Plus 10%|=G15*(1+10%)| |---|---|Starting in F17, type these rows (one value per cell, moving right):
|
Less 15%|=G15*(1-15%)| |---|---|
Two hundred and seventy five with ten percent added, and two hundred and twelve fifty after the discount.
Cleaning text: TRIM, PROPER, UPPER and LOWER#
Sheet: Text
In A1 type
Customer listFormat A1: bold, font size 16
Starting in A3, type these rows (one value per cell, moving right):
|
Raw name|Code|City| |---|---|---|
Starting in the next empty row, type these rows (one value per cell, moving right):
|
mia thompson|AU-NSW-2041|sydney| |---|---|---| |LUCAS wright|AU-VIC-3000|MELBOURNE| |zoe king|AU-QLD-4006|brisbane| |ari PATEL|AU-WA-6000|perth| |noah lee|AU-SA-5000|Adelaide|Format A3: column width 18
- In D3 type
Clean name - In D4 type the formula
=PROPER(TRIM(A4)) - Fill D4:D8 down (drag the fill handle, or select it and press Ctrl+D)
Beautiful. Mia Thompson, Lucas Wright, Zoe King, Ari Patel and Noah Lee, each with a capital at the start of every word and exactly one space in the middle.
Click A4
Starting in F10, type these rows (one value per cell, moving right):
|
Raw length|=LEN(A4)| |---|---|Starting in F11, type these rows (one value per cell, moving right):
|
Clean length|=LEN(D4)| |---|---|
The raw name has fourteen characters, the clean name only twelve. TRIM removed two of the three spaces between mia and thompson. Here's a tip. If a lookup or a comparison mysteriously fails, and the text looks identical, check the LEN of both cells. A hidden space is very often the culprit.
LEFT, RIGHT, MID and FIND#
Starting in E3, type these rows (one value per cell, moving right):
|
Country|Postcode|State MID| |---|---|---|Starting in E4, type these rows (one value per cell, moving right):
|
=LEFT(B4,2)|=RIGHT(B4,4)|=MID(B4,4,3)| |---|---|---|Fill E4:G8 down (drag the fill handle, or select it and press Ctrl+D)
Country is A U for everyone, and the postcodes are two zero four one, three thousand, four zero zero six, six thousand and five thousand. The states work for N S W, V I C and Q L D. But look at Western Australia. It shows W A dash. And South Australia shows S A dash. Our MID always takes three characters, but those state codes only have two.
Starting in H3, type these rows (one value per cell, moving right):
|
Second dash|State| |---|---| |> =FIND("-",B4,4)|=MID(B4,4,H4-4)|Fill H4:I8 down (drag the fill handle, or select it and press Ctrl+D)
For the three letter states, the second dash is at position seven. For W A and S A, it's at position six. So MID takes three characters for some rows and two for others, and every state is now correct.
Joining text: the ampersand, CONCAT and TEXTJOIN#
- In J3 type
Username - In J4 type the formula
=LOWER(SUBSTITUTE(D4," ",".")) - Fill J4:J8 down (drag the fill handle, or select it and press Ctrl+D)
- AutoFit the width of column B:J (double-click its right edge)
mia dot thompson, lucas dot wright, zoe dot king, ari dot patel, and noah dot lee. Ready to become email addresses.
- In A12 type the formula
=D4&" lives in "&PROPER(C4) - In A13 type the formula
=CONCAT(E4,"-",F4)
Mia Thompson lives in Sydney. That sentence wrote itself from three different cells. CONCAT gives A U dash two zero four one.
- In A14 type the formula
=TEXTJOIN(", ",TRUE,D4:D8) - In A15 type the formula
=UPPER(D5)
TEXTJOIN gives all five names in one cell, with commas between them. Try doing that with ampersands and you'd need a very long formula. And UPPER gives LUCAS WRIGHT in capitals.
The TEXT function#
- In A17 type the formula
="Total: "&1234.5 - In A18 type the formula
="Total: "&TEXT(1234.5,"$#,##0.00")
Without TEXT, we get Total colon one two three four point five. No dollar sign, no comma, and only one decimal place. Joining text throws away the number's formatting. With TEXT, we get Total, one thousand two hundred and thirty four dollars fifty, formatted perfectly.
- In A19 type the formula
="Due "&DATE(2026,12,24) - In A20 type the formula
="Due "&TEXT(DATE(2026,12,24),"dddd d mmmm yyyy")
The date is even worse. Due four six three eight zero. That's the date's secret number, which we'll explain in the next section. And with TEXT, Due Thursday twenty four December twenty twenty six. Four d's give the full day name, four m's give the full month name.
- In A21 type the formula
=SUBSTITUTE(B4,"-","/")
A U slash N S W slash two zero four one. SUBSTITUTE replaces every match by default. If you only want to replace one of them, it has an optional fourth argument for which one.
Pause and try it#
- In A22 type the formula
=LEFT(D4,FIND(" ",D4)-1)
Mia. FIND says the space is at position four, so LEFT takes three characters. And it would work for any name length, which is the whole point.
How Excel stores dates#
Sheet: Dates
In A1 type
Working with datesFormat A1: bold, font size 16
Starting in A3, type these rows (one value per cell, moving right):
|
Typed date|3-Nov-2026| |---|---|
Starting in A4, type these rows (one value per cell, moving right):
|
As a number|=B3| |---|---|Format B4: number format general
Starting in A5, type these rows (one value per cell, moving right):
|
DATE function|=DATE(2026,11,3)| |---|---|
In B4 the date becomes four six three two nine. The third of November twenty twenty six is day forty six thousand three hundred and twenty nine. And DATE gives exactly the same date as the one we typed. DATE is the safest way to put a date in a formula, because it never depends on your computer's regional settings.
Starting in A6, type these rows (one value per cell, moving right):
|
Today|=TODAY()| |---|---|Starting in A7, type these rows (one value per cell, moving right):
|
Year|=YEAR(B3)| |---|---|Starting in A8, type these rows (one value per cell, moving right):
|
Month|=MONTH(B3)| |---|---|Starting in A9, type these rows (one value per cell, moving right):
|
Day|=DAY(B3)| |---|---|Format B3:B6: number format d mmm yyyy
Format B4: number format general
AutoFit the width of column B:B (double-click its right edge)
Format A4: column width 16
B6 shows today's date, whatever day you're watching. TODAY updates itself whenever the workbook recalculates, so never use it for a date you need to keep, like the day an invoice was paid. YEAR gives twenty twenty six, MONTH gives eleven, and DAY gives three.
Date arithmetic, EDATE and EOMONTH#
Starting in D3, type these rows (one value per cell, moving right):
|
Project start|2-Feb-2026| |---|---|Starting in D4, type these rows (one value per cell, moving right):
|
Project end|18-Nov-2026| |---|---|Starting in D5, type these rows (one value per cell, moving right):
|
Days between|=E4-E3| |---|---|Format E3:E4: number format d mmm yyyy
Format E5: number format general
Two hundred and eighty nine days between the start and the end. Excel just subtracted one number from another.
Starting in D6, type these rows (one value per cell, moving right):
|
Review in 3 months|=EDATE(E3,3)| |---|---|Starting in D7, type these rows (one value per cell, moving right):
|
End of start month|=EOMONTH(E3,0)| |---|---|Starting in D8, type these rows (one value per cell, moving right):
|
End of next month|=EOMONTH(E3,1)| |---|---|
Depending on your version, you may see plain numbers here, like four six one four four, the date's secret number. Either way, let's give them a proper date format.
- Format E6:E8: number format d mmm yyyy
- AutoFit the width of column D:E (double-click its right edge)
The review is on the second of May twenty twenty six, exactly three months later. The start month ends on the twenty eighth of February, because twenty twenty six isn't a leap year. And the next month ends on the thirty first of March. EOMONTH saves you from remembering how many days each month has. It's used everywhere for monthly reports and due dates.
WEEKDAY and NETWORKDAYS#
Starting in D9, type these rows (one value per cell, moving right):
|
Weekday number|=WEEKDAY(E3)| |---|---|Starting in D10, type these rows (one value per cell, moving right):
|
Weekday name|=TEXT(E3,"dddd")| |---|---|Format E9: number format general
WEEKDAY gives two, and the name is Monday. By default, WEEKDAY counts Sunday as one, Monday as two, through to Saturday as seven. Its optional second argument lets you start the week on Monday instead.
- In G3 type
Holidays - Format G4:G7: number format d mmm yyyy
Starting in D11, type these rows (one value per cell, moving right):
|
Working days|=NETWORKDAYS(E3,E4)| |---|---|Starting in D12, type these rows (one value per cell, moving right):
|
Less holidays|=NETWORKDAYS(E3,E4,G4:G7)| |---|---|Format E11:E12: number format general
Make G3 bold (Ctrl+B)
There are two hundred and eight weekdays from the start to the end of the project. Take away the four holidays, which all fall on weekdays, and we have two hundred and four real working days.
Ages with DATEDIF#
Starting in A13, type these rows (one value per cell, moving right):
|
As at|31-Dec-2026| |---|---|Starting in A14, type these rows (one value per cell, moving right):
|
Name|Born|Years|Months| |---|---|---|---|
Starting in the next empty row, type these rows (one value per cell, moving right):
|
Olivia|14-Jun-1990| |---|---| |Sam|5-Jan-2008| |Lena|23-Dec-1975|Format B13:B17: number format d mmm yyyy
Make A14:D14 bold (Ctrl+B)
Starting in C15, type these rows (one value per cell, moving right):
|
=DATEDIF(B15,$B$13,"Y")|=DATEDIF(B15,$B$13,"YM")| |---|---|Fill C15:D17 down (drag the fill handle, or select it and press Ctrl+D)
Format C15:D17: number format general
Olivia is thirty six years and six months old. Sam is eighteen years and eleven months. And Lena is fifty one years and zero months, because her birthday was just eight days before our as at date.
Times#
Starting in G10, type these rows (one value per cell, moving right):
|
Shift start|8:45| |---|---|Starting in G11, type these rows (one value per cell, moving right):
|
Shift end|17:15| |---|---|
Starting in G12, type these rows (one value per cell, moving right):
|
Time worked|=H11-H10| |---|---|Starting in G13, type these rows (one value per cell, moving right):
|
Hours|=(H11-H10)*24| |---|---|Starting in G14, type these rows (one value per cell, moving right):
|
Start as number|=H10| |---|---|Format H13:H14: number format general
AutoFit the width of column G:H (double-click its right edge)
Time worked shows eight thirty, eight hours and thirty minutes, in a time format. Hours shows eight point five, a normal number we can calculate with. And eight forty five in the morning is stored as roughly zero point three six five, a little over a third of a day.
Errors and what they mean#
Sheet: Errors
In A1 type
Product reportFormat A1: bold, font size 16
Starting in A3, type these rows (one value per cell, moving right):
|
Product|Revenue|Units|Per unit| |---|---|---|---|
Starting in the next empty row, type these rows (one value per cell, moving right):
|
Widget|1200|48| |---|---|---| |Gadget|950|0| |Gizmo|780|30| |Doohickey|640|16|In D4 type the formula
=B4/C4Fill D4:D7 down (drag the fill handle, or select it and press Ctrl+D)
Format A4: column width 12
Twenty five, twenty six and forty for three of them. But Gadget shows hash DIV slash zero, exclamation mark. That's the divide by zero error. You can't divide nine hundred and fifty by zero, so Excel refuses. See the little green triangle in the corner of the cell? Click the cell and a warning icon appears beside it, with a menu explaining the problem.
Starting in H3, type these rows (one value per cell, moving right):
|
Problem|Result| |---|---|Starting in H4, type these rows (one value per cell, moving right):
|
Misspelt function|=SUMM(B4:B7)| |---|---|Starting in H5, type these rows (one value per cell, moving right):
|
Text times a number|=A4*2| |---|---|
The typo gives hash NAME question mark. Excel doesn't know any function called SUMM. You'll also see this error when you forget the quotes around text, or misspell a named range. Multiplying a word gives hash VALUE exclamation mark. It means one of the values is the wrong type, like text where Excel needed a number.
Starting in H6, type these rows (one value per cell, moving right):
|
Item not found|=MATCH("Sprocket",A4:A7,0)| |---|---|Starting in H7, type these rows (one value per cell, moving right):
|
Item found|=MATCH("Gizmo",A4:A7,0)| |---|---|AutoFit the width of column H:I (double-click its right edge)
Sprocket gives hash N slash A, which means not available. Excel looked, and the thing just isn't there. And Gizmo gives three, because it's the third product in the list.
IFERROR and IFNA#
In E3 type
Per unit safeIn E4 type the formula
=IFERROR(B4/C4,0)Fill E4:E7 down (drag the fill handle, or select it and press Ctrl+D)
Starting in H8, type these rows (one value per cell, moving right):
|
Not found, handled|=IFNA(MATCH("Sprocket",A4:A7,0),"Not found")| |---|---|
The safe column shows twenty five, zero, twenty six and forty. No more error. And the MATCH now politely says Not found.
Starting in H9, type these rows (one value per cell, moving right):
|
Typo hidden|=IFERROR(SUMM(B4:B7),0)| |---|---|AutoFit the width of column E:I (double-click its right edge)
It shows zero. No error, no warning, just a wrong answer that looks perfectly reasonable. IFERROR hid a mistake in my formula, not just a problem in the data.
Named ranges#
Starting in A10, type these rows (one value per cell, moving right):
|
GST rate|10%| |---|---|Name B10 as
GST_Rate(type it in the Name Box, or Formulas → Define Name)
- In F3 type
GST - In F4 type the formula
=B4*GST_Rate - Fill F4:F7 down (drag the fill handle, or select it and press Ctrl+D)
One hundred and twenty, ninety five, seventy eight and sixty four dollars of GST. And here's a bonus. A named cell is automatically an absolute reference. We filled the formula down without any dollar signs, and every row still points at B10.
Name F4:F7 as
GST_Amount(type it in the Name Box, or Formulas → Define Name)Starting in A12, type these rows (one value per cell, moving right):
|
Total GST|=SUM(GST_Amount)| |---|---|Format F4:F7: number format currency
Three hundred and fifty seven dollars of GST in total.
Showing and tracing formulas#
- Press Ctrl+Backtick
- Press Ctrl+Backtick
- Click F5
- On the ribbon choose Formulas > Trace Precedents
- On the ribbon choose Formulas > Remove Arrows
Project: staff commission and bonus report#
Sheet: Commission
In A1 type
Staff Commission and Bonus ReportFormat A1: bold, font size 16
Starting in A3, type these rows (one value per cell, moving right):
|
Name|Region|Sales|Target|% of target|Rate|Commission|Target met|Bonus|Total pay| |---|---|---|---|---|---|---|---|---|---|
Starting in A4, type these rows (one value per cell, moving right):
|
Priya Sharma|North|112437.6|100000| |---|---|---|---| |James Wilson|South|68254.35|70000| |Mei Chen|West|91806.2|85000| |Liam Walsh|North|45612.8|50000| |Sofia Rossi|South|103915.6|95000|
Starting in the next empty row, type these rows (one value per cell, moving right):
|
Noah Taylor|West|38750.45|45000| |---|---|---|---| |Aisha Khan|North|76318.9|75000| |Tom Nguyen|South|59920.15|55000| |Grace Miller|West|84162.85|90000| |Ethan Brown|North|71205.4|65000|
Starting in L3, type these rows (one value per cell, moving right):
|
Bonus rate|1%| |---|---|Starting in L4, type these rows (one value per cell, moving right):
|
Bonus minimum|60000| |---|---|
- Name M3 as
BonusRate(type it in the Name Box, or Formulas → Define Name) - Name M4 as
BonusMin(type it in the Name Box, or Formulas → Define Name) - Format M3:M4: fill Yellow
- In E4 type the formula
=C4/D4 - Fill E4:E13 down (drag the fill handle, or select it and press Ctrl+D)
- Format E4:E13: number format percent
Priya is at a hundred and twelve percent of her target. James ninety eight, Mei a hundred and eight, Liam ninety one, Sofia a hundred and nine, Noah eighty six, Aisha a hundred and two, Tom a hundred and nine, Grace ninety four, and Ethan a hundred and ten percent.
- In F4 type the formula
=IFS(C4>=100000,8%,C4>=80000,6%,C4>=60000,4%,TRUE,2%) - Fill F4:F13 down (drag the fill handle, or select it and press Ctrl+D)
- Format F4:F13: number format percent
Priya and Sofia, both over a hundred thousand, get eight percent. Mei and Grace get six. James, Aisha and Ethan get four. And Liam, Noah and Tom, all under sixty thousand, get two percent. Remember why the order matters. If we'd tested sixty thousand first, Priya would have stopped there and only got four percent.
- In G4 type the formula
=ROUND(C4*F4,2) - Fill G4:G13 down (drag the fill handle, or select it and press Ctrl+D)
Priya earns eight thousand nine hundred and ninety five dollars and one cent in commission. James earns two thousand seven hundred and thirty dollars seventeen. Without ROUND, his commission would be two seven three zero point one seven four, a tenth of a cent that can't actually be paid. Mei earns fifty five oh eight thirty seven, Sofia eighty three thirteen twenty five, and Noah, on two percent, seven hundred and seventy five dollars and one cent.
- In H4 type the formula
=IF(C4>=D4,"Yes","No") - Fill H4:H13 down (drag the fill handle, or select it and press Ctrl+D)
Six people said Yes. Priya, Mei, Sofia, Aisha, Tom and Ethan. James, Liam, Noah and Grace fell short.
- In I4 type the formula
=IF(AND(C4>=D4,C4>=BonusMin),ROUND(C4*BonusRate,2),0) - Fill I4:I13 down (drag the fill handle, or select it and press Ctrl+D)
Priya gets a bonus of eleven hundred and twenty four dollars thirty eight. Mei gets nine eighteen oh six, Sofia ten thirty nine sixteen, Aisha seven sixty three nineteen, and Ethan seven twelve oh five. And look at Tom. He met his target, but his sales of fifty nine thousand nine hundred and twenty are just under the sixty thousand minimum. AND needs both conditions, so Tom's bonus is zero. That's exactly what the rules say.
- In J4 type the formula
=G4+I4 - Fill J4:J13 down (drag the fill handle, or select it and press Ctrl+D)
Priya tops the list with ten thousand one hundred and nineteen dollars thirty nine in total pay, then Sofia with nine thousand three hundred and fifty two forty one.
Starting in A14, type these rows (one value per cell, moving right):
|
Total|| =SUM(C4:C13)|=SUM(D4:D13)| |---|---|---|Starting in G14, type these rows (one value per cell, moving right):
|
=SUM(G4:G13)|| =SUM(I4:I13)|=SUM(J4:J13)| |---|---|---|
- Format C4:D14: number format currency
- Format G4:G14: number format currency
- Format I4:J14: number format currency
- Format A3:J3: bold, font colour White, Background 1, fill Blue, Accent 1
- Format A14:J14: bold, double bottom border
- AutoFit the width of column B:J (double-click its right edge)
- Format A4: column width 14
Team sales are seven hundred and fifty two thousand three hundred and eighty four dollars thirty, against a combined target of seven hundred and thirty thousand. Commission adds up to thirty nine thousand three hundred and eighty three dollars twenty two, bonuses to four thousand five hundred and fifty six eighty four, and total pay to forty three thousand nine hundred and forty dollars and six cents.
Regional totals with SUMIFS#
Starting in L6, type these rows (one value per cell, moving right):
|
Region|Sales|Total pay| |---|---|---|
Starting in M7, type these rows (one value per cell, moving right):
|
=SUMIFS($C$4:$C$13,$B$4:$B$13,L7)|=SUMIFS($J$4:$J$13,$B$4:$B$13,L7)| |---|---|Fill M7:N9 down (drag the fill handle, or select it and press Ctrl+D)
Format M7:N9: number format currency
Make L6:N6 bold (Ctrl+B)
The North sold three hundred and five thousand five hundred and seventy four dollars seventy, and earns eighteen thousand four hundred and seven eighty seven in pay. The South sold two hundred and thirty two thousand and ninety dollars ten, with thirteen thousand two hundred and eighty dollars ninety eight in pay. And the West sold two hundred and fourteen thousand seven hundred and nineteen fifty, with twelve thousand two hundred and fifty one twenty one in pay. Together the regions add back to our total pay exactly.
Starting in L11, type these rows (one value per cell, moving right):
|
Targets met|=COUNTIF(H4:H13,"Yes")| |---|---|Starting in L12, type these rows (one value per cell, moving right):
|
Highest pay|=MAX(J4:J13)| |---|---|Starting in L13, type these rows (one value per cell, moving right):
|
North on target|=SUMIFS(C4:C13,B4:B13,"North",H4:H13,"Yes")| |---|---|Format M12:M13: number format currency
AutoFit the width of column L:N (double-click its right edge)
Six staff met their target. The highest pay is ten thousand one hundred and nineteen thirty nine. And North sales from staff on target are two hundred and fifty nine thousand nine hundred and sixty one ninety, from Priya, Aisha and Ethan.
Summary sentences that write themselves#
- In A16 type
Summary - In A17 type the formula
="Team sales of "&TEXT(C14,"$#,##0")&" against a target of "&TEXT(D14,"$#,##0")&"." - In A18 type the formula
=M11&" of "&COUNTA(A4:A13)&" staff met their target."
Team sales of seven hundred and fifty two thousand three hundred and eighty four dollars against a target of seven hundred and thirty thousand dollars. Six of ten staff met their target.
- In A19 type the formula
="Total payout: "&TEXT(J14,"$#,##0.00")&", including "&TEXT(I14,"$#,##0.00")&" in bonuses." - In A20 type the formula
=A4&" earned "&TEXT(J4,"$#,##0.00")&" at "&TEXT(E4,"0%")&" of target." - Make A16 bold (Ctrl+B)
Total payout, forty three thousand nine hundred and forty dollars and six cents, including four thousand five hundred and fifty six dollars eighty four in bonuses. And Priya Sharma earned ten thousand one hundred and nineteen dollars thirty nine at a hundred and twelve percent of target. Change the data, and the sentences rewrite themselves.
Highlights and a chart#
- Conditional formatting on J4:J13: highlight the top 3 values
- Conditional formatting on H4:H13: highlight cells containing “Yes”
Priya, Sofia and Mei light up as our three highest earners. And every Yes in the target met column is now green, so you can see at a glance who hit their number.
- Select L6:M9 and insert a column chart titled “Sales by region”, no legend
The North clearly leads, with the South and West close behind each other. And because the chart is linked to the SUMIFS formulas, it updates whenever the data does.
What if the rules change?#
- In M4 type
50000
Tom now qualifies. He met his target, and fifty nine thousand nine hundred and twenty is above fifty thousand, so he gets a bonus of five hundred and ninety nine dollars twenty, and his total pay rises to one thousand seven hundred and ninety seven sixty. The bonus total becomes five thousand one hundred and fifty six dollars and four cents, total pay becomes forty four thousand five hundred and thirty nine twenty six, and the South's pay and the summary sentence update too. One cell changed, and the whole report followed.
- Press Ctrl+Z
- Press Ctrl+Backtick
- Press Ctrl+Backtick
- Press Ctrl+Home
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.



