Excel For Accountants: VLOOKUP & PivotTables Complete Lessons: Basic to Advanced: CWU Seminar

welcome to Excel for accountants vlookup and pivot tables to make your job easy hey this is an excel how-to video presented at Central Washington University in Lynwood we're gonna be using version Excel 2013 and here are our 15 amazing examples to help you get up to speed with vlookup and pivot tables now if you want to download this file so you can follow along here's the name of the file and there's a start file and a finish file you want to click on the link below the video and go all the way down to the bottom of the page to the other section and look for the CW Linwood accounting file now this is a seminar for accountants and we have the topics vlookup and pivot tables now why are we talking about these two topics for accountants because when you're out working as an accountant you have to know these two topics and actually many other topics in Excel but vlookup and pivot tables are amongst the most important now here are our 15 topics I'm going to start out with vlookup and actually will by the end of the videos be using vlookup and pivot tables together to do some important accounting reports now we want to start with vlookup and we're actually going to start on the sheet lookup tables because we got to talk about why vlookup is so common and why lookup in general is such a common task now here's our first example of a lookup table if I were to ask you the question what is the price for quad you could probably tell me that the price is 43 95 but notice that's a common lookup that we do in business in accounting we know what the product is we find a match in the first column and then we jump over to the third column and get our price here's another example of a lookup situation if I ask you the question what is your earn Commission if your sales were six thousand bucks well probably you could look through the first column and realize that for this row right here the category five thousand all the way up to twelve thousand five hundred but not including that amount would give you a two hundred dollar Commission yet another common lookup situation here's an employee table employee name and lots of information about that employee if I asked you the question chuck's hell what is his hire date you would probably look through the first column find a match and jump over all the way to the 8th column and there it is the hire date is 5:17 2012 yet another lookup situation if I ask the question if your income is 3,000 bucks what's the task or you're probably going to tell me this would be the category so the tax would be 60 yet another example here we have sales rep in region so if I asked you which region does Pitman ODIs represent you would race down find a match in the first column in this case we jump over to the second column and West would be the region that ottis pittman represents now these are five different look-up tables in five different lookup situations we will get to see all of these in action now we're going to go over to v1 and look at a very simple example to introduce ourselves to the V lookup function now at the top of each one of these sheets over here are some notes but let's look at our table we have products supplier and price now right here we have V rang the product and I need a formula to automatically retrieve the price and if I change the product like what would happen in an invoice I need the price to instantly change now our formula is going to be in this cell f18 and i want to run through how we do this manually first because if you can do it manually then V look up is a piece of cake now what do we do if our goal is to get the price in this sell well first we have to look at the name of the product V rank we remember what it is we know where the table is right so we go to the first column and we see that it's in the second row we know that we're looking at price so we go over to the second third column 1895 once we see that we actually get it retrieve it and bring it back over here now we never want to have to do it manually so instead we're gonna use vlookup delete all right now this is a formula so any formula in Excel starts with an equal sign we start typing the letters of the function V L and look at that the function name is in blue so I can simply hit the tab key to enter my function now notice this is called a screen tip here it lists the function name and you can count one two three four different arguments we have to enter into vlookup to get it to work correctly now luckily the bold means that's the particular argument you should be entering then we type a comma and we go on to the next now for us lookup value just like we would look at this manually remember it and go over and try to get a match vlookup function with the lookup value needs to know which product to go and try and find now notice I clicked on that cell and I put a 18 that way when we change the name of the product later vlookup will know to go look up that particular product now lookup value is bold we've finished entering it so now we type a comma now the next argument is table array now just like we would know where the table is vlookup has to know where the table is and I always remember by the name of the argument it's this table so I know this is where I enter the table now for vlookup you do not enter what are called field names or column headers at the top just the first column any potential other columns you want to retrieve items from we have our table I'm going to type a comma and notice each time I type a comma the bold argument name changes now this one says column index number now notice if we were doing it manually we would read the price column and no to get it from here but vlookup needs a way to know which one of the columns 1 2 or 3 has the thing we want to go and get and bring back to the cell notice vlookup for this table right here needs to know which column to go and get something from we count on our fingers 1 2 3 prices in the third column so I type a 3 now our fourth argument I type a comma there are two types of vlookup exact matches what we're doing we're looking up an exact name or product ID or something like that when we looked at our tables and we had a range of values like for commissions and tax that's when we do approximate now notice it says true for approximate and false for exact those are sort of arbitrary they just decided if I put false in this argument vlookup will know to do exact match if I put true it means vlookup will know to do approximate match now I don't follow the convention of true and false I follow the convention of 1 and 0 in excel true is represented by 1 and false is represented by 0 so my convention is to put a 0 in I'm going to show you both ways here if you simply down arrow to false and notice it's blue just like our function name that means the tab key will work so I'm gonna hit tab notice it puts false there it doesn't mean that the form is false it's just instructing vlookup which type of lookup to do now my convention is always to put a 0 so that's what I'm going to do 0 and false are equivalent notice up in your notes up here it remind you that either one is fine and that's it close parentheses and enter 1895 now here's the real magic of vlookup if I type quad and hit Enter look at that it instantly went over matched the quad in the first column jumped over to the third column got forty three ninety five and brought it back to the cell now I want to make a mistake here and what's that n/a n/a means not available well I want to check this formula so I'm gonna hit the f2 key and I'm looking right at that cell there and I can manually do this I see quad I see quad over here so why isn't it finding it any time you see an n/a that's vlookup being polite it means it did not find quad over there now how do we verify this I'm gonna click in the cell and I'm going to hit the f2 key to put it in edit mode look at that there's an extra space at the end that means we have five characters qu a D and a space that is not the same as it I'm gonna click escape click over here and f2 that's not the same as over here that's four characters qu ad so space is matter so now we want to backspace and when I hit enter it works fine now we're going to learn how to do something called data validation drop-down list and it will prevent us from making errors like that I want to drop down so I can select the exact product from the first column of this table now anytime you have a lookup table that means you also have a list of the acceptable items that vlookup can understand so watch this I'm gonna click in the cell now I have my ribbon tabs collapsed you simply click and it opens up that particular ribbon tab you can also right-click and uncheck collapse ribbon if that is easier now notice I have this cell selected I want to verify that only items from this first column could be put in and I want to drop down so I'm going to go up to data tools and there it is data validation I'm gonna click on data validation now here it is the data validation dialog box it first asks us what would you like to allow in this cell right here notice the default is of course any value because all cells in Excel allow anything now you could build various rules based on whole number decimal D time all sorts of things but we want lists now once we select list then the source text box down here becomes exposed there's this collapse icon here that means once my curse is there I can simply click in the cells and I'm dragging to highlight the range there it is that means from now on in that cell the only valid data is from that column there and there's our in-cell drop-down when I click OK you've got to be kidding me look at this now I can select V rang I can select belen and if I selected quad put it in edit mode and hit a space and hit enter it's gonna give me an error message now you can add your own error message if I go back up to data validation you could add an input message and an error message if you would like alright so that's a first example of the power of the vlookup function and of data validation drop-down list now we want to go to v2 or we're going to use the same vlookup and data validation list to build an invoice here is our invoice now notice we want to enter a particular product and I have already added the data validation list and wow this has a bunch of products we're going to enter quantity we're gonna have our vlookup to look up the price we're gonna build a formula to calculate the line item total and then we'll add shipping and get our invoice total now down here is our expanded product table first column has our product and then second column has our price now I've already done the data validation so each one of these cells will be easy to change for our invoice now let's go ahead and build our V look up VL name a function is in blue I hit the tab key lookup value that's gonna be our product name now we're gonna copy this formula down so we need to learn the difference between a relative cell reference and an absolute cell reference now this a 19 as we copy the formula down always needs to look at the product name two cells to the left so if we leave it like this when we copy it that blue cell will move down a majestic view for this second row down to vellum for the third and so on now it's called a relative cell reference because from the point of view of the formula relative to the formula where am I always going to look one two cells to the left now that'll work that's our lookup ID comma the table I'm gonna come down here product in the first column price in the second column I'm going to highlight all the way down just to the bottom and now if I copy this formula down that table will move as it goes down each cell the table will move down and that would not work so we need to lock this and the way you lock it or make it absolute is with your cursor touching the cell reference hit the f4 key make sure that there's dollar signs in front of the a 29 and B 43 that way it's locked as I copy it down it'll always know to look to that range there for our product if now those dollar signs don't mean money or anything they picked the symbol to represent locked cell reference arbitrary in the beginning of spreadsheet history but once those dollar signs are there that range will be locked now we come our column index we always look it's this column that means which one of the columns in the table one or two has the thing I want to go and get and bring back to the cell it's two of course so I simply type a2 comma we are doing exact match you can either put false or type a 0 now vlookup knows to do exact match look up close parentheses and watch this control-enter because I want to actually put the thing in the cell and keep the cell selected and now notice in the lower right hand corner is a little green box that's called a fill handle if I take my selection cursor and move it close not the move cursor that's the move cursor that's not what I want I want to hover right over the fill handle and once you see your crosshair or angry rabbit you can click and drag down look at that now we want to verify that the cell references worked when we copied our formula so I'm going to click in the last cell and hit f2 notice that blue one is relative to the formula one two cells to my left and notice our lookup table is totally locked on the proper range all right now I'm going to hit enter here and if this is an invoice that we can change like delete these and change these then I actually need to copy this down two more rows but watch what happens when I copy it down of course when I come to this last cell and hit f2 vlookup is trying to look up nothing and it can't find nothing in that first column so it's polite it says hey not available so I need to turn these errors off when they occur now there's a few ways to do this in Excel but the easiest way for a small invoice like this is to come to the top cell and I'm going to hit f2 to put it in edit mode now I'm going to add the if error function in front of vlookup so I'm going to put my cursor right after the equal sign before the V and type I F now notice there's a couple if so I'm going to use my down arrow to get to if error once I see it's blue I hit the tab key now the way if error works is if you click on that value that vlookup will be run in every single cell if it's not an error it'll just let it run meaning it'll get the 27 78 right here but as soon as the if error sees err here then for the second argument I'm going to type a comma it's asking what value would you like me to put in the cell if it's an error now we need to show nothing and there's a special set of characters in Excel for showing nothing you actually have to type double quote double quote now this is actually a zero length text string it's actually considered text with no link now you don't really need to worry about that technical side because what it will do is it will show nothing close parentheses I'm going to ctrl enter because I want to put the formula in the cell and keep the cell selected I immediately want to point to the fill handle and when I see my angry rabbit I'm gonna click and drag and look at that now watch this this is the magic of Excel I'm gonna delete all of these now I'm going to come up here and this is a new invoice I'm going to type Bella and I'm gonna type 12 that is pretty amazing now I'm going to ctrl Z Z Z ctrl Z is undo I did it three times to get back to my original products and quantities all right so this columns looking pretty good let's try line item total well we need to multiply price times quantity so to do any formula you type in equal sign now we're not going to use a function here I'm simply going to click on a relative cell reference one cell to the left and I'm gonna use the multiplication symbol either shift eight or the asterisks on the number pad that's that means multiplication then I'm gonna click one two cells to my left those are both relative cell references as I copy down that's exactly what we want for each line always the two cells to my left ctrl enter and I'm going to point to my fill handle and copy it down now notice here we're getting a value error and it's a different error and you don't really need to know this but it's actually taking that empty cell which turns out to be a zero and remember there's double quote double quote in the cell which is actually considered x-value error is when you multiply a number of times text it doesn't like it we need to do the same thing as this column we need to turn the errors off so we can come up to the top and there are a few ways we can do this but I'm gonna hit the f2 key and I'm going to use the if error function again I F down arrow tab there's the formula to run in every single cell click at the end comma if error we're gonna put double quote double quote that's the syntax for show nothing close parentheses control enter and I'm going to point to my fill handle and click and drag that is pretty amazing let's test it I'm going to delete this type a quad and to enter and look at that it is working perfectly ctrl Z Z Z now our next step is we need to add for our subtotal all of these cells right here now normally what you do when you learn Excel for the first time is you go to the home ribbon and you come over to the autosum button and click this this will add the sum function which will add but I want you to notice something in that screen tip it shows us the actual keyboard for the sum function now of all 450 functions in excel only one of them has a keyboard shortcut and it's the autosum why because it's the most common function so let's try it alt equals and look at that now when you use alt equals be sure to look at the dancing ants and verify that it's got the right range once it has the correct range we can simply hit enter that is pretty amazing now the shipping is going to be for this particular invoice 15 bucks enter now the invoice total is the sum of these two and watch this I'm going to alt equals and look at that it did not guess the correct range now the dancing ants indicate to us that this is fully editable I can actually highlight any range and it's not until I get it just right then I can hit enter we have our invoice total and I hit enter and so now we have a pretty dynamic amazing invoice if I come over here and to lead all this let's test it come over here quad we bought 43 of them tab and look at that now I'm going to control Z Z Z all right here we we took advantage of data validation drop-down list F - we used vlookup if error and double quote double quote the syntax for show nothing and F - over here we created a formula to multiply price times quantity and used if error and then we have to use the sum function and F to use the sum function here now in our first two examples with V lookup we did exact match now we want to go over to the sheet v35 and talk about approximate match lookup now if your sales are $12,000 for the period and you would like a formula to calculate your Commission's we're going to use vlookup but notice if I did exact match and tried to find an exact match in the first column we couldn't find it there is no 12000 this is a situation where we do approximate match now that $12,000 fits in to this category right here because approximate match will always search through the first column and notice the first column is sorted from smallest to biggest and try to find the category where it fits for this row every number that we possibly want to look up that is exactly $5,000 all the way up to but not including 12,500 would get a 200 dollar Commission now this is how you set up the lookup table when you're doing an approximate match notice down here same table when we want to get Commission rate sorted smallest to biggest down here when we do our tax example for column four income is sorted smallest to biggest now the first time we do approximate match lookup I want to show you this table over here this explicitly lists each category that describes how approximate match vlookup works so yes we're never gonna have this first column here we're just gonna have our sorted first column but this is exactly the description of all the numbers that would get a commission 200 that means your sales are greater than or equal to 5,000 and less than 12,500 if your sales were exactly 12,500 all the way up to but not including 15,000 then your commission would be $625 now since we never have these nice categories here to remember this is how I remember how approximate match vlookup works vlookup will look up this 12,000 it will race through the first column until it bumps into the first bigger number then it jumps back a row and that's how it knows to get the Commission 200 bucks now technically that is not how vlookup works it actually does something called a binary search on the first column which is how most databases work not only that but a binary search is really fast and calculating as compared to our exact match which we did in our first two examples all right but I like that metaphor looks up the first value races through finds the first bigger one and jumps back a row all right let's see how to do this equals VL tab our lookup value is going to be your sales comma the table we highlight the table notice we're not copying this formula anywhere so we don't need to worry about relative and absolute cell references I type a comma column index that means which one of these columns has the thing we want to go and get and bring back to the cell Commission is in the sec can come so we type a - now comma our fourth argument true or one for a proximate match that's what we're gonna use we're not using an exact match here but guess what approximate match lookup is the default for vlookup what default means is that if you don't put anything into this argument here meaning you don't even type the comma and you never get to this argument vlookup will automatically do approximate match lookup so to reiterate you can either put a true in this argument range lookup or you can put a 1 or you cannot put that argument in at all because this is a much shorter and easier formula to create when I do approximate match I don't even put that argument in all I have to do is close parentheses and enter and look at that it got it exactly right I change this to 13,000 and hit enter instantly vlookup did its job it took the 13,000 raced through the first column bumped into the first number bigger jumped up to the next row went over to the second column and boom 625 bucks was brought back to the south now I want to try a few other numbers here what if we ended up with the number less than the first number listed in the column now I assumed that we couldn't have sales less than zero so that's why I put a zero but what if we had - one dollar boom vlookup will always give you an na when you're doing an approximate match look up when the lookup value is smaller than the first category so when you're designing this first column be sure and put a number that's going to be smaller than any of the possible lookup numbers ctrl Z to undo that what about if you had an exceptional day and you went well above 20,000 you have $25,000 in sales well vlookup is to take that number race through try and find the first bigger number but if it can't find a bigger number it will just return the last Commission rate now over here in this table you can explicitly see our categories each one has a lower and upper limit except for of course the last category your sales have to be exactly equal to or greater than 20,000 anytime that happens you earn one thousand seven hundred now let's try one penny below this category so instead of 12,500 your sales were twelve thousand four hundred ninety nine dollars and ninety nine cents now watch what happens when I hit enter it looks like the number changed but we have to talk about one of the most important topics in Excel now this video is about vlookup and pivot tables but if you don't know about number formatting in particular if you don't know that number formatting is a facade you can't do anything in Excel now notice 12500 on the surface of the spreadsheet that's how it's displayed but we can look up into the form of the bar and see oh there's a different number underneath that number formatting that's actually sitting in the cell now how did that happen well we selected this cell home ribbon number group and someone applied currency but in particular they used that button they decrease the decimals so notice what happens if you don't know about number formatting you're saying well wait a second vlookup is looking at 12,500 it should race through find the first one bigger and jump back the Commission should actually be 625 bucks but why in the world did we only get 200 bucks the reason why is because formulas never see number formatting that number formatting is a decreased decimal right there and the number is just display this way the actual vlookup formula always sees the number that sits underneath in the cell now we can easily fix this by using the increased decimal button and there it is now what we see on the surface of the spreadsheet is the same as the actual number that sits in the cell that's a very important lesson throughout Excel but in particular for us when we're do an approximate match vlookup we need to be aware that sometimes if it looks like there's an error it might just be number formatting now of course let's say our sales were in fact 12,500 and enter now that number race through found the first bigger one jump back on that's 625 bucks alright now let's look at example number four we have our same first column for earning Commission's but now we get a rate that we need to return to the cell and then to calculate commissions we multiply rate times sales all right we're gonna do the same thing equals V L tap lookup value 12,500 comma table array there's our table comma column number Commission rate is in the second column so I type a2 we're doing an approximate match so we don't even put that argument in close parenthesis and enter now what did vlookup do it took the 12,500 it raced through found the first bigger one jump back and return 5.75% to the cell no it didn't don't freak out when this happens as soon as it looks like there's an error that formula does not make errors it's us it's the driver of the spreadsheet let's click on the cell you can see up in the formula bar there it is so someone decrease the decimals so I'm going to increase the decimals that is the correct Commission rate if in fact your sales really were one penny below the next lower limit all right we returned our Commission rate now let's calculate Commission amount equals Commission rate times your say now when I hit enter 593 dollars and 75 cents hey I want to check this out because I know anytime you multiply decimals like this a lot of times what is returned is a bunch of messy decimals and those we have number formatting apply now I could just increase the decimals but I want to show you an important trick if number formatting is interfering with you seen what is really in the cell because notice we don't even have the opportunity to look up to the formula bar here because that's a formula so what do you do if you want to see what's in the cell you come up to number group in the home ribbon tab click the drop down the very first number formatting is called general it is what we use when we want to erase any one of the applied number formatting to see what's actually in the cell so I'm gonna apply general and sure enough you could see there's a big messy decimal there now actually in our next example we'll see how to officially round which is an important skill to have in accounting but for right now just take note general number formatting gets you a view of the actual number that sits in the cell alright let's go down to example number five income tax in the second column I want vlookup to look up whatever income we put here and return the tax equals VL tab lookup value comma table second column has our tax comma type the to we're doing an approximate so we just leave that out close parentheses and enter sure enough the lookup took that raced through found the first one bigger jump back saw that a hundred and twenty dollars is in the second column and returned it to the cell alright so on this sheet we saw three examples of approximate match lookup now we want to go over to the sheet v-6 now on v-6 example where we want to look at a proc match vlookup to add a helper column to our payroll data set now here are the sales here's the commission amount and look at this I have a huge table with employees here are their sales we need to look up their Commission's there's their fixed weekly salary and then we need to add those to get total gross pay now when you get a larger data set like this instead of scrolling all the way down with your mouse or the scroll bar there's a great keyboard that jumps to the bottom of the data set so I'm going to click in the first cell on this data set we're going to use control down arrow to jump down notice I went all the way down to row 114 now we can use the same keyboard to jump back up control up arrow now I'm going to do control down arrow and you can see it went exactly down to the bottom and it knew that this was the bottom because there's an empty cell now what would happen if I did the same keyboard now control down arrow it goes right down to the bottom of the spreadsheet row a million forty-eight thousand five hundred and seventy six now there's another useful keyboard that'll get us back to cell a1 control home though so it went all the way up to a-1 alright so now we want to look up our Commission and we want to see how to copy the formula down quickly I'm in cell F 15 equals VL tab and now instead of using my mouse to click on this sales amount I can actually use my arrow key so I'm going to use left arrow and notice it immediately went to the left similarly to earlier with our dancing ants if you arrow key in the wrong direction as long as those dancing ants are still going you can simply arrow until you get to the right cell now why would you want to use the arrow key instead of the mouse well when the cell reference is close it's almost always faster to use your arrow keys now I need to get to table so comma I'm going to I like that commissions table , the second column has our Commission so I type it - we're doing approximate match so I don't even put that range lookup argument in closed parenthesis now I'm going to use control enter which puts the thing in the cell and keeps the cell selected I want to point to the fill handle in the lower right hand corner and with a CR angry rabbit instead of clicking and dragging down like we did earlier in this video I want you to simply double click to send that formula down now luckily the formula is being polite it says n/a so I'm gonna go to where this cell is and hit the f2 key we're going to verify that all of the cell references are correct and we can already see the blue one is oh look at that we forgot to lock our range if you were to come down a little bit lower in hit f2 you could see we didn't lock it so it acted like a relative cell reference moving as we copy the formula down so now I'm gonna come to the very top cell hit f2 now we need to lock this now we want to be careful here I don't want to just click inside there I want to click on the table array argument which highlights everything inside that argument once the whole range is highlighted now we can hit the f4 key you want to make sure that the dollar signs are present in the a 15 and B 19 now this formula will work control-enter to put the formula in the cell and keep it selected and watch this this double click trick will work all the way down now I'm going to with that top cell selected ctrl down arrow go to the last cell hit the f2 key to verify and sure enough it's looking at Vincent summers sales and it's got the correct locked lookup table control up arrow now I'm a little nervous here about all of these no decimals showing so watch this I'm gonna highlight this entire range and come up and increase the decimals I see that those are all zeros so that's fine I'm going to do the same thing here now I do not want to highlight all the way down manually because it will take too long so I'm gonna highlight the first two cells instead of just using the ctrl down arrow if I hold the shift key so I'm holding ctrl shift and then down arrow it'll not only jump down to the bottom but it highlights all the way to the bottom now I'm going to increase the decimals I click twice that pound sign means this column is not wide enough so I come right in between E and F and I'm going to click and drag click and drag until it's wide enough I already see that there was definitely one at least one amount where the decimals were being hidden alright so we used vlookup to add this helper column now we can add total gross pay I'm going to use the keyboard alt equals now remember what we said about this awesome keyboard even if you click the sum button the dancing ants are trying to be polite and guess which range you want it including the sales is not correct so we simply redirect it and highlight simply commissions and fixed weekly salary we have the right range control enter I'm going to increase the decimals and double click and send it down control down arrow to go down to the bottom f2 to verify sure enough that is looking good control up arrow so in this example we saw how we can use vlookup to add a helper column to make a lot of calculations quickly all right now let's go to our next example we're gonna go over to the seat v7 on sheet 7 we have our employee lookup table we have all sorts of data we might want to retrieve for a particular employee now this is a very common task we have a drop-down here we want to be able to select any particular employee and retrieve in our case phone email salary and hire date this is an example of where we're actually looking something up and we need return in essence a record or multiple items now this is no problem we're gonna do exact match we could say equals VL tab I'm going to use my left arrow key for that lookup value now this formula we're gonna copy to the side I now I always need it locked on the employee so I'm gonna hit the f4 key now we type a comma the table we put the entire employee table first column has potential employee names all these other columns there's potential things we want to look up we definitely need to hit the f4 key to lock it comma and now wait a second for column index 1 2 3 4 5 6 so for phone we need to put a 7 comma and we definitely need exact match either put false or a 0 now close parentheses control enter and if I copy this to the side we can now put each cell in edit mode and change in this case email is not in 7 it's in 6 so I have to edit this and change it to a 6 tab now we got the right email f2 salary is in 8 9 so we have to change 7 to 9 tab and then hired 8 f2 is not a nine but in 8 enter now I want you to notice something both of these do not have the proper number formatting I'm going to go up to the drop down and select currency you can select the County and if you want now this is a topic for a different video but dates are actually not stored as date they're stored as serial numbers that's the number of days since December 31st 1899 where January 1st 1900 is 1 January 2nd is 2 and so on but that's another video I'm simply gonna click in that cell vlookup got the underlying number here but no problem we can apply date and we can see 5:17 12 is exactly the correct date for how now what did we do here we changed a nine and so on that's a very manual process and for many columns like this that's kind of a hassle not only that but sometimes you have many records so we don't want to do this manually well we're gonna learn about our second lookup function it's actually called match and you can see match up here in the notes it does something very simple looks up an item and tells you the relative position of the item in the list now if I asked you where is email as a relative position in this list right here you would simply count on your fingers one two three four five six it's the sixth position in this list that's all that match does so I'm gonna above it just show you in a separate cell how match works there's match tab notice it says lookup value just like it did with vlookup I'm going to choose this relative cell reference which is one two below as I copy it'll move from phone to email salary and so on I type a comma lookup array for match you can only have a one-way array it only looks through a list of single items so that it can properly count one two three four five six seven in the case of phone now we need to lock it so I hit the f4 key now comma match type there are actually three different types the first one is approximate match the second one is exact match so for exact match there's a 0 we have to put a 0 now we close parentheses control enter it looked up seven well sure enough it looked up phone and counted on its finger and returned a seven now if I copy to the side you got to be kidding me yes emails in the sixth position salaries in the ninth and higher date is in the eighth so that's why we have the match function so we can find out Eryn item is in the list now what we want to do in situations like this is f2 we actually want to instead of typing a number into column index we want to do what's called nesting a function we want to put match right into column index now here's where our screen tips are gonna help a lot notice right now I'm in the column index but I'm gonna type an M Wow it knows that this is a formula so it offers up your set of potential functions I'm going to hit tab but watch what happens right now I'm in the screen tip for vlookup as soon as I hit tab now I'm in the screen tip for match now I read it very carefully watching the bold and typing commas lookup value one cell above match is always going to see the actual field name or column header name one cell above comma look up array that's always a single list of items in our case it happens to be the column headers at the top I'm gonna hit the f4 key there it is it says lookup array I type a comma I'm still in met I see exact matches 0 so I type a 0 now we're still looking at the screen tip for match now this is the last argument right match type as soon as I type close parentheses watch what happens to the screen tip close parentheses it jumps back to vlookup so even no nesting functions and I can click on this column index I nested the match function inside the column index number argument of vlookup even though I'm nesting a function the fact that these screen tips change helps us out a lot alright so max what is it going to do it's going to tell column index which column and the table to go look up so I'm going to ctrl enter and copy it to the site now watch this when I copy this to the side it's gonna wipe away that number formatting anytime you copy something it copies the content in our case of formula and the formatting but watch this that is called a smart tag and it is awesome hover your cursor right above it and when you see the drop-down Wow check that out I can say fill without formatting oh just like that it copied just the formula and kept the original number formatting and now we can simply look up the information for any particular employee there's Byron's phone email salary and hire date all right so the concept here is we're looking up a record for a particular employee and we saw the new lookup function match which tells you the relative position of an item in the list we saw how to nest it inside of column index and use vlookup and match together to retrieve a record all right now we want to go look at our final example before we move over to pivot tables I want to go over to v8 now this is our culminating example for vlookup we have a data set and I want to click on the top and ctrl down arrow we're gonna call this a big data set this is 50,000 records or about 50,000 records now really it's not a big data set there's many many more rows that we could be using but but as a first example of larger data set 50,000 records is quite a lot I'm going to control home to jump up to the top our goal is to extend this data set we were given date units sales rep and product notice we actually weren't given net revenue and we weren't given the region so if our goal from this transactional data set is to calculate a regional net revenue report which we'll do in a few examples with the pivot table we don't have enough data to calculate that no problem we know how to add a column for price will simply look up the product there's the price lookup table then because there's discounts based on units we will do vlookup and lookup the units the hair is our discount table remember you in vlookup we'll be able to calculate net revenue units times price times one minus the discount and finally notice we have a lookup table with sales rep that tells us each region that each sales rep sells in so we'll simply lookup sales rep return the region then we have what we want all of our regions all over our net revenue and we can make a report based on those two columns this is very important vlookup is often used to extend the data set based on lookup tables all right so the price equals VL tab I'm going to arrow over to get the product name comma table I'm gonna scroll down highlight the table now this definitely needs to be locked so watch this I'm gonna hit the f4 key not only does it put the dollar signs in but it jumps the screen back in view comma the price is in the second column comma this is exact match so I'm putting a zero close parentheses control enter double click and send it down I'm gonna go all the way down to the bottom control down arrow f2 I'm verifying that the formula works I'm going to use the Escape key to revert back to whatever the formula was before I put it in edit mode control up arrow all right now discount equals VL tab I'm going to arrow over to get the units come on the table I'm scrolling down with the wheel on my mouse I'm gonna highlight this table sorted first column second column has the discount we want to retrieve ask for to lock it and jump the screen back in view comma to we're doing approximate match lookup so we leave that last argument off close parentheses control enter now we want to point to the fill handle and we see our angry rabbit double click and send it down control down arrow I'm verifying that the last cell f2 has the correct cell references and yes it does escape control off arrow now net revenue equal sign I'm an arrow over to get units x arrow over to get price and x I cannot just air over and get discount because that would give me the amount of the discount I actually need to very carefully in parentheses take one which represents the full amount and subtract the discount and then close parentheses those are all relative cell references control enter double click and send it down now I want you to notice something that right there and then here's another one there are some extraneous decimals here and if we're dealing with money there's no such thing as a half a penny so I need to officially change the underlying number if I'm ever gonna use this column of numbers in subsequent calculations like adding them if I want those amounts to be correct I need to properly round now watch this in the top cell I'm going to hit f2 luckily we don't have to do each one of these by hand because there's a round function after the equal sign before the cell reference I'm going to type our oh you and there it is round I hit tab now round requires almost always some sort of formula I don't think you'd ever actually type a number here but you might there's gonna be some formula which represents the number you very carefully come to the end and now you type a comma number of digits you have to tell round which position you need to round to now for us if you're looking down at the 6/6 2.0 to 5 the way you remember how to use number digits that you start at the decimal and count 1 to that second position is where the penny is that's the position we need to round to that's how I remember to put a 2 for penny now notice if we were counting one two if we needed to go further to the right we go one two three four and so but notice what if I was going this way what if I needed to round to the dollar like for some tax calculations well if this is 1 2 this would be 2 1 and 0 those are the two memorization tricks I remember for howdy put the right number of digits - because we're rounding to the penny close parentheses control enter double click and send it down and sure enough boom that number and the rest of them are properly rounded now we have our last column equals VL tau our lookup value I'm an arrow over to get sales rep comma the table here is the sales rep table second column has our region f4 to lock it comma 2 comma 0 for exact match close parentheses control enter double click and send it down now I can show you a little trick here we did exact match now remember when we did for example over here approximate match remember the requirement for the first column had to be sorted from smallest to biggest if you ever wanted to not use exact match because it is a very slow calculating method for how to look something up as long as the first column of the table is sorted from smallest to biggest in our case alphabetically right which this one is if it's always going to be sorted then you do not have to use exact match now if I went like that and deleted the zero notice there's a comma so that argument is still there anytime you leave an argument empty it actually internally converts to a 0 so even doing that would to be doing exact match you're really as we've repeated numerous times already in this video you just leave it out ctrl enter double click and send it down it does exactly the same thing all right very important concept for viga cup oftentimes we're given data sets we have incomplete data because we need to extend the data set to make our report we have to use various vlookups based on look-up tables all right now in few examples ahead we're actually going to use this big data set 50,000 records to create some pivot tables but before we do that I want to introduce you to the concept of pivot tables so I want to go over to the sheet pt9 now I clicked on the sheet PT 9 and we got to switch gears and talk about pivot tables now actually I shot the first part of this video with Excel 2016 we were doing formulas there was no difference but now some of the pivot table features in Excel 2016 are different all right here's some notes up at the top I'm going to leave those there for you to read if you'd like I'll cover all of them in the video now this is going to be an introduction to pivot tables now what is a pivot table well first before we know what a pivot table is you actually have to have a proper data set and your goal is to create some summary report where you're creating some calculations with conditions or criteria now notice we have an entire column of sales and we're not just adding all of them we're adding just some of them so right there I need to add all of the sales from this column but only when the date is 10 21 13 from this column similarly down here we're gonna have a regional sales report for that calculation right there I need to not add all of the sales numbers just the ones where Southwest is in a region column if we were doing this manual we'd actually have to very carefully look through each record in this data set and look for the region Southwest and then pick out the sales number very carefully going all the way down looking for Southwest picking out each one of those numbers once we have all those numbers then we could to get our some calculation with a condition or criteria so when we use the pivot tables much different than when we do if you look up right we have some summary report where there's calculations based on criteria now really the very first thing is you have to make sure this is a proper data set so let's define that this is called a field name or column header only dates go in this column field name region only the region sales rep and sale so you have to have your field names in the first row then you have to have records in each row and you have to have empty cells all the way around your data set for example you can have some little note right next to the data set I'm going to click escape now when you create your pivot table for the first time it helps to visualize your report up front now notice we have a different calculation in each row but at the head of each row is our criteria or condition for adding same with this report the row header Southwest is our condition for adding now this is a simple report with a calculation with a single condition here let's go look at pivot table 10 this is our next example we'll actually have to calculate a total based on two conditions at the head of the row will be our date from the date column and at the head of our column will be our region so visualizing the report up front really helps when you use the pivot table feature to create reports all right so let's see if we can create these three reports now I'm gonna click in a single cell over here and pivot tables can be found under insert table group and you click on pivot table now there's only two steps to this create pivot table dialog box the first one is where is the data set if you have a proper data set with empty cells all the way around it will always guess right so that steps done second step where do you want your report on new worksheet or on an existing sheet for this first example we're going to compare and contrast our pivot table to these reports done with formas so we're gonna put it on this existing sheet click in location and I'm very carefully gonna click in I 18 click OK now our pivot table fields task pane comes up and look at this there is a list of our field names not only that but down here there's row area column area and values now let's just look at our report our criteria from the date column is at the head of each row and our value were adding is from the sales column now notice what happens when I click outside of the pivot table what happened to the fearless no problem click back inside the defined area of the pivot table notice it says rows and values so the trick is once we visualize then it's easy we simply take our date hover your cursor and click and drag down to the row area and when you let go you've got to be kidding me there is what is called a unique list of items the pivot table feature look through this entire column and gave us exactly one of each called a unique list or sometimes called a distinct list now we take our sales drag it down to the values area and you got to be kidding me look at that a few clicks and we have the start of our report two other things we're always going to have to do when we create our report that is not a good label for our list of dates it should say date and that is the default behavior of pivot tables but no problem we're always going to go up to the design ribbon tab special context-sensitive pivot table tools ribbon tabs click on design go over to layout click on report layout and we want to either show show in tabular or outline I'm going to use tabula and what does it do it lists the field name now I wish that was the default or that there was a way to change the default but there isn't so we always have to go up to report layout tabular the second thing is we want to add number formatting now unlike cells in our excel sheet this is actually a field so when I click in a single cell here to go in add number formatting it will automatically do it to the field now I'm gonna use the method of right-clicking a cell in our values area right click and what you don't want to do is point to format cells that's the dialog box we use what we're doing number formatting to the cells and I'm gonna click on it by mistake you can always tell that this is for the cells by noticing that all of these tabs are here so we don't want this one you want to right click and right below format cells is number formatting when I click on this number formatting you can tell this is the correct dialog box because there's only a number tab now Microsoft made a mistake when they titled this dialog box it shouldn't be format cells it should be format the pivot table field because that's what it will do I'm going to click on currency and I'm going to display zero decimals when I click OK notice it did it to the entire field all right in essence what do we do we dragged and dropped two fields change to tabular layout add a number formatting and that is done now we're not learning how to do this with formulas in this seminar here but what you would have to do is you would actually manually have to go through here and list all of the dates then you would have to create a formula that hats with conditions or criteria now let's do this again we're gonna create this report right here click in a single cell in your proper data set insert pivot table it got it right because we have a proper data set existing sheet the location now this is sort of dangerous because later we'll see that you can pivot PivotTable but I'm sure that I'm gonna leave this top pivot table just like that so I'm going to list the pivot table right below in i-26 click okay now we go up and get region and watch this one I drag region down to the rows area instantly I get a unique list now imagine if this was 50,000 rows tall and you had to manually go through and verify and create a unique list pivot tables do it automatically now we drag sales down to values instantly it makes our calculations based on our conditions or criteria we don't like row labels we go up to design over to layout report layout show in tabular right click a single cell in the values area point to number formatting I'm gonna say currency display zero decimals click OK that's pretty amazing now let's create our third pivot table report I'm going to click in a single cell insert pivot table it got it right because we have a proper data set existing location we're gonna be daring I'm gonna put it always at least two below I thirty two click OK drag sales rep down to rows I'm going to drag sales down to values and notice it defaults to some in a couple examples ahead we'll see that there are 11 different aggregate functions and then there are some other types of calculations we can make but anytime you drag a number down into values it defaults to sum all right row labels design report layout and I'm going to show you outline that won't work also it gives us the field name notice the difference is that we don't see the grey lines right click a single cell number formatting currency and by the way I'm just showing zero decimals you could show two if you wanted or even more click OK and there we have three summary reports where we're doing calculations based conditions or criteria all from a proper data set now let's go over and look at our examples 10 to 12 same data set but this is called a cross tabulated report that means any intersecting cell is adding not all of the numbers but adding only the numbers from this column where the date is equal to 1024 and the region is equal to Southwest now we visualize this up front which means we'll drag the date column to the row area region column to the column area and then sales to the values this is really going to demonstrate the power of pivot tables actually that's a hard report to create with the formula all right I click in a single cell in my proper data set insert pivot table it got the right range because we have a proper data set I'm going to put it on this existing sheet I'm going to click in K 18 click OK now let's drag date down two rows and instantly we get a unique list I'm going to scroll over using the horizontal scroll bar just a bit I'm gonna drag region down to the columns area and look at that just like that a unique list of regions now I drank sales down to values you've got to be kidding me that is amazing I don't like row labels or column labels so I go up to design report layouts show in tabular right click one cell number formatting I'm going to apply currency and leave two decimals click OK now if you wanted to see zeros by default it doesn't show zeros but we can tell pivot tables how to display an empty cell we want to right-click somewhere in the pivot table and down here is pivot table options here's our pivot table options dialog box on the layout and format it says for empty cells show and I'm gonna put a 0 click OK cross tabulated report with just a few clicks that is pretty amazing now I'm gonna scroll over let's make this report it's going to be sales rep and region and we're gonna put this on a new sheet I'm gonna click in a single cell now I want to show you a keyboard for invoking the create pivot table dialog box and it's not a ctrl keyboard because there is no control key bowl for a pivot table but let's hit or Alt key just tap it and let go and notice what happens these are screen tips as soon as you hit the Alt key if you want to go to the insert tab which we do you then tap the N the N key so I'm going to tap n now notice I tapped it and then let go of the key now I'm noticing there's a V so if I type a V the create pivot table dialog box pops up now I'm going to click escape all keyboards when I hit Alt alt keyboards are meant for you to teach yourself the keyboards that you use all the time so if pivot tables is one of the things you do all day long at work the first few times you look at the screen tips but once you get it and you know it watches all 10 V that is much faster than going up and using your ribbon tab now notice also that the default is a new worksheet and for any ok or enter button that's in a dialog box if it is the highlighted button that means the Enter key will invoke it so if I hit enter it'll automatically put this pivot table on a new sheet enter now I'm going to right click delete this because I gotta show you this delete all right says are you sure I'm gonna say yeah here it is I want to create a pivot table report on a new sheet Alt + V enter if you do that at your work and your boss sees you they are gonna love your efficiency and speed now I'm going to drag the pivot table field list over here we want sales rep down to Rose region up to column sales down to values don't like row labels design report layout show and tabular right-click number format currency and I'm going to say zero decimals right click pivot table options for empty cell shows 0 click OK that is pretty amazing now I'm going to come down and name the sheet I'm going to double click and call this PT 11 and enter now we have to learn why they call this a pivot table here's our pivot table dialog box you created this report in fact I'm gonna zoom by holding ctrl and rolling the wheel on my mouse you made this beautiful cross tabulated table and your boss comes in and says I didn't want it as a crosstab I wanted all the conditions and criteria listed vertically so what do you do you say no problem click on region in the column area drag it down to the rows I'm going to drag it below sales rep and when you drag it we have pivoted our report that is amazing maybe the boss came in and said no I wanted region and rows and sales rep up in columns just like that we have pivoted our report I'm going to drag sales rep down to region and that is pretty amazing now another benefit of the fact that we right click number formatting remember that feature right there formats the field so as we pivot it it's not relying on the fact that we added number formatting to the cells we actually added it to the field itself alright drag sales rep down here pivot table now you know why they call it a pivot table let's go back over to PT 1012 here's our first cross tabulated report now notice we have sales rep over here I want to add the sales rep and I'm going to scroll I'm going to add the sales rep as a condition but I'm not going to use rows or column I could use the filter which means it would add a drop down filter above and it would filter the whole report but instead of using filter we're going to use a slicer which does the same thing it'll actually filter the whole report remember that intersecting cell takes Southwest and date as soon as I move to a different cell there's two different sets of conditions or criteria but now we want to add a slicer that will add a condition to all of these numbers on the inside of the pivot table I'm gonna click in a single cell go up to analyze over to the filter group and click on insert slicer I'm gonna check sales rep click OK and now we have a slicer for sales rep we could size it we can come up to context-sensitive ribbon slicer tools options you could change the color if you have lots of conditions you can change how many columns for example if I did up to 2 we'd have sales rep listed in two columns all right I'm gonna click and drag and here it is when I click June those are the numbers for June every single cell in here if I click right here that's a calculation done with three conditions the date 1024 the region west and the sales rep June when I click on Gigi that cell right there is adding with three conditions 1025 West and Gigi we can unfilter by using that unfilter button alright so that's an introduction to pivot tables we always start with a proper data set we drag and drop to our field list we are making calculations with conditions or criteria now we want to go and look at PT 13 to 15 now on the sheet PT 1315 this is a picture of the final report we need but where's the data set we're actually going to go back to v8 and here's our data set if I click at the top control down L we have more than 50,000 records here we extended the data with our vlookup this is the data set we're going to use in particular we need net revenue we're going to make calculations on this column we're gonna need our region and we're gonna need date now let's go back over to 1315 I'm going to slide this off to the side and I'm gonna click in cell a5 go up to insert pivot tables or we can simply use our keyboard alt + V now look at that by default it assumes since there's not a bunch of data everywhere that we want our pivot table here and it's waiting for us to tell us where the range is no problem that text box we can simply click on v8 notice the sheet reference part of our range is already entered so create pivot table dialog box we'll know always to get the data from VA but watch this here's the field names and I don't want to manually drag down I'm gonna click that again and I'm gonna highlight just the top notice the dancing ants are highlighted and we're gonna use the keyboard ctrl shift and down arrow to highlight all the way down to the bottom that is an example of a keyboard saving you a lot of time we highlighted 50,000 rows when I click OK back on the sheet PT 1315 there's the start of our pivot table there are all of the fields now remember our report needed years and months watch what happens when I drag the date field down to rows now anytime you do this in a pivot table instantly you get a unique list even though there were many duplicate dates over there we have a unique list now interestingly enough if we dragged and drop the date field down two rows in Excel 2016 it would have automatically grouped it by month and year but watch this in earlier versions we just had to come over to our unique list right click and there it is group and when I click group it gives us the min and Max date by default it shows months but we definitely want years also we wouldn't want all the January's from 2016 and 17 rolled up into a single number so when we click months and years and click OK you gotta be kidding me look at that it grouped us and when I click back in the pivot table if I were to drag net revenue down to values look at that basically with a few clicks that calculation is looking through that data set over there and adding all of the net revenues that were from January 1st 2016 all the way to January 31st 2016 now we want to pivot this I don't want years in the rope I want to drag it over to columns all My heavens already there that's an amazing report that accountants have to create from a transactional data set it gives us month year and the intersecting value is the sum of net revenue now I immediately want to come up for row labels I want to say design report layouts show in tabular now that's a beautiful report right there but that's not our finished version I actually want to create two columns for 2016 one to show the change from one period to the next and then another column to calculate the percentage change that means that instead of one field being dragged down here I'm going to drag the same field down twice now 2016 we have to duplicate columns but now we need to learn about something called show values as now in our next example we'll see how to change the function from psalm to average and min max but here we want to see that there's another feature besides functions that allow us to make calculations in pivot somewhere in the sum of net revenue I want to right click summarize values by gives us different functions and we can change those but show values as gives us a bunch of amazing calculations that are sometimes really hard to do with formulas we could calculate the percentage of grand total so it'd compare every single number to the grand total if we wanted to do it for just columns or rows we can do that down here there's even a running total if I said running total it would give me a running total from January February March adding each subsequent month but that's not what I want either I want difference from now notice it says difference from that's because it needs to know first off which field we only have one field in the row area so it just tells us that it will do it on date but the base item sometimes in accounting you want to compare everything to a particular month like January but what we want is previous that way for each cell in each row it'll calculate the change from the previous month that's it I click OK and not only in this column but also for the 2017 there's some of net revenue it calculates the change from period to period now we'll change the name up here and the name of this one notice to put a two there because there was a duplicate but let's try this right click show values as down two percent difference from date and I want previous when I click OK there's the percentage change there's the amount now I want to in sum of net revenue right click number formatting will do something like currency zero decimals click OK notice when you do percentage change it automatically adds a percent number formatting assuming that that's what you want to change columns one for each year two percentage change columns one for each year now one thing I do don't really want the total change or the percentage change so I want to turn off the grand totals somewhere in the data set I click and I go up to design in layout grand totals off four rows and columns and there we go now I want to change the name of this so I'm going to click here and we're just going to type change net revenue and when I hit enter it changed in both places now I'm going to click here and type percent change in net revenue maybe I'll change the column widths a bit also now one last thing we can change the formatting click in a single cell go up to design you can actually pick all sorts of different ones most of them are just terrible but I'm going to choose this one right here it's kind of a minimal just add bold to the top that is an amazing report from 50,000 records in a data set including our vlookup columns with changed calculations and percent change calculation based on month and year now we want to go look at our last pivot table example on the sheet PT 16 now here's our data set date region sales our customer cost of goods sold and sales now we're actually only interested in sales rep and cost of goods sold but we're gonna try to calculate five different aggregate calculations total cost of goods sold account for how many sales each sales rep had the average min and Max from the cost of goods sold column I'm going to click in a single cell insert pivot table or use the keyboard alt + V it got the right data set we want to put it on this existing sheet the location will try H 3 click OK now we can drag sales rep down to rows and instantly we get a unique list from that column now we want to analyze cost of goods sold and have five different calculations so I'm going to drag values down five times and look at that our field list down here for values isn't even big enough to show all of those now I should show you this if I close this and later want to get it back if I click inside of here it's not going to show up because I closed it in that case you would right click show field list and it would come back but I'm gonna close it for now I'm also going to move this I should have put it one cell down so watch this I'm gonna highlight the pivot table actually inside is just fine highlighting in exactly and I'm gonna point to the edge and when I see my move cursor I'm gonna click and drag down I'm gonna type a label at the top I put cost of goods sold report and enter it actually is trying to autocomplete the format I'm gonna leave that there I don't like row labels so I'm going to go up to design report layout show in tabular now for each one of these columns I have to change the calculation and the number for many now I'm gonna click in the top and actually type total and enter because we have the sales rep name here and cost of goods sold label up here now it already is some function so right click number formatting will say currency no decimals click OK now I'm going to skip this one for the time being and I'm going to come over to this column we want to average right click summarized by average right click number formatting currency no decimals click OK I'm going to click at the top and type average and enter right click the fourth column summarize values by and there's our min I'm going to type minimum and enter right click number formatting currency no decimal click okay right-click the fifth column summarize values by max right click number formatting currency zero decimals click OK and then I'm going to type maximum and enter now I'm going to click back in here right click show field list and I want to drag this off and notice it was a number so it tried to use the sum function I'm going to drag this off and now watch this I'm gonna specifically drag a text field this is sales rep and I'm gonna drag it and I'm very carefully gonna drop it right after total and look what happens if it is a text field it defaults to count because of course text you can't add me and Max or calculate an average now I'm simply going to click at the top count transactions and enter now I can close this pivot table field list come up here and let me show you something here this has font and fill and I want to get rid of all of the formatting so I go up to home over and editing the awesome eraser clear all would remove everything content and formatting I want to say clear formatting change the column width and watch this I'm going to cheat I want to copy the formatting I'm going to use right click and this is called the mini toolbar and there's the paintbrush if I click this it copies just the formatting when I click in that cell it pasted just the formatting and so there we have a pivot table report for sales rep based on the cost of goods sold column where we did some count average minimum and maximum wow that was a lot about pivot tables and in fact this whole video was a lot about pivot tables and vlookup hey we can use pivot tables to go from a proper data set into a report that has multiple calculations over on 13:15 we can use the group by feature to group by dates and use show values as to calculate things like the change between periods and sent its change over on PT 1012 we saw how to create a cross tabulated table and we saw how to add a slicer back on PT 11 we saw how to pivot a pivot table back on PT 9 we saw the basics of going from a proper data set to reports that at their essent have calculations with conditions or criteria back on VA this was our culminating vlookup example where we did vlookup multiple times to extend the data and then we made a pivot table from that over on v7 we saw how to extract a record using vlookup and match on v6 we saw how to use vlookup and the sum function to create helper columns for our payroll table back on V 3 to 5 we learned about approximate match vlookup on v2 we created an entire invoice using data validation drop down list over here if error vlookup and another formula using the if air and back on v1 we started off talking about data validation list and exact match lookup alright thanks for watching thanks CWU Lynnwood for awesome fun with Excel vlookup pivot tables and accounting alright we'll see you next video

Channel: ExcelIsFun

Views: 253,296

Rating: 4.940825 out of 5

Keywords: Excel, Microsoft Excel, Highline College, Mike Girvin, excelisfun, Michael Girvin, Mike excelisfun Girvin, Excel Magic Trick, VLOOKUP, PivotTables, VLOOKUP Function, Pivot Tables Made Easy, Excel CWU, Excel Central Washington University, Excel for Accountants, Accountants VLOOKUP, Accountants PivotTables, VLOOKUP for Calculations, VLOOKUP for Data Analysis, Approximate Match VLOOKUP, Exact Match VLOOKUP, Pivot Table Slicers, How to Use VLOOKUP in Accounting, CWU Lynwood Excel

Id: v5l82vjuMpY

Length: 92min 45sec (5565 seconds)

Published: Thu Jan 12 2017