Transcripts
1. Trailer: Hi. My name is Al Chen. Welcome to my Skillshare series, Intro to Excel. Now I used to think using Excel was meant for data nerds and people that didn't have social skills and after I started working, I realized Excel is something I will use every single day. In class one, we're going to focus on just the interface and we're going to talk about workbook structure, how to write formulas, and how to format your Excel environment to make it work for you. Excel is a useful tool no matter what line of business you're in, whether you're working for an advertising company, a nonprofit, you'll come across using a spreadsheet at point or another, so please take this series and be better and faster in Excel.
2. Using the Ribbon: Welcome to unit one, using the ribbon, one of the basic features of Excel to click on buttons to do operations in Excel. Right now we have Excel opened, right now you can see we have all the cells and we're on the 1.1 worksheet, less than one. The ribbon is basically everything up here that I'm highlighting with my mouse. It's all the menu bars, the buttons, the colorful font coloring here, and we just want to learn how to use the ribbon. So, the ribbon is basically these tabs along the top that give you access to all these different things in Excel. So, on the Home tab, you have all the fonts, you have the number formatting, and you have other things relating to operations in Excel. As we move on to the other parts of the ribbon, there's layout tables, charts, these are all other advanced things you can do in Excel and we'll talk about these things in future lessons. But just get used to clicking around on the ribbon, clicking on the different buttons here, maybe even just trying to see sub-menus within Excel. So, that's how the ribbon works. Along the top of Excel, here, this is kind of in any other Mac program, you'll have the actual menu bars and you can go on View, Insert, Format. A lot of these menus are actually similar to what you'll find in the ribbon. So, play around with those menus too and you'll see other sub-menus as well and that's the first step in using the ribbon. The second step is hiding the ribbon. So, a lot of times when you're using Excel the ribbon gets in your way because you want to be able to see as many possible cells in your worksheet. So, right now I'm highlighting all these cells in here. So, if you want to hide the ribbon there's just really small little up arrow right here on top, right? If you click it, it hides the ribbon, and now you have more space to basically play around in Excel. So, you can click on this drop-down arrow again to unhide the ribbon. I usually tend to hide the ribbon because I like to have a clean slate when I'm doing stuff in Excel, but it's really up to your personal preference. The final step in using the ribbon is clicking on the settings gear at the top right. So, this button right next to the unhide and hide ribbon thing, you click on this, and you'll click on ribbon preferences. And this is again, ways you can customize your ribbon so that it fits your style or whatever when you're doing stuff in Excel. So, things like turning on the ribbon when you open workbooks, I usually have it on, expand ribbon when workbook open. So, that just means that when you open Excel, the ribbon automatically shows up. Again, I tend to have it hidden, but that's really up to you. You can hide group titles, all kinds of stuff. Appearances also is something you can play around with in terms of the colors. You can organize whether or not you want to include certain menus in the ribbon. So, these are all preferences based on what you'd like to do in Excel. Finally, you can customize the ribbon tab order. This is where you can literally say I want layout to come after tables, or Charts to come after Smart Art. It's really up to how you want to customize it. So, yeah, that's really the basic thing here about the ribbon is it's there for you to access different operations in Excel, play around with different buttons, and most importantly is just having it customized how you feel comfortable using Excel. You probably have realized that hiding the ribbon might be better for you because you're building graphs, and gnatt charts, and formulas, and you want to be able to see as much as you can in Excel. So, that wraps up the first lesson in using the ribbon.
3. Workbook Structure: Welcome to unit two, Workbook Structure, where we're going to look at how a workbook in Excel is structured using worksheets, renaming worksheets and so on and so forth. Workbook is essentially a file in Excel that contains many worksheets. The file you have right now in front of you on your MacBook, this is a workbook and as you can see there's many different worksheets in the workbook like README, 1.1, 1.2, 1.3, and we're on the 1.2 worksheet within the intro to Excel one workbook. So, let's get into typing stuff in a worksheet. So, you probably have done this before either on Excel or Google Spreadsheets, but we'll just try this here. You can type anything in these cells here. All you have to do here is just type and then press an arrow key to get out of the cell or you can press Enter after you type something like I'm doing here, Enter, and that's basically entering stuff into a worksheet. A worksheet can be many different rows as you can see here. It goes all the way down to, I don't know, a million. Yeah, a million point five or whatever, but most of the work will be done in this kind of cell A1 to T column. So, it's important to be mindful of how work sheets work because when you get more advanced in Excel, you'll be linking different worksheets together because you'll be referencing certain cells from worksheet 1.3 and other cells from 1.4, and we'll talk about this more in future lessons. The next step in understanding workbook structure is just creating a new worksheet. So, once you have a workbook, how do you create new sheets? So, the main way is actually clicking this plus sign down here. At the very end of the work sheets at the bottom, you'll see a plus sign. When you hover over it says insert sheet, you click on it, and there you go. You have a new sheet. It's automatically titled Sheet 1 or Sheet 5 or whatever. You can click as many times as you want to insert many different sheets. So, treat each sheet like a blank canvas when you're building your array of art design or something like that. Each different sheet has different kind of canvas for you to play around in Excel. The final step is after you create the worksheet is just renaming the worksheet. Naming worksheets is probably one of the most important things in terms of structure because you want to be able to understand quickly what the sheet has. So, if you have one sheet that contains inputs and you have one sheet that contains charts, then you want to clearly say that the sheet is called inputs and the other sheet is called charts. So, let's say we want to do that. So, in Sheet 1 here, double click the sheet name, and you'll get into the kind of edit mode and we'll call this inputs. So, I'm just going to type in inputs here and then hit Enter and there you go. Your Sheet now is called inputs. Sheet 2, let's rename this to charts. I'm going to double click the chart name or the sheet name, sorry. I'm going to type in charts. There you go. So, again, clearly mark what your sheets are called not only for you, but for people that open your workbook, they can understand quickly what your worksheets mean, what kind of data they expect to see on the worksheets so on and so forth. So, that wraps up this lesson. Most important is just understanding how to create new worksheets, renaming the worksheets and how they all function together. One quick thing on worksheets again is if you want to move worksheets around, all you have to do is just drag and drop. So, you click on the worksheet down here and you'll move it around and you'll see that you can quickly move the location of the worksheet within your workbook. That wraps up lesson two.
4. Formatting: Welcome to Unit three. Formatting. We will look at how to do basic number formatting and to bring up the Format Cells menu in Excel. So in the first two lessons, we talked about looking at how Excel works, the worksheets workbook, and now let's get into really basic formatting within Excel. Formatting is actually a really important aspect of using Excel because you want to quickly know what kind of data you're working with and it helps to have good formatting principles and discipline to figure out how to format things correctly in Excel. So, just follow me along here step by step. First step here is changing fill and font color for a given text. So, obviously we know how to enter things into Excel. We have some text here in cells F4 to G5, and let's say we want to convert, we want to make this font color "Hello" in this, we want this font color to be red, right? So, we go up to where we've been here and all we have to do is click on Font Color and we automatically can click on the color here because it's already marked as red. Let's say you want to change the font color to, let's say blue. You go here, you can click on the dropdown arrow and you'll see all different colors you can change it to. So, there we go. That's now blue font color. Fill color is kind of the same thing as background color. So, let's say I want to convert this, I want to change these cells to have a red fill color or background color. So the button next to the font color button here is fill color. You see it's got a paint bucket that's spilling some pain out. You click on the drop down arrow here and we want to make it red so we're going to go down to the red color, click on it and there you go. Now these cells have red fill color, so you might see sometimes when you're looking up things to do in Excel, if you do not do something people will say things like, "Oh use this fill color or that font color." That's basically what we're referring to. Font color is the actual color of the font, fill color is simply the background color. The next step is understanding how to use font colors effectively when you're working with actual data in your spreadsheet. So, this is a principle used by financial analysts, bankers, consultants when they're building a lot of their models and formulas in Excel, and there's three general principles here for understanding font colors. So, anytime you enter in hard-coded numbers, so these are numbers that you're actually typing into Excel, you always want to color that font as blue. So, see here we have numbers 100 and 200, and I've marked these numbers or changed the font color of these numbers to be blue. The reason is because these are not formulas, these are not references to other cells, this is straight up numbers entered into the worksheet so we have 100 and 200, right? Now, anytime you have a formula, that's something you want to have in black. That's a default color in Excel. So, here we have the number 300. Now, this three hundred is actually not a hard-coded number, I didn't enter this number into Excel. If you double-click on the cell, you'll see the sum of G9 plus H9. So, since this is a formula, we want to make sure that the number is colored as black, the font color's black, and we'll see why that's important in future lessons. Finally, anytime you reference a cell from another worksheet, that should always be a green font color and that's what this cell is, which is the Workbook Structure name. When I double click this you'll see that, it's referencing something from worksheet 1.2, and which is the previous worksheet here 1.2 and it's cell A1. So, if we go to cell A1 of worksheet 1.2, what will we see? It's actually the text Workbook Structure. So, that's why when we reference it here, it's going to be Workbook Structure. So, again, anytime you have something that's referencing another cell from another worksheet, you always want to make sure that that font color is green. The reason why this concept is important is because when you're building a really complex model, you want to tell someone that's looking at your model or even yourself that this number is hard-coded, this number is a formula or this number is referenced from some other worksheet. If you have everything as black or everything as blue, then it's going to be hard to delineate which cell is from which, that's why font coloring or font color is a really important concept in Excel.
5. Formatting Continued: The next thing we're going talk about, next step is formatting numbers in Excel. So, let's try to add a comma to this big hairy numbers. So, 33987234. So, the main way you can do this is actually just go into your ribbon, and underneath that number kind of menu, you'll have this little comma. Once you hit that comma, it automatically converts that number into comma format with two decimal places. So, that's how you add a number format, a common format to a number that's unformatted. Now, let's try to round this number to two decimal places. Right now there's four decimal places. How do we round this to two decimal places? So, if you click again on the ribbon under number, you have these two buttons here. One with this left arrow and one with this right arrow. If you click on the left arrow, it will just add more decimal places. If you click on the right arrow, it will give you less decimal places. There you go, we just click on these buttons a few times and we reduced number of decimals. Finally, let's convert this number to a percentage. So, this is 0.6, we want to make it 60 percent. So, how do we make that a percentage? Again, on the ribbon, we have this Percent button. If you click on it, it just converts that number to a percentage, and there you go. So again, number formatting is really important because sometimes you'll have a bunch of numbers that aren't formatted correctly that don't have commas, that don't have percentages, that don't have the right decimals, and you want everything to look very neat and tidy. Formatting numbers is really key to make it look presentable, making your Excel file presentable to somebody else. So, the next formatting step is aligning text to the right or centering text, and we're going to use the ribbon as well as the Format Cells menu, which is a new menu which we'll talk about right now. So, we have this text here which says, "Align this to the right." How do we do that? So, right now, as you can see, it's all flush aligned to the left. Let's align it to the right. So, if I go to the ribbon again, we have a section called Alignment. Pretty self-explanatory, and you have this button here, this button here and that button here. Left, middle, center, and right. So, we want to right align, we just click on this, right align, it gets it right aligned. If we click on the middle button, it gets center aligned. If you click on this left one, it gets left aligned. With this one, let's use the Format Cells menu. So, let's align this to center, right now it's still left aligned, let's align this to the center. So, if I click on the cell, and I'm going to bring up the Format Cells menu by pressing command 1. Or the other way you can do that is just going to Format, and then go into Cells, and we want to align this center. I'm going to click on Alignment here, and then click on Horizontal, and then General, and Center, and then hit OK. There you go. I just use Format Cells menu to align the center. You notice that took a lot of steps. So generally, I would recommend using the ribbon for this case because the format cells, many it does take a few more clicks and steps to get to, so just keep that in mind as you're formatting. Finally, we're going to talk about Center Across Selection and Merge cells. So, most of you guys if you've used Excel in the past probably use Merge cells. The reason why Merge cells is not a good way to do things in Excel is because when you reference that cell into a formula, it gets all crazy and it doesn't know what cell to use because you merge them together. So, I'm going to show you why you should use Center Across Selection instead of Merge cells for formatting purposes. So, let's try to do the regular merge. So, this text should be merged. Let's merge this cell with that cell, F23 and G23. So, I'm going to bring up the Format Cells menu again. Format, Cells, we're on the Alignment tab and under Text Control, just hit Merge cells. Check the check mark. Hit OK. You notice that this has became one big cell. Like it used to be two cells and now it's one cell. Now, the problem is if I try to reference this cell, I can only reference F23. G23 is gone because I merged it with F23. So, this is why when you merge cells they can get hard to do formulas because one of your cells will go away or multiple cells may go away. Now, the better way to do this is to do something called Center Across Selection. So, normally, people do merge cells because they want to center something like this, so it get center aligned. But the better way to do this, so that you don't have that formula thing I talked about, is to do Center Across Selection. So, let's take this cell and do a Center Across Selection with that cell. So, I'm going to click on Format, Cells, and then under Horizontal, this is where you'll find the Center Across Selection menu item. You click on that. Hit OK. There you go. Basically, you've done the same thing as merge except you didn't waste a cell. Now, G23 is still available for you to use, and you still have that center align text. So, that's why it's important to do Center Across Selection versus Merge cells when you're trying to do center alignment. That wraps up lesson three, which is understanding how to do some basic formatting within Excel. Number formatting using color coding to color code specific data in Excel, and just number formatting all kinds of alignment. These are all really important things to know how to do because formatting your worksheet and workbook is very important to how you present your final model.
6. Selecting Data: Welcome to unit four, selecting data. In this unit, we will look at how to use select data and multiple ranges within Excel, and how to build some basic formulas while selecting data. This lesson is all about selecting data, and selecting things with your mouse in Excel, and how to do some basic formulas fills, and we'll talk about what that means in this lesson. So, in the previous lessons, we talked about entering things in Excel workbook structure and now, this lesson is all about selecting things. Being able to select cells in Excel seems like a no-brainer, but being able to do it effectively is important because you want to be able to move data around really fast and really quickly. So, the first step is just kind of learning how to select different ranges within Excel. So, the basic way to select data is this drag and drop, right? So, if I select cell H4 here with my mouse, I can just hold my mouse down right now and drag, and you'll see that the selection is expanded to include all the cells that I've dragged and dropped. Now, let's say I wanted to select the cells and columns L through N, all these A's, but still have the original selection. Select it, right? So, that means is if I select this, and I'll also want to select this. If I let go off my mouse and select this, you notice that the blue outline went away. So, how do I select this and this at the same time? So, the way I do that is using the command key. So, the first step we want to do is select these cells, right? Drag and drop the mouse, and now using your keyboard, hit the command key and hold down the command key and don't let it go, and now select these other cells in columns L through O. You'll notice that there's a light blue outline that shows that these two ranges now have been selected. So, this is a quick and dirty way to select multiple ranges because sometimes, the ranges won't be connected or you don't want to select everything in between the two ranges. So, this is how to use the command key to select multiple things in Excel. So, step two here is using this auto-fill feature, which involves selecting cells in Excel as well. So, we have the numbers 1, 2, 3, 4 here. Now, how do I make these cells below? I want this to be 5, 6, and 7, right? Now, how do I do that without actually using the keyboard? I don't want to actually enter in numbers 5, 6 and 7. Excel has its own brain that figures out patterns when you're using numbers and different text in Excel. So, if I select these numbers here are 1, 2, 3, 4, and if I hover over the bottom right of the selection you'll notice that this little dark plus sign comes up, and what does that mean? So, that means that you can actually auto-fill these values down below. So, what that means is, if I hold down my mouse while that plus sign shows up pressing clicking my mouse now, I drag down, you'll notice that the blue box expands a little bit. If I just drag and drop like that, you'll see that the numbers automatically auto-fill with kind of the pattern that it saw in those first few numbers, and let's try to do that with texts. We have January, February, March, April. I am selecting that, I'm going to go to the bottom right hand corner again. I'll see a black plus sign. That's why I know I can auto-fill. I hold down the mouse and I drag and drop, and you'll see that the blue outline kind of expands and I'm dragging down a few more cells like go, and Excel somehow new to automatically fill those other cells with May, June, July. This is when you are entering a lot of data in Excel and you want to quickly fill in the rest of the data by using auto-fill. This is how you use that feature within Excel. So, it's really super helpful feature. Definitely use it when you're playing around with things in Excel. Finally, we want to know how to get into edit cell mode. So, when you're building formulas. Most of the time you're going to be in edit cell mode or formula mode. What that means is just kind of looking at the underlying formula behind a cell. So, here we have two numbers three and three, and these are hard-coded as you can see when I double-click them it's just number three, but this six here is a formula. If I double-click the six. You'll see that it's a sum addition of H21 this cell plus l21. So, three plus three equals six, right? So, another important thing before we move on is, you'll notice that in the previous lesson, we talked about number formatting and how hard-coded numbers should always be blue, as we have seen here and formulas are always going to be black, which is number six. So, back into edit cell mode all you have to do is notice that when you double-click. You can get to edit cell mode and you can actually add more things to the formula. So, if I want to add I22, l can type in I22, and notice that this little kind of color-coded box pops up telling me that, okay, this formula now includes a new cell l22 in the formula. If I want to get out of edit formula mode, I can just press Escape, and I'll get back to the number six, which is the original formula l had. Another way to get into edit cell formula mode is actually just going to the top bar here, which is below the ribbon, and you click on the formula and it automatically brings you into edit formula mode and here you can also type in your extra cells here. So, right now I'm typing and I22 again, but I'm not doing it in the cell I'm doing it in the formula bar up here. So, those are the two ways that you can get into the formula mode. So, this lesson again was all about selecting data using auto-fill, and how to get into edit formula mode because these are kind of the main ways that you're going to be utilizing Excel when it comes to selecting data and kind of moving data around in a way. An edit formula mode is kind of the one of the most basic features of Excel, which is learning how to edit a formula. And that wraps up Lesson four.
7. Writing Formulas: Welcome to unit five: Writing Formulas. In this unit, we will write some basic formulas to break down some texts and to also calculate some basic functions using Excel. Formula writing is somewhat of an art and science. Obviously, it involves a lot of logical thinking, but at the same time it also involves some creativity and how you build formulas and correctly write formulas to get the results that you want. So, we're going to walk through a few basic formulas in Excel that you should know how to use in everyday kind of modeling and analyzing data. So, the first step is, we're just going to again check out how to use formulas by looking at the formula bar for cell J4. So, J4 here is a formula, and you can see here it's a sum of 3 plus 1 plus 4. Again, if I click on the formula bar here, you can also see the formula as well. Again, formulas basically involve referencing cells in your worksheet or other worksheets in the workbook and then applying operations, such as addition, subtraction, and other kinds of functions to help you build your formula in Excel. Now, the difference between a function and, well, let me back up here, a function in Excel is kind of like this tool you can use to get result you want. So, for instance, sum, if I wanted to add these numbers up without using the plus sign, I could use the sum function. So, let's try to get the same function, same result here, number 8, by using the sum function instead of using plus, right? So, in cell K4, I'm going to write the formula equals and then the function sum and then the left parenthesis. So, here now, we can use the sum function to sum up the numbers. So, equals, sum, left parenthesis and we'll click on cell G4, and then comma, H4, and then comma, I4, and then right parenthesis; and there, we just wrote the same formula as G4 plus H4 plus I4, but instead we use the sum function. The sum function takes those three cells and you just have to add a comma after each cell. By enter, you'll see that I get the same result, which is eight. So, that's the basics of writing a formula. Now, other kind of basic formulas you should learn besides the sum function are the kind of things, like left and concatenate, right, and I'll talk about what that means here, but I want to help you get used to writing formulas, which is simply writing the equal sign, the function, and then the left parenthesis, right? So, let's try to use a sum function again here. We have four orders. Each order has different value of dollar value; and in cell I13, I want you to write a sum function summing those numbers. So, how do you do this? So, we first write equals, because that means we're starting formula, sum, the word sum, left parenthesis. Now, we could do this where we click on each cell: I9, comma, I10, comma, I12, comma, I13, right and then, right parenthesis, and that's our sum formula. If I enter, that's going to 223.96. That is our sum. Another way you can write the sum formula is actually writing equals, sum, left parenthesis, and instead of doing each number, each cell followed by a comma, you can actually just select the entire range here. I'm going to select this whole range. I tend through I13. You'll notice that the syntax is I9 colon, I12, then I'm going to hit right parenthesis, enter, and there you go, you have the sum, same thing. Now, let's do the average order size now. So, we just took the sum of the orders, and now let's try to find the average. The way to write that is equals and the word average. Very surprising function there, right? Average, left parenthesis, and then same thing, we're going to select these four cells, right parenthesis, enter, and the average there is 55.99. Another way to do the average, and this is just simple math, is you can take the number 223.96 and divide it by 4 since we know we have four numbers, right? So, how do we do that? So, we had the sum here. Now, in this cell, let's write equals. Now, we want to take that 223 and divide it by 4. So, how do we do that? We have equal sign. We click on to 223.96 cell, which is I13, we hit the backslash, which means division and divide, and then enter the number 4, and there we go. We'll get the same result: 55.99, and that's how you do basic formula writing in Excel.
8. Writing Formulas Continued: Other kind of, again, formulas or functions to be aware of are left and concatenate. Left kind of helps you take characters coming from the left hand side of a cell. So, let's say, I want my cell J9 to be only the letters Ord from this cell, H9, right? So, how do I make J9 basically viewed as Ord So, the way I could do that is write the left function, which is equals left parentheses, and then the first cell I have to click on is, you'll see here that Excel automatically popped up this text thing, I have to click on the cell that I want to take the characters from. So, I'm going to click on H9 here, and then comma, and we only want the first three characters, we want to show Ord So, that's three characters. I want to press number three, right parenthesis, and then enter and there you go. You notice that the left function takes a given cell's text and then you can tell Excel how many characters you want to take from that cell. So, we just told Excel that we wanted to take cell H9 and take the first three characters, which is Ord. Another quick function to know is concatenate. It's pretty long function, but all it means is just adding two cells, is text together. So, I'm just going to show you really quickly what that means. Concatenate. Concatenate, long word, left parenthesis, and I want to concatenate or add this cell text, which is order one, with this number 44.99, click on that, comma, right parenthesis, enter, and you notice that it automatically added the two cells, ISTEXT, together. So, order number one plus the number 54.99. So, concatenate is also kind of a useful formula to get to know, and in terms of how to formatting your text. The next step is seeing what happens when you add the dollar sign in front of cell references. So, you notice that when we reference cells, we basically are saying like, "This cell equals some other cell." Right? I'm seeing here, I want K20 to equal M20. Now, you'll sometimes see an Excel that there are these dollar signs next to the column and the row reference. So, for instance, right here, we are referencing cell I22, but there's dollar signs in front of the I, and a dollar sign in front of the 22. Now, this is called an absolute reference, that means that no matter where you copy this cell to, it'll always remain I22. Relative reference basically means that there are no dollar signs in front of the column or row. Now here, we'll see that it's also I22, but there is no dollar sign, right? So, what does that mean? Again, an absolute reference means that the cell does not change. So, if I copy and paste this. So, copy and paste is just command C, and if I paste it somewhere else, you'll notice that the formula stays as I22 because there's dollar signs in front of the column and the row. Now, if I copy and paste this number over here, you notice that it changes because the reference now is K22. The reason is because the dollar signs lock the common row. We don't have the dollar sign, it will move the reference all over the worksheet and you have no idea where you're referencing. So, sometimes it's important to know when to use absolute references because you want to make sure that that value stays constant no matter where you move that formula to. So, as we saw here, I22 stays constant, but here, I22 change to K22 because we didn't have the absolute reference. Finally, one of the most used functions in Excel is VLOOKUP. Now, VLOOKUP, basically allows you to find a unique value in a list and then pull in something else from that table. So, here, we'll see a list of characters, show and rating. You've all probably seen these shows of four Fresh Prince of Bel Air, Saved By The Bell. Characters; Will Smith, Screech, Carl Winslow, and we want to basically tell Excel, "I want to find from this list." Imagine if this was a thousand rows with various characters and the shows. If I tell Excel, "I want to know what shows is Cory Matthews is in?" Right? Now, I could easily look to the list and find, "Okay, I know Will Smith is Fresh Prince of Bel Air, Screech is Saved By The Bell. Okay. Cory Matthews is Boy Meets World." But, how do I do this in a more automated way? So, this is where a VLOOKUP comes into play. VLOOKUP, we're going to write here is a formula that takes in a few arguments, and we'll walk through each one individually. So, VLOOKUP is we're going to type in the vlookup function, left parentheses. So, the first thing we want to look up is, the first value you want to enter into the vlookup function is the value we're trying to find. So again, I want to find a show associated with Cory Matthews. I'm going to first click on Cory Matthews with his L29, and then comma, and now it says, "Table array." So, we have to select the entire table that contains that our data. So, I'm going to select this whole thing right here. Right? So, it's H27 to J33, and it's important because we want to make sure that a character is the first column of the dataset because we're trying to find what show Cory Matthews is in in the dataset. So, we have our value, which is Cry Matthews. We have our table, which is this right here, and then comma. Here is the important part of VLOOKUP is returning the column index. All that basically is saying is, within this table, which column contains the value you're trying to take back. So, we're trying to find the show that Cory Matthews is in, right? So, the character field is column one, show is column two, rating as column three. So, we're trying to pull in the show, so we're going to put in the number two here as the index,right? Then, we key in another comma and range lookup. You don't have to really know how this works; but by default, you always want to put the number 0 or the word false, and that basically means that you want the VLOOKUP to find the unique value of Cory Matthews and nothing else. So, we're going to write number 0, and then write parentheses, enter and there you go. You'll notice that the vlookup function return Boy Meets World because we told Excel we want to find Cory Matthews in this list of characters. The table is obviously that this table here in green, and the column index, which is the most important part of VLOOKUP is which column do you want to return from this table? We're not trying to return the rating or trying to return the show, so show is number 2 within that table. Once you hit enter, you get the value. So, we talked about various formulas here. This is probably the starting point for how you're going to learn to build formulas. Learning how to use left, concatenate, sum, average and VLOOKUP. Definitely try to play around with the VLOOKUP formula a lot because that's going to be one of the most used formulas when you're building stuff in Excel, and that wraps up Lesson five.
9. Lists & Sorting: Welcome to Unit six, listing and sorting. In this unit, you will look at a basic list of data and learn how to filter and sort it, as we find the data we need. Most of the time you would be using Excel to sort through a list of data, you're getting a bunch of numbers, and characters, and data that you want to quickly filter and sort to find the results you want. So, we're going to talk about some basic filtering and sorting operations in Excel that you can use to filter and sort your data. So, the first step here is we have our data set. Let's take a quick look at it. If you follow along in our lesson, you'll notice that we use the same data from Lesson 1.5. In this data set, we have again character, and we also have show, and we also have ratings. So, Screech, Zack Morris, AC Slater, Kelly Kapowski, they're all from Saved by the Bell, great show, Family Matters, Boy Meets World. We want to quickly filter this list by show, by rating, maybe by character. So, how do you do that? So, the first thing is we want to click on the first cell in the table, which is G4 on our data set. I want you to filter this list by show first. Now, the way to access the filter function or operation in Excel is through the data tab in the ribbon. So, click on Data, and then under sort and filter, you'll see a filter panel thing. Click on Filter, and you'll know that you've applied your filter correctly when you have these drop-down arrows show up in the fields names. So, let's first filter by the show Saved by the Bell. Someone click on a drop-down arrow here. You notice that it brings up this submenu that you can drag and drop, move around. But I want to only have the data around Saved by the Bell show up. So, I'm going to uncheck Boy Meets World, I'm going to uncheck Doug, I'm going to uncheck all these other shows until I get just Saved by the Bell. There you go. So, my dataset is now filtered. Now, let's say I want to filter by show and by rating. So, let's get rid of our filter. You can actually just go over the filter by clicking on the filter thing again. So, let's filter by two filters now by the show name and by rating. So, let's filter by Saved by the Bell and by Hey Arnold, and only filter on those two shows that have a rating of eight. So, the first thing we want to do is click on the Filter menu again. Again, we want to filter on just the shows Saved by the Bell and Hey Arnold. So, I'm going to quickly select Hey Arnold, Saved by the Bell, and then there you go. I have filtered on those two shows. Now, I want to do by rating. So, you can already notice that the only show with a rating of eight is Saved by the Bell, but let's filter through the list anyways. So, click on the drop down arrow on Rating. You'll notice it has two numbers six and eight. I only want number eight. There you go. Now, I've filtered this list by both show, which was originally Saved by the Bell and Hey Arnold. Now, I only want to show the shows, those two shows that have a rating of eight, which is just Saved by the Bell. So, that's how you do a quick filter. There's various other options you can use with the filter menu. You can filter something by the value that it has, maybe you want to filter by things that only begins with the letter, let's say F. You'll notice that the two shows that begin with the letter F are Fresh Prince of Bel-Air and Family Matters. So, definitely play around with the filter menu and it's all pretty self-explanatory on how you filter things, but it's important to know that you can filter by multiple fields at once to get the results that you want in Excel. Now, the sibling to the filter is the sort menu. So, which is right next door to the filter button. So, if you click on the sort menu here, you can quickly sort something by ascending or descending order. So, let's sort this list by rating and let's sort it by ascending order. So, I want the shows with the lowest rating at the top to the highest ratings at the bottom. So, I'm going to select rating, click on sort and select descending. Oh, sorry, ascending, because I want to go from low to high. So, there you go. I just filtered the list and now it shows all the shows and the characters from lowest rating to highest rating. I can obviously do descending order. The interesting thing about Excel is you can also do alphabetical, obviously. So, if I click on character, I want to sort this by alphabetical order in ascending order. I can click on character ascending and there you go, you have AC Slater at the top and Zack Morris at the bottom. Now, let's get into the advanced sort menu. So, if you click on the sort button again, the drop-down arrow, you'll notice that you can actually have a custom sort, and we bring this sort up. So, you'll see that's another sub menu, kind of similar to the filter menu, but now you can actually sort things by more than just character, but by font color, by whatever, and you can add multiple sorts. So, let's sort by show, by alphabetical order, and then by rating from lowest to high. So, how would you do that with the advance sort menu? So, the first thing is we want to sort by show. So, I'm going to click on the sort menu. I want to sort by, not character but by show. Here is where we can say I want to sort by values, cell color, font color, cell icon. Very rarely will I use cell color, font color, cell icon, so leave this check as values. We want to sort this in alphabetical order so it's A to Z. So, this is done. Now, don't hit okay yet. Now, hit this plus button down here, because we want to first sort by alphabetical order, and then we want to sort by the rating from lowest to high. So, let's add a level or add a level of sort. So, you'll notice how it says Sort by Show Then by something else. So, now let's sort by rating. I'm going to select rating, and then we want it to sort from smallest to highest rating. So, Excel automatically knew that I want to do small to the largest for some reason. I can obviously change it to largest to smallest. Let's do that anyway. Let's do it from largest to smallest. There we go. So, now we've sorted our list, we've set the sort up so that we first sort by show from alphabetical order A-Z. After Excel does that, then I want you to sort it by rating from largest to smallest. So, we have to hit Okay. You notice that Excel did all the work of first sorting the show by alphabetical order, and then it took the ratings that were from largest to smallest. You notice that it's not exactly in the right order because it was 8, 7, 10, 6, 8. But had these been different numbers like eight, nine, two, whatever, then Excel would have smartly sorted from largest to smallest. So, the important concept to know here is that you can sort on multiple fields just like you can sort filter on multiple fields. So, play around with the filter and sort menu. It's really important to do things like this because if you have a huge list of data with thousands of rows and all you want to do is find what was the top show with the best rating, or what's the last character with the letter Z in their name, this is a quick way you can sort and filter your data to find the results that you want. That wraps up lesson six.
10. Paste Special: Welcome to unit Seven, Paste Special. This unit is specifically focused on the Paste Special menu, and how you can do various paste to do certain operations within Excel. If you've paid attention so far, God bless you. Thank you so much for sticking around for this very last lesson in Excel. This lesson is all about using this really awesome menu or function operation. I just use a bunch of words there called Paste Special. Now, Paste Special allows you really basically to paste some data that you've copied from somewhere in Excel, and paste it in a certain way that matches your preference, whether it's the formatting, the values, the formula, and it's different from a regular copy and paste, or cut and paste. So most of you guys are probably used to using copy and paste just regular command C, command V stuff, but Paste Special is one of the most used Excel operations in terms of modeling, creating a dashboard, creating a report, building charts. So understanding how to use Paste Special will definitely help you with learning how to use Excel for the most common purposes for data analysis. So, the first step is, let's do a good old copy and paste with the data in column H. So in H4, I have number six. So you'll notice that in the formula bar, it's not just the number six, but a formula of F4 plus G4. But double-click this, you'll notice that again it's F4 plus G4. So, how do I- I want to do a good old copy paste of this number? So let's try to copy and paste and move this six over to J4. So most of you know how to do copy and paste which is just command C, command V. I'm going to use the menu bar just to switch things up a little bit. So first select column or cell H4, and then do edit, and then copy. Anytime you've copied something, you know that you've done correctly when you have these little ants marching around the cell. So I want to do a copy and paste and past it in cell J4. Now, I'm going to do edit, paste. Nothing crazy here. You'll notice that it copied number six. Nothing special but let's dig a little deeper behind the scenes on what's going on here. So if I double-click this cell now, you'll see that it's a sum of H4 plus I4. H4 is number six which is a sum of three plus three, and I4 is blank. There's nothing in column I. So, what is six plus blank or zero, number six. You notice that I also copied the yellow font color, and the green fill color, or green background color. So when you do a regular good old copy and paste, it paste everything from the cell, not just the formula but also the font color, the fill color, anything related to the cell. Now, let's use the Paste Special menu to do some really cool things where we only paste the values for now. So, we have in step two, the numbers three three and six, and six again is a sum of three plus three. Here, I want to copy and paste only the number six. I don't want the yellow font color. I don't want the green fill color. I just want the number six. So again let's do a copy. This time I'm going to press command C which is the keyboard shortcut for copy. We have the little ants marching on the cell which we know we've copied something correctly. Now, I'm going to move my cell over to J8. Now, go to edit in the top menu bar, and then Paste Special, and you'll notice this Paste Special menu that has all this cool fun stuff that you can do to paste something. Now, we want to do a paste of values only. So I'm going to select the values radio button here, and then hit okay. Let's take a look of what happened here. So, we had the number six but there's no font color, or no background color. More importantly, there's no formula either. So by double-click, you'll see that it's just number six, there's no like H4 plus I4 which was in this original copy and paste. It's just the value six. So when you hear people say do a Paste Special values, this is what it means. We are just pasting the values no formulas, no font color, no formatting. So in the final step, let's do exactly the opposite which is it only copy the format from the cell, and own the formula. So, in cell J13, I have the number 10. You can see it's hard-coded. It's not a formula. It's just number 10. Now, I want this cell to have the yellow font color, and the green fill color as in cell H13. Now H13 itself is a formula. We've seen this in the previous two steps, F13 plus G13. Now, I want to basically copy this formatting over to this cell. So again, we're going to do a copy. Nothing crazy here. We've done this in the last two steps, edit, copy. We got the little ants marching around the cell, and now I'm going to move over to J13, your edit Paste Special. Here we'll see that we have a paste formats radio button which is right below the values radio buttons or formats. I'm going to hit okay, and there you go. We still have number 10. We have no formula, but we pasted the formatting from H13 to J13. So, that's where the Paste Special format comes into play. You'll be doing Paste Special format a lot in addition to Paste Values because sometimes you have a dashboard, or report that you made that you've formatted correctly in one section, and you want to apply that formatting to across to a whole another section of your worksheet that maybe has a similar table structure or whatever. You don't want to have to go through and click on the font color again, the fill color, number formatting. If you just copy and Paste Special formats you can quickly apply the formatting from something you've done already, so you don't have to redo all your work. Now, paste formulas is of a similar idea, and we're basically going to be pasting the formula and nothing really into the values, the formats, et cetera. So, let's do just that. Let's copy cell H13, command C here. Move over to cell K13, and now I'm going to bring up the Paste Special menu edit Paste Special, and formula is right below all. So, basically when you do a regular good old copy and paste, you're basically doing a paste all. But Paste Special allows you to do paste formulas, comments, column, widths. You can also paste operations like these add, subtract, multiple, divide, transpose, skip blanks. So, we'll talk more about this in class two, when we talk about keyboard shortcuts, but let's focus on just the formulas here. So, I'm going to click on the formulas radio button, hit okay. You'll notice that I didn't copy over any of the formatting which is the yellow font color, and the green fill, but I just have the formula of the previous two cells I13 plus J13. So, you notice how again we talked about relative references in a previous lesson. We didn't have dollar signs next to these columns and rows, and so that means when you copy and paste these formulas around, the cell reference will move around too. So, when we pasted the formula from H13 to K13, the formula cell reference also moved, so it's from I13 to J13. I13 is blank, but J13 contains our hard-coded number 10, and so what's zero plus 10 is 10. So that's why the paste formulas cell here has number 10. Just a recap, again Paste Special is really useful because you can copy specific aspects of your cell whether it's the font, the fill color, the formula, or just the values you can move it over to one section of your worksheet so that you only take the certain things that you've done. Really it's a way to save you time from having to redo formatting or redo formula building because you can copy just those aspects that you need into Excel. So that wraps up lesson seven. Graduations, you finished class one. Thank you for taking this class. I hope you continue your Excel journey on this three-part saga. Class two is next, Keyboard Shortcuts to make you faster in Excel.
Al Chen, Excel Trainer & Coda Evangelist
