Excel Formulas and Functions Tutorial

Aug 13, 2023

Excel Formulas and Functions Tutorial

Learn how to use formulas in Excel, including the following functions \u0026 formulas:

⌚ Timestamps
0:00 Introduction
0:55 Add
1:55 Subtract
2:18 Multiply
2:50 Divide
3:20 Sum
4:46 Min
5:05 Max
5:30 Count
6:15 Counta
6:48 Average
7:20 Median
7:54 Concatenate
8:29 If
9:10 Countif
9:40 Vlookup
10:58 Sumif
11:46 Current Time

📃 Watch related playlists and videos
- Excel:    • ❎ How to use Excel
- Pivot Tables:    • Pivot Table Excel Tutorial
- Vlookup \u0026 hlookup:    • VLOOKUP \u0026 HLOOKUP in Excel Tutorial
- Mail Merge:    • How to Mail Merge in Word, Excel \u0026 Ou…
- Learn the fundamentals of Excel in just 2 hours: https://kevinstratvert.thinkific.com

Thinking about subscribing to Office 365? You get the Word, Excel, and PowerPoint desktop apps and up to 6 TB cloud storage. Check the latest pricing on Amazon: https://amzn.to/2UgyoJ4 (as a disclaimer, purchasing through this link gives me a small commission to support videos on this channel — the price to you is the same).

Let me know if you have any questions. Also, let me know if there are other topics that you would like to see videos on.

🚩 Connect with me on social
- LinkedIn: https://www.linkedin.com/in/kevinstra…
- Discord: https://discord.gg/kHuujY2pZf
- Twitter: https://twitter.com/kevstrat
- Facebook: https://www.facebook.com/Kevin-Stratv…
- TikTok: https://www.tiktok.com/@kevinstratvert
- Instagram: https://www.instagram.com/kevinstratv…

🙏 Request How To Tutorial Videos
https://forms.gle/BDrTNUoxheEoMLGt5

🔔 Subscribe to my YouTube channel
https://www.youtube.com/user/kevlers?…

🎬 Want to watch again? Navigate back to my YouTube channel quickly
http://www.kevinstratvert.com

🛍 Support me with your Amazon purchases: https://amzn.to/3kCP2yz

⚖ As full disclosure, I use affiliate links above. Purchasing through these links gives me a small commission to support videos on this channel — the price to you is the same.

#stratvert #excel

Content

0.4 -> Hi everyone. My name is Kevin. Today I want to show you how you can use formulas in Microsoft

6.48 -> Excel, and as full disclosure, before we jump into this, I work at Microsoft as a full-time employee.

11.76 -> All right, well, let's jump into it. Here I am in Microsoft Excel. This is the latest version.

17.04 -> I have Office 365, but these same formulas should work on any older versions of Excel.

22.48 -> Well, whether you have 2019, 2016, 2013, 2007, you name the version, it'll probably work.

29.28 -> So, here I am in Microsoft Excel,

31.36 -> and I have a list of formulas that I want to show you how you can use them in Excel.

35.28 -> If there's one that interests you more than others,

37.68 -> feel free to jump to that section of the video.

39.68 -> I have time stamps down below in the comments and you could just jump to

42.96 -> the section that you find interesting. So how do you do Excel or formulas in Excel.

50.24 -> We’re going to start with the basics of add, subtract, multiply, and divide.

54 -> So, let's say that I have two numbers in cells. I have 1 and 1 and I want to add these up.

59.12 -> How do I do that?

60 -> Well, the key to doing formulas in Excel is you always start with the equal sign.

64.4 -> So here I'm going to enter equal and what I could do is I could say well what is 1 plus 1?

69.36 -> So I could just enter that in and you'll see that shows up in this formula bar up above.

75.28 -> So, what I'm going to do now is I'm just going to hit enter

77.44 -> and you'll see that it added those two values up.

79.6 -> So, it says equals 1 + 1.

82.08 -> Now what I can also do is instead of typing the numbers into that formula bar,

86.24 -> I could use numbers that are on my spreadsheet.

88.88 -> So, what I'm going to do is I'm going to go ahead and delete that and once again we're

91.52 -> going to start with the equal sign and instead, I'm going to say, hey, this value here that's in

96 -> cell B1, let's take that and I want to add it to the value that's here in cell C1,

102.48 -> and so I did the plus sign and then C1 and so here you see it says B1 + C1. So,

107.12 -> it's taking those two cells and now I'm going to hit enter and so it added those two up.

112.16 -> And now I'm going to show a few examples.

113.84 -> Subtract, multiply, divide.

115.36 -> You do them all the same way as addition.

117.36 -> So, let's say I want to do 2 - 1.

120 -> What I'll do is I'll do equals.

121.76 -> We could do 2 - 1 and hit enter and that'll tell us it's one.

125.04 -> But once again, I want to use the cells. So, we're going to do cell B2 and we're

129.28 -> going to subtract cell C2, and then I’m going to hit enter and what do you know 2 – 1 is 1.

136.32 -> Big newsflash there.

138.16 -> And now we're going to do a few other examples. We're going to do 2 * 2,

141.84 -> and then I'll do we'll do 4 / 2.

144.48 -> So, let's do 2 * 2. So once again, we're going to enter the equal sign,

148.88 -> and we're going to take cell B3 and then multiply. You can't enter an X here like

154.56 -> the multiplication sign. Instead, what we're going to do is we enter the asterisk sign.

159.12 -> And then we're going to multiply that by cell C3. You don't have to enter,

162.96 -> you could also enter the cell values, or you can just click with your mouse.

166.24 -> Clicking with the mouse is easier, so why don't we do that?

169.04 -> So, I'm going to click that and 2 * 2 is 4 and here same with division.

174.08 -> I'm going to do 4 and then we're going to see how many times two goes into four.

178.8 -> The division sign on the computer is the forward slash, so I'm going to enter the

182.48 -> forward slash and then we're going to click on cell C4 and so we're going to do B4 / C4.

188.72 -> And what is 4 / 2 everyone? It is 2. 2 goes into 4 or 2 times. So, add, subtract,

194.88 -> multiply, divide those are all pretty straight forward. Now, let's say a little more difficult.

200.24 -> Let's say that I wanted to add all these values up.

203.04 -> Now once again you could enter a formula where you say OK well 1 + 1 + 2.

207.52 -> It's going to take a long time.

208.64 -> We have a lot of different cells, so we don't want to do that.

211.6 -> I could also say, you know this plus this, plus this, plus this, but that also is going to take

218 -> a long time and we don't want to do that, so that's where the sum formula comes in.

221.84 -> It makes things a little bit easier for us and what you could do is you do

224.88 -> equals again and then we type in sum and then I'm going to do open parentheses,

230.8 -> and then you'll here it's giving you a little hint of what you need to do.

233.44 -> So number 1, number 2, so here I could go in and I could say you know this one and then it says

238.24 -> insert a comma, so comma, comma, so I could do that, but that's still not that good.

242.88 -> So, we're going to go to equals sum and then open parentheses, and then I could just highlight all

249.12 -> these numbers, so I'm just clicking with my mouse and dragging it, and then I'm going

252.88 -> to close the parentheses at the end, and I hit enter and that adds up all the values.

256.48 -> It's 24, so super easy to do.

258.96 -> What I could also do is I could click on this formula sign, and I could say

262.56 -> sum and if you want help writing your formula, so here too you could see kind of how it's done,

268.72 -> but here I could drag all the numbers in that I want to sum and then I can click OK.

273.44 -> So, if you'd rather not kind of deal with opening and shutting your parentheses,

277.52 -> that's another way you could do it. But here you see that the value is 24 and we're good there.

284.56 -> Some of the other things I could do min that gives me the minimum value above.

288.88 -> So here are all these values,

290.72 -> just by glancing at it, you can see that the minimum value I have here is 1.

294.4 -> But what I could do is I could just type in min and then we're going to open my parentheses.

300.08 -> I'm going to highlight all my numbers that I want to min for

302.96 -> and then I'm going to close my parentheses.

304.64 -> Now so you tend to do that a lot when you enter a

306.72 -> formula. You have a parentheses open and the parentheses closed,

309.76 -> I'm going to enter that. The minimum value there is 1. We could see what the max is.

314.4 -> So here I'm going to highlight all these same numbers and then close

317.44 -> the parentheses and the max value that I have here is 4.

320.56 -> If I now were to change this to 10, you'll see that this automatically updates to 10.

325.76 -> Similarly, if I were to change that to 0,

327.52 -> that would automatically say the minimum value is now 0.

331.44 -> OK, now we want to look at count, and so what count does

335.04 -> is you can basically count all the cells that have a number in them.

338.88 -> So, what I'll do is I'll say let's count and I want to know in this, so in this set,

344.64 -> let's say these set this set of numbers, how many, how many cells are there that have a number?

349.76 -> So, I highlighted all those.

351.04 -> I'm going to close the parentheses

352.16 -> again and I'm going to hit enter and so it says there are 12 numbers.

354.72 -> Well that make sense because 1234 and then another 4 and then another 4 gives me 12.

360.8 -> What's interesting is it only looks for cells that have a number in it.

364 -> So, you know, here I could go, and I could highlight all these, close the parentheses.

368.32 -> It's still only telling me 12 because all these other cells that I copied have no value in it.

373.6 -> Now there's also called something called counta and that counts all

376.8 -> the cells that even just have a value in them.

379.2 -> So, we'll go equals again, counta and then I'm going to highlight.

384.64 -> Let's highlight all these cells and then I'll close it.

387.12 -> There too there are 12, but let's say I type in my name here,

391.36 -> so it's counting any cell that has a value.

393.84 -> So that counts Kevin, but the count formula is only counting cells with numbers.

399.68 -> So that's why it didn't pick up the Kevin, and then here I could type in another value.

403.04 -> Let's say I type in the city I live in Seattle, and so you see the counta is picking up that cell.

408.4 -> All right, so moving on down here, we want to know the average now, so we're going to change it up

412.48 -> a little bit, I'm going to type in, let's type in some salaries, let's say that these are different

418.48 -> income levels, and then I'm just going to throw on the dollar sign, so they actually look like

425.2 -> dollar amounts, and so let's say I want to know the average income.

428.56 -> I just type in average. Super easy. The name of the formula matches what you're trying to do

433.92 -> and then I'll just highlight those incomes, and then so the average is $42,500,

439.2 -> you'll notice that the $100,000 is pulling the average up, and then you could also do the median.

444.32 -> The median is the middle most value, so you take all your values, and you line them up.

448.08 -> What is the value in the middle?

449.84 -> So here I'm going to select these values and we're going to see what the median is.

453.84 -> It looks like the median is at $27,500. If I threw in let's say another value,

460.08 -> let's extend that one more, then it'll pick the middle most value. So, there's $30,000.

465.76 -> One thing you'll notice is the average is just an average across all the numbers.

469.84 -> The median is the middle most value, so the median is lower than the average.

474.16 -> What I could also do concatenate.

475.76 -> So, my name is Kevin Stratvert and let's say I wanted to concatenate them,

480.88 -> concatenate basically means taking one and connecting it to another.

484.72 -> So, I could say concat and I'll take this value and then we're going to enter a comma.

489.52 -> It's telling me to put a comma there between the values,

492.08 -> and then I'll close the parentheses, and there it's filled it in.

495.44 -> Although I might want let's say let's say I want to add a space in there,

498.72 -> so I'm going to add a space in that cell.

500.8 -> You can concatenate as many values as you want, so here I could also bring in that value,

505.92 -> and that brought in this space, so that's concatenate. If is pretty nice.

510.24 -> This is bringing logic into Excel.

512.64 -> So, what I'm going to do is, let's say this is smile.

516.24 -> So, what I can say is if and what I want to do is if, let's say, this cell equals smile, then what I

527.52 -> want to do is I want to say yay and if it doesn't so the value of true and the value of false.

533.44 -> So, if it's not true and it doesn't contain that, then I'm going to say boo,

537.28 -> and so here it's smile, so it says yay, and let's say it's frown, then it says Boo.

541.76 -> So, there you can see how you can include an if statement to

544.32 -> basically say you know if this cell contains something, do this or that.

548.72 -> I can also countif, so this is kind of interesting.

552.88 -> What I could do is so let's say countif, and I could say you know of this range,

560.88 -> so I'll say I want to count, let's say here I want to count the number of cells that have a 10 in it.

569.36 -> OK, so basically you say, hey this is my range and then what's my criteria?

573.12 -> The criteria is what I'm looking for and so I'm looking for a 10

576.72 -> and here it tells me within this range only one cell has a 10 in it, so it tells me 1.

583.12 -> A few others and these are getting more advanced as we go on.

586.96 -> VLOOKUP so I'm going to show a quick example here.

589.92 -> I'm going to type in a few names, and we're going to put down our favorite colors, so I like red,

595.2 -> which is why I'm wearing this red shirt, and Alex likes blue, and Steven likes yellow.

600.96 -> So with VLOOKUP, what I could say is I want to look across these values

604.4 -> and I want to return back this other column, so let's show a quick example.

608.08 -> I also have a video on VLOOKUP and HLOOKUP that goes more in depth if you want a deeper example.

613.2 -> So I could say I want to look up Kevin. So you read the look up value and then table array.

618.64 -> That's just the table that I'm looking in.

620.8 -> I'm going to do a comma. This is the table that I'm looking at.

623.92 -> So, I'm going to highlight that table and I want to get the color back.

626.88 -> It says column index number, well that's, that's the column that I want to get back.

630.96 -> So, I'm going to say, well the second column

633.2 -> and then the last thing is do I want an exact, approximate match or exact match?

636.88 -> What that means is, you know, if my name was Kevin something an approximate match would match.

640.88 -> But I want to do an exact match,

642.08 -> so I'll just click on that, and then we'll close parentheses and so here it returns

646.56 -> Kevin’s favorite color is red, so it looks for the value here and then it returns the value here.

651.6 -> That's what I could do with VLOOKUP.

656.88 -> OK, with sumif, what I want to do is let's say that we have these numbers associated with these

662.08 -> different people and I only want to add up the number when it's associated with Kevin.

666.32 -> What I could do is we'll do sumif and I want to look at this range and the criteria I want

672.8 -> to look for is that it's Kevin, and then this is my sum range that I'm looking at.

678 -> And so what this does is it says, hey, you know,

680 -> we have two Kevins and the value associated with them is 10 and then 5 and it sums them.

685.52 -> So, let's take a look at that again.

687.12 -> So here is if I go through the formula again.

691.36 -> Sumif I want to know the range, that's my range.

694.16 -> The criteria is that it equals Kevin,

697.12 -> and then I want to add up these values every time there's a Kevin.

700.64 -> And so, there are two here, 10 and a 5, and so I'll hit enter, and so it's 15,

704.16 -> so that's doing sumif, and then the last one. Super simple to finish off with.

708.24 -> If I want to know the current time, I just do equals now and then open and close parentheses

712.56 -> and that tells me the exact time today is July 12, 2019, 1:18 in the afternoon Pacific Standard Time.

718.56 -> That's when I created this video and that's when we're finishing the video, but that's

722.16 -> just a very quick tutorial of how you can enter formulas and how you could use formulas in Excel.

729.52 -> I hope you enjoyed this video, if you did, please give it a thumbs

732 -> up. If you want to see future videos like this, hit that subscribe button,

735.12 -> that way you'll get a notification anytime new content like this comes out,

738.56 -> and if you want to see more videos, more tutorials, leave a comment down below.

742.48 -> Let me know what you're looking for and I'll add it to my list of videos to make.

745.76 -> All right, well I'll see you next time. Thanks a lot, and bye.

Source: https://www.youtube.com/watch?v=Jl0Qk63z2ZY