Collaborate on Bilty Format in Excel for Accounting with Ease Using airSlate SignNow

Bilty format in excel for Accounting

welcome to Excel for accountants formulas functions formatting to build accounting templates now if you want to download this Excel file here and there's a start file and a finished file so you can follow along click on the link below the video hey this is the 2017 Washington Association of accounting and tax preparers Tacoma accounting conference at LaQuinta in July July 12th now we're actually going to have two videos at this conference the first one is about formulas functions and formatting and this entire list of topics if you scroll down there's 44 amazing accounting Excel topics to cover the second video will be power query and pivot tables to import and clean data and build reports now this entire list list of 44 topics is below the video if you look below the video in the show more area there's a table of contents with time hyperlinks so you can jump to any part of the video you'd like not only that but here in the Excel workbook every one of these topics is linked to the sheet in the workbook so if you want to jump to the today and eate functions example you simply click and it jumps to that sheet notice it jumped over to dates number formatting formulas and on each sheet the topics is a hyperlink to jump back to the table of contents so that means there's two table of contents one here in the Excel workbook and one below the video where you can jump to different parts of the video now this first video the first 15 topics I'm going to review all of the basic of excel while showing you how to build Excel accounting templates and then the remaining part of the video is just all sorts of great Excel accounting topics now our first topic is going to be about keyboards it's not going to be about features or functions it's about keyboards because in order to be efficient in Excel you got to know keyboards now I'm actually going to click right on this and jump over to the sheet it jumps right over here I'm on the add quickly keyboard sheet now the very first keyboard I want to show you is notice the active cell is A2 that means the selected cell if I scroll down to do something to look at something and notice I haven't clicked anywhere we often times do this but now I'd like to just jump back to that active cell use contr scoll backspace not only does it work when you have a cell selected but later when we're in the middle of creating a formula and we highlight all the way down we can jump back to the cell we're editing using control backspace now perhaps the most important keyboard in all of excel is how do you jump to the bottom of a data set or how do you highlight all the way to the bottom of the data set now we have some sales and cost of good sold and on this sheet I'm going to add over here but I want to know how tall this table is the keyboard to jump down to the bottom of this column meaning all the way down till it sees the first empty cell is controll down arrow look at that 12,000 rows of sales and cost a good Soul now I use the wheel on my mouse to scroll down what if I use control down arrow here well it didn't find anything so it just kept going to the bottom of the spread sheet not only that but I can do control right arrow wow that is the approximately 17th billion cell in this entire one spreadsheet now the keyboard to go back to cell A1 is control home now that's another important keyboard because a lot of times we're we're looking somewhere and I just want to jump not back to the active cell but to A1 so control home now I'd like to highlight notice we we have oh look at this a bunch of numbers and I want to add some number formatting now the next topic is number formatting as facade now most people in the world who use Excel think they know what number formatting is but unless you think of it as a facade that means when we apply number formatting it puts something on the surface of the spreadsheet that is different than the underlying number so if you don't understand number format as a facade you run the risk of err all right let's just prove that to ourselves but first I want to highlight the top two cells and now I need to jump all the way down and highlight everything so instead of just control Arrow we hold shift also control shift down arrow and just like that I've highlighted down 12,000 rows now in the home ribbon tab number group we have a great drop down for the most common number formattings now I want to do currency but this is one type of currency so another way to do this if you want more control is to use the keyboard control one to open up the format cells dialogue box now number alignment font border wow this is a great one for accountants we use that all the time fill and protection control one opens up this so we can get to any one of those hundreds of different options now I'm going to click on currency and the nice thing about this dialogue box is I can choose the way I want to display the negative number how many decimal places and symbol I'm going to accept the default sometimes accountants like to see the parentheses but I'm going to click okay and there we go wait a second how how is that facade the way it's facade is it put a dollar sign but not in the cell please go look up at the formula bar there's no dollar sign not only that but it looks like it and I'm going to use the wrong word here it looks like it rounded it it did not round it it just displayed it so that only two decimals are showing we can clearly see that the extra decimals are still in the cells now later we'll learn how to remove extraneous decimals because sometimes can get us in trouble with our calculations now our next step is I simply want to add all of the sales and then add all of the cost a good sold now most of us either type equals suum we don't need to do that anymore I'm going to click Escape Escape always reverts back to whatever the cell was before you put it in edit mode the other way that people invoke the sum function is to go to home over to editing and there's the sum function if I hover my cursor let me do this again hover my cursor you can see the keyboard is alt equals now there's 450 functions in Excel but only the sum function has the keyboard ready alt equals all to equals puts the sum function in now I'm very carefully going to click in the top cell and we're going to use that same keyboard contrl shift down arrow 12,000 rows instantly now I'm going to use control backspace to jump back to the active cell and look at that now instead of hitting enter since my goal is to put this thing in the cell and move to the right I'm going to hit the Tab Key uhoh they put a fence up we cannot see that simply means that the column is not wide enough I can come up and between D and E I can click and drag like that or I can double click and it best fits all right you ready alt equals oh it guessed wrong but remember when you are putting a range or any cell reference into a formula as long as the dancing ants are dancing around you're in full edit mode now I can click over here control shift down or control backspace that is amazing and enter doubleclick just that keyboard alone can save so much time now here are are some of the most important keyboards also contrl CVX lots of people rightclick lots of people use the clipboard don't do it not only are these faster but these keyboards work in lots of places where rightclick or the clipboard will not like sometimes in dialogue boxes another great keyboard is contrl z for undo anytime you want to undo something don't go up notice I don't even have the undo button because I altered my quick access Toolbar to remove it but normally that's that little blue back arrow don't do it contrl Z is what you do contrl y is redo not only that but another amazing keyboard is F12 why because since 2007 they made us go to file save as then we have to pick our fold then this thing pops up that's just too many clicks the keyboard to get straight to save as Escape Escape F12 so there it is every time you save as you've saved three clicks all right now let's go over to our next sheet dates number formatting and formulas now here's our topics and here are our notes I'm going to scroll down we in essence have three templates we we want to complete here now I accidentally left the formula here no problem control shift down arrow delete that's fast all right so here we have a days past due template it's simply the supplier name the invoice amount the due date we want to put today's date in and then calculate the number of days past due now if we want this to automatically update meaning every time we update this we just want our days pass du to tell us the current days pass do then we want a dynamic date using today function now I see the function in blue in my drop down I'm not going to type the whole thing out I'm not even going to get my mouse and double click I'm going to hit the Tab Key this is an argument function so I simply close parenthesis now I'm going to control enter instead of Enter if I hit enter that means I have to click back up and then copy it down anytime time your goal is to put something in the cell and keep the cell selected because you're going to do something to the cell please use control enter rather than enter now that cell is highlighted we can see the green outline all the way around and in the lower right hand corner that little green box is called the fill handle this right here this is the selection cursor when you move your selection cursor over the fill handle it turns to what Bill Gates called calls a cross hair I like to call it instead of a cross hair an angry rabbit and the angry rabbit is awesome because remember I can click and drag and it will automatically copy it down now wait a second that was a lot of rows that took much too much time so I'm going to control Z to undo that what we really want to do when we have a template that has data all the way down is simply move the selection cursor over the fill handle and with the angry rabbit double click it automatically copied it all the way down and it knew to stop because there was an empty cell now notice I use the wheel on my mouse I really shouldn't have done that I should have double clicked and send it down notice the active cell I should have just done control down arrow and there we go control up Arrow now today that's what you want want when you have it want a dynamic date but in accounting the historical date is very important now for this particular template we probably want a dynamic date but in lots of other situations we want to hardcode the date and there it is one of the greatest accounting keyboards ever let's see I even put it up here in bold because it's so important control semicolon control semicolon that puts in today's date now I can control enter and I can double click and send it down but wait a second that didn't work now we're going to see this a few times the amazing smart tag now in this situation when I hover over the smart tag there's a little triangle pointing downward when I click on it later we'll see this amazing feature down here but here I want to stop it from filling the series well wait a second that's really cool if you're creting a calendar right it automatically when you double click a date or copy a date in increments but here I want to stop it from incrementing so I click copy cells now another important date and time trick remember control semicolon is today's date but tab what if you wanted control semicolon but the current time then you type a space and do control shift semicolon that is totally cool and enter there we get our fence that means I need to click and drag now the days pass due well before I do this formula I want to tell you what a date really is now I'm going to come up here and I'm going to contrl semicolon and then enter then I'm going to type one SL one SL 1900 contrl enter and I'm going to copy it down and notice it will increment now why did I do that because really when I put a date in the cell look up here it has date number formatting date number formatting so if we look up to the formula bar it looks like there's a seven a slash a 7 sl27 in the cell meaning remember number formatting is a facad on the surface of the spreadsheet it looks like that's what's sitting underneath in the cell but in this case it's not that's why you have to be so careful with number formatting that's why in your head you have to think of it as a facade now I'm going to show you a great trick here if I highlight these right at the top in the number group in the home ribbon tab right at the top General notice no specific formatting I wish they called it the Eraser for number formatting because when you apply General it erases any number formatting that you might have applied so when I click general what 42723 1 two you got it dates are really serial numbers and I'm going to contrl Z to undo that serial numbers that count there's the first day in excel's Universe 11 1900 there's the second day and there if I control y to undo the undo or use redo to today is 42,900 days since December 31st 1899 now why in the world did they do that because of date math because there's a number underneath I am allowed to subtract or add dates to calculate the days between dates or adding dates numbers to dates and things like that now when I control Z you got to be aware that this is a case and same with time time is another one where you cannot see what's actually in the cell up in the formula bar now let's put this into action notice we have a later date in history and we're going to subtract an earlier date in history so it will give me the difference between the two now we're going to do a formula here and all formulas start with an equal sign and there's a few ways we can put cell references into our formula we can click with our Mouse and then I'm going to subtract and then click with our Mouse now I'm going to click Escape usually when the cell references are really close that's the slow way the fast way is to do equal sign Arrow Arrow minus Arrow Arrow arrow that is the rule if the cell references are close use the arrow keys on your number pad if they're far away you use a mouse now my goal is to put this in the cell and copy it down so I controll enter and double click and send it down control down arrow to go all the way down to the bottom I'm going to hit the F2 key I'm just verifying that the sour references that I put into the formula are working now actually those are called relative sour references notice d69 minus c69 now when you're copying a formula you do not want to think of it that way you don't even want to call it out that way that really is one two cells to my left minus one two three cells to my left it's eight control up arrow down arrow and F2 notice up here it looks like it's D20 minus C20 but it's really two to my left minus three to my left now notice that means enter F2 enter F2 those cell references are moving relatively throughout the copy action all right that was our days past du template how about this one we have borrower an amount an issue date there is the term in days and we simply want to calculate the maturity date equals sign Arrow Arrow remember underneath that date is a number so I'm simply allowed to add using plus and then Arrow contrl enter and there is the maturity date double click and send it down control down arrow F2 I'm verifying that the formula is working that's pretty cool control up Arrow definitely when the spreadsheet was invented in 1979 by Brooklyn and Frankston the fact that they thought enough in advance about dates and that they put numbers underneath so we can do date math definitely one of the more amazing things that Excel can do now here's our last example for dates we have employee and hire date and we need to figure out the pension vest date and our rule in the company is five years ahead now I definitely wouldn't want to do this five years ahead manually I don't even want to use the date and month and day function there's a special function that can take a date and jump a certain number of months ahead equals Eed tab the start date there it is comma uh-oh this argument says mon months and we have years no problem we'll take 5 * 12 and copy the formula down now let's just click either with your mouse or up arrow and that formula will not work because that is a relative cell reference that says please always look one two three above if I were to copy this formula down right now down the column that cell reference would move notice when I copy to the next cell this three above would move right there and it would be incorrect now let's prove that to ourselves I'm going to finish the formula times 12 close parentheses controll enter and I'm going to copy it down just one row click in that cell F2 that is not what we want that blue one is correct meaning always look one to the left so we're going to have to fix our formula up in that cell right there and lock the cell right reference now I clicked escapee delete I'm going to click in the cell and redo it equals Ed tab left Arrow comma up Arrow three times and now we need to learn how to lock it and the way you do it is you hit the F4 key that F4 key puts the secret code in which is dollar signs there's a dollar sign in front of the column reference p and the row reference 17 that means no matter where we copy this formula it will always be locked on p17 now relative Sol references as we copy the formula they move relatively locked they just remain locked now let's finish the formula times 12 and wait a second I typed a number into a formula the rule is if a formula input can change like the five or the date we use cell references to refer to the formula input but if the formula input is not going to change and in our case it's not going to change you can simply type it into the formula now that's actually called excel's Golden Rule and if you look up in the notes that's listed there now let's close parentheses control enter doubleclick and send it down controll down arrow and F2 we definitely want to always go to the bottom of the column and verify because for example what if I forgot to lock it then I would have noticed in the last cell that it wasn't locked and I could have fixed it all right Escape control home now I want to scroll over here and emphasize something about excel's Golden Rule notice hardcoded in Formula input is in the cell with a label and we refer to them with sell references no problem look at this mitsuko that's the incorrect hire date they actually got hired on the 16th so I simply change it hit enter and that works not only that but the person who told us five years they weren't reading the contract it's actually six years so when I change that and hit enter everything updates that's excel's Golden Rule not only that the the number one source of Errors throughout three decades of spreadsheet history is when people incorrectly hardcode variables into formulas now this is not the best example some of the famous examples have to do with interest rates and things like that where they hardcode them in or tax rates and they change and so we follow excel's Golden Rule Number can change we put it into a cell label it and refer to it with a cell reference all right now I'm going to contrl ZZ contrl home all right so on this sheet we saw a lot of date related tricks formatting functions formulas and even some cool keyboards all right let's go to the next sheet payroll quickly and round now most of us do payroll in some other system or have some system do for us but occasionally we have to make calculations so what we want to learn here and I shouldn't have had all of these in here notice luckily I'm deleting those what we want to see here is we're going to have to make a calculation and we're multiplying gross pay times tax rate and yes as we copy the formula down we're going to need relative sell reference and this one will have to be locked but we're going to run into some trouble here when we're multiplying decimals especially when right here that 75 that's past the penny position anytime we're multiplying decimals like this and we might have extraneous decimals we better be careful because all of these results were actually going to add so we want to talk about the importance of the round function now let's first just make our base calculation so I'm going to click here equals left Arrow time right right arrow F4 that calculation should work controll enter I'm going to double click and send it down uhoh double click and send it down in the latest version of excel that it used to stop right there but now because we're connected with column headers or field names at the top it thought this was the bottom but no problem contrl Z and now I'm going to copy it down now I want to come to the bottom and ALT equals I see the Dancy NTS are surrounding the correct cells so I hit enter now the problem with what we did is there was already number formatting applied not only that but if I click right there and look up to the formula bar I have a formula I can't tell if there's any extraneous decimals now if I was given this template I would immediately highlight these cells go up to the dropdown and apply General I am going to teach you the keyboard for General since it is so important when diagnosing number formatting problems control shift either grave accent or Tilda applies the general number formatting in essence it's our eraser to remove all the number formatting I see lots of extraneous decimals anytime that happens and we're using these formula results in a subsequent formula you run the risk of error here because that sum function right there does not see number formatting so if I escape and contrl Z number formatting down here that sum function does not see the number formatting all formulas look at the underlying number that means sum is adding all those extraneous decimals anytime you have that situation you run the risk of an error so let's redo our formula equals now I'm going to hit F4 we know that's going to give us extraneous decimals and we want to use the function that will round automatically for us now a lot of times you have to guess the name of the function like sum or average or PMT for payment on a loan round luckily they named it smartly now if I type R O I can see right away that there's a bunch bunch of round functions I can use my down arrow to get to the function I think is the correct one I can read the screen tip rounds a number to a specified number of digits now this function does our standard rounding now notice it's highlighted in blue I'm going to hit tab now the round function requires a number it's almost always some formula that we create and then it needs to know the number of digits so very carefully at the end not the elction cursor you got to make sure you see your ibeam cursor click type a comma to get to the next argument now number of digits what do we do here I always start at the decimal and count on my fingers one two since I'm rounding to the penny I need to put a two if I was starting here I could go one two 3 four to whichever position I wanted to the right but what if like in some income tax situation you needed to round to the dollar well if I start at the decimal and go 1 2 3 going backwards the other way I would go 3 2 1 zero minus one min-2 Min -3 would be to the thousands positions like sometimes for financial statements so for number of digits I'm typing a two close parentheses controll enter I'm going to copy it down click in the last cell alt equals and enter and sure enough when I properly round it which I'm required to do because this is money these are actual amounts that are going to be subtracted this is an actual amount we need to total and report on our tax form there it is a one penny difference now the tax Authority is not going to come after you for a penny nobody's really going to be harmed if it's a penny off however the reason that round is so important on invoices and payrolls and monetary calculations where we're going to be adding them up in a subsequent calculation is because if someone sees that you're off by a penny they no longer trust your calculation and here's the sad truth about Excel users on the planet Earth most people do not use round in the right situation that's why we're looking at the round so what do we do when we are using money we're required to round we have extraneous decimals and we're going to use the formula results in some subsequent calculation you got to use round there are the rules right there one last note here we didn't round these but if these were just sitting off to the side we were never going to use the result in another formula that's perfectly all right we're just looking at it because guess what the number formatting displays it just like the round function actually Returns the correct number so if you're just looking at it it's fine but in our situation we're using the result in a subsequent formula so we had to round now let's go look at our next example we're going to build an amortization table now as soon as people hear amortization table oh people get nervous because they're often times huge with lots of calculations but not an Excel it's quite easy to build an amortization table in Excel all right here's our loan 575,000 it's an N which means the payment comes at the end of the period there's a balloon payment at the end $100,000 the annual rate is 4.75 30-year consumer loan number of periods per years they have to pay monthly here's the first payment date now we need to calculate the period rate because we're going to use the PMT P function to figure out the monthly payment and since the actual payment is monthly we need monthly rate and total number of months all right you ready the monthly rate is equal and I'm going to up Arrow to 4.75 and we're going to divide it into 12 parts so divide and then up Arrow up arrow and enter now notice that is an unformatted decimal which is perfectly fine that's what we're going to use in our calculation you could format it with percentage number formatting if you want all right total number of periods I'm going to up Arrow to 30 times and then 12 and enter so 360 now how many people have memorized the annuity formula that's been around I think it was Fibonacci back in maybe about the 12th century or something I can't remember my exact year but a long time this famous annuity formula has been in exist luckily we don't have to have it memorized we just use the PMT function and you can read the screen tip calculates the payment for a loan based on constant payments and a constant interest rate and that's what we have so I'm going to hit tab now we read each one of the arguments here in our screen tip if you're not quite sure what each one means look at this right there on each function screen tip is a hyperlink if I click on that it opens up help and there it'll tell you what each one of the arguments mean including an example down below I'm going to close that the rate that's the period rate so I'm going to up Arrow up Arrow comma NP R that's number of periods up Arrow comma present value that mean what is the current worth of the loan now when you're using Financial function functions whether PMT or FV for future value or PV for present value or rate for the rate or NP to figure out number of periods you have to know cash flow now from the borrower point of view that cash flow that's the present value it's positive even though they immediately give it to the seller because they're buying a house that cash flow comes in to the cons consumer's pocket as a positive cash flow from the bank so I'm going to click on this present value the loan amount up here and enter it as a positive number now comma future value that means at the very end is this investment or is this annuity worth anything well there is there's still $100,000 left on the loan now I can't just click on this 100,000 because again we have to think point of view from the consumer point of view that balloon payment is coming out of their pocket out of their wallet or purse back to the bank so I very carefully have to type a minus and then click on the 100,000 comma type we have an end now something very important about all functions in Excel whenever you see the square brackets around a function argument it means if you know the default you can leave it out now if you go to help it'll tell you and is the default so we don't even have to put that in now you could double click the zero and leave it in in general once I use functions a lot and I know what the default is I do not bother because it saves a bunch of Click now comma be sure if you're going to leave it out to get rid of the comma too and when you see it no comma type in square brackets I want the default that is find close parentheses controll enter now why is that a minus these functions no cash flow of course each month that amount is coming out of the borrower's pocket all right so we have our calculations up here now we need to build our amortization table now I definitely don't want to type 0 1 2 3 and so on I don't even want to calculate or I'm sorry not calculate highlight type two numbers in to establish a pattern and then click and drag that would give me all the way down to 360 no there's a much better way simply type a zero now there's two ways we can get this to automatically extend down to 360 the first way is to go to home over to editing and fill and fill series now I do this a lot and I don't like to use my Mouse and go up there since I'm usually since my mouse is usually real close I simply go to the fill handle hover my cursor and when I see the angry rabbit I rightclick drag down one cell drag back and that opens up a secret menu and I point to series now let me do that again angry rabbit right click drag down drag up Let Go point to series since we want to fill down the column you got to select columns often times I forget and I'm in a hurry and I come down here and fill out this and then it shoots it throughout the row I want this to go down the column so I click column step value was one and that is beautiful 360 it will stop at 360 I click okay control down arrow that is beautiful I'm going to control home to jump all the way up to sell A1 now due this is actually the due date I'm going to hit F2 to put it in edit mode and put the cursor at the end space date and enter enter now the first date is 73127 73120 2017 now control enter and watch this I'm going to use this to double click if I double click it sent it all the way way down stopped at Row 360 for this amortization table but that's not what I want I don't want every single day I want end of the month for every single one well that's where the smart tag is amazing I hover over the downward pointing arrow click and it I have all sorts of options Phil weekdays I could build a template with just weekdays months that's the one we want years I'm going to say months and look at that it knew to go to the end of the month including look at this it knows leap year 229 220 now as you noticed so far I always have um green cells while I'm teaching to differentiate between the raw data the input data and the cells with formulas almost always in your final reports you don't want that but I tell you something when I build templates for my own use I follow this convention because it just helps me figure it out now I want to add some borders to this before I build any of the formulas and I don't want to like highlight with my mouse and highlight all the way down I don't even want to highlight here and because the active cell is right there I could control shift down arrow that highlights everything I'm going to control backspace to jump back to the active cell here's the fastest way find asteris on your number pad and do control asteris or if you don't have a number pad you have to do control shift 8 that's the fastest way to highlight the current table now it wouldn't work if this template was touching there if I control ASC here what it got everything else contrl Z the way that keyboard works is that the empty cells are all the way around so control asteris I've highlighted the whole table now control one to open up format cells and there it is borders now here's none that will get rid of everything I'm just going to click outline and inside well wait a second I already see that didn't work so I'm going to click none what I really wanted was just a thin line everywhere so now I select my line if I wanted to change the color I'd select the color then I come over and click click to put them everywhere click okay now that's not quite what I want a lot of accounting templates have these kind of lines everywhere but then a double line to differentiate the columns now notice when we highlight this there's something around the outside that's the Outside Inside the button we clicked went both directions but notice we want just vertical so we'll keep that in mind controll one outline did the whole outline inside did everything but we want just that one line right there now if we want them on the outside too then we'd have to click all three vertical lines no problem let's try that click on the double line I select the line I want color we could change it now you could use these buttons right here I pretty much never do those because I like to draw myself I click on the line click on the line be careful if you click diagonally it'll do diagonally so I click a second time to get rid of them and then there we go click okay click off to the side maybe that's not what you want I'm just going to click on balance control ASX control one I think I want this medium line around the outside so I'm going to say outline click okay now I click off to the side that's more what I want all right so now we need payment now notice payment did come out negative and in general in our amortization schedule we don't show it as negative so instead of typing equal sign then negative then clicking on the cell way back in spreadsheet history we didn't always use the equal sign we could use the plus we even way back had to use the at symbol but just to save a key here since I want a negative I'm going to type just a negative sign then click up here since I need the same number all the way down I'm going to hit the F4 key to lock it now I'm going to controll enter cuz I first off want to look up to the formula bar whoa look at that if I use a plus or a minus it'll automatically put the equal sign in which is convenient and now I want to double click and send it down so I'm going to double click with my Angry rabbit all the way down control down arrow F2 I'm verifying and sure enough that looks good Escape control home now the balance is what we need next that of course is equal sign and I'm going to click on the starting loan amount and enter interest well there's a different interest each period and it's simple how this works that's the amount that's been sitting in the account for the month so of course we multiply the period or monthly rate in our case times that now as we go down we will have a different balance because we'll subtract the principal reduction so for every single interest paid it'll always be one cell up two cells to my right as a relative cell reference times the rate so right here equals one cell up two over as a relative cell reference times and I'm going to click on my rate f for to lock it now I'm going to hit tab I'm not going to copy this one down yet because I want to show you a cool trick now principal reduction equal sign I take Arrow Arrow that's the full payment minus that's a lot of interest in the early stages so that's our formula tab our balance equal well whatever was above as a relative s reference from the period before subtract left Arrow whatever the reduction was for this month controll enter now here's a great trick we have three different formulas I simply highlight all three and there's my fill handle when I see my angra rabbit double click and send it down there's the active cell I'm going to control downl because I want to verify F2 and sure enough there it is locked up on B8 and it's looking at the correct balance tab F2 that's working tab F2 that's working two and notice there's the exact balloon payment now if we were to properly round this like we're probably supposed to cuz this is dealing with money this would be a few pennies off right you know that last payment is always a few pennies off but if you don't use the round function of course it has to come out exactly that 100,000 control home if we were to round the only calculation we would need to round is this one actually we can do this I click before the payment r o you I see round I tab I very carefully come to the end comma we're dealing with with money so it's 2 to the Penny close parentheses and controll enter that formula of course would be correct all the way down because it's always pointing there this this one actually has to be rounded also we have lots of extraneous decimals I could come over here round tab very carefully at the end comma to close parentheses controll enter double click and send it down we do not have to round that CU both of those are already rounded correctly nor do we have to round that control down arrow and we will be a few pennies or a few a dollar and a few pennies off all right controll home that's an amortization table now let's go over to our next sheet invoice all right I'm clicking on the sheet invoice and on this sheet we want to learn how to build this invoice template now here's our topics for this sheet including the ever important vlookup function and and data validation list to get a drop-down list in the cell now V lookup and the IF function we'll see both of these functions on this sheet are amongst the more important function so I have a bunch of notes off to the side not only that but we're only going to see one example of vlookup and since it's so important I put reference videos down here for my Highline Excel class which have 29 other examples of vlookup and lookup function functions now here's our started template we have the boomerang name and we have the quantity here now our first goal is to look up the price then we have to calculate line item total and then our subtotal and invoice total now let's think about how we would look something up if I asked you how to go get the price for Flatt top Boomerang you'd have no problem well wait a second we better scroll down now you'd have no problem you'd actually look at the text flatop and go oh I need to go look up the price for Flatt top you would know that the lookup table is down here you'd race through the First Column find flat top jump over to the second column you'd see that it's $12.3 and then you'd come up here and type $12 36 now look at what we did fundamentally yes we looked up the text flat time and found a match but fundamentally we went and got something and brought it back to the cell that's what V lookup does now let's see how this works I'm going to type equals VL and there it is vlookup tab now why the V because most lookup tables are vertical we're trying to find a match in the First Column and it's set up so we search it vertically now the lookup value that's just like we need to know which item to go and try and find in the First Column so I click on that as a relative cell reference now I type comma table array the way I always remember what that argument is asking for is it has the word table yes you need to tell it where the lookup table is so I highlight and this is one of these examples you do not highlight the field names at the top just the actual in our case products and then price now I need to lock this as I copy down so I hit the F4 key comma column index number now we would just know to read wholesale cost and go and get the price from the wholesale cost but notice we gave vook up an entire table so we have to tell it which column number within this table one two now this is a small table some tax tables or some employee information tables are huge so we might look up in the third fourth fifth sixth or whichever column ours happens to be two that means the thing we want to go and get and bring back to the cell is in the second column so we simply come up and type a two now I type comma and there's two types of lookup now exact matches what we're doing that means we look up in in the formatting is blocking that but we look up exactly flat top and find exactly flat top that's exact match approximate matches when you're looking up taxes or commissions so you have to choose which one and if you select true or select false it tells vlookup function which of the two vlookups to do now I'm going to down arrow and tab to put false in now I'm going to backspace for a second because true and false can be represented with one and zero so that's the way I tend to do it I'm doing exact match so you can put false or type a zero close parentheses controll enter na yes vlookup is being polite it means whatever it was looking up is not available it could not find it now we're looking at that right it looks like I see a flat top down here but either it's spelled wrong here or here so I'm going to start here and the F2 key we've been using to put in edit mode it's a great key because it will always put the cell in edit mode and put your cursor at the end and in this case we can clearly see someone mistyped it if I backspace and now hit enter Now vup can do its job because Flat Top is matched with flat top but that brings up an issue I don't don't want humans to be messing up with an extra space so every time you have a lookup table and your list of valid items that can go into the cell we might as well use data validation drop-down list now I'm in F2 and backspace and hit enter now let's highlight this and we're going to go up not to home but over to the data ribbon Tab and over in data tools there's something awesome called Data validation I'm simply going to click now on some of your screens this might be a really small icon like that right there so you got to look for it but if you hover you'll see data validation all right I'm going to click data validation Now the default is any value that means anytime you open up a spreadsheet with the 17 billion cells every cell automatically allows you to put anything in this cell but we want to validate what goes in now this drop down here is amazing you you can validate a certain range of whole numbers decimals dates times length but the one we want is list so I select list and there it is the source I simply highlight the valid items and notice look at that inell dropdown and we want to ignore empty cells there so we keep those checked and click okay now I am not going to be allowed to put flat top with the space I simply select select from the dropdown you can type it also if you know it like I could type Bellin and then hit enter But if I had an extra space or something like that it just wouldn't let me if I typed this and hit enter it gives me a message now I'm going to click Escape highlight all of this go back up to data validation You by all means can put an input message that means as soon as you click on the cell it it gives you the input message and you can type your phone error message I'll leave that for you to do now I'm going to delete this Bellon now we're going to copy this down and we're going to run into trouble because na it is looking up nothing an empty cell and it can't find it over here so we need to amend our formula because if this is a template we need to be able to clear the template and use it again no problem we have to learn about the IF function now I'm going to hit F2 here notice here we want to run the VL up but down here what I would like is to have nothing in the cell so for this whole column I want to put one of two things in the cell I either want to put the V lookup or I want to put nothing in the cell anytime you have a situation where every single cell is going to have one of two things that's the perfect job for the IF function now if you have the IF function you also have to find a logical test now there's all sorts of possibilities here I could ask the question has anything been entered in the cell true or false if something's entered in the cell true then I put the formula otherwise I show nothing if I wanted to use a different logical test I could instead ask the question is the cell empty if the cell is empty then please show nothing otherwise that means if that logical test comes out false please run the vlookup so I'm going to come up to the top here F2 and right after the equal sign I'm going to type if tab now the IF function simply needs your logical test and then the rest of it's easy what do you want to put in the cell if it's true what do you want to put in the cell if it's false now again there are many different possible logical tests here I'm going to use the function ISB now it should say is empty because the word blank is sometimes ambiguous but really if you look at the help it says it checks to see whether the cell is empty and if a relative cell reference remember we're copying this down if that relative cell reference contains nothing meaning it's empty it will will return a true now I'm going to come after that closed parentheses and now I have to type a comma remember we built our logical test to come out true when it's empty so what do we want in the cell if it's empty well we would like nothing but there is no way to put nothing in the cell there technically is no nothing function so you have to use the syntax double quote double quote now I'm going to type a comma there's the value of false but let's go back to the value of true down here where there's nothing every one of these cells will actually contain that double quote double quote it is the syntax we use to show nothing now technically it's called a zero length text string because there will be text in the cell that has zero length but it is the syntax we use to show nothing so there it is every cell will either get when that comes out false the V lookup or when it comes out true the double quote double quote to show nothing now I come to the end close parenthesis control enter and I'm going to copy it down that's pretty amazing and watch what happens when I come over here I select quad instantly now that cell does not have double quote double quote the vlookup is allowed to run now I can put five here they bought five all right all right so we did that part of our invoice template now let's do the line item total equal sign left Arrow times left Arrow left arrow and we don't have any extraneous decimals here so I'm not worried about any incorrect totals down here when we sum controll enter and now I'm going to copy it all the way down uhoh now we get value error over here we got not available because when it tried to look that up it was not available but value means that you either entered the wrong thing into a function or you're doing something with an operator like multiplying that you're not allowed to do that is an empty cell that is text now I want to show you a trick here to prove that actually an empty cell inside a formula yields a zero and that is just clearly going to be text Let's Escape for a second and I want to go up to the formulas Tab and there's an amazing feature called evaluate formula if I click on this it will actually step through and tell us what's happening with the formula now c14 is underline that means when I click evaluate or hit enter oh look at that it shows the double quote double quote which is a zero length text string now it's going to evaluate the B4 14 which is the empty cell when I hit enter oh it's a zero you are not allowed to take text and multiply it by a zero now this is sort of an abstract example but imagine if you tried to multiply the color red meaning red times 3 well that doesn't make any sense that's what a value aor is all right I'm going to click close or Escape no problem we will do the same test if the cell is empty then show nothing otherwise run the formula now here's a great trick I'm going to put this cell into edit mode with F2 and I'm going to cheat I'm going to highlight all of that contrl c Escape come here F2 and right after the equal sign I'm going to control V because that's the same logical test we can use it's just a different formula is going to be dumped in the cell if value is false close parentheses controll enter and copy it down that is pretty amazing we're learning how to build a template using the data validation and if is blank V lookup and then over here if is blank and our formula now we can come down here alt equals enter that shipping cell is always going to be empty until you put something so I type 15 and down here I want to Alt Enter oh it guessed wrong remember as long as the dancing answer going you're in full edit mode controll enter now to follow my convention I held control and clicked on both cells going to go up and just add a slightly different green now again if this was a template you probably wouldn't do that but that's just telling us that the white cells have content the green cells have formulas and there is our template there's the topics we covered and notice I had round here but because we didn't have any extraneous decimals we didn't use it when we were multiplying now if I got this spreadsheet I of course would go investigate I would look down here F2 look at that this is the wholesale cost it's pointing down to this sell F2 and sure enough someone made a formula list price trade discount and it's properly rounded we also could have up here notice it came with number formatting so we should have done our control shift Tilda to check it out if there were any extraneous decimals contrl Z all right so that's an invoice let's go over to our next sheet mixed cell references now on this sheet we want to build a template that will tell us for any intersecting cell when an investment of $4,000 at the end of each year at a given rate and a given number of years will be worth in the future now in this case we have a variable that's at the top of each column so this whole column is going to need 2% and we have at the head of each row the number of years now we're going to use the future value function and the inputs are simple it'll be the annual amount annual rate and number of years the problem is most cell users on the planet Earth would have to create 11 different formulas meaning we'd lock the rate and the 4,000 but we'd let the years move relatively then we'd create the next Formula same situation locked locked moved relatively copy it down we don't want to do that what if there was a possibility so right in this cell we just lock the 4,000 in all directions because every one of these cells needs the 4,000 but for this column as we copy down we need it locked on the 1.5% but we need this to move relatively when we got down to this row what if our cell reference instead of being permanently locked on 1.5 it was just locked when we copied down and when we copied it to the side this cell reference would move to the new column header and this one would remain locked on 11 we can do that with mix cell references now I've been teaching Excel for almost two decades and this without a doubt is the hardest topic for people to get I'm not quite sure why it's so hard but I do know that when I learned it was super hard now let's just review if we were looking at a relative cell reference that's a bunch of cells that way contrl enter I can copy it down right it moves relatively if I was to copy it to the side I see a zero because it moved exactly as a relative cell reference it moved to the right when I copied the formula to the right so that's relative cell reference but what if we locked it well of course F4 there's two dollar signs and that's because there's a column reference and a row reference more fundamentally it's because there's two directions when we copy formula across the columns or down across the rows so this one when I control enter it doesn't matter where I copy it I can copy it any direction and it's always locked because when I copied it across the column the a remained locked when I copied it down across the rows the 10 was locked now contrl ZZ let's do that one more time but in this case let's hit the F4 key and now we're going to see that the F4 key is really the merrygoround key if I keep hitting F4 F4 F4 it's a merry go around it toggles between relative absolute mix cell reference with the row locked only and mix cell reference with a column locked only so you can toggle between as much as you'd like let's stop and lock only the row now when I copy it down that 10 is not going to go anywhere but when I copy it to the side the a will be allowed to move to B so controll enter I'm just going to copy it down one and over down here I could see F2 the 10 is locked but when I come over here F2 the a was allowed to move to B now let's try the other direction F2 and I'm going to hit the F4 key one time now the a is locked so as I copy it across the columns the a will always remain locked but there's no dollar sign in front of the 10 so when I copy down it will move to 11 12 and so on controll enter copy it down and over now F2 there's a 10 enter F2 it moved to 11 F2 there's an a tab F2 it's still a because the dollar sign locked it now delete those are abstract examples let's go over and try our future value example with our future value function now you can already see the situation when I copy this down I'm going to lock it on the row for the rate but when I copy it to the side we'll leave the column free to move so notice it'll move from B to C getting the next interest rate and when we have our input from the year into our formula we let the row remain free to move but when it copies to the side we will lock it on the a column all right let's try this equals FV that stands for future value it calculates the future value of some investment now our rate this is our annual rate now watch this as I copy down I need it to be locked on nine so I'm going to hit the f4q once and twice but when I copy it to the side it needs to move from B9 to C9 so that will work comma NP R for us it's number of years so I click on A10 when I copy down I need that 10 to move to 11 and 12 so I'm not putting a dollar sign in front of the 10 when I copy it to the side I want a to remain on the a column so I hit f for one two three times that will work now we type a comma and our annual payment at the end of each year is going to be this $4,000 but we're doing this from the point of view of the investor so the investor has coming out of their pocket $4,000 each year so we have to acknowledge that cash flow with a minus so now I'm going to click on that 4,000 and that's locked in all directions F4 comma we do not have a present value that would be any amount at Time Zero in our account this in essence is going to be a savings plan so at the end of the upcoming year we'll put our first amount in now comma type this is end so as we talked about before I'm going to backspace the default is end I'm going to backspace again because I don't need present value either because there is none close parentheses control enter copy it to the side side double click and send it down immediately go to the diagonally furthest cell away clicking that cell F2 to verify that the cell references are working and look at that that is absolutely beautiful I still remember the first time I did mix cell references and how it transformed my ability to create templates like this quickly there is the proper column header 6% there is the proper row header 20 years and sure enough it's locked on 4,000 now one last thing if we highlight all this and home General right there or use the keyboard control shift Tilda we have all sorts of extra decimals there past the penny but guess what it doesn't matter here because we're not using any of these calculations in any subsequent calculations so contrl Z I'm going to leave there each one of those we're just looking at now let's go look at our next example we're going to talk about the sum ifs and count if function and we'll see another example of mixed cell references now sometimes we want to add and sometimes we want to count but really most of the time we are not adding an entire column most of the time we're adding from a data set with some conditions or criteria so for Sue if I look through this list I can see there are one two three four records for Sue so really I want to pick out these numbers and add them but when I copy the formula down I want the formula to then add for Amira and chin and Monifa not only that but I'd like to do the same thing for counting anytime you are adding or counting with conditions there's a number of functions in Excel for adding we have to think of the word sum so I type sum and there's a real old function Su if without the s that was before Excel 2007 now we use sum ifs because Su ifs can do everything that Su if can do plus much more now Su if just adds with one condition some ifs can add with one or more conditions so I'm going to use that function and tab now also the reason we use Su ifs instead of su if without the S is the actual name of the argument makes it easier to interpret what you put into the function now the first argument is Su range that means please give me the column you want to potentially add so I'm going to highlight this column right here and hit the F4 key to lock it in all directions comma criteria range and I'm going to type comma criteria notice that argument doesn't have the word range backspace that one does that means you have to give it the entire column in our case sales rep so I'm going to highlight sales rep and hit F4 comma Now we put the criteria that some ifs can use to start picking out which records to use for us it's a relative sence and that's the formula for adding with a single condition close parentheses control enter and copy it down now I clearly see Monifa over here so just as we saw saw earlier in the video you always have to be careful we're typing these in right you got to make sure F2 look at that and extra space I always leave little things like that when we shoot these videos because we got to remember to check those kind of things if you get control z a zero answer that's good that says there's either zero as the answer or there's some trouble either you did your formula wrong or the input or the column the actual data and the column is incorrect for us luckily I can simply backspace and get rid of that extra space now similar to some FS if we want to count we use count and there's a bunch of counting functions count counts numbers count du counts not empty cells count blank of course counts empty cells count if that's the old one and count ifs is what we want that means we're going to count if something is true criteria range I'm simply going to highlight the entire column F4 comma and then I'm going to left Arrow to get the criteria controll enter and doubleclick and send it down I go to the last cell and hit F2 I should have done that over here to F2 checking if all the cell references are looking right now those two formulas are adding and Counting with a single condition here is what's called a cross tabulated t table and for any intersecting cell we want to look through the product pick out trifly and then also look through sales rep and pick out a miror notice just like our future value table for this whole column I need to look at the column header quad for product but when I copy over it needs to move to Bellon for this whole row I need to look at chin but when I copy up I need to move to a mirror so no problem we're going to count so equals count it anytime you're counting with one or more conditions we can try the count ifs function now criteria range oh wait a second which one do I put in first well now that we're going to use multiple criteria that means we're doing an and logical test so in order to find account for Eagle sue the record has to find Sue as the sales rep and Eagle as the product an and logical test can be done in any order so it doesn't matter which column I pick first I'm going to pick sales rep first so I highlight F4 comma Arrow to get Sue now think about this as I copy down I need the 11 to move to 12 and then to 13 but when I copy over to the next column I need to stop the F from moving so I'm going to hit the F4 key one two three times and that will work comma second column is product so I highlight hit the F4 key comma and up Arrow now Eagle as I copy this down I need G10 to be locked G10 G10 G10 all the way down but when I copy the whole formula to the side the green cell needs to move from the G column to the H column to pick up the triflight so we're definitely not putting a dollar sign in front of the G only in front of the 10 so I hit the f4k once and twice that formula will do it close parentheses controll enter I'm going to copy it to the side double click and send it down go to the last cell F2 I'm verifying that all of the cell references are working fine uhoh Monifa I can't type Mona's name F2 backspace and enter now one important note about this cross tabulated table or even this small report up here we actually later in this video will see how to create these r