 Welcome to Excel Tips and Techniques. This is my favorite topic to teach because I can teach all sorts of things that will be time-saving. However, you can pick anything in Excel and say that it's time-saving. So I just want to let you know I got a little carried away in my book. It's probably longer than what we're going to be able to cover. But if you have a chance to read over some of the things that we haven't gone over, I think every single tip will save you a lot of time. Some of the tips you'll probably already know and you'll think that they're basic and some you won't, but in my opinion if you just get a handful of tips that will save you time, you're better off. So we're going to start with this screen that I have some information on. And what I see a lot of people do when I go out and do some one-on-one support is I see people trying to select a range by dragging, which is a great way to go. You just drag down across whatever cells you want. The problem comes in when you start going down beyond a screen, people just keep going up and down and miss their mark. And in the end, they might not even be at the place they want. And it's just very stressful to try and find the end of where you want to be. So rather than doing that, another way of selecting, let's say, notice I'm off by one here. Let's maybe end here. Instead of starting the whole process over, which I see a lot of people do, as long as you have your shift key down, when you click, you can change your mind as often as you'd like. Now, if you take away the shift key, then it's going to take away your entire highlighting. But just know that you can also just click at the starting point and then go to the scroll bar, scroll fully down to wherever you'd like to end, and shift click. So clicking and shift clicking is a great way to highlight everything in between. So you want to click on one end, shift click on the other. Then if you'd like to highlight something that isn't next to your original selection, you can hold your control key down and start dragging across any other ranges. So clicking, shift clicking, and control dragging across ranges are great ways to select. Okay. One of the nice things about selecting, I don't know if you've ever tried to enter things across a row rather than down a column. But as you know, generally speaking, when you type something in and you press enter, it goes down the column. And in order to go across, you'd have to press tab. And some people don't enter as quickly by pressing tab. So the nice thing is if you select the area that you want to enter in, prior to I'm having trouble with my mouth here, if you're wanting to enter, there we go. This is not, this is interesting, sorry. Trying to drag, here we go. As I type, if I type something like January and then want to go to the next cell, if I press enter, notice that it is only going to those cells that are highlighted. And if I want to go back, I can press shift enter. It's also great if you're going to highlight a larger range. So if I want to highlight, let's say, from here through here, I will go down one column and instead of having to reposition when I go to the next column, at this point I can just press enter. So it's a really nice way to enter data quickly. And that's what's on the first and second page, put the hand out if you have that. Okay? When you are entering data, sometimes you want to fill the same data to the right. So let's say that I've got some data in this first column and I'd like my second column to have that same data. I'm having a lot of trouble with my mouse here today. If you highlight that second row and just press control R, control R will take everything that it basically says copy right. And it takes whatever you have in the column just to the left and copies it right. You have that same kind of thing instead of control R. If you want to copy down, you can just highlight and do a control D for down. Okay? That's the thing you can get your mouse to work here. There we go. And if you want to go down many rows, you can highlight your data. I don't know what my problem is, but I'm just going to shift click and press control D and it will go down as many rows as you'd like. So these are some data entry kinds of things. There are other data entry kinds of things that you might like as well. I'm going to go to a different sheet. And while we're talking about going to a different sheet, in the lower left-hand corner, notice you have a left arrow and a right arrow. Depending on what version of Excel you have, Excel 2016, which is what we're using now, did a way with, I don't know if you remember, but there used to be an arrow to the left of that one that had a line on it, which allows you to go to the very first worksheet. I have lots of worksheets in this workbook. And every time I click on this, it only goes back one worksheet at a time. Every time I click on this, it only goes forward one worksheet at a time. So what if I wanted to go to my very first worksheet or my very last worksheet? Well, if you hold your control T down when you click on the left arrow, it goes to the very first worksheet. And if you hold your control T down when you're clicking on the right arrow, it goes to your last sheet. So that brings you to your sheets much more quickly. In addition, one of the things I didn't know until years and years after I taught Excel, and I was actually taught by one of my students, is if you look at how many worksheets I have here, I have a ton of worksheets. And it is not much fun to scroll between all those sheets. So as I point to these arrows, notice it's even telling me that if I right-click, I can see all the sheets in my workbook. That has saved me so much time over the years to just right-click, and then I see all the different sheets. And I can either click on the sheet I want and click on OK, or double-click on the sheet, and it takes me right to where I want to go. So now let's say I've got a sheet like this, and I would like to, in all of these cells, and I'm just control-clicking on all the different cells that I'd like, in all of those cells, I would like to enter the number 5,000. What most people would do is they would enter that number in each of those cells, or they would enter it in one cell and perhaps copy and paste them into the other cells. A much faster way to enter data in lots of different cells is to highlight the cells like I just did. So I just control-click on all the cells that I wanted, type in the number which I said I wanted 5,000, and here's where you want to not press Enter, because Enter will only enter it into that first cell. So if you want to have, excuse me, in this case the last cell, it's the very last one I highlighted which is here. But what I want to do now is press Control-Enter. By holding the Control key down when you press Enter, notice that I've entered it in every single one of these cells. That will save you a ton of time. So if I want to enter the number, let's say, 50 in all of these cells, I just highlight all of those cells, type in 50, press Control-Enter, and I'm done. What if the number that you would like to enter in all those cells is already in one of the cells? Let's go ahead and highlight a bunch of cells. What you want to make sure of for this technique is that the cell that's active, and you know the cell that's active because notice how all these other ones are gray, this one's white, not only that, but notice I'm in L6, and notice my name box over here is saying L6. So there's lots of ways of you being able to tell that this is the currently active cell. You want the currently active cell to be the number that you, or it could be number, it could be text, that you'd like to copy to all the other cells. Now, if you'd like to copy these to all the other cells, what you need to do is be editing that cell. The F2 key, the function 2 key up at the top of your keyboard, if you press F2, notice that my cursor is now blinking inside that cell, which means it's the currently active cell. So at this point, if I press Control-Enter, like I just taught you a second ago, Control-Enter, copy, then if I press Control-Enter at this point, notice that all those cells became 10,900 immediately. How cool is that to be able to enter data that quickly? Okay, so if I wanted the word total here, and I wanted the word total here, again, all I have to do is highlight both areas by Control-Clicking. Okay, type in the word total one time, and then what do I press? I press Control-Enter. Really a great technique for entering data. Okay, so that's on page 3 in your handout. Now, what if I want to copy a value from the formula above? So here I've got a formula, right? Equals some, and it's adding basically these three cells. How do I know where those three cells are? Well, if I just double-click on this, notice that when I double-click on the formula, it actually highlights all the numbers that are in that formula, which is very helpful to see if you've got things going correctly. But on page 4, it says I would like to copy a value. So to do that, I just need to hold down my Control key and then press the quote. Now, quotes is at the top of a key, so I do have to hold Control-Shift and then the quote key. And notice that it is not the formula that got copied down, but rather the result of the formula. So it's the same as if you guys are aware of the ability to copy and paste values. That's what you've just done. You've pasted the value. Now, why would you want to paste the value? Well, you may want to copy it to some other spreadsheet or something that you don't want to copy the rest of the data, in which case if you don't copy the data with the formula, then it's going to just show up as blank in the new sheet. So pasting a value is often really helpful. So that's what I just did, and you can tell it's the value because it says 10,200 up here. So that's a quick shortcut key for bringing down values from the row above. Another thing, if you often type in the date or the time, it's really helpful to be able to do that quickly. So a shortcut key for doing that is the control key and the semicolon key. Oh, excuse me, the colon key. No, semicolon. I was right the first time. So control and semicolon on your keyboard put in the date. And then if you'd like the time, that would be the colon key. So for that, you would just hit control and then shift because they're on the same key, control shift and colon, and that gives you the time. So those are quick time entry key strokes, and they're all in your handouts. So you don't have to remember any of these or write any of them down. The next tip that's kind of fun is notice this grand total. Now what I see a lot of people do to get a grand total, grand total meaning I would like to add this plus this plus this. So what I see people doing a lot is they'll click in that cell and they'll type it in equals. Then they'll click on this cell and hold their control key down, click on that cell, and click on that cell. First you'd have to do an equal sum to do that, or you'd have to do equals to click on this cell, then do a plus and click on that cell. Either way would work, but that's still a lot of time, right? Especially if you had 100 of these to add. Instead of doing this plus this plus this, let's say, and pressing enter, which works just fine, right? Instead of doing that, what you could do is just highlight from the top of your range down through this extra row, which is where you'd like to have your total. So let's go back to the top of my range and down through, so I'm clicking and shift clicking. Okay? So all my data is highlighted and this extra row where I'd like my totals to be is highlighted. And all I need to do then is click on the autosum tool and boom, let's see what it did. Look at that. It says equal sum B23 plus B16 plus B9. So instead of doing all that clicking in two seconds, I got my grand total. And that's on page 5 in your handout. All right. Have you ever had to enter a bunch of data from a worksheet or, you know, just a paper copy? If you have, what happens to me, I don't know about you, but I question to make sure did I type the right thing in. So I would type in, let's say, 1500, press enter. I'd be looking at my sheet of paper. Then I would look back at my screen to make sure it truly was, uh-oh. Hang on. Brian? What's up? I see control off delete on the other screen. It's fine. Just move the mouse. We see it. Yeah, but I won't see it, but that's okay. I'll look at my screen. Okay. So... We see everything just fine. Okay. All right. Sounds great. So what I'm trying to say here is that I would look at my computer, then I would look at my sheet of paper, then I would look back at my computer to make sure that every time I entered something, it would be correct, which is slow down the process of entering data significantly. So there is a tool that a lot of people don't know about called Speak Cells on Enter. And that tool can be put on your Quick Access Toolbar. So if it's not already, all of you, this is your Quick Access Toolbar. I don't know if you've used it before or not, but it's the most spectacular thing that you can do as far as time saving, and we will be going over that. So let's go ahead and right-click on our Quick Access Toolbar and notice that it lets me customize my Quick Access Toolbar. And I'm going to see that if I look down here, I'm not seeing Speak Cells on Enter. Oh, yes, I am. It's at the very bottom, so it was off the side of the screen right here. If you are not seeing it, which you probably aren't because you may not have even known it was there, all you have to do is go from popular commands to all commands. That will show you a list of all commands in Excel. Click on anything on the list and type in an S. Now don't then type in a P to go down to the P's because that's what it will do. It will go to the P's, it won't go to the SP's. So once you get to the S's, you're going to scroll down until you see Speak Cells on Enter. And right here it says Speak Cells on Enter right here, so you would just double-click to put it over here on your toolbar. Now if you just double-click on it, it's going to go, let me take it off by double-clicking. If I only double-click on it, it's going to go to the very bottom of my Toolbar, my Quick Access Toolbar. If you want to move it up, you'll have to hit your up arrow a bunch of times wherever you'd like it. Another time-saving tip, instead of doing that, is to click wherever you'd like it on the toolbar first. Let's say I'd like it here. Then double-click on Speak Cells on Enter. And notice that it puts it right below whichever thing I've clicked on. So put it right below, select visible. I'm going to click on OK. So now I see it right here on my toolbar. And watch what happens. I'm going to hope that you're going to be able to hear it. I'm going to turn my speaker system up. Look what happens. Cells on Enter. With having that verbal verification, I can just quickly type in numbers without ever having to look at that sheet of paper. Okay? Excuse me, without ever having to look back at my computer, of course, you'd have to look at the sheet of paper. To turn Speak Cells on Enter off, I would just click it again and notice it turned it off. So that now as I type it, it's no longer on. And that's on page 6. A new feature, if you are on 2016, is up here. It's called Tell Me What You Want to Do. I don't know if you've used Help in the Past, but help all it ever did was it took you to an explanation of how to do something, which was nice, but it would tell you which tab on the ribbon to go to and which tool to use and that sort of thing. But what if you would like the help function to do it for you? For instance, let's say I'd like to put a comment on this cell, but I don't know how. I click on the cell that I'd like to put the comment on, and in Tell Me What You Want to Do, I click there and I start typing in the word comment. Notice when I type in the word comment, it says would you like to insert a comment? So instead of having to find where it is and all of that, all I have to do is click on insert comment, and right then and there, it's done it for me, and I can type in whatever I'd like. It's sort of a mega help on steroids in that it actually does the function for you if you'd like it to. Now, if I click back in here, notice that insert comment is up at the top because you recently used it and it thinks, hey, maybe you want to use it again. So it will show you the recently used function, and then it will also give you other things that if you'd like to try, you can try something different. Let's say that you'd like to do something like some and you don't know how to do that, or summarize with a pivot table all of those things. If it can do something for you, it will. If not, or if you'd like to know how to do it, you want to go back to that old style help. It always has that capability as well. Now, get help shows you help that's within Excel, and smart lookup on some is something also new that is actually going to go to Bing, the search engine, sort of like Google, but it's Microsoft search engine, and actually look that up and open up a pin on the side of the screen to give you any information that it finds on the Internet. Okay. All right. Any questions? Notice that I've got my comments in there now, and that's tell me what you want to do. Another thing that was new a couple of releases ago, which I really like, is in the formula bar. If you had a huge amount of text or you had a huge formula, you would have to scroll down, or if you click in it, it would actually cover the rest of your sheet. Do you remember that? You click in as it would expand and cover your sheet. Now, what you can do is notice that if I point to the bottom of this formula bar, I can actually, I should be able to drag it down, but there we go. I can drag it down as far as I want, and instead of it covering any part of my spreadsheet, it just moves my spreadsheet down. That is so handy to be able to see a lot of text or a large formula as opposed to either scrolling down or having it cover your sheet. Okay. So that's right here at the bottom of your formula bar. The next thing we've got is the Quick Access Toolbar. This is the Quick Access Toolbar that I was talking to you about before. And I wanted to go over, well, I already kind of showed you how to add things to the Quick Access Toolbar. Another way of adding things to the Quick Access Toolbar is simply right-clicking on the item that you want to add. For instance, if I wanted to add bolding to the Quick Access Toolbar, I could right-click on bold and click on add to the Quick Access Toolbar. And unfortunately, I have so many things on here, you can't see the end of mine, so I'm going to click at the end. And notice there's a bold right at the end of my Quick Access Toolbar. If you don't want that on there, I can right-click and I can remove it from the Quick Access Toolbar. That's easy to add things and to remove things. If you want to change the order of things, though, you will have to right-click and go down to customize the Quick Access Toolbar. That's the thing I can. And customize the Quick Access Toolbar, and then customize it the way that I showed you earlier. I'm not sure why it's not saying that I can customize it. Still not saying I can customize it. I don't know what just happened, because we were able to customize it just two seconds ago. But let's go over some of the things that I have for the Quick Access Toolbar, because remember, all we have to do is right-click and customize to add things to the Quick Access Toolbar. So I'm going to start with some of these items that I really like. So first of all, there's open and new and save. The other thing is that if you do them a lot, you have to go to File and then Open or File and Save or File and Print take way too long. So those are ones that I would definitely put on there. And Save As. Save As is really nice. Notice though that this is a Save As with a down arrow. What's nice about that, there's different Save Ases that you'll see when you bring it in, when you go to add it. But this one allows you to save as a different format. So you could save as a PDF if you want. Or if you have an old workbook, if you save as a workbook, it will upgrade it to the version that you've got currently. So let's say you're opening up a 2013 workbook, you'd like to be 2016, you could do a Save As workbook, and it would save it as a 2016 workbook. Next, I have Send As email. So if I have something on my screen that I would like to send to you, I could click on this and it would immediately open up Outlook and put this in as an email attachment, rather than having to save it, go to Outlook and try and find it. This is the same kind of tool except it's going to take this entire workbook and immediately on the fly convert it to a PDF and send it, which is also very handy. And all of those things you can find to add to your Quick Access Toolbar. This is Closing Workbook, Undo and Redo. This is Freezing Pains. I don't know if you've used Freeze Pains in the past, but that's very handy. Let me show you why you'd like to have Freeze Pains. Let me go to my Freeze Pains sheet. What Freeze Pains allows you to do, notice that right up here I've got my titles. As I scroll down, notice that how they go off the top of the screen, which is annoying when you don't know what is in each column. If I just click on the cell below, the cell that I'd like to have frozen, so if I want to freeze this row up here, I click on the cell A4, come up here now and say Freeze Pains, and notice that as I scroll down, it no longer goes off the top of the page, which is really nice. There's also the same thing for Freezing Pains for going right and left. Let's say I start scrolling to the right and I'd like to always have my date and my account name on the screen. Well, instead then of being in this cell, if I am in this cell, it will freeze everything to the left and above the cell that I've selected. So by selecting this cell, it will make sure that this doesn't go off the screen and also these two columns. So I come back up here and I first need to unfreeze the pane, and then I come up and freeze them. Now as I scroll down, the top row stays, and as I scroll right, the date and the account name stay on the screen. Okay, I'm going to unfreeze those panes again, and I'm going to go back to the left to my navigation. Have you ever wanted to select a contiguous range? I want to select this whole range here. This is the Select the Current Region tool, and what it does is it selects everything until it hits a blank row or a blank column. It's such a nice tool to have if you want to do anything with your current range. For instance, let's say that I didn't have these... Let's say I did not have these formatted. I'm going to go ahead and take away the formatting. So I'm going to come up here and I'm going to take Clear Format. So now I've got this range up here, and I'd love to see these be formatted the same as this. So I click anywhere in this range and select... Excuse me, select my current range, which I've done, and all of you probably already know about the Format Painter, which allows you to copy formatting. It doesn't copy the data, but it copies the formatting. So I'm going to click on the Format Painter, and then just click in this top left cell, and it just copies the formatting. I need to also copy the formatting down here. So instead of just clicking once on the paintbrush, I'm going to click twice. So that's going to... Let me do that again here. I'm going to double click. I'm going to click once here now and once here. How much faster is that to format your data than doing everything another time? Now notice my Format Painter is still ready to paint. So I'm going to click it again to take it off. But that's what this does is select the current region. A keystone way of selecting the current region is pressing CTRL-A. You click in the region, press CTRL-A, and that will select current region as well. You already know about the Speak, Self, Unanswer, which was neat. Another one I have is called Clipboard. Clipboard opens up this area on the side that allows you to copy up to 24 different items at the same time. So if you have a large spreadsheet and you would like to copy this first row, so I highlight the first row once I've got this visible and do a copy, and then I'd like to copy this area. So I hit Copy again. Notice that it gives you little snippets over here. So I can go anywhere now I want in my sheet and click on this first one, click on this second one, and I can do it as many times as I want because it'll stay there even when I exit Excel, it'll stay there till the end of the day or until I sign off is what I should say. So can I only use this in Excel? No. If I go to any other program, let's say I go to Outlook and go to a new email. Notice I've got my clipboard right here. To display the clipboard, all I have to do is click on the little clipboard tool and now come into my text area, click to get the title, click to get the rest. Now the formatting got a little messy on this one, but how cool is that that you can go from any program to any program using the clipboard function. Again it holds 24 different items so you can copy a lot otherwise you can only really copy one thing at a time in Excel and in Word. And that same clipboard is in Word, Excel, Outlook, PowerPoint, all the different applications. So that's this one. The next one is Paste Special. A lot of people don't know about Paste Special and it has some cool things. Let me show you a couple of them. Have you ever wanted to take these numbers? And let's say next year these are going to double. If I just type in the number two in here, normally what you would have to do is you would maybe do a formula that says equals two times this and then drag it across and then you'd have to have your whole area over here that has the correct information delete all of this. You don't have to do that. If I just type in a two here and do a copy and then select whatever it is I would like to double. Now I don't want to double the totals, that'll happen naturally because those are formulas. I just want to select the data and I go back to my Paste Special tool. Notice all these amazing things in Paste Special. One of them says Multiply. Simply by copying whatever number you want to multiply by selecting where you'd like to sort of paste that multiplication and clicking OK everything here just doubled in place without you having to do a formula, do a Paste Special, all of that stuff. OK. And then I'll just delete my, whoops, didn't mean to do that. Delete my number two and I'm ready to go. So did you see what I did? So realistically instead of doubling you may only have let's say 20% increase. So you could do a point two or something like that. You would want to multiply by one point two because you'd want the number plus an extra 20%. But that's a really neat feature with this being able to paste Special and multiply or add. You saw there was add, divide, all that. Another very cool feature with that Paste Special I think is sometimes you get this information and you have it, here we have the months going across the top and the items going down the side. There are times that you want the exact reverse. But in order to make that happen to have the months go down the left and these go across the top, you would have to type every single thing in the entire table again, taking you a ton of time. Instead you could select the current region and do a copy. Then position your cursor wherever you'd like it to go. Go back to Paste Special and go to Transpose, click on OK, and boom! You've just made every heading go across the top and down the left-hand side in two seconds. Now true you may have to change some of the formatting but that is a lot faster than retyping every single number. So that's Transpose and that's under Paste Special. So Paste Special is a really nice tool. We talked about Paste values a little bit earlier. What they do is they take formulas. Notice that this is a formula here. So if I take this formula and I copy it, and then let's say I go to a new sheet. Let's add a new sheet here click on the plus and paste it. Notice it's giving me all zeros which we talked about earlier because the data didn't come with it and it's not able to add the data from the other sheet. So instead, if I go back here and instead of pasting what I'm going to do is go back to my new sheet and I'm going to paste values which is this one here. Notice that instead of doing the formula which gave me nothing it actually put in the values. Of those formulas. The next thing, excuse me, have you ever gotten a sheet from somebody else or even your own sheet and wondered are the formulas correct? How do I see the formulas? Normally in order to see the formulas you have to click on every cell and then you have to look up here and it's hard to see if there's any mistakes. So this next one is called show formulas. Notice there's a quick keystroke for it which is control and tilde. So I can either use that or I can use control and tilde which is in the top left hand corner of my keyboard and it will switch back and forth between showing me my formulas so I can quickly see if they're correct versus showing me the result of the formulas. So these are some of the things that I think are really nice to have on your quick access toolbar. There's more of them but I've got every single one of them put on page 13 in your handout. Any questions on any of those before I continue? Another thing that I like is page layout view which is down here on notice this is normal view which is where you're going to be most of the time. The page layout view is right to the right of that on the status bar. The reason I like that this view is mostly just for putting in headers and footers. It's really cool to be able to just come here, click to add whatever header I want like NJP or NTAB or whatever it is I want you can enter it right into your screen and see what it looks like. So page layout view and the ability to enter headers and footers and that sort of thing right in your screen versus before having to go to insert header or going to the page layout screen is really nice. The page setup screen. Screen capture. A lot of times people want to move or copy data from Excel into Word or into Outlook and they don't like to look at the formatting when it gets there. One way to make sure you get the formatting and exactly the way you want it is to go to whatever program you want like make sure you're in the screen that you want to copy first then go to a program like Outlook and if you want to create a new email let's say go to that email back to my screen it has to be the last screen I saw. Go to this email and under insert there's a screen shot tool that you can then do a screen clipping it'll go back to the last screen you saw simply drag across whatever you'd like to put into your email or into Word let go and notice the formatting is absolutely perfect. So that's screen clipping. Another neat thing in this version it used to be that when you opened up a second workbook in Excel it would occupy this same screen making it very difficult to copy things let's say from one workbook to another but now in this version the second Excel book go to this one opening up the second book look what I can do I can actually move this to this part of the screen and this to this part and notice how I have complete access to all the toolbars everything they're in each in their own windows making it so easy to highlight copy and paste from workbook to workbook going back earlier remember how I showed you to select a current region all we have to do with select current region tool or press control A sometimes you want to go to the end of a line and select simply by using your mouse to do that notice that I've got a border around this cell if I double click on any edge of the border it's going to take me to the end of that range so if I double click on the right hand border notice it took me to the end if I double click on the left hand border it takes me to the beginning if I double click on the bottom border it takes me to the end of the range okay now if I want to select as I'm going I just have to hold my shift key down when I double click and notice that it will select to the end I again can hold my shift key down double click and it selects to the bottom so using that border to select is a really handy tool alright a lot of you have already know about something called the fill handle the fill handle allows you to type in something like January and then go to the bottom right hand corner this is called the fill handle so your mouse should look like a plus I drag it to the right and it fills some people also know that if I drag my fill handle once it's filled to the left it will erase so dragging to the right fills and dragging to the left erases okay what if though what if you would like to create your own heading for instance let's say you have offices in Seattle and Bellevue and I think of some more cities my mind went blank Bellingham okay and every time you create a sheet or not every time but quite frequently you're typing in these same headings over and over and over again and you would love to just be able to click on Seattle drag across and have it have Bellevue is the plot in Bellingham well you can you can create your own what's called custom list simply by one way is you could type it in and you could select the list and the only difficulty about doing this yourself is finding out how to do it and so on page 23 I have the direction that all you have to remember is go to file because they hide it really well go to file go to options go to advanced scroll all the way down almost all the way down to the bottom notice where it says edit custom list click on that because I highlighted it notice B46 through E46 because I highlighted it I can now just click on import and notice it's added it to my list notice is also showing me all the other ones I've added previously now the rule here is I can only add a list that starts with something new so I can't have one list that says Seattle Bellevue it's a plot and another one is saying Seattle and then some other city whatever because it would get confused it wouldn't know which one to use so as long as you have a different starting name or it is that you like in the first position you're good to go so one way is to import which we just showed you even if I hadn't had this highlighted when I came in here I can always now click over and highlight and then do an import okay so you don't have to have it highlighted first I just did you can also if you don't have it in your sheet at all you can click on add come in here and add some new data making sure it's one item on each line so let's say I wanted to do something like pleading enter make sure you enter after every line so pleading worksheets whatever it is and then click on add and it would take it from here and bring it over and you have a new list if I want to add to the list now all I have to do is click on the list come over here add something else and add and notice it added it if I don't want this list anymore I can click on it and press delete and it's permanently deleted so now I just have to click on okay to get out and now no matter what worksheet I'm in I can be in a completely new workbook and I can just type in Seattle and either drag the fill handle to the right or down and notice that it didn't work okay let's look at that again that's kind of interesting what it did was it just copied it instead of using my custom list I thought I hit okay maybe I didn't let's make sure that I did hit okay so I just highlighted again I'm going to go to file go to options go to advanced scroll down click on edit custom list I somehow didn't get I must not have clicked okay I must have hit cancel at this point note to self do not do that click on okay and okay now it should work a lot better let's just click on one of these Seattle drag the fill handle there we go notice that it's telling you what it's doing as it's going so it'll keep going from Seattle to Bellingham and start again so you can put it in as many times as you want but isn't that nicer than having to type these in every single time so whether you're going across or whether you're going down it's going to put in your Seattle Bellevue it's a lot Bellingham of course if I can get a hold of the handle I'm really sorry my mouse is acting so strangely today but it works just great as far as the custom list goes now the other reason a custom list is really nice is let's say you wanted to sort you know how when you sort normally it'll sort either numerically or alphabetically right so what if I wanted to sort so all my Seattle's were together all my Bellevue's were together and that sort of thing but I wanted it in that order I wanted Seattle Bellevue Issaquah Bellingham I did not want it to be Bellevue first okay a good example of that might be Monday Tuesday Wednesday Thursday maybe I want it in that order I don't want it necessarily A to Z I want it in a special order that I create by creating a custom list you can actually use that custom list when sorting so if I wanted to sort these I can now go to sort and filter and I can do a custom sort and instead of just sorting A to Z which is usually your option right if you go to option then excuse me if I go to where is my here we go I click on A to Z click on custom list scroll down click on Seattle Bellevue click on okay or I could do Monday Tuesday or I could do high medium low all of those things that you might not want to do in alphabetical order and notice it says this is going to be my sort order so I click on okay and notice that instead of putting Bellevue first and then Bellingham that sort of thing it is Seattle Bellevue Issaquah Bellingham exactly the way I want it so to be able to sort in the order you want versus sorting in just a plain A to Z kind of sequence is another benefit of these custom lists okay move and copy these are techniques that you might already know but a lot of people do move and copy very slowly so I just want to teach you some neat tools if you didn't already know them if I want to move let's say I'd like to move my next row in between my ice cream and cherries what a lot of people would do is they would potentially insert a row between ice cream and cherries and then move nuts and then delete the row where nuts came from all of that another way of doing it notice there's a border along the side of nuts if I hold my shift key down as I drag that border notice that I have this line that appears that line says is going to move now that it's between ice cream and cherries it's going to move nuts right in between ice cream and cherries so instead of inserting a row deleting a row all of that all I did was I held my shift key down dragged a border to where I wanted it to go let go and it was in place of copying and pasting or inserting or any of those things dragging and dropping is an amazing way to move and copy things if you're not going very far so for instance if I wanted to copy this I could highlight it and if I just drag it it's going to move it you need to make sure that you're on a border point to the border drag and it's going to move it okay if though I want to copy it I can still drag but what I want to do before I let go is I want to hold my control key down notice that when I do next to my arrow there's a little plus as long as my control key is down when I let go of my mouse notice that now I have two copies okay have you ever wanted to have four of the same looking sheets I'm going to open up a new workbook and I'm going to put in four sheets okay and let's say I would like to have the same thing on each of those at least to start with I'm going to type in January here and by the way to change the name I'm just double clicking on the sheet I could either create each sheet at a time which would be a long process or instead I can group these sheets so that I can enter data into them all at once to group sheets all I have to do is click on the first and shift click on the last sheet that I want now if I type in let's say January and drag my fill handle and just because I used it a second ago I'm going to type in Seattle and drag my fill handle notice that instead of only being on the first sheet and me having to copy it to the second and third notice that all the sheets it's like putting carbon paper between the sheets it's on every single one of them now I'll also notice that I just ungrouped the sheets by clicking on them individually so I'm going to once again group the sheets and you know they're grouped also because up here it says group so now I would like to add the word total in this spot and this spot so I'm going to control click put it in both areas type in my word total and does anybody remember what I need to do in order to get the word total in both spots at the same time instead of pressing enter remember I need to press control enter boom both spots at the same time now I'm going to drag across this and hold my control key to drag across this to both of those at the same time but now what I'd like to do is I would like to put in total the problem is with no data in here I can't automatically put in total so what I'm going to do is you remember if I want to just put in some fake data I'm going to select the entire area that I would normally put data in and I'm just going to put in what I call dummy data by typing in a number and pressing control enter by doing that I put my data everywhere and now I can select by just clicking one more row and one more column and click on my thumb tool notice that now I've got totals in here well I no longer need or want my dummy data so I'm just going to shift click back one and press delete in second created a January of February of March and April spreadsheet with all of the headings all of the totals ready to go all at once by grouping my sheets now the problem is what I forgot was I would like to have a first quarter sheet I would like to add all of these and make another sheet and I don't want to do it again well what some people would do is they would copy and paste this to a new sheet have you ever done that and found that when you copy and paste your row heading your column width and your row height are not what you want them to be let's go ahead and do that I'm going to take this data I'm going to select the current region copy going to open up a new book and going to paste do you notice how the column widths aren't correct the row heights aren't correct now some of you may know that if I go to my paste tool that there is a neat tool that it says keep source column width by doing that at least my column widths are correct but I don't know if you remember but my row heights were different and they aren't correct so rather than copying and pasting and having to deal with all of those formatting issues if I just copy the worksheet instead you will have no formatting issues now what a lot of people do to copy a sheet is they will go to move or copy create a copy and click okay there is nothing wrong with that but if you remember that the control key is your copy key if I take my sheet drag it with my control key down notice there's a little plus in this little sheet that I'm dragging let go notice it made an exact duplicate of my sheet and now all I have to do is type in the new name so whether you group your sheets or whether you copy your sheets both of which are so much faster than doing it any other way and so much more accurate okay so grouping sheets is on page 33 now I do have some functions on page 34 page 35 36 but because next month we are going to cover those things I think I'm going to skip that and go on to the next topic which is flash fill on page 39 to do flash fill I'm going to find my flash fill sheet hopefully it's in here let me know if this will be good okay so flash fill is new in I believe 2013 it came out and there are times that you want to take data like Joe Jones and you would like Joe to be in one column and Jones to be in the column to the right of that actually the other way let me see here yeah let me see that the other way around I want Sandy Eileen Rylander let's say to be over here okay so I'm going to start typing Sandy Eileen Rylander and Nicholas notice that the second I start typing Nicholas it says oh it looks like what you're trying to do is take these columns to the left and bring them together called concatenating or bringing together and that's what flash fill does notice it's also showing you a preview of what it's going to do to the rest so simply by typing in the first one and starting to type the second one all I have to do is then press enter and it flash filled it all the way down you used to have to do a concatenate but now and you still can but flash fill will do it for you as well okay there is a little tip on page 39 it says important be careful that numbers that have leading zeros flash fill will only retain those leading zeros if you first form as a column that it's going to fill into as text okay finding special on page 41 let me okay there's another neat thing that's under go to you can either or go to special excuse me go to is an easy thing to get to just by pressing control G and then click on special actually before I'm in the range that I want to be in and then I'm going to go to go to special what I like about go to special is it allows you to do things like say could you please show me my formulas and click on okay and you see how now is highlighted everything that is a formula so if you want to know let's say somebody type the number in here which has happened several times with some of my clients if I go again I press control G special go to formulas and click on okay do you see how it immediately highlights where somebody types over my formula it's such a neat way to quickly find things either go to formulas or go to blank cells or all of these different things go to visible cells this go to visible cells is actually something that I've added to my quick access toolbar let me tell you what go to visible does have you ever taken a column and right click and hidden it or let's say I don't want any of this drag across all of those columns right click and I say hide okay or I need to hide some that's actually let me undo that because I want to do everything except the total okay I'm going to hide everything except the total so now what I want to do is I want to send you just this information so I copy it go to a new workbook and I paste it and it's like I went to all that trouble of hiding it and it didn't really do that right and that's because those columns are really still there and when you went to select you selected everything just by doing that okay so instead I'm going to select it still the same way but now I'm going to go to visible so press go to and you can add it to your toolbar like I said go to special and visible cells only click on okay do you see how this is actually white in here trying to show you that all I'm selecting is this and separately this so now if I do a copy notice also I've got two sets of marquis going around the data also visual clue and when I come to my new sheet notice that what I'm seeing is only those visible cells select visible is an amazing feature that will really help you if you ever hide cells and don't want somebody to see it when you go to a new sheet okay okay if you have a sheet back here let me unhide by coming here and unhide those same columns we're going to look at something called named ranges a lot of people don't know named ranges exist and it's so easy it's so easy to create a named range what is the named range well let's say I would like to highlight this because this is this is my I'm going to use my select here this is my back in robin's in belview let's say so I'm going to select this range and remember this is called the name box to name a range one easy way to do it is just click in the name box first select the range click in the name box and I'm going to type in belview and hit enter then I'm going to do the same thing here I'm going to select the range and let's say this is Seattle now if I have a large sheet and I have a lot of these if I want to go to belview I can just click on the down arrow here click on belview and boom or Seattle click on Seattle and boom this is great if let's say I want to either go there or I want to print it so now I can say print selection and it's a way because you know you can only have one print range so this way you kind of sort of have a print range just by printing selection so these named ranges are great another thing a named range is good for is let's say you have a tax rate and normally what I would tell people is type tax up here type your tax rate up here which might be nine and a half percent and then if you're trying to calculate the tax what you would do is you would do equals you take the total right and you'd multiply it by the tax now when you do that you need to make sure that it's always referring to this cell so I'm going to make this Q1 I'm going to make it absolute by pressing my F4 key notice pressing my F4 key puts dollar signs around the Q and the 1 that makes it so when I fill this formula to the right it won't make it from Q1 to R1 to S1 it'll always point to Q1 okay otherwise I won't have the correct data I drag this across now and it works beautifully right but if somebody accidentally deletes this because they don't see it or whatever then I have a problem right because everything goes to zero so you can use it it works just fine but I also have to make sure that I absolute it to make sure it doesn't go to R1 all those things let's try a different method let's define a named range a named range doesn't actually have to be in your spreadsheet you can just go to these named ranges so the name manager is in under formulas notice here okay but I can also define a name just by clicking on define name here and it named a tax just because that's where I was but I can name it anything I want and instead of having it point to a cell I can say my tax is going to be 0.095 so what I'm doing is I'm naming an amount a name of tax okay click on okay and now I can say my formula can be equal to this times tax and notice as I'm typing in tax it actually even shows up on my popup okay then I can drag this across and it's really nice so I don't have to worry about these cells getting deleted I have that tax rate in here it's also very visible that it's called tax up here and anytime I want to change tax I can go to my name manager I can come here to tax if my tax is now 10% I can take away this 0.95 and make it 0.01 and enter it close and notice immediately everything in there change so using names names ranges and names is a very time saving tool and a handy tool because you can actually read the range names go to places quickly and there's a ton more things you can do with range names as well and I think this is a good place to stop there are more things in your handout as I said earlier not much though we covered almost everything for any questions I definitely also wanted to let people know that I just dropped in the chat the Excel formulas and functions advanced session that is coming up that CND is putting forward it is on May 10th same time anybody have any questions because if not I do since there is only well there are two other topics but I really would love to do one last one which is called data subtotaling or subtotals let me see if I have a there we go data subtotals is so handy and a lot of people don't know about it and so a lot of people who pivot tables which are great tremendously time saving functions but data subtotals is a lesser known incredibly easy to use tool let's say so I've got this spreadsheet that's just in a list form I've got date, account name, invoice amount that sort of thing and now I want to see maybe I'm at the end of the quarter and I want to see buy account how much was billed to each account this in this year or however much I've got here what I can do instead of trying to figure out a way to do it myself all I have to do is sort by account if that's what I'm trying to do is see buy account I would sort by account so I would click in the account space and I would click A to Z to sort okay now I've got it sorted by account after that on getting subtotals you would go to the data tab and you would go to subtotals just over here data subtotal and all I have to do is say for each change in and we had decided it was going to be by account and then what do I want to do do I want to add count average I want to add and I want to add quarters I want to add amount right click on okay and in 2 seconds I've got all my subtotals now maybe I just want to see the subtotals then I can click on the number 2 and it only shows me the subtotals or number 3 will show me everything and number 1 will show me only the grand total so data subtodaling is such a handy tool and I can add a total by absolutely anything if I want to get rid of the subtotals I can click on subtotal again and remove all and I can subtotal by something else like maybe I want to subtotal by quarter well if I want to subtotal by quarter click on the A to Z got my 1 through 4 here and I go to subtotal and this time instead of by account I'm going to go by quarter and I still want to subtotal the amount click on and maybe I only want to see the subtotals click on 2 I see my subtotal per quarter such a cool little tool called data subtotal okay now I'm going to be open for questions does anybody have any questions we have about 10 minutes or so for questions 5-10 minutes it can be on anything Excel related no questions on anything recovered or anything that you find cumbersome because if in Excel you find anything at all cumbersome there's a faster way how do you add a formula to the top navigation bar like a particular formula not 100% sure that I understand the question add a formula to the top navigation bar what are you considering the top navigation bar are you considering it the formula bar I'm not sure exactly I guess is the formula bar that you've got there has some specific formulas there is there a way to edit those which one show up the formula bar are you talking about the quick access tool bar this is the formula bar right here okay so the quick access tool bar has particular formulas on it correct it has particular commands on it not formula I did put in these multiplication division addition subtraction friends that sort of thing here so that if I want to point and make a formula so let's say I want to say equals this plus that minus this it means that I can stay on my mouse the whole time without having to use the keyboard at all so I don't know if that's what she's talking about but formulas in and of themselves unless she's talking about functions maybe she's talking about functions or he can have different functions like some storage max min that sort of thing if so I put this on there now has these and you can click on more functions to go to more functions that might be what they're asking not formulas but functions is that true I'm not sure another restatement is how to add formula to an entire or this is another question how to add a formula to an entire column without dragging only to range of rows or content not the entire sheet yeah so thank you for asking I believe that is one of my tips in my handout that I obviously glance past so thank you for asking that question so this would be what you're talking about if I can not okay so here we go I'm going to delete those so I put my formula in here and now I'd like to have it go down the column all I have to do is come over to my fill handle and instead of dragging that just double click and it will bring it down as far as the column to the left of it so it won't do it further than the column to the left okay so all I did was come to the fill handle and double click any other questions I've got another one here just a second how do you get to the bottom of the page quickly without having to scroll yeah great question that actually we covered under the navigation which there's different ways there's one that I really like because people seem to like mice or the mouse I should say let me go to a little larger like the freeze pane here we go so if I want to get to the bottom and you've probably done this accidentally certainly I have is if you double click on that bottom border and double click that will immediately bring you to the first blank cell so assuming you don't have blank rows in between it's good to go if I double click on the top border it brings me back to the top except then I click again so that would be a mouse way of doing it but if you want to do it a keyboard way of doing it holding your control key down and hitting your down arrow will bring you to the last of the the bottom row control right arrows and control and any of the arrow keys bring you to every part of your spreadsheet of the filled data the worksheet where you entered the months by grouping for all sheets if you want to make that a total page for all months how do you do that are you asking when I did January February March how do I total it make a consolidate yes a first quarter that would be the consolidate function and don't know if we quite have the time for that but let's try my consolidate area so what I had was okay so what I did here is just so that we already have the numbers put in I have a first quarter, second quarter, third quarter, fourth quarter instead of a January February and I have a yearly total so the first thing you want to do and this is really a top again and of itself so but anyway it's going to be a little quick but so the first thing you want to do is make an exact duplicate of the spreadsheet that you have the data in so again if you hold your control key and drag you know and then take out the data to make the yearly totals one okay and then once you have it assuming that you have the same labels going down the left and the top so that everything that is bringing in is in the same position then you can just go to the consolidate which is under data so I highlight what I want to bring in you don't want to bring in the totals that'll automatically happen highlight the data click on consolidate you want it to come then you want to come in here for reference you want to go to the first one or whichever one you want to actually bring in so I'm going to click on first quarter and I'm going to select the data that I want to add click on add going to go to second quarter and it's already got it highlighted for me how cool is that click on add go to third quarter click on add now oh excuse me got fourth quarter click on fourth quarter click on add and so remember I started in yearly totals highlighted where I wanted the totals to go and then went to each of those sheets and then all I have to do at this point is click on okay now before I click on okay I want to show you a couple other things if everything wasn't in the same place maybe one year you didn't have cherries maybe you had something whatever it is then you're going to have to use the labels so if you have extra or less rows or columns you want to make sure if you have less rows you're going to want to use the less columns if you have different things across the top you're going to want to use top row but what it does is to match up the numbers in the right place from sheet to sheet if it can't count on them always being in the same place it then looks at it matches up all the values or it matches up all the ice creams or whatever to make sure it's right these are important only if your data isn't in the same place and then if I now click on okay it's going to give me totals of everything but if I ever change any of these it won't be reflected in the yearly totals if I wanted to change I would have to say create links which would mean if I then change this data it would change yearly totals so it really depends on what you want to do people don't want them to change because at the end of the year no matter what happens they don't want it to change so I'm going to just leave it like this click on okay and zoom notice it brought in all the totals and all of these are now correct so I want to thank you guys for coming that is the consolidate data consolidate command you can find out a lot more by telling me what to do but we quickly went over it just so that you know it's there it's you know there's so many amazing features like that that will save you so much time I want to thank you and hope you come to the formulas because formulas will save you a ton of time even if you say but Sandy I never add I never subtract I never do anything like that well maybe not but maybe what you're doing is you want to compare you know does this column compare correctly with this column or how do I bring these this data together or separated or whatever there will be functions that you will learn about that will help you with data just as much as there are functions that will help you with numbers so I hope to see you next month and thank you so much for coming thank you so much Sandy greatly appreciated