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

Just another WordPress site

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

welcome to that EWTN accounting excel seminar in Seattle now this video here is gonna be an epic video this is for conference in Seattle it’s gonna be like between an hour and two hours long here are the topics over on this sheet we’ll look at the subtopics a couple things before we start this video one is if you want to download this workbook and follow along and practice with the video click on the link below the videos not only that but because it’s such a long video here’s the nine main topics over on the sub topic sheet here are the sub topics you wanna click the show more button below because there will be hyperlinks to the different parts of the video you click on the time hyperlink and it will jump so if you want to talk about percentage number format show more click on the time link now there’s gonna be this is a huge video we’re gonna cover keyboards number formatting efficient formula creation look at all those topics these topics will be below in the show more lookup formulas a huge section on pivot tables – recorded macros and a bunch of things on charts alright so you can download the file you can click the show more and go to that hyperlink and we’re using Excel 2010 so we’re gonna start with our first topic keyboard so I’m gonna click on the sheet’ call keyboards now of course if you don’t know how to be efficient with formulas you don’t know how to create pivot tables and other Excel features then you know you’re you’re gonna have a hard time working and using Excel ah but what’s the most important thing to become inefficient after you have some basic foundation knowledge keyboard shortcuts now let’s look at a few here I’m just going to show you a few throughout the videos I’ll be using keyboards what you want to do is you want to take the time and teach yourself keyboards because man they’re fast let’s look at our first keyboard if I’m just looking this data set and I don’t know how many records are there there are well I could Joe I could scroll down and look for the bottom right but no you don’t want to scroll down let’s do control arrow control arrow jumps to the bottom of the current region it keeps going until it sees a blank cell and stops if you control arrow now and there’s nothing it’ll go to the bottom another really helpful keyboard shortcut is control home you’re working in the spreadsheet you’re a couple hundred rows down you’re like I just want to get to the top control home always goes to a one now another useful keyboard is let’s say we want to highlight all of this range here if I have one side of the range selected before I click on this next range if I hold this shift key boom it highlights everything between touch the first part before touching the last part hold shift and boom you highlight the whole thing and then you do something like add a number formatting I could go up to the home and then apply either accounting or currency now wait a second I just have my ribbons collapsed because it gives me more room how did I do that you could right-click on minimize in 2010 or there’s a keyboard ctrl f1 is a toggle ctrl f1 toggles on and off the ribbon tabs now we could get the currency there not only that we could use the keyboard and it’s right here control one control one that opens the format cells dialog box because so much of what you do in the spreadsheet is cells this is a powerful dialog box and we’ll look at a bunch of options throughout the videos this is number formatting so I’m gonna click on currency or accounting now the nice thing about using this dialog box is then you have control you could say I don’t want to see decimals I’m gonna click escape so it doesn’t apply can you believe it there’s a keyboard shortcut for currency and I use this all the time because I have huge data sets and I need a quick currency format control shift for absolutely amazing now let’s talk about not click shift click but some much more useful what if I wanted to highlight this whole column and add a number format this is huge right I could do this manually but not this one well guess what we can combine control down arrow control home with the shift trick so watches control downer would jump to the bottom but if I hold shift boom instantly the whole column is highlighted control shift forward to add currency unfortunately there isn’t an accounting keyboard for the accounting

number format now I’m going to control f1 see in accounting what is the difference currency has this floating dollar sign accounting has the dollar sign fixed on the outside people like accounting number format because it lines up the decimals but a lot of time these dollar signs here you don’t want them whoa check this out this is two thousand seven ten and thirteen County number format is right there it’s not currency that’s a County and look at this in earlier versions this has always been here but it was called the comma style but guess what it’s really if you read this screen tip right here it’s really accounting without the dollar sign people like that comma because it lines everything up no dollar sign now notice that we have some decimal show in here there’s no problem we can come up and use our decrease decimal button here but I want to show you the quat this is the quick access toolbar and if you look at your computers probably doesn’t look the same as mine because I have altered mine you can simply right-click customize quick access toolbar and the trick is once you get to this options this is the the options area it’s automatically on quick access you come up to choose commands from and you want to say all I wish that was the default instead of popular right so all then there’s all like 1000 plus things you can do in Excel now you can add any button you can go through this list and say add originally I had undo redo safe there’s keyboards for those so I got rid of home by saying remove notice I’ve added a few here I’m gonna click OK what’s so cool about that is watch this we got to talk about the alt keyboard shortcuts now in earlier versions when you hit the Alt key it underlined menus but since 2007 they show you these screen tips now we’ll show you especially when we do pivot tables is a great keyboard shortcut that we derive from using the alt keys but notice up here on the quack whatever buttons you put first second third and whatever order you put there’s a one so I’ve hit alt if I tap one now it will apply this I’m gonna click escape so really after you get the hang you teach yourself your alt keyboard shortcuts for here I’m gonna Alt one alt one just like that it decreases the decimal now that’s your sort of personal alt keyboards because each computer can have a different order to the clap buttons here one two three now the alt keys for a pivot table will be the same on each II so here just to show you how awesome these alt keyboards are here’s a data set field names at the top records and rows let’s hit the Alt key which one of these would we use to get data well you’d think that the pivot table would be there uh but it’s not they put it on insert you got a tap N and look at that it opens up that ribbon tab so to get to pivot tables you hit V and then to table you hit T and then it would ask you so if you do pivot tables all the time you teach you yourself that alt keyboard the reason they designed alt to show up with little screen tips it says so that each person in their daily activities if they’re always doing area charts they’ll teach themselves the keyboard for that if you’re always doing pivot tables you’ll learn alt n VT all right now we want to talk about the sum function the sum function is one of over 400 function it’s the only one with a keyboard shortcut hey why cuz it’s used so often now let’s go to the home ribbon and just take take a second since everybody if you want to be efficient if you use Excel you should know this keyboard but if you don’t remember it no problem a lot of the elements and the ribbon like hover over B it says control B and hey that’s one of the oldest keyboard shortcuts ever but hover over the autosum so if you forget the keyboard is what alt plus equals you ready alt equals now the dancing ants are dancing around they’re dancing so that if it didn’t guess right it’s as if Excel is being played saying hey I know I might not have guessed the right range you can redirect it either with your mouse or with your arrow keys as long as the dancing ants are still dancing you can still redirect so I’m going to click right there and watch this we’re gonna use ctrl shift down arrow not for formatting the cells but to control shift down arrow insert a range into a formula look at that b2 to be 1,001 now if I hit enter it goes down one cell I’m gonna show you an alternative here Watch alt equals now I’m gonna use my arrow key left arrow right arrow and then just

just to get the cell started in b2 and then ctrl shift down arrow instead of using Enter I’m gonna use shift enter shift enter puts the thing in the cell and jumps the cursor up now let’s try this down here alt equals that puts the sum function in instead of hitting Enter and pushing my cursor down because I immediately want to format the cell after I enter the formula I’m gonna use the keyboard ctrl enter ctrl enter puts the thing in the cell keeps the cell selected again the idea is most people do enter and then click back or arrow back and that’s an extra click that you don’t need now we immediately want to add double line accounting so I’m going to ctrl one to open format cells on the font tab I’m gonna go to underline and there we have single or double accounting underline and then I click ok that’s different than the border up here this would put double line for the whole cell this just does the number alright keyboards absolutely fundamental to being efficient in Excel our next topic is number formatting let’s go click on the number formatting sheet it wait a second I make sure there’s a keyboard shortcut to jump from this sheet to keyboards to number formatting there is if you’re going to the right ctrl page down jumps to the next sheet now watch this control page down I’m just page down when it gets to some sheets that there are some sheets that are not showing they’re under the scroll bar it will not only activate the next sheet but it will expose sheets not visible absolutely amazing now to go to the left control page up it’s a great keyboard circuit especially when you have a huge workbook filled with mini sheets now keyboards are really fundamental to becoming efficient a topic that many people are not aware of is number form and now lots of people you know on the planet earth everyone uses Excel and many different endeavors and people know how to use formulas and pivot tables but a lot of times number formatting will trip them up but here’s the deal there’s only one concept you need to get and then you understand number for many it’s a facade number forming like accounting currency percentage it’s just something that sits on top it’s like painting the house or putting a Halloween mask on the surface can be different than underneath now let me delete these if I come here in alt equals tab tab notice I put something in the cell and I was immediately going to the right so I used tab alt equals now what’s going on here I can clearly add this in my head these should equal 100 these should equal a hundred well what we see on the surface of the spreadsheets and can sometimes be different than what’s actually in the cell I’m going to highlight these and I’m going to notice that up here on the quat i have increase as the I can button summon alt – – and there we go we can see that number formatting something as simple as decreasing the decimal was showing us something different on the surface of the spreadsheet that number four nine point five is what was sitting in the cell down here – alt two – now I’m gonna actually highlight all this in alt one-one notice I’m seeing a 50 that’s the surface that’s the facade you can look for some with some number formats you can look up into the formula bar and see what’s actually in the cell absolutely important if you’re going to be able to track down errors and not get tricked by number formatting and one of the most important types of number formatting to understand is date number formatting now let’s learn an important keyboard shortcut if you’re hard coding dates into Excel say you’re doing transactions instead of typing out today’s date how about the keyboard control semicolon I absolutely love that now let’s do that again control semicolon I’m going to ctrl enter now look there that’s what it looks like I see on the surface of the spreadsheet and eight a slash of 26 2013 and if you look up to the formula bar hey it looks like that’s what’s in the cell but now the trick is is we can wipe away all number formatting apply general and you’ll see what’s in the cell what for 1519 you’ve gotta be kidding me what’s and I’m going to ctrl Z because this is so important to know about general number formatting especially if you’re working with dates and times because number formatting gets applied when you don’t like it the keyboard

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

formatting now we want to go to our next sheet I’m gonna use ctrl page down and we want to talk about formulas and then we’re gonna look at some lookup formulas on this sheet now we already alluded to excels golden rule and it is if a formula input data of any sort can change put in a cell and refer to it with the cell reference if it doesn’t change you can go ahead and hard-code it into the formula we saw an example earlier where we had times those could change so we use cell references and that 24 hours in our form though wasn’t a formula input that can change so we typed in it now this goes back to Bricklin and Frank’s ins original spreadsheet their idea was they didn’t want to have to re-enter a bunch of variables into a formula if only one of them changed so they thought they created the first spreadsheet busy calc that’s excels golden rule now in the book I wrote slain Excel dragons I have a huge chapter on formula and what I’ve done is I’ve kind of taken a bulleted list of all the ideas from there so if you want to read through this you can here’s all the type of things that can go into formulas those are formula elements we have our math operators our comparative operators different types of formula these are just the terms I view I use we’ll look at some examples of each one of these here is the order of precedence or this is how Excel calculates a formula which really comes in hand if you make complicated formulas that can help to shorten a lot of formulas or knowing this can help you track down errors we have number formatting we talked about that data alignment text is always to the left numbers are always to the right that is an important default setting which helps you track down errors like our numbers really being considered text logical formulas we’ll see an example the answer always gets spit out centered in the middle that is a default setting in Excel all right let’s look at some examples here first example just a simple example we have annual insurance and we want to calculate the monthly allocation well I’m going to say equals formula input that’s that’s a number that can change and I’m going to divide it by 12 that’s a formula input that probably is not going to change so it’s all okay to hard-code this in now I call this a calculating format it just means that gives you some number answer there is an equal sign cell reference math operator and a number that’s not going to change now let’s look at violation of our golden rule we want to calculate attacks so we say hey take this amount times 0.125 now a bunch of you should be laughing right because people all over the world since the beginning of spreadsheet time have been doing this why is it a bad idea because when you hit Enter I don’t see any indication of how I made that calculation even if you come back to it next week you know you’re looking at this pitch and you’re like ah I don’t know whether the cells are that have that the interest calculation or the whatever it is so you don’t want to hard-code those in a much better formula of course is hey let’s take our amount times our rate not only that not only do you want to actually put it in a cell you want to label it also so when you come back later or someone using the spreadsheet they know what’s going on now there’s an amazing link over here there’s been research done on this topic if you go read this link here it’s called it’s an article the risk of spreadsheet errors they found it to be the number one contributor to spreadsheet mistakes and faulty spreadsheets I know for my own experience it is just havoc when you have to deal with a spreadsheet where you hard code variables into your formula where they really should be put on the face of the spreadsheet and labeled properly here’s an even better example right we want to figure out the net cash in so we including tax considerations so we have our original principal times the the interest rate and then we want to consider taxes so we take one minus the tax rate that will give us our net cash in from a particular interest revenue now of course I have some examples down here this is just not efficient to actually type those in and this is even worse right because so you’ve combined if you have point zero 375 you come back later and you’re looking at you don’t know what the tax rate is you don’t know what the interest rate is you want to if you’re building a spreadsheet label formula inputs use cell references alright let’s go look at our next

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

like count if some if countifs with an S some ifs average if you can combine right in your formula now if you know anything about formulas and you use some product to do a rate calculations this is not how you do it for those type of formulas but for the count if and the formulas M that’s how you have to combine a comparative operator and a number formula input or you actually can have other types of inputs too that will give us the same thing now why do we have to do it that way well I have a formula over here you know what I’m gonna show you the clear so here’s home there’s the clear I want to get rid of all the formatting because it’s hard to read that formula there clear the eraser does everything actual content either formula or data and the formatting for clear formatting does just the formatting and clear contents that’s like the Delete key so I’m gonna say clear formats and if I put this in edit mode I can see I’ve joined some text and what’s ever in that cell what’s nice about this and I’m gonna control Z to put that formatting is if I come here and type 475 enter our text formula updates are calculating formula updates so that’s awesome if we had a comparative operator there this formula wouldn’t work out so well alright let’s look at our next formula example we are gonna look at the sum ifs function now in 2007 and later there’s some ifs countifs and average ifs they all make calculations with one or more condition now let’s do a former here we want to add from this column based on two conditions I’m going to type equals s um now notice there’s this drop-down we’ve seen it a few times already but I haven’t mentioned it you can arrow through this and once you find a function you want you can hit tab now let’s just look at some if this has been around forever it only at adds with one condition notice the screen tip range and some range I’m gonna backspace down arrow and hit tab and compare that this is a screen tip and the actual descriptions and the screen tips are much more explicit some range criteria range it’s clear which is which so not only has the screen tip more polite but we can do one or more conditions so I tend to use some ifs all the time now for one or two or three conditions now the sum range I simply highlight the range there comma criteria range now this is an criteria which means both conditions must be true you can enter them into the sum ifs function in any order you want I’m going to say the sales rep column for criteria range come and then the criteria for that column criteria two will be product that’s the whole range comma and then the criteria will be criteria two control enter and there we go if we change this of course it will update there aren’t Celia did not sell any product twos let’s look at our next example hey we’re going to use the sum ifs again but we want to talk about between criteria here’s a column of dates and we want to count between a lower and upper date so I’m gonna come here and use some ifs the sum range hey we’re counting units and guess what the criteria range one and two will both be dates comma and the criteria now notice what we did here I actually typed this out greater than or equal to nine / twenty – so this is text but the sum ifs will properly interpret that as greater than or equal to that date that is not a serial number sitting in the cell it’s actually text that’ll be our first criteria criteria range two is the date and there is our upper value now notice we’ve included an equal sign there’s no single greater than or equal to or less than or equal there’s two characters in both of those cases when you’re doing between criteria you’ve got to be careful sometimes you want to include the upper and lower other times you want to only include one either the lower or the upper all right control enter and that will add between those two dates if you have actual serial numbers then you have to do the construction where we use comparative operators and join them to our criteria all right so our criteria range Bowl time is going to be dates and watch this for our criteria I’m going to say greater than or equal – all in double quotes and then join it this is a serial number so up here some ifs properly interpreted this text here it will properly interpret the serial number join to the text now watch this here’s a great trick if you have a

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

order in which these are orientated has to be the same as the assumption area but that’s a relative cell reference control-enter double click and send it down when i F to the last cell boom one locked one apps relative this is absolute this is not but what that means is for forming a creation time you then have to come over here F 4 times this and oh that means I have to do it five times you know four big budgets with 12 months I want to be able to do one formula and copy it down and over for a whole year if I can choose or create one formula in one cell and copy it throughout the range I can do it 12 times faster than someone that only knows relative and absolute now this is a hard topic I’ve been teaching this a long time so it just takes practice but here we go I’m just gonna build the basic formula alright so I have these two cell references right here now the way I do it is I ask a question not just one but two questions of each cell reference because here’s what we’re doing our goal is to copy the formula down and over when I copy it down I’m copying it across the rows when I’m copying it to the side I’m copying it the columns so I’m gonna ask two questions because there’s only two directions in Excel up and down across the rows and left and right across the columns so I’m gonna ask a question when I copy this formula down do I need it locked on D 226 the answer is yes so then I go through the copy motion I am copying down across the rows or numbers that immediately tells me that the dollar sign should be in front of the number now the f4 key if you hit it one time it puts in two but hit it again it puts in oh just the dollar sign up from the row in fact it’s a toggle or a merry-go-round key as I keep hitting f4 it just merry-go-rounds through all of the four major types of cell references so I’m gonna stop with only the dollar sign in front of the number that means when I copy it down across the numbers it’s locked on to 26 now the second question is what I copy it to the side do I want it locked or do I want relative well think about this when this gets over to this cell should it still be locked on January or should it move to February it should move to February so no dollar sign in front of the D same here two questions when I copy D 233 down do I want it to move relatively yes I’m copying down across the number so no dollar sign in front of that number now when I copy to the side across the letters do I want the D to move to e and then to F no I don’t need to put a dollar sign in front of the column reference to lock it down only one we’re going across the column so I hit the f4 key until I only see a dollar sign in front of the letter and not the number that is the formula mix cell references this one has only the row lock but not the column this one has only the column but not the row control enter copy it down now this is a trick to you can’t copy it control-z you can’t go like this you can’t reuse your fill handle or angry rabbit to copy it in two directions you actually have to cop one let go and then copy it in another control Z Z I’m going to delete it another way to do this if you highlight all the cells that are gonna get the one formula and then the active cell you build your formula equals up arrow f4 f4 times down down down f4 f4 f4 now I have my formula in the active cell to populate it into all the cells I use ctrl + Enter now when you copy a formula to a range like this you always want to go to the diagonally furthest one away from the active cell and hit the f2 key and admire to see if you have the right cell references in our case we’re going to admire that is amazing it got exactly the right cell references now there is a keyboard if you control enter that’s called the active cell control period moves the active cell from corner to corner so I can use control period period and then f2 to check it by the way that control period is great for huge datasets when you need to you know go around the edges alright let’s look at some lookup formulas next alright let’s scroll down our first example will involve the lookup function vlookup now lookup functions and

formulas are usually taught and in advanced Excel class but they’re not that hard let’s look at a simple example we’ve all done we’ve all done lookup some time in our life while they’re looking up an employee’s name to get a telephone number or a product name to get a price or a tax table or something like that in this case in this cell we’re gonna have to look up this product for find it in the first column then that will determine the rut which row in the table we’re then going to jump over get this thirty bucks and bring it back to the cell that’s fundamentally what lookup functions do they look something up retrieve something from a table or column all right so the function we’re going to look at is vlookup I’m going to type VL I immediately see in a drop-down so I hit tab now the V means vertical because the table is orientated vertically lookup it means we’re doing some lookup now the screen tips are just like we do it by hand right lookup value that’s just the thing we need to look at remember in our brain comma the table well we need to know where the table is now this table first column has the thing we’re going to actually match we’re getting a matched product full that tells us row for the second third fourth fifth or sometimes there’s big tables like employee data there may be many columns in our situation we just have to column table comma column index number that is which column right there has the thing we want to get and bring back to the cell well for us it’s one two so you simply type a two here if this was a tax table and our table had five columns and the tax rate was in the fourth column you’d put a four there comma now you have approximate max that’s for income taxes and commissions and things like that or exact match when you’re looking up text or tables that are not sorted you use exact match now this table is technically sorted but we want to exactly find P ro space for so we’re gonna use exact match now if this table was always sorted and you wanted to find exactly product space for you could use approximate match and in fact if it’s you have lots of lookups and the tables are huge it’s advantageous to sort your first column and use approximate match but in most cases you don’t want to risk it you want to just look up exactly product for and use false now another thing false also can be represented with a zero and true can be represented with a one so I tend to use a zero instead of false when I’m using exact match that also matches some of the other lookup functions that require you use a zero for false or exact match all right so vlookup that’s what it looks like the home it got the 30 if I change this to 2 it goes over it finds the 2 jumps over the second column gets it and brings it back to the cell now what if you’re a bad type or like I am and you type no space the vlookup function and the other lookup functions are polite it says hey it’s not available hey I couldn’t find that in my list so what we would like instead of having to type a space as we’d like to avoid that in the first place so we can add something called data validation list now the great thing about beat vlookup is we already have the table here that lists says exactly what things should be allowed in that cell in that case we can simply highlight the cell go to data and then data validation and data validation now you could learn the keyboard alt keyboard shortcut in this version and it’s a good keyboard circuit but if you are a keyboard junky the keyboard shortcut from earlier versions of excel is shorter than the alt keyboard you would use in 2007 or 10 it’s alt dl LD l now the default is allow any value we don’t want to allow any value we want a list now there’s a bunch of other cool things that data validation can do but in this case we want a list there’s the source we simply click in this text box and then highlight our range now notice there’s an insult drop-down that’ll be awesome and convenient you could put an input message which means as soon as you selected the cell a message which of you could even put an error alert type whatever you want here and there and then if you type something incorrect this will pop up in the dialog box you can even have have a little fun with that I’m gonna click OK so now look at this if I select product 3 and instantly

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

other ways to solve this sometimes people put extra columns alright that’s a lot about form doesn’t look up so now let’s go over and talk about pivot tables let’s click on the sheet PT data Oh pivot tables the most powerful feature in Excel and guess what the rumor for pivot tables through the history of pivot tables and even back to Lotus improv is that they’re hard to create and I’m here to tell you they are not hard at all a few simple tips and you will see that pivot tables are like the easiest thing and the most powerful thing in Excel now before we can look at pivot tables you first got to understand that the data that you’re going to use for a pivot table has to be in a certain format you have to have field names at the top and records in rows now the field names don’t necessarily need to be at the top meaning the first row here but they do have to be at the top of each column you also have to have empty cells all the way around the data set control-z or the column headers of the row headers the big mistake people will make is you’ll have a note with some note here right next to your data set notice how it’s touching one of the cells in the data set or as we often like to do we make our calculations off to the side some calculation you cannot do that I’m going to ctrl escape and the control Z you got to have empty cells all the way around or column headers or row headers you also don’t want to obviously have an empty column or row you do want to try and avoid empty cells although you can definitely make a pivot table with empty cells we’ll look at a couple problems that that will cause and you definitely want don’t want to have empty field names so if you get a data set without a field name put a field name some name that describes what the data in this column is all right now let’s learn the most important tip for seeing that pivot to creating pivot tables is really quite easy the trick is to visualize the report in advance before you start creating your pivot table here’s a table we’ve all seen tables like this it’s a cross tabulated table here are sales rep names here are the regions an intersecting cell is in our case we’re adding it is adding with one two conditions these are called row headers these are called column headers so for a cross tabulated report we’re adding we’re going to add on the units column this number 1344 was calculated looking for fam in the midwest so if we look at our our records here fam in the midwest there are two it went through for the region column and anytime it found a midwest and then in the sales rep column it found a fam it used the units in calculating that number i know went through the entire data set anytime i found both of those it used it in that calculation alright so what’s so important about visualizing in in advance these are row headers at the head of this row is fam at the head of this column at the top of this column is midwest these this came from the region column this came from the sales rep column so when we go to create our pivot table and this is what the end result will be we simply are going to take there’ll be a list of field names we’re gonna put region as column header sales rep as row header and the units will go in the calculating area or the values area all right let’s do it let’s see a pivot table i’m gonna click in a single cell on making sure that there’s empty cells all the way all the way around in fact control down arrow right okay i’m good control home any single cell will work now I’m gonna go to insert pivot table pivot table I’m gonna use my keyboard shortcut from now on alt n V T we learned that earlier in the keyboard section now this dialogue replaced earlier dialogues that had three steps its guessing and it will always guess right if you have blanks all the way around and column headers and or column headers or row headers so it’ll always guess right we don’t have an external source so we’re not gonna click there we do want it on a new sheet we’ll see later how you can put it on an existing sheet you simply click here you got to be careful

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

select whichever element in the pivot table I would like now you can read through this list and format whatever you want I tend to be a minimalist I’m gonna select header row that’s this right here click the format and I have font border and fill I’m gonna do a dark fill I do a dark fill you gotta have a light font click OK scroll down there’s a bunch of great things column subheadings row subheadings I’m just gonna go right to the bottom that’s the very bottom here grand total say format and let’s do some borders a thick dark line at the top double line at the bottom click OK if you don’t like your formatting you can click clear and start over there’s a preview here I’m gonna click OK set as default pivot table quick style for this document I like that but now I’m not going to do that I’m gonna click OK I’m gonna call this my style too since I already have a my style in there alright click OK now I can simply come up here oh look there’s my first dollar it’s the same I’m gonna click this my style if you hover you’ll see the screen tip my style – and instantly I have created my own style I’m not limited to these built-in styles now two more things about a pivot tail the first is hey why do they call it a pivot table in the first place anyway hey your boss comes and sees the sales rep sitting in the row headers and they’re like those are supposed to be in the column no problem you can pivot the report you simply click and drag the fee and drag it to whichever section you want no notice down in the row I see that gray bar I can drag it below or above if I drag it above I get two fields dropped here and look at that sometimes this is just the report you want of course the boss doesn’t want it this way but I got to show you one other thing about this particular view here there’s these collapse buttons so you can collapse and see not only the individual detail for each employee but you can collapse them and see just the east total you could also right-click expand or collapse and here it allows you to expand the entire field or collapse the entire field so just like that you can pivot your report expand an entire field alright so the boss doesn’t want that either but that’s pretty cool pivoting now I’m going to drag the sales rep up to the column labels and boom those the boss has exactly the report they want now what if in this case we’re adding right we’re adding on the units column based on two conditions what if I wanted to change the calculation to average or count I could simply come up to options field settings I could right click and summarize values by or I could right-click and we saw this before the value field settings here you can change the name the function the number format so I’m going to change it to count and just like that boom I’ve changed the calculation all right I’m gonna ctrl Z and leave it a sum all right let’s go to our actual data sheet I’m going to click on this PT down and we’re going to create our second pivot table now this pivot table we want to make a calculation based on three conditions and we want to see how to do filtering now as always it’s helpful to visualize your report first so we would like to have region in the row labels and product so two fields in the row headers or row labels and then we want our sales rep over as the column headers or column labels I’m going to click in a single cell on my data set and use our keyboard oldt NVT enter alright here’s our field list simply take our region and drag it down to row our product and drag it down to row sales rep drag it over to column instantly it’s looking just like we visualized now we’re gonna make a calculation upon sale so I’m going to drag that to the values area alright now let’s do some formatting I want some sort of number formatting so I’m going to right click value field settings number format these are this is a sales field so I’m gonna show it in some currency I don’t need to see any decimal so I’m gonna show zero decimals click OK click OK immediately go to design report layout and show in tabular we want to see those field names now filtering there’s two ways we can filter one is we can filter upon the fields they have this drop-down it’s amazing now I’m gonna scoot this over to the side click

on region actually before we filter it let’s look over here see the totals what we’d like to see is I don’t want to see all of the reasons I just want the top to sew filters have two couple parts you can sort you could actually manually uncheck and check to filter there’s label filters for the filters and value filters these will filter the values and no way down at the bottom top ten I don’t want top ten so I’m going to only show the top two click OK and just like that that’s amazing I filtered my pivot table now I’m gonna unfilter clear that’s an excellent way to go with filtering but there’s an another amazing filter here report filter now right now we’re um these are adding sales based on three conditions but watch this I’m going to take the customer and drop it down to the report filter immediately it will add something above the pivot table that will filter the whole pivot table I’m gonna drop it there instantly it shows up here click this drop down I can select any one of these when I select BBT it will add a fourth condition click ok this pivot table is four conditions the so for this value right here it’s salesperson francs product sunshine East and sold to BB t customer I can click this and say all you can also select multiple items and then there’s a checklist and you can check uncheck all and check whichever ones you want now this is I’m going to unselect this this is a report filter in just a moment I will show you a slicer and the slicer is much easier than this but there’s a great advantage when you have your filter here let’s say your boss comes in and says I would like you to create there let’s say there’s 20 customers I want you to create a pivot table for each customer that means you have 20 sheets I’m gonna click OK here let’s name this this is not a good name I’m gonna call this second pivot just second pivot enter so I typed it and hit enter now again the boss wants 20 individual pivot tables one for each customer there’s an amazing feature and it’s been around a long time in pivot tables I’m gonna go in 2010 2 options and then 2 options and show report filter pages and earlier versions it used to be called show page filters now I’m going to click this and we only have one report filter we could have dropped multiple filters down here with that selected I’m going to click OK but before I click OK I’m even going to drag it down here instantly 20 sheets will show up in front or to the left of this sheet watch this I’m going to click OK and watch Daniel instantly we have an individual pivot table for each customer I’m using ctrl page down apps with even the customer name on the sheet absolutely amazing now you can see why they call it the most our feature because you know with a couple clicks and then knowing where that options for report filter is wow that is amazing now let’s go back here and look at a slicer now I’m gonna have my cursor in the pivot table I want to go to options and slicer now here I’m gonna say insert slicer I can choose this is going to do the same thing as the report filter here I’m going to say a customer same as I did over there the only advantage when I click OK is it adds a much more friendly user interface now we can come up here to options and I can say columns and increase it to save for columns expand this out or however big I’d like it I could add some formatting absolutely amazing now what this does is it will filter it without having to use this if I click on FM instantly it’ll PCC instantly it will show PCC now your the selection tricks that we learned earlier with our keyboard and mouse apply here so here I want to go from ast to e PP I’m gonna hold shift and then click and it instantly it highlights all of those if you want to highlight ones that are not next to each other or non-contiguous you simply use your ctrl key and click on whichever ones you want and instantly

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

right and whatever you do if we were to drag region to the report filter instantly that the pivot table updates but so does the chart if you go ahead and select east and click OK instantly the pivot table and the chart update they are linked all right so we grouped by date we grouped by integer we group by decimal we even saw with one click that you can create a chart linked to the pivot table now I’m going to call this group number pivot enter now we want to go back and talk and go back to our pivot table data sheet click on PT data now we want to create a new pivot table on initiative we want to look at some different calculations possible with the pivot table I’m gonna click in a single cell alt NVT enter now I want to create a basic pivot table and show you how to copy it a bunch of times and then change the calculations let’s go ahead and use date we have our dates already grouped and then I’m gonna say sales drag it to Vail values now I’m going to do a little formatting let’s see right click value field settings number I’m going to show currency these this is the sales column 0 decimals click OK click OK to OK’s there we definitely don’t like that row labels there so I’m going to go to design and show in tabular form all right I’m gonna change this to some right-click summarize value by and some all right now we have this table here I’m gonna highlight this ctrl C click ctrl C and then I’m going to come over here and ctrl C alright so let’s look at some of the different calculating capabilities now I’m going to turn those dancing ants off with escape the first one is running total I’m gonna right-click show values as a bunch of amazing calculations let’s look at running total in this is great it asks us all ponder which feel well we want a running total through the month so it’s the date field click OK and just like that it shows you the accumulation of total sales through each month there’s the total at the end of all the months now I’m going to copy this control-c control-v actually we want to put a I’m just gonna put running T here I want to change it to percent of running total right click show values as and percent running total and based on the date all right so it shows us our percentage moving forward now let’s change this also to percent running T you could put a better label there now let’s look at our next example I want to add another field so I’m going to take the region and drop it down to column and what I’d like to do is create percentages so I want all these numbers as a percentage of the grand total then I want to see how to do each individual January amount based on these conditions up here region as a percentage of the total for January and then we’ll do the same thing for the column percentage of each of the East numbers for each month as a percentage of the total East three different calculations so I’m simply going to right click some are show values as and we have percentage of grand total again that’ll be the percentage of that over there instantly percentage of grand total you can always tell the base because it’ll be shown as a hundred percent how about right-click show values as percentage of column total each individual element as a percentage of the column total and then there’s percentage of row total absolutely amazing I’m glad there’s a pivot table to make all these calculations a lot harder to do with the formula now let’s look at difference from oftentimes in accounting you want to see the difference from month to month as a number or a percentage right click show values as and difference from now it’ll ask us what’s the base the date and it will ask the base item now for us I want to see the difference of each one of these items as compared to just January so we’ll start with this one then you click OK each one of these is the difference April compared to January May compared to January right click show values as difference from again but this

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

but when you have spaces in your number category it defaults to count now it’s easy enough to change you right-click and change it to some a more troubling problem I’m actually going to empty in number let’s go back to our data set the more troubling problem is if you have a empty cell in your date column now I’m not going to do this but if you open this up as a new pivot table and try to group you will get a message that says you cannot group further if you have dates entered as text so this would be 6/19 slash 13 right it may you know look similar but that’s text empty cells or text in your date column you cannot group as a month so anytime you get the message cannot group this field in its dates almost certainly it’s empty cells or dates entered as text all right that was a lot of stuff about pivot tables now our next topic is recorded macros now we’re gonna click on the sheet m11 now what is a macro macro means that you’re typing out code you can actually not use the features in Excel but type out code and get Excel to execute some command now I’m not a code writer but luckily for those of us that are not code writers and actually the code inside of Excel is called VBA it’s Visual Basic application for those of us that don’t know how to write out that code luckily there is a macro recorder and here’s what it can benefit us say we have a report like this and we get it each month it’s got different numbers but we do the same formatting the same add an extra column with a formula if there’s a repetitive task we can actually turn on the macro recorder and as we do things like open up format cells out of formula and copy it down it will write the code for us now if you really want to automate things you need to learn how to write you VBA code because the macro recorded quarter is very limited but we’ll see a couple good uses for the macro recorder now there’s a couple things we have to do first first thing is you have to show the developer ribbon so right click customize ribbon and then over in the left you can check developer number two it is you can’t have the file extension dot XLS so I’m actually going to save as and change it to dot XLS M so I’m going to hit f12 that’s a keyboard shortcut for save as notice down here it says xlsx Excel workbook but we want to click on this and use the M dot XLS M the M obviously means mackerel Excel macro enabled workbook and then we can click save so now that we’ve converted this to a dot XLS M and we’ve showed the developer ribbon we can go ahead and talk about recording a macro let’s first take a look at the developer ribbon over here in the code section record macro we’ll click that when we want to record this use relative reference this is very important if we if it’s not yellow orange that means when we record our macro and we click in a cell that means that it clicked in b2 if I select this range it will select exactly b2 to be 11 if we use relative reference then if our cursors here and we click in d3 it doesn’t really click in d3 it’s recording a relative movement meaning wherever the active cell was it goes one two to the right or adds to column if I were here and relative was on it would go one two we will use that in the next example here we’re going to keep it absolutely this report we get it it’s the same size it’s always in the same set of cells from a1 to c11 so we can use absolute macro security if we click here you get to choose your security level in general you want to disable all macros with notification that means you get a little yellow button it says do you want to enable macros there’s disabled macros except for digitally signed disable all macros if you’re only working with your own stuff and nobody else’s workbooks maybe you want to select this one enable all macros but in general this is the one to select I’m gonna click okay this is our list of macros notice

there’s a keyboard shortcut there alt f8 there’s also the visual basic editor we’ll see that later that’s alt f11 all right now we’re gonna record a an absolute macro so it actually doesn’t matter where our cursor starts again our report is always going to be in the same set of cells now we’re going to turn the macro recorder on do what we want to this report and then we’ll test it on these other sheets to see if it can automate the process all right you ready I’m gonna click record macro now very importantly we want to give it a good name I put format static report because it will always be in the same range you can assign a keyboard which is kind of cool we’re not going to do that for this one you can store your macro in this workbook or in your personal workbook for this first one I’m going to save it in the personal workbook that means store it in personal macro workbook it is available universally to you on this computer now personal macro workbook is a hidden workbook on this particular computer and we’ll look at where it’s hidden and how to open it after we record our macro now we want to create a description alright so we give it some sort of description now when I click OK anything we do including mistakes is recorded in the macro alright I’m gonna click OK the very first thing I’m going to do is I’m going to select a 1/2 D 1 or D 11 and I’m gonna go up to the home and select all borders now I’m going to click in highlight the range b2 to c11 control 1 and add some sort of formatting like number with a comma zero decimals click OK this is all being recorded by this macro then I’m going to add a new column change and then I’m going to add a formula equals one cell to my left – two cells to my left control-enter and then I’m gonna copy it down all right when I copy that down it copied from the copy to the range D – 2 D 11 now this will only work because our report is always the same number of rows and our next macro will see a way to trick the macro recorder using relative references all right so I’ve added all my formatting I want to end the cursor I want it always to be an a.1 so I’m going to end in a1 now I can turn the macro recorder off right here it says top recording or there’s a button down in the status bar I’m gonna click stop now let’s go look at what got recorded now we could use macros alt f8 or even Visual Basic alt F 11 but I’m going to use the keyboard sugar – alt f8 and I have a bunch of macros here the one we just recorded is right here XLS B format static report in the personal now that’s a hidden workbook if I click a run it will work because it can run from the hidden workbook but what I really want to do is I want to go look at the code I want to click Edit if I click Edit it’s polite it says hey this is a hidden workbook you have to go unhide it first so I’m going to click escape escape and lo and behold on the View tab we can go over to hide and unhide yes this is hide and unhide for a workbook so I’m gonna click unhide and all the hidden workbooks will show up I’m gonna unhide this click OK here’s that personal XLS B now I can alt F 8 and I’m gonna select my format static report notice it just shows the name of the macro because I’m actually in that workbook I’m gonna click edit Wow look at that let’s see if I can get this whoops down on the screen this is the VBA editor we have our VBA project view here there’s the workbook the workbook we’re working in is over here I can open this up if I had macros in in this one we will later there’s all the sheets I’m gonna go ahead and close that over here this is the personal what just happened as we inserted Mazda I already had one in this on this module to the code shows up over here let’s just look at this code range select a 1 to D 11 dot select now that is an absolute reference that means no Matt when ever you run this it’s always going to select a 1 to D 11 and then there’s a bunch of code notice

the macro recorder didn’t record record efficient code it recorded all sorts of things just to do that border and then select the range b2 to c11 it added the number format it added this formula cell references are in a completely different form than you’re used to it’s not the a1 b1 form down here we have a fill destination again it’s hard-coded in so that formula got hard-coded and we’ll see how to trick that instead of using range we’ll do something out and then the last little bit range a1 dot suck so that’s the code that got recorded now I’m gonna close this this is a separate window separate from the Excel window we just went and looked at it right now I’m gonna go ahead and hide this again alright now we’re back here now let’s go test it i’m gonna click over here remember it doesn’t matter where our cursor starts sometimes it does matter and we’ll see an example of that later alt f8 I’m gonna click on my personal format static report and click run you’ve got to be kidding me look at that is that totally beautiful I’m gonna come over here now I want to alt f8 if you ever wanted to create a keyboard you could come here to options and oops that’s the wrong one this one here click click on options and you could assign a keyboard there’s the information we entered in at the beginning alright and then we could run it one more time but I’m not going to bother we’re going to go over to this next sheet m1 for now here’s the situation m1 for m1 v m16 we’re getting completely different heights to our report so now we’re gonna have to use relative reference we can actually switch back and forth between relative and absolute references while we’re recording our macro alright now we’re gonna start with absolute because the very first move is going to be select a1 and then we’re gonna do a special keyboard shortcut that highlights the current range see if we want like this during our macro it would highlight a1 to c5 but if you use the keyboard shortcut ctrl asterisks and I’m gonna use the asterisks on the number pad or if you’re going to use the asterisks on the number 8 use control shift 8 control asterisks the the difference because notice ctrl a does that to control a is a completely different line of code than control asterisks and control asterisks is what will get us to highlight a whole the whole range no matter how tall it is control asterisks I’m doing that on each one all right and then we’re gonna have to be tricky when we create our label we can do an absolute reference even when we create our formula and enter we can enter it into d2 because it’ll always be a formula in d2 but when we go to copy it down we’re gonna have to trick it and here’s how we’re gonna do it we’re gonna absolutely click one over and then we’re going to use the keyboard shortcut ctrl down arrow now ctrl down or didn’t select c5 it went to the bottom of the current region and then we’re gonna click relative reference so when I click here it’s not clicking d5 it’s going offset please go from the active cell and add one column then we will control shift up arrow which is another trick that doesn’t highlight the range it highlights a relative range and will paste the formula all right so we’re the idea here is we’re gonna use relative and absolute references to get a variable height report to get to format it alright you ready we’re gonna go ahead and record our macro this is gonna be format report variable height I am gonna assign a keyboard just to show you how that works I’m going to hold shift and then F so it will be ctrl shift F and I am saving this in this workbook now of course you might want to save this in your personal if it’s going to use this all the time and then we’re gonna write a description all right so we added a description there alright when we click OK it’s gonna record even if we make a mistake all right here we go click OK now I’m absolutely I’m actually not going to click in a1 I’m going to start here and create the formula first so I’m absolutely gonna click in d1 I’m going to type X the formula is gonna say rain select range d1 and put this the text change in it

enter now I’m gonna do my formula equals one cell to my left – two cells to my left when I ctrl enter it puts the formula in d2 but now watch this I’m gonna copy it when I select c2 it’s recording absolutely but now I’m going to trick it I’m gonna go ctrl down out it says go down to the bottom of the current range now because this could be in any different cell in this column see I’m going to switch over to relative references now when I click in d5 it’s not really d5 it said please offset from the active cell and add one column now this I’m use ctrl shift up arrow and then control V I’m gonna hit escape to turn off the dancing ants now I’m absolutely gonna click in so I’m gonna turn this off and I’m gonna click in b2 and watch this I want to format this whole text here so again I’m going to use a keyboard shortcut to trick it ctrl shift arrow will go however far over until it hits an empty cell and then control shift down arrow that goes down as far it needs to until it runs into an empty row and now I’m gonna control one number formatting number separator zero decimals and then click OK what I want to do now is add the borders and we’re gonna use that keyboard trigger and this is actually the active cells so we’re gonna use control asterisks then we’re gonna add our borders and if we’re still in absolute mode so right here I’m gonna click in cell a1 or wherever I want it maybe I wanted it over here always in two columns over so I’m gonna click there now I’m ready to stop so I’m gonna click on this button here or this one up here all right here’s the moment of truth let’s go over and even before we look at the code let’s see if this works I’m going to click on this sheet and our keyboard shortcut was ctrl shift F you gotta be kidding me look at that amazing let’s try it here ctrl shift F oh it even did a big column look at that now let’s go look at the code alt f8 I’m gonna find my format variable height and click Edit if you wanted to look what the options where you could see or you forget the keyboard shortcut you look there I’m gonna click Edit now it brings me right to the module notice that this module has been inserted inside this is a big welcome inside this workbook right here that’s how it works all right so there’s the module and the first part was absolute so range d1 select even this one there’s our formula we copied it we selected c2 now look at this selection dot end down dot select that’s go to the bottom of the current region and there it is the active cell dot offset row comes first then column it added one column now this little bit of code here comes from the macro recorder it doesn’t really mean anything we went over and then we did our keyboard selection dot end up dot X and paste it absolutely amazing then we selected b2 absolutely that’s where we turned the absolute reference back on and we did our range dot selection range selection this exhale to the right Excel down that’s when we control shift arrow absolutely amazing that’s a way of tricking the macro into a variable height and even at the end we selected absolutely f1 I’m going to close this right here so very important concept being able to use relative reference and absolute now we have there’s some notes right here but we want to look at one more macro now this macro isn’t formatting reports but it is going to take advantage of knowing how to use relative reference here we get a data set and the record is set up vertically and we need to have it in a proper data set so we got this data dump and all we did is we added the field names up here now let’s step through what we’re going to do and when you’re recording a macro especially if you’re learning for the first time actually when you’re learning for the first time the most important thing is you record your macro it you get an e run it you get an arrow air alt f8 and then delete I did that so many times over the years I learned to lead it and start over that’s also why I had a number of sheets for us here so when you get this workbook you can practice because you may try it the first time

and mess up always copy your data before you run a macro on it right alright so the idea here is we need to copy this and this mackerel is going to require that we start the macro in the first cell in the record we want to copy and transpose right so we can use with that cell selected control shift down arrow control see now this is the active cell before I do anything I want to turn on relative reference and remember then from this cell it’s going to offset one because right now we’ll go from in a two but later when we have lots of records it’s going to be an A three a four a five so then I click there I’ll do something like right-click transpose now the active cell is still of air I offset an add one row I’m gonna highlight all the way down so the code will say highlight the entire row offset this many right-click delete and then I’m going to end the macro by selecting the first item that way we can run the macro over and over and it will always transpose the record correctly so I’m going to ctrl Z ctrl Z escape alright let’s try this we’re going to start this with they use relative reference on we’re not even going to use the absolute a button here so the whole way through it will be relative alright record macro transpose records and it definitely add a keyboard shortcut shift G I’m going to save it in this workbook this is at one time mackerel I’m gonna add a description alright there’s our description alright when I click OK this thing is running all right you ready control shift down arrow control see now notice active cell is right there the relative is turned on so when I take my mouse and select or simply do up arrow it it did offset minus one row now I’m instead of pasting I’m going to right-click paste special transpose the active cell is right there I’m going to then when I click on number 3 it says offset add one row and go all the way down to row 11 right click delete I’m gonna make sure there’s the active cell to offset an add one more row and now I’m gonna turn off the macro stop recording now watch this let’s try our keyboard ctrl shift G ctrl shift G ctrl shift G boom boom boom boom boom boom boom absolutely amazing now let’s try it over here control shift Jeep Boop why did that happen because the correct cell was not selected I’m not going to click debug I’m gonna click end escape that’s why you copy the data set oh that was scary now I’m gonna go over that that one is rec now I’m going to click here click in the correct cell before starting my macro and then control shift Gee Gee Gee Gee Gee Gee Gee Gee Gee Gee wow that is amazing alright that was just a little bit about recorded macros the main ideas that we learned for us non code writers that we can do basic repetitive tasks record a macro and then it’s easy to do that repetitive tasks and we saw how to use absolute and relative those are the movements of your selection cursor alright now we want to talk about our last topic we’re going to go over to the chart types sheet click on chart types now we want to talk about charts we’re gonna start off by looking at the different chart types now up here we have a data set and look at this it’s already summarized so oftentimes you either with formulas or pivot tables you create your data set now there’s lots of different data sets here’s a common one row headers column headers now we take this data when do we use a pie chart it’s very simple when we’re comparing the parts to the whole or we want percentages in this case we have kites toys and boomerangs so we have these numbers here these are the the labels or categories and here are the numbers but we highlight those are the parts they make up the whole that’s when you use a pie chart chart junk that is a phrase made famous by Edward Tufte he wrote a bunch of books on charting this is just chart junk why excel even has the exploded pie as an option is beyond me first off the most critical thing about this is any time you have a pie chart there are percentages and when you tilt it on its side you distort the percentages this formatting is ridiculous we also have unnecessary repetition you would not believe how many charts you will see out there even

in board meetings boomerangs boomerangs you don’t need it twice alright chart junk we don’t want chart junk column charts very simple we have some categories along the horizontal axis and we have columns columns are vertical there are numbers on the vertical axis the height of each column tells us what that particular number is we’re comparing across categories now we have a legend up here that tells us blue is toys oranges boomerangs and gray is kite a bar chart and oftentimes column and bar the terms column and bar are used interchangeably but they’re not columns are vertical in excel bars are horizontal now the only difference between these two charts is that bars city and horizontally sometime emphasize the differences more effectively than a column chart here is a stacked column and it is great because what it did is it took these three different bars notice this is about ten thousand this is about four thousand and this is a little bit extra here it just added them all and showed them proportionally as a total so this is exactly about fourteen something so each one of these shows to the three categories but also visually shows the total in that March is much bigger than February or January line charts and XY scatters one of the biggest mistakes in excel line charts XY scatter what you want to remember is that lines represent a single number XY scatters have two numbers now here’s what I mean all line charts have categories equidistance of along the horizontal axis and then the up-and-down of the line is the number on the vertical axis there’s only one number even when you have years along here and we’ll see this example later their equal distance one number for line charts XY scatter for any one of these particular dots out a certain distance up a certain distance so this is an XY scatter our study for a test probably can predict the final score on some sort of test the more hours the higher the score on the test again it’s a certain distance along the horizontal or x axis and then a certain distance up or down along the vertical or y axis two numbers finally we can have some variation this is a column chart but the bars are touching because the categories extend from 40 to 49 then 50 to 51 so there’s nothing in between so we visually portray that with a no gap all right now let’s go look at some examples we’ll click on the sheet’ column all right we’re gonna start our set of examples of how to create charts by looking at a column chart now here’s a cross tabulated table usually these are created with pivot tables or formulas and this the column chart is particularly well-suited for this cross tabulation now let’s highlight this not the totals we don’t need the totals just the column headers and the row headers and now let’s go to insert column and then I’m gonna do the column by the way we talked about chart junk our combs and pyramids chart junk you bet they are there’s no real good reason to be articulating the height of a particular item with a cone or a pyramid it’s charged up well except for if you’re a cone factory or a pyramid Factory then it would be perfect all right I’m gonna use this column but I’m going to teach a keyboard shortcut so I click escape the keyboard for a column chart on this sheet is ulta f1 the keyboard for a chart on a new sheet is f Allah now it’s really the default chart not a column chart it just happens that most computers have column as their default now on the next example I’ll show you how to change that default alright you’re ready I’m gonna do alt f1 beautiful just like that it comes out actually kind of nice now let’s talk about this how did the charting engine decide to put the months on the horizontal axis as categories and the row headers in the legend it goes like this if the number of column headers are equal to or greater than row headers it will show up just like this column headers horizontal axis now if I’m down here if the column headers are less than the row headers then it will be the reverse I’m going to alt f1 and we could see here it chose the row headers to go on the horizontal axis all right I’m

gonna scoot this over to the side we’re going to talk about this one another important aspect and actually have some notes up here when you get to advanced chart and you really got a dissect and know what excel names all the numbers now up here because it took the row header so I’m just up this data and put it over here it took the row headers and put them in the legend that means these numbers will be called toys these numbers will be called boomerangs these numbers would be called kites there’s some notes right there let’s explicitly look inside the chart and again for some advanced charts this is the real trick we’re gonna go to the design and select data this is the power because you can do what you can edit any particular series of numbers you want you can add you can remove you can also edit two categories on the horizontal axis now let’s just click on one it has toys I’m gonna click Edit and sure enough it took the name boom toys and here are what are called the series values again the reason this is important because for some charts when you highlight the data it just comes out all but no problem you have the power to edit add remove and edit the labels I’m gonna click escape alright now I’d like to scroll down and look at the same example but we’re gonna format the chart I’m gonna highlight this and hey this time just to show you I didn’t show you let’s do f11 notice it puts it adds a new sheet I’m gonna call this column chart 1 no I just I forgot to show you that so that’s kind of cool if you’re gonna print it out on 8 by 11 it prints out perfectly on a page but now I’m gonna go back here and do the keyboard alt f1 now let’s improve this chart it would be nice to see a chart title at the top so we’re gonna go to the layout chart ribbon and this group is very important labels there’s all sorts of great things we can do I’m going to go to chart title above chart now by default when it comes out this is this chart title and notice it’s got a solid line check this out you can just start typing so I’m gonna take summer sales what wait a second I started typing where did it go it’s up in the formula bar hit enter and there it goes now it would also be nice or let me show you one thing but before I show you how to link the labels to the cells a lot of times people come in here and highlight and type that’s fine too that dashed line will say hey you’re adding in at live so I could type something like into the wind and then there you go but let’s look at one advantage if you have the chart title as a solid line you could come up to the formula bar if there’s text you highlight a lot oftentimes there’s no text I’m gonna highlight this and type an equal sign whoa wait a second that means I can link this with a formula to the cell so I’m gonna click on a 28 and enter now it’s linked now let’s look at this everything is linked I’m going to change this to 1900 notice the bar shut up but also the access change I’m going to control Z if I were to change all of this formatting control shift tilde to go back to general look what happens to these numbers control Z now there actually is a setting where you can either link or D link the the number formatting to the chart I’m gonna click on the axis and when selecting elements sometimes it can be tricky one way to avoid the trickiness is to go up to the layout over to current selection and there’s a whole list of all the elements and you could select whatever you want this way it’s got vertical access now when you select an item you can format it and there’s a few dialog boxes you have to wade through but the majority of all of the elements allow you to use the keyboard shortcut control 1 to format now I’m going to control 1 and there I get the format axis dialog box now remember control 1 is format cell so that keyboard works in the cells and in the charts now I’m going to go over to number and there it is that little text box there allows you to either link or not link to the source data because you can come here and do all sorts of wild things I’m gonna click close alright let’s click on the legend control 1 and you can move the legend move it to the

top move it to the bottom into the wind sails so we’ll assume that from this title we can understand that these are cells these are sales in dollars with month names you really don’t need a label down there if it’s ambiguous what they are then you can add a axis title and we’ll do that in later videos alright let’s go to our next example I want to go actually one more thing here you know I kind of think this is busy then what’s the keyboard for delete delete alright let’s go to our next example I’m gonna scroll over a little bit here templates and to fall now here I’ve created a black and white chart it’s a pie chart let’s say I do this all the time and not only would I like to save it as a template so when I go up to insert chart it’s just sitting there as an option but I’d like to set it as the default so once we have a chart we’d like to save as a template it’s very easy we go up to design save as template when we click on this button a save as dialog box comes up here’s the pathway to the default chart folder and look at this chart dot CRT X now I’m gonna call this something more useful than chart how about BW pi so then I’m gonna click and it’s being saved in the templates folder when I click Save now watch this when I highlight this and go up to insert well wait a second I don’t see it there no problem I can click any one of these but this will get me to the dialog box and there it is up at the top there’s templates now I have two of them one is chart one and here’s the one I want black and look pi so now I have this as an option when I click this and click OK there we have selected it now let’s set this as the default chart that means our keyboards will work to create this black and white pie I’m gonna go up to insert click on the charts dialog launcher I’m going to go up to templates I’m gonna select they want to know I want and there it is by the way in this version it’s a lot easier some of the earlier versions of Excel was a little bit more complicated to set a chart as default so now I’m gonna click that boop-boop click OK and now watch this I’m going to highlight this and use alt f1 no way saving as a template setting as default really useful tricks if you’re using a lot of charts now let’s go over to the sheet copy a I’m gonna click and we have a chart here we want to copy this chart and then change the chart type so we want one column but we also want some other type of chart no problem don’t click in the inside because then it will copy just that particular element click on the outside and then our keyboards control-c click in a Cell control-v will work now columns show differences across categories let’s change it to a bar I’m going to remove those lines delete right-click over in the white if your right click here this is a great mistake I should show you if you accidentally right-click the columns and say change series chart type it’ll change just that one series and if I change this one to say a bar you’ve got to be kidding me it can do two at once but that’s not what we want control Z you got a right click and when you see change chart type you know you’re on the right path now I can simply change it to a bar and click OK what is going on here a couple ways we could deal with crowded numbers we could expand the size of the chart when you see your horizontal white arrow you could do that control Z you could click on this access control 1 and change it to some other format currency in fact we could do something tricky these are in thousands we could come down here to custom and right here we can put pound and comma comma says remove three zeros or a thousand and then put space K now in order to put text into number formatting you usually have to put it in double quotes but K is the one thing that number formatting understands I’m going to click Add there it is I’m going to click close and no way there’s a kid whoa wait a second we don’t want that control 1 I’m going to change that so the custom number formatting should have three sections one for positive 1 for negative 1 for 0 semicolon separates the sections then I’m going to put – tirean comma space K

semicolon and then a 0 right so that will show up properly on our chart No it doesn’t say linked to source data I’m overriding that I’m gonna click Add and then close and so now I fixed it lots of ways to fix that we could have removed the decimals removed the dollar sign all right now let’s copy this whole chart ctrl C and we want to look at a stacked bar so I’m gonna right click change chart type it’s over on bar but nominee is this one the difference between these two we’ll look at I like this stack bar because it retains the actual height which then you can compare the totals for the categories listed on the vertical axis so right here we can clearly compare March February and January and then within each we have our legend items now I’m going to copy this control-c control-v and now what if I change this I basically never use this chart I always think it’s a little bit confusing but sometimes there’s a good use for now I’m going to select that notice it’s stack 100% stack bar and it changes the access here completely indicating that these are no longer numbers these are percentages but sometimes it gets confusing because then it looks like that are all the same you know the power of a chart is that it’s visually articulating the numbers in a way where you can quickly understand and and this has the potential especially if you’re not looking down here to say oh look they’re all the same all right now those were some different bar charts we saw out a copy let’s go over to line I’m going to click on the line now line charts are important we have some categories one two three listed on the as row labels and the years listed along the column header so I’m going to highlight this whole range here and go up to insert line lines will show us one number on the vertical axis so I’m going to select and what it totally misinterpreted what I want it put years here this is where you have to know how to go back to design lack data this is the real power of knowing how to make charts I’m simply going to come down to product in here that’s the blue one right there and delete or remove now I’m going to click Edit because it went by some default one two three and now I’m allowed to highlight my years so I’ve restructured my chart click OK knowing how to edit the series the actual numbers edit or delete or remove and the categories horizontal axis very important I’m gonna click OK we have our three lines that we can compare over time again there’s only one number here these were equidistance now we probably want to add a title to this I’m going to go to layout chart title above chart and I’m going to just type I want to say sales by year notice I started typing because it was a solid line it appeared up here when I hit enter a boolean and that will cover it we don’t need to put it in here because that will tell us that the sales we can figure out this as a year maybe we want to move this I’m just going to control one see what it looks like at the bottom okay I kind of like that there alright let’s move this over to the side now notice what happened we had some text there if you don’t have any text right here which sometimes is okay and sometimes is not watch what happens if I go up and do my insert line select this one so it was that text there that caused the line chart to misinterpret it now let’s go over to this next sheet number formatting we’ve already talked about this a little bit but I want to show you here and I can already see there’s going to be a problem if I go up we have our years here and this is a typical data set field names right so we have our year here when we insert a line chart we’re going to get that same problem or you we could fix it but it would be just as easy to delete it and now when we highlight this same data set and go up to line it will interpret it correctly now I’d like to do something with this axis here we talked about whoops now I tried to move the chart and I accidentally selected highlighted rangefinder color-coded range I’m going to control Z luckily there’s an undo now I’m going to try and point over here now what I like to do is format this in the cells we saw this earlier here is that

code I’m going to control one and actually in the format cells dialog box for the sails I’m gonna say pound comma space K semicolon – pound space K and semicolon zero again I if I don’t put a zero even though we don’t have any zeros here then the chart will show something funny so I put that there this is custom number formatting the cells I’m gonna click OK and sure enough it shows it’s sucking it from the cells and we could further improve this by adding a title I’m not going to just in case you’re interested pound comma comma each one of those comma removes three zeros or a thousand this would be how you’d have to show a 1 million as one M notice because it’s not a K we had to put it in double quotes alright now let’s go talk about XY scatter click on XY before we talk about XY scatter let’s review line chart one number XY scatter two numbers a line chart one number up and down vertical axis and then there’s equidistance categories along the horizontal axis line charts are great for showing trends over categories totally different chart here there are two numbers some number along the X or horizontal axis out or in plus or minus and then second number along the vertical axis up or down so we don’t in this case out a certain distance up a certain distance and put a marker ok and XY scatter shows the relationship between two numbers now we’re going to see two examples two types of XY scatter when to use the dots and when to use a line all right let’s scroll down here here is a data set I’m going to zoom in a little bit I’m gonna hold my control and roll the wheel on my mouse that keyboard is great because it works everywhere excel word websites alright so we have our studied and test score so we’re wondering if there is a relationship between these two numbers X always comes first then you put your Y the X is supposed to be the predictor variable yes you’d think since we’ve all studied and take taken tests the longer we study hopefully the higher the score that means there’d be a direct relationship as this our study goes up the scores on our test will go up now X has come first and it’s helpful to have labels at the top remember the charts will name a series of numbers all right so I’m going to highlight I’m going to go up to insert and under the scatter there’s either the scatter dots or these lines these lines are meaningless when you’re actually collecting data this is collected data we did a test we collected a random sample you use the scatter dots when you have sample data or population down for that matter lines are only when you have models like a fixed cost net income model which we’ll look at next all right so I’m going to click on this and instantly it shows us a bunch of markers that indicate out a certain much this is our study this is test now this is an example of where a chart really has to have horizontal and vertical labels on the axes now I’m going to click here go up to the formula bar type in equal sign and link it to that cell right there so we got this now font if you control one this just Madden’s me there’s no font here why didn’t they include it there now you could right click thought you know what I’m just gonna go up to the home cuz the fault will work straight from this I’m gonna say sure ten all right I don’t need this at all I’m gonna use my favorite key keyboard shortcut besides ctrl z delete I’m gonna get rid of these lines right here delete now I want to go up to layout axis titles and I’ll go do the horizontal first title below I’m immediately going to link this not just the title but the axis labels equal sign and this is X I love it look at that makes it a lot easier than having to type out now I’m going to go back up to axis titles and now I want rotated click click in the formula bar equal sign and click on my test score – y alright so there I have my labels this video is counting seminar it’s probably you guys aren’t probably doing linear algebra or regression or anything like that but you can show the line and you you know if you you know how to do that

it takes a while and it’s easy in Excel if you know all the functions pull watch this you can right click these dots and go to add trendline and just like that you say what type of regression type linear you can even come down here show the equation and r-squared I’m gonna click close and just like that it’s right on top and the way so I’m going to point to the edge when I see that solid line I’m gonna click and drag alright so what does the XY scatter show probably shows a or tries to show a relationship it looks like there probably is some relationship the more our study the higher the score if you get dots all over the place that means there probably isn’t a relationship between the two variables let’s quickly look at another example because they don’t always come out positive like this meaning as x increases Y probably increases it also can have the opposite an inverse relationship as x increases Y decreases so as number of police in small towns increase the crimes have some relationship probably looks like they decreased so let’s highlight it and go to insert and I love charts because what do they do they give you a quick visual it looks like as this one increases oh the Y decreases alright now let’s go over to the next sheet break-even analysis and click here is an example where you might use the XY scatter but you have a model that is uniformly predicting so here we can use our line now I’m not going to go into how we calculated but here’s our assumed units we simply took our units here times our sales price we calculated our total cost based on some assumptions down here we have our fixed cost and our net income now here’s the cool thing about XY scatter if you put your X first all of the Y’s notice X units is going to determine sales total cost it actually doesn’t determine fixed costs but we’ll plot it against it anyway X and one two three wise no way it will if you highlight and go to insert scatter and this is where we want a line it will plot it perfectly we’ve all seen this as in accounting you’re kidding me look at that just in just clicking that one chart and it looks just like all the textbooks or your models boom there we go now we could add labels there I’m not going to do that here we have one last example to this for charts of this epic epic video I’m going to click on two chart types now here we could go ahead and plot this as a column chart we could see sales and expenses and that would be fine I’m going to I can’t alt f1 because I’ve changed my default so I’m going to go up to insert column and column I’m going to point to the edge here I should have showed you this a long time ago and click and drag to make it a little bit smaller now notice we have expenses and we saw a mistake earlier in our one of our charting examples where we must achingly changed one of the series to an incorrect chart type but in this case we want to change expenses to a line chart and what does a line do one number along the vertical categories equidistance along the horizontal it’ll work fine I’m gonna right click and it says change series chart type you could certainly go up to change chart type 2 it will assume that you want to do it just for that series I’m gonna click line and click OK and just like that we’ve combined with a nice little legend over here and we could certainly add a title if we wanted oh that was an epic video that’s the longest video I’ve ever done over two and a half hours all right we’ll see you next video