Well, we've covered some interesting basics already in this 'Don't Fear The Spreadsheet' series, but today Tyler and Bill are really stepping out onto new ground! Today, Bill is going to show the process of creating your first Formula in Microsoft Excel! If you need to learn the way, today is a great start to get you launched!
Many 'Beginner Oriented' Excel How-To books say they can bring you from zero to familiar with the important features of Microsoft Excel -- 'Don't Fear The Spreadsheet' actually will. Why are we so confident? Because the questions in this book were asked by an absolute Excel Beginner -- Tyler Nash -- with the answers provided by three (3) Microsoft MVPs specializing in and dedicated to teaching others Microsoft Excel, from the ground up -- Kevin Jones, Tom Urtis and Bill Jelen
"The Learn Excel from MrExcel Podcast Series"
Visit us: MrExcel.com for all of your Microsoft Excel Needs!
Many 'Beginner Oriented' Excel How-To books say they can bring you from zero to familiar with the important features of Microsoft Excel -- 'Don't Fear The Spreadsheet' actually will. Why are we so confident? Because the questions in this book were asked by an absolute Excel Beginner -- Tyler Nash -- with the answers provided by three (3) Microsoft MVPs specializing in and dedicated to teaching others Microsoft Excel, from the ground up -- Kevin Jones, Tom Urtis and Bill Jelen
"The Learn Excel from MrExcel Podcast Series"
Visit us: MrExcel.com for all of your Microsoft Excel Needs!
Transcript of the video:
MrExcel podcast is sponsored by Easy-XL.
Don’t Fear The Spreadsheet, Podcast Episode Number 9: A Formula.
Bill: Hey. Welcome back to the MrExcel netcast. I'm Bill Jelen from mrexcel.com.
Tyler Nash asked today's question. I knew that we'd get to this point.
The book aims to take someone who's at level 1 and move them to level 4.
Level 1, all I do, open an attachment and click print.
Level 2, they start to learn how to do some formatting, alright?
So, some simple little cell styles here and so on, but that gets you to the 40th percentile.
Beyond that, you have to do…well, I'll let Tyler describe it.
Tyler: Okay.
So, I've entered some data into my spreadsheet and now I want to add some additional values based on the data already entered.
I've heard that you can do this with a formula.
So, how do I build a formula?
Bill: Alright, Tyler.
I'm glad you asked, right?
This separates casual use of Excel from powerful use of Excel, and it's the reason that spreadsheets were invented, the whole formula, alright?
So, here we have a department luncheon plan, we've got everyone's order, and we need to figure out how much petty cash we're going to need for this.
Alright.
Everyone who gets the sandwich gets chips.
So, I need to add up all of those cells into the chips, alright?
7, 10, 13, 14.
No, no.
Don't type it.
No.
Don't use a calculator.
Don't use [ unintelligible – 01:46 ] machine.
This formula right here to add up the numbers just above, we’re going to use the AUTOSUM.
AUTOSUM is this funny-looking Greek E out here, the letter Σ, I click on that, and Excel proposes a formula.
It's going to sum everything from B4 : B7.
Everything in the marching ants is going to be summed.
Beautiful.
Click ENTER.
There we go, and now if someone comes along and says, hey, no, I don't want a meatball sub, I want a BMT, and we change that, that formula updates just like that.
Cool, huh?
Drinks.
Everyone who gets chips gets a drink.
So, I just need to pull that number down.
Alright.
So, this is going to be a regular formula.
All formulas have to start with an = sign and we're actually not going to use any operator at all, so we’ll hold off on that, type an = sign, use the mouse, click on the cell we want to copy or pull the value from, and ENTER, alright?
So, now, if we change…someone comes along and says, hey, I'm not going to make the meeting, cancel my meatball sub, alright, see, that number updates and then that number updates.
Sweet.
Alright?
Okay.
Operators.
Addition, + sign, just like you'd expect, subtraction, - sign, ah, but multiplication, it's not an X, it's the *, division, not the little ÷ sign, ÷ sign, it is a regular /. These are kind of specialized.
We'll talk about these later.
Join text with an &, exponents with a ^. All formulas have to start with an = sign.
So, let's see if we can do something a little more fancy to figure out the total cost, alright?
So, we come out here.
All formulas start with what?
That's right =. Alright.
So, we want the HOW MANY, and then I use on the keyboard times the *, the COST EACH, and press ENTER.
There we go.
There's our formula.
7 * 6 is 42.
[ =B4*C4 ] Alright.
Now, this is really cool.
The formula for this cell is just like the formula for that cell, alright?
It’s HOW MANY of this row * COST EACH of this row, so I don't have to re-enter it.
That little square dot there is called the fill handle, and I'll click on the fill handle, I'm going to drag it down, and it's going to create the same similar formula all the way down.
Now, here we have a problem.
1 * 350, they're rounding it off.
I want to see the 2 decimal places.
So, we'll go back and do a little bit of cell formatting there.
Alright.
So, now, someone comes along and says, now, hey, get me a meatball sub, that number updates, or Subway calls and says, hey, the sandwich of the month is the turkey sandwich.
It's only 5 bucks.
So, we change that and the number updates.
Cool, right?
Subtotal.
We want to add up everything above us.
What's that again?
AUTOSUM.
Come out here, click the AUTOSUM, it shows us what numbers it's going to add up, and we'll press enter.
Alright.
Now, I know, Subway, you don't tip the guy at Subway, the sandwich artist, but our Subway actually delivers.
So, we do give the guy a 15% tip.
To calculate a 15% tip, it’s =0.15* and I'll click on the cell here, and there's our tip.
Now, total.
I could maybe use the AUTOSUM to add up the 2 numbers just above me, but, in this case, I'm just going to use the + sign, so = this cell, the SUBTOTAL, + the TIP.
There we go, 117.25, and the beautiful thing about spreadsheets and formulas is as the data changes, right, as Joe calls and says, I changed my mind, I don't want to a BMT, I want a turkey, I change those 2 numbers and everything calculates right down throughout the whole spreadsheet.
[ =.15*D10 ], [ =D10+D11 ] That's the reason that Dan Bricklin and Bob Frankston invented a spreadsheet back in 1978.
They were tired of using a calculator to do the same types of calculations over and over again.
That product was called VisiCalc and it's still, in my opinion, the reason why we have spreadsheets today.
Change some input cells, all of the formulas update, and we're good to go.
Tyler: Thanks for stopping by.
We'll see you next episode.
Check out Don't Fear The Spreadsheet.
This book [ unintelligible – 05:50 ] look like it was written for rocket scientists.
Don’t Fear The Spreadsheet, Podcast Episode Number 9: A Formula.
Bill: Hey. Welcome back to the MrExcel netcast. I'm Bill Jelen from mrexcel.com.
Tyler Nash asked today's question. I knew that we'd get to this point.
The book aims to take someone who's at level 1 and move them to level 4.
Level 1, all I do, open an attachment and click print.
Level 2, they start to learn how to do some formatting, alright?
So, some simple little cell styles here and so on, but that gets you to the 40th percentile.
Beyond that, you have to do…well, I'll let Tyler describe it.
Tyler: Okay.
So, I've entered some data into my spreadsheet and now I want to add some additional values based on the data already entered.
I've heard that you can do this with a formula.
So, how do I build a formula?
Bill: Alright, Tyler.
I'm glad you asked, right?
This separates casual use of Excel from powerful use of Excel, and it's the reason that spreadsheets were invented, the whole formula, alright?
So, here we have a department luncheon plan, we've got everyone's order, and we need to figure out how much petty cash we're going to need for this.
Alright.
Everyone who gets the sandwich gets chips.
So, I need to add up all of those cells into the chips, alright?
7, 10, 13, 14.
No, no.
Don't type it.
No.
Don't use a calculator.
Don't use [ unintelligible – 01:46 ] machine.
This formula right here to add up the numbers just above, we’re going to use the AUTOSUM.
AUTOSUM is this funny-looking Greek E out here, the letter Σ, I click on that, and Excel proposes a formula.
It's going to sum everything from B4 : B7.
Everything in the marching ants is going to be summed.
Beautiful.
Click ENTER.
There we go, and now if someone comes along and says, hey, no, I don't want a meatball sub, I want a BMT, and we change that, that formula updates just like that.
Cool, huh?
Drinks.
Everyone who gets chips gets a drink.
So, I just need to pull that number down.
Alright.
So, this is going to be a regular formula.
All formulas have to start with an = sign and we're actually not going to use any operator at all, so we’ll hold off on that, type an = sign, use the mouse, click on the cell we want to copy or pull the value from, and ENTER, alright?
So, now, if we change…someone comes along and says, hey, I'm not going to make the meeting, cancel my meatball sub, alright, see, that number updates and then that number updates.
Sweet.
Alright?
Okay.
Operators.
Addition, + sign, just like you'd expect, subtraction, - sign, ah, but multiplication, it's not an X, it's the *, division, not the little ÷ sign, ÷ sign, it is a regular /. These are kind of specialized.
We'll talk about these later.
Join text with an &, exponents with a ^. All formulas have to start with an = sign.
So, let's see if we can do something a little more fancy to figure out the total cost, alright?
So, we come out here.
All formulas start with what?
That's right =. Alright.
So, we want the HOW MANY, and then I use on the keyboard times the *, the COST EACH, and press ENTER.
There we go.
There's our formula.
7 * 6 is 42.
[ =B4*C4 ] Alright.
Now, this is really cool.
The formula for this cell is just like the formula for that cell, alright?
It’s HOW MANY of this row * COST EACH of this row, so I don't have to re-enter it.
That little square dot there is called the fill handle, and I'll click on the fill handle, I'm going to drag it down, and it's going to create the same similar formula all the way down.
Now, here we have a problem.
1 * 350, they're rounding it off.
I want to see the 2 decimal places.
So, we'll go back and do a little bit of cell formatting there.
Alright.
So, now, someone comes along and says, now, hey, get me a meatball sub, that number updates, or Subway calls and says, hey, the sandwich of the month is the turkey sandwich.
It's only 5 bucks.
So, we change that and the number updates.
Cool, right?
Subtotal.
We want to add up everything above us.
What's that again?
AUTOSUM.
Come out here, click the AUTOSUM, it shows us what numbers it's going to add up, and we'll press enter.
Alright.
Now, I know, Subway, you don't tip the guy at Subway, the sandwich artist, but our Subway actually delivers.
So, we do give the guy a 15% tip.
To calculate a 15% tip, it’s =0.15* and I'll click on the cell here, and there's our tip.
Now, total.
I could maybe use the AUTOSUM to add up the 2 numbers just above me, but, in this case, I'm just going to use the + sign, so = this cell, the SUBTOTAL, + the TIP.
There we go, 117.25, and the beautiful thing about spreadsheets and formulas is as the data changes, right, as Joe calls and says, I changed my mind, I don't want to a BMT, I want a turkey, I change those 2 numbers and everything calculates right down throughout the whole spreadsheet.
[ =.15*D10 ], [ =D10+D11 ] That's the reason that Dan Bricklin and Bob Frankston invented a spreadsheet back in 1978.
They were tired of using a calculator to do the same types of calculations over and over again.
That product was called VisiCalc and it's still, in my opinion, the reason why we have spreadsheets today.
Change some input cells, all of the formulas update, and we're good to go.
Tyler: Thanks for stopping by.
We'll see you next episode.
Check out Don't Fear The Spreadsheet.
This book [ unintelligible – 05:50 ] look like it was written for rocket scientists.
