Transcripts
1. Introduction and Excel's Benefits: Welcome to Excel for beginners. My Name's Julian, and for the past 10 years I've been working with Excel on a daily basis and teaching others how to use the software. Excel is an incredibly useful piece of software that can help you store in organized data, create systems to keep track of processes. Review performance of business functions, identify insights to inform decision making on more As Excel has so many use cases, it's worthwhile for most people to gain a basic understanding of how to use it, even if you don't work in a job that involves data analysis. So in this course, we're going to cover all you need to know to get started in using Excel. At the end of the course, you'll know how to format spreadsheets, how to use a range of different functions, how to create charts on more. So thanks for enrolling in this class and let's get started
2. Basic Formatting Tips: So in this video, we're just gonna look at some basic formatting tips in Excel. Obviously, most of Excel functionality is all about being able to analyze and visualize data, but it's worth being aware of these basic formatting tips just so you can keep your spreadsheets organized on tied. So first of all, as you can see spreadsheet to made up or many, many cells like this and you've got your columns along the top, which are named off the letters of a B. C. D etcetera, then the rose, which are named after numbers. So let's say we put some text into one of these cells. This is a cell contains text. Now you can start to sort of change the way this looks. So, first of all, you can see that the amount of text that we've got in this cell is too much to fit into this small space. So what you could do is you can expand the columns like this. So how it fits into that cell, you can also expand the rose like that on then. Another point to mention is that when you're changing the width of columns and the height of roses that you can also right click on Select column whipped there, and then you can change it with the actual numbers so we could change this to 40 for example, it will get slightly bigger. Um, and then the other point to mention is that you can highlight multiple cells, and if you double click, it will automatically make them all the correct size for what's in that self. So these have don't need to be any smaller because they didn't have anything in them. But this one did so will automatically fit to that. A zit is another point to mention is that rather than re sizing cells, you can also use the wrap text function. So, say, we've got our text in this box and it looks like this on again. You can see that is overflowing, so there's two options. A. We could sort of resize it, but we can also use wrap text and what that does. It will keep all text in the box like this, so it's kind of the contains text part has moved underneath. Um, something else to be aware of is you can move the location of this around just with these options here so we can move into the middle. Ah, move it to the top of the cell and then we can put it to the center alignment. Align it right on. And then, you know, with this, maybe you'd want in the middle there and then in the middle of assets, it's bang on in the middle. Um, another way. Teoh. Just look at some of the basic formatting tips in Excel. It's just to look, if we were to say, Have something like a menu written out in Excel, where we had our menu information. So let's say we have It's a pizza restaurant and we've got menu there. We put pizza here and in this, so we've got the price of $11 fries gold. Let's say $4 lemonade, $3. So we've got a kind of really basic menu here on you can just using, you know, this sort of dummy information. It's useful to look at how we can make this look more attractive. So, first of all, you can use this option up here to select borders so we can select all borders on. Then we have this black border around it on. You'll notice that menu. It's a line to the left where it kind of makes sense for this to be a kind of title that straddles both of these columns on. To do that, you can highlight both of these cells and select, merge and center on what that has done. Its combines both of these cells and put that text in the middle. Um, other basic things to be aware off. It's just simply things like the colors and the highlighting so we can put the menu in bold . We could maybe give this a little background color of light blues the title, and then these could be changed their color to, you know, other know something like green or whatever else. And then these again, we could put these in the center. We could put these in the center on. You can just see how you can kind of play around with the formatting. Teoh make things look mawr visually attractive and excel. That's just the basic example off some of the formatting, but it's worth being aware of that. Just so you can, you know, make things look more visually appealing in Excel
3. Further Cell Formatting Tips: in this video. We're going to look at a few more tips and best practices when it comes to sell formatting . So we've got a small selection of data here. You've got items listed on here, so shoes, hat, sunglasses, except you're the quantity and their price. Now what you'll notice is is that the price is not listed in any sort of currency. We don't know what this is. So what you can do to change that it's come up in here and then you can actually select currency or the accounting option here. So let's select currency on, and what you'll notice is is that we get the pound symbols, and that's because I've got my Excel set up in that way. But you could change that $2 or indeed any other currency. And to do that, you can just press control one on. Then it brings up this option here. Teoh format cells of a whole range of different options. So let's say we want to change this Teoh U S dollars so we can just scroll down here and we could see we've got the dollar sign their United States or select that okay on then they go . So we've changed that $2. It looks a lot better now. It's a lot clearer what this actually refers to. Another point to mention is that you can increase or decrease the number of decimals are showing. So we're just gonna decrease these. So you come up here and then that's the option for increasing on. Then this is the option of decreasing. So we're gonna decreased by one, decreased by two. And then we take that to no decimal places. That just looks a little Tidier. Um, and then something else to mention with formatting is that you can actually sort of paste the formatting itself. You can paste this information without the formatting. You can paste and keep the formatting on. You can also change the direction off the ways in which these are in excel. So let's just run through those examples. First of all, we could actually paste this formatting onto these cells. Now, obviously, this is quantities. It doesn't make sense for that to be in price, but just for example, purposes. Let's do that now. So you copy these cells and you right click here on, then you. There's this little paintbrush. One. You just select that and that pastes the actual formatting that copies the formatting over onto these cells useful. Want to be aware off? On the other hand, you can also take these cells on. If we would just do control be just a normal paste, it will kind of copy them exactly as they are. But let's say we wanted Teoh pay something on, not retained the formatting. What you can do is you can right click, and you can come down to this option here values. Let's pick that on. Then you can see here how it's reverted back to no currency symbol on. It's just gone into kind of the standard general former, the other one I just want to show you is transposing So let's say we've got these cells here on there will going down at the moment, but you can actually right click here, for example, And then this one here that has the arrows going in a different direction, transposed. Select that, and then you'll notice that direction of these has all changed, so that's useful if you want it. Teoh change the way your data is showing in Excel to get it in a different table format, for example, so those are just a few extra tips to be aware off their arm or formatting options. Obviously, we've got things like the percentage style here through the common style where you have a comma between thousands on. Then when you select control, one that's stay, just don't know Selves again. There's a whole range of different options here, but it's just being aware of some of those key ones that's really useful, such as pace values and pace formatting to.
4. Simple Functions You Need to Know: in this video, we're going to look over a few basic functions in excel that allow you to do things quickly and easily without having Teoh manually right out formulas or filter or look through tables manually in a way that's going to take more time. So let's just get started and look at the data. So we've got a small selection of data here. We've got different types of merchandise hats, T shirts, different colors here. So green, red, etcetera, size small, medium, large, etcetera. There quantities were listed down here, and this is the numbers on the data that we're gonna be interested in having a look at. So we're going to start off on. Just look at a function Teoh. Be ableto add up all of these values without having Teoh manually click on them. Andi, what you can do is you can just type in equal some, and this is thief function. You want to ride up different values. You select that whole range hit, enter on, we get the Toto 1898 So it's not a case of having to click on each one like this one, plus this one, for example, and keep on going, You can just do equal some on it will bring back that whole range another useful function to be aware off his average. So again we can just type out equals average press tab, highlight those cells select, enter. And then we get the average across all of these. 126.5333 That's really just want to be aware of to, um, another want to be aware off is men on this return to smallest number in a set of values, ignoring logical values and text. So we just press tab there, highlight these. Now we can actually see that the numbers are ordered in descending. Former anyway, going from 392 all the way down to 40 on what that means is, when we enter, we just get that bottom value there or 40 which is our lowest value. The men. You can also use Max People's Max, which simply does the complete opposite. So highlight those and presenter on we get 392 which is the largest one now. Obviously here we don't really need those functions because the dates it's very small, But if you've got an enormous table of data on the data is in different order. It is a useful function to be aware off. Um, lastly, another useful function to be aware off his median on again. What this does is it means you don't have to write out the formula manually. Just type. All type equals median on. Then you highlight these cells and it will bring back value that sits in the middle of the So we've got 15 values. Here s 07 there, seven there. And it's brought back seven there and seven there. And it's brought back the value of 88 which is our median number. So those are a few basic functions in excel. Pretty simple, but useful to be aware off, so you don't resort to doing things manually.
5. Cell Referencing Best Practices: in this video. We're gonna look at best practices when it comes to sell referencing. So what sell referencing refers to is simply the way in which you select cells and the different options you have available in Excel. So we've got some example data here. It's a kind of dummy receipt or kind of shopping list on it. Simply got a list of items here, so shoes, hats, sunglasses, etcetera, their quantity in column B on. Then the price of each item in column C No. What would be good is if we had a total price in these cells here, which was the the price multiplied by the quantity. Now there's a few ways we could to get that in here. I mean, the most basic way would be to kind of do the maths in our head and do one times 37 who that in there, two times 14 and then write that in there. Obviously, that's not using any of the functionality of Excel on. Do you know it is. The reason we use Excel is so we don't have to do things manually, so, you know, the next option would be to, for example, right out the formula completely, manually, where we just do it like this equals one. And then we use the time symbol and then we put 37th and then we get 37 there, and then we could do equals two times 14. So we're kind of using this information to write our own formula. But that's not really very good, even because we've actually got the information, obviously, all your it in here on what we can do is we can reference those cells simply by clicking on them. So we put equals, Then we can select the quantity and we put times on. Then we can select this cell. So be to time, see to hit, enter. And we get our aunts like that now because we've put the formula in in this way by referencing the cells. What that means is that we can just drag this down on Excel will automatically do the calculations for all of these eso. If we click on that, we can see that it's reference this and this. If we click on this, it's referenced this in this. So it's doing all of our calculations correctly, which is great now the reason is doing that is because what we have here is something called relative cell referencing in place. And what that means is is that these cell references will move depending on how we drag our , um, original formula around. So if we were to move it all the way down here in this one here we get zero. Because there's there's nothing here and it when it won't work. And if we kept on going, we would get error value hash value. Simple here because this isn't a calculations even possible. So that's one of the ways in which you need to be aware of self referencing and that you can move formulas around on the formula will move with the direction off your this house that is going to, um, But it's also worth being aware off absolute self referencing on to show what absolute cell referencing is. We're going to actually be using this summer sale discount figure or 10%. So let's say we've got all of our prices here. We calculated the total price, but we also want to apply a 10% discount Teoh each of these to work out what the discount is. So what we could do is we could take this here we've got at the moment. We've got B two time See, too. Now, let's say we want to add a discount in this column here to work out what the discount is. So what we can do is take our total price here so equals 37. And then we can just times that, using the gastric symbol by the discount rate off 10% hit, Enter on. We get $3. 70 now if we drag that down, what you'll notice is is that it doesn't work in the same ways before it's no actually giving us two correct answers. As we know, 10% or $28 isn't 0.0 Neighbor is for $21.0.0 on. The reason that that is is because we need Teoh implement something called Absolute cell referencing. Because this discount rate is only in one cell, it's no in all of these cells here now to influence absolute cell referencing. You can just open up the formula on press F four on. You'll see that these dollar signs come in place. You can also manually entered dollar signs. But you can just press therefore to bring them up on what this does is it locks each of these items in place. So it's locking F in places, locking column, meth in place and it's also locking column eso row two in place suit. No matter how you move, your formula around this cell will not move at the moment. You could also just have the row locked eso we just got to lock. But then f would still move around on. Then you could also just have the column locked, but then two would still move around. In this case, it will work fine if we have both of these locked. So we've got both of them. Lot now will drag this down. And as you can see, they've all worked correctly. So $2. 10 is temps and $21 etcetera on The reason that that is is because, as you can see, even as we go down, this is staying in the same place that whilst these are moving, that's move there. This stays in the same place on we could have absolutely sell referencing where we only lock the road like that hit, Enter drag it down and it will still work. But in this particular case, we can't have absolute cell referencing where we've only locked the column. It won't work. And that's because we're moving the formula down, which means the road will be moving and we need to go to stay in the same place. Even if the column isn't moving so again, that hasn't worked. So which gonna revert back? We can have everything locked, hit, enter and then go down there. And then there you go. So we've got all of our discounts correctly applied. So now let's just quickly work out the total price once we remove that discount rate, so price after discount, um Andan Excel, obviously with the multiplication. Now, obviously, you can also do you addition subtraction and division. Um, addition is just with plus symbol subtraction of the minus symbol and then division with the Ford Slash. So we're gonna use attraction here, so we're gonna take our total price on Then we Can you subtract our discount on again relative sour since you sell, referencing will work here because these were all filled out on these well filled out, so it works just fine. We've got our price after discount, and then we got all of them down here. And then what we can do is we can just use the some function so equals some, and then we can highlight all of these hit enter, and then we get our total price, their 198.90. So that's a brief introduction. So referencing as you can see, it's important to understand so that you use formulas in a way that they're not going to break on, that you can do things in an efficient and quick way in excel.
6. Why You Should Use Conditional Formatting: So in this video we're going to have a look at the conditional formatting feature in Excel . Now, conditional formatting is really useful. A because it can make your data in excel Look sort of mawr visually interesting. It console adult life to the information. So it isn't just a simple case of, um, you know, information in cells that can look quiet, intimidating, almost or boring. It makes it more easy to understand on the other thing is, it allows you to sort of visually identify patterns and trends, which you might know of a otherwise been able to see. So we've got some data here. This is actually just the IMDB top 100 films with their rating in this column on their run time in this column here. Now, at the moment, it sort of looks fairly plain. There's not a lot of sort of interesting aspects to this data, and that's where conditional formatting will be used to. I'm gonna run for a few different examples off how conditional formatting will be used on. We're going to start off with this I am de Rating column. So let's highlight those so Selves. So control shift down to select all of those cells. And then if we come up to you, got to be in the home tab up here, and then you go along here to conditional formatting. Now what you'll see is that there's various different types off options here, so we've got highlights, cells rules. And then we can highlight cells that are greater than a certain value or less than a certain value, or between a certain fairly equal to etcetera, top and bottom rules. So the top 10 items the top 10% etcetera, etcetera above average, below average data bars will come onto what those are color scales and then icon sets. And then you can also manually write some rules out, which gets quite granular. And you could be really quite specific as to how you want to do things. We're going to run for a few simple examples that so let's highlight those cells there. So we've just done control shift down to select all those cells. Let's go to conditional formatting. Then let's select highlight cell rules. Now let's select the greater than feature. So let's say we wanted Teoh look at values which were above 8.6, so we can write 8.6 here and then, as you can see, it's already highlighting just the values which are above 8.6 on. We can also change the way it looks so we could have yellow, for example. Instead green filled with dark green text. Just read text, um, or red border. There's there's many different options, and you can also select custom form as well. And then you can enter exactly how you want things to appear here. If you want to sort of do it in the exact style you like. Let's just stick with light red Fill with Dark Red Texas. Now select. Okay? And then you can see the all of ourselves, which are above that 8.5 value, 8.6 value. Sorry, have taken on the sort of dark red tax with that sort of pink background there. So that's one example. Now I'm just gonna press control Z to remove that conditional formatting. Let's have a look at another example of conditional formatting. So now I am going to have a look at the data bars option now, and you can see there's a few different styles and you'll see there all this time to appear on the screen. Now. In this particular case, I think this is a good example of where data bars is not the best type of conditional formatting to use it, because what you'll notice is the numbers in this column are all quite similar because it's the top 100 films we've got sort of 8.38 point four way provide, except you're going up to 9.3, so there's not a not a lot of a range between those numbers. So with these, when you select that, you can see that the way the data by works is more filled up, depending on the size of the value, and then as we go down, the data bar gets quite small. But because these values are quite similar, there's not a lot of difference in the formatting there. So this is just a good example of how we've conditional formatting. You know, you still need to be careful with how you use it on which which type of conditional formatting used. Sometimes it won't look as good as it could otherwise do, so we just do controls a on. What we're gonna do is we're gonna highlight those cells again. Carts conditional formatting on. We're going to go with color scales on. We're going to see how this looks. So let's select this one here. Green, yellow, red color scale. As you can see, there's that. There's a range of different options, and they all look slightly different if we go that one there and you'll notice that this is kind of more visually useful. We can clearly see that the different scores a kind of grouped up on a different shade off a different color. And those colors are quite distinct from each other. It's quite easy to see. So that's an example off, you know, slightly more appropriate conditional formatting there. So we can also have a look at the run time, which is the one time of the film in minutes. So let's just select those cells there come up to conditional formatting on. We could also have a look at, say, icon sets on on, you know, let's say we wanted to look at these directional I concept here four hours college. So let's select that one seal actors and again you can see here that the longer films have taken on this kind of green upwards. Ara, the kind of medium and so short medium films have taken on these directional arrows, and then the the ones with the lowest runtime have got that red downwards arrow. So it's another example of the type of formatting that's available in Excel You to kind of make your data look more visually appealing. Let's just do controls either. And if we just do one more look at this, we go to conditional formatting. This time, let's have a look at top slash bottom rules. So, as you can see currently, because the data is currently ordered from top to bottom with the score baiting, the runtime numbers are kind of all over the place. But let's say we look at the top 10%. So let's select that. And then again, we'll just leave the default color there, although we could change that and you can also change the present it to we could do top 10 or, you know, we could do top 20. Let's do top 20% on, then select okay, and then what we can see is that sells with the runtime that are in the top 20%. So these other those other longer films, like 202 minutes or 207 minutes, 179 minutes sexual. They've all been highlighted, this pink shade of the red color, and that's quite interesting. It allows us to see kind of you know which films longer, and as it happens, we can see that some of the films more towards the top of the list are also some of the long ones. So that's a quick run for of conditional formatting. There is, as you can see, a lot of different options, and you can also start to get really quite specific if you do your own custom rules, where you can do things that, for my own cells that only contain it could be, you know, really precise value. And there's all sorts of options that you could do to get rid of the conditional formatting . You can come down to clear rules, and then you can do it from the selected Selves or from the entire sheet. Let's just do that. And then we've removed all of our conditional formatting nice and easily so conditional formatting It's a really useful feature in Excel to be aware off on something that can make your data look a lot more interesting and insightful.
7. How to Remove Duplicates: in this video, we're going to look at the remove duplicates feature in Excel so removed you Cricket's not a formula that you Taipower. It's simply a feature that's built directly into Excel on. It's a really useful one to be aware of. So we've got some example data here. It's a list of clothing, so hats and T shirts or in different colors had all in different sizes on. Do you know when you're removing duplicates? There could be many reasons why you got duplicates in your data. Maybe it's through mistakes or it's just a feature of the data set. But you just want to see how many sort of unique items there are. That's not really important that the important thing is to understand just how to use it, in case you ever do Nitties in future. So how user is you click into the data range where your data is. So here I'm gonna click on this sailor. It could be this so it doesn't matter on. Then you go up to the top here, and then you click the data tab and then you'll see the options will change here and then select the remove duplicates button up here. So as you say, it's see, it says delete. Duplicates rose from a sheet. You can pick which columns should be checked for duplicate information, so let's select that on. Then you'll notice it has highlighted all of the relevant data on the headings, so clothing, color and size have gone to these columns. Here is identified that these are heading so clothing color on size now by default, it leaves all three of these selected. Now. If we wanted to remove duplicates based on all three of these, we could leave it like that. But I'm just going to start off just by looking at the clothing column and seeing what that does. So basically, whichever ones you select me can select more than one, or you can just select one. Those are the values which it will look at to remove duplicates. So if we just select clothing, it's only going to remove duplicates based on that clothing column. So we've selected clothing on Let's select. Okay. Oh, okay. So 14 duplicates values found removed. Two unique values remain. Okay, So what you can see there is that obviously we had providers do control Z. Now we've got there's only two values here on the way. Remove duplicates. Works is it takes away the bottom values. So it will leave this road here on this road here. So T shirt red, medium and hat green small and we just redo that control. Why, you can see There we go hat green, small T shirt, red, medium So it has removed all of those rose beneath those two roads so that we've only got one row with hat in and one move T shirt while the name duplicates. Let's just get back now will select remove duplicates again. Except this time we're going to select just the clothing and the color one as well. So we've selected to, but we're not selecting or three now, As I'm aware of this data set, I'll let you know kind of what's gonna happen. So it's going to look for all of these bows on. It's gonna identify instances where both the the Cell in Column A and the selling column B have duplicates. So what that's going to see is going to see we've got T shirt orange here. We've also got T shirt orange there on we've got T shirt, orange there and also there. These are the only instances where there's duplicates in both. Row column A on column B So what you'll notice is when we select Okay, way go down. It says three duplicate values found and removed. And that's because there was four bows that had t shirt orange in on its identified that three of those were duplicates. And it's left us with just one T shirt orange in that road there. So that's how it works. When you select two out of the three column headings in this example, let's go back again. Now let's say we want to remove duplicates based on a lie three columns So it's gonna make sure that every row, each cell in each column is the same. Before it was to remove the Jupiter. And again, as I know this data set, I can tell you that there's one inch. There's only one instance where you've got duplicate value and it's T shirt orange medium. There's one instance of that here, and there's another instance of that here. T shirt, orange, medium. So we've currently got 16 rows. What we're expecting is this bottom one to be removed, and then we will just have 15 rows of data. So let's do that now. So remove duplicates. Now we're gonna leave it, All of them they're selected on. Then we're going to select. Okay, Way Dio one duplicate values found the removed 15 unique values remain. There we go so that I'll just do controls it. So that is the remove duplicates feature and Excel advocacy It's really useful on. It's also quite flexible in that you can, you know, select different columns on, specify exactly which columns you want the removed Supercuts feature to work on.
8. Sum data based on a criteria (SUMIFS): in this video, we're going to look at the summit's function. Some dysfunction is really useful, and what it allows you to do is somewhat values. Add them up based on different criteria that you specify. So it's different of some formula and that rather than just adding up everything, you could be quite specific about what you do and don't want to get added up. So let's dive in and a good example. So we've got a table of data here. We've got merchandising column, A river. It's a hat or a T shirt color and column B the size and column C and the quantity of each item in column. D. So just taking the top row, we can see for green hats that are inside small. We've got 392 of those. Let's say we wanted Teoh do a calculation to figure out how many green hats in total that we had. So to do that we can use a some obstruction. So what you do is type people, some ifs and you just press tab now some range. This is, quite simply, the column or the row of data that has the numerical values in that need to be added up. So for that, in this case here, it's all of these cells here. So we just highlight those cells and you can see it says D to two d 16. Next is the criteria range one. And for this we're going to specify these cells here in column A. So that's where it's a hat or a T shirt on. Then you have to specify the first criteria. And as we're looking to add up green hats, we're going to specify hats because that is the criteria which will be found in this column column. A next up is quite eerie. Arranged to now we're interested in the color green. So we're going Teoh, select the cells where along the colors are listed, which is from B two to B 16. Now we have to specify the next criteria. We're gonna type out green for that Now. You could click on cells that have these values hat in green, written out if you have them elsewhere in a spreadsheet. But because we don't, we just We just got the table of data here. We just typing them in and manually with the quotation marks. Now it's a simple case of yours hitting enter and then we get the answer off 680 now. This was obviously a very sort of basic example, and we can see that what that value there is done is it's just looked across and found these two rows here on because this these rows both match the criteria of hats and green. And then it's looked to that some range and added up. These two on, we can see if we were to just do a basic some function on Just select those two cells. We would get 680. So that's what some its function has done here. Some ifs. Although you can have, say, 234 acceptable criteria. You can also use it in the same way that you would a sum if formula where you just have one criteria on din. That case what we would do, we can just remove all of this, the criteria arranged to and the criteria to. So I'll just delete that press enter, and then we get 14 to 8, and what that has done is summed up a lot of hats. So we select those cells here. Just the hats press Enter 14 to 8. We can see how that's worked there. So that's the summit Dysfunction really used for one to know Onda Great. Want to be aware off?
9. Find a value in a table (VLOOKUP): in this video, we're going to look at the V look up function. The look up is really useful function on what it does is it will look for a table of data. It would look in the left most column off the table based on a value that you specify, and then it will find that value on. Then it will look for a value to the right of that in one of the columns in a in a column that you specify to the right on. Then it would turn what that value is so t run through this at least be easier just to sort of get started, dive into an example. So let's say we want to find out the run time off the line King film. So if we start off just by talking out equals V, look up tab and then we specify I look up value. Now we've got the Lion King typed into this cell. You could equally type it out like this with quotation marks, but we're just going to select that cell and then next up, you need to specify the table array, so we just highlight the cells that have our data in control shift down to select all of those. Um, now, at this point, it's good practice, depressed at four. Just to lock these cells so you can see these have all locked in place. And this is helpful is if you move this formula around, it will keep your table in the same space next up. We need to specify the column index number. So what that is, it's the column where the value that we're interested in is located based on the location off the first column. So the first column is always column one on. Then, from there we count. So we have to go 123 to get the runtime column. So in this, in this part of function, we put three and then next up is the range. Look up on If you're looking for an approximate match, which most of the time you're not going to be doing, you would select true. But as we're looking for an exact match, we want the line King to be matched up exactly with the Lion King we're gonna look with Go go to the folks part of this function and select Tab on, then closed up off inventor on. Then we get a run time off 88. So what has that function done? Well, it looked down in the left. Most column here it's gone all the way down until it's found The Lion King, which happens to be on Road 50 on. Then it's looked across. Teoh COLUMN three SO COLUMN one column to column three. It's found that value of 88 just there on. Then it's returned that into that cell there. So that is simply how the V look up function works, as you can see really useful way to quickly retrieve information, especially when you've got a big table of data on a great function to be aware off.
10. Combine Text Strings (CONCAT): in this tutorial going to look at the contact function, the concrete function, combined cells that contain different texts. See, you conform different textures. So we've got a small table here. There's a list of clothing down here. It's just got hat and T shirt in it. And then there's the color column, which has got green, yellow, red, etcetera, size river. It's small, medium or large, for example, On then all of this information is listed in these columns in the form of codes. So hate for hats G for green has for small etcetera. So what the contact function would allow us to do is, for example, we could take this information so green hat in small on, we could actually combine that into one cell so that it's in in sort of sentence format. So what you can do is revoking that the contact function now, which gonna select green and then a comma on. Because we're putting this in a sentence. We need to make sure we add spaces, and to do that, you just open up quotation mark put a space, another quotation marks, close it off, and then a comma now got green space Now we're going to select hat and then we do another comma. Then we don't have a quotation mark now, because we haven't got in written down anywhere, we're gonna have to type that in. So we're gonna put in like that Another space comma. It's now we've got green space hat in on. Then we distract cell c Too small. So we've got green hat in smoke and then we hit. Enter there. We got green hat in small. We can drag this down. It will populate all of these cells. Use this format of the color the clothing item and ends in size. And then it will take all of this information and you can see how that could be really useful if you wanted to just change the order of these or, you know, put it in a format like this that you could then use elsewhere, like on your website, for example. So let's remove those thea other use off the con cat function on This is how it differs. Teoh The Katyn eight function comm Katyn. It functions actually legacy function in excel. What can cat allows you to do is allows you to select a Siris of cells, like a range of cells just like that on. Then it will actually join them all up. So we've selected those cells there. Hit, enter. We get hate GS so we can actually take these individual parts of the clock po'd clothing code the color code size code and combine them up all into one and drag that down on. We've got this kind of combined code all in one cell on it. It describes exactly what the product is. So it's a T shirt that's orange that's in large, or it's a hat that's in yellow. That's in media. So that's two examples of the Concord function. It's a really useful one to understand, aunt. Hopefully, those examples have illustrated that
11. Creating Charts: in this video, we're going to look at how you can make charts in Excel. So charts are one of the features that you're probably gonna use part often in Excel on. There's something that you kind of really need Teoh understand in order to kind of get the most out of excel. So we've got some example data here. It's simply just a list of offices, offices, a through to ETH, with their 17 4018 and 2000 and 19 sales listed here on this data here. At the moment, it's not really easy to kind of spot clear trends or, you know, see what this data really means in this former. And that's where charts current handy. You can visualize data, so make sense and you can see what's going on. So if we wanted to turn this data into a chart, we can just select any cell within this sort of range on. Then, if we come up Teoh when you go into the insert tab, so you probably on the home tab by default. But make sure you're on the insert tab on. Then you see here we've got this chart section here, so you can select often individual chart that you want. If you know what type you're interested in, whether that's a line or area chart or a column or bar chart, for example, then there's a few other types, such as pie charts as well. But what is a good thing to do is just to select recommended charts, which this big option here. So you select that, and then you actually get a kind of preview visualization of what your child will look like based on the different four months that are being recommended on. As you can see, there's quite a few options that are being recommended here, and then we could also go Teoh this all charts option on then you can kind of preview even more options, looking up all sorts of different types of that's hissed a gram or math or pie or column or line etcetera. Now, based on the type of data that we've got here, we're going to go with this clustered column chart, which is kind of this fairly basic, simple chart style here, Um, and as you can see, it kind of lists out details. What is useful here So a clustered column chart is used to compare values across a few categories. Use it when the order of categories is not important. So this is usual for our data. So we're gonna select that and then select okay, and then instantly our chart has appeared on. We've kind of got something here that without doing anything more than effectively clicking two options, we've got a useful chart here that shows the data in quite an easy to understand way So we can see we've got our different officers listed along the X access the horizontal axis on Ben. We can see that the different years where it's 17 4018 or to father in 19 all in different colors. And we've got this down here, this legend that shows us which color relates to which year, and you can easily start to spot things like the fact that officer has gone up each year office be went up and then down office. He has also seen a rise office. De is being declining so on so forth, so instantly we taken our data and we turned it into something that looks a lot more visually appealing and easy to understand on that is how you build a chart in exile. It's really quite simple, but it's worth just being aware of some of the other options that are available. So for a start, you'll see that we go to this chart design on option at the top here, which will pop up on there's also there's currently, like, a few options we can select in this the folk designs that they've got listed here so we can even scroll down, and you can see how the chart is changing based on all these different preset designs. Now some of them look better than others on. It might be that there are certain features of some of designs that you like in certain features of the other ones you don't like. One of the things that is clear here is that with these data values on top like this, they're too big and there's not enough space. So this isn't something that really looks very effective and is not something that we would want. Um, if you wanted to remove feature like that, you can simply select them on then select elite. So click on that delete click on that elite, and it's removing those values for each off the years. Anyway, let's scroll up and let's look at our kind of more default one. Let's just go back to that which is quite nice and simple and clean. Um, what's also worth mentioning is that you can also just change the colors with these preset options here. The other option would be Teoh kind of manually change each bar. You can right click there, and you can change the different options here. Um, but you know, these preset one's pretty good, and it allows you Tiu have quite a good range, and the colors are kind of in a way that shoot each other. So we're gonna just again leave it with the default one for now. But it's worth being aware that there's a whole range of options up there, and then with the information here, you can just click into this chart title, and we can just rename this very easily so we could call this office sales on then. Another point to mention is that there's these two options here. Add a trial element on quickly out, so quick layer is similar to this here and the conclude of change of design. So it allows you to add things like a data table at the bottom. So we've actually got all of our information, the actual data values listed out numerically below There, on you can just highlight these different options and preview what they're going to look like. Um, so there, that one There, for example, has the legend at the top. Um, I think you know how we had. It was pretty good. So we're not gonna do any of those ones right now. Another point to mention, though, is the actual element section. Now, here you convey quite specific and specify exactly what you want. So, for example, the chart title we can have this centered a ballet. So it's kind of on the graph chart itself on the data labels. You can have these popping up in different places the date's table. So we saw that previously you could add one of those the legends. You can move the position of it so right talk or left, for example. You can really make things look exactly how you want them to. One additionally, will do just as an example is will add a vertical axis. So we just got access titles and then we're going to select Primary Vertical on. Then you'll see that this boxes appeared here. Access title. We'll just click into that on what we can do is just rename that sales. So we've got the information as to what this data is in, not Axis title. There on that is a sort off basic introduction to charts in Excel. Really, As you can see, there's lots of different options, but just getting a basic chart is something that's really quite quick and simple to do.
12. Next Steps: so that concludes the Excel for beginners class. Thanks for watching. And I hope you found it useful. Let me know if you have any questions and you can find the class project instructions below this video to test what you've learned. Did you want to learn more Excel? Check out my other videos and click follow below. So you're the first to hear about my newest classes. That's all for now. See you next time.
Excel Classes, Excel teacher
