Excel Basics - Tutorial for Beginners - Microsoft Excel 2007, 2010, 2013, 2016 for beginners

hello everybody my name is time for galley welcome to the webinar on Excel basics today we're going to use Microsoft Excel 2016 I really show you how to get started with your Excel spreadsheet now the same kind of techniques will work in Excel 2013 and Excel 2010 so what I'm going to show you should work in just about any version of Excel now here we are in the Excel workbook and let's go over the screen here so in Excel 2007 they came out with this new menu structure so if I pick on the insert menu notice how the whole toolbar changes and if I took on page layout notice that the whole tool bar changes so that structure started in Excel 2007 you should have the same menus there now if you go to Microsoft it might call the menus tabs like it might said tick on the Home tab and they might call the toolbar the ribbon if you see the word ribbon anywhere in documentation it just is what we used to call the toolbar where all the icons are now if you look what my mouse is going to go notice that you have a couple icons in the very top of the screen that's called the quick access toolbar here I have seized and undo and redo now in this case you can add more icons to the quick access toolbar in a couple quick ways notice where I'm going to go by the way sometimes during this session I'll make my maps do that so you can see where I'm analysis I'm going to click on the arrow next to the quick access toolbar and then I could just pick any one of these so example yeah we're always adding a new documents what's going to add new and now you can see how the new icon is on the quick access toolbar so that's a really quick way if I click the arrow to the right of the quick access toolbar then this time it took in the word open and you can see the open icon is upset now another way to add things to the quick access toolbar is the following if you right click on any of your icons I'm going to right click on bold for example and then also you add the quick access toolbar and then you can see how that is up there now so I do use that a lot and it works the same way inward or in PowerPoint that's why your favorite icons are always available now let's go over some basic terms in your Excel spreadsheet all the individual blocks in the screen are called cells all right so each block do you see is called a cell when you have a bunch of cells together that's called a range of cells that's terms that you'll see all over the place one block is called a cell and a bunch of cells together is called a range of cells now if you look where my mouse is going to go it says a one that's called the name box it's going to tell you what subject area at the time let's go over some basic ways to move around your spreadsheet if you just move your mouse somewhere and then click then that becomes the current cell just move your mouse and click and that becomes the current cell so you can see now it says be eight in the mini bucks now another networks is the keyboard arrows so right now I'm going to use the left and right up and down keyboard arrows and you can see it's going to move one cell in that direction so if you see my my current cell moving around it's because i'm using the keyboard arrows now I'll do that a lot because I learned spreadsheets way back in the 80s on lotus 123 way way back and what I learned it you're going to have a mouse you have to use the keyboard so you'll see me move around like that a lot of this use my keyboard IRS now another one that works is page down if you notice right now I'm in cell e5 so if I hit page down it takes you one screen down now I'm in cell e37 and then e 69 now if I hit page up it up taking one screen up now back in a 37 and then e five so that's another quick way to move around now let's say I'm way over here somewhere and you'd like to return back to cell a1 so here's a keyboard shortcut for you hold down control and then on your keyboard look for the home key control home always take you back to sell 81 let me try that again yeah notice how control home always takes me back to cell a1 so i could be anywhere in the workbook quickly to return bit too so 81 is control home now notice how the rose go one two three four five they used to go down to 65,000 rose and you think that will be enough right well some people needed more room to nap so let's see how many roaster is now here's a keyboard shortcut for you I'll do ctrl down on your keyboard ctrl down now that's not a hundred thousand that's 1 million 48,000 okay so that should be more than enough room for you they really made a much larger now you know you'll never get up to that point but you know some people will go over a hundred thousand rows and then that you can do that now in this case I'm going to use the right arrow key to go to the right and watch my columns you can see I'm going to the right let's see what happens going to get to Z it starts the alphabet again at AAA and then a be a see it always did that it always with the column IE v which is 256 columns across let's see how far it goes across now here's another keyboard shortcut I'll do control right on my keyboard control right that takes you all the way to the right and notice now because of the xfd so that's something like 16,000 columns across and in million 48,000 rows down there should be enough room for you write that mean that's that's that's really massive weight a much bigger let's try the other keyboard shortcut again I let's go back to cell a1 remember what i did i do control home and there's no i'm back into cell a1 ok so those are all great ways to move around your spreadsheet now here's one more way sometimes you want to go to a specific cell so watch what I'll do we click on the name box and I'll type in j10 with no spaces j-10 and then hit the enter key and now you can see I'm it's LJ 10 so that's another quick way to move around just go to the name box and type in the cell you want i'm going to do control home again and now back to cell a1 now look at the bottom of the window over here it says sheet1 a sheet is just a different section of the file the whole file is called the workbook so maybe you've heard the word workbook and that's the entire file you would use a different sheet maybe four different month or a different year or a different department watch my mouse was going to go and I'm going to pin this plus sign right here and that would add a new sheet now have she to cheat three later i want to show you how to rename those sheets but it's really easy to add a new sheet the sheets they're also called worksheets they're just different parts of the workbook alright so now let's get started on our Excel workbook here now I'm going to go to sell see one column C whoa Row 1 and so it's called see one you can see in the name box and I'm just going to type in let's say company company budget so you go to your cell the type of what you want and then you hit the enter key and that's how you can get your spreadsheet started so now notice when I hit the enter key it takes you one cell down now I'm in cell c2 if I hit the tab key after i type of my entry it would go one cell to the right but a lot of times I hit the enter key and it'll go once fell down so that I can did a whole call at one time now i'm going to click on the word company budget and that is how it appears up here a second time that's called the formula bar now there's two reasons why you would use the form of the bar one maybe you want to change that cell notice how i I'm going to click on the formula bar and now I'm up there and I've just type in space 2017 so I'm going to hit the enter key and now that's been changed so to change a cell you're going to click on it and then you change it up here in the form of the bar now another way to change the cell is to double-click on it if I double click on cell c1 then you can see I have the car should bear that can change it that way also now if I pick themselves even now they sell nothing appears in the form of the bar so there's nothing really in cell b1 it's just that c1 overlaps in the d1 so as you're clicking around your spreadsheets if a cell looks like it has something but you click on it if there's nothing in the form of the bar that cell is your empty it's just at c1 overlaps in the cell d1 and that's how you can tell all right so down column a i'm going to type in some some department names maybe from your company so here i'll type in sales I'm just typing them in and I'll hit the enter key and then purchasing will have financed you can see when I hit the enter key go to the next cell down so I go to the whole column at one time and if you misspell something you can just fix it like I showed you buy those two is it should edit the cell so maybe we'll have accounting and maybe we'll have tea and we will have a warehouse so you can say get your yourself strategy started it's not difficult now I'm thinking ahead and maybe I like to have totals for that column as well so i'll just type in the word toes just as a hint if you have a more complicated Excel spreadsheet you want to plan it ahead of time and kind of think about what that spreadsheets going to do so the more complicated ones are definitely will spend some time and plan this now let's say I'm going to go ahead and type in one more let's say I want to have another row and now let me show you how to insert a new row you're going to right-click on the number i'm going to right click on number eight and then I'll take in the word insert and that'll insert a row as you can see so you're always adding rows in this case i'll type in research and development good now so to insert a row you're going to right-click on the number and you took in the word insert same thing with the column if you right click on the letter then you can pick them inserts there and that will insert a blank column see how everything moves to the right because insert of the blank column in the same way you can delete a column or delete a row i'm going to right click on that RC and i'll take on delete okay and now that Tilly it's a whole column now if you ever to meet the wrong thing if you follow my maps you can always undo right now the nice thing about undo is you can keep on undoing to the last time that you save your document so you know you can do undo multiple times until it's the way you want it back again if you undo too far you and you want to redo what your undid we also have really do alright so don't forget about undo and redo we use those all the time I notice how is able to insert that realm with the research and development now notice how research and development overlaps in the column B it even goes the column C let's see what we're going to do about that let me show you how to make the column bigger follow my mouth I'm going to go right between letter A and B see you get the black cross like that now you might be used to dragging that black cross back and forth and that always works but here's a quick way i'm going to double-click on the black cross double click and then that made that as big as it needs to so watch again you go you go between two columns up there and you get the black cross now see how I can drag the black cross back and forth that is one way a lot of people use that way but I like this way I'm going to get the black cross and then double click then it makes it just as big as it needs to so each column could have their own individual column width now let's go over the cell c3 and i'm going to type in the red january good now i can type in the rest of the must mean so here's a quick way this is called a speed so this is going to be another one that you use all the time so I'm going to tick in the word January now follow my mouse to the corner of that cell and you get a black cross that's to be right on the corner of the sub to get the black cross that's called the speed spell or the autofill now you can either drag it to the right or you can drag it down so I'm going to go ahead and drag it to the right and notice what happened it's filled in the rest of the month names for me watch that again I'm going to go ahead and undo that i typed in the word january i'm going to get the black cross right in the corner i'm going to hold down my left mouse button and drag it over as far as you want to go and then it's filled in the rest of the month now let me show you what else that I can work with now I'm going to come down here and i'm going to pick on sheet2 because maybe I don't want to ruin what I just did so everything I just said is on sheet 3 which is fine but now I'm on a different section of the workbook alone sheet 2 i'm going to come up here and i'll type in january and i'm going to click on the road january will get the black cross and we'll drag it down see how it works that way as well and it's just still don't over the months for me when it gets to December starts again with January let me show you what else that works with typical monday because it works with the days of the week so i'll click on the word monday get the black cross drag it down and now works that way as well notice how we move it to sunday it starts again with monday so these can really help you build your spreadsheet though the quicker this is called the speed fail or the autofill it also works with deep i'd like to have today's date moving forward now i can type in today's date but here's a quicker way there's a keyboard shortcut for that so if you ever won today's date hold down control on your keyboard and then do semicolon control semicolon will give us today's date and that is the date that I'm making this recording so watch again I did control hold down control and then do semicolon on your computer you'll get the current date now if it gives you a different date than what you're expecting it means your computer clock needs to be reset but usually control semicolon will give it give us to this date well if you like that one let me show you can get to the other current time hold down shift and then do control semicolon and notice how shift control semi call will give us the current time so control control assembly call will give us the current date shift control semicolon will give us the current time so those are real useful both of those are based on your computer clock now look what we'll do for the day we'll get the black cross drag it down and now we have the rest of the dates going forward really is it much quicker way to build your spreadsheet rather than have the type those in let me show you something special we can do it to deeds so this is actually a friday and the 25th and 26th our saturdays and sunday and I'm sure we have other side of this and some days in there as well what if we just want to show the weekdays only watch where my mouse is going to go when you do a speed so you get this little box at the bottom now for a long time I thought of that box was really annoying until somebody showed me was for so I'm going to click there and i'll pick on the word Phil weekdays and watch what happened it just took off this area's in the sunday so now it's just showing the weekdays only that was really handy so I started to sell with the today's date I got the black cross dragged it down now if I want to get rid of the weekend deeds click on that little box and I picking the word Phil weekdays and you can see that's exactly what happened I can do the same thing over here so if I pick in the word Monday get the black cross you see you have to did a speed so again to get that little box again so i'll click on the little box and i took on the still weekdays and notice how it took off the sides in this sunday's so that's really very helpful now it also works with numbers type in 1 i'm going to click on the one get the black cross drag it down whoops missed it get the black cross drag it down and now we have all won and that's not what i want to do here's a quick witasick set you click on that double box at the bottom of the speed fill and you tick on still series and now it'll say 1 2 3 4 5 right so that could be really going to be as well I was teaching this a seminar like this one time and somebody said can we make it be every Friday because maybe you run payroll and friday and you want this spreadsheet to reflect that so i'll type in today's day which is fri 22 24 / 2017 then I'll take the next Friday's date which is going to be mark starts reading / 23 / 2017 the type in the first 2 i'm going to highlight both cells and when I say highlight I'm going to use the white cross see the white cross I'm going to drag the white cross nut so you can have it more than one cell that's called a range of cells and i'll show you more examples of using the white cross but we'll get the black cross in the corner well drag it down and now i have every tribe after that right so I typed in the first two fridays i got the black cross in the corner i highlight those cells with the white cross then i got the black cross in the corner and dread it down now i have every friday after that and here's one more that comes with Excel you can use this to number any kind of series so let's say we have class one now that have been group one or person one or patient when it could be any word file by the number one get the black cross drag it down and then you can see how it numbered that series so those are all the ones that come with excel there's equal the speed fill or the autofill let's go about the sheet 1 actually it's interesting here I started on sheet 3 okay guy so there she thread now I'd like to save this that was a lot of work so far so i'll just click on the normal save did you used to now the first time you say something you want to give it a good name so i'll click on browse and I'll put it in my documents folder and i'll call it class 1 so you always want to give it a good name and notice how the name appears up here on the appears up here on the title bar at the top of the window so now here's going to be a really important part about this session i want to show you how to start to do math in your excel spreadsheet sometimes I see people using Excel and it did the mass in the calculator and then type in the number no you can do the math right here all right let me show you so now we're going to start to type in some numbers typing in numbers is just as easy as typing in the text or the dates so I'll just making up numbers here I've been 20,000 maybe purchasing gets 24,000 number how I'm just hitting the enter key so I can go to the next cell maybe they get 24th out 25,000 marketing will get 2,000 research and developed usually gets a lot of money will give them a 30,000 counting i'll give them 25,000 and it'syou gets a lot of my list custom 45 styles and I don't know why I t got more money than Anna maybe I'm partial to that I'm not really sure but I just worked out that way and then warehouse will get them 26 steps okay so typing your numbers is pretty easy as you can see now we can always get into formatting those numbers later on if you like so now clearly I want to get a total of that column let me show you a quick way to do this this is one that you're going to use all the time it's called the autosum so you click on the cell where you want your total tool PR and that's have been hundreds of numbers you click at the bottom of the column let's say now we're going to go with the home Manuel top home and you come over here and you look for this icon and then when you move your master most of the words some or in your case in way said their word although some are ready but you're looking for that icon right step I'm just going to click on it and usually it puts the proper formula in there for me let's take a closer look at that and notice how it says equals some c4 through c12 which is correct now that little colon the way you read that is it means as a range of cells and the way you read that is C 4 3 C 12 that is what I want to do is so I'm going to hit the enter key and now that's the mathematical total of that column while shall we did that again I'm going to now if you want to delete a cell sometimes I see people click on this out and then type in this space bar and that doesn't really make it essentially empty if you really want to make it officially under your hit the Delete key hey by deleting now it's officially empty so I'm going to click on the cell where I want my total to appear which is right here we'll pick on the home menu up top and we'll come over here and look at where my icon is looks like that little grease out of E and then I'm going to go ahead and click that and notice how that usually will put the proper formula in there for me and i'll hit the enter key and now it has the the sum for that column that's called the autosum it's going to be one that you use all the time now notice up in the formula bar it says equal some c4 through 12 so I can change the formula up on the form of the bar and the result of the formula will show up here in the spreadsheet now if I change any of those numbers I'm going to change this to say 23,000 watch the bottom number it's going to recalculate as you see so if you see if you set up your formulas properly you should be able to change your numbers and the form the tool will recalculate automatically that's the way to excel is designed now let's see some more about math okay the autosum is great but of course you want to do other mass beyond just normal edition so let's see how we're going to do other math first of all let me save I'm saving all the time because I've been burned too many times by a computer so I'm saving all the time that's a good idea now there is an autosave in Excel and I even have it turned on but I still save even with you author said I Justin burned too many times so now I'm going to go to a different sheet in this case i'll pick on sheet 1 and i'm going to write down an important statement all formulas in excel start with an equal fun all formulas in Excel start with an equal sign right now I just put the period error to end that sentence but the formula would not continue to appear it you'll just have the equal sign so let's see an example of that which had been equals 56 plus 98 now when you're typing in your formulas they always start with your equal sign and usually they do not have spaces once in a while they'll be a formula that will have a space for a specific reason but as a general rule this deformers do not have spaces so I want to hit the enter key right there and it's going to do the math for me now if I click on that cell then notice how the formula is up in the form of the bar and it did the math from it let's try a subtraction I'll say equals 98 minus fifty three and then when we hit the enter key does the math if I click on that cell then you see the formula upset in the formula bar now i'm going to write down a chart here these are the mathematical formulas that use all the time or most of the time and give you some examples of these so sometimes we use parentheses i'm going to show you why we use parentheses in a few minutes now some of you might have to do an exponent what i mean by an exponent is like 2 to the third power will be an exponent the way you get to it is you use this little up arrow I think the official name for that is called a carrot and you get that beginning a shift 6 on your keyboard so that's called an exponent and let's do an example of that so I'll say equals to this little up arrow you get by doing a shift 6 in your keyboard and then for that means take 2 to the fourth power and you can see it to the mast so some of you might have to do that multiplication in Excel is going to be the asterisk multiplication good so this should I wonder that will do equals the asterisk 6 b times six okay now division is going to be the little flash that's on the question mark ii you see the flashlight typed in for division so if a equals 90 9/11 good of course the plus sign would be for addition and of course the minus sign will be for subtraction so those are the mathematical operators use all the time now let's see some let's see an example of one that's going to have more than one of those so I'll say equals 4 plus 5 times 6 now if you didn't know what I was about to tell you you would think it would take the mass left to right like you would say 4 plus 5 is 9 times 6 is 54 you would think it's going to give this 54 if you didn't know it what is about the time but it doesn't say 54 it says 34 let me show you what happened it doesn't take the mass left to right it takes the mass in what we call the mathematical order and I have these written down in the mathematical water so it actually did be the multiplication first 5 times 6 and then it came back which is 30 and then it came back it adds 24 and that's how we got to be the 34 cut the mathematical order so they run for a long long time now if i want the addition to happen first watch what we'll do that's when we're going to use the parentheses so i'm going to put that within parentheses see the parentheses takes top priority so anything in parentheses is going to happen first therefore I'm kind of forcing the addition to happen first and now we have the 54 did you see the difference that the parentheses made so this is called the mathematical order now you would think okay well why do we need to know this this is the way that Excel does this mess whether you like it or not so you have to get used to it all right the way you control it is you put the parentheses around the part of the function that you want to go first now here is a way to remember it I learned this a long time ago and as I go around the country teaching these classes a lot of people in it this way as well so i'm going to write down the first letter of each of those words if you're looking column J it's the same letters that's the let's start with the words in column G right now there's another free so it starts with those letters that helps you remember the order and the phrase is maybe the show ring a bell for you from way way back please excuse my dear aunt Chelle please excuse my dear Aunt Sally now what possibly could hint shall we have to do with Excel well notice how those words start with these same letters and those are the same loads to start with these words it's a mnemonic device that helps you remember the order when you have longer calculations you'll be thankful for that it'll help you remember the mathematical order I please excuse my dear Aunt Sally so is people that use enough for years and years okay so that's going to be important to mathematical order now let me show you how to really do mass in Excel let's say I want to add those three numbers up now I just shows you the oldest um of course that's one way but let's try another way i'll show u equals 1 54 plus 45 plus 54 i can do it that way and that will give me the right answer and then if i click on that cell you can see how the numbers are hard-coded in there but here's the real way you should do math in Excel you always start with the equal sign now you click on the cell that has the number that you want after you type in the equal sign so I'm going to click on the 154 which is really sell be four plus the 45 which is really cell b5 + 254 so after you type in the equal sign then you click on the cell that has the number that you want to use and you tie things together with your mathematical operators notice how that one also says the 253 but here's the difference I'm going to change this number on the change into 187 and watch the bottom number the bottom number changed the top number I've stayed the same because those numbers are hard-coded into the formula to say the bottom one changed because that formula is pointing to those other cells so if there's other cells change then it will recalculate all right so that's really the best way to do your formulas now let's tie all of that together all flim does always start with an equal sign after you type in the equal sign you can either type in the number that you want or if the number is already on your spreadsheet somewhere you're going to click on that cell that has the number and then you're going to tie things together with your mathematical operator and in the mathematical order kicks in and the way to remember the mathematical order is please excuse my dear Aunt Sally that should be the basic premise to do any mathematical formula in Excel it was really important that you saw that you should that that's something that you really wanted to grasp because this comes individually is over and over again the mathematical formulas and then after that you do more complicated formulas using that premise now let's put that in the real life practice I'm going to go back to sheet 3 okay let's say January was actual numbers and let's say separate is going to be a ten percent increase over January well mathematically if you multiply any number by 1.1 it's going to give us a ten percent increase so i'm going to say equals i'm going to click the 23,000 which is really cell c4 x 1 point 1 that means take the number thats in cell c4 and multiply it by 1 point 1 and we get 25,000 300 okay think about that ten percent of the 23,000 is 2,300 add that again on to the 23,000 and then you get 25 3 that's really a ten percent increase over that number and notice the formulas of c-4 times 1.1 here's why I did it that way if I click on that cell and change it then this cell down here will change and then this one will also change because they're both pointing to cell c4 I'll type in 25,000 notice how this number changed and this number change so that's how you really want to build your formulas after you type in the equal sign you click on the cell or cells that have the numbers that you want to use you need type things together at the mass amount of operators so start with simple formulas as you're practicing and then you can build up to more complicated ones after that now let me show you a really quick way to build your spreadsheets this is called a relative formula now I can type in all the formulas down this column but here's a quick way remember how we use the black cross to drag to drag that what we can do this thing thing and it's going to copy that formula so it's going to make a change so I'm going to click on the get the black cross drag it down and then it's still then each of those now let's see what happened to the formula this one says see four times 1.1 this one says see five times 1.1 it made that adjustment automatically that's called a relative formula because it changes relative to the new cell and then the next one says c6 c7 i already had to type in the formula one time and then I was able to copy it right on down and it made those adjustments for me that's called a relative formula that's going to save you a lot of time when you see most people's larger spreadsheets it's hardly ever the case where they type in each form of the one by one whenever they can they do that they type in the top one get the black cross drag it down and then it makes those adjustments for us those are called relative formulas you're going to use that so many times now let's see even go a little bit further with that let's say march is a ten percent increase over februari April to ten percent increase over March so I'm going to take that singing formula and copy the cross as well so first of all notice how I have the white cross to the white cross that's how you're going to highlights themself so I'm just going to drag the white cross that's called a range of cells and that could be I could have dried the white culture cross or down as many as you wanted to it's called a range of cells and get more than one you get 2 black cross and I'll drag it over to the June column now that that time I missed it sometimes you now seems a little bit so i'll get the black cross and i'll drag it over good now let's see what happen to this formula this one says see four times 1.1 this one says d for justin says 'i for notice how it's always the cell to the left of the current cell the made those adjustments for me automatically that's called a relative formula it's going to save you a lot of time let's do the same thing down here remember how this one we did with that although some and I could give you all this sums on each of those other ones going across but here's a quicker way those Redknapp's is some c4 through C 12 and the four the bar will get the black cross drag it across now let's see what happens this one else is some D for this one else is some e for see how it made the adjustments for it once you once you learn that relative form of the concept you're going to use it over and over again it really saves you a lot of time and doesn't your spreadsheets now a lot of people are looking at the spreadsheet and they're thinking well you know look a whole lot nicer if everything had two decimal places so let's do that this is just going to be formatting so to format your cells you're going to highlight yourself and you can hunt as many as you wanted to so I'm going to click on this so over there with the white suit of white cross and we'll just drag it I'll just drag on my mouth now I know I'm going to have a couple more columns so I'll go a little further over good so you can hide at a big range now let me show you we can find your formatting features you go back to the home menu home now you see it out of your format into do you still and in Excel has a special format exception for numbers right over here so I'm going to click them to pull them where it says general and then you have some different number formats I'll pick on currency right there and just like that especially looks a million times better because now everything has a dollar sign a comment two decimal places so all you do is you highlight some numbers with the white cross and you come over here on the home toolbar and then you have your number formats right now now some of you who might have used Excel before might be familiar with something is called format cells I've been using that for a very long time and I still use it let me show you can get the format cells it's a vision more flexibility so i'm going to right click on one of those cells right click then you pick under words format cells right now now i've been using excel for so long i still use the screen to this day and it gives you some more ways to format notice how there's a number tab here and you see the same kind of number formats but here you can go a little bit further here i can pick different decimal places if you do business with countries outside of the united states you might have a different t symbol and then for your negative numbers you can put a minus sign or in red or in parenthesis or red and parentheses so check with your accounting department and asking how to want to see their negative numbers what's it going to tell you is they don't want to see negative numbers at all okay but when they do show up how do those show up so you have a lot more choices when you go to format cells as you can say click on ok so to format your numbers you're going to highlight yourself you'll find a lot of your formatting under your home menu especially over here here some number formatting and then another way is you right click on the cells after you highlight them and you're picking the word format cells and you get this other window now let's keep on going so in this case I'm going to come over here and it'll have a total column as well so i'll type in the word totals we're just typing it in go now i want the total for that road to appear right down once again we're going to use that auto sum that I should your earlier so I'm going to click on the cell where I want the total to up here I'll took in the home and you up top and you come over here and you look for this little icon and in yours it might say there with auto sum right next to it so I'm going to click that now this time it put the Rosell the row sum in there says some c4 through h4 usually it guess is the right thing for you it doesn't always but many times it does get the right things for you look II did here I don't know how that works just like I don't know how it works out a brown cow cheese green grass and gives your white milk I don't know how that works okay but I can still drink the milk right I'm going to hit the enter key now now I have the road total and let's take a look at the formula this is some c4 through each for now watch what I'll do for so get the black cross drag it down even drag it down to this row and now we even have a grand total now this is the first time that we've seen two pound signs in today's webinar well some side then I called pound signs anymore these days are called hash x-rays for a long time I call them pound signs a number of signs and other called hashtags I have to get used to that that's a new new word for me well when you see the hashtags in your spreadsheet and you will see them you just have to make that column bigger that's all that it means so follow my maps again we'll go up to between letters I and letter j like i showed you before we'll get the black cross and double-click Delta makes a column bigger which makes the Thompsons go away so when you see the town signs in your spreadsheet it simply means you have to make that column bigger but notice now you even have a grand total now this one is zero because they're all blank on that row so when an atom all together are just 0 I'm going to delete that sell the bunt delete and now we're in good shit so you want to start using these basic concepts the basic math I showed you the autosum the speed fill and you can start to get some pretty nice results as you can say now let's go ahead and add an average over here as well now there's many ways to add an average so here's a quick way we can use that although similar showed you before so I'm looking for the autosum and it's right there notice how there's a pull down to the right of the autosum i'm going to click that and then that is how you can do other calculations some average count and so on and then took an average now in this case it did not pick the proper range it says c4 through I for and if I included I for and the average then it would be incorrect so sometimes you have to pick your own range and i'll do that so I'm going to take on the January number and drag that over to the June number and then that is what I want to average c4 through h4 and I make the enter kick watch I did that again click on the cell where I want the average you pick them to pull down to the right of the autosum and then you can see there's different calculations though that you can use up with an average now this time it did not pick the proper range so you would pick your own range click on c4 and how to over to h4 and then I'll hit the enter key and that's what I want to take your average from now I'm going to go ahead and click on that cell and of course we'll drag it down with the black cross and we're good to go down so now i have the average for the rest of the race as well now let me show you how something the normal function is are in there you can see this is a normal mathematical formula like I showed it a while back now let me show you is some that are called functions and the built-in functions so this one for example says the word some there and this one says the word average so when you click on a cell if it has the equal sign and then it's a word that follows the equal sign that's a built-in function let me show you where you can find the rest of your built-in functions I want to show you another way to do an average okay now a function is just a built-in formula and all formulas start with an equal sign so i'll type in equals now follow my mouth and you're going to come over here and you pick on this F X it's been there forever when I took in the FX we get this new window is called insert function so such a powerful window because it really gives you many more ways to use Excel you can either search for your functions up here or you can find it by category notice how there's financial functions date and time math and trig statistical look up in reference I mean there's so many great categories engineering now cube functions so I'm going to go ahead now i happen to know that the average is under the statistical category but if you didn't know that you would search for it up here so i look into our statistical and then i'll click on the word average need your listed alphabetically once you pick the category now in this case it's going to give me a brief description of what that does right there now follow my mouth and you would pick on help on this function and then you'll get the help window about that function the help window will be very helpful for you because it'll give you the descriptions and then if you scroll down further it'll give you some examples of that as well so i'm going to close the help window you should really go through that so on this screen you can search for your functions here you can also find them by category once you pick one of the functions it'll give you a brief description and then you can pick on help on this function right down and then though it'll give you more descriptions and more examples so I'll click on the word average i'll click on OK now you're going to get this window it's called function arguments in other words what does the function need to make it work I also use the word function parameter now let me show you a big clue whenever you see this red arrow and you see it all the time in Excel that means you're going to hide other range for that option so watch what I'll do this is exactly how I use this when I'm going to move it to the side and I'm going to highlight what I want to average which is C 4 through H for all right and I'll click on Oh cash so whenever you see that red arrow right that means you can hot its themselves for that for the option or we could just type to that I find easier to highlight to self so I'll click on OK and now notice how that gave us the same result as this other cell I just wanted to show you those functions so the way we got two functions start it is you click on a blank cell type in the equal sign because it is a formula and then you pick on the FX right there and that gives you that screen that's called insert function then you can either search for them here or you can find them by category alright so that gives you some things to explore maybe good signs find some functions that will help you in your job now let me show you how to rename our sheets as been saved in a while of course i'd like to save all the time right so i'll click on file and save good now let's talk about renaming the sheets that's how i have sheet 1 sheet to sheet 3 usually you want to give those a better name so i'll double click on sheet 3 then i can type over the name i'll type in 2017 i tried it again so we'll click that and i'll type in 2017 and then you hit the enter key statistics yep now what do I have on sheet to sheet 2 is where I have my audit my speed cells or autofill so i'll double-click on sheet2 and i'll type in the word auto fills and i'll double click on sheet 1 and this is where we had the basic math so i'll type in basic math though so a lot of times you want to give your sheets a better name just double click on the sheet name then you'll be able to type over them now another thing that you might want to do is you might want to give the sheets a different color so I'm going to right click on the autofill sheet and i'll pick in the word tab color and i'll pick on red and i'll right click on the word basic math and took in the word tab color and make it blue all right so then you can see I have blue and red so they really stand out so it can rename the sheet i can add i can give them a different color remember how I showed you before we can add new sheets with the plus sign clicking the plus sign right there and now it's a sheet for sheets 5 I can also delete a sheet if you right click on a sheet then i can pick on delete now if that she has information it'll ask you like this one does for sure you want to delete that sheet applicant aletta and that you can see that she is gone so when you right click on the sheet and you get some good options there like delete rename now let's see what move or copy is going to do I have the sheet that's called 2017 where we did all that work maybe some of you are already starting to plan for 2018 and beyond well I want to copy this entire sheet with all the formulas so I don't have to read we do all that so a right click on sheet 2017 and i'll pick up on move or copy and you come over here and you took in the word create a copy and we'll pick an okay notice now i have 2017 version 2 i'd like to remain that so i'll double-click the act and i'll type in 2018 now we have an exact copy of the sheet with all the formulas and formatting so that saves you a lot of time you don't the reason vent to wheel watch Howard did that again I'm going to right click on 2017 we'll pick on move or copy click on create a copy and then i'll click on ok that gives you 2017 version to like to rename that so i'll double click on it and i'll type in 2009 good now let me show you how to move your sheets around it's just going to be a matter of dragging your sheets so i'll pick up 2017 and just drag it over here good and i'll pick up 2018 and drag a two to the right of 2017 so i showed you how to add new sheets how to delete them how to give them a different color how to give them a different name how to copy the sheets and how to move the sheets so that should help you manage your sheets now in this case we're going to go ahead and save ok I'd like to show you one more example that's important and with this information that we had in this webinar really should get you start and Excel have to go maybe a little bit farther now I'm going to get rid of column K because we already have the average I was just showing the different weather did he averaged so I'll just highlight those cells with the white cross and don't hit my delete kit now remember if you delete the wrong thing we can always undo right don't forget about that now in this case i want to show you sometimes you might see the formulas with the dollar signs in there those are called absolute references when you see the dollar signs in Excel it does not represent much or wind it when does stone the dollar signs in the formula it does not represent money it's quite an absolute reference let me show you what it means so i'm going to type in percent of total now the percent of total will be taken by taking the total for that row and dividing it by the grand total but I all those to the Viper that seeing grand total so let me do it the wrong way first and I'll show you why it's wrong I'll say equals this cell / this cell I 4/13 and it says point 11 and I really want to make that into a percent so I'm just going to hunt at that column with the white cross and a quick way is I just add the percent sign right there and it makes it into a percent for night and it says eleven percent which is fine but you think I would just be able to copy that down but let's see what happens I'm going to get the black cross drag it down and d SE division by zero okay let's see what happened this is the important part now if I double-click on that cell will be able to see the formula offset so i'll double click that and it says I 4/13 as to when i typed in if i double click on the next one it says I 5/14 you see it's still a relative limit so the whole point of moved down by one around but I don't want to / 14 there's nothing there that's why says division by zero so somehow I have to figure out a way to make it so that still says I 13 when we copy the formula down the next one down is getting even further away this one says I 6/15 but there's nothing in ISS team that's why this is division by zero but I 1/15 01 / 13 so let's see what we're going to do that's the trap that everybody falls into and then here's the way to fix it we're going to click back with it a cell and on the form of the bar before the I i'll type in a dollar sign and before the 13 I'll take in another dollar sign the dollar signs when we look at it in the formula like that they do not represent money it's called an absolute reference that means when a copper deformed it down to the next cell that part of the form that will not change but you have to see it to understand what I'm talking about so I'm going to hit the enter key now the dollar signs are not going to make a difference to you copy it down again so I have to recover that formula down let me get the black cross and we'll drag it down now you can see now we have real numbers but you have to see what happened to the formula here's the important part this one says I 4 / dollar sign I dollar signs 13 this one says I five but it's still dividing it by I 13 because of the dollar signs it's called an absolute reference and this one says I 6 / dollar sign eyes our sign 13 the first part that's one that is relative it's changing from cell to cell relative to them you sellin ounces i7 right but the second part of the formula always says I 13 it's called an absolute reference because of those dollar signs you're going to see those a lot so I wanted to make sure that Alicia introduced a topic to them those are called relatives of formulas so between the basic formulas the autosum the speed fill the the built-in formulas that you get with the FX and the relative in the absolute formulas you can really start to build some tremendous spreadsheets with Microsoft Excel I hope that you found the session helpful and its really been my pleasure being here today thanks a lot everybody I'll see you the next time