# Excel for Accounting: Formulas, VLOOKUP &amp; INDEX, PivotTables, Recorded Macros, Charts, Keyboards

Just another WordPress site

### Excel for Accounting: Formulas, VLOOKUP &amp; INDEX, PivotTables, Recorded Macros, Charts, Keyboards

shortcut is ctrl shift grave accents or till da so ctrl shift grave accents or tilde and that applies the general format now that’s pretty weird but this is convenient when they first started making spreadsheets they wanted people to be able to enter dates into the cell and due date math so we could subtract two dates and figure out how late an invoice was the first day in Excel history is 1:1 1900 day 2 is 1 – 1909 4 is 41521 so that’s the day that the seminar will be given this is the day that I’m shooting the video so that’s convenient but again there’s this disconnect if you click here and look up here they didn’t for whatever reason they didn’t put the actual number there probably because most people would just say what is that but we know it’s just counting it’s a serial number 1 2 3 4 5 all the way up to whatever day it is since December 31st not 1899 so since that date 1 1 1900 is 1 X since this date here 41521 now the reason this is so important important it’s because of course we can do date math I put whatever date I’m going to do ctrl semicolon and enter the way you do date math to figure out the difference between two dates I’m going to type an equal sign and I’m going to use my arrow keys because if the formula inputs the cells with the things you want to calculate upon are close it’s more convenient and quicker to use your arrow keys so I’m going to do the minus sign and then up up arrow that’ll give us the difference and that’s perfect for invoicing that’s the way invoicing or loans days that alone is outstanding however if you have a project you have to remember to subtract the later date – the earlier date and then add one back in so anytime that the start date is included you want to add one back in now there is a keyboard shortcut for to the current time it is ctrl shift semicolon and now I’ll give you the current time and actually really cool you can do ctrl semicolon space ctrl shift semicolon and I’ll give you the date and time now let’s just look at these two things here I’m gonna wipe away the number for min and look at general control shift tilde or grave Accent so if we had today’s date control semicolon control enter control shift tilde you see it’s just an integer time is always a number between 0 & 1 representing the proportion of 1 24-hour a day and if you do them both it combines them now the reason that time is a serial number between 0 & 1 if only I could type anyway the reason that time is the number 2 & 0 1 is because it represents a proportion of 1 24-hour day so what they do is they say 8 divided by 24 equals 1/3 or 0.33 3 3 3 3 out of 15 threes or if it’s noon that number will be 0.5 so if I were to type in the cell 8 : 0 0 km/h Jose 12:00 : 0 0 p.m. if I wipe away the formatting control shift tilde I get those numbers time again the idea is equals 8 divided by 24 equals 12 divided by 24 the proportion of one 24-hour day now let’s scroll down and look at some time examples here’s some times typed into the column if you click on the cell there and look up in the formula bar you can see oh yeah look they’re both showing that number formatting but we know that this decimal that represents a 20 proportion of one 24-hour day that’s actually what’s in the cell now this is important because if we want to start doing formulas like time math figure out time billed as an integer in decimal if we take our formula equals later time minus earlier time control enter it’s going to calculate upon the decimals further because we’re using formula inputs here that have a number formatting that number formatting got sucked right here so it looks like it’s an acceptable three and a half hours it looks like I could type a waves like 25 and then come down here and say equals that 25 the three and a half but what do I get well again I’m sucking down that number formatting remember this keyboard

shortcut ctrl shift tilde is invaluable if you’re doing date and time number for many well that’s ridiculous what’s happening is 25 times control shift tilde 0.14 that’s giving this answer we this person if they work three and a half hours is going to get a lot more than three dollars and 64 cents no problem it’s a decimal and Excel thinks that decimal is a proportion of one twenty four-hour-a-day so I’m gonna hit f2 and edit this we simply force the subtraction to calculate first and multiply times 24 now we’ll talk about in our next topic these are formula inputs all of them that’s an operator these are parentheses two for the subtraction before the multiplication but notice these are numbers that have the potential to change so we put them in the cells label them and use cell references to refer to those formula inputs this however is not going to change this is a formula input that you can just flat-out type into your formula alright we’ll come back because that’s one of the most important topics in Excel also we’ll talk more about when you can type numbers into formulas and when you can’t all right now let’s we looked at time there we can see now that the gross is totally correct eighty seven point five one less number formatting and this has accountants everyone uses percentages so the number of all the years I’ve been teaching and doing conferences and consulting with Excel the number one mistake that I’ve seen has to do with percentage number formatting here it is someone wants three percent so they type a three into the cell and then they either control one percentage go up to here percentage however you do it and they get three hundred percent the problem is Excel obeys you perfectly we put the number three in there it slid the decimal and added a percentage symbol all as a number format that number three is still in the cell control shift tilde shows us that that three is in the cell so that’s not how you do it if you type the number in first or there’s decimal some from some calculation say point zero three that will work you can apply a number format to that but don’t forget the three that’s beautiful if you made 300% profit from this quarter the next quarter but it won’t work for tax rates let’s look at adding the percentage format before you put the number in the cell because here if we put the number in the cell first and then second apply the number formatting we get problems but watch this and I don’t know how they decided that it works this way but I’m going to apply percentage format before I put anything in the cell then if I type a three and by the way you can always tell it’s pre formatted because that percentage symbol pops up that’s not going to be a problem but watch this point zero three the percentage symbol doesn’t pop up but it works so those are the two important things if the cell is pre formatted now a lot of times with percentages you know you’re just typing out you can actually type format as you type so I’m going to type three point zero zero right now if I hit enter or ctrl enter the integer three goes into the cell but if I type a percentage symbol I’m saying hey I’m formatting as I type so when I control enter yes we see the percentage number format but the number that went into the cell is point zero three control shift tilde will verify that I’m going to control Z wait a second I don’t have the undo and redo on my quiet here and the reason why is those are like our best friends we use them a hundred times every day so ctrl Z is undo control Y is redo I’m going to control Z and leave it there here’s another big problem with percentage number format someone has done some calculation they get point zero to five that’s going to be some some rate they come up and they use this percent it’s button oh no my 2.5% is converted to a three again it’s just number formatting if you’re not aware that there’s a disconnect between what you see on the top and what’s underneath you and you can get into trouble now interestingly enough this will show us two point five there up so if you’re looking up in the forum tomorrow you can see it’s correct but really we should increase the decimal either with these buttons or with the quad I’m an alt two by the way all one you can prove that to yourself that is not three percent so equals 100 times three percent will give us three dollars but formulas don’t seem number

example here we have revenue and some expenses I’m gonna say equals go up with my arrow key get the revenues – and I’m gonna type a function right in the formula now I’m gonna do my up arrow and then hit shift up arrow up arrow so we’re seeing for the first cell reference we just air out but if we want to range we can click on the first cell and while holding shift arrow arrow arrow and then close parenthesis now in this formula equal sign cell reference up math operator a function and then a range of cells control enter that gives us our answer let’s go look at our next example now this is going to be example of a logical formula are these two in balance I’m gonna say equals now the equal sign as the first character in a cell when you’re in edit mode makes a formula but anytime you put another equal sign it’s considered an comparative operator so I’m going to say is whatever’s in C 122 equal to D 22 control-enter false well I can see with my own eyes that these are in balance but when I see something like this I immediately suspect number for many that’s why we have logical formulas like this lots of accountants everywhere are checking their work if I were to highlight this and decrease decimals I’m going to I’m sorry increased decimals alt to a bunch and I can see you know there’s an extraneous decimal now it may not be number formatting but at least the formula signals that there’s a problem something isn’t in balance so I changed it control and turn maybe I’ll highlight this and all to one one this logical formula very useful tells us that there’s some trouble and we need to investigate all right next type of formula so we’ve seen calculating which gives us a number answer in a logical formula which gives us true or false how about a text formula there’s lots of great examples for text modal’s here’s one we have first and last and we need to combine them in one cell how about an equal sign and we’ll go get whatever’s in this cell and we’ll join it using the join symbol shift seven the ampersand and I’m going to join it with whatever is in cell D 134 now join symbol we’ll take whatever’s in that cell in this cell put it together control-enter it joined two things but we really need three things so i’m going to hit f2 that is the keyboard shortcut to put a formula in edit mode I’m going to come right up to the ampersand and I need a space any text including the space character has to be put in double quotes if you’re going to use it in a formula so double quotes both space double quote and then a second air for saying because we’re joining three things ctrl enter and now I want to copy this down the column this is the Select cursor if I take my selection cursor to move it tones towards the lower right-hand corner there’s that little fill handle my cursor gets closed on it turns to a crosshair or I like to call it an angry rabbit you can double click and send it down it’ll copy the formula down the column as long as there’s something to the left the right or below it it’ll copy until it sees an empty cell and stop all right let’s look at our next formula example here’s an example we have some numbers and we want to count how many are greater than 350 now this formula input has a comparative operator and a number together in the cell you can see it’s a line to the left it looks like it’s being considered text I’m gonna say well wait a second I want to make a formula I can use the built in function count if now count F has been around a long time I simply highlight the range comma and the criteria it will count with one condition control-enter there are two now in this case it’s perfectly all right to combine these two there’s a bunch of D functions D some D COUNTA they’re very nice and they like to combine comparative operators and numbers like this but there’s some cases where you don’t want that here’s an example here we have a 350 here and we need to use that as a number in other formulas so here we’re gonna use the same count if but we’re gonna have to take our range and combine a number I’m gonna type a comma get to the next the criteria argument the number oh but I need the comparative operator before it so in double quotes I’m gonna say greater than n double quote enjoying it using the ampersand that’s the construction for functions

certain function argument that you have and you want to highlight you can click on the screen tip and highlight it not only that but you can evaluate it to see what it is what it evaluates to what the formula thinks it is I’m gonna hit the f9 key that’s evaluate now I want to immediately undo that using ctrl Z but just for a moment see that’s much different than this and some ifs countifs all those functions will interpret it correctly ctrl Z all right comma we get our date arranged for a second time now I’m gonna do criteria in double quotes less than or equal to in double quotes join and there’s our serial number close parenthesis and ctrl enter very useful sometimes you have to summarize by months and you can have a column of begin and end or years or whatever it is now let’s do our third example we want to count between a thousand and 1500 all of the units that fall within this category for this nth one however we will include the lower limit but not the upper limit so we’ll see some ifs we’re gonna get our some range and guess what all three times we will highlight the unit’s column so some range and then we have two criterias on this between criteria is one way to think of it and criteria both of these have to be met so double quotes greater than or equal to n double quotes and join it to the lower limit and will highlight the units for a third time that’s our criteria 2 comma and then greater than in double quotes join with our upper limit alright and they are we that’s the sum of all of the units that were between these upper and lower all right useful to do between or and criteria like that let’s go ahead and look at our next example this is an example where we have a new function in Excel 2010 called workdays international now what we’d like this to be able to count for a project between an start and an end we have some holidays and our weekend is Sunday not Saturday and Sunday so we use the networking days now networking days has been around long before 2010 but it only considers the weekend Saturday and Sunday so this new function is just awesome down arrow tab we give it our start our end comma our weekends and there it is this beautiful drop-down we can select whichever we want I’m going to double click 11 finally our holidays I’m going to go ahead and highlight and notice some of them are empty just in case next time we use this template we need some extra holidays there those empty cells will not interfere with the correct calculation now we’re going to copy this formula down the column and these two are relative cell references as we copy down the formula needs to look two to my left and one to my life but this range needs to be locked or absolute now the way you do that is you add dollar signs for all of the column and row references now the quick way to do it is just hit the f4 key boom it puts the dollar signs in everywhere that means when we copy this it will not move relatively control-enter I’m going to double click and send it down now relative cell references those blue and green ones are still looking relative to me the formula where am I going to look one cell to my left and I’m sorry two cells to my left in one cell to mouth whereas this one was locked in every single cell or absolute now it most people no relative references in absolute if you know that different you know how to use those you can make spreadsheets quickly however there are two other major types of cell references that help you speed up formula creation time now let’s go look at an example the four types of cell references are relative absolute mixed with the column locked but not the RO and mixed with the RO locked but not the column now if you’re new to this and most people are the vast majority of Excel users on the planet Earth do not use mixed cell references I have this epic video at YouTube and in the slain Excel dragons there’s a whole section on mixed cell references but there’s a good resource there alright so here’s the situation we have percentages for R for expenses and we have our revenues now most times people would make a formula like this in fact I teach classes and all the textbooks how you say hey teach them this way I want to copy this down the column so I need every one of these cells to be looking at the revenue so I’m going to hit the f4 key and then multiply by your first expense now the

updates in I pretty much can’t put something that’s not from the draft I know technically you can copy and paste from another cell and overwrite the the data validation but if I type something here that message pops up pretty cool all right now what if we didn’t have data validation or in some cases you have an empty cell like an empty template so our vlookup if we were looking here in this table right here comma column 2 comma 0 exact match we get an n/a so in some canned product n as we saw that’s going to be na 2 so in some cases you don’t want to see this na you want to see something different there’s a great function Excel 2007 or later if error now the great thing about if errors it just evaluates the vlookup if it’s not an error it will deliver that to the cell the result of the vlookup otherwise you come to the end and you tell it right and if error and what do you want to see in the cell if it’s an error now there’s a couple ways we could go in fact there’s all sorts of things we could put here double quote double quote is the syntax for nothing that is just like we saw our text element formula elements earlier except for there’s nothing there not even a space it’s a null text string with zero length so it’ll show an empty cell you could also if you had data validation put something like select well that wouldn’t pop up you might put something like not in list so in this case we’re not using got a validation not in list all right but if we put product 3 boom it goes and gets the price all right now that’s exact match approximate match you have all sorts of situations where you have a table could be Commission’s like in our situation right here so these are the different amounts these are the Commission’s you get or tax tables lots of other examples square-footage tables for example but here the trick is you actually have you have to list the items in ascending order from smallest to biggest and really when you think about looking this up we need to get the Commission rate and put it here well if we look this up which one of these categories does it fit within because vlookup is were going to require that we put the numbers this way and not some category like greater than or equal to 2,000 but less than 10,000 now if you have your table set up that way you have to have it as an extra column that’s not included in the lookup but here’s the deal this is really convenient and here’s the way to think about it if we do equals vlookup and look this up doing an approximate match what vlookup is going to do is take that number race through the first column in the table and when it bumps into the first bigger number it knows to jump back now technically that’s not what it does it does a search word divides the table in half and it’s actually really fast it doesn’t have to look through every item like with an exact match but for understanding races down and bumps into the first bigger one and jumps back a row that’s how it determines the row with approximate match that’s why we were allowed to have gaps in between there’s lots of numbers between two thousand and ten thousand that we might consider for a commission rate of point zero four all right so the tables the same we still only have a two column table and the second column has the thing I want to retrieve now we can put true but we don’t really have two or a one but we don’t really have two if you know the default for this function you can leave it out the default is approximate match now there’s a hint in every screen tip if you see square brackets around the argument that means if you know what the default is and that’s what you’d like you can just flat-out leave that argument out so when doing approximate match we don’t need that last argument only one two three the first three arguments and so now whatever we put in here twenty-five hundred seven thousand but if I type exactly ten thousand what it does is it has to take this race through the table when it bumps into the first bigger one it jumps back two other examples if you in this case you would never put a mega t’v number but if you put a number smaller than the first one it’s gonna give you an n/a but conveniently of course someone could sell more than 15,000 so the way it’s programmed this if it’s bigger than the last one no problem it races through it can’t find a bigger one so it just takes the last one all right so that’s a proximate match let’s look at another great example for lookup often times you

have a table and you want to retrieve multiple records but look at here if I’m doing vlookup to lookup this ID so I’m gonna find this third row right here well the first name is in the second column the last name is in the third column and the email is in the third fourth fifth six seven eight column so I could easily do this vlookup now I’m gonna look this up I’m gonna copy this to the side f4 that’s perfectly alright here because you’re going to copy it OB locked if you want to be hardcore we only need the column reference seek us as we copy this way we’re not copying it down we only need the c-loc our table I’m gonna highlight the whole table f4 to lock in all directions because I’m going to copy this format now right now first is to so I’m simply gonna do – and then comma zero for exact match all right yeah so these are not sorted so I’m not going to mess around and do that but watch this as I copy it over then I can come and change this one to three and this one – okay that’s eight right so there we go we have built a formula to retrieve a record or some informations from a record when I changed the ID here at all changes now in many cases especially if you’re doing this all the time or you have you know lots of formulas you don’t want to have to hard code this in three or go and edit them not only that but sometimes you have situations where this formula input I mean it should be a formula input and we shouldn’t man we have to say hey that’s in the second column there’s a function that will actually look up an item and tell you the relative position of the item in a list and it’s called the match function it is a lookup function but it looks something up we’re looking up the field name comma within one-way array I’m gonna highlight from ID all the way to the end only the field names and hit f4 now look it’s only got one dimension meaning there’s one row eight column so it can find the relative position it actually looks for first it goes hey that’s not first but the second one is so it will report a to the relative position of an item in the list come up and this is where you have to this is the other lookup function that uses one zero and negative one we’re gonna use exact match zero these field names are not sorted and never will be that’s it control enter and copy it over by itself it seems ridiculous relative position why would you ever want that but this is perfect for that third argument in vlookup so watch this I’m gonna copy this in edit mode not the equal sign just the match controls C escape and then I’m going to come here click on that column in Nick’s or highlight that too and ctrl V to paste so now we put match inside the column index inside a vlookup control enter and copy this over now in this situation you may never want to change these but you could absolutely type now city and this is the essence of excel formula input when I change it the formula updates so that’s looking up a record finally for our couple more examples and this is a really common question certainly at YouTube over the years this is one of the more common questions I received you have list two and this one and we need to compare these well we in our case take these prospective customer less and compare it to the list for the company right we’ve been trying to get these people to become customers well we can see that PCC is already in the master list so what we’d like is to compare these if so the question is going to be for each one of these elements unless two is item in list two in list one we can use the match function not the math function not the matter the match function I’m gonna look this item up comma within this whole list notice just a moment ago we saw that match did a one-way with many columns now we’re doing a one-way with many rows I’m gonna hit the f4 and then comma zero not sorted so what does it say na means guess what that item is not

in this list this number says that item is in this list now oftentimes this is all you need the numbers are or they in our case if these were prospective customers the numbers mean I don’t need to phone them anymore that anaise mean I should still phone them right but in some cases you want the NA s in other cases you want the twos so we can take our match and actually notice that that’s a relative cell reference that’s absolute I can actually copy this cell and paste it right here notice the relative works perfect the absolute works perfect so I’m going to copy this down I’m gonna do the same thing here control V I’m actually going to copy this whole column ctrl-c ctrl-v so if you don’t want the messiness of Na and numbers now I actually like to turn those dancing ants off I’m gonna use the escape now this is the same question we had up here is the item in list two also in list one so I want to know is PCC here true so I’m interested in the numbers so I can wrap put match inside the is number function there’s a set of logical functions that start with is and it will do these functions will deliver true or false because it’s is number the numbers will get it true that n A’s or anything that’s not a number will get a false if you’re interested in the N a switch if you had a customer list these are the people I still want to call in that case and n a is good and sure enough is there an is n a you betcha and then control enter and double click sending that send it down so very important concept of comparing to list the match function can do it is number can help and so can is n a alright our last example before we move on to our next topic sometimes you have to look up something to the left now vlookup look at that there’s a so I’ll edit while I’m filming here you need to look something up and it’s the thing you want to retrieve is not in a column that’s to the right of the lookup column in this case hey there’s the name of the product right here and I want to retrieve ID now in many cases you just move this column over here and build your vlookup based on the first column but if you can’t here’s a great solution now we saw how match works and we saw how vlookup works another incredibly useful function is index now index is great you give it an array of house we’re not going to talk about this one this is when you have multiple tables by the way I have a playlist of almost 90 examples of lookup everything under the Sun lookup left lookups when you return multiple items looking at pictures so this playlist of videos has five or six videos with 90s examples of almost every lookup example I can think of alright and this one’s in that but here this is the more common one we’re going to give it a rain now this can be a two way or a one way and then you give the index function the row number and the column number and it retrieves the intersecting value now in our case we’re going to use the array of values that we want to retrieve is only going to be one way so I’m simply gonna highlight one two three there’s one column and three rows then I simply tell it which row the item is in well guess what match comes to the rescue I say hey match look up boom three comma within the product column it’ll determine one two or three feed that as a relative position and I’m gonna assume these are not sorted feed that as a relative position into the row number now really that’s also it’s the row number from that particular table there that blue table one two or three so actually I wouldn’t liked it better if they called this relative position because it’s not 319 320 ok so match and by the way we can highlight this and let’s do our evaluation trick f9 it’s telling the index please go get the third item from this array right there now be sure and undo this control Z you don’t want to leave that hard-coded we don’t have a column number we’re not doing a two-way lookup so we can simply leave that out that’s a lookup left that’s a great solution for that notice here I used the indent feature right there because sometimes it’s annoying when you’re selecting and if with this drop-down kind of hangs over there’s

though because if there’s anything below it it might get replaced I’m gonna say new sheet and then enter now watch this alt NVT enter that’s how it should go for you after you create pivot tables for a while now it immediately puts sheet 1 I’m going to go ahead and call this first pivot now obviously if you had a particular name you might give it a better name but naming sheets is always very important I just double click that and typed and hit enter all right now this is the field list and check this out they give you all of the fields here that’s why they’re so important how would you know where to drop the fields if they didn’t have names and look at this row labels and it even gives you a little color-coded picture those are the rows these are the column and there’s the value it uses that Sigma some Greek letter character to indicate that this is where it’s going to make the calculation so what do we do region where did it go in the columns now notice I’m dragging dragging no no no no that no symbol and then boom notice it even in the icon there that blue strip is at the top saying that sits in the column instantly we have a unique list of our regions now we take our sales rep it says no no no no no I could drop it there and we’ll talk about that later but I want it in the row labels instantly a unique list of sales rep names then we take our units and we drag it there that is unbelievable with a quick keyboard and three drags of our mouse we have created an amazing pivot table and that really is how easy it is again it also depends on you understanding row labels column labels in a cross-table tabulated type of table now immediately in its 2007 10 and 13 there’s a problem here it says row labels and column labels instead of listing the field name so you immediately you just have to have this as an automatic reflex go to design go to report layout show in tabular form and you know there’s no way that I’ve ever seen to set this as the default but watch OOP much better much better we can see the field names there now we’ll do that many times throughout this video so you know we’ll get used to it but there you go now we have our cross tabulated pivot table now notice that we have some numbers here if we wanted to add number formatting we could highlight this and do control one but that would format the cells only and not the actual field so the more efficient way to do this is and there’s a few ways to access field settings you could go to the options pivot table tools and field settings you could go right click value field settings that’s what I tend to do I’m gonna open up the value field settings and we’ll see this a bunch because we’re gonna look at a bunch of examples you can change the field name here now if you wanted to let’s just change the feeling I’m just gonna put some well you could change the function we’ll talk about this show values as that’s a different types of type of calculator but there’s the number formatting you gotta click that so it’s a couple extra clicks rather than doing control one but even though this looks the same we are actually adding the number formatting to the field so when we learn how to pivot our table and move it around the number formatting will stick all right I’m gonna click on number and then decrease the decimal and use a comma click OK click OK so there are our units that was adding number formatting now style there’s a bunch of built-in styles up here you can select whichever one you want I usually go pretty minimal there’s some built-in styles so you see if you click one here or whichever style you like it’ll immediately apply that style know the problem with pivot table Styles is there they’re not very good so I like to come down to the bottom here and I think you can’t see that that’s off the screen you click on this more button here right down at the bottom it’s new pivot table style I’m gonna click on that but I’m gonna scoot this back down here more and then new pivot table style now we can give it a name I’m going to call it my style even though I don’t have any style now I can have pivot table style and I

the report is filtered using a slicer now I’m gonna move this over here okay so filtering absolutely amazing that great option to show report filters as individual sheets let’s go back to our pivot table data set will click on PT data now before we do our next pivot table we want to compare and contrast formulas and pivot tables now in our next pivot table this is the end result we’re gonna have months as row headers and region collimators now look over the data set we’re not given months we don’t already have it some right so we’re given daily dates and we need to somehow summarize from daily dates the pivot table you won’t believe it it’s just a few clicks and instantly it can be done now the problem with doing this with formulas is that this is text criteria jan10 directly match up with serial number dates so the formula gets a little bit wild right there it is I mean that’s a wild formula so the advantage to pivot tables here is that they’re easy well we’ve already seen a bunch of examples of how pivot tables are so amazingly easy so why in the world would you ever do formulas well two reasons the first reason is in a pivot table there are only 11 aggregate functions and then later in the video we’ll see that there are some other ways to calculate but the calculations that you can do an ax pivot table are very limited compared to the nearly infinite capabilities of formulas and there’s a second reason now let’s look at Midwest April 13 26 well the formulas in the pivot table both get the same answer of course however if you change the source data so let’s take April Midwest that number 49 again we get the same number if we change any formula input whether it’s raw data or our criteria formulas instantly update pivot tables do not instantly update now it’s easy to refresh a pivot table but in many situations when you’re building solutions you do not want that extra step you want formulas to update instantly let’s look at an example I’m just going to change this to some ridiculous number like 5,000 let’s come over and look at that instantly the number updated with the formula but not the pivot table now it’s really easy all you do is right click refresh and there’s a refresh button up in the the options ribbon up there instantly it updates but again for lots of solutions you want to change something and have it instantly update in essence those are the two comparisons pivot tables are ridiculously easy pivot tables cannot do a lot of calculations that functions can pivot tables don’t instantly update formulas do all right now let’s go and build this and see just how easily easy it is I’m going to change this back to 49 enter I better come over here and right click refresh and instantly now we have the same numbers again alright let’s go over and create our second data set I’m going to click in a single cell and then use our keyboard shortcut alt in VT enter now we want to first drag dates to the row labels instantly we have a unique list of dates and in fact this is a great trick right here if you drag sales you get daily total but that’s not what we want we want to group these so all you have to do is right click group now if you have multiple years you want to be sure and not just select months but select years also my rule of thumb is I always select years when I click OK and then I see that there is only one year that’s my way of checking then I grab the years and drag it back up here there I have months now I can drag the region and I want sales so I’m going to drag whoops look at that I drag the reason down there by mistake so now I’m going to drag it up here and instantly I have my totals we could format this I’m going to leave that right there now we want to talk about grouping sometimes instead of grouping by dates you want to group by number now let’s go look at our data set here let’s say I want to count all of the sales between 0 and 500 500 and a thousand well we can

do that not grouping by a date by grouping by some other I’m gonna click in a single cell actually I’m going to notice over here I’m gonna double click and call this date pivot enter all right so I’m going to come back over here and we want to group by number now there’s two types of grouping by number situation there’s a situation where you have integers and if you look through this entire sales column they’ve all been rounded there are only integers there’s no decimals whereas the cost of good souls has some decimals so when you group by numbers you got to just keep in mind are they all integers or are there some decimals now it doesn’t matter that much but the way the label that’s created by the PivotTable engine is different whether it’s an integer or it has a decimal alright let’s click in any single cell and do alt + VT enter there’s a new sheet now I’m going to drag sales to the row labels right now I’m going to come over here and right-click group it’ll instantly give me the min and the max so I’m gonna start at 0 I’m going to end at 2000 and I’m going to increment by 500 when I click OK instantly it gives me a label now notice the label because it’s an integer with no decimals 0 – 4 99 500 to 999 so there is no repeat this doesn’t say 0 to 500 and 500 to a thousand right so integers have cleaner labels though the labels are not ambiguous we know exactly which number goes in which category now this if I drag any one of these sales for example or any one of these other ones it’s gonna count now I’m gonna drag this sales back up here drag oops drag the sales back up there and put region in every case we’re gonna get the same count because what it’s doing is it’s counting the transactions all right I’m gonna format this design so in tabular and I’m gonna leave that right here and now we’re gonna do a second pivot table and this one will do on the same sheet and then compare and contrast alright I’m gonna go back to my data set alt in VT and instead of enter I’m gonna click on existing click on this location here click on sheet 1 on 5 we need to change the name of that and I’m gonna click write write a couple to the the right of that and click OK now I’m going to drag the cost of goods sold down to the row labels right click group start at and I’m gonna say zero all the way up to a thousand and I’m gonna group the increment will be a hundred and click OK notice the difference in the labels 0 to 100 100 to 200 200 to 300 now the trick is is that it well I mean the problem is it could be ambiguous where does a hundred fit does it hit there or there well pivot tables will always include the upper value but not the lower vise so for this category right here exactly 100 doesn’t fit here it fits here it’s always the upper number that is included in the in the category the lower number is not if you know how to use the frequency function that works the same way as a pivot table now I can simply drag any again I drag sales there I get a count of 80 from 0 to 100 if I drag any other one I get the same count so that is a useful type of pivot table when you want to group by a particular number and then count now let’s make a chart and we’re going to talk about charts later in this video but let’s just see how amazingly easy it is and the fact that the chart is a type of pivot table – it’s actually a pivot chart I’m just going to click on the charting button you could just as easily go over to insert and click on any one of those I’m gonna click on pivot chart it looks just like the regular insert chart I’m gonna click column and click OK it puts the chart right on this sheet and it’s actually has some ability for filtering just like it would in in the pivot table it puts the labels here the column Heights there you can clean this up I’m going to click delete I’m going to click the delete count of product all

time let’s do previous and now it shows us moving forward the difference between February and January was down about 30,000 the difference between February and March was about up 9600 finally you could do this as a percentage also show values as percentage difference from I’m going to say date and then the base item let’s do previous and there we go so for each month we are down from January to February down by 17% February to March up by about 7% so all sorts of amazing show values ass now one last calculating table I’m going to copy no I’m just going to create a new pivot table and put it on that sheet alt + V T I’m gonna say existing sheet click in this location click background up oh forgot to name it I’m gonna click right here now you got to be careful if you put it right here then the it’ll interfere with this pivot table because it needs room for that report filter so I’m going to click a few below this is dangerous because if you ever pivot this one up here and it starts to throw lots of data below it’ll give you a warning but it’ll say you know do you want me to destroy the data below so you’ve got to be careful when putting multiple pivot tables on the same sheet all right let’s just do a simple on region but watch this I want the average the sum and the count so I’m going to drag sales one two and three actually will key so each one of these is count by default so I’m gonna right click summarize value by that will be sum right click summarize value by that’ll be average right click summarize value by instead of counter I’m gonna show Max and there we have it now we could clean this up with some labels and some formatting the bummer is is that you can’t format the whole all the values at once you have to do it one two three times open up that number dialog box alright I’m going to call this pivot count all right now a couple last things we want to see about pivot tables now notice we’ve already grouped by month so if I alt NVT enter and drag the date it is gonna be grouped the reason why is 2007 2010 there’s a single what’s called cash so the original data is stored in a single cash in memory so it remembers that there the date has been grouped if you ungroup it here it’ll ungroup it on all the other sheets so I’m gonna right click delete that sheet we can go back to the old three step wizard now in earlier versions 2003 and before when you built an individual pivottables it would create a new cache the reason why they changed is because it takes up a lot of memory right so if we want to create a new pivot table that and not have the dates group we have to invoke that old pivot table three-step wizard now you can add a button to the quat or you could use the keyboard alt D P that’s D for data menu P for pivot tables already alt D P and there it is the old three step wizard all right so we have Excel list pivot table I’m going to click Next that’s the data set I’m gonna click Next if you click yes you will save memory if you click no two reports will be the two reports will be separate that’s what I want I’m gonna click note on a new sheet click finish now when I drag the date down here yes now I can do whatever I want I could put the sales over here there I have a daily sales summary I’m gonna say new cash now I’m going to go back to the pivot table data set and we want to see how to do two last things if you have a empty cell in a number column you can still do pivots but watch what happens alt NVT enter if I drag the date down here notice now it’s group because I’m working off of the 2007 2010 cash if I drag sales down here it should be some because a number should default to some