Master Microsoft Excel for Finance: From Basics to Financial Modeling

The exercise files for today's course are located in the video description below. Don't forget to like and

subscribe. Hi everyone, welcome to Learnit Excel for Finance and Accounting Part One

course. My name is Elyssa Smith and I'm an IT facilitator with over 25 years of experience teaching professionals smart

ways to use Microsoft Excel. During this course, we're going to show you everything from the basics up to very

advanced concepts on how to make Microsoft Excel your best tool when it comes to finance and accounting. In this

course, we're going to explore everything from the basics of data entry in Microsoft Excel to using conditional

formatting to help make values pop out. We'll also be exploring basic formulas and even more advanced formulas like

VLOOKUP and if statements. We will also spend time in this course looking at how to use financial formulas like the PPMT

and the IPMT formulas to help you as you begin your journey with financial formulas in Microsoft Excel.

Looking to support our channel and get a great deal? Become a member today to unlock adree videos. That's right, your

favorite courses without a single ad. Interested in a specific video? Purchase one of our ad free courses individually.

Looking for even more? Gain access to exams, certificates, and exclusive content at learnit anytime.com.

More information can be found in the video description below. Hi everyone. In this lesson, we want to

overview how to navigate in Excel and get you familiar with some of the vocabulary that you'll actually see used

in the platform. Microsoft Excel is a data analytics program that's been around for over a quarter century. It's

really the main application that people use to calculate and analyze their data in any business setting. Now, the first

thing to know about Microsoft Excel is that everything is contained in cells, hence the name Excel. You'll see here

that in my current spreadsheet, I have these little boxes. They're called cells. They allow me to designate data

to a specific spot within this grid system. It's fantastic when you're trying to analyze and calculate data.

You'll see that columns are known as letters, rows are known as numbers, and the way you locate a specific cell is to

click in it, and it will be known by its column letter and its row number. Now, if you look up at the top of Microsoft

Excel, it uses the same interface as the other Microsoft Office applications like PowerPoint and Word. So you'll see that

there are ribbons that you use to access different buttons. And when it comes to navigating, you click in a cell to

locate the cell you want to type in. You can also select rows and columns by clicking on column letters or row

numbers. And if you ever need to know what cell you're in, you can come up and look in the top leftand corner. You'll

see again the cell name box that will tell you the name of the cell that you're currently clicked inside of. You

can select multiple cells. This is called a range by using your mouse or your keyboard. And you can select

non-adjacent ranges by selecting one group of cells, then holding down your control key on your keyboard to select a

different group. Now, the other critical thing to know about a spreadsheet is it also has sheets. Down in the bottom

lefthand corner, you'll see that my current workbook has three sheets in it. The reason for sheets are to allow you

to organize data by sheet. It's a great way to take large amounts of data and split it up in an organizational

fashion. All you do is click on the sheet you want to go to and that sheet will come forward. You can also insert

more new sheets as well. Now, as always, if you need a practice file, we have practice files for you to use during

this course. Just click in the link in the description and it will take you to the practice files. The practice file

I'm in right now is called practice 1 and you can use it as you explore Microsoft Excel. The next thing that

we'll talk about is how to do data entry in Excel. So, join me for the next lesson.

Hey everyone, I'm ready to look at how to do data entry inside Microsoft Excel. I just have a blank workbook open. And

if you're trying to follow along with me, feel free to open up Excel as well and do this as we talk about it. Now,

all you need to do to begin doing data entry in cells is click in a cell and start typing. I'm going to click in cell

B2. Remember your cells are known by their column, letter and row number. When I click in cell B2, I can simply

start typing. As soon as I hit enter, you'll notice that the data entry is complete. Now,

another way to do data entry in Excel is to use the formula bar. It is used for two things. Creating formulas and doing

data entry. The formula bar is located right below the ribbons of Microsoft Excel. It can be turned off, but by

default it should be turned on. You'll see this long white bar where you can do data entry. So, I've clicked in another

cell C2. This time, I'm going to go ahead and type in the topic that I want entered into my formula bar. You'll

notice that as I type that whatever I type here in the formula bar gets entered in the cell below. Once I hit

enter, you'll notice that that information gets put into the cell. So, I can do data entry by clicking and

typing or by using my formula bar. Now, let's chat for just a minute about keyboard shortcuts. When you're

navigating inside an Excel workbook, you can use your scroll bars, but they can be a fairly slow way to navigate. So, I

just want to make a few suggestions. One of them is you can actually use the cell name box next to your formula bar for

fast ways to navigate. If I know that I need to get down to a cell quite a ways down in my workbook, I could scroll. But

scrolling is one of the slowest ways to navigate. So instead, I'm going to click in the cell name box and enter in the

column letter. In this case, the column letter is M. The row number is 500. And it doesn't matter if your column letter

is uppercase or lowercase. I'm going to hit enter. And notice that my computer in Excel takes me all the way down to

cell M500. Now, how do I get back to cell A1 quickly? I love the keyboard shortcuttrl

home. This is on a PC. On an Apple computer, you'll need to use the command button for this. But what you're going

to do is look for the page up key on your keyboard. The home button is always located right next to it. Hold down your

control key and then your home key, and it will take you back to cell A1 in any workbook. This is a fast way to use your

keyboard as opposed to using the scroll bars because sometimes the scrolling will take longer than using those

keyboard shortcuts. So, just remember, get a new workbook open and practice typing in cells and navigating. You'll

get the hang of it very quickly. Join us for the next lesson. Welcome back. We're ready to show you

how to use rows and columns in your workbook to edit the data in your Excel spreadsheet. I'm using a practice file

right now called practice 2. Please remember you can use these practice files to follow along. They're available

in the link in the description below the video. Now, what I want to do is go into my workbook and I'm going to click on

column F. When you click on the letter for a column, you'll notice that it selects everything in the column below.

Now, once everything is selected, if I hit the delete key on my keyboard, notice everything in that column will

get deleted. Now, if I come up and click on the undo button, remember top lefthand corner up on the toolbar, the

quick access toolbar, it will bring it back. What if I wanted to insert a new column into my workbook? I'm going to

come into the column and rightclick on column G. On a PC, when you rightclick on a column letter, you'll get a

secondary menu that gives you opportunities to do things like insert new columns or delete columns. Please

remember that if you delete a column, notice that everything including the content in the column will be deleted.

I'm going to undo that. In this case, I want to insert a new column. So, I'm going to rightclick. By default, when

you insert new columns, they always insert to the left. So, you'll see that the current column moves over the one

that I had selected, and I get a new blank column. I'm going to go ahead and undo that. Rows work the exact same way.

In this case, I want to select more than one row. All I need to do is come in and I'm going to highlight rows 7 through

11. You'll notice that I can select all the content in those rows just by left dragging on the row numbers. Now that I

have these selected, I'm going to rightclick and say insert. Now I currently have several rows selected.

When I insert new rows, they'll go above and number of rows that I have selected will be inserted into my workbook. So

you can see that by again working with rows and columns, you can manipulate large amounts of data in your workbook

without having to individually select sections of the workbook with your mouse because the columns and rows allow you

to do it all at the same time. Feel free to use the practice files to play with rows and columns in a workbook of your

own. Welcome back everybody. Let's talk about printing, saving, and opening

spreadsheets. Now, right now I have the practice file called practice 5 open. So, feel free to use it as we explore

these functionalities. When it comes to printing, I need to go up to my file ribbon to access the print option. I'm

going to come to the top lefthand corner because the file ribbon is the very first one on the ribbon tabs. You'll

notice that when you go to the file ribbon, it takes you to a part of Excel called the backstage view where the rest

of your spreadsheet is covered up. All the functionalities run down the left hand side. Print is right below save as.

When you click on print, it takes you to a combination print task pane on the left and a print preview on the right.

Now, I love this view because everything I need to preview and also update my print is right here. You'll see that you

can do things like decide the number of copies you want to print, the printer that you're printing to, the portion of

the workbook that you're going to print, and even do things like update your orientation. And I can view these

changes as I make them and even update things like my margins right here inside the print dialogue box. When I'm ready

to print, I just click on the print button and my worksheet will suddenly be available on paper. Do note that the

default is only to print the current sheet that you have selected. Now to exit print preview or the print

dialogue, I'm going to come to the top lefthand corner and click on the back button. When I'm back in my workbook,

you'll notice that changes that I make in print do not update the look of the workbook. The only thing you may notice

is that you're going to suddenly see dash lines in your workbook representing where page breaks are. And this happens

after you go in and print a workbook. Now, what about saving this workbook? Let's say that I've come in and made a

few changes and I'd like to make sure my workbook is saved. We're going to come up to the file ribbon tab again, go down

to save or save as. Save is commonly used the first time that you save a workbook. Save as allows you to take an

existing workbook, navigate to where you're saving it, give it a name, and when you click on save.

I'm going to type in my updated name, that workbook file will be saved, and you can see the new name at the top. But

saving Excel is exactly the same as saving in, for example, PowerPoint for a presentation or Word for a Word

document. Finally, how do I open up a file? Well, we're going to go back to the file ribbon tab again and come into

open. This will take you into your computer where you can see different locations where you can save and open

files. I'm going to browse, locate the file I want to open, either doubleclick on it or select it and say open and then

that file will open and I'll see its name up here at the top. As always, remember if you have multiple files

open, a great way to switch between files is to come down to your Windows taskbar, locate the Excel icon, and then

you can switch between the different files by just clicking on them. And again, try this out in Excel on your own

so you feel comfortable opening, saving, and printing files. Hi everybody. Let's talk about

formatting inside Microsoft Excel. Now, what's really important in a spreadsheet is what's inside the cells. These are

what we sometimes call your cell contents. Formatting is made to highlight or make it easier to

understand the contents inside your cells. Right now, I have practice file format one open. Feel free to use it to

follow along as we try out these different formatting tools. There are three main things we want to talk about

here. Applying color, alignment, and cell borders. Now, first of all, to apply any formatting, you need to select

the contents of your cells. In this spreadsheet, I'm going to select cells B3 over to G3. Most of the formatting

tools will be located on the home ribbon tab in the font and alignment groups. Or if you rightclick, you'll notice that

you get a toolbar that has a lot of the same tools in it. For this video, I'm going to be using the ribbon options.

Let's start with color. I've selected my cells, and the first thing I want to update is the color inside the cells.

This is what we refer to as font color. For this, I'll go to the font group and look for the capital letter A on the

bottom row. And I'll see that when I click on the arrow next to it, there are different colors I can apply to the

writing of my cell. It's really important that when you apply font color, you ensure that the color you

select is still going to allow people to easily read the contents of the cells. What about the background color of the

cell? This is what we call fill color. For this, I'm going to highlight A4 down to A13. And directly next to the font

color button, you'll see a paint can. This is called fill color. This allows you to update the color inside the

cells. Again, making sure that whatever fill color you pick does not make it difficult to read the text inside the

cells. Now, alignment has everything to do with how things are aligned in the cells. For this, I actually want to go

between rows four and five and make the row taller. The reason I want to do this, and notice that I'm hovering over

row four and five, my mouse pointer turns into an arrow that points up and down, and I can drag down. This will

make my row taller. Each cell is like a box. So, you actually have a left, center, and right alignment, and also a

top, middle, and bottom. What I'm going to do is I'm going to highlight row four by just clicking on the number four.

Remember, this will select everything in that row through the entire sheet that I'm on. Then I'm going to come to the

alignment group. On the alignment group, you'll see that on the bottom I have left, center, and right alignments that

I can use. I'm going to put the numbers in the center. And then I have top, middle, and bottom. And I can use these

to align something directly in the center of the cell. Now, in addition to that, you can also do alignments that

are at a tilt. For this, I'm going to actually go in and make row three taller by dragging it down. Then I'm going to

highlight again cells B3 through G3. Up on that same alignment group, directly to the right hand side of the bottom

alignment, you'll see a lowercase A and B button with an arrow pointing up and to the right. This allows you to

actually tilt text inside the cells and put them at an angle. It's kind of fun, but again, make sure your cells or your

row is tall enough and the cells are wide enough for this to happen. Finally, cell borders. Now, it looks like I have

borders turned on in my spreadsheet, but these are actually grid lines, and they don't print by default. To apply

borders, I need to come in and highlight the cells I want to apply the borders to, and then directly to the left of the

paint can or fill color, I'll see the borders button. When I click on it, it provides me with different borders that

I can apply. I just select the border I want. Click on it and then when I click away, I'll see that border applied to

the cells. This is a border that will print. As always, try this out in one of the practice files or a workbook.

Welcome back. Let's talk about formatting values or numbers inside of cells and also using one of my favorite

tools to copy formats called the format painter or the format paintbrush. Now, right now I'm in practice file format 2.

Feel free to use it to follow along. Let's talk about formatting values or numbers. First of all, you'll see that

in any spreadsheet values are always at the right. Now, remember that values include dates. And up here in this

spreadsheet in cell B2, I actually have a date. And if I make the column a little bit wider, we'll notice that

again it aligns at the right. Text, which means any combination of letters and values together or just letters

themselves will always align at the left. This is a tool to help you be able to view automatically a number as

opposed to something that is not a number. Now, how would I, for example, take these values and make them look

like a currency because that's what they actually are. Well, first thing you need to do is highlight them. So, I'm going

to highlight cells B4 to G4. Then, on my home ribbon tab, I'm going to come to the number group. Now, Microsoft has

conveniently put a dollar sign right on the second row of this group on the ribbon. When you click on it, it will

automatically apply a dollar sign and two decimal places to all those values. Again, if you don't want the decimal

places, notice that on the right hand side of the group, you have the ability to both increase and decrease decimals.

Just be careful with this because if your values do have decimals and you remove them, the values will round up. I

also want to apply this same format to my cells in B7 all the way over to G13. So again, I'm going to select the values

and go up and click on the dollar sign and apply a currency format to all those values in my spreadsheet. Now, what

about dates? Right now, if I look at cell B2, I've made it a little bit wider, the column, to see the full date.

Right now, this is called the short date format. Can I update this? Absolutely. I'm going to come in and go right to the

drop- down arrow next to the current field where it says date because Excel recognizes this. And you'll notice that

if I come in, it's already showing me a short date format that I currently have, but I can update this to a long date

format. Now, what's common when you do this is that you're going to see number signs inside the cell. Anytime there are

numbers in a cell, and Microsoft Excel cannot show you all the information. It just shows you number signs because it

never wants to show you incomplete information. A quick trick to fix this is to come right up and doubleclick

between the columns where you see those number signs and it will auto fit the content so that you can see it. So now I

have the long date format. Now what if I have a format for example these cells here don't have decimal places and I'd

like all the cells below to also not have decimal places. This is a great time to use the format painter or the

format paintbrush. The first step is to select the cells that have the format that you like by going up and clicking

on the format painter. Then I'm going to come in and I'm going to highlight the cells. And notice when I do this, it

makes my cell or my mouse pointer in this case look like a paintbrush. I'm going to highlight the cells that I want

to copy the format to. When I release my mouse, it will have copied the formatting for me. This is a tool that

you can use also in Microsoft Word and PowerPoint, and I use it all the time. If you don't want it to turn off,

doubleclick on it. Now, as always, try this in a spreadsheet of your own by again updating number and date formats

and also playing with the format painter. Welcome back. Let's talk about

conditional formatting and merge and center. Right now, I have practice file format 3 open. Feel free to use it to

try this as we go. First of all, up in cell A1, I'd like to merge and center that title. I'm going to come in and

highlight all the cells that I'd like to make into one. So, in this case, cells A1 through G1. One of my very favorite

buttons, the merge and center button, is on the home ribbon in the alignment group right under the wrap text or next

to the wrap text button. When you click on this button, it takes cell A1 and merges it. So, all the other cells B1

through G1 become part of it and it centers the content that was in cell A1. It's a fantastic way to quick quickly

center your title at the top of a workbook. Now, the next thing that I want to do is I want to apply

conditional formatting. Conditional formatting is a way to make things pop inside of cells with formatting. I'm

going to come in and highlight cells B7 all the way over to G10. Basically, all the expenses in the spreadsheet. Then to

apply a conditional format from my home ribbon tab, I'm going to come to the conditional formatting button. Now,

there are different kinds of conditional formats. We're going to try out a couple. The most common type is called

highlight cell rules. This allows you to have something formatted in your spreadsheet based on a criteria. For

example, is something greater than, less than, between, or equal to. You can even do conditional formats that are based on

text contained in a cell. We're going to start with greater than. I'm going to come in and say any value that is

greater than 5,000. You type that in. Then click on okay. You'll see that the conditional format turns the text and

also the fill color in the cell red. Now, can you do multiple conditional formats? You can, but let's try a

different one. For this, I'm going to go down to row 13 and highlight cells B13 over to G13. I'm going to go back up to

the home ribbon tab in the styles group and select conditional formatting. This time I'm going to come down and show you

a data bar. This is a conditional format where based on the data inside the cells, the values, the color will go

further over. And you can see that there are different colors that you can pick. So in this case, I'm picking the green

gradient fill. You'll see that for a cell that has a higher value, the color goes further over. Now, how do you edit

a conditional format or clear it? I'm going to rehlight the cells in row 13 where I applied the conditional format.

Go back to conditional formatting and come clear to the bottom of the menu to manage rules. Here in this box, I'll see

the current conditional format that I selected. If I wanted to edit it, I could select it and click on edit rule.

To clear a conditional format, the easiest way is to highlight the part of the spreadsheet where the conditional

format is located. Go back to the conditional formatting button and come down to clear rules. Just be careful

that you select clear rules from selected cells because if you pick clear rules from the entire sheet, we all know

what's going to happen. You're going to lose all your conditional formats in the workbook. As always, try this out. It's

a great way to make things pop or highlight things in cells. Welcome back. Let's talk about how we

can easily see the top of our screen with the bottom in a large workbook. This practice file I have open is called

navigate one. Feel free to use it to follow along. Now, one of the challenges of Excel is how big a workbook can be.

This particular workbook, if I scroll down in this sheet that I'm on, they're only about 200 rows, but I would still

spend a significant amount of time scrolling between the top and bottom if I'm trying to remember what my column

headers are. Very common issue in Excel. Well, Microsoft has two different ways to deal with this issue. The first one

is to split your screen. To do this, we're going to go up to the view ribbon. This ribbon is all about adjusting the

view of your workbook sheet that you're in and come to the window group. On the right hand side top of the window group,

you should see a button that says split. Now, what this does is it puts these gray lines in your workbook. Basically,

what you're doing is splitting your workbook into what are like window screens. There's a vertical line that

you can adjust by just resting your mouse pointer on it and dragging it to the right and left and a horizontal line

as well. Now, what you'll see now is that you have two sets of vertical scroll bars and two sets of horizontal

scroll bars. So, what I can do here is stay at the top in this top portion of the split and scroll to the very bottom

and I can also be at the far left in one of the splits and the far right in the other. So, I'm able to align the

spreadsheet and see it in different portions. If you don't want one of the splits, for example, if you don't need

the horizontal split, you can rest your mouse pointer on it, drag it down into the bottom of your screen, holding down

your left mouse button as you do it, and you can basically just get rid of a split you don't want. To turn the splits

off, you go back up to the ribbon and click on the split button, and it will remove the split. Now, I prefer a tool

called freeze panes. This allows you to either freeze the top row on your workbook sheet or the top left column or

pick an apex point of a cell. For me, I'm going to go up and click in cell C2. I'm currently at the very top of the

sheet. And then I'm going to go back to the view ribbon tab, back to the window group, and look for freeze panes.

There's actually a little snowflake on the button. When you click on it, it'll give you three different choices. Now,

in this case, I'm not trying to freeze the very top row, even though I am, but I'm not trying to freeze the very far

left column. I'm actually trying to freeze from cell C2 above and to the left of that point. So, I'm going to

pick instead the option at the top that says freeze paints. This means anything above and to the left of the cell that I

have selected will be frozen in place. Now, it's kind of hard to tell that this has happened, but notice if I look at

the right hand side of the spreadsheet, I'd see a thin black line that goes above row two. And also, if I look at

everything to the left of column C, I can see the same thing. Now, to really see this in action, you need to start

scrolling down. So, as I scroll down, I'm going to see that everything above row two stays fixed in place. But the

exciting part is when you start scrolling to the right because again I didn't say the first column, I said

everything to the left of cell C2. So when I start scrolling, column A and B stay in place, but everything else is

scrollable. So it's like you've frozen the spreadsheet in place. To turn it off, go back up to the freeze panes

button again and say unfreeze panes and it turns off. As always, try this out in your own workbooks. These are tools that

you'll use every day. Hey, welcome back. Let's talk about some ways to save you time inside Microsoft

Excel. For this, I have a practice file called Navigate to open. Feel free to use it as you follow along. I want to

start by talking about the quick access toolbar shortcuts. I use these all the time. And don't forget that you can

customize these to add shortcuts of your own. Now, the quick access toolbar is actually one of the final toolbar still

left in Microsoft Excel. It's important because on mine, this is where the undo and redo buttons are. Remember that if

you go to the right hand side of the quick access toolbar, you'll see a line with an arrow pointing down. When you

click on this button, you'll see frequent shortcuts that you can add. I'm a big fan of shortcuts like the print,

preview, and print and the spell check. Also, undo and redo are usually pinned to your quick access toolbar for you. If

you see a shortcut that you want, or I should say if you see a shortcut that's not here, feel free to come down to more

commands and add it. All you do to add the command is leftclick on it and you'll see the button get added. And

these commands will always be here no matter what workbook file you open. To remove them, you just go back to the

same arrow again, come in and leftclick on the particular command you want to take off. But it's a great way to again

add your own shortcuts to Excel. Don't forget also that the quick access toolbar can be located above the ribbons

or you can come in and say show below the ribbon and in this case it'll be placed right above your formula bar. I

like to keep it in the top left corner. That way it doesn't get hidden but it's up to you where you put it. Now what

other options do you have? We already talked about this but I highly recommend learning a few critical keyboard

shortcuts to help you in Excel. I just want to share a couple of my favorites. One of the first ones is to be able to

select the entire sheet and all the data that's on it. To do this, I'm going to do Ctrl A on my keyboard. Now, you'll

notice the cell I was clicked in stays white, but everything else gets highlighted. Another way to do this is

to go between column A, the letter, and row number one. You'll see a rectangle, and this does the same thing. It selects

all the content in the workbook. Another great keyboard shortcut that we already talked about on a PC is to do control

home. And remember the home button is located directly next to the page up on a Windows PC keyboard. This will always

take you back to cell A1. Another great keyboard shortcut is controll. Now the end key end like the

end of a book is located by the page down button. This will take you to the last point where someone typed inside

your sheet that you're on. So, controllome and controll can take you between the top and the bottom of your

spreadsheet. One more good keyboard shortcut to know is how to select all the data where there's typed information

in a row or column. I'm going to come to the top of column G and click in cell G1. Now, if I scroll down, this goes all

the way down to row 200. It takes quite a while to scroll down to it. Sorry because I'm making you seasick while I

scroll to it. But in this cell, I'm now going to click and or on my keyboard, I'm going to hit control shift down

arrow. Control shift down arrow will select everything in that column to the last cell where something was typed.

It's a fantastic shortcut that will save you a lot of time having to drag and select. To unselect the content, you

just click. The same thing will work for rows. If you click in a cell, I'm going to select cell in this case A10, and

then do control shift arrow to the right, it selects everything inside a row. So again, those keyboard shortcuts

are controllome, controll, and then control shift. Use your arrow keys to select all the

content in a row or a column where there is typed information. Try these out in a workbook of your own because these few

keyboard shortcuts will save you hours of time. Now, are there more? There are many. So, feel free to explore more

Excel keyboard shortcuts because they will save you so much time. Hi everyone. Let's take a couple minutes

and review the most basic formulas or functions of Excel. So, a formula is a type of calculation that Microsoft Excel

knows how to do, and there are many. They're called functions. Now, in my spreadsheet that I have open, it's a

practice file called formulas. One, I want to review some of the most basic functions that are part of Excel. The

first one we want to do is a sum function. Sum means to add. In this case, I want to add up cells B6 down to

B9 and place the answer in cell B10. When you create a function, you always start at the end. So, I've clicked in

the cell where I want the answer to go. To help me create the functions, I'm going to use one of my most favorite

buttons in Excel called the autosum button. Now, the autosum button is located in a couple of places, but one

of the most common ones is on the formulas ribbon. Since we're creating a formula, that's a good place to go.

You'll see the autosum button is the second button in from the left, and it looks like a sigma. Now, when you're

just doing a sum, you can actually click on the top half of the button, and it automatically assumes you're creating a

sum function. Now, let's look at the syntax of the function. You'll see that every function begins with an equal

sign, the name of the function, and then in parentheses, the range of cells that are being calculated. You'll always see

the first cell, and then a colon, and the last cell. You'll also see the formula put up in the formula bar. I'm

going to hit enter and we'll see that it's added up the cells B6 through B9. Now, because this formula is complete, I

can click on it, go to the bottom right hand corner, and you want to be cautious here because you don't want your mouse

pointer to look like a white plus sign or four black arrows pointing up, down, left, and right. It needs to look like a

crosshair, like you're aiming at something. This is called the autofill button. I'm going to hold down my left

mouse button and drag that formula from cell B10 over to G10. The fill handle copies what's in a cell. Well, in cell

B10, I had a formula. So, it's copying the formula. But the exciting part is that if I click in these new formulas

that have been created and look at the formula bar, I can see that as I've copied the formulas over, they're now

copying relative to their position. So, they're updating to a new range of cells. Let's try another common formula.

This is an average. I'm going to come in and click in cell B12. Now, remember to find an average, you add up all the

values and divide by their number. It's not fun math, so let's let Excel do it for us. The autosum button can also help

us with averages. What I'm going to do is click on the arrow that's either under or next to the autosum button

after I've clicked in cell B12 because that's where I want my resulting answer to go. And I'll pick average. The only

problem we're going to see is that the range of cells is incorrect because I only want my range to be B6 through B9.

So, what I can do while it still has the box open, I can highlight the correct cells and update to the correct range

and hit enter. And I'll see that it will now give me the average again. I can now click on cell B12 where I see my answer.

Go to the bottom right hand corner, get the crosshair and drag it to the right. Let's try a couple more common

functions. I now want to look at the max function or maximum. Its goal is to look at a list of values and return the

highest value. You can also use the autosum button for this. So, I'm going to click in cell B13.

Go up and click on the arrow of the autosum button and pick max. Again, it's probably going to select the wrong set

of cells for your range. So, highlight B6 through B9 and then hit enter. Again, this is one that you can use that fill

handle to drag over to cell, in this case, G13. Now, the min function that's located in

cell B14 does the opposite of the max function. It finds the lowest value. So again, I'm going to go up and click on

the arrow next to auto sum. And this time, I'll pick min. Now, it might select the wrong range. So highlight

cells B6 through B9 to correct the range and enter in your formula. Then go to the bottom right hand corner and drag it

to the right to find the lowest value in that range of cells. The final function we want to try out is the count

function. This will just take a list of values or even text and tell you how many things there are. So if you have

four cells selected, the answer will be four. But it's still very commonly used in many functions. So I'm going to go up

again after I've clicked in cell B15. Click on the arrow next to auto sum and this time pick count numbers. Then I

will select the correct range which is B6 through 3B9 and hit enter. And again I had four cells selected. So the answer

to the count function is four. But I can copy that formula over. So remember with these five basic functions, what we're

doing is reviewing what a function looks like, what a range looks like, and how you can use the autosome button to help

you create these functions. As always, be careful with the autosum button because it's very common for it to

select an incorrect range. But while you're using it, you can correct that range. Using these tools, you can start

introducing yourself to the basic functions of Microsoft Excel. Welcome back. Let's talk about one of my

favorite types of functions in Excel, an if statement. There's a practice file for this called if functions hyphen

practice. Feel free to open it up and use it. Now, in this spreadsheet, you'll see that I have months, sales reps, and

their totals. And I need to calculate their bonus. However, there is a threshold. They only get the bonus if

they made more than $7 million in sales. If you look at my spreadsheet, I have a few sales reps that did not make that

threshold, and I only want to calculate their bonus if the threshold was met. This is the perfect use case for an if

statement because an if statement needs to have a logical test that can be set to true or false. I'm going to click in

my spreadsheet in cell 03. Now, to help me do my if statement, I'm going to use a tool called the insert function

button. You can get to the insert function box by going up to the formula bar and on the left hand side you'll see

a small FX. This is the doorway to the insert function box. Now the first thing I need to do is go into Microsoft

Excel's library of functions and locate the if function. The top portion I'm going to type in the name of the

function I'm looking for. On the right, there is a go button that I'll click on and it will take me into the library

below and find any functions that have the word if in them or close to the spelling I've used. As soon as I see the

function I want, I'm going to select it and then come down to the bottom of the box and click on okay. So, now that I've

selected the if function out of Excel's function library, it shows me the three different portions of the formula that I

need. The first thing is a logical test. Well, in this case, my logical test is, was the amount the sales total in cell

N3 greater than or equal to 7 million? Now, it's really important that I count the correct number of zeros for this

because if I have too many zeros, it won't calculate correctly. I don't need to use dollars and cents because it will

automatically format those for me. Now, if that's true, then I need to take the sales total that's in cell N3 and

multiply it by 5%. You can use a decimal if you prefer here, but I like percentages. If that's false, I don't

want it to calculate a bonus. And I also just want it to tell the person no bonus. So, anytime you use a text string

inside a formula, you need to come in and enter it in quotes. So, I'm just going to put in the words no bonus

included in quotes. So again, my logical test has three arguments or I should say my if statement. The logical test is

there sales amount over 7 million. If that's true, then multiply that by 5%. If it's false, just put in the words no

bonus in the cell. I'm going to click on okay. And based on my function, now if you look at the formula bar, you can see

the function how it starts with an equal sign if and then includes the logical test. Is N3 greater than or equal to 7

million? Then a comma followed by what to do if that's true and what to do if that's false. And it looks like this

person sold over 8.3 million in product. So they did meet the threshold for the bonus. And we see it. Now, is this a

formula that can be copied? It is. So, I'm going to click in cell 03, go to the bottom right hand corner, get the black

crosshair, and drag it all the way from 03 down to, in this case, 027. And what I want you to see is anytime a sales rep

had less than 7 million, the formula goes to the false portion and just puts the words no bonus in the cells. These

are a super fun type of function and it's very common in an if statement where you see the false portion of the

formula for another entire if statement to be nested in there up to several levels so that your formulas can become

very complicated. Hey, welcome back. Let's try out two of my favorite functions in one. And it's

actually two different functions. Sum if and average if. These are a great way to only sum or average content based on the

matching criteria that's also in the workbook. Now, there's a practice file for this called sum ify average if

practice. Feel free to open it up and use it to follow along. I'm going to start by clicking in cell K2. This is

where my first function will go, the sum if function. I'm also going to suggest that you use the insert function box the

first time you do this formula because it does have a couple of different pieces. So, I'm going to go up to the

lefthand side of my formula bar and click on the FX button. This will take us into the insert function box. At the

top, I'm going to type in the name of the function I want, which is sum if. No words or no spaces in between the two

words. And I'll click on go on the right hand side. Make sure you pick sum if and not sum ifs s. When you select the

function name, doubleclick on it and it takes us to the second portion of the insert function box, which is the

function arguments. I'm starting with a range. This is the group of cells that I'm going to be highlighting. And you'll

see right here that in my case, it's column E. Now, I'm not going to include cell E1 because it's a column heading.

To select the cells, I'm going to click in cell E2 and then do the keyboard shortcuttrlshift

down arrow. This will select all the cells to the bottom of the spreadsheet that are in that column where my

criteria is. My criteria is going to be based on the fact that I only want to sum those destinations that are to

Cancun. Now, what is my criteria? My criteria is that the destination is Cancun. Because it's text, I have to

contain it inside quotes. So for my criteria in quotes, I will type the word Cancun and I will make sure I spell it

correctly. My third line in the function arguments box is my sum range. This is the column that I will be summing. In

this case, it will be column H. I'm not going to include cell H1 because again, it's a column header and it's not a

value. So I'll click in cell H2 and on my keyboard I will select cells H2 down to H200

using the keyboard shortcut controll shift down arrow. So here we can see the three arguments of the function my range

which contains my criteria. So it's cells E2 to E200 and then what I'm summing which is H2 to H200. Now I can

already see that the formula will work because in the bottom lefthand corner of the function arguments box, it tells me

my formula result. Then I'll click on okay. We'll see right here that it's showing me that if I were to sum all the

Cancun destinations and here I can see Cancun because I've sorted the destination column. If I come over and

also select the totals in column H and then just highlight them, I can see that down on my again status bar. If I come

over and look at the autoc calculate, it shows me that yes, that would equal 2952.

Now, let's do the same thing in cell M2, but rather than a sum if function, we will do an average if. So, I've clicked

where I want the formula to go, and then I'll come up and use the insert function box. This time, the name of the function

is average if, all one word, no spaces. I'll click on the go button. Once I've located average if down in the select a

function list, I'll double click on it. This one is very similar to the sum if function. You need the range where your

criteria is located. This time it's going to be Boston. So again, it will be the same range I used. I'll use control

shift down arrow to select E2 through E200, not including E1 because it's a column header. Now my criteria is that

it's Boston. So in quotes on the criteria row I'll type Boston as the destination that I want averaged. Then

in my average range again I'm going to average H2 down to H200. So I'm using the control shift down arrow keyboard

shortcut to select that range. And in the bottom lefthand corner I can see if the function's going to work. When I

click on okay you'll see that there are quite a few decimals included with this particular formula answer. So, I'm going

to come up and just format this as a currency to get rid of some of those decimals. Now, it looks like my answer

is $530.91. I can again come into the spreadsheet, highlight all the Boston destinations,

and go over and highlight the total row as well, and then just highlight the ones that will be for Boston. If I come

in and look at the average for that group of cells, you'll see that down on the status bar, it rounds it up to 531,

but that is very close to 530 because my status bar doesn't do decimals. These are two fantastic functions where you

can only sum based on criteria or average based on criteria. And they combine some of our very favorite

functions, sum, if, and average. Howdy. I want to show you some of my favorite functions for reforming data in

a cell when you need to take just a portion of information in a cell to use somewhere else. These functions are

called left, right, and mid. And there's a practice file with that same name, left hyphen right hyphen mid, that you

can use to follow along. Now, I'm going to click in cell D2. You'll notice that to the left I have a customer location

and code. It's the first four characters of that cell that I need to put over in cell D2. And the left function is a

great way to do this. To start, I'm going to hit an equal sign and type the word left. Now, you'll see that as soon

as I come in and double click on the formula autocomplete, it tells me that I need two things. I need the cell with

the characters in it, which is cell C2, then a comma, and the number of characters. It's four. And then I don't

need that closing parenthesy. I can just hit enter. And we'll see it's captured the first four characters. In this case,

it's a code that give me the customer location code. Now, this is a function. So, if I click on cell D2, go to the

bottom right hand corner, and get that black crosshair, I can drag it down, and you'll see that it brings the customer

location code in for me. And that's the left function. Now, the right function does the same thing, but rather from the

far left side of the cell, it does the right. So, I'm going to go to column F this time to the office code cell.

You'll see that in column E I have office names, but I also have a code at the end of each of those. So here I'm

going to type in an equal sign in cell F2 and type in the name of the function, which is write. Again, you'll notice the

same thing comes up with the formula autocomplete. I'm going to double click on it to get my opening parenthesy. And

here underneath you're seeing that it's telling me again I need to provide the cell that has the text in it that I need

to return characters from. So, I'm going to click in cell E2. And then I need to do a comma and tell it the number of

characters from the right hand side of the cell that I want to return. It's four. We don't need that closing

parenthesy because there's only one step set. So, I'll hit enter. And you'll see in this case, it's returned the four

digits from the right hand side of the cell, which is 1 3 4 3. This is again a function. So if I click in cell F2, come

to the bottom right hand corner, get the black crosshair, I can drag it down. Now the final function I want to show you is

called the mid function. For this one, I'm going to go over to column L under customer rate. Now in column K, I have

some pretty complicated decimal places that represent the customer satisfaction rate. I want to simplify that. So what

I'm going to do is I'm going to click in cell L2 and use the MID function. This function, we're just going to type in

mid after the equal sign, allows you to select a cell and it can contain values or characters, either one. You need to

give it a little bit more information on this. So, I'm now going to go up and actually select the FX button so that

you can see the two additional arguments. We need to tell it where the characters need to begin. For what we're

doing, I want to capture stuff after the decimal place. So, I'm going to tell it as soon as the decimal place starts

after two characters in, I'd like it to return three characters for me. And then I'll click on okay. And you'll see based

on that, it's doing exactly what I said. Go to the second character in and return three characters after that point. This

is also a function. So, I'll drag it down. And now I'm getting a little bit easier information about what the

customer satisfaction rate is with fewer decimal places. Now, these are three functions, but they are all again

functions. I want to show you another tool that actually works a little bit faster and can accomplish some of the

same things. I'm going to drag over to the right. In column B, I have my customer names. Now, what if I just

wanted the first name of the customer and not the last name with it? I'm going to rightclick on column C and insert a

new column that I'm just going to call first. For the first name, we'll call it first name. Then underneath I'm going to

type in the first name that I see next to again in cell B2 which is Antonio. Now I need to create a pattern here for

Excel to understand. This is using a tool called flashfill. It's also important that you spell it exactly as

it appears in the cells to the left. Now it looks like right now my computer is not picking up on this pattern. So to

force it to recognize the pattern I have two options. I can do control E on my keyboard or if I go up to the data

ribbon tab and come to the data tools group. I can also click on the lightning bolt button which is called flashville.

And this helps Excel to recognize the pattern that I'm asking it to take out the first names from each of the cells

to the left and put them in column C for me so that I don't have to use a function to accomplish this. So, this

will allow you to combine information and also separate it as I've just showed you. Try this out because sometimes it's

faster than trying to use a function. Welcome back. Let's talk about the count function and how you can combine it with

the if function to make it really useful. The count function by itself is probably the simplest function in Excel.

It does basically what it sounds like. It counts how many of something are in cells. We want to combine it though to

count based on criteria. This is where you take the count function and an if statement and put them together. I'm in

a practice file right now called count if and count ifs. Please open it up to follow along. I'm going to click in cell

K2. My use case is I need to count how many sales reps have received a commission. I can do that if I only do

the count if cell I or anything in column I has the word yes. So, it's a great use case for count if. We are

going to use the insert function button to help us with this. So I've clicked in cell F2. Then I'm going to go over next

to my formula bar and click on the FX button. Now I need to type at the top the name of the function that I want to

use which is count if all one word. Click on the go button. And then for this first one, make sure you use count

if and not count ifs. There's only one letter difference. Now again my range are going to be cells I2 to the bottom.

So, I'm going to click in cell I2 and then use my control shift down arrow to get everything down to the bottom

selected. Now, for my criteria, it's if the word yes is located in the cell. So, I'm just going to type the word yes. And

you'll see right here, it's telling me that there are 102. I'm going to click on okay. And based on that, yes being

found in certain cells and no in others, it's done a count for me. Now, for the commission, no, I want to do the same

thing. This time though again I'm going to come in and type in count if for my function name. The first thing I'll need

is my criteria and again that will be column IS2 down to the bottom. So I'm using that control shift down arrow.

This time I'm not using the insert function button. Then my criteria this time is if no commission was paid in

quotes I'll type the word no. Then because the formula only has one set of opening and closing parenthesis, I'll

hit enter and we'll see that there's a 97. And really, this is correct. Now, if I come to cell M2, it gets trickier. I

only want it to do a count if the location or the destination in this case is St. Louis and a commission was paid.

So, you can see here that I've got two different things going on. And as I scroll through the different locations,

we'll see as we come down that St. Louis is one of the destinations that's in column E. So to do this one, I'm going

to come in and use the insert function button. So I've clicked in cell M2. I'm going to go up to my FX button, and this

time I'm going to type in the word count if S. It's really important you get that S at the end because this is what allows

you to have two criteria in your formula. So, the first thing I need to do is come in and select my first

criteria, which in this case is going to be whether a commission was paid or not. So, I'm going to come in and do the I2

down to the bottom again and type the word yes. Now, you'll notice that as soon as I do my first criteria range, it

opens up to give me an additional one. For this one, it's going to be again is the location

like we talked about or the destination St. Louis. So for this one, my criteria range will be cells E2 down to the

bottom. And I'm using that control shift down arrow again to get that. Now my criteria is going to be St. Louis. I

need to put it in quotes. And I also need to be sure it's spelled the same way it is in the spreadsheet. And I can

double check that right here. I can see the spelling. Then I'm going to go ahead and click on okay. And you'll see that

based on what I did right there, if I come down and look at St. Louis, if I were to again have the spreadsheet

sorted in ascending order, which I do, I could come over and I could actually check this and count how many St. Louis

yeses I have. And we'll do it. 1 2 3 4 5 6. So if I look at my function answer, it's correct. So again, it's only

counting if yes, they had a commission and yes, the destination was good old St. Louis. These are super fun functions

and you'll see them used a lot in conjunction with other functions to only count when certain criteria is set.

Thank you so much for joining us for this part one of our Excel for finance and accounting. In this course, we've

started at the very beginning by how to navigate in Excel and then we jumped into formatting basic functions like

sum, average, min, max, and even count. And then we jumped into more advanced functions like being able to use sum if,

average if, and even count if, and the round function. Join us for the next portion of this course, the part two,

where we're going to explore more advanced functions like VLOOKUP, XLOOKUP, and even functions that you

will use as you start creating your own financial statements and some of the specialty charts that come with

Microsoft Excel. Hello, welcome to Learnit's Microsoft Excel for Finance and Accounting Part

Two course. My name is Elyssa Smith and I am an IT facilitator with over 25 years of experience teaching users like

you how to maximize their productivity using tools like Microsoft Excel. Now, in this part two course, we're going to

spend our time exploring more of the functions that help you become a financial genius in Excel. Just kidding.

But we are going to explore things like the date and time functions that are part of Microsoft Excel. We'll also look

at how to validate data in cells using data validation. Now some of the functions we're going to look at are

VLOOKUP and XLOOKUP. We'll also be exploring some of the scenario management tools like scenario manager

and goalsek. And then when it comes to financial functions, we're going to spend a great deal of time looking at

those, including the PMT function, NPV, irr, the IPMT, and others. As we begin to teach you how to make your own loan

schedules, we're also going to explore some of the templates that come with Microsoft Excel that you can personalize

and make into your own loan schedules as well. So, join us for this course. Hi everyone. Let's talk about the date

time functions that are part of Microsoft Excel. They're actually an entire category and today I'd like to

introduce you to two of the most basic ones. Keep in mind that dates are very critical to reporting financial

information. So having knowledge of a few of these datetime functions can be very useful. I'm in a practice file

called date-time. Feel free to open it up and use it to follow along as we do this activity. I'm going to start by

clicking in cell C5. This is a very basic expense report and I'd like to put the current date into this cell. There

are a couple of different ways to do that. One of them is a function. I'm going to hit an equal sign and type the

word today. Now, if I don't even know what today's date is, as soon as I type today with an opening parenthesy, Excel

will get the current date off of my computer. And when I hit enter, it puts the current date into the cell. Now,

it's important to note that this is a date that will o that will update every time you open the file. So keep that in

mind. It will change. What if you need to know the date and the time? Because the exact time can also be useful to

know. For this, we're going to do an equal sign and type the word now indicating the date with the time. I

have an opening parenthesy. I don't need to do the closing parenthesy because Excel will capture the current date and

time off of my computer. Now, you're going to see the date with the time. It does use a 24-hour time clock, but

please remember you can always click on this date. That is again because of the formula. Go up to your number group on

the home ribbon tab. Click on your different number formats. Come down to more number formats. And then from the

format cells box, you can come to date. And you'll see that if you go through this list, there are some date and time

styles that include a 12-hour time clock that you could reformat that particular function answer to. And remember, these

are both dates that will update. Now, is there a way to insert a date that will not update? There is a keyboard shortcut

that I'm a big fan of that you can use to insert the current date into a cell. I'm going to click in cell A8. And then

on my keyboard, I'm going to do the following keyboard combination for a PC computer. The control key with the

semicolon. Now, the semicolon button is usually located next to the letter L as in lettuce on most laptop keyboards and

normal keyboards. So, I'm going to do control semicolon. You'll see the current date is inserted into the cell.

Now, when I click on this cell, you'll notice that this date is not a formula. When I look at the formula bar, it's

just a date. So, this is a keyboard shortcut that will capture the current date off of your computer. If you'd also

like to include the time, I'm going to double click after I've put in the date and double click right after the date

and hit a space bar to get my cursor in that cell. Then, to do the current time on my keyboard, I'm going to do control

shift semicolon. So, three keys all at once. Controll shift semicolon. This will insert the current time in the

cell. This date and time keyboard shortcut does not update. So the next time I open this spreadsheet, the date

and time that are located in cell A9 will stay there. They will not update. So keep these in mind when you start

recording financial information in your spreadsheets. You can either use functions that will update with equal

sign today and equal sign now or you can use keyboard shortcuts like control semicolon to insert dates that don't

update. Welcome back. Let's talk about data validation. Now, first of all, what is

it? It's a way to verify that what's being entered in your cells is correct. Data validation can be added to blank

cells or existing cells. If you add validations to existing cells, the contents of the cells will be

grandfathered in, meaning the data validation will not apply to them. But if you go over the top and enter in

something, then the data validation will come into play. Right now, I have a practice file called data validation

open. Feel free to use it to follow along. Now, I'm going to click in column H. This is where I'd like to enter my

validation. My validation is that I don't want incomes under 15,000 entered into the spreadsheet. So, they need to

be 15,000 or greater for them to be validated in column H. Now, I'm going to start by highlighting the cells that I'd

like to apply the validation to. I'm not going to include cell H1, but I'll highlight cells H2 down to H19. Then I'm

going to come up to the data ribbon tab and come over on the right hand side to the data tools group and locate the data

validation button. It's usually right next to remove duplicates. If I click on the button, it's going to open up the

data validation box. I want to start on the first tab at the left, which is settings. Now, right now, you'll notice

that it tells me that my validation allows for any value. This means there are no validations, but I want to come

in and tell it that I only want to allow whole numbers in these cells. Then, I need to pick the criteria. In this case,

they need to be whole numbers that are greater than or equal to. Then, I'll type in 15,000. You don't need to worry

about decimals or dollar signs because those are just formatting. Now, it's important to let your users of your

spreadsheet know what the validations are. So, to do that, I'm going to come and fill out the other two tabs that are

part of the data validation box. We're now going to come to the middle tab, which is input message. Just like this

name implies, this is a message that will come up as soon as a user clicks in the cell to let them know what they need

to enter. So, I'm just going to put in income amount, and we're just going to open the box up

again. And then below I'll type in the text to help let them know what they need to

type. You must enter an income amount 15,000 or higher. Very simple. Now, what if

they do enter the wrong thing? That's what the third tab is for. Error alert. With the error alert tab, there are

three styles of error alerts. But let me warn you that the only one that actually keeps them from entering an incorrect

thing is the first one, which is the default, the stop style. Again, I need a title,

which I'm going to call incorrect income amount, and then an error alert. You have entered an incorrect income

amount. It must be 15,000 or higher. Now to try out my data validation, I'll now click

on okay. The first thing I'll see is if I click in any of the cells in column H where I applied the data validation