Inside Excel - Learn 23 Essential Excel Skills the Pros Know | Steve McDonald | Skillshare

Inside Excel - Learn 23 Essential Excel Skills the Pros Know

Steve McDonald, Excel and Photoshop Geek

Inside Excel - Learn 23 Essential Excel Skills the Pros Know

Steve McDonald, Excel and Photoshop Geek

Play Speed
  • 0.5x
  • 1x (Normal)
  • 1.25x
  • 1.5x
  • 2x
18 Lessons (2h 15m)
    • 1. Intro - Know What the Pros Know

      1:35
    • 2. Quick Calculations with the Status Bar

      3:38
    • 3. Insert Multiple Rows or Columns Easily

      4:52
    • 4. Hide and Show Rows, Columns and Entire Worksheets

      3:00
    • 5. Freeze the Top Row of Your Spreadsheet

      3:11
    • 6. Sort and Filter

      4:44
    • 7. Flash Fill and Auto Fill for Fast Data Entry

      5:25
    • 8. Shortcut Keystrokes the Pros Know

      8:47
    • 9. Shortcut Keystrokes Using the ALT Key

      4:57
    • 10. Absolute vs. Relative Cell References

      6:46
    • 11. Paste Special

      16:24
    • 12. Conditional Formatting

      8:46
    • 13. Layout Pages and Print Like a Boss

      4:38
    • 14. Find and Delete Empty Cells Located Outside of Your Data

      5:46
    • 15. Use ROUND to Fix Money Math Errors

      13:23
    • 16. Use Tables Like a Pro

      19:58
    • 17. Use INDEX/MATCH Instead of VLOOKUP

      9:37
    • 18. Mail Merge with MS Word and Excel

      9:19
  • --
  • Beginner level
  • Intermediate level
  • Advanced level
  • All levels
  • Beg/Int level
  • Int/Adv level

Community Generated

The level is determined by a majority opinion of students who have reviewed this class. The teacher's recommendation is shown until at least 5 student responses are collected.

303

Students

--

Projects

About This Class

You don't have to know everything there is to know about Excel. But, there are a few things the pros know that you'll want to know, too.

Most of these skills are easy to learn and they will enable you to use Excel in a much more powerful way.

If you use Excel regularly but you don't feel the sense of confidence and efficiency that you wish you did, this course is for you. It's designed to teach you the 23 Excel skills that will give you the biggest advantage with the least amount of effort when you use Excel.

Based on my research and experience, these 23 Excel skills are the most important and useful skills you can learn beyond the basics of creating a spreadsheet.

Most of them are easy to learn, and offer huge returns in efficiency for the small time they take to learn.

A few of them will take a little more practice, but I've included them because they can be immensely powerful in ways that many experienced Excel users don't realize.

You know how to use Excel. You've get the basics down.

Now it's time to learn the 23 essential skills the Excel Pros know!

Meet Your Teacher

Teacher Profile Image

Steve McDonald

Excel and Photoshop Geek

Teacher

Learning is easier if you are given the right tools and instruction. In every one of my courses I take you step-by-step through the tools and knowledge you need to accomplish your goals. 

My talent is taking complex subjects (like Exce... See full profile

Class Ratings

Expectations Met?
  • Exceeded!
    0%
  • Yes
    0%
  • Somewhat
    0%
  • Not really
    0%
Reviews Archive

In October 2018, we updated our review system to improve the way we collect feedback. Below are the reviews written before that update.

Your creative journey starts here.

  • Unlimited access to every class
  • Supportive online creative community
  • Learn offline with Skillshare’s app

Why Join Skillshare?

Take award-winning Skillshare Original Classes

Each class has short lessons, hands-on projects

Your membership supports Skillshare teachers

Learn From Anywhere

Take classes on the go with the Skillshare app. Stream or download to watch on the plane, the subway, or wherever you learn best.

Transcripts

1. Intro - Know What the Pros Know: Hi. Welcome to inside Excel, where you'll learn the skills that excel. Pros know my name is Steve McDonald. I've been teaching on unit for years, and in this course I'm very excited because this course is going to teach you the skills that excel pros know that will make using excel easier and more efficient and more enjoyable for you and just make you feel more proficient with it. This course is for you. If you're a beginner who has taken one of my other courses or if you were experienced with Excel and you're ready to take your skills to the next level deep in your skills, go inside, excel and really round things out and really start rocking and rolling with Excel. In this course, I'm gonna teach you the most important excel skills that everyone should know. And I'm going to start with the easiest ones and I'm gonna move forward to Maurin Intermediate and more advanced skills. And some of them you may recognize. But even if you do, I'm sure that you'll learn some techniques and ways of using these things that you haven't thought of before. There will be something for everyone in this course, actually, quite a few things for everyone in this course, and I know that you're gonna enjoy it. Get a lot out of it. So if you're ready to feel like a pro when working with Excel, I encourage you to jump right in. Start the course and I'll see you inside. 2. Quick Calculations with the Status Bar: okay. And this lecture, we're gonna talk about the status bar, and the status far is a really simple and quick tool that can give you a ton of information without really doing anything. And the status bar is this guy down here and way over to the left. Here, it just shows your ready message what mode you're in. It shows your macro button that you can click to begin recording a macro. And over here it has your different page layout. So here's your normal view. Here's your like print page layout, and then this one shows your page break previews, all of which are very helpful when you're printing or laying out your page and then you have the zoom over here and this is just a slider that you can zoom back and forth. Okay, let's go back to normal view, though What I really want to show you is in this blank section right here where it's gonna show you formulas. And first you have to have some data selected. So I'm gonna go and just select this row right here with this call I'm rather And now look down here and see what we have happening here, so you can see right here we have the average of the selected cells, so it's actually adding up all of the cells in that we've selected, and it's taking the average of them. Over here we have the count, which is basically the number of selected cells that contain data, and it shows eight of them eight cells. And then finally, we have the some of the selected cells and you can see it's $5790 just like in our total here. And this is really useful because if you just want to do some quick calculations in your data, you can do that without having to create a formula anything. So you know, the real simple is like if I want to know the total of these two cells, I do that. And bam, I have a sum of two. You know, if I want to know a total of my entire spreadsheet, I could just click on the select all button. It's going to select my entire worksheet, and it's going to give me all the totals of everything in the worksheet, but you can do one cell. You can do a whole variety of cells. You can press control and select a number of different cells up. Cited me to click off it, control and click these cells, and it will give you the some an average of just those selected cells. Okay. And then finally, to go back to here so you can see some numbers. If I go down to my status part I right click. You can see all of these options for the status bar, which I think most people are not aware of so I can get rid of the zoom. I could get rid of the slider. I can get rid of the some function I can add in other functions. I can put in the men or the max, and you have a whole bunch of other options that you can put here to customize your status bar. But just to give you an example, I could add the men in there. So now it's giving me the minimum number or the smallest number in this list, right? And if I want to get rid of that again, I could just uncheck that, and now it's gone. So that's just a really quick tip that will help you to do lightning fast calculations in your spreadsheets, using any selection of cells, the entire sheet, a couple of cells, a column, whatever. Thanks for watching. 3. Insert Multiple Rows or Columns Easily: okay. And this lecture, we're gonna talk about how to insert rows and columns into your spreadsheets quickly and easily, and I'm going to show you some shortcuts and tricks, ways that you can insert multiple rows and columns. And how to, for instance, insert every other blank row just to make your work a lot easier. So we'll die right in first. I'm going to show you a little cheat sheet here. These were just some keystrokes that are gonna help you. But before we get into these shortcuts will use just our mouse to get some rose in here kind of the old fashioned way. And then we'll build on that to show you some cold tips and tricks. So the first way to insert a row is just to click on that row. You click on the road number, it'll highlight the entire row, and they're gonna right, click and click Insert and bam! You have a row. Pretty basic, pre straightforward. What's controls either get rid of that. You could do the same thing with a column left. Click on the column number right click and click insert. You have a new column. You'll notice that When you insert a column within a formatted range, it will keep that same formatting, which is really cool. Let's say we're just clicked in a cell here and we want to select the whole row. This is where this shortcut right here is gonna be you hit shift space bar, and that selects the whole row. Then you can use this shortcut. Ault. I are. So when we had Ault, it's gonna bring up all of these shortcut keys. We're not going to use any of the ones up here. We're going to use I for insert, but I and then it's still waiting for another command. You can see from the notes that says, up here we had our our and inserts of wine grow. You'll also notice this little drop down here. It gives you some insert options. And if we click on that, it'll give you some formatting options. So you conform at the same as the cells above or the row above. You conform at the same as the rows below, where you can clear the formatting entirely so it does its own thing. And that could be really helpful, depending on where you've inserted your row. If you don't want to use that, just you click there. Click off of it, and when you click back into this space, it'll go away. So let's undo our insert row. And this is where the fun begins, because you probably already knew that. But the fun begins when you go ahead and select, say, 123456 rows and you go to your insert row. Now it's gonna insert multiple rows, so this could be very helpful when you need to insert more than one row when you're doing a lot of work. Are you doing this every hour at your job? And that's what a lot of people don't know. You could do the same thing with columns If we select all three of these columns and insert , that's gonna give us three new columns. Okay, Control Z to get out of that and then finally, And this is kind of my favorite little trick is if you select this row and then you press the control key and click on each of these rows individually, gotta make sure you actually click on it like that, and then we do Altai are again or right click and click insert row, but we're going to Ault. Ah are now see what happens. It does a row every other row. So each row that I individually selected it adds a row. And actually, I missed one down here, but you get the idea, but you can see the point of how efficient that could be if we needed to say, Put sub totals or or some other information in between each of these rows that's gonna help you a lot. And you could do the same thing with columns like Into Control Z. Select that press control Bam, bam! Let's go all the way and then you could do your fault. I see or right click and hit insert. You see that through a column in between each one, and you'll notice some of the formatting on. This one is weird with the columns, so you're just gonna have to be aware of your formatting if you're doing that. But that's a quick and easy way to do that. Okay, so that's how you insert rows and columns, multiple rows and columns and every other line rows and columns. Thanks for watching 4. Hide and Show Rows, Columns and Entire Worksheets: okay. And this lecture, we're gonna talk about how to hide and unhygienic rose and columns as well as entire worksheets. This is a quick lecture, but this is really important and very useful. This can help you to visualize your data, but it can also help you to hide unwanted data when you are printing. Okay, so first of all to hide, Rose, you simply go in and highlight the rose You want to hide. So let's say we want to get all the Cabinet information hidden, and then you right, click and you just go to hide. You notice it's going to get rid of it, and you can see it goes 12 and the jumps to five. But it puts this little bar here and you'll notice when you hover over your normal dividers between the road numbers, you get a dark black line. You get that same dark black line here. But if you go down a little further, you get a double line. And if I double click on that, it on heights, everything okay? So again, I can right click and go hide, and that I could double click toe on hide it or I can also select these two rose on either side of where my Rose Air Hidden. I can right click and say Unhygienic, and they'll reappear. Same thing with columns. Um, let's say I just want the materials and the total costs I go hide simplifies my spreadsheet , but you can go select the columns again and go unhygienic. Or you could just get the two double lives and double click toe in Hide it. Okay, so that's how you hide and un hide rows and columns. And then, if you want to hide an entire sheet like, let's say, on sheet to here we had a bunch of calculations behind the scenes calculations that applied to this sheet, which is more of a user interface or a presentation piece for, say, your board members. Right. But we want to hide sheet to then we just right click on it and say hide, and that brings us back to sheet want and sheet to is hidden. But the question is, what's it's God? How did we get it back? Well, it's just a simple is I'm hiding rows and columns. You right click on it, click on Unhygienic. It's gonna give you a list of sheets that are hidden. If you want sheet to unhitch hidden, you make sure it's highlighted. Click OK, and she too is back. Okay, so that's how to hide and unhygienic rows, columns and entire sheets in your workbook. 5. Freeze the Top Row of Your Spreadsheet: Okay, let's take a look at how to freeze the top row and or the left column of your spreadsheet. This is great for saving a lot of time and hassle. And if you're going to be sending a spreadsheet to a co worker or sharing a spreadsheet, it's good to enable this to make it easier for them to work on it as well. Okay, freeze panes is great when you have headers and a column down the left that you want to always be able to see. So, for instance, these sales rep names over here. As I scroll to the right, they disappear. So what? I'm getting into data that's over this way. I lose the view of that, and that's a problem. The same thing happens with a range we have scrolled down. Then those wouldn't stick to the top. So the way to fix that is to go to freeze panes, Frieze bans, and we couldn't freeze all of them based on where were selected. We can freeze just the top row or just the first column. So first, let me show you how to freeze the top. Just click on that. You don't see any difference yet. But as I scroll down, see how they're locked to the top, Which makes it really handy when you have huge, long lists of data. Because I just scroll down, you're not gonna lose track of which month you're looking at. To get rid of that, you just go in and say Unfreeze panes. And now it's back to normal. Okay, Now, if I want a freeze, just the sales rep names. I gotta freeze pans and I say freeze, First column. See, that little line appeared Now, as I scroll to the right, the stay right there. So as I'm looking at the month of August, I could go down, say 7 25 Ok, that's Jane. Okay, so that's really helpful. Let's go and select. Actually, let me show you a different example real quick on the swan. Let's go ahead and put our cursor right here, and we're going to do freeze panes, and we're gonna say Freeze all pains, actually. Sorry. Let's put our cursor right here. We're gonna freeze all pains. The top button that freezes it right where your cursor is. See the line going this way and longer in this way. This is a small example. So it doesn't really matter, but you'll see as we scroll, see how material the materials list stays locked and the rest of the range scrolls when we go to the side, same with the top. It's going to stay locked right where this line is. So we're headers. Stay locked, and as we scroll, it just rolls right up into it. Okay? And then again, to get rid of that, you just say unfreeze panes and it will behave normally as you scroll. Okay. Okay. So that's how you use freeze panes. Toe lock the top row or the left column of your spreadsheet to make it easier to keep track of what you're looking at when you're way down your spreadsheet. 6. Sort and Filter: in this video, we're gonna be looking at the sort and filter. So let's go get some data in a worksheet Here, Here we have, ah, table with our employees. Their first name, last name, area code, phone number, offside. The date of birth, Employment status, higher date sorting and filtering is a really quick way to get to the data that you're looking for. And when you have a table set up, you have these little drop down filters. But even if you just have a range here, you can use these tools up here. So the quickest way to do it is to just select into a cell in a column within the cell or in the heading. And you just go up to here and you consort ese or lowest to highest, which is how it's already sorted and noticed. There's a little arrow now that appears, that shows that this is being sorted where we consort Zito A or highest to lowest. And when we do that, you'll see that all of our listings flip upside down, starting with the highest employee number and going down. Okay, if you're finished with that and you want to go back to normal. We get rid of those filters, you can go clear and that will clear. But you notice it didn't reset it, so that one is at the top. OK, so if we want to reset it and we have to re sort it a dizzy and then clear the filter So that's a simple way. You can also sort here, and this allows us to put in multiple criteria for multiple different columns. So, for example, let's say first, we want to sort people by their office i d number. So we put all the offices together, we go sort by, we click on office, I d number. We're gonna sort by values. You also have some other choices here, but we're gonna sort by values. And they were going to go smallest to largest. And first let's just click, OK, so you can see what that looks like. Okay, so you see, now there's a little up arrow in the office. I d dropped out because it's sorting from lowest to highest in the offices there. Okay, Now we could go back into our sort and we could add a level we could go by last name do my values and a dizzy again and click OK, and actually, you'll see that nothing changed. So let's go ahead and do a different sort. We'll go ahead, remove this level at a new level. Let's sort them according to whether they're part time or full time. So in our office, I d three will put the full time people first and the part time people second. Okay, so go to employee status, sort my values So dizzy because gonna put the f before the tea click. OK, and now you'll see we have some more organization over here. So in the threes here you have all the full time people first and then the part time and in the full wars, same thing, full time, full time, part time, part two. So that's what you can sort using multiple criteria in a table. Arrange like this. Let's look at filters. With this button, you can toggle the filters on her off. And, of course, when you click on the drop down here, you have options to filter here. Now, the cool thing about filters is that you can actually remove things from the list, depending on what you remove. So let's take another quick look at our list. So let's say let's stick with their part time full time here. Let's say we only want to see the people who are part time in our table. So we go to the drop down on the employee status. You can see we have the choice of select all which is selected right now where we can de select all we can just select part time, which is what we want. Just part time people. Go ahead and click. OK, you see, that removes all the full time people. Okay, You know, it's now it has this little funnel up here which is showing that it's being filtered. Okay, we could go to change that to full time. People only look OK. And now it's filtering out all the part time people. Okay, If you want to get rid of those filters and sorts, you just go clear, and that's gonna clear all your filters and sorts and put you back to normal. But you'll notice it's still left it sorted in chronological order. According to the office, I d. Because that's the last sort that we did and they also have the option of advanced sorting , which we're not going to get into for this lecture. That advanced sorting allows you to just basically Adan MAWR criteria you can filter with and and or criteria and just dig even deeper into your data. 7. Flash Fill and Auto Fill for Fast Data Entry: OK, in this video, we're gonna be talking about Flash Phil and auto fill how they both work and what the differences. So let's start out with flash fill. Flash fillers just excels way of helping you to fill out cells using some basic intuition. And let me give you an example here. So we have this first name column, last name column, and then we have a mailing label name. Where we gonna combine the first and last names? And you could use a formula Lykken Captain Eight or something to put this together as well . But Flash Phil makes it super fast and easy. And what we do is we just click into ourselves. We start typing. So obviously the name is gonna be Steve's. No. When we hit, enter and we start typing. Sam, George is you can see what it does. It basically uses its intuition to say, obviously, just put a first name and a last name together, and you're starting to do it again. So you're probably going to do this for the whole list, okay. And if we want to keep that, that would just hit, enter, and it populates everything. If you don't want to keep it, and you just keep typing And it will essentially stop bugging you about filling out all these things. And you can do it manually. But that you'll notice do that. We have a flash feel dropped down here. We can see some options. We could undo that. We could accept it, but it's already accepted where you can select all five of the cells. Okay, so that's one way to do Flash Phil. Let me just delete these and I'll show you another way. You can also click on this cell and we're gonna go ahead and right click on this and drag it down, release it and that you'll notice it has different Phil options over here and we can go to Flash Phil. And again it will predict what we wanted to do. And flash Philip. Okay, we'll go control Z. Undo that. There's one last option where you just go to Philip here and hit Flash Phil. And once again it's gonna populate that. Oh, and there's one more. Let's do controls the one last time we just hit control E. That's the shortcut. In fact, if you look up here, you'll see that it shows control E right there as a shortcut. Okay, so those are a couple of ways to use Flash Ville to just make data entry a lot quicker and easier. Okay, let's talk about auto fill. I don't feel a similar, but a little bit different, and we'll go to a new sheet here for auto fill. I'm gonna widen up these columns just a little bit. Auto feels great when you want to enter data. That is a repeating pattern. So a really good example is a type Monday, and then you grab on the selector handle, you start to drag down. You'll notice that it says Tuesday, Wednesday, Thursday. In other words, Excel is predicting what you wanted to dio, and it automatically populates it for you. You can do the same thing with numbers if you type one to and then select them and start going down 345 You know, etcetera. You could do the same thing with dates I put in a random date. February 3rd, 2001. I let it drag it down. See, I go 6th 7th 8th 9th all the way, and it just fill in the States. And then finally, you can do this. Just if you want to repeat the same thing over and over, like just the word name, for instance, select it and drag it down, and it's gonna auto. Fill that name over and over and over. You do have some options with the auto Fila's Well, you can do the formatting Onley. You could do it without any formatting where you can skill regular flash ville. But if we wanted to, we could put name and date, and then we could auto fill these and watch what happens now say name, date, name, date. So I could fill this all and with alternating things. And that is a recognisable pattern for Excel. So little follow that. So that's a used auto fill. And one thing to notice. If you don't have flash Phil activated or it doesn't seem to be working, you can go double check that it's work and go to file Good options advanced and under editing options, it's going to say enable auto complete for cell values and automatically flash Phil, so make sure those were checked. Click OK and then you should be able to go back I light it and do your dragging, and it will continue to auto fill for you. Okay, so that's how to use Flash Phil and auto fill to make your data entry quicker and easier. 8. Shortcut Keystrokes the Pros Know: in this lecture, we're gonna be talking about excel shortcuts and I'm just going to use this spreadsheet here. Just has a list of sales reps, their total monthly commissions, whether they hit their monthly sales target and their total monthly pay in the sales region that they're in. I'm just going to use this spreadsheet as an easy way to show you a bunch of these shortcut keystrokes and how they work. So the 1st 1 is a shortcut for the auto some and the way you do a quick auto summons. You hit Ault and equals. And just like that, it inserts your auto some formula. If you hit, enter there it is that leads me right into my next one, which is Control Z. If you want to undo something that you've just done, you hold down control and it Z and it does. It controls. He is a favorite of a lot of people who use excel because most of the time, if you make a mistake, you can fix it by clicking Control Z. You can also read Do it by pressing control. Why? And that will redo what you've just done. Done. If you want to delete a column, you can select the column and click control minus, and it will take it away. And again, I'll click Control Z to undo that. If you want to add a column you press control plus on your keyboard, or if you don't have a number pad, you press control shift plus and that adds a calm will control Z to take that back. If you want to make something bold, you press control be and it makes it bold. You can press control, be again toe. Unmold it When you're working with a formula, you could make absolute cell reference by selecting the cells that you're referring to and clicking f four. And it will add these little dollar signs, and you can see that this one has already been done. But if I hit F four again, it will make just the row absolute. I hit it again. It will make just the column absolute. See how it says dollar sign F. So it's referring to the F calm as an absolute cell reference, and then if you hit it again, it will make it a relative cell reference. And then I hit a final time, and it brings it back to an absolute reference like I had it. But you could just hit the F four button and toggle through the different cell references. That way, if you select a range of data and you hit F 11 it'll automatically create a new sheet with a chart based on the date of you've selected. So you can see here it's taken our sales reps and charted them. According to their monthly commission. If you hit the F one key, it'll bring up the help feature. If you select a cell and press control shift one, you'll format it as a number you can see. Right now. This is formatted as accounting, but if we hit control shift one, it changes it just a normal number, of course, all control Z to undo that. To insert a hyperlink, you press control K. It brings up an insert hyperlink box, and you can enter the address of the hyperlink you want there. Another useful shortcut is if you click into a formula, so a double click on this. I can evaluate a part of the formula so I can look at this first section of this formula. And when I press F nine on that section, it analyzes the formula and tells me what that part of the formula is calculating. So when you have longer, more complex formulas, this is a really great way to look at pieces and parts of the formula so you can analyze it and troubleshooting. And again you can see that that was C six equals no and see six equals. Yes, which is why it when I hit f nine, it came back. It's false. Another really useful shortcut as I can open the create table dialog box by just being clicked anywhere in this range. And then I press control t and it immediately brings up the create table box. If I wanted to create a table out of that or so, just click OK, and there I have a table control Z to undo it. If you want to quickly move between worksheets, you can just press control and page up. That will take you one worksheet to the left so you can see this is the chart we created a few minutes ago or you compress control page down and that will take you one tab to the right on your worksheets. And if I continue to do that, that will take me to a blank sheet that I have shed three and then page down again will bring me to another worksheet where I was playing with some conditional formatting and to get back to where I won't want it to be, I press control page up twice, and that will bring me right back to where we were. If you press control one, it will bring up a dialog box to format cells. And, of course, you can do your number alignment for border Phil and protection formatting. If you press control A, it will select your whole active worksheet. So Control A is basically a select all click on any cell to UNSA licked the entire worksheet. You can press control F that will bring up the find and replace dialogue. So if I wanted to find all of the entries where Bob existed and replace it with Robert, I could just say, Replace typing Bob, Change to Robert. Look, replace all says, All done. We made one replacement, okay, and you can see it's now changed to Robert Control Z undo that if you want to close your entire workbook, you press control W. Of course, it will ask you if you want to save it. If you want to save your changes, click save. If you don't click, don't save in this case, I'll click. Cancel because I don't want to close it. One of my favorite keystrokes isn't necessarily a shortcut, but if you're entering a formula, for instance, you're entering a some formula down here, you type in equals s, um Instead of entering your parentheses manually, you can just press the tab button, and it will automatically enter the parentheses and put the cursor there so you're ready to select yourselves. If you want to copy a lot of information from certain cells, you highlight whatever you want to copy. Press control. See, see highlights it and then you click wherever you want to paste it and press control V that'll paste it. Undo these for a moment and highlight these again. Press control X two cuts. Select where you want to go and control V and you'll cut it and paste it. So see how, in this case, when we're cutting it actually cuts it out and puts it in the new spot controls. Eat on, Do it. When entering a formula, you'll notice that I select my formula, and when I hit enter it drops me down to the next line. If I wanted to stay there in that same cell, Aiken type s, um, select my cells and then press control. Enter and you noticed that now I'm still selected in the same box without moving down to the next line. So if you want to do edits in there or do formatting on that box without having to grab this and re select it than control interests, way to get a fairly obvious one. But a simple way to select cells is to press the shift in arrow keys. Right Arrow keys takes you one column to the right down Arrow key, takes you one road down and the up and left. Syracuse discussed the opposite direction That's up, and that's left. You can also press control and right arrow keys, and that will take you to the furthest right cell in your range. So I press right goes to their breast down. It'll be the furthest down cell press down again. I'll take me all the way. The bottom press up again. Press left and it will take me all the way over. Press down and so if you press control s, it will automatically save your document Press F 12. It'll open the save as dialog box if you're in a cell and you want to edit it rather than double clicking, you compress the F two button and it will drop your cursor into the cell, seek and then start editing. And then finally, if you're selected inside a region or range, you press control Asterix. Or, if you don't have a number pad, you press control shift. Eight. It will select the current region or range that your cell is within. So those are some shortcuts and keystrokes to help you navigate Excel more quickly and to be more efficient and effective while you're using Excel, when you're spending a lot of time working in excel and doing some of these things over and over again, these keystrokes can save you a whole lot of time. 9. Shortcut Keystrokes Using the ALT Key: okay. In the next few sections, we're gonna look at a whole bunch of additional functions that are just going to round out your Excel skills. But in this lecture, I want to introduce you to the altar shortcuts because I use them a lot, and that way you'll have a solid understanding of what I'm doing now. When you're using Excel, a lot of people will take the mouse and go to wherever they want to click, and they'll go to menus like these and click insert and then click again. Insert column or insert rose or insert sheet, and they will go back to your sheet and continue typing. But the thing is, your fingers are already on the keyboard, typing numbers or typing data into your spreadsheet. So it's much easier to use keyboard shortcuts to do the same things. So let me give you a few examples. Let's just type the word data into a cell and will use a keyboard shortcut to insert rows and columns. So one way to do this is to take my hands off the keyboard, grabbed the mouse, find, insert and navigate through the dropped out menus, or after I've taped in my data. I could just sit the all key we had all it brings up all these little black squares with letters in them showing you where the shortcuts are. So H is for home and for insert p for layout and all of these shortcuts bring up the tab that you're looking for. So if I had Ault and then I want to see the data tab, I just hit a and that brings up all the data tools. So I'm gonna have escaped to get out of that. And let's insert some rose. I had Ault h for home I for insert, see its way over here and then our for row. And actually, you can't really see that there's next to row. So let me put in some more data, so it makes it more obvious again. I had all to each, I are all one at a time. I don't have to hold them down and you could see a new room and see how fast that could be . I noticed that in our second cell in column B, it's much narrower. Then I should be. And one way to fix that is to grab your mouse and go to format and auto fit column with which works. But let's it controls and undo that. Another way to do it with the malice is to double click right here that also works all controls, the again to undo that. But if only is a keyboard shortcut. You had Ault h Oh, the letter. I, if you have it, memorized it super fast. But even if you don't, it's still faster just because you already have your hands on the keyboard. So that's how to use the Ault shortcuts. But let's look at another favorite shortcut, and that's the control one shortcut and yet control and the number one. It brings up the format cells. Dial it box, and you could even navigate through this without the mouse by hitting the tab to go to the list of things. Or you can just hit the arrow keys on your keyboard to navigate through the different taps . So that's a really good one. You can hit tab and change which type of formatting you want. Todo You can also get to it by hitting Ault h oh de or simply hit control one, which is much faster. Okay, so let's change this column back using Ault H o W c the w there with that, and we'll change it back to eight. Now, let's say instead of widening the column, we want to just wrapped the text and you hit Ault age. And then there's the W up there that wraps the text, and if I do it again, it turns it off. Now, the cool thing is, if you know where things are with your mouse, all you have to do is hit the all key and then look at the key strokes. Essentially, Excel is teaching you how to use the shortcuts. So it shows you f for file H for home and so forth. For example, if a f i s or a and I couldn't save my project using the old keystrokes, it's a lot faster than using the mouse and to clear the shortcuts just hit the escape key. And the coolest thing about the old shortcuts is that they could be type with one finger on one hand. You don't have to hold down the old key while you're hitting the H key. You just hit Ault and then release and then hit H and then release. So it's easier than doing like a Control Z where you have to hold down the control and the Z together. So that's just a little introduction. Teoh how to use the all shortcut keys to work more quickly in Excel. 10. Absolute vs. Relative Cell References: okay. And this lecture, we're going to be talking about absolute versus relative cell references. And you may be wondering, what does that even mean? And why is that important to me? And I'm really excited to show you, because this is not only a very cool trick to understand in excel, it's also very important when it comes to trying to do certain calculations on certain spreadsheets to save yourself a lot of headache. So let me just demonstrate for you quickly what absolute versus relative cell referencing is. So I'm gonna put in a formula right here, and we're just going to go with our good old some formula. And I'm gonna reference a couple of cells over here. We got C one and I'm gonna hold the control key and click D one. So there we go. So you want anyone click enter. If a double click back in the cell, you can see our formula. So the sum of C one and the one pretty simple. Okay, I'm gonna click off that. I'm gonna highlight that cell again. And I showed you this before, but it's good to see again. I'm going to now hover my cursor over this little green box in the lower right hand corner of the cell and you'll see my cursor becomes a skinny cross now and I'm gonna left. Click it and drag it down a few rows, and I'm going to copy that formula down those rose. Now let's see what happens when I click on these. The 1st 1 is just just how I set it up. The 2nd 1 has copied the formula, but it's changed the row to row to. So instead of being C one and D one appear, it's copied the Formula two, C two and de two and so on and so forth that continue to go down here. You'll see that it's copied it for each row to match the cell that I've entered the formula into. Now this is known as relative cell referencing, and it's very valuable of Excel for the purposes of copying formulas exactly like we've done here. But what if I wanted to have a relative cell reference in column C so that it did continue to move down each row matching the row that the formula is in? But what if I on Lee wanted to reference cell D one as the other number that it's adding to . How would I do that without manually entering all these formulas? Well, let me show you. I'm gonna go ahead and delete thes go back to our original formula. So I want the c calm to change, and I want the d calm toe. Always stay in row one. So here's an awesome trick. Click on De Juan and I pushed the F four key on my keyboard. Watch what happens. You see those two little dollar signs that appeared next to the D and next to the one Those are the magic symbols that make this cell reference on absolute cell reference rather than a relative cell reference. Now watch what happens when a copy this formula down the rows. Okay, this 1st 1 is the same as our original version. When we go to the 2nd 1 look what happens. The sea reference is relative. It's moving with the formula, but the D reference is staying exactly where I told it to go. Now watch as we continue to move down, see how the Sea reference continues to move relative to our formula and the D reference stays absolutely where we told to stay. Now you might be following along and thinking, OK, well, that's neat. But what's that going to do for me? When can I use that? And I'm so glad you're thinking that because I have a perfect example for you. So in this very simple spreadsheet, we have some sales reps. We have their monthly commissions, some dollar figures. In here. We have a formula at the bottom here, a some formula that just calculates their totals. No big deal. We have their total monthly pay, which we have not calculated yet. And then we have a holiday bonus over here of $500. Now what we want to do is create a formula in here that will calculate all of their monthly commissions and include their holiday bonus in it. So we get their total monthly pay. And if we were to create a formula here, a simple some formula saying $2000 from beats L B two plus $500 from Cell D to and then try and copy that down what we'd wind up with his $2000 plus nothing and $2100 plus nothing and $2300 plus nothing because that relative cell reference would move down with our formula. But by using our absolute cell reference, we'll put in our some formula again. We'll reference our monthly commission and our holiday bonus using the control click, and then we'll hit the F four key lips. Time out. I just put in absolute cell reference on both of our references, and we don't want to do that. So I'm gonna undo that. I'm gonna click on the D to hit a floor, and now we have a relative cell reference over here. And because of these dollar signs, we have an absolute cell reference here. So I will hit enter to complete my formula, and you can see it's taking the 2000 plus the 500 added them together for our total. Now let's click in Hair and copy are Formula Down. And as you can see, it's working. So we have 2000 plus the 500 up here D two is 2500 $2100 plus the 500 appear D two toy, 600 so on and so forth, and we can double check our for Miller's to see that this is still doing an absolute cell reference to this one cell appear and only that cell. And this is doing a relative cell reference to the cell one to the left of the formula and will take this one down here and the same thing. So that's the difference between absolute and relative cell referencing. And that's how you create an absolute cell reference so you can copy formulas down large columns in your spreadsheets but still being able to refer back to one specific cell or a number of specific absolute cells. 11. Paste Special: OK, in this video, we're going to talk about Paste special. And if this is something you're familiar with, you know that it is very powerful for a lot of different reasons beyond just your simple copy Exactly what you see and paste exactly what you see. And I'm gonna give you a whole bunch of examples and kind of different scenarios where pays special could be really valuable to save you a ton of time. Okay, so let's just take our kitchen remodel costs worksheet here, and I'm gonna highlight the whole thing and going to copy it. Okay, We've got the little dotted lines around the outside showing that it's copied to the clipboard. And if I wanted to just do a simple pace, I could just go over here and click here and click Control V, and that's going to paste it right into these cells. Now you'll notice the formatting Well, the formatting is the same, but the column width is a little bit different because these columns are different. But for the most part, it's carried over everything. If you cook into this, you'll see it still has the formulas. You can see that these cells still have the dollar formatting and things like that, right? So let's go ahead and done deal using controls the and we'll look at paste special, which gives us a whole ton of different options. So you find pay special by either going up here doing this drop down, and it gives you all of these different options, which will go through where you can also click the pay special button down here. It's gonna bring up the pace Special Dialog box. You can also right click, and it's going to bring you up these options here, and it's also going to give you an additional drop down here with a bunch of options. Or again, you can go down to here and open up the dialogue box. And then finally, if you press control Ault and V, it'll bring you straight to the paste Special Dialog box again. That's control Ault V, or you could think of it as a cult and then your regular paste control the right all to control the okay, and that brings up to the style Ugg box, which gives you all of these options. But before we look at this, we're going to go back to this version will right click, and then you can actually preview a lot of the different options with space special, which is really cool. So, for instance, this is your normal paste that's just gonna bring values formatting. Basically, everything across you can paste on Leah's values so you can see here. We don't have any of the coloring, the formatting, the grid, the dollar signs. It's just giving us the numbers and text. In other words, the values right. And we'll talk a little bit more about why that's valuable in a second. But I just want to show you these previews first. Okay, here's another one where paste the formulas and it doesn't look any different. But you'll notice that in these number columns, under the totals and costs where those air formulas it will maintain the formulas here and then transpose is a really cool one, so you'll notice what transposed does is it takes the kitchen remodel costs title and it puts it to the left. And then it puts all of column A. In fact, there. I'll just finish this so I can show you with my arrow, basically takes all our first column and lays it across the top. So we have all of our materials now listed across the top and let me highlight thes and double click here. Even a model of that secrecy cabinet handles cabinets, flooring, fridge, just like we have down the left side. And then, instead of going across and rose, it goes down in columns. And that could be very handy if you need to just change the orientation of arrange so that you could view it vertically. Instead, let's go back to our pace special. So that was that was transposed. And then we can just paste the formatting, which is also kind of cool. So if I wanted to create, say, a bathroom remodel cost worksheet, and I wanted to keep this same formatting or style that I could just paste all the formatting and then fill in the blanks with the bathroom remodel costs. And then finally you can pesetas a link, and this one's pretty wild because, for instance, you can see right here. This is $120 if I go over here and I change this to $300 when I had enter, watch what happens right here. It updates. So this is linked to this. And in fact, in the formula bar, you can see it says equals D three. And this is cell D three. So any time you make changes to this one, it's gonna make change to this one. So I could use this scenario on a different sheet. And whenever I update this sheet, the other sheet will update as well. So that's a pretty valuable use of it. Okay, I'm gonna controls the again to get rid of those cam. Make sure I covered him all. Yep. Okay, so we have paced regular paste formulas. We have paced formulas and number formatting. So this you'll notice the dollar signs air still in there and the formulas air still in there. So if you want, if you want to keep those, you can keep those without all the cell formatting, but just keeping the number formatting. That's kind of cool. We have keep source formatting, which is basically going to keep all of the formatting from the original source. We can do it without borders. You can see the grid line is gone. This one's really cool. Keep source column with. So remember how, when we do just the normal paste, it just messes up the column widths. So when you do this one keep source column widths, it actually adjusts your column widths automatically to make it look just like it was before. So that's pretty valuable transpose we already looked at paced values is important because again, if we just want these numbers, we don't want the formulas or anything like that. We can do that. This one will keep the values and the number formatting, but not the formula. So remember, this one keeps the formulas and number formatting. This one is going to keep the values and the number formatting. So you basically just get the numbers formatted, how they are but no foremost come with you. And then we have paced values and source formatting. And this one isn't gonna work because my columns in this area of the spreadsheet aren't the same size. But, for instance, where we could use this is if I wanted to replace the spreadsheet with the same spreadsheet , but without the formulas in here, Then I could just click here, go to the swan click paste, and now I'm gonna escape out of it. And I'm gonna show you when I go to these areas where they used to be formulas, right? $2400. That's 400 times six in the formula where it just has the number. So it just has the value, not the formula originally. And you'll see if I go control Z to undo that paste and click back into that cell, you'll see that it has a formula in there. Okay, so once again, the this place, special values and source format and keeps all the source formatting and the values, but leaves the formulas behind. Okay, so that's that one. We have a couple more here, and we've seen these before. Formatting We could show just the formatting do the links. We could pace a picture which basically just paste an image of what we're looking at. And then a linked picture, which is a picture that is linked to the original spreadsheet. Okay, let's go to the dialogue box real quick and talk about some of those things. Now, a lot of these we've looked at already. You can paste everything you can pace just the formulas you can paste just the values. Just the formats. We won't go into comments, invalidation. Some of these things are not. Something will use very often. But you can do column widths, which is kind of what we looked at before, where you get these columns all the same size. Basically, most of these are the same as the right click dialog box, but it's just a different format for selecting them. Okay, so let's talk about how to paste using skip blanks. I'm just going to give you a couple of numbers. Little range hero. Quick. Okay. So you can see that we have two columns of numbers. We have the tens column and the twos called, and we have a couple of blanks throughout this little range we have in the first row. We have the 10 and the two in the second row. We have two blanks in the third row. We have the 10 on the left side and a blank on the right and the fourth row. We have a blank on the left and two on the right. Now what I'm gonna do is I'm gonna copy this. I'm gonna pace it right over here as well. so we can keep track of what happens when we change this. Now I'm gonna take this left column, and I'm gonna copy this left calm. And then I'm going to pace it over this column using peace Special skip blanks so you can see what happens. Okay, lets escape out so we can compare. So in our original, when we had the tens and the twos. Now, when we've pasted the tens over what happens is we have where the 10 finds another value, the to it replaces it with the 10. These air blanks of nothing happens where the 10 finds another blank. It also fills in the space. And where we have a blank on this side and a value on this side, it does not replace the value with the blank. And that's what it means by skip blanks. Okay, It means that the values that you copied from this side will Onley populate over here if they're not blanks. So where you have a blank here and a value there, it does not replace it. It skips the blank. Okay, So that skip blank those I mentioned that I was gonna talk about paste as values because that's one of the most valuable uses of paste Special one of the ones you'll use the most. So we'll just talk about that for a minute, and then we'll be finished on. I want to start with this hyperlink down here, and I didn't mean to have to I need one. So this is a hyperlink, right? And if I take this and I highlight this carefully and copy it a copy and I pasted over here , right click and just paste it, then I'm still gonna have the hyperlink. Okay, so I'm gonna controls you to get rid of that. Now, if I don't want to have the hyperlink, though, I could go to my pace special, and I could go to paste values, paste it. And now the hyperlink is gone. So I've removed that and simplified the text, and that's gonna work for you in so many different scenarios. And I'll give you another example. So off controls that you just to get rid of that escape. Now, let's say we were dealing with these values over here, right? We have these formulas in here so that whenever we change these numbers, these numbers are going to change. But let's say I just wanted to do a calculation with these numbers. Without the formulas in there, I can actually copy these. And I can either pace them right into here using paste values, right, and that will. Basically, it's a basically a simple way to delete out the formulas. See how now when I click on these, they're just numbers. No more formulas, so it can clean up your data to make it easier to do calculations. And, of course, if I had another spreadsheet over here where I was doing calculations like raw calculations just with these numbers, I could go over here and I could paste just the values like that. And then I don't have the formatting from my range anymore. I just have the numbers. And when I want Teoh say, I want to do a calculation where I'm just picking out various things like these, like I want just want to add a few of the numbers or something. I could do some formula, uh, grab a couple of these numbers and get some results without having these numbers. For instance, this member of change this number it's not gonna affect my calculations because it's not going affect these numbers that I pulled out of it. Okay, so that's the one who use the most, and that's paste special. And just to go back into a copy again and go back to our paste options, make sure you missed anything with last one, which I don't use very often is. You can add an operation and actually let me go like this. We grab these numbers and copied them, and we're gonna put him right here. And let's say we wanted to, um, make these negative numbers. We can actually put a minus one in all of these cells. Just copy it down, and then we gotta pay special. Oops. Have to copy this again. Could have pay special. There are dialogue box. We're gonna, uh, multiply. So we're multiplying all of these numbers by the numbers in these cells, which is minus one right and 120 times negative one is negative. 120. So when we do this, we're using this operation here. We're using a multiply click. OK, we've now converted all those numbers to negative numbers and I mean, that's like a intro to the things that you can do with Paste Special as you continue working in Excel and you have these pay special options in your head, whether it's this dialog box or the drop downs, particularly these simple ones, like the formulas and values and four Months and also transposed, these are going to pop up in your mind as opportunities to simplify your data, to make it easier to work with an easier to do more calculations with. Okay, so grab one of your spreadsheets and do some copying and paste special and practice using these to get a sense of how they'll help you in your work in the future. And thanks for watching. 12. Conditional Formatting: okay. And this lecture we're going to be talking about conditional formatting and conditional formatting is really cool because you can basically apply a format to a bunch of data, and it will either color code it or put little icons on it. And it will give you a really strong visual indicator of the values in your spreadsheet. So let's take a look at an example here and you'll recognize our good old familiar sales rep spreadsheet. And what we're going to do is apply some conditional formatting to their monthly pay here just to make these numbers stand out. So we can really see whose numbers are high, whose numbers are low and so on. So first we're gonna highlight this data, and we're gonna go appear to conditional formatting. You see, we have a number of options. First, we have the ability to set some rules, and I'll show you how to do greater than or less than rules using this feature. We have top to bottom rules so you can do like top 10 items, bottom 10 items and so on in a larger list. And then we have these three presets, and these basically just allow you to apply either data bars, color scales or icons to your data. So we're going to start at the top here and go create a cell rule we're going to go to greater than. And it brings up this nice little dialog box, and you can see that Excel has already sort of analyze the information in here and made some assumptions about it. So it's assuming that we probably want to see all of the cells that are greater than $2500 you can see that includes three of these, and the other two are not included, and then it also just has a default color. So it's gonna add a light red fill with dark red text. But we can change this number if we wanted to. We could say anything higher than, say, $2700 which would rule out this middle one. But that's actually a pretty good number. So we'll leave that there and then you'll see that there are other options for this as well , so we could go with the yellow go just read text. We'll just go with the default for now and I'll click OK, And then when I click off of it, you see that the conditional formatting has been applied and you can see that it makes these numbers that are over $2500 really stand out. So let's undo are highlighting rule here, and we'll go back into our conditional formatting and check out a top and bottom rule. So let's go ahead and go with the top 10 item, and obviously we only have five items here, so we're gonna change this. We're just going to go to the top two, but we'll do the same thing. Same color. Do the light red filled with dark red text click. OK, and now you'll see it's done essentially the same formatting, but using a different rule. It's just picked out the top two in our list. So let's select this again will add some more formatting. Now, the cool thing is, you can add multiple conditions to your formatting. Let's go into our presets here will go into our data bars, and the nice thing about these is you can hover over them and it will preview it for you. So these are all just different colors that it applies These are all Grady int, and these are all solid, so you can see what those look like. I don't need to click on them, but while we have this top two formatting in the red laid on their let Me jump to the icon sets and show you what we can do with that. Because this is a case where we can apply multiple formatting without too much overlapping , basically muddying things up so you can see you have arrows, shapes, indicators and ratings. Let's just go with a simple example of directional arrows so you can see when I hover over this. In fact, I'm going to click on this. You can see that now. What it's doing is it's taking the top two and doing up Green Arrow taking the middle and doing a sideways yellow arrow. It was taking the bottom two and doing a downward facing red arrow so you can see how this gives you a really strong indication of the different values in this range, and it compliments fairly well with our top two in red here. So now, to get rid of some of this formatting instead of just clicking the undo button, which doesnt work to go all the way back to something that we did a long time ago. We'll go back into our conditional formatting, will say, manage rules or you can say just clear rules if you want to just get rid of them. But we're going to go to manage rules because we're just gonna get rid of one of them. And so you can see our two rules that we have set up here and all we have to do is pick one and elite rule. And okay, so now it's taken away our top two red ones, and it's just left our icons. So let's go back in to do some or formatting. We looked at data bars. Color scales are similar to data bars, but they just add, Ah, full color. You can see they basically sort according to value so that so color scales work. Now let's go look at a new rule, and we can actually create a formula to make a determination about what sort of formatting we're going to have in ourselves. And the style Lockbox gives us a number of ways to modify the rules, but we're gonna look at this one at the bottom use of formula to determine which sells to format. And then we're going to go in here and we're gonna add a formula that's going to determine how these air formatted. So we start with our equals and we're going to use the function of, and parentheses were going to say E to equals Quote. West Comma and D two is greater than 2500 close parentheses, and you can see it's showing that there's no format set, so we have to go into our format now. We could form according to number fault, border or fill. It's good with a bright yellow just so it stands out and we'll click. OK, and you could see the preview here and we'll click. OK, and now you can see that what it's done is it's used our formula to pick out on Lee the West region and on Lee the sales numbers that are over 2500 in the west region. And if you want to look at that again, we can go to our conditional formatting back to her, manage rules and you'll see our rule is right here. We click on that and go to edit rule takes us right back in here where we can change this to less than 2500. Click. OK, and now it's only applying the conditional formatting to the west region that is under 2500 . So that's how you apply conditional formatting to cells and where conditional formatting becomes really valuable is your ability to apply it to the cells to make it visual, but also to sort your data according to that conditional formatting. So, for example, I'm gonna take this range here, and I'm going to turn it into a table. Okay, I'm going. Apply some basic formatting to it. Click OK, you can see that it's added these drop down arrows. Now, if I go to this, I can now go to sort by color, and I could sort by any of the conditional formatting that I have on here. So if I want to sort my red arrows, I could do that and it brings my red arrows to the top. I want to sort by green arrows. I can do that and it brings the green arrows to the top. So when you think about this when applied to a gigantic spreadsheet with thousands of entries. For instance, thousands of sales reps from the East and West regions. If I wanted to find on Lee the sales reps that are in the West region and have sold under $2500 for this month, that I could just go to sort by color go to my conditional formatting that applies to those sales reps. Click on it, and all of those that are highlighted in yellow are gonna jump to the top, and I instantly have my results. So that's how you use conditional formatting, not only to apply strong visual formatting to yourselves, but also to use that formatting to quickly and easily sort through your data. 13. Layout Pages and Print Like a Boss: in this video, we're going to cover how to print and excel document. We're gonna talk about some of the print basics such as print preview page orientation, layout in margins, paper size page breaks, headers and footers and some other basics like that. Like most Microsoft products, Excel allows you to go straight to file and print and print preview. And there's the print button, and it shows you that if you have a sheet loaded and I hit the escape, he to get out of the print preview and back into the main scream. Now, if you have this chart and several other charts on the same worksheet and you only want to print this chart, what you need to do is set the print area the way we do that. As we go to page layout print titles click in their slide this off to the side, and then you can just select this field. So now that's the only area that will print. So here's our print preview, but what happens if we want to add a comment down here? Below are spreadsheet. I'm just gonna pace the comment in here real quick. I will go back to print titles, print preview and see that comment doesn't show up in our preview because it's outside the print area. So let's go back in and look at that Under view and page break preview, I'm gonna hold the control, but now and scroll my mouse to change the zoom. You could do this in the lower right hand corner of the screen as well. And if you see this blue border here, this is showing the print area that we've selected and our text comment is outside of that print area, so nothing out there will print. So we're gonna move the cursor to the blue border, where you see the up and down arrows well left. Click with your mouse and drag it down, and now this area will print. Now let's go back to their view. Normal. Go back to our page layout, print titles, print preview and there it ISS. Let's see what else we can do with the print titles menu because there are a lot of options . Another way to open it is to hit the old key type P for page layout and then I for titles, and you'll see these little letters show up in the ribbon. Okay, let's go to headers and footers. Now there's all kinds of things that you can add to a header or footer, and one of the really common basic ones is page numbers. So we'll put a custom foot or here, and we'll do it in the right section in the bottom of the footer, multi page and space. And then you click this button and that gives you a page number were basically putting a little bit of code that we type space of space. And then we hit this button and it puts code in for the number of pages in your document. These are some other things you can click to put data in your footers and headers, but we're not gonna mess with that. And you can see that Page one of one shows up in the footer here. Okay, let's do a print preview so you can see it. And there it is. And if we use all and hit P than I will bring up the page, set up again. So let's talk about margins. This is where you change the spacing around your data when it's printed. We can change it from 0.752 point five or whatever spacing you want. We can change the space for the headers and footers to show up. And what's most useful about this particular section are these buttons. Center on page, horizontally and vertically. So we'll look at that print preview again and we get all of the data center to the page and you can see it looks a little bit prettier. So let's hit the escape button again. Well, O p. I again and we go to page and change the orientation to landscape hit print preview. And look at that. Looks even a little bit better will hit escape again to get out of that. Okay, now one of the other parts you should be aware of on the print preview button, and we'll get into that by hitting the all key again and then w I that gets into the page break and you can lay this out any number of ways you can lay it out however you like. And once you've changed it, we're gonna back out of this by hitting Ault W l where you can just click up here to normal . Notice this darker grey line here and here. That's showing you where the print areas set in normal view. So those are the basics of printing and Excel document and how to change your margins, your page orientation and your centering within a page. 14. Find and Delete Empty Cells Located Outside of Your Data: okay. And this lecture we're gonna talk about how to find and elite empty cells that are outside of your range. Have you ever gone to print a simple spreadsheet and wound up having eight pages come out of your printer for one little spreadsheet? This is why that happens. Many beginning Excel users think that if these are the only cells that have something in them, that they're the only active cells in their workbook. But that's actually not the case. If you've typed around here at all, they very well could be active cells they might be asking. Well, why does that matter? Well, if you go to print this page, for instance, and you have active cells that go all the way down the page to say, sell 100 or row 100 that you're gonna wind up printing several pages on accident just to get this one a little spreadsheet printed there are other instances. For instance, when you're doing mail, merge If you're taking a list of addresses to make envelopes rather than printing off your list of 100 people, you might accidentally print off another 100 blank envelopes that only have a return. Address it. Waste your time. Your resource is and one final reason is that when you have active cells outside of your data, Excel requires more memory, too. Keep track of those cells even though they're technically empty. Okay, so I'm gonna show you how you do this. If you're selected up here in the upper left hand corner and you click control and end, it's gonna bring you to the last active cell in your workbook, the furthest down and the furthest to the right. So in this workbook, F 18 is the last act of cell in the workbook. And this can change based on your behavior. If I go over here to call him I 19 and I select that cell and I type some numbers into their and I hit enter, it activates that sell Now, even if I go back and I that backspace delete that now remember, I 19 should now be the last active cell in the workbook. And we contest that by going back up here. Hitting control and and you see now before was G 18. It's now I 19 because I activated the cell. And even though I deleted the values out of the cell. I didn't deactivate the cell. OK, so there are several ways you can get rid of these active cells, but the best one is just to highlight all these rows and columns and delete them. Okay, So, keeping in mind that were I 19 you can highlight them however you want. I'm gonna go control shift left arrow, and then shift up arrow to highlight those that I'm going to go over to eat. I'm gonna hit control. So I'm still selecting, and I'm going to select these with the mouse. So if selected all of those cells plus them, which is fine, okay? And then we can go appear and hit clear and clear. All and that should take everything out of those cells. Now, if I go here, you're gonna notice it hasn't changed. Gonna hit control and now still goes to that same spot. What you have to do is save your workbook, close it and then reopen it So we'll do that really quick. Save it. Could a file on clothes and then file and open. Now, when I go back to my sell a one and I hit control and end notice. Now it jumps to the last cell that actually contains data in my spreadsheet. Okay, now, if I go to print this, it's only going to print to here, and I'm not gonna get extra pages printing off. And when I do other calculations or exporting, its not going to even look at any of these other cells because none of them are active. Okay, so just to wreak out that if I have an active cell over here, I put some data into it and enter that data into the cell. Even if I going to leave it when I go back here and say control And that's the last active cell in my workbook. We wanted to lead all that. I could do it like I showed you using keystrokes. Or I could just select that cell and drag up and select all the cells here, hit control and slipped the rest of these cells clear. Clear All. Sometimes you can right click and also hit. Delete. You know, it'll ask you to shift sales up or left as long as you don't have a date out here. Doesn't matter. Click. OK, they're gone. And Then when we save it and restart the workbook, we can go to hear it control. And and instead of going here to our last cell, it's going to go to our last bit of data that isn't an active cell. And I guarantee you, if you use Excel very often, you can go find worksheets or spreadsheets at your job or that you've used personally. And you could find a lot of blank cells that are active below your sheets so you can go in and practices and clean those up and you'll just get one step further toward having cleaner . Easier to manage, easier to use worksheets. Thanks for watching. 15. Use ROUND to Fix Money Math Errors: okay, And this lecture, we're going to talk about rounding functions, and I'm going to show you a number of different functions that can be used for rounding. And I'm going to show you one really important thing that rounding does to correct math. There's in your Excel formulas that could otherwise potentially get you into a lot of trouble. So let's take a look at our example sheet here so you'll see I have this broken down into five different rounding functions. We have the basic round function we have round up, round down, ceiling and floor and round up and sealing are very similar and round down and floor very similar. But I'm going to show you some subtle differences between them, so that if you want to use it, you know how to use it. But let's start with the basics of the round function, and my favorite use for the round function is to keep you out of trouble. With math, there's in Excel, and the best way to show you this is just to dive right into an example of this. So let's say that we have two numbers here, so we have 2.9999 and 2.9999 So if we add these two cells together and I'm just going to Goto had and use the auto some and you can either go click on the auto, some on the home tab or you can just click Ault Plus and it will drop the auto some formula right in there. You can see it's highlighted. The two cells were looking for Click Enter. It comes back with a result of 5.9998 But here's the problem. If we click on these two cells and we reduce the formatting to get rid of some decimal places, look what happens to those numbers. When you reduce decimal places, it automatically rounds it so you can see that in the cell. Here it's showing 3.0 but the actual number in this cell is still 2.9999 So when we add this cell plus this cell, the actual answer is 5.9998 So this is where the round function comes in handy. First, we're going to delete this formula. We're going to use our round function equals round and they will tab, and we're going to say this cell plus this cell. So those are the numbers in our equation. Now you notice it's asking for the number of digits. So what it's asking is how many digits do we want it to round two If we're working in dollars and cents that we want to hit comma and we want to hit two because this is the number of digits after the decimal, and any time you're working with dollars and cents, you need this to say two digits because you have the 10th place and your hundreds place for your sense, so you'll notice. Now if I click on this and I changed the decimal places and add decimal places, it still comes out a 6.0 because I've rounded it to the two decimal place. But you will notice if I go over here and I create an equation that says equals this. Plus this and hit Enter that it comes back his 599980 because it's taking 2.9999 plus 2.9999 changing the formatting up here doesn't change the actual number. That is the result it on Lee changes what's displayed in your cell, so you'll notice if I reduce the number of decimals on this. Excel automatically rounds it to the sixth, but the actual number in that cell is not actually six. When I get it out to the full number of decimal places, that shows the true number, which is 5.99980 So this rounding function actually brings us back a solid six. It's still essentially $6. So when working with dollar amounts using the round function conceive you some serious critical math, there's because where we're talking about a percentage of a sent here. If we were to take this amount and multiply it by 100 watts, what would happen now we're off by two cents, whereas if we take this amount and multiply it by 100 where we've rounded it, it's going to give us the correct answer. So six times $6 times 100 is $600 of course we'll change the decimals to make it match. But it without the rounding. We get errors in our math when it comes to dollars. So that's an example of how to use rounding to fix your math. There's now let's look at another example where we're using a percentage, and this is basically the same issue. We'll go with our equal sign and we say Sell a three times Selby three, which is 29% and it comes up with 0.8671 Now again, if we change the decimals and reduce down the number of decimals to to, I noticed that it rounded it up 2.87 or 87 cents, because it's taking 870.8671 and it's rounding it 0.87 So what we need to do to fix it again to see is the round function and C equals round. And then we can take the product of $2.99 times 29% rounded to two decimal places. Hit, enter and it gives us the correct amount of 87 cents. And then one more thing, I'll point out to you. As far as the round function goes, you'll notice that I've been using the regular number, which is our math equation, and then the number of digits and I've been putting 22 is the number that you always use when you're using dollars and cents. But if I change this 20 I could go with whole dollars when I hit enter rather than being 87 cents, it rounds it up to a dollar so you can enter in whatever decimal place you want to go to. If I wanted to just go to the tens of sense that I can hit the one and it'll round it to 90 cents and of course I could make this look more like 90 cents by extending the decimal over and then it looks like 90 cents, and it works better when you have larger numbers. But if I have, for instance, $245.565 dollars, but if I'm adding these two numbers hit enter, then I get $810. But if I want around this to the hundreds that I can actually do around function, then I add the two numbers. Then I go to my number of digits, and if I say minus two and enter, you'll see it rounds it to 800 because it's rounding it to two places to the left of the decimal. And again, if I I'm do that with just the regular Matthew, see that it comes out to 8 10 And if I redo it with the rounding to minus two decimal places, you can see it rounds it to minus two. I could even if I wanted to round this to minus three, and you'll see that. Then it rounds it all the way up to 1000 because it's rounding to the third place to the left of the decimal. So those are the two essential parts of the rounding function. You have your number, which in this case, we're entering as a mathematical equation. But you could just be putting on a regular single number and then the number of digits that you're rounding to, which is a positive number, a negative number or zero. Okay, so let's get into the roundup function. The difference between the round up and the round is that rounding will naturally round up or down according to the standard mathematical rules. So if we do a rounding function on this and we around it to zero digits. You'll see that it naturally rounds that down. Let me copy that formula down and you'll see that it naturally rounds this one up cause $2.15 is less than $2.49 and $2.75 is more than $2.50. And that's the normal rounding rules in math. So watch what happens when we change the use to round up, copy the formula. And now you see that it's taken the $2.15 and forced it to round up to $3. Of course, will change this formatting so it looks like dollar signs. Then there we go and round down does exactly the opposite. So we already cools around down Tab. Take your number and a number of digits. We're going to zero round it to the whole dollar hit, enter, and then if we copy it down, you'll see that it forced the rounding of both of these down to $2. Now let's take a look at what ceiling and floor ceiling is basically the same as round up with one subtle difference. So let's do our ceiling on this is it to have go to her cell at the comma and you'll notice now, instead of saying number of digits, it's asking for the significance, and the significance is what number increments you want to round two. So if we want around to the fives will hit. Enter, you'll see that it takes us $2.15 and it rounds it all the way up to five. Now we could change that toe ones and you'll see that it takes the $2.15 and rounds it up $1.23 or we could change it 2.5. You'll see that it takes the $2.15 and rounded up to 2.5. So that's really helpful if you need to do your rounding in increments other than hold numbers and you'll notice for copy this down. So where this rounds up to the half dollar, essentially to the to the 0.5, the 2 75 rounds up 750.5 as well. 23 and then the floor works in the same way that the ceiling does, but it's going to round down instead, but using increments, so we get a floor tab. We choose our number and then we pick our increments or are significance. Let's say we want to go to the $1 increments. Then you can see it rounds down to $2 and same with the 2.75 It's going around down to $2. Let me reform at these, so it doesn't bother me. There we go, and the ceiling and floor can be pretty much as extreme as you want them. So if I want the ceiling to go to the to the twenties, then it's going around to the nearest $20 increment. Or, if I wanted to go to the nearest hundreds, that's going around to the nearest $100 income it. But more typically you would probably be in the range of the single digits or the percentages of a number like 50 cents, and I'll show you one more example of how the ceiling and floor could be effective. You could use round up round down for this is well, but if we're doing, say, retail dollars, if these air wholesale prices in a retail store and you want to double the price and then change the ending to, ah, 99 cents, for instance. Then we would take equals ceiling. You take our $2.15 times two, we got around it to the whole dollar closer brackets. And then we're gonna add one more math equation and say, minus 0.1 which is basically minus one cent copy this time for the number below. And so it's gonna take our $2.75. Multiply attempts to and give us $5.50. Then it's gonna round it up and take one penny away. So that would be a really quick way to create a bunch of retail prices out of a huge list of wholesale prices. And I'll just let you take a look at that formula again. So has our original number times two the significance. It means the number that we're rounding to us. We're just net rounding to one whole number and they were subtracting one penny and we could actually change this to 50 cents if we wanted to buy having 500.5 hit enter and then it would round it up to 5 49 and we could do the same thing with the one above. So that's how you can use five different rounding functions and also a couple of ways that you can use those functions to fix errors and also save a lot of time doing calculations. 16. Use Tables Like a Pro: okay. And this lecture, we're gonna talk about some pro tips for creating and using tables and excel. And listen, this is going to be a long lecture, and it's gonna be a little slow at the beginning because I've got to cover some basics. But bear with me because this is going to have some really power packed stuff regarding tables and how to use them and things that a lot of people either don't know or have for gotten. That could do when it comes to tables, and it's going to get better and better as the video progresses. So stick with me and this is gonna be really cool. The first thing to know about tables is that you have to create a table. And if you look at our little example here, you may recognize this from my other courses. This is our kitchen remodel cost worksheet. It's just stripped of all the formatting. And I've taken away the kitchen remodel cost header, and what we're gonna do is take this simple ring engine. We're gonna turn it into a table, and then we're gonna do all kinds of cool stuff of it. So the first thing you have to do to make a table is to click into the range and you go up to insert and table. You can also hit control T, and that will create a table. Excel already is selecting where the data is, and it's select their headers as well. But we also have this box check that says my table has headers. If you don't have headers, then uncheck that box. And if you need to re select the data in your table than you can click here and you can just go drag and select, and when you release, it'll come back. You click OK, and that's gonna create our table and you'll notice that in our ribbon it immediately jumps over to this new design tab for a table tools. And it does some awesome things to our table. It gives us little filter drop downs. It gives us, um, cool formatting, and you'll also notice this little green arrow down here in the bottom, which is showing the outer limits of our table. So again, I'm gonna ask you to bear with me through a couple of basics here, and then we're gonna get into some really cool stuff. Okay, so the first thing about tables is the formatting. And if you cruise up here to the table styles, you'll notice that you have a ton of options and you can even open up this drop down. And you could just hover over these two preview different styles. And I'm a big fan of this block in grand one, so I'll just select that. And then immediately updates are four Matic to make it look really sharp. And they have some cool options here to. For instance, if you don't want these little filter drop downs there, you can undo that. Click it to bring it back. And while we're talking about those, do you notice how they cover up my headings? Pretty annoying, right? Well, if you double click on the side of the column, then it will expand those columns to fit everything in there. So it looks better. You could also left justify all of these right, but going toe home and left justify, and sometimes that makes it a little cleaner and a little easier. So you're not having that problem, and then if you want to size, he's back down. You could. Let's go back to are you want to click back into our table to get to our design tools again , you could do some different formatting. You can create banded columns where it creates fans downward. You can bold the last column. See the total rose now folded. You can bolt the first column. You can remove abandoned rose. You can remove the header row if you just don't want to see that. And then one of the coolest formatting things is that you can add in a totals row. You just click that button and it immediately creates a sub total down here, and I'm going to get into this in a little bit. But first, let me get into a couple of things you want to be careful about when working with tables. Now let's say that I wanted to turn this back into a range. I could just go up here and click this convert to range button, and what's gonna happen is gonna keep a lot of the formatting. It'll get rid of these drop downs and so forth, but it will keep the formatting. The problem is if I then convert it back into a table That formatting is sort of permanent , meaning I won't be able to just toggle through these different styles and get whatever I want. I'm gonna be stuck with this if I converted to arrange and back to a table. So if you're going to go back to a range, what I recommend is scrolling all the way up or actually here will do the drop down. Go up to this one right here. This is the nun. In other words, no styles office click none. And then we can go click in there Convert to range. Yes, I want to convert it to arrange. And there we go. OK, And that kept our total row, but it got rid of all the other formatting. Okay, Anyway, let's go back to insert table. Look, OK, and you'll notice that that actually changed what's happening down here, which I don't like. I want to go back to my regular sub total style. So I'm gonna go ahead and delete this. There we go. So now we're back to a regular thing and we could go back up here and click totals row and they will give us this nice totals row. The reason I want this specific totals row is because this is a really powerful tool. If I click into this totals row, I haven't a little drop down Now, now, when I click on this drop down look at all these other functions that gives me like, for instance, if I just wanted to do a count of these rows, I can click count. They're eight entries if I want the maximum. In other words, the biggest number in my totals Row I click maximum and it brings back 2400 which is my flooring. So I have all of these options. You can even go tome or functions here right now often what you'll just uses the some. But you have those options. You can also go over to these columns and do the same thing. So you have the drop down here. If I wanted to know how many quantities of items that I have, I could also some this. So I know that I have 438 different little pieces, right? Okay. And if you want to get rid of that, you just click on that. If you want to get rid of this. You can just click into that hit after and it will get rid of all your drop downs. Okay, so that's how you use a sub total roll with some functions, which is amazing. Let's talk about inserting additional rose. If I go in and highlight thes Rose and I right click and go to insert it's gonna plop four new rose in there, you might say, Well, that's nothing special, but they're formatted exactly like everything else in the range. So if I added a item, let's go here. And I call this railings. Oh, you know what? Okay, I made a mistake, so you'll notice it's not showing our formulas over here. Which means that are basically I kind of broke the table because any time you ab rose into a new table, it should continue to have your formatting and your formulas. But I messed it up when I included this totals row from our range when it converted it. So here's what. After you, I'm gonna delete thes All right, that gets us back to all having all of our formless here. I'm gonna get rid of the totals row. I'm going to go back to no formatting. Now I'm going to convert it to a range. I'm gonna say yes. And now I'm going to convert back to a table. Say OK, you can go back to my cool formatting we can add in the total row. Okay, Now I should be able to add these rows in by right clicking insert Rose. Okay, now it's work. So see what happened. Because I'm in a table. It's automatically assumed that if anything is gonna be inside here, it's gonna follow the same format. So put these back in here. The reason it didn't work before was because when I had the range right which covered this whole air, and I confused it by having this sub total wrote in here with the range when I converted it back to a table. OK, but the beauty here is when I want to add things into my table. Now we'll see if I want to do some railings. And there two of them and their $300 then everything updates. And I don't need these two. So get rid of those. So not only does it update, but this is a cool thing. You've seen how to insert Rose. What if I wanted to hide some roads? Like for instance, Let's say I didn't want any of these top items here. Good. Gonna hide these rows and I want to see how much the other items will cost. I right click and go down to hide. And that gets rid of those rows. And you'll notice that there's now a little bar here between Row Juan and Rose six to show me that there some hidden rose. But the cool part is our sub total function. Onley totals that rose that are shown. And let me show you why this is cool. Let me just open these back up, double click Your rose will reappear. Let's do a some function of simple some function down here and we're just going to do a some of these rows. Same is this right? Hit? Enter. It gives us the same number. But watch what happens when I now hide these rows. The sum function is still referring to every row in this table, even the ones that are hidden. But our sub total function is on Lee doing the ones that are showing, So that's a powerful tool for being able to basically sort your data and do calculations on just the data that you want to do calculations on. Okay, so go ahead and delete that function, and we'll open these back up. And you can also go select those cells and double click, and that will bring back all of them. Or, if you have these headem, or you can select these right click and say, I'm hide and it'll pop the ball back. Okay, so that's a bunch of functionality with your table. I want to cover a few more things that relate directly to tables, and I'm gonna show you how to make a chart that will update when you update your table, which is rat. First, let's talk about one amazing little pro tip, which is naming your table. OK, so here we are in our design tools. Let's go over and give her table on name. See what's this table name? Let's just call this kid lips Kitchen remodel. Arif Arema hit. Enter it now has a name. If I go up to this little name box right here and hit the drop down, it shows be five. That's why I'm selected. I have this option of Steve's Cell, which is a random cell that I named within my worksheet, which you might remember from one of my other courses. And then I have kitchen R E. The cool thing is, if I click on this, it's elects my entire table, minus the headers and the subtitle Bar, you might say, Why does that matter? Well, let's say we have 43 sheets and I'm way over here in sheet 43. I know this is always sheet for, but I'm nowhere near that sheet or that table. I don't even know where it ISS, right. I have 43 sheets that all have kitchen remodel cost worksheets that are all different. One is for Bob's house. One is for Jill's house. One is for Su's house. I don't know how to find it. I don't even know where to start. I didn't label my sheets Well, all I have to do is go to this name. Drop down, go click on kitchen Ari, and it takes me right to the spreadsheet that I want right to the table that I want. Okay, Awesome. Right. So whenever you do a table. Go ahead and give it a name. Remember, That's up in the clicking your table. Go to design tools and you can name it right there. Okay, let me show you another cool little thing that tables do that. Other things can't watch. What happens if I slowly scroll down? That was very slow, but I scrolled down and look what happens to our headers. We do that again. Our table headers Air right here. Materials, quantity, cost. Total are column Headers are up here as a scroll. The table headers pop up into the column headers. So if I have 1000 rows of data down here, I'm scrolling, scrolling, scrolling. I'm always gonna be able to see my headers because they're gonna now scroll with it. It's sort of like freeze pain, but it's built in so you don't have to go and set up freeze pain. It's built right on the table. OK, tables are awesome. You can also select things easier in a table. So if I go hover my mouse over the top of the table header, you see it turns into that black arrow, right? If I click it, it's selecting the column but just within my table, right. If I click at the top of the column, it's gonna select the whole calm. But if I hover in the line between the two, it on lease. Alexa Calm and I could do this. The salmon could carry this across. Oh, I forgot. Show. If you click it again, click it. Once it stays within your table, click it twice. It gets your headers and and your foot or your subtitle. And then if I click it and drag it across, I can select the whole thing. Okay, so again I conflict. That click hopes I keep doing that Wrong. Cook, drag. It's like the whole thing. You can also do that with the Rose just selecting the road. Or I could hover over the outside edge where I get the four hours and click it, and I'll select the whole table. So there's some awesome little selection techniques and then, really importantly, going back to the screen arrow down here. If I hover over that, see that? Get the little double ended arrow. I could grab it and I can expand my table, um, or across, and that will basically populate the same kind of formatting into these cells and make it part of my table. Obviously, I don't want that. So I'm gonna go control Z. But that's more useful for expanding downward and notice. When I go downward, it puts those formulas in there. It takes my sub total down to the bottom. Just really smooth and clean. Operating with tables, OK, And finally, let's do on Zika. I mean, undo unsee. Let's unsee that control Z to undo and let's create a chart from our table. This is awesome. Okay, you click in your table, go to insert, go to the charts We're gonna do a pie chart and pop apart Heart in there will slide it up here next to our table. And right now this pie chart doesn't really mean anything cause it's just looking at the quantities, like how many qualities there are so showing like so much yellow under flooring, right, because we have 400 units of flooring, so it doesn't really make any sense. So what we're gonna do is go in here and go to the filters chart filters and what we really want is a pie chart that shows the totals of the various materials, right? So we want to show which is our most expensive in which their least expensive item in a good visual representation. So we're gonna click totals and then we're gonna click, apply. And now you can see if you look at the yellow box. This shows that are flooring takes up the most of our money. If we look at the gray boxes that the next biggest one, that's our cabinets and so on, although after this tiny little blue one, which is our Cabinet handles, which is our least expensive. Okay, now, what's really cool, too, is let's say that, for instance, are flooring. Let's just say that our cabinets are actually gonna be way more expensive. Okay, Instead of being $200 per unit, they're actually gonna be $600 per unit. We had enter. Watch what happens to the pie chart. Our price are total for the cabinets, just updated. And now it's the biggest thing. And it's very clearly represented on our chart. Okay, if we decided that we didn't want to eat cabinets at all or maybe we got some for free. We come in here and delete cabinets, Boom cabinets is gone Now the gray is the flooring, and it is by far and away our most expensive item. Okay, so those air Ah, whole bunch of things that the pros do with tables that you should also be doing. And I want you to just practice some of those things. You know, Go to your click into your table. Go to your design tool. You play with designs, play with your table style options, practice selecting just the columns, loops, just the columns in your table and just get good at this because tables are awesome. So I hope you wanted a lot on that, and I'll see in the next lecture. 17. Use INDEX/MATCH Instead of VLOOKUP: OK, in this video, we're going to talk about the index and match functions. An index and match are used similarly to be Look up and h look up. But you don't have to worry about the data being right next to each other. So in that sense, it makes it superior, more effective and easier to use. Okay, so we're gonna be using this table, which is included in the downloads for the lecture. And these are just random numbers. And most times, when you want to analyze data from something like this, you'd go to a new sheet and then you type equals and at the control page up key to grab the data. And by the way, I know bananas is spelt wrong and I'll show you why in just a minute he and grab, say, the topic that you want to look at and the year that you want to look at just by selecting these here and then Okay, you grab that and there it is. And then it makes it easier to do whatever analysis you want to dio. However, occasionally you might end up moving data around. So let's say we want events in bananas to change places. So we're gonna insert a row above events and hit Ault H i R. And I used shift space bar to select the whole row. When we go up here and shift space bar to select the whole roll up here control V to Paste and Ault H d r deletes the empty row. So now you go back to your sheet. Were you doing calculations? And all of your references are messed up. So if you use index Match to do this, then you wouldn't run into this problem. So let's delete thes and start over with index match. So Index Match incorporates the labels in your cells into those values. So let's just clear the rest of this out and let's get right into the function equals index . So the way Index works is it asked for an array and then your phone number and then your column number. And that's how you find the number you're looking for, and an array is just a collection of data. So in this case, we're going to navigate back to sheet one using control page up, and we're going to select the whole row of data next to apples. So we click here and say, shift control right to select the entire row than shift control down Arrow to select the entire array. No, I have everything selected. I'm gonna hit F four to make these absolute. So let's call this area The pond and our data is the fish. So all of our fisher in the pond So any fish that we want to find us right here and we can editor function right here in the function bar without having to go back to sheet too. So we hit comma, and you could just put in a road number six or seven or eight. But matches gonna help make this a lot easier. The match function is gonna help us find our fishermen. And these labels here and here are fishermen that are gonna help us to find our fish or our data. So we type in match and then tab and our look up value. It's gonna be back on sheet, too. It's gonna be in column D right next to our information, and we'll enter in a label there later, and we'll have f 43 times to lock just the column. Not the row, because we're going to copy this down and over. And we want to still reference the labels and column D or are fishermen. So that's our look up value. Next, we need to look up a race with a comma. Our look up array is gonna be all of the labels in all of the rows where data is. So we're going to select the labels for the rose five through 12 and hit F four toe lock those references and then comma and then the match type. In 99% of the time, you'll use zero for exact match. There are rare instances where you'll use long or minus one, but we're not going to get into that for this purpose. Most of the time you'll do it exact match because you want the exact spelling of that label . So that means you have to get all the spelling exactly right in sheet, too, for this to work. So that's important. Okay, so that match function identifies the rose close parentheses to finish the match function. So now we're back to our index function, So we just did our row number and now we're gonna do our column number now call a member has these brackets, which means it's optional. Well, let's just hit a comma so we could just put one. And you just have one column selected in your ray, and that would be just fine. But in this case, we have five columns, so that wouldn't work. We have to identify which column we're going to use. So we're gonna use the match function again, hit Tab Goto our look up value. And we're going to create a spot up here. And this time will hit F 42 times to lock the row number. But not the column, because we're gonna copy this across the columns. Okay, So where is our array for the fishermen across the columns? Let's go back to sheet one click on 2012 and then control shift right arrow to select the whole array and hit four to lock the array. The arrays should always be locked, comma zero for exact match. Closed the parentheses for the match formula, and then we close the princes for the index formula. So now we've nested to match functions into one index function. Now, when I enter you'll see I haven't error because it doesn't have any results yet because it's referencing blank cells when I want this match to be referencing E six rather than F six. But that's simple. You just grab it and move it over. Okay, so now we can put in our references. Let's start with the year we have equals and then we'll come over to sheet one, because this that's exact spelling. So we just click on this and it brings in 2014. And to show you how important matching up your spelling is, even if I type bananas wrong like it is in the spreadsheet. If I add a space behind it and hit Enter, it still is going to give us an error because even that space is enough to throw it off. So that's why I want to use equals and then select that cell and grab it exactly how it's spelled. So even though it's spelled wrong, it's still gonna reference correctly and see. Now it's grabbing the number that we want 66207 here and 66 to 7 over here. So now to show you the magic, let's see what happens when this reference moves. So let's go down here and select two cells and hit Ault H i R to insert two rows, shift space parts select this whole row and control. See to copy it control V to paste it in here. All to h d r to delete throw all hdr to delete this other row. Oh, and I forgot to delete the equals to find the bananas. I didn't take the foreign lot, So let's go back here and grab bananas again. And this time we'll take that formula out of it and then Ault h v v to paste it just a za value. No formula. Now let's move everything around again to see if our reference still works. So go appeared they ault h i r to insert a row shift space far to select the whole row control See to copy it Troll V to paste it and ultra HD are to delete Throw. Okay, now let's take a look at our reference. So bananas a 66207 And when we look at sheet 26 16 07 Okay, there it worked. No errors. So even though we moved our data around in the array. We didn't lose our reference because Index match doesn't care where in that selected array . The data is it just cares what the fishermen are or those two reference points or labels along the side and the top are so as long as you have the references along the side in the top, correct it confined it anywhere in that array. The only time you will have a problem if is if you take bananas here and copy it and paste it outside of the array that we're referencing and then go cult H d r to delete this row, and now you'll notice that it's not finding it. But that's because it's outside of our array, because our references only D five through age 11 and our references all the way down here on Rose 17. But you can even fix that scenario. And the way you do that is when you write your array, just select the whole column. So instead of having this limited to de five, we just have column D. And rather than having it be age 11 we just have it be calling H. But if you do that with your index array, then you have to do it with your match a raise as well, because they do have to line up. So we'll take out the rose on our match as well for our labels. And then we can leave the columns as they are. So now we enter and you can see that they're references working again. Now it doesn't care how far down the spreadsheet bananas is because our array includes the entire column. So that's how you use index and match together as a nested function to essentially do the same thing that you would with be, look up except you're not limited to just the left column, and you confined your references even if they move. 18. Mail Merge with MS Word and Excel: OK, in this video, we're going to talk about mail merge and specifically, we're gonna talk about how to take an Excel list like we have here on sheet, too. A mailing address list and merge it into Microsoft Word to create a mailing and whether you're pulling these first names into the greeting field of a letter or you're pulling these entire addresses with the mailing label name to create envelopes, pre printed envelopes, mail merges a really amazing and useful tool. And I realized that technically, it is a Microsoft Word feature. But I think that because of the way that relates to excel, it's worth showing you. And it's just one of those things that so many people need to be able to know how to do So taking a look at our list here we have our mailing address, and you can divide these up in various ways. But one of the better ways is to do first name, separate last name in a separate column. I like to do the mailing label name, which has the first and last name together, because sometimes this is gonna be different. Sometimes you're going to use a shortened name or a nickname here for first name. It just gives you an option to have more information here. And then you do your address, your street address, your city, your state in your zip in separate columns. And then we're gonna merge those altogether inward. Okay? And normally wouldn't do this. But I just listed are return address over here so I can copy and paste that where it needs to go. Okay, so we're gonna jump into a word I'm playing for document. And I'm going to assume that if you're an intermediate to Advanced Excel user that you're probably pretty familiar with word as well. If not, just follow along and you'll see how we make this work. So first, let's create an envelope. So if we go toe mailings and we go to select recipients, this is where we're going to find our list of mailing addresses, go to use an existing list. I'm just gonna navigate to that excel document, and it's our kitchen remodel cost worksheet. It's got a couple of sheets on here and we're on sheet tubes. We need to select that. Then click. Ok, so this now has our recipient list in the background. If we want to do envelopes, we can either just click on envelopes or we can say start mail, merge and we can go toe envelopes. That gives you some options here. Like you can choose the envelope size you can choose where your delivery and return address goes. These are both set toe Otto, which is fine. Gives you a little preview of what it's gonna look like. Then you click, OK, and then it creates a little envelope for you. If I shrink this down so you can kind of see that envelope on the screen Better First, we can just go to our Excel document and copy this return. Address it. Control will see to copy. Then we go back to our word document and we could just pace it. And here, control V. Oops. I accidentally hit a regular be to delete that, and then we're gonna go down here. So that's a return address. Field this start to address field and then we're going to go poll fields Or I should say columns from our Excel spreadsheet to insert into here. So we go to insert merged field and see these are column names first name, last name mailing label, name address. City State, etcetera So we can go mailing label name and then you'll see our cursors here we just hit Enter to go to the next line. There's a cursor there now Goto another field it address hit after again City And now we're gonna go comma Space State, Two spaces and zip So these little brackets show you which column it's gonna pull a front and it's gonna pull this list of addresses from our Excel spreadsheets. Now we can go to preview results and there's our first envelope. We can cruise through the previews toe. Look at all the records. This will take us to the next record. Now we see Sam Margot, Claire be back and given and that's our whole list could weaken, jump all the way back to the beginning, and then you can click preview off, and that will show you your field names again that you've inserted in there. Then we got to go to finish emerge, and then you can either just print it, send emails, or you can edit the individual documents. Mostly, you would just print it, but if you want to kind of preview each one individually or do some kind of changes to it. Each one you can say headed individual documents. We're gonna merge all of them because we want all of them. And now it's created an individual envelope for each one of these, and we can scroll down through them and see all of our envelopes and, you know, for six centuries, obviously, this isn't saving us a lot of time. But if you have 6000 entries, you're putting 6000 envelopes. This is really, really worth your while and pretty much any office job that you have. You're gonna run across this at one point or another, so really important to know. Okay, so that is our envelopes. Oh, and one more thing I want to show you. If you get to your preview here and you see, there's one kind of fought here and a different type of thought here, rather than go through here and change each and every one of these and scroll down and change it and scroll down to change it. You can just close out of this or just minimize it. Go back to your original document where you're doing the mail. Merge. Do your edits. So what I'm gonna do is just use the format painter. Highlight a little bit of this. Not the whole thing. Just text click on the format painter. Look over here. I needed toe klik. It better try that again. Form a painter, all the There we go. So we've changed the return address to the same fault, and then we could go back to our preview results. And as we go through, they're gonna be the same now. And we could go to finish emerge. And now we have a finished document with matching fonts. And then if you go ahead and print this Okay, so that's how you create an envelope using mail. Merge. Next, we'll look at how to add a custom greeting to a letter. Yes. I'm gonna jump into a different word document. Here. Here we have just a really generic form letter and said blah, blah, blah for placeholder texts and then a signature at the bottom. And we want to put in a custom greeting right here with their first name. So the first thing we're gonna do is go to select recipients using existing list. Same thing. I'm gonna navigate to that same excel worksheet. Go sheet to again. And I meant to tell you this last time You want to make sure this is checked. That shows first row of data. Contains column headers because it does have headers. That's our first name. Last name address, etcetera. Click. OK, we now have it loaded and we can go to insert merge field and we have our cursor selected there. And we clicked first name. So it inserts the field they're gonna enter to put our text starting on the next line and then we can preview are results of this. So we have a preview and you'll notice I don't have a common there, so we want to change that. Turn the preview off. Go back here and Atacama. Now we can preview. So there's a Steve when we can cruise through these Sam Margo Claire defect jibali. Okay. And then the same with this, you finish emerge. You can either just print it or you can go open up each of these in their own page and you can edit them individually, if you like, or you considered as an email. But that's how to use your excel spreadsheets along with word to do a mail. Merge. Thanks for watching