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

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

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

Play Speed
  • 0.5x
  • 1x (Normal)
  • 1.25x
  • 1.5x
  • 2x
6 Lessons (28m)
    • 1. Here Is What You Will Learn

      1:53
    • 2. Five Things That Can Go Into An Excel Worksheet - F2 Is Your Friend

      5:52
    • 3. Minimize Repetition - Learn The Auto Fill Handle

      7:27
    • 4. Learn How To Create A Create Custom Fill Series - No More Typing That List!

      5:31
    • 5. How Many Rows and Columns In Excel? Select All And The Name Box Will Tell You

      3:01
    • 6. Insert Rows and Columns Like A Professional

      4:08

About This Class

Learn five things that can go into and Excel worksheet, and be on your way to Entering and Editing Data like a professional.

You will learn, for example, that when editing F2 Is Your Friend.

Plus, other methods of entering, and editing data in Excel.

Have you ever wished there was a faster way of typing a list, like a series of names, numbers, dates, etc.?

There is, it is called the Auto Fill Handle, and it is amazingly easy to use with its preset lists, and ability to create your own custom lists.

Learn how to use the Auto Fill handle, and unlock the secrets of this handy little tool.

Create your own customized lists known as, Custom Fill Series.

Ever wonder how many rows are in Excel 2007, and beyond? Before 2007 there were 65,000 rows, and 255 columns.

Learn how many are available now, and the fastest way to find out.

As you have noticed, Excel is made up of Rows and Columns. So you will need to know how to insert rows and columns when you need them.

There are many ways to do this, and in this class we learn fast and easy ways of inserting rows and columns into your Excel worksheets.

Transcripts

1. Here Is What You Will Learn: welcome to another powerful class in the Excel Basic Skills and Power tip series. This is building part one. Here you will learn five things that go into the Excel worksheet and be on your way to entering and editing data like a professional. You will learn, for example, that when editing F two is your friend, plus other methods of entering and editing data in Excel have your wish that there was a faster way of typing the list like a series of names, numbers and dates? Well, there is. It's called the auto fill handle, and it is amazingly easy to use with its preset this and ability to create your own custom lists. Learn how to use the auto fuel handle and unlock the secrets of this handy little tool. Create your own list known as custom Phil. Siri's like that way. Everyone there. How many roads Aaron Excel 2007 and beyond. Before 2007 there are 65,000 roads available at 255 combs. Learn how many are available now and the fastest way to find out. As you noticed. Excel is made up of rows and columns, so you will need to know how to conserve rows and columns when you need them. There are many ways to do this, And in this class we will learn fast and easy ways of inserting rows and columns and your XO worksheets. So come on. Now, step right in and take your excel worksheet building skills to end cell expert level. Let's keep on learning. And remember, don't get mad. Get skills. Oh, huh? 2. Five Things That Can Go Into An Excel Worksheet - F2 Is Your Friend: Now that we know about design, now that we know about our templates now we gotta work inside of those templates and to work inside of those templates. We move on to step number two of the Seven Steps of Excel Success and that is building. So once we have opened up our template, how do we keep on building? How do we enter data? How to insert worksheets? How do we move them around? How to insert Rose and the like? Well, this is where we're learning now, take a quick, uh, January is misspelled. Oops, that's gonna happen every once in a while. And there's nothing worse than spending hours working on a spreadsheet. And then suddenly people start to tell you Oh, it's a great spreadsheet, but ah, take you misspelled January way. We don't do that. Okay? We want to be remembered by the quality of our work. Remember our brand? What the people think when they hear my name. Keep asking yourself that. So whenever you have a word that's misspelled like this, you wanna do a spell check and the shortcut to a spell check in Excel is F seven shortcuts , tips and tricks So when I press f seven, you notice have my marquee on the cell containing the misspelled word I press f seven and that launches my spell Check. Now F seven does a spell check in all of office, not just excel. So it checks first contents of that cell, and it's give me some suggestions here. Now you've got to remember that Excel a stupid right? It doesn't think it just does. And the brands of Accel er where in the chair? That's right. You're the brains of excel. So we take a close look here, and we noticed some suggestions. And one of them is January. So we're gonna go with the January and I go right ahead and click on change, and I'll just press changed and it changes it. Now we see that the spell check is complete. Your good to go. So first that checks where you have the marquis, then it checks the rest of the document, and here we are. Okay, so what's a shortcut to do? A spell check at seven. You got it. Now let's talk about what are five things that can go into an Excel worksheet. Five things will five things that can go into an Excel worksheet. Well, one of them is text. What's this? Another one is numbers. We can also insert formulas and functions the heartbeat of excel. We can also add formatting and graphics. So those air five things that can go into an Excel spreadsheet, text numbers, formulas and functions, formatting and graphics. Now let's talk about editing. What? You're wondering, how I did that, where I grabbed the auto full handle and dragged down and pre filled everything well, that's coming up. In another lesson. It's called the Auto Feel Handle. Yeah, I know what you're talking about. This right here. How did I do, Dad? It's called a custom field. Siri's all right. So let's keep on rolling. Now that we know five things that can go into an Excel spreadsheet, how do we edit? Well, let's say that we will take this word text right here. We're gonna add the i N g to it and change that from text to texting. So there's a few different ways of doing this. One of them is I can just double click and notice when I double click. It puts my cursor right in the end, and I could just type I N g just like that. But what's this? Let's see, I double click right here on formulas and functions because I want to make this formulas and functions and something else. So if I double click right here, you notice where it puts my cursor right in between the L and the A and formulas. Then I have to press end on my keyboard to go all the way to the end so I can start typing well instead of double clicking. Because if I wanted to start editing at the end of functions, I think the press f two F two to add it. Remember that. Now watch this. When I pressed F to look, we're put the cursor right at the end, and that could just type in and whatever else I need to type. So one way that edit text is just to double click and it put your cursor wherever you double click in another way to add it. It's just the press F two now notice up with my marquee on a one and I press F two and then I can start editing. So we've got two ways of editing now weaken DoubleClick. We can do F two to add it, or we can just go to our formula bar. Take a look right up here at the formula bar. The formula bars veiling us, know what the contents of the current cell are. So here we have text so I can go right ahead and quick in the formula bar and that the I N g. Right there. I can also see that I've gone to sell a three that contains formulas and functions and inside the formula bar, I could go ahead and start editing just like that. So now we know five things that can go into an Excel worksheet and how we can go ahead and edit the contents of those cells three different ways. DoubleClick. After you read it or use the formula bar now you know how to add a data. Let's learn how to use that auto feel handle to create a custom Phil Siri's and let's keep on learning 3. Minimize Repetition - Learn The Auto Fill Handle: Let's say that your boss comes up to you one day and says, Can you make me a list of 21 days? Monday through Sunday, 21 days? And you said Of course I can. So you start typing in Excel and you type in Monday and then you enter and then you type in Tuesday and then you enter and then you start typing Wednesday and then you realize it's gonna take me forever to do this for 21 days. Well, there's a better way, and that is using your auto fill handle. So watches We type in Monday to start it off. And the other feel handle is this little dot on the lower right hand corner of your marquee that's actually called a handle. And it's the auto fill handle. So we're gonna put our pointer over the artful handle. You see, I change is still a little skinny cross. I'm gonna left quick left click to grab it, and then I'm just gonna drag and I'm gonna go down 21 days. So now when I've gone down to 21 days, I can release and look at that Monday through Sunday for 21 days, and now suddenly you're Bosco's. That's great. And you didn't really fast. However, what I really wanted, I forgot to tell you is I just want the 1st 3 letters of every weekday. What? Just the 1st 3 letters? Yeah, that's all I want. Well, you don't have to redo it all you can easy just edit the first cell. So I go to a one and I'm gonna take out all of the letters, so that only end up with the 1st 3 letters of the day of the week. And then now we're going to double click on the auto full handle. So here I have the wonderful handle and I want to double click on that. Watch, click, click. And there you have it for 21 days. It's only the 1st 3 letters and then your boss. Oh, yeah, I like that. Really nice. I'm sorry. I forgot to tell you I wanted all in lower case. Can you do that for me? Of course I can. So we go back to the beginning, it sell a one, and then we just make this in lower case, and then we double click on That's right, The outer, full handle. Quick, quick. And there you have it all in lower case. And now some of your boss goes it. Billy, I'm sorry, but ah, I don't want the weekends in their only want the week days. Can you go ahead and fix that for me? You're gonna go? Of course I can. So take a look. You don't need to go in there and delete all the Saturdays and Sundays. Look at this little icon that just appeared right there. Check it out. We're gonna hover right over it. And when we hover right over it, it tell us that that is our auto feel options. So it's click on it. And when you click on it, look at the options that we have And what we want for this case is we want the week days. We don't want the weekends just the weekdays only. And then we click Feel weekdays, Boom. And there you have it. Three weeks worth of week days in lower case weekdays. Only that ah, that's it. The out of fuel handle. Now, let me show you a few of the things that you can do with your auto feel handle. Let's say that we want to start a series of numbers, so I type in the one. I grabbed the outer full handle and I left, click and drag. And then I just got a 111111 Well, we can do is click on our wonderful options and say, Feel Siri's or what we can do as well is Start the Siri's so type in the one in the two. Then grab the artful handle and drag. And there you have your Siri's. Okay, so what's this? I'm gonna type in the to And a four. I'll select the two in the four. Grab the out of full handle left, click and drag where we're going to see. That's right. Multiples of two. I'm gonna go ahead and type in three in another cell type. In six. I select the three and the six grabbed the auto for handle left click. And when I drag what we're gonna see? That's right. Multiples of three. Just like that. Pretty neat, right? All right. So now let me go right ahead and delete this and let me show you something else that I am Today's date just like that. How did I do that? I press the shortcut. Control semi Colon. When that press control semi colon, it gives me what's called the date stamp. Today's date right there. So I press enter and there I have it. Now I'm going to grab the artful handle and that's a my boss says, Can you go out 52 days? So we go out 52 days right there. There we have it. And then now our boss says, Look, can you take out the weekends? I just wanted to be weekdays. Can we do that? Yeah, of course we can. How? That's right. Click on the auto feel options. So we click on the article options and then we just say, Just fill in the week days click. There you have it. That's just a week Days now. Look at all these other options that we have. We can just fill in the months quick. Just like that, we can fill in the years quick. There you have it. Same month, same day, different years, and we can just copy the cells. There you have it. Now that's the auto fill handle, and there's even more that we can do with this watch is I'm gonna type in first quarter. Now, how many quarters is there? It's not a trick question. How many quarters is there? That's right. Four. So I grabbed the wonderful handle in that drag. And there we have 4/4. Now. If I keep Dragon, am I gonna get 1/5 quarter suit? Quarter, Seventh quarter? Let's see. So I grabbed the awful handle and I keep Dragon and it starts it all over again. That's pretty cool. It's impressive to me. Help. You know, there's people doing this. There's people like you and me, their programming, all this. And if they can do all this well, we can do all of this to we Rocket Excel. We just gotta work at it. And that's what we're doing. We're working at it. This is a special version of Excel. This is the Teacher Edition and then the teacher edition. I could do this watch. I put in the letter, I then I grabbed my wonderful handle and that drag and boom Look what I have right there. I love to help my students excel. Well, you don't think that's ah pre programming to excel. You're right, it's not. And this is not the teacher's edition. That's why I'm laughing. There's no teacher addition to Excel. This is just Excel 2013 professional and what I did right here. It's called a custom Phil Siri's I pre programmed that listing to excel, So when they ride tap in the letter, I I can't even do it in lower case I. I grabbed my beautiful handle and I drag, and it populates for May, something that already put in there. I love to help my students excel, so let's learn how to create a custom Phil Siri's. 4. Learn How To Create A Create Custom Fill Series - No More Typing That List!: Now let's learn how to create a custom feel. Siri's What's a custom? Phil Siri's Let's say that you have multiple teams of people that you're working with. You got Team one, Team two, teen three, team four and they're made up of multiple individuals. So you always have the type in. For example, team won and they type in Bubba Any type in Mary and he type in Steve and so forth. And you're doing this for Team one. Team two, Team three, team four and you're saying to yourself, There's gotta be a better way to do this. There is create a custom. Phil Siri's something like this, so that when you type in Team one, you just grab the auto feel handle. You left click, and then you drag. And there you have right there, your custom Full. Siri's already set up pretty nice, right? Let's learn how to create a custom feel Siri's. I'm going to show you the fastest, easiest way. I'm always looking to do everything as fast and this quickly and effectively as possible. Shortcuts, tips and tricks is what I like to do. There's a lot of ways of doing the same thing in Excel. So let's synergize with each other, Having number six of highly effective people, synergy. And any time that you're seeing me do something, if you got a better way of doing that, going to discussions and share it with the rest of us now here we have it. The steps for creating a custom. Phil Siri's First make your list. Let's say that we have a team to and own team to. We have Steve. We have key oniy. We have Mark, We have Sarah and we have Cecilia. That's our team two. So we create our list. First, the steps for creating your custom. Phil Siri's. We created a list. First, we then select our list. You see how I did that watch? I'm gonna select this entire set of data. I could just do that pretty neat, right? We haven't learned that yet. Navigation shortcuts. There's coming up saying so. Step one. Type in your list. Step to selected. Now let's go to our options. We're gonna go to our file tab and in their file tab good. Our options. And once we're in our options, we're gonna go to advanced. So you created your list. You selected it. You went to your Excel options, and now you're going to click on Advanced right here. So it's quick on advance. When we click on Advanced here, you see that we have editing options are encouraged to explore this section, the advanced options. There's all kinds of really great tools and features in there. So now when we're in our advanced options, we see editing options. Where we're looking for this to say is General, So we go ahead and we scroll down practically all the way to the bottom. And here we see General right there. So step one, create your lists. Step to selected Step three. Go to your Excel options, go to advanced and go to general. Once we're in general, we're going to select create list for use in sorts and fill sequences. Edit custom lists. Once we click on edit custom this, it's gonna show us our custom list dialog box. Now let's see that you're writing of this from scratch. What you would do is good gone new list and then just type your list in here, and then you can just add it. Well, no, we don't need to do that right now because we're exercising happen. Number two of highly effective people, which is begin with the end in mind. We already typed up her list and we went ahead and selected it already. And because we already did that, Well, we're gonna have to do is import the list from these cells. You notice that this was pre selected for us? So now we gotta do is click on import, and it's gonna import it into our list entries. So import click. And there you have team to notice. I just added it right to her list entries. Now it's gonna press okay twice. Okay. To get out of the custom list dialog box and okay to get out of your excel options. Now, whenever you type in team two, we describe our auto feel handle and just drag it on down. And there you have it. Oh, looks like I misspell Cassie. Leah. So let me do an F seven, and there's my spell check, and it's giving me some suggestions. And it identified that Cecilia is one of them, and that's what I want. So change it and, yes, continue searching through the end of the sheet and it. Whoa, it doesn't know Hawaiian names. Uh, and that's why I say the brains of Excel are in the chair except those no Hawaiian names. But I do, and that suggestion is not necessary. Now. The spell check is complete, your good to go. And now we know how to create a custom feel. Siri's Let's keep on learning. 5. How Many Rows and Columns In Excel? Select All And The Name Box Will Tell You: Have you ever wondered how Maney Rose Aaron Excel? Have you ever wondered how many columns Aaron excel? Well, let me teach you some quick shortcuts that will help us answer these questions and learn how to navigate within our worksheets. This is my favorite shortcut in all of excel. Now, let's say that we want to go to the very last row you could scroll like I'm doing right here. And this is gonna take a long time to get down to the very bottom. So what we do is we use a navigation shortcut like this, and there I am. We have 1,048,576 rows and excel. Isn't that nice? I love that. Did you know that before 2007 we only had 65,000 rows? What do you supposed to with 65,000 rows? Nothing. We need 1,040,005. 76 now watches. I'm going to use my navigation shortcut to go right back up to row one. Just like that. Now, have you ever wondered how many columns are in excel? So what on that, just navigate to the very last column that's calling X F D. That's common number 16,384. How do I know that? Well, because Excel told me. Let me show you one quick way that know how many rows and how many columns are in excel. So now I'm going to go back. Two column A. So there I have it. Now, before I show you what the's navigation shortcuts are that I'm using, Then you go in and show you ah, feature of the name box. Remember the name box that lets us know the location of our marquee. Well, that name box right now, I can think of about 25 features at that name, Bob Task. And I'm not going to discuss him in this course. I encourage you, though, to go ahead and do a little research on features of the Excel name box. Now watch what's gonna happen here. What I'm gonna do is I'm going to press this button right there. That but them right there is called your select All button. Now, when I press the select all button, it selects all 16,384 columns. 1,048,576 rows and all the cells in between Don't press print when you press the select all , Uh okay, this is press and hold the select all instead of president one time like I'm doing right now Press and hold it and look up with the name box someone up press and hold the select all button. Now we're looking at the name box and we see that we have 1,040,576 rows and 16,384 columns . So there is one way of knowing how many rows and columns you haven't excel. Now let me show you the navigation shortcuts that I'm using. 6. Insert Rows and Columns Like A Professional: Now let me go and show you how to insert some rose and insert some columns. I'm going to show you two ways. First, I'm gonna show you the long way of going about inserting rows and columns. Then I'm gonna show you the shortcuts, tips and tricks way of doing it. So to insert a row, select where you want to insert around some of the select row four and I'm gonna insert a row right there to insert a row. I could just right click once I've made my selection. And then I see a menu appear and one of the options is insert. Let me give you a quick tip. When in doubt, right quick, see what your options are. Nothing happens. Left click. Nothing happens. Go to your cell options. So here we right click and we see when the choices is insert. So when I click insert it's gonna insert a row right above my selection. So I click insert and it insert a row right above my selection. What if I wanted to insert two rows at once will select two rows and then you got it right ? Click and insert and I just inserted to rose. What if I want to delete Rose? Let's say someone delete these three rows, someone to select these three roads that I just inserted and then right quick and delete, and that's gonna delete my selection. Now let's talk about inserting a column so simply select where you want to insert a column and then you can right click and then insert, and it's gonna insert a call him right before your selection. You see that? Now let's say I wanted to insert two columns at once. I can select two columns and then right click and then insert would. If I'm gonna delete these three columns, I can select these three columns and then right click and then divvy it. Um, so now you know the long way of going about inserting and the leading rows and columns. Now let me show you the shortcuts, tips and tricks way of doing it. When I want to insert a row, I select where I wanna insert a row. Then I just press control. Plus, I want to keep inserting Rose control plus control. Plus, Now let's say that I want to defeat these three rows. I can select these three Rove's and then that compress instead of control. Plus, I press control minus. That's right. So control plus inserts control minus removes. Watch. I select these three roads right here and I press control Plus, and it's gonna insert three rows of press control minus and it takes him right out. I wanna insert column, select right when they started calling that press control. Plus, I want insert another column control. Plus, I want to select two columns here because I want to insert two Commons saw Select two columns and in that press Control Plus and I just inserted two columns. I wouldn't delete these four columns that I inserted, so I select them all and then to just the freedom I press control minus you got it control Plus to insert rows and columns control minus to take him out. Since we're learning shortcuts, let's learn a few more shortcuts. Let me show you a short cut to hide and un hide the ribbon. Notice the ribbon. Right now the ribbon is hidden. That way I can maximize my desktop space. So let me show you how to hide and then hide the ribbon to a new hide the ribbon because it's hidden right now I'm gonna press control F one control F one on hides my ribbon. I want to hide the ribbon. I could just press control F one So control F one on hides and hides your Ribot control F one. Next, let's learn how to select cells, select individual cells so, like the range of cells, or select multiple non adjacent cells and that's keep on learning.