Excel Basic Skills And Power Tips - BUILD - Building Your Worksheet - Part II | Billy Wigley | Skillshare

Excel Basic Skills And Power Tips - BUILD - Building Your Worksheet - Part II

Billy Wigley, Microsoft Certified Trainer(MCT), MOS Excel Expert

Play Speed
  • 0.5x
  • 1x (Normal)
  • 1.25x
  • 1.5x
  • 2x
6 Lessons (25m)
    • 1. This Is BUILD Part II - Check It Out!

      1:56
    • 2. Zip Through Your Worksheets - Navigation Shortcuts

      3:25
    • 3. Here Is How You Select Cells And Ranges

      3:20
    • 4. Ever Hear Of Universal Shortcuts?

      4:33
    • 5. Insert, Rename, Move Worksheets

      7:09
    • 6. Move to another workbook color sheets

      4:33

About This Class

With 1,048,576 rows, and 16,384 columns in a spreadsheet, it is important to skillfully navigate   from cell A1 to cell XFD1048576 like an Excel ninja.

Prepare yourself to learn the often ignored Navigation shortcuts that will change your life.

With all of the tools that you have available in Excel, you are going to be eager to use them. Well, how do you select where to apply these tools?

What if you want to Bold a range of multiple non-adjacent cells?

Here, you will learn how to select your cells and ranges like a professional.

Did you know that Ctrl+C is the shortcut to "copy?" What's the shortcut to Paste? Ctrl+P! No, that's the keyboard shortcut to Print. The shortcut to Paste is Ctrl+V.

In this class you will learn these, and a few more Universal Shortcuts that are commonly used.

Just like you must work with Rows and Columns in your worksheets, you work with worksheets in your workbooks. It is easy to quickly insert, name, and rename worksheets.

Sometimes you need to have the same data in multiple workbooks. Instead of copying, and pasting the data, there's a better way--you can Move or Copy entire worksheets to other workbooks!

What if you have many worksheets, and you want to easily identify specific ones? Just color the worksheet tabs, and you are done!

Step right into this class and let your path to Excel success continue. Keep on learning!

Transcripts

1. This Is BUILD Part II - Check It Out!: with 1,048,576 roads and 16,384 columns and excel. It's important that you can skillfully navigate from so a one to sell except the one making 5 40,076 Like an Excel ninja re prepared to learn often ignored navigation shortcuts that will change your life with all the tools that you have available and excel, you're gonna be eager to use them. Well, how do you select where to apply these tools? What if you want a bold a range of multiple non adjacent cells Here you learn how to select cells and ranges like a professional. Did you know that control? See the shortcut? The copy. Well, what's a shortcut? The paste Patrol P now, huh? Control P is a shortcut to print shortcut. The paste is controlled In this class, you learned these and other universal shortcuts that are commonly used. Just like you work with rows and columns in your worksheets. You work with worksheets in your work books. It is easy to quickly insert name and rename your worksheets. Sometimes you need to have the same data in multiple workbooks, so Instead of copying and pasting the data, there's an even better way you can move or copy entire worksheets into other workbooks. What if you have many worksheets and you want to easily identify between these worksheets? Well, just cover the work she tops and done stepped right into this class and that your path to success continue. Let's keep on learning. I remember. Don't get mad. Get skills. Hello. Hi. 2. Zip Through Your Worksheets - Navigation Shortcuts: Here are my navigation shortcuts. I'm gonna show you a short cut to navigate to the last empty cell and a shortcut that selects while navigating. So to navigate to my last empty cell, I'm going to press the control and the arrow down. So in that press, the control key and the arrow down it takes my marquee all the way down to my last empty cell. When I pressed her control and they are up, it takes my marquee up to the last empty cell. When the press control arrow to the right, it takes my marquee all the way to the right to my last empty cell. And now, when that press the control key and the arrow to left and have gates to the last empty cell in that direction. Now watch this. I'm saying that when I press control arrow, it'll navigate in the direction of the arrow to the last empty sell. What I mean by that? What's this? If I go, for example to row 45 attacked the word data right in, they're not gonna press the control key and the arrow down to navigate when I press the control key and the arrow down that's going to navigate down to Rule 45. Surprise control. Arrow down and look. Word stopped right at row 45 right there. That's considered the last empty cell. So now when I press control iro up, it goes all the way up to my last empty cell. So one more time, control over down stops at the last empty cell controller up all the way to last Empty cell . Now, what's this? I'm gonna press control our down stops at my last empty cell Row 45. What would happen if I press control Arrow down again, sir? Press control Arrow down. And now we go all the way to the last empty cell. Now in the press control are up. Where is he going to stop Rule 45. That's right. Control Arrow up. Stopped right there at row 45. Now in that press control arrow up again, it stops where? At my last empty cell. What about selecting while navigating? Now that said that you want toe select a range. So you left click on. So they one left quick and then you drag because you want us like all the way to Road 45. And there you go. Simple. No problem, Right? Well, we can do it a faster way. Shortcuts, tips and tricks Use your navigation. Short kits. Check this out. So the control plus the arrow navigates in the direction of your arrows. The shift plus your arrows selects in the direction of your arrows. What would happen if we press to control to navigate while holding down the shift to select will let navigate while selecting Check it out. I'll press the control to navigate and the shift to select and are pressing arrow down. Click bam! There it is. It's selected while navigating all the way down to my last empty cell. Pretty cool, right? And now you can hold down the shift key and you can find tune anyway that you like. Those are your navigation short kits, my favorite circuit in excel. Now let's inserts from Rose and some columns 3. Here Is How You Select Cells And Ranges: when you're working with yourselves to select them, you can just click on them. Just click on the cell that you want to select and work with. Now what if you want to select a range of cells? So I want us like all of the cells in this entire range. How do we go ahead and do that? To do this, we can just click and drag, so I'll just select Cell C three and then I left. Click left, click and drag. Remember, I'm pressing and holding my left mouse button so I can left, click and drag left, click and drag. That's one way of selecting a range of cells. You can also use your shift key, so I'm going to select this cell C three and then I'm gonna select sell E five. However, before I click on Cell E five, I'm gonna press the shift key, so I'll go ahead and I'll select Cell C three. I'm gonna hold down the shift key and then select so e five and what's gonna happen? It's going to select my entire range, so I select So c three. I press the shift key and click on cell e five, and I just selected that range. Look I can click on. So e five press down the shift key and then click on Cell C three. And there you have it. Remember, I'm pressing and holding the shift key one more time. I click on C five. I press and hold the shift key, and I click on a three and I selected that range. So now we know two ways quick and drag and using the shift key to select a range. What about selecting just the cells containing the word data? I don't want to select entire range only one to select the cells that contain the word data multiple non adjacent cells. So instead of pressing shift, we can press control. So I press and hold the control key while I'm selecting Click, Click, Click and I just selected five. Multiple non adjacent cells now can apply one move to all of them. Let's say I want to both all of the cells containing the word data, So I've selected the multiple non adjacent cells, and now the both I can easily press control be. And that's the shortcut to bold all those cells can turn the word data in one click boom boat. How did I know that the shortcut the bold is controlled be well, I press control F one Teoh, open up my ribbon. And then I'm just gonna hover over the boat tool in the font group. And the two tip tells me that the shortcut to bold this control be just like that. Now I'm gonna hide my ribbon. How can I hide my ribbon control F one? Hide my ribbon. All right, so now we know how to select on entire range of cells and multiple non adjacent cells using your shift and using the control key this nurse and Universal short gets, and that's keep bone learning. 4. Ever Hear Of Universal Shortcuts?: What are universal shortcuts? Universal short gets are short gets that work across many different applications. Can you think of any shortcuts or you're using right now that work in all of your Microsoft Office application and even working other programs that you use can't like the copy and paste? Those are universal shortcuts. Let me hide out a few. I'm gonna talk about the cut the copy the paste, Undo, redo and save those air. Universal shortcuts to cut. The shortcut for that is controlled plus X control. X cuts. The copy is control. See, the paste is control. Snacker Tropea control P is print paces control B as in Vegas, Las Vegas, where I live Undo is control. What? It's control Z. What's control you control you is underlying? I want to undo that. Underline. I could just press control Z to undo. Redo is control. Why and save. But we know that one. It's control s so let's just watch him in action. I put my marquee on cell a one I want to cut right there So that press control X to cut. And then I put my marquee where I want a paste. You noticed this marquee right here, Dan Markey. Right there. It's called your Marching Ants Marquee. That is the official Microsoft name for that marquee. And this letting us know that we have an active selection. So now I've got my marquee on C one where I'm going to paste and a shortcut to paste is control. The as in Vegas. Now I'm going to copy. So I click on Cell A two and the press control. See to copy. There's my marching ants. Marquis up. Select where I want to pace in a press control V. And now, look, I just made a copy. Now you notice that marching ants Marquis is still active? That's because this is a copy. The cut is a one time action cut paste the copy. I can just keep making copies. Notice that. Then when I'm done making copies, I can either press enter where I compress escape, and I'm gonna undo these copies. So impressing controlled Z Josie Control Z. Now, if you just want to make one copy, then just select where you want to make a copy. Press control. See? So like, where you want a pace and then just for one copy. Press enter and you notice that marching ants Marquis is now off for the paste. Aiken, select where? I wanna a copy. Control, See? And then I want a pace. We got that control V just like that to undo. Watch this. Control Extra cut control V to paste way. I didn't want to do that, Control Z. But what if he did want to do that? Well, then you redo it would control why, but no. I want to undo control. Z control. Why control z control? Why control z control? Why? You just go for it? You're never gonna break Excel, okay? I'm just playing around right now because I'm not scared that I'm gonna break it. That's how you learn. You just got to keep going for it. My job is to break excel, and I've never been able to break it. Now I've managed to mess up a lot of spreadsheets. That's why you want to make sure you saving on a regular basis. So the shortcut the save is control ass. When I press control less because this is a read only workbook watches gonna ask you to save it as what? So I press control s. And then Now it's telling me that I can't save it because it's read on Lee and we know about saving with read only. So control s take me right here to the save as in this particular file. So I get out of their escape. By the way, do you remember the shortcut to get to the save as dialog Box F 12? That's right. So in that press have 12. Now I'm going to see my save as dialog box. Now you have some universal short gets. Next we're gonna learn how to work with our worksheets, and it's keep on learning. 5. Insert, Rename, Move Worksheets: our workbook is made up of worksheets, so let's learn how to work with our worksheets. To do this, I'm gonna open up a brand new workbook. Now, the long way of going about opening up a brand new workbook is to go to your backstage view by clicking on the file tab and clicking on New like this. Click on the file tab. And now that you're in the backstage view, select New and that will open up a brand new workbook. Let me escape out of here. I prefer to use a shortcut, shortcuts, tips and tricks. So the shark, it opening up a brand new workbook is control and suppress control. And and here we see ah, brand new workbook. You'll notice it's called Book four. It's the fourth new workbook that I've opened up today that's good and minimize it and adjust it so that we can easily view where we're working on now. Here's the area that we want to focus on our were sheets. Notice how many sheets we have in our brand new workbook by default right out of the box Excel 2013 and beyond. When you open up a brand new workbook. It opens up with one worksheet 10 4007 and prior versions. When you open up a brand new workbook, it would open up with three worksheets in it in Excel 2013 and beyond. When you open up a brand new workbook, it contains only one sheet. That one sheet is called she one. It's called Sheet one because it's the first new sheet in this book. Now let's say that I want to add another new sheet. You see that plus sign right there? Let's hover over it when we hover over it. It's saying new sheet. So that's letting us know in that tool tip that when we press that plus sign, it's going to insert a new sheet. So press it quick. And then there's a new sheet. Notice where it inserted it. It inserted it right after my selection. I'm gonna show you another way to insert a sheet. Another way to insert a sheet is when in doubt right quick. That's right. When in doubt, right click, nothing happens. Left click. Nothing happens. Go to your Excel options. Notice what's gonna happen when I right click on sheet to right click, and now we see a menu. When I click on insert, watch what's gonna happen. My insert worksheets Dialogue box appears, and here we notice that we have the ability to insert just the plain worksheet or one of our own personal templates. Remember when we learned how to create a template and when you created your own template, it's stored in your personal. So these air your personal templates. When you right, click on a worksheet tab and you click on insert, you have the option of inserting a plane worksheet or one of your personal templates. Now notice the following tab. Spreadsheet solutions. I mentioned that with Excel, you get seven templates preinstalled. When you're connected to the Internet, you have access to thousands of templates. Well, when you right click on your worksheet, you can insert a plane worksheet. You can insert one of your personal templates, or you can insert one of the seven temples that came preinstalled these air seven templates that came preinstalled with Excel. You may notice that there's more than seven templates in my particular version of Excel, and the reason for that is because you'll have a Spanish version, and then you have an English version. I teach Excel in Spanish as well. And like many of you, I speak two different languages. So sometimes when I'm working in Excel, I like to see everything in Spanish. And sometimes I want to see it all in English. When I'm teaching Excel in Spanish, I definitely need my whole entire interface to be in Spanish, including the tool tips. So I ordered the Spanish language pack and installed it into my version of Microsoft Office . That's why I have Spanish available to me. Now we're gonna just insert a plane worksheet. So I go back to the general tap and I'm a double click on worksheet. Or I could just select it and press OK, right there. So press OK. And now notice where it's gonna insert that worksheet Watch. We have sheet one sheet to when that press. OK, it puts sheet three right in between sheet one and two. In other words, it has inserted my new worksheet before my selection to insert a new worksheet. The shortcut is Shift F 11 and when you press shift F 11 it just inserts a worksheet right before your selection. So if I click on, she won when I press shift F 11. It inserts that fourth sheet right before my selection and it's called Sheet for because it's the fourth sheet that I've inserted into this work. But today Now let's say that I want to rearrange them. I want to get him in the right order. 1234 Well, I can just click and drag so I can click on sheet for moving all the way to the end. And I can click on sheet three and I could move right in between she two and four. There you have it so you can just click and drag notice one more time I'm gonna click on she for I'm gonna left click and hold When you left, Click and hold I grabbed it. Now you notice that it looks like I have a little sheet at the end of the pointer and you also see upside down black triangle with that upside down. Black triangle is just letting me know the insertion point so I could just go right ahead and release right between sheet two and three, quick and there I have it now it's gonna drag it left, click and drag it right to the end. Now let's say I wanted to give this sheet a name. She four I don't want to go Just sheet wants you to shed three She four. I want to give it a name. Let me show you two ways of doing this one way. When in doubt, right quick and weaken Just select the option to rename Now you notice I gets bold ID That said We know that now I can give it a name Watch this I'll press enter and I'm done So instead of right clicking this time and renaming the way I'm gonna rename is I'm just double click on the worksheet So I double, click, click, click And now you notice I gets bold it again Now I can just give it and you name Enter and there you have it Let's talk about moving from one workbook to another 6. Move to another workbook color sheets: we learned how to move within the same workbook. Rename our worksheets. Now let's move to another workbook. So, for example, let's say that I have this chart in one workbook and a friend of mine says, Billy, can you send me this chart? And I said, I love to send you that chart. I just don't want to send him the whole workbook. I just want to send him this worksheet. So how would I just send my colleague that work? She That's right. I'm going to move it into another workbook. And to do that, watch this. I'm going to put the point over this worksheet chart one and I'm gonna when in doubt, Right click. And when I Right click Now I can go to move or copy. So I'm going to click on mover Copy. And now here I have my mover copy dialog box. But what I want to do is I want to move this chart one from this workbook into another. So here we see move selected sheets. I'm gonna click on the drop down, and I see my other open workbooks to move between workbooks. You got to make sure that you have the workbooks open. I'm gonna move the selected sheet to book four. And now here I see all of the sheets and book four and I'm gonna put that chart right before she she won. What I also want to do, though, is I want to create a copy. And that's what we have right here. I want to create a copy because I want to keep the original in this workbook and move a copy into the workbook number four. So I'll click. Create a copy, I press OK. And then now we see right here we have a chart one in our book four in our chart. One is still remaining in the other workbook. So now we know how toe move worksheets, Insert worksheets, rename worksheets How about color and the worksheets? Let's see that I want to have different colors on my worksheets so I can easily tell them apart. Well, it's very simple If I want a color This worksheet chart one when in doubt, right quick. That's right. And here you see tab color. So I'll just pick color like that. Read. I can also right click on the next sheet and go to tab color and give it this orangy yellow . Then I can right click on this next sheet and go to Tab Color and give that one this blue right here. And then Now here I can see all my worksheets have colors now that sound when I remove the colors and I want to remove the colors from all these three sheets at once, I don't need to go right Click on Chart one go to tab color and then no color. I can just select from chart one all the way the sheet to. In other words, I'm gonna group thes sheets. So to do that, I click on Chart one before I click on sheet to I'll press the shift key. So I clicked on Chart one. I hold down the shift key, and I click on she, too. Now what I've done is I've grouped. Oh, sheets. Look at the name of the workbook. When you look at the name of the workbook, it's telling us that we now have a group, and that means that whatever I do in any one sheet inside the group, it's gonna take place in the exact same spot in every single sheet in that group. More on that later will now selected all three of these sheets at once. So now I'm gonna right click on any one of these sheets and I'm gonna go to the tab color and I'm going to say no color. And when I click on no color now, all the colors gone there you have it. Now you can work with the worksheets. You know, a few shortcuts and we could move on to calculations will start with formulas, and it's keep bone learning.