 Thank you so much for coming in and providing this training today, Sandy. It looks like we are over 70 people at this point. I'm turning it over to Sandy Reilander who's on several of these for us. Thank you so much for coming in today, Sandy. Thank you for having me. Well, I want to welcome everybody to class today. This is a beginning as well as a tips and tricks kind of class for Excel. A little bit about my background. I graduated from UC Berkeley and then started teaching Lotus and WordPerfect and DOS. And then about 27 years ago, started looking at, a little less than that, 20 years ago, started looking at Excel. And from that point forward, I was in love with a Microsoft Office suite. But Excel is by far the best program I've ever seen in my life. And if you really know Excel, there's no way you cannot love it. It's an easy program to learn if you are given the correct training. And so hopefully we'll get you really excited today about some of its features. If you have any questions, please feel free to unmute yourself and ask because I'm not able to see the questions on chat. Although I believe that I'm going to be told if one of you asks a question on chat. And also know that at around 11.15 or so, we're going to open up to questions. So if you want to wait till then, please write down your questions so that you don't forget them. So Excel, starting off with Excel, as you see, I have a spreadsheet already open. I think a lot of you are aware that Excel has these, if you look down at the bottom of my screen, there are little tabs, like the one I'm pointing to right now. This is navigation selection, QA tool, et cetera. Those tabs are worksheets. And notice that there are quite a few of them. And those make up what I would call a workbook. And so workbook is nothing more than just a binder. And if I can move this here, you can see up here, I've got the name of the workbook. Okay. So what we're going to do today is we're going to look at some fast ways to use Excel, and some of the shortcuts, that sort of thing, and some of the things that are kind of important to know right from the start. So when you start with Excel, the first thing you have to do a lot of times is enter data into Excel. So we're going to sort of replicate what you see here. I'm going to move this up a little bit. And over here, I'm going to click, and I'm going to start out with January. Now, if I press enter, look what happens. When I press enter, my, I'm automatically down one cell. And what I might want to do is I might want to go across to February instead of coming down. So instead of pressing enter, if I've typed, let me do that again, January. One of the things I can choose to do is press tab. And so that saves me from having to come down here, come back up, and come back across. Okay. And anything that saves time is a good thing. So I'm going to type in February, and press tab. Notice I'm in the right place right now to enter March. However, this is something that you have to do a lot. Anytime you have to do something frequently in Excel, and it seems like it's cumbersome, there's always going to be a better way. And in this case, the better way is in the lower right-hand corner of your cell. Notice that there is a black box around this cell. This is the border. Okay. Telling you that that's the cell you're currently in. When I've got this black border, notice that the bottom right-hand corner looks different from all the rest. And if I point to it, look what happens. When I point to it with my mouse, do you see how what I call a fat plus becomes what I call a thin plus? Okay. And the difference is, when it's a thin plus in the bottom right-hand corner, if I now drag this, notice it says February, March, April, May, June, July. Do that. So I can go forward or backward, and then just drop it wherever I want. Let's say I go as far as October, and I say, oh, you know what, I really only want to go through June. Because this is still highlighted, I can grab this bottom right corner again, and drag back to the left. And notice that it makes it look what I would call sort of grayed out. If I now let go, do you see how it erased that? So dragging to the right will give me more, and dragging back on top of itself will end up deleting. Okay. So that's the first part, making it pretty easy. But now I'm looking at what I still have to do, and I'm looking over here. I need to type in ice cream, and I don't want it to be right next to this. So how can I quickly move this over? Well, because I've got this black border going all the way around, if I point to the black border, do you notice that I have a four-headed arrow? Remember over here, it was a thin plus. Up here, it's a four-headed arrow. In the Microsoft suite, four-headed arrows always mean moving. So if I point to this, just by virtue of seeing a four-headed arrow, you should know that if I now hold my left mouse button down and drag to the right, or drag down wherever I drag and drop this, it's going to move it. How easy is that? If I want to bring it down here, up here, anywhere I want to go, I can just drag it, as long as I highlight first what I want, point to a border, and drag. Okay. So now that I've got it where I want it, I can type in ice cream, cones, cherries. Now I want to put the word total in here, okay? But I not only want the word total here, I would also like the word total here. Remember a second ago, we learned that if we point to a border and drag, that works beautifully. The problem is, now I have it here, but not here, and that's what moving is, right? It moves it, and it will no longer be in the same location. So what we really would like to do is copy it. Now I know all of you know how to do moving using cut and copy on the ribbon, or perhaps you also use keystrokes, like control X for cut, or control C for copy. Notice that it's showing you that in the little tooltip. But we just learned how to do moving using drag and drop. So I would like you to know how to copy using drag and drop. Is there anybody on the phone who knows how to copy using drag and drop, or who has a guess? If so, unmute yourself with star six and let me know. No one? Okay. So we're going to come to the first important thing to know in Excel. The first important thing to know in Excel is that if you don't know how to do something, you should right click on whatever you don't know how to do. Now, if I right click on total, do you see you have cut and copy? But that's not going to help you with what I just said, which was drag and drop. So the second important thing, so first is if you don't know how to do something, right click on whatever you don't know how to do. The second is if left dragging, in other words, dragging with your left mouse button doesn't do what you want, try dragging with your right mouse button. So I'm going to, instead of dragging with my left, I'm going to use my right mouse button, drag down here, let go, and look at that. So the left mouse button always does what Microsoft thinks you want to do most. And the right mouse button always gives you options. And nowhere is that more helpful than in Excel. Look at all these options. So I'm going to say copy here, instead of move, I'm saying copy here and look at that, I have it in both places. So once again, instead of dragging with my left mouse button, I'm going to drag with my right mouse button, let go, and copy here. Okay. So now I'm going to start typing in some of my data. I'll type in 2000, 1900, 1800, and then I'm going to drag down here. And 1700, come back up here, 3000, 2900, 2800, and 2700. Now, remember up here, when I dragged the bottom right-hand corner, remember how it was nice enough to fill in the dates? That's called the fill handle. It fills information from cell to cell to cell. It doesn't, it's not the same as copy because copy would allow me to take January and copy it just down here where fill is going to fill every single cell. Okay. So it's not the same. So notice we have the fill handle here again. Look what happens when I drag the fill handle here. So up here it went January, February, March, but here it says, hey, you highlighted 2000, so I'm going to assume that you would like 2000 going all the way across, but that's not true. I wanted 2000 here, 3000 here, and as you can see over here, what I want is then 4000, 5000. So instead of just coming to one cell and dragging it, what we need to do is teach Excel what the pattern is of what we would like to do. So if I highlight both cells and then go to the fill handle, do you see how it's understanding the pattern you started? 2000, 3000, so it knows four, five, six. Again, highlight two cells, drag across, two cells, drag across. How nice is that? But I'm going to delete all this and show you that Excel is smart enough that if I had only done the first two columns, if I now come down to that same fill handle, look at that. How much faster could you possibly enter data? Now that the data is entered, we would like to total these columns. One way to total it is to type in a formula. A formula uses things like equals, plus, minus, and multiplication, and division. Now normally when you write a formula by hand, a lot of times we've been taught it's five plus six equals 11, right? In Excel, it's the other way around, you're always going to start with an equals. So I can either type in equals or I happen to put it on my toolbar up here, but you can just type in equals if you want on your keyboard. And then you have to say what it is you would like to add. Now I could say equals 2000 plus 1900 plus 1800 plus 1700 and get the correct answer. Let me go ahead and press enter. Notice it says 7400. Does anybody know what the problem with doing that is? If I actually type in these numbers, first of all it takes a long time, but second, and it's prone to error, right, because you could type in the wrong number, but the problem, the real problem is what if this 1900 later on becomes 2100? Notice the answer right now is 7400. I'm going to change this to 2100. Hit enter. Notice how this did not change. This would not change unless I come down here and also change this to 2100. So in Excel, you try really hard to stay away from ever typing numbers in a formula. What you really want to do is you want to use what are called cell references. So this cell is, and cell references are referred to by the column letter, which is q, and the row number, which is 5. So this would be q5, and you can see that they're even nicely highlighted. Notice that this is orange, q5. If I click on the next one, it's q6, the next one, q7, and the next one, q8. If you're ever in doubt, over here in the left corner, there's something called the name box, and the name box will always tell you which cell you're currently in. See this? q7, q6, and q5. So I could type in equals q5. Notice as soon as I do, do you see how it's nicely highlighted up here so I know I did the right thing? Plus q6. Once again, it's nicely highlighted. Not only that, but I don't know if you can tell the colors on my screen. But q5 in this formula is blue, and this is highlighted in blue. q6 is green, and this is highlighted in green. Okay? Continue, plus q7, plus q8. Hit Enter. Notice that this is now 7600 instead of 7400, which it should be. And if I change this back to 1900 the way it was, now it's back to 7400. So by using cell references, as opposed to typing in what's actually in the cell, you make sure that your formulas always can stay the same while your data changes, and that's really critical in Excel. Okay? So you saw me typing in each of the cell references, q5, 6, 7, and 8. You don't have to type them in. If you just type in an equals, then I can click on the first cell, and do you see how it types it in for me? Then all I have to do is press the plus and click on the next one. Plus click on the next one. Plus click on the next one. When I've got them all in there, I can either press Enter, or notice over here you've got two keys or two icons. The check says Enter. The X says Cancel. So if I want to stop what I just did, I can click on the X and I'll clear everything out. But if I want to enter, I can click on the check and boom, it's entered. Now, so I've typed it in in two spots. I don't even have to really type it in in two. I could have just typed in the first one, and you've already learned about this here in the bottom right corner, right? It's called the fill handle. As soon as I've got the formulas that I want, I can now drag the fill handle across, and you see how it nicely puts that formula into each and every cell. So I have a question for you. If my formula here is Q5 plus Q6 plus Q7 plus Q8, why aren't all of these 7400? If what I copied was Q5 plus Q6 plus Q7 plus Q8, isn't that always going to be 7400? So why aren't these? Does anybody know? If you know, un-meet yourself by pressing star 6 and let me know. So it dynamically changes and realizes that you're looking at the next row and increments at each time? Very good. Okay, so let's look at that. Some people in class will just guess as to why this happened. So I'd like you to always do, instead of guessing, if you don't know what's happening in this cell, because you can't tell, right now it just says 11,400, right? What I want you to always do is click on it and look up here. This is what's always going to tell you the truth. This, you don't know if somebody typed in 7400 or what, but this is going to tell you what's actually in this cell, which is R5, R6. So notice this is Q5, Q6, R5. So it automatically incremented the column letter in each of these. Why is that? Well, if it didn't, then in Excel, you really could never successfully copy any formulas or functions. Because if I copied this and it really stayed Q throughout and really had 7400, that would never do you any good. Not to mention, what if I moved to this whole group down? If this were still referring to 5, 6, 7, and 8, then it would be saying zero down here. But when I moved, it changed 5, 6, 7, 8 to 12, 13, 14, and 15. That's called relative cell referencing. It will change these references relative to where you move or copy. It's a really important concept to get even in a beginning class, even though it's one of the most difficult concepts to understand. It's very important to understand that it's going to do that for you, and in a minute we'll show you why. Now, those of you who've used Excel before are probably saying, wow, she probably just doesn't understand Excel, because you don't have to do equals Q5 plus Q6 plus Q7 plus Q8 because there's something called a function, and it's called the sum function, that will allow you to add a column or a row of numbers very quickly. And that is true. So why did I teach you this first? Well, because a lot of people don't understand how to do a formula, and this is a formula, and oftentimes you're going to need a formula. For instance, in a minute we're going to try and find out what the tax on this is, and there's no way to do that with a function. You're going to be doing that using multiplication. But let's look at the summation function. The summation function is right here. It's called autosum, and what you do is you could either select the data, or you can just click right below where you want the total to go. So I've clicked where I want the total to go, and summation means to add. So if I click on this, notice that it's going to put in a function. A function always starts with an equals, just like a formula does. But then it has the function name, and in this case it's sum. Sum means to add. After that it's going to put what range of cells you want to add. Notice that it's saying q5 colon q8. The colon stands for through. So what it's saying is I want you to add starting at q5, ending at q8. So I could type this in myself if I wanted. I could do equal sum. Notice when I do that, every single function starting with sum appears. If I then double click on sum, notice it puts in the word sum for me, it also puts in the parentheses, which are important, or at least the left parentheses. And then down here it's trying to say what it needs you to do. So if I want to add these numbers, I can just drag from the first to the last. Be really careful. You don't also drag on top of where you want the total. Okay? Make sure you just drag where you want to start and where you want to end and don't put in the total. Okay? Because then you just want q5 through q8 and then you can press enter. Okay? Another way to do it is to just click on the other sum. Notice it's showing you what it's going to add. If it looks good, you can press enter or click on it again. At this point, you could drag the fill handle if you want. Now the one thing you need to be careful of is if your data has got what I call holes in it. If you leave this blank, I'm not talking about if you type in a zero, but if you leave this blank and you click down here and click on autosum, do you see how it's not adding the 2,000? So you have to be careful if you've got data that leaves some cells empty. There's a couple ways of correcting this. You know the data starts in q5, not in q7, and it's showing you that. It's why it's really important you always look to make sure it's correct. So I can come over here and change the 7 to a 5 if I want. Notice now it's showing me the hole. Okay? Or I can come in here and change the 7 to a 5. It never makes a difference whether you correct it in the cell or in this formula bar. Either way is fine. You can also... Let's do this again. When it shows it to you incorrectly, I can just drag across what I really wanted. Okay? Any of those work fine, but be really careful when you have holy data not to just trust that Excel is doing the right thing. Always look and make sure. One last way of making sure your holy data doesn't affect you is you can highlight your data plus an extra row where you'd like the total to be and then click on Autosum. By doing that, you're forcing it to start at q5. Okay? So two ways of doing it, just click down in the total, click on Autosum, and correct it, or select the data plus the total, and it'll do it right every time. Okay? So now we want... Let's say you want all of these totals. You can actually select every single area that you'd like the total, and it will do them all at once. We not only want row totals, but we would also like column totals. So if we select the data plus an extra row and an extra column, like I just did, and click on Autosum, look at that. It did it all at once. It did our Sundar rows and Sundar columns all at the same time. Pretty cool. Now let's say we have a tax. Okay? In Washington, we have a tax of 0.095. Well, not all of Washington, but at least where I live in Washington, 0.095. Now remember I told you earlier that you needed to understand formulas, and remember formulas always start with an equals. So what would the formula be for the tax? Well, it would be equal, and then I would take what I'm trying to get the tax on, and then times... Times is this... It's a star. So shift in the number 8, or it'll be on your numeric keypad. The location symbol. And then times 0.95, right? Is that right? The amount times the tax. Looks correct to me. So I'll press Enter. So if we want to make it easy, I could do 10%, so I'll just do 0.1, and you can see that, yes indeed, that that is 10% of 74,000. Okay? Now, since I've got the correct formula here, I'm going to drag this across. Uh-oh. Can anybody tell me what went wrong? Okay. Well, remember if you don't know, you should look. So this is correct. We've already determined 74,000 times 10% is 740. But this doesn't look right. Okay? So let's not guess. Let's just look. One way is to just click on it one time and you can read the formula. Something that might make it a little easier is if you double click on it, it's going to show you, remember how it highlights and color codes, whatever's in your formula. So it's showing you that where, if I double click on this, do you see how it's multiplying 7400 times 0.1, or 0.1, which is perfect? When I double click on this, it's multiplying 11,400 times nothing. Click on this, multiplying 15,000 times nothing. Why is that? Well, remember a few minutes ago, you told me that relative, or I told you relative cell referencing was what was changing Q to R to F. And at that point you liked it. It was changing it as I moved it to the right. Well, Excel has no idea that that isn't what you wanted here. So that's what it's doing. It's taking this times this. And since I'm moving it to the right one, the next time it's going to make it this times this. That's what relative cell referencing does. So how do we correct for this? We don't want to put 0.1 in every single cell. We just want it to stay there. So what we have to do is go to this first formula, and we have to tell this P11 that we want it to stay put. It's called absolute cell addressing. It's saying no matter where I drag this, I always want you to point to P11. To do that, you need to put a dollar sign in front of whatever you want to stay put. And so I want the P to stay put. So I'm putting a dollar sign in front of the P and pressing enter. And now as I drag across, hopefully it'll be correct because it won't go from P to Q to R. It'll be PPP all the way across. Let's see. Perfect. So over here, if I click, notice it's R9 times P11, S9 times P11, T9 times P11. So I caused it to always point to this simply by putting a dollar sign in front of whatever I wanted to stay put. Really important concept in Excel. Okay? So we've gotten some formulas down very basic, gotten some functions down also very basic. Let's do some formatting now. All right. So we would like to make these, let's say centered in bold. So I'm going to select them all. And on my home tab, right here there's a B for bold. Notice it says I can do control B if I want to use keystroke, but I can press B for bold. Then over here, you can see that currently I'm bottom aligned. Bottom aligned just means I'm at the bottom of this cell. Okay? Notice if I come over here, this just says left, center, and right, but this is bottom, middle. See how now it's in the middle and top? So this is vertical alignment and this is horizontal alignment. So if I want to center, I'm going to use this tool here, left align, center, right align. Okay? Now if I want to, I can also do this, which will move it up, but right now I'm going to stay bottom aligned and centered. Okay? All right. Then I'm going to bold this too. Now if I wanted to have bold both of them at the same time, Excel has this neat thing that if I select these, and then I hold my control key down, I can select multiple areas. In fact, I can select as many areas as I want, and now when I press B, do you see how it bolds both of them at the same time? The problem when I do that, notice how ice cream. You can't see ice cream anymore. How do I make or you can't see all of it? How do I make this a little bit larger? Well notice up here where there's the P and the Q. If I put my mouth right between, so I can't do it here, but up in this heading area, notice I have a two-headed arrow, which always means sizing. So if I drag this a little bit to the right, do you see how that will allow me to make each of these larger or smaller? So if you come up to the heading, that's an easy way to make your column wider or narrower. If you make a column too narrow that has text in it, do you see it just chops off the text? If you make a column too narrow that has numbers in it, as soon as you can't see an entire number, it will replace those numbers at least visually for display purposes with pound sign, so that you can't possibly confuse this with the number 200 instead of 2000. If you can't see the entire number, it won't show you the number at all. So if you ever have not enough room to display numbers, just make your column a little wider and they will come back. Don't delete them and retype them because they're always still there. They're just not showing until you make the column wide enough. So I've been telling you to drag the column to make them wider. You can do that, but you can also double-click. If you double-click on the right-hand side of a column, watch. It does what's called a best fit. It makes it wide enough for the widest entry, but as narrow as possible. So I can come over here and double-click. Double-click, okay? Now you may save it, Sandy. If I had all this data, 12 columns or maybe even 100 or 200, that would be such a pain. Any time you say that would be such a pain, it means that there's a better way. Instead of just doing a column at a time, I can drag across all the columns that I'd like to affect, go to any one of these borders, and double-click. Notice how they all became best fit. That being said, though, a lot of times people don't like the fact that poor may doesn't get nearly as much room as January. They don't want a best fit. They want what's called an equal fit. If that's true, then just highlight again Q through W, not down here, but up here, Q through W. And instead of double-clicking, which we just did, go to whichever column you think is the largest. I'm going to guess February. Drag it just a smidge, and by dragging instead of double-clicking, you're going to make them all equal fit. Now you may say, well, why didn't you do that on May? Wouldn't it have worked? It does work perfectly the same on May. The problem is I don't quite know how far to drag it, because it's not the largest. So I will keep dragging and dragging and dragging until I get it right. Where, if I go to my largest and just drag a smidge, I know it's going to be right, because it already was big enough. Does that make sense? Okay. How you can change column widths and row height. If you'd like to add some color, I can drag across the titles. Over here, I've got my little paint bucket. If I like yellow, I can just click on the bucket, because that's the last color that was used. You can see down here, I don't want yellow, so I'm going to click on the down arrow, and you can pick any color you like. Okay. If you want a border, I can click on this down arrow, and I can click just the bottom, top, left, right, or outside, inside, all sorts of different border options. Okay. Now, on total, oftentimes, accountants like a thin top border, excuse me, a single top border, and a double bottom. So if I select that row, click on this down arrow, notice there is a single, a top, and a double bottom. And that's what gives you that look. Okay. Any questions so far? Okay. If you had this great worksheet, I'm going to take the formatting off of those bottom, getting in my way here. Let's say I don't want all this formatting right now. Notice there's a clear, because if I just press delete, do you see how my formatting stays, but the contents goes away? Instead, I would like the formatting to go away, and my contents to stay. So that's what clear does. I'm going to click on the down arrow next to clear. If I clear all, it's going to get rid of the contents and the formatting. Don't want that. Clear formats, that's what I want. I'm going to click on that, and do you see how now it's just as if I had just typed it and not done any of this great formatting? So either I can format each of these again, which means that I've got to make them blue and bold and centered and all of these different things. Or there's a tool called the format painter. You might be familiar with that in Word. It also works here in Excel. If I select what I like the look of, the format painter will copy. It's like a copy and paste, but it's a copy and paste of formatting, which is looks, and not a copy and paste of the data, just the looks. So I highlight what I like to look of, click on the paintbrush and drag across what I don't like to look of, and how much faster is that? That'll save you so much time. Now notice it'll only let me paint once. So what I'm going to do this time, I'm going to drag across what I'd like to look of and double click on the paintbrush. This will allow me to paint as many times as I want. Oops, I went down one too far. So let me do it one more time. I highlighted. I didn't see I had the grand total, which by the way, I'm going to get rid of. I'm going to select this again, double click. Okay. Any questions on that? Format painter, if you remember it, is an amazing tool. Okay. Now, when I originally created this, I highlighted my data and clicked on the autosum and I got my total. Okay, let's say this is the total for Seattle, Bellevue, and Issaquah. But now I'd like to get a grand total. Can anybody tell me how I can add this number plus this number plus this one? Well, we learned in class that if we click on an equals, then we could click on that plus this plus this and enter and drag the fill handle across. Not bad, right? The problem is what if instead of three stores, what if you had 2,000 across the U.S.? Wouldn't that make you a little sad if you had to do equals this plus this plus this 2,000 times? Again, if anything seems cumbersome in Excel, it means there's a better way. All you have to do if you want to get grand totals for all of these is, again, select the data plus where you would like the grand total to appear and hit the autosum. Isn't that easier? Let's see what it did. Well, it did a sum of just those totals. How did it know to do that? I had all of this area selected, all of this and all of this. Why wasn't the number double this? Well, Excel is smart enough to say, hey, you did a sum here, a sum here, and a sum here. I know that I should just be adding the sums and not the data also because then I'll be doubling everything. Simply because you used autosum in each of this, it knew to go to each of those cells and add them, saving you a ton of time. Let's say now that we have this worksheet, we worked hard to create it, we really like it. We're going to copy it. We're going to copy this data and we're going to put it on a new sheet. Okay, next year's coming, we want to have a new one. So I'm going to select it, kind of come over here and click copy. Down here I can click on a new, an entirely new workbook if I want to. And then I can just paste. What happened? All of that beautiful formatting that I did earlier, look at that. All the column widths are wrong. All the row heights are wrong. Not very pretty worksheet anymore. When you do something like that, always look, notice that there's a little pop up here. Okay, many people when they work in Excel, they get very annoyed at these pop ups and they wish Microsoft would just make them go away. I would like you to think of these little pop ups as gifts from Microsoft that will save you so much time if you take the time to open them. So we're going to open up our gift simply by clicking on it. Now I grant you that these are not very clear, but if you point to it, it will tell you what it does. So if I look at this one, let me move this out of the way. If I look at this one, do you see where it says keep source column width? Let me click on that. And oh my goodness, look at that. It just restored all my column width. It made them identical to where it came from. Now true, it did not keep my row height. So I would have to once again, I'd have to go between the four and the five. Go to the bottom border of my row, drag it to make it bigger. So that would take me a little time, but not nearly as much time as doing both. That being said, copying and pasting entire sheets is probably the wrong way to go to begin with. Even though it works, if you have to copy something and you would like it to be identical, the easiest way to do that is to copy a sheet. This is a sheet. If you don't know how to copy a sheet, how would you make a copy of this sheet? Well remember earlier today I said one of the things I want you to remember is if you don't know how to do something, right click on whatever you don't know how to do. So if you don't know how to make a copy of this sheet, right click on it. Do you see where it says move or copy? Click on move or copy. Please, please, please be careful to select create a copy. I so often forget that and then all it does is move it. If I now click on OK, notice I have a second sheet called navigation selection with a little two next to it. Although that's a great way to copy a sheet, let me show you one other way. To move a sheet, if I just drag it, notice this little arrow, if I drag the sheet, do you see how it moves it? So now it's after this QA tool, moves it, it moves it to wherever this little black arrow is. Moving isn't going to help me copy is it? So instead of just dragging, another thing that would be nice to remember is if you hold your control key down as you drag, do you see the little plus in the sheet? Now my control key is not down. Do you see how there's no plus in the sheet? Now there is one. If you have a plus, when you let go of your mouse, you will automatically copy. Let's do that again. I'm going to delete this. I'm going to start dragging this and the only time it's important to hold the control key is down is before you let go of your mouse. So I've got my control key down. I let go of my mouse and I just copied. Notice how these sheets are 100% identical because we copied the whole sheet. So anytime you need a sheet to look the same as the last, I would highly recommend doing this as opposed to using copy and paste. Okay? What if you want this to have a different name? Well, once again, if you don't know how to do something, right click, look at that. Rename. And I can name it anything I want. In Excel, there's another way of doing the same thing and that is to double click. If I double click, I can rename it. So let's say I want to name this 2016. That's all. And press enter. That's it. Double click, name it 2017 and enter. That's it. So that's a way of renaming your sheet or right click, rename. Either way works fine. Okay. One of the problems a lot of people have, by the way, I want to get rid of this data here. If I just select it all and press delete, remember how it's not going to get rid of my formatting? Instead, if I go to clear all, now it got rid of all of that as well. So that's kind of nice. All right. Look what happens if I start going down my screen. In fact, I'm going to get rid of, okay. Let's say you have a large spreadsheet, which many people do. Look what happens as I go down my screen. How do you know which one of these is February, March, April? It would be difficult, wouldn't it? Because as I scroll down, do you see how my titles go right up off the top and I don't get to see it? That makes it really difficult to work with my worksheet. It means I have to keep going up to see what column I need to go in to go down to make my change, go back up, see what column to work in, go down. And that's a pain. Microsoft has given us a way to prevent titles from going off the screen. The same happens if you go too far to the right. Do you notice how now I can't see my ice cream cones, cherries, and nuts? So let's say I'd like to keep January, February, March on my screen. What I'm going to do whenever you want to change how things are displayed, it's going to be on this tab called the view tab. Okay. Now on the view tab, there's something called freeze pain. So what I want to do is I want to click in the cell that I'd like everything above it to be frozen. I'm going to now come, since I've clicked here, I'm going to come and I'm going to click on freeze pains and freeze pain. Notice this line that comes across here. It's showing you that at that point it won't go off the screen. Let's look and see if it worked. Look at that. Won't that make it a lot easier for you to enter data? But as I go to the right, do you see how ice cream cones, cherries, and nuts still goes off the edge of the page? The reason is when I clicked, I clicked here, which means it freezes everything above and to the left of where I clicked. Well, there is nothing to the left of ice cream. So if I really wanted ice cream to be frozen as well, I should have clicked here. So I'm going to unfreeze my pain by clicking on freeze pains. I'm going to unfreeze them. Then click where I really want to go. Go back to freeze pains and freeze. Notice now I've got lines going down this way and this way. So as I go to the right, do you see how ice cream cones, cherries, and nuts stay there? And as I scroll down, do you see how January, February, March stays there? Okay. So freeze pains under the View tab is really helpful if you have a large spreadsheet to keep things on your screen. Now even though freeze pains is great for viewing, what happens if you go to print what's on your screen? I'm going to click on File and Print. And look, this is the first page. Print also shows you a print preview. All right. Notice that on this first page I get to see my headings. But as I scroll through the pages, look at that. My headings are gone. And you may say, but Sandy, we just put on freeze pains. That's true. You did. But freeze pains only works for what's being displayed on your screen. It will not help you when you go to print. So people reading your report are going to have a difficult time knowing what each of these columns is about. So how do we take care of that? So what we need is something to help us when we print. And that's called Setting Print Titles. Okay. So let's again, we'll go to, oh, there's a couple ways we can get to it. But what we're going to do is we're going to go to Page Layout. I could have gone to File Print if I wanted to. And I would have Page Layout Options or Page Setup Options right here. Okay. So if you want, you can go to File Print and do Page Setup here. Or if you go to Page Layout, you've got Page Setup here. Either way, they're identical. What you want to get to is this thing called Print Titles. When I go to Print Titles, notice that it says here Print Titles. And it asks you what rows would you like to repeat at the top? One of the easiest ways to tell it what rows you'd like to repeat is to click in the box and then go over here to where the numbers are. Those are the row headings. They're not in the text, but over here to the row headings, one, two, three, four. And just drag across the rows that you'd like to repeat. So for us, it's up through January. If you also want to make sure that the columns, ice cream cones, cherries, and nuts repeat at the left, I could click here and click on Column A. I don't care about that right now. I just care about my first four rows. Let me look at Print Preview. Notice that I've got my titles up here. And as I come down here and scroll through my pages, look at that. Wouldn't you prefer that if you were reading this report? Now the thing is I only see through June. Why is that? Well, for some odd reason, Excel chooses to have portrait as their default, which means the page is like this instead of laying on its side, landscape. So if you say, gosh, I sure think it's better landscape, notice that because they put print and print preview on the same page, you can actually right here change from portrait to landscape. Now it's still only showing through September. So you might say, huh, what else can I do? Well, maybe instead of normal margins, maybe I can change those to being narrow. Now I've gotten through October. Still need a little bit of help. So maybe I need to scale. I'm going to click on this down arrow. And I'm going to say fit. I don't want to fit everything on one page because that means that not only do my columns have to fit, but also my rows. And generally, if you have a large sheet, that would be impossible. So I do want to fit all columns on one page. Let's see if that works. Boom. Now the problem is it looks like I'm still a little short for total, which is why I'm getting the pound sign. So even though I got narrow margins, well, that's probably about all I can do because it's .25. But I'm going to try. I'm going to go to custom margins and see if I can do .2. You can't go too small. But I'm going to see if I can do .2 and see if that takes care of it. Nope. So at this point, I'm probably going to have to decide to make my font size just a smidge smaller. So I'm going to go to my home tab and I can just highlight my date if I want. But do you see this little cell up here in the top left corner? If I do that, it'll highlight my entire spreadsheet. I can do that or I can just highlight my data, whichever one you want to do. Okay. And change the font size to whatever font. Oops, I'm not going to want to do that because if I do that, I'm going to change my basket and robins, which was the 20 point. Let's see what this is. This is 12 and this is 12. So I want to start down here. I don't want to start so high up. Okay. And let's change this to 10 point. And then if 10 points acceptable, and then just make our columns a smidge smaller. That only made the total column a smidge smaller. So I'm going to come up here and make these just a little smaller. And now I bet I'm going to fit on my page. Let's go to file print and look. Boom. Now it looks great. And it all fits on one page. So notice that 90% of what you want to do changing to landscape, changing the margins, changing the fit, almost everything that you want to do with printing will be right here. And if that's not enough, you can always click on page setup, which would take us to that same place that we were in when we went to page layout, page setup. Okay. So we know how now to get a nice worksheet and be able to view it easily on screen. We know how to make a nice print of what we've got. We know a little bit about formulas and a little bit about functions. Did you notice when I wanted to highlight this area that I dragged? Make sure you have a fat plus when you drag. What I mean by that is if I happen to be on a border when I drag, I can accidentally move something, right? So I don't want to do that. I want to make sure my point is a fat plus before I drag and I can select. But wouldn't it be nice if instead of having to drag the whole area, if something did that for me? Well, there is something that can do that. It's called selecting the current region. And this is all in my handout. If you guys look at my handout, but one of the shortcuts for selecting current region, as you, well, this is called select current region, but it's control, shift, and the number eight. Or actually, let me give you an easier one, control A. You know how control A in Word selects an entire document? Well, in Excel, there's a million rows and over 256 columns. So it knows that you probably don't want to select the entire sheet. So control A will select only the current area. And the current area is defined by when it hits the first blank column or first blank row. Okay? So see what a nice job it did if you want to select your current area. If I go back here and click in here and do a control A, you see it selects the current area of whatever I have my cursor in. The great easy way to select the area that you're working with. All right. So we've covered a lot of basics. Are there any areas that I haven't covered that you're particularly hoping to have covered today before we go into questions? Otherwise, I'll just continue. Nobody? Remember to unmute yourself with star six if you want to ask a question. But if not, then we'll keep going. All right. We showed you how to bold and how to fill the background and that sort of thing. But remember when I typed in my numbers at first, they were very plain. Let's look over here. Do you see that? There's no commas. There's no dollar signs, nothing like that. A lot of people, when they're new to Excel, they'll type in the dollar signs and the commas and the decimals. You never want to do that. You want to let Excel do that. Okay. So if you want all of these to just have dollar signs, highlight as many of these as you would like to have dollar signs. And notice you've got formatting right here on the home tab. So this dollar sign will put in dollar signs. Uh-oh. What just happened? Well, it put in dollar signs, commas, and two decimals. So I'm going to make these columns bigger so that you can see the numbers again. Notice that it put in dollar signs, commas, and two decimals. Okay. Now, instead of immediately making the column width wider like I did, let's look at what these do. This, if I don't need the decimals, because I really don't have any pennies in any of these numbers, I'm going to go ahead and decrease the decimal by one. Each time I click, it decreases the decimal by one. Well, it appears that it's going to be still too small. So I'm going to make these bigger and show you. So if I decrease... Oh, that's interesting. Okay, here we go. If I decrease the decimal by one, you see each time, and this increases the decimal by as many as you'd like. All right. So this is increase and decrease decimal, and this does the dollar sign. Now notice over here that I decided I only wanted dollar signs on my top and bottom rows. I didn't want them all over, okay, because it gets a little bit busy. If that's the same with you, then what you can do... Let me go back to the way we were. Here we go. I can highlight just the top numbers. Remember, I can hold my control key down, highlight the bottom numbers, and maybe, yeah, just those two, let's say, and I can click on the dollar sign and then get rid of two decimals. Here, I don't want the dollar sign, but I do want my commas. So notice there's a comma format. I can click on the comma, and again, go down two decimals. So now this, that's called number formatting. This number formatting looks the same as this number formatting. So right here is your number formatting. Now notice how Baskin Robbins is centered over all my columns. Okay, let's get rid of that formatting. What a lot of people do to center Baskin Robbins is they will try and put it in a cell that looks like it might be centered. The problem with doing that is as you add rows and columns, not as you add rows, excuse me, as you add columns or you make your columns wider or narrower, it will never be in the right place. Okay, so instead what you want to do is you want to type it into that first cell and now you may say, well, okay, Sandy, just hit center. But if I hit center, it's going to just center it right in that cell. What we really want is we want to drag our cursor, our mouse, excuse me, across all the cells that we would like to center across. So I'm going to drag it across from here to here. And do you see where this says merge and center? What that's going to do is it's going to merge each of these cells into one and then center Baskin Robbins across. Now if you change your mind later and you say, oh, bad idea. I don't like that or I'd like it to go even further. Maybe you only did a merge and center across this many. So oh, bad idea. Just click it again. It's a toggle. Then go across what you really wanted and hit merge and center again. Now let's say you had a large spreadsheet and you needed to drag across what I call the bottom border. Do you see how this sort of races up and races down, making it really difficult for you to get exactly what you want? Whatever happens, know that you can just click in the top left corner of whatever it is that you'd like to work with. Scroll down. Don't arrow down, but scroll down with your mouse. Hold your shift key down and click the end. So you can always click and shift click. And in fact, you can keep doing it until you get it right. I see a lot of people highlight some data and then they go, oh, I meant to go down here so they start all over again. You never have to do that. You just have to hold your shift key down and click wherever you intended to go, whether it's more or less. So we've learned how to change column widths simply by either dragging or double clicking. We've learned how to change row height by dragging or double clicking. Double clicking always does the best fit. So if you ever accidentally drag a height and you go, oh my gosh, what did I just do? Just double click on it. It'll snap it right back into place. Okay. We've also learned how to move using drag and drop. Remember, we selected our data just by coming across. And then we went to the border and we moved it wherever we wanted it to go. Okay. Now that works except what if I drag, what if I want nuts to be between ice cream and cherries? If I just drag this on top of ice cream or on top of cherries, look what happens. It'll give me an error message saying, do I want to replace the content? In other words, if I do that, it's going to kill my cherries. Let's see if I'm telling the truth. Boom. That's not what I wanted, right? So what I see many experienced Excel users do, let me get rid of this, what I see many Excel users do, even though they've used it for years, is they will insert a row. Now how do we insert a row between ice cream and cherries? Well, remember if you don't know how to do something, you right click on whatever you don't know how to do. So I'm going to right click on the number six. Because we're using, we're working with the whole row, we want to be right on the number. If I want to insert a column, I want to be right on the letter. But I'm going to go right to the number six and I'm going to right click. Notice when I right click, it shows me everything I can do with rows. So I'll hit insert and boom. Now if I want to, I can drag nuts up here. But now it leaves a blank row and so I would have to right click and delete. That seems like a lot of work. So instead, instead of dragging with my left mouse button, which will kill my cherries, remember earlier I said if dragging your left mouse button doesn't do what you want, try dragging your right. So I'm going to highlight what I want to copy. I'm going to drag with my, or move, drag with my right mouse button on top of cherries. And look at this. It says, would you like to shift cherries down and move nuts in its place? I can also shift cherries down and copy nuts, which means I'd have two rows of nuts. But I'm going to shift cherries down and move nuts in its place. Is that not a whole lot faster than having to insert a row, move, and delete a row? Okay. All right. Well, we've learned a lot. There's only so much you can learn in Excel in an hour and 15 minutes. We didn't even quite have that. So if you have any questions, what happened? Do you have any questions on anything I've taught, or is there any topic you would like to learn more about before we end for today? You should all have a copy of my Excel handout, which has all of this in it. And you also, at the back of the handout, there's a list of shortcut keys that would be very helpful in Excel, so look at that as well. Tell me how to color alternate rows. I know when I create a table, it does that for me, but I haven't been able to figure out how to do that quickly without inserting a table. Is there a reason you don't want to insert a table? I guess not. I find them more cumbersome to work with. I don't really need a table. It's for visibility, just for readability. I mean, sometimes it's helpful. Sure. So it is a great question, and I used to do that all the time, which is why I'm asking. There are several ways of doing what you're requesting. The best thing would be to learn how to use tables, but the reason a lot of people don't like tables, and that's a new feature that's actually an amazingly cool feature. But the reason that people don't is because the formulas are different in a table than they are not in a table. Like the equal sum that I showed you is something completely different. But one of the cool things about a table, should you use it, is so you highlight your data, and again, of course, I don't even need to highlight my data. If I'm anywhere in my table, format table will add that table function, and I know that's not what you want, so we'll get to what you want in a second. You do decide to choose, let's say, like this look. You can click on this look. It'll ask you, is this the range? And it's pretty darn good as long as you have no blank rows or columns that ascertaining the range. It did a perfect job. It also figured out that I do have headers, which is what January, February is, and I click on OK. Now the reason I love the table feature for this is if you notice in the bottom right-hand corner, there's actually a little arrow. I don't know if you can see it or not. But I can, if I want more rows having the bands, I can just drag that down. And you see how it puts more bands in there for me, which you can't if you don't use the tables. The other neat thing is if I even just, if I'm here and I press Tab and start a new row, it'll do it. If I just come over here and start typing something and hit Enter, it'll do it. So it always stays correct, which is not true if you don't use the table function. Also, as I scroll, if I'm in the table as I scroll up, notice my headings don't go off the top. They are right up here in the bar. But that being said, I certainly understand if you don't like using them. So if you then go to the Design tab under Table Tools, which by the way, you only see that if you're in a table. So if I click outside, notice it's gone. Click inside, it's backed up. So if you're in a table, you have this tab. There is a Convert to Range option. If you click on Convert to Range, it says, do you want to convert the table to a normal range? You say yes. Now you've got all your formatting and none of the tables features anymore. So that would be a good way of doing it. Another good way of doing it is in olden days, like before 2010, there was this feature called Auto Format, which is how you got a table to look the way you wanted it to without the tables feature, because that's the only way you could. So if you clicked on this Auto Format, then here you get those old formats that you were used to. And this was one of them. It has less choice, but here's one of the gray shades, and it would do this for you. If you like that, I put this on my Quick Access Toolbar. This Quick Access Toolbar is the most wonderful thing in the entire world that a lot of people don't know they have. Normally the Quick Access Toolbar is up here. Next to the X, if you see a few little tools, that's your Quick Access Toolbar. I hate it being up here, because these are supposed to be your most used tools and they're as far away from your data as possible. So what I do is I right-click on it and I say, show below the ribbon. Now it currently says show above because I am below, but I right-click and say show below the ribbon. Where is it? Show below the ribbon. So now it's down here. If I want to add things to it, one way I can do that, because Auto Format is no longer a function in 2010, at least not on the ribbon, you would just right-click and you would say, customize the Quick Access Toolbar. And it's not a popular command, because it's an old one, so you just right-click on the down arrow, go to All Command, and Auto Format should be in the A's. So you can just scroll down until you get to Auto Format, double-click so that it goes over onto your Quick Access Toolbar, use your arrows to move it up or down to wherever you like, click on OK, and that would give you Auto Format. So if you don't want to bother with making it a table first, you can just have Auto Format. If you do want to make it a table first and then convert to range, then you can just have the choices of colors in that sort of thing. Does that help? Definitely. Thank you. I think I need to learn how to put tables with more patience. Oh, my gosh. Tables are so wonderful, because the other thing tables do, I'm going to go back to that. And I realize this is not a beginning topic at all. Oops, wrong place. Make sure that, yeah, here we go, Format table, here we go. Click on OK. So I just converted this back to a table. If I now want to have a total row, I can click on total row, watch it already put my total in for me, and so if I want this I can drag it across. Now one of the reasons people don't like tables, I didn't drag it quite far enough, but anyway, it doesn't say equal sum, which is what you're used to, it says equal subtotal, which is annoying to people who are used to equal sum. But this is actually a much better function because what equal subtotal does is it allows you the option of showing, okay, let me back up. So sometimes people hide rows, okay, so you can't see them. So I'm right clicking, whoops, I didn't mean to delete, I meant to hide. Notice the total here is 13,200, and I'm going to hide. And do you see how it did take away the amount that are hidden? Do you see how it's no longer as much as it was a second ago? Okay, I'm going to unhide now. Do you see how it's 130,200? I don't know, I said 13,000 didn't I, but 130,200. So with equal sum, you're always going to, well, let's just do an equal sum, if it'll do it while I've got this on here. Okay, so with an equal sum, notice it's got the right answer. Let's look what happens when I hide, which is often the case, people will hide rows. And it automatically did take away the hidden rows, do you see that? But what if you didn't want that? What if you still wanted everything to be in the total? Because those rows are really there, even though you're not seeing them currently. So this allows you, this 109, this allows you, if you take out the 10, to say hey, no matter what, I always want to see all totals whether rows are hidden or not. Or if you like the fact that they aren't shown, you can leave the 10 in there and it will take away any hidden numbers. So that's one of the nice things. The other nice thing is anytime you want to insert rows, if you're over here in the bottom right, do you see how it inserted a row in front of the total? So you didn't have to move that or anything. You just hit tab and you automatically get a new total. And your numbers are always going to be, I mean your formulas are always going to increment, which isn't always true in the other, there are just so many neat features. It also adds the auto filter for you so that you can easily sort and filter. So it's also in my book, but it would be a topic that if someday we had just tables as, or just tables and a couple other features, it'd be a fun session maybe next year. Thank you for asking the question. Does anybody else have any questions? Or do you have any more questions? Whoever asked the table question? No, thank you. Anyone? We have just a couple more minutes. Well, since we are here, I know none of you have, oh, go ahead. Oh, go for it. Oh, I was just going to say another thing that many people want to do in Excel is not to always have to type data in in the right order. So you can type it in in any order you want. And then over here where it says sort and filter, notice there's an A to Z, which will sort it low to high. So notice it puts all my cherries, cones, and nuts together. OK, and I can click in whatever column I want to sort and sort either low to high or high to low. Make sure you just click in the column, do not select the column, but just click in the column that you'd like to sort and do a sort. So I want to thank you. I think we've used up our time. I want to thank you so much for coming and hope that you come to future sessions. Hopefully we'll have some next year. Thank you guys so much. We will see you next time.