Collaborate on Billing Statement Sample Excel for Accounting and Tax with Ease Using airSlate SignNow

Billing statement sample excel for Accounting and Tax

[Music] welcome to learn it training 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 learn its Excel for Finance and Accounting part one course my name is Elissa 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 Advan adved 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 ad free 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 certific ific Ates 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 AR 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 sheet s down in the bottom left hand 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 one 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 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 quick I love the keyboard shortcut control 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 sell 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 leftand Corner up on the toolbar the quick access toolbar it will bring it back what if 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 selct 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 the same 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 leftand 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 paint 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 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 left hand 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 in 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 double click 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 spread sheet 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 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 cam 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 C 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 we 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 spreadsh sheet 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 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 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 sign 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 double click between the columns where you see those number signs and it will autofit 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 2 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 sells A1 through G1 one of my very favorite buttons the merg 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's 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 for my home ribbon t 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 re highlight 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 vile I've opened 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 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 paints 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 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 paines button again and say unfreeze pains 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 2 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 toolbars 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 left click 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 left click 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 short CS 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 contrl a on my keyboard and 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 control end now the End Key end D 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 contrl home and control end 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 contrl 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 crl a control home contrl end 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 keybo board 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 auto sum button now the auto sum 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 auto sum 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 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 and sell 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 auto sum 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 auto sum 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 b 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 auto sum button for this so I'm going to click in cell B13 go up and click on the Arrow of the auto sum 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 sell 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 b63 B9 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 Autos sum button to help you create these functions as always be careful with the Autos sum 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 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 and sell O3 and to help me do my if statement I'm going to use a tool called the insert function button but 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 that 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 their 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 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 be 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 if hyphen 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 Le hand 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 Su 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 some if and not some ifs when you select the function name double click 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 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 shortcut contrl shift 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 contrl shift down arrow so here we can see the three arguments of the function my range which contains my criteria so it sells 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 left hand 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 auto 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 short cut to select that range and in the bottom left hand 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 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 hyen 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 d too 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 auto complete 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 parentheses 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 parentheses 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 parentheses 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 13 43 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 okay I have some pretty complicated decimal places that represent the customer's satisfaction rate I want to simplify that so what I'm going to do is I'm going to click and 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 ARG 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 right click 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 CR 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 Flash Fill 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 rep 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 form 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 in 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 account 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 I I sells I2 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 parentheses I'll hit enter and we'll see that there's a 97 and really this is correct now if I come to sell M2 it gets trickier I 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 five six 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 learnet Microsoft Excel for Finance and Accounting part two course my name is elisssa 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 look at our vlookup and xlookup we'll also be exploring some of the scenario management tools like scenario manager and goalseek 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 schu schules 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 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 parentheses 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 ow 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 parentheses I don't need to do the closing parentheses 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 too 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 control 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 up date 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 this 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 an 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 if 15,000 or higher very simple now what if they do enter the wrong thing that's what the third tab is for