YouTube Transcript:
Excel Formulas and Functions Tutorial

Skip watching entire videos - get the full transcript, search for keywords, and copy with one click.

Video Transcript

View:

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

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

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

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

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

So, here I am in Microsoft Excel,  

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

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

feel free to jump to that section of the video.

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

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

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

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

How do I do that?

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

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

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

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

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

So, it says equals 1 + 1.

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

I could use numbers that are on my spreadsheet.

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

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

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

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

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

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

Subtract, multiply, divide.

You do them all the same way as addition.

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

What I'll do is I'll do equals.

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

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

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

Big newsflash there.

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

and then I'll do we'll do 4 / 2.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

It's going to take a long time.

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

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

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

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

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

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

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

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

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

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

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

It's 24, so super easy to do.

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

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

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

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

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

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

So here are all these values,  

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

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

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

and then I'm going to close my parentheses.

Now so you tend to do that a lot when you enter a  

formula. You have a parentheses  open and the parentheses closed,  

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

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

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

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

Similarly, if I were to change that to 0,  

that would automatically say  the minimum value is now 0.

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

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

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

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

So, I highlighted all those.

I'm going to close the parentheses  

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

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

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

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

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

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

the cells that even just have a value in them.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

What is the value in the middle?  

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

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

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

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

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

What I could also do concatenate.

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

concatenate basically means taking  one and connecting it to another.  

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

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

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

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

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

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

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

This is bringing logic into Excel.

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

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  

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

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

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

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

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

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

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

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.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

But I want to do an exact match,  

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

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

That's what I could do with VLOOKUP.

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

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

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

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

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

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

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

So here is if I go through the formula again.

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

The criteria is that it equals Kevin,  

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

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

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

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

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

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

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

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

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

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

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

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

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

Click on any text or timestamp to jump to that moment in the video

Most transcripts ready in under 5 seconds

One-Click Copy125+ LanguagesSearch ContentJump to Timestamps

Paste YouTube URL

Enter any YouTube video link to get the full transcript

Most transcripts ready in under 5 seconds

Get Our Chrome Extension

Get transcripts instantly without leaving YouTube. Install our Chrome extension for one-click access to any video's transcript directly on the watch page.

Add to Chrome — Free

Works with YouTube, Coursera, Udemy and more educational platforms

Get Instant Transcripts: Just Edit the Domain in Your Address Bar!

YouTube

←

→

↻

https://www.youtube.com/watch?v=UF8uR6Z6KLc

YoutubeToText

←

→

↻

https://youtubetotext.net/watch?v=UF8uR6Z6KLc

YouTube Transcript:Excel Formulas and Functions Tutorial

Video Transcript

Paste YouTube URL

Transcript Extraction Form

Get Our Chrome Extension

Get Instant Transcripts: Just Edit the Domain in Your Address Bar!

YouTube Transcript:
Excel Formulas and Functions Tutorial