Excel for Accountants: Formulas, Functions & Formatting to Build Account Templates (37 Examples)

welcome to Excel for accountants formulas functions formatting to build accounting template 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 La Quinta Inn July 12 now we're actually going to have two videos at this conference the first one is about four of those 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 of 44 topics is below the video if you look below the video in the show more area there's a table of content 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 eat eight functions example you simply click and it jumps to that sheet those had jumped over two 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 basics 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 gotta 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 keyboards sheet now the very first keyboard I want to show you is notice the active cell is a two that means the selected cell if I scroll down to do something to look at something to notice I haven't clicked anywhere we oftentimes do this but now I'd like to just jump back to that active cell use ctrl 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 where editing using ctrl 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 fit now we have some sales and cost of goods 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 ctrl down out look at that 12,000 rows of sales and cost of goods sold now I use the wheel on my mouse to scroll down what if I use ctrl down arrow here well it didn't find anything so it just kept going to the bottom of the spreadsheet 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 where we're looking somewhere and I just want to jump not back to the active cell but to a1 so ctrl home now I'd like to highlight notice 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 this 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 it's a facade you run the risk of error 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 ctrl arrow we hold shift also control shift down L 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 formatting 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 dialog 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 dialog 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 OK and there we go wait a second 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 world 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 that 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 the cost of goods sold now most of us either type equals S um 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 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 2 equals puts the sum function it now I'm very carefully going to click in the top cell and we're going to use that same keyboard ctrl shift down arrow twelve thousand rows instantly now I'm going to use ctrl 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 oh 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 in it best fits all right you're ready alt equals oh it guest's 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 or ctrl shift down arrow ctrl backspace that is amazing and enter double click just that keyboard alone can save so much time now here are some of the most important keyboards also control CVX lots of people right-click lots of people use the clipboard don't do it not only are these faster but these keyboards work in lots of places where right-click or the clipboard will not like sometimes in dialog boxes another great keyboard is ctrl 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 there's that little blue back arrow don't do it control Z is what you do control 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 folder 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 alright 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 want to complete here now I actually left the formula here no problem ctrl shift down arrow delete that's fast alright 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 past due to tell us the current days past due then we want a dynamic date using two day function now I see the function in Lewin 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 list function so I simply close parenthesis now I'm going to ctrl enter instead of enter if I hit enter that means I have to click backup and then copy it down any 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 ctrl enter rather than enter now that cell is highlighted we could 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 calls a crosshair I like to call it instead of a crosshair and 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 too much too much time so I'm going to ctrl 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 used 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 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 hard-code 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 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 creating 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 Pat is totally cool and enter there we get our fence that means I need to click and drag now the days passed do 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 control semicolon and then enter then I'm going to type once last ones last 1900 control enter and I'm going to copy it down and notice it will increment now why do they do that because really when I put a date in the cell look up here it has date number formatting date number for many so if we look up to the formula bar it looks like there's a 7 a / 7 / 2017 in the cell meaning remember number formatting is a facade on the surface of a 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 format 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 by highlight these right at the top and 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 forty two thousand seven hundred and twenty three one two you got it dates are really serial numbers and I'm going to ctrl Z to undo that serial numbers that count there's the first day in excels universe one one nineteen hundred there's the second day and there if I control Y to undo the undo or use redo today is forty two thousand nine hundred and twenty three days since December 31st 1899 now why in the world that 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 and numbers to date and things like that now when I control Z you've 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 form of the 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 - arrow arrow arrow that is the rule if the cell references are close use the arrow keys on your number pad if far away you use a mouse now my goal is to put this in the cell and copy it down so I control 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 cell references that I put into the formula are working now actually those are called relative cell references notice D 69 - C 69 now when you're copying a photo 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 - one two three cells to my lap escape control up arrow down arrow and f2 notice up here it looks like it's D 20 - C 20 but it's really - to my left - three to my left now notice that means enter F to enter F to those cell references are moving relatively throughout the copy action alright that was our days past due 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 equal sign arrow arrow remember underneath that date is a number so I'm simply allowed to add using plus and then arrow ctrl enter and there is the maturity date double click and send it down ctrl 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 Bricklin 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 vast date and our rule on the company is five years ahead 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 e D tab the start date there it is comma oh this argument says month and we have years no problem we'll take five times twelve 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 copied 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 twelve close parentheses control 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 reference now I click the skate delete I'm going to click in the cell and redo it equals e D tab left their own 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 seventeen that means no matter where we copy this formula it will always be locked on P 17 now relative cell references as we copy the formula they move relatively locked they just remain locked now let's finish the formula time is 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 excels golden rule and if you look up in the notes that's listed there now let's close parentheses control enter double click and send it down control down arrow and f2 now 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 excels golden rule notice hard-coded in formula input is in the cell with a label and we refer to them with cell references no problem look at this Mitsouko that's the incorrect higher 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 in hit enter everything updates that's excels golden rule not only that the number one source of errors throughout three decades of spreadsheet history is when people incorrectly hard-code 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 hard code them in or tax rates and they change and so we follow excels golden rule number can change they're put into a cell label it and refer to it with cell reference alright now I'm going to control Z Z control home alright so on this sheet we saw a lot of gate related tricks formatting functions formulas and even some cool keyboards alright let's go to the next sheet payroll quickly and round now most of us do payroll and 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 those group 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 grosspay times tax rate and yes as we copy the formula down we're going to need relative cell reference and this one will have to be luck but we're going to run into some trouble here when we're multiplying decimals especially when right here that 0.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 x right right arrow f4 that calculation should work control-enter I'm going to double click and send it down all double click and send it down in the latest version of Excel it used to stop right there but now because we're connected with column headers or field names at the top if thought this was the bottom but no problem control Z and now I'm going to copy it down now I want to come to the bottom and alt equals I see the dancing ants are surrounding the correct cell 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 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 drop-down 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 killed up 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 an error here because that's some function right there does not see number format so if I escape and controls the number formatting down here that some function does not see the number formatting all formulas look at the underlined number that means some 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 oh I can see right away that there's a bunch of round functions I can use my down arrow to get to the function I think it's the correct one I can read the screen tip round the 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 selection cursor you got to make sure you see your eye beam 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 three four to whichever position I wanted to the right but what if like in some income tax situations you need it to round to the dollar well if I start at the decimal and go one two three going backwards the other way I would go three two one zero minus 1 minus 2 minus 3 would be to the thousands positions like sometimes for financial statements so for a number of digits I'm typing it to close parentheses control 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 floor 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 the 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 and 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 alright 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've 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 here on mirta's ation table Oh people get nervous because they're oftentimes shoes with lots of calculations but not in Excel it's quite easy to build an amortization table in Excel alright here's our loan five hundred seventy five thousand it's an N which means the payment comes at the end of the period there's a balloon payment at the end a hundred thousand dollars the annual rate is four point seven five thirty 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 P t 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 four point seven five and we're going to divide it into twelve parts so divided and then up arrow up arrow and enter now we 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 230 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 formulas been in existence 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 someone 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 it's 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 nper that's number of periods up arrow comma present value that means what is the current worth of the loan now when you're using financial functions whether PMT or FV for future value or PV for present value or rate for the rate or nper to figure out number of periods you have to know chassé blow now from the borrower's 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 consumers 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 a hundred thousand dollars left on the loan now I can't just click on this hundred thousand because again we have to think point of view from the consumers 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 tap the type of - and then click on the hundred thousand 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 will tell you and is the default so we don't even have to put that in now you could double click the 0 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 - and when you see it no comma type in square brackets I want the default that is fine close parentheses control enter now why is that a - these functions know Castle 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 a 1 2 3 and so on I don't even want to calculate or I'm sorry I now 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 right click drag down one cell drag back and that opens up the secret menu and I point to Siri 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 a select columns oftentimes 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 is 1 and that is beautiful 360 it will stop at 360 I click OK control down arrow that is beautiful I'm going to control home to jump all the way up to cell a1 now do 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 7 31 2017 7th last 31 class 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 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 fill 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 new to go to the end of the month including look at this it knows leap year to 29 to 0 to 0 now as you notice so far I always have 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 ctrl backspace to jump back to the active cell here's the fastest way find asterisks on your number pad and do ctrl asterisks 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 elastics here what it got everything else ctrl Z the way that keyboard works is that the empty cells are all the way around so control Asterix 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 a color then I come over and click click to put them everywhere click ok 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 click went both directions but notice we want just vertical so we'll keep that in mind control 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 light be careful if you click diagonally to diagonally so I click a second time to get rid of them and then there we go click OK click off to the side maybe that's not what you want I'm just going to click on balance control elastics control one I think I want this medium line around the outside so I'm gonna say outline click OK now I click off to the side that's more what I want alright so now we need payment no 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 app 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 locket now I'm going to ctrl enter because I first off want to look up to the formula bar well look at that if I use a plus or a minus it'll matically 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 ctrl down arrow f2 I'm verifying it sure enough that looks good escape control home now the balance is what we need next that if 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 principle 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 f4 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 - 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 cell reference from the period before subtract left arrow whatever the reduction was for this month control 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 anger rabbit double click and send it down there's the active cell I'm going to ctrl down oh 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 - and notice there's the exact balloon payment now if we were to properly round this like we're probably supposed to because this is dealing with money this would be a few pennies off right you know that last payment is always a few pennies up but if you don't use the round function of course it has to come out exactly that hundred thousand 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 are oh you I see round a tab I very carefully come to the end comma we're dealing with money so it's two to the penny closed parenthesis and control enter that formula of course will 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 control enter double click and send it down we do not have to round that because 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 control 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 data validation list to get a drop-down list in a Cell now vlookup and the if function will see both of these functions on this sheet or 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 functions now here's our started template we have the boomerang name and we have 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 flat top boomerang you'd have no problem oh wait a second we better scroll down now you have no problem you'd actually look at the text flat time go oh I need to go look up the price or flat top you would know that the lookup table is down here you'd race through the first column fine flat top jump over to the second column you'd see that it's 12.36 cents and then you'd come up here and type 12 dollars 36 cents now look at what we did fundamentally yes we looked up the text flat top and found a match but fundamentally we went and got something and brought it back to the cell that's what vlookup does now let's see how this works on this type equals V L 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 low cup 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 wholesaler cost but notice we gave vlookup an entire table so we have to tell it which column number within this table 1 2 now this is a small table some tax tables or some employee information tables are huge so we might look up in the 3rd 4th 5th 6th or whichever column ours happens to be 2 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 2 now I type , and there's two types of look up now exact matches what we're doing that means we look up and the formatting is blocking it 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 1 and 0 so that's the way I tend to do it I'm doing exact maps so you can put false or type of 0 close parentheses control enter and a 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 miss typed it if I backspace and now hit enter now vlookup can do its job because flat top is matched with flat top but that brings up an issue I 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 going to 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 alright 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 the cell but we want to validate what goes in now this drop down here is amazing you can validate a certain range of whole numbers decimals dates times length but the one we want is less so I select list and there it is the source I simply highlight the valid items and notice look at that in cell drop-down and we want to ignore empty cells there so we keep those checked and click OK now I am not going to be allowed to put flat top with a space I simply select from the drop-down you can type it also if you know it like I could type bail-in 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 own error message I'll leave that for you to do now I'm going to delete this felon now we're going to copy this down and we're going to run into trouble because na it is looking up nothing and empty cell and it can't find it over here so we need to and 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 vlookup 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 vlookup or I want to put nothing in the cell anytime you have a situation where every single cell it's going to have one of two things that's the perfect job for the if function now if you have a 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 is 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 I F 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 I s B 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 return a true now I'm going to come after that close parenthesis 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 vlookup or when it comes out through the double quote double quote to show nothing now I come to the end close parentheses 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 5 here they bought 5 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 control enter and now I'm going to copied all the way down uh-oh 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 enter 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 0 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 C 14 is underlined 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 texting now it's going to evaluate the B 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 try to multiply the color red meaning re D times 3 well that doesn't make any sense that's what a value error 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 ctrl C escape I'm here f2 and right up to the equal sign I'm going to control the 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 control enter and copy it down that is pretty major we're learning how to build a template using the data validation and if is blank vlookup 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 take 15 and down here I want to alt enter oh it guessed wrong remember as long as the dancing ants are going you're in full edit mode control enter now to follow my convention I held control and clicked on both cells they go up and just add a slightly different green now again if this is the 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 are multiplying now if I got the 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 cell 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 tilde to check it out if there were any extraneous decimals control 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 will be the annual amount annual rate and number of years the problem is most Excel users on the planet Earth would have to create 11 different photos meaning we'd lock the rate and the 4000 but we let the years move relatively then we 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 locked the 4000 in all directions because every one of these cells needs the 4000 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 roll 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 mixed cell references now I'm in 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 control-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 a formula across the columns or down across the row 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 luck because when I copied across the column the a remained locked when I copied it down across the rows the table is locked now controls easy let's do that one more time but in this case let's hit the l4 key and now we're going to see that the f4 key is really the merry-go-round key if I keep hitting f4 f4 f4 it's a merry-go-round it toggles between relative absolute mixed cell reference with the row lock doughy and mixed cell reference with a column locked only so you can toggle between as much as you 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 be the control enter I'm just going to copy it down 1 and over down here I could see f2 the 10 is locked but when I come over here F to 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 column 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 1112 and so on control-enter copy it down and over now f2 there's a 10 enter f2 it moved to 11 f2 there's an a tab F - 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 to the side will 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'll 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 needed to be locked on nine so I'm going to hit the f4 Q once and twice but when I copy it to the side it needs to move from benign to c9 so that will work comma nper for us it's number of years so I click on a 10 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 10th when I copy it to the side I want a to remain on the a column so I hit f4 1 2 3 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 0 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 double click and send it down immediately go to the diagonally furthest cell away click in 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 mixed cell references and how it transformed my ability to create templates like this quickly there is the proper column header 6% here's 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 I use the keyboard ctrl shift tilde 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 calculation so ctrl Z I'm going to leave it there each one of those were just looking at now let's go look at our next example we're going to talk about the sum ifs and count in something 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 su if I look through this list I can see there are 1 2 3 4 records pursue so really I want to pick out these numbers and add them but when I copy the formula down I want the form of it to then add 4 Emira and chin and monetha 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 sum if without the S that was before Excel 2007 now you some ifs because some ifs can do everything that some it could do plus much more now some if just out 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 you saw miss instead of some 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 some rain change 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 up so I'm going to highlight sales rep and hit f4 comma now we put the criteria that summits can use to start picking out which records to use for us it's a relative service and that's the formula for adding with a single condition close parentheses control enter and copy it down now I clearly see monetha over here so just as we saw earlier in the video you always have to be careful we're typing these in right you gotta 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 ctrl 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 EPS if we want to count we use count and there's a bunch at counting functions count counts numbers count up 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 laughte arrow to get the criteria control enter and double click 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 like now those two formulas are adding and counting with a single condition here is what's called a cross tabulated table and for any intersecting cell we want to look through the product pick out try fly and then also look through sales rep and pick out a mirror 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 belen 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 if any time you're counting with one or more conditions we can try the count ifs pumps and 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 a count for Eagle Sue the record has to find Suh as the sales rep and eagle as the product an and logical tasks 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 su now think about this as I copy down I need the eleven 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 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 G 10 G 10 G 10 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 try flight so we're definitely not putting a dollar sign in front of the G only in front of the 10 so I hit the forty once and twice that formula will do it close parentheses control enter I'm going to copy 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 Oh Oh monie fuh I can't type Monica'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 we'll see how to create these reports with pivot tables and pivot tables tend to be a little bit easier to create the one advantage f2 that formulas have over pivot tables is if the source data changes there it is 200 if I change this just for kicks to 5,000 watch what happens here when I hit enter it instantly updates if it was in Qin but su now the count down here and the count right there updates that's the advantage of formulas and sometimes in accounting and business people want to build solutions that automatically update when the source data changes again later we'll learn pivot tables a little bit easier to create these nomina controls easy to bring back the original two bits of data alright so that's a little bit about count ifs and some ifs and another look at mixed cell references now we want to go look at a great accounting example we want to see how to build a checkbook register and we'll see an interesting formula here and we're going to see how to use mixed cell reference to help us reconcile so anytime we put an X meaning we found this in our bank statement we not only want the X there but we want the whole road to turn yellow now I'm going to control Z now the first thing is let's highlight and if I control Astrix that's not going to work here because I need my template to go down below all the way where there's nothing because I'm going to be the formula all the way down so I manually highlighted that control one and just like we saw before we're going to do border I'm going to select a medium thick line and say outline then I'm going to click thin line click on inside go back and get my double line and click vertical now when I click OK the outside has the dark border horizontal has a single line and vertical has the double line why is that hanging over well it looks like it's right aligned and the column is not wide enough so I'm going to click and drag and there we go now let's first think about the formula for calculating the balance so and this is just one type of checkbook or one type of register where we potentially have a column for subtracting and a column for adding well right here since this is a subtraction I need the one above minus the check but when I get down to this cell right here I need the one above plus the addition well our template is always set up where for any record it's either going to be one or the other so here's our formula equals relative cell reference one cell above - and I'm going to arrow over to get as a relative cell reference the amount of the subtraction + left arrow right here it's a zero so won't matter down here it'll pick up the 5/16 and it will subtract zero so that's a formula all the way down control-enter now if I copy this all the way down I'm pretty sure it's going to wreck our template so if I click off to the side you can see it got rid of that thick border smart tag totally awesome click the down arrow and say fill without formatting I totally remember when the smart tag came in Excel 2002 I had all these beautiful accounting templates with all these borders and I would always recommend this smart tag came in man that is a life saver now the problem is I don't want the balance down here well we can do our same trick we did earlier here I need to run the formula down here when nothing has been entered and I'm going to pick the date column show nothing now again we have a choice here we can test whether there is a number because remember dates or numbers or we can test if the cell is empty since we did is blank which checks for empty last time this time I'm going to check and see if it's the number f2 and right after the equal sign if' tab and in the logical test I'm going to say is number by the way there's a bunch of is functions so you can look through those we looked at is blank and now we're looking at is number I'm going to click on the date close parentheses comma now our logical test this time is asking is this a number well if it's a number what do we want there it is we want the formula so you see when you're building an if function to put one of two things in a cell we have choices between logical tests it's not that one is better than the other in this case is number or is blank would be equally reliable and efficient now I'm going to come to the end comma but in this case if it comes out false which means down here that's not a number I'm going to put double quote double quote that's a zero length text string which will show nothing close parentheses control enter double click and send it down immediately point to the smart tag and click on fill without formatting now just for kicks we could come over here and is text showed up we can test it looks like the cell is empty but is text remember zero length text rink control enter oops I hit enter but there we go we're proving than the fact that is text even though it's the way we show nothing now we could test this I come over here 9/5/2013 the formula shows up now our last task here is to build this column to help us reconcile now actually it's two steps we're going to do data validation drop down list I'm going to highlight this whole column go up to data over to data validation list by the way there's a keyboard for this I use this all the time and it's an Alt alt the Alt key it's an alt keyboard so you have to hit the Alt key whoa when I hit the Alt key all of these screen tips pop up reminding me how I get to each one of the ribbon tabs so the letter A would get us to data a and look at this V gets us to data validation V oh wait a second then I have to hit V again well that would work now remember this is I'm going to click escape now remember this is a ribbon tab that was invented in Excel 2007 if we were in an earlier version the keyboard was shorter it was all DL and so that's the one I used to this day when Excel 2007 came out a in essence compared all of the new alt keyboards to the earlier ones if any of the earlier ones were shorter I kept those now we go to settings and we're still going to use lists but watch this this is another great use I can just type values separated by commas and you could type a list now this is hard coding so you want to only hard code them if the values are not going to change but here we don't even have a lift we just have one value I just want to make it easy on myself and since the conditional formatting is going to depend on the fact that there's an X there I want to make sure that we validate it so I click OK now I can come and as I find these on the bank statement I'm going to select my X's and now we'll do the conditional formatting by the way I think I still have a template from the 90s that I still you using the same trick for reconciling why don't I show you this normally when you first learn conditional format and you go to home and you just use the built-in features oh I didn't highlight all the way down I highlight all the way down go to conditional formatting and highlight cell rules I could simply come over and say cell value is equal to and I could type in X now I'm just going to keep the default and click OK but that's not what I want I want the whole row and there is not a built in feature to do the whole row anytime you have a conditional formatting task and it's not built-in you can use a logical formula meaning true or false because what if conditional formatting do here it asks the question is there an X in each cell so for each cell I got true I'm going to give you the formatting false you don't get the formatting now I'm going to ctrl Z on that because I don't want to keep that now think about this this whole row needs to look at for us 11 but when we go down to the next row this whole row needs to be looking at e 12 if I were to come off to the side and simulate this equals and I'm going to click on 11 I can see my seller effin so I hit f4 one two three times I'm locking to eat because when I copy it to the side I do not want it to move off the letter E but when it copies down the 11 will move to 12 now I need to ask the question of that cell are you equal to an X so the way we do that is we use an equal sign and we need to ask it whether it's equal to some text and you cannot just type text into a formula because it'll think it's either a built in function or a defined name or something like that so instead we have to use double quotes anytime we put text in joyfulness the double quotes are you equal to X but here we'll get Truths all the way across for the entire row let's I copy this down this is what conditional formatting will see so I'll get all these true and it will know for this entire row to format it yellow down here this does not contain an X so it does not get the formatting now I'm going to leave this off to the side it's not going to have anything to do with our formula let's highlight all the way the entire table and very importantly the active cell is in the upper left hand corner now I go to home conditional formatting and down to new rule now I'm going to use a formula to determine which cells to format then I'm going to click in the format values where this formula is true and I'm very carefully going to click on notice active cell right there the formula we create here in memory will be copied to every single cell and then down but remember for this whole row we need to look at 11 now in the conditional formatting dialog box it comes up by default lock so I want to hit the f4 key once and twice I want to lock only the e but not the 11 then if they are u equal to in double quotes and X now I come over here and format and you can use any of these tabs to format I'm going to click yellow click OK click OK that is totally amazing now when I use data validation drop-down conditional formatting formats the entire row yellow meaning we found this item on the bank statement alright so that is a cool checkbook example where we did and if formula data validation with a typed in value and conditional formatting with mixed cell references now I'm going to use my arrow to scroll over and show more sheep and we want to go to the sheet flash fill now on the flash fill sheet we got to talk about one of the more amazing features in modern excel this feature right here flash fill came in excel 2013 and what it does is we oftentimes don't get our data in the form we want now I just want first name or just last name and change the case now we could use full we could do it manually but flash Phyllis absolutely amazed now flash Ville will try to recognize a pattern that we give it in a column next to the data set now for first name the pattern is very simple everything before this space so all I have to do is type mo enter and as soon as I type a g-- flash fill appears this ghost list offers us the option to hit enter and once we hit enter it flash fills extracting just the first name now last name will work with two examples too because it's going to be everything after the last space so here I'm going to change the case gim enter and as soon as I type capital T there it is exactly all of the last names and enter now Microsoft describes flash fill as program by example and the reason they describe it that way is because sometimes like when we're trying to extract first and middle name we're going to have to give it a bunch of examples and then technically what happens behind the scenes is it writes a little code to extract our data again these first two examples were easy but if we want first in middle notice there's no middle here there's a middle name there and down here there's an initial with a period now let's just try and see what happens if I say mo enter and then GG it tried to extract first name but now I'm going to keep typing elbow I'm thinking that I'm giving it enough examples because there's one with no middle name and one with a middle name but when I hit enter and then capital a it almost gives me what I want if I hit enter that would be fine as long as I didn't mind that it emitted the period because really we needed to give it three example so it could build a program behind the scenes one with no middle name one with a middle name without a period and one with a period so if I delete all this and I know my data well enough I can go to any three cells where I need to supply the three examples Cody space a period now when I hit enter I can use the keyboard ctrl E and it gets it exactly right so in order to use flash bill you have to know your data and you have to give flash fill enough examples now can we reverse putting last comma space first well this one should be easy because it's just going to get everything before the first space and after the last space so D I am and I'm going to insert a character what you're allowed to do so comma space mo and now when I enter and type capital T it got it exactly right again this was an easy example for flash fill to decipher now here's another one that's going to be easy to decipher if I have phone numbers that I want the right parenthesis in spaces I can simply ctrl-c ctrl-v and then put the correct parenthesis in space and an arrow over and a - now when I hit enter as soon as I type open parentheses this is another easy one for flash fill to figure out enter still another example here we have some asset IDs and in order for us to import these into the database they all have to have lead apostrophes so watch this I love this we can simply type lead ' nine zero five one three now when I hit enter and type of lead ' it knows what to do and enter you can look up to the formula bar to verify another way that I use flash fill a lot I copy and paste stuff from the internet I just want the number so 1 5 7 4 enter as soon as I type a 1 there it is last bill I got all the numbers so flash fill really is an amazing new feature that allows us to clean our data or extract data now it is not dynamic like a formula would be but when you have a one-time data cleaning or data extracting task it's an amazing feature Wow that was a lot of amazing topics for our first video at this conference we talked about flash fill back on the sheet check register we saw how to create an if formula use data validation and conditional formatting to build a checkbook register over on summits we saw how to add and count is in some extent counteth when we have conditions or criteria on the sheet mixed cell references we talked about the amazing formula creation time-saver mixed cell references on the sheet invoice we talked about using the vlookup function and the if function along with data validation to build an invoice back over on amortization table we saw how to make various calculations to build an amortization template back on payroll quickly round we talked about the importance of round and getting your calculations correct over on dates number formatting and formula sheet we talked about formulas functions and number formatting for dates and we started it all off by talking about the importance of using keyboards now that's the end of video number 137 topics be sure to tune in for our next video Excel for accountants where we talk about power query and pivot tables to import and clean data and build pivot table reports alright if you like this video be sure and click that thumbs up comment and subscribe alright we'll see you next video

Channel: ExcelIsFun

Views: 320,500

Rating: 4.9326391 out of 5

Keywords: Excel, Microsoft Excel, Excel 2016, Highline College, Mike Girvin, excelisfun, Excel Magic Trick, Excel For Accountants, Excel for Accounting, Excel Formulas, Excel Basics for Accountants, Excel Functions, Formatting Templates, Build Accounting Templates, Learn Excel keyboards, Amortization Table in Excel, Checkbook Register in Excel, Future Value schedule in Excel, Absolute References, Mixed Cell References, Relative Cell References, WAATP Excel, WAATP Excel seminar

Id: RsDFonVtKGM

Length: 97min 2sec (5822 seconds)

Published: Mon Jul 10 2017