Excel Bootcamp - Master Data and Formulas like a pro

1. Skillshare welcome video: Hi there, my name is Keith Greeks. Welcome to this course, so I'm very glad that you joined us. So just to briefly tell you a bit about myself, I used to be a management accountant. I used to do management accounts every month for the board of directors and I did it all using Excel. And I automated everything just to make it really easy for myself. And I've been using Excel pretty much every day for the last 30 years. I've read books about it. I'm always like Googling stuff about it, looking at YouTube videos about to go on courses about it. So I really know my stuff and I'm really passionate about I love Excel and I'm also trying to spread the word and also help other people master this amazing software. So that's why I started getting into doing courses and so on. So I'll briefly tell you about this course. It's about really about data and formulas, which is really at the heart of Excel. So there are two spreadsheets. So the first half of the course is really about data. So you can see e.g. it's got a table at the beginning with lots of data in. Then. Here, one of the lessons is about using conditional formatting to show duplicates, duplicate names, duplicate numbers, and so on. So she soon as you download data in CSV format, comma separated values or share to split that into different columns or merge columns together. So that page. Then also, I'll show you how to do a column which shows the percentage increase or decrease from the previous month or the column to the left. So that's the data one. Then I've also got a and then there's also a formula spreadsheets. So the idea is that you download spreadsheets. And while I'm delivering a lecture, you're also working on the spreadsheet at the same time. So you can actually do what I'm doing, because that's really the best way of learning. It really helps you master, master skill while the, otherwise, you're just watching videos, just goes in one ear out the other. So this spreadsheet show you how to count the number of data in each, in each bit. And then this shows you how to round it, rounds it to the nearest integer. This one shows you how to round it down to the integer below. So in other words, it's 15.7, it would round down to 15 if you use this formula. This compares two lists and finds once excludes the duplicates. Then I get onto v lookup, an HLookup, which are really important things. Here you look up, you type in the word Michael, and then it has Michael's data, which is from there. And then this is a similar sort of thing, but it just is a column the other way round so it's vertical lookup. We solve you type in October, and then this is the October data from that spreadsheet. Also, if it's really incredibly useful, function, if D is greater than C4 and D4. So in other words, this one would just do the biggest number out of those two. Then copying formula, you really have to understand the difference between relative and absolute formulas. Otherwise, you are going to get very unstuck. Maybe you know this already, but if you don't add the whole course, this is the most important one to watch and really use. It shows you how to use the dollar sign to anchor it. So if you've copied too long, the BOP fixed by the dollar sign, and that's an absolute reference compared to relative reference where it will always change it if you cough, that's just a whirlwind tour. Course. I hope you love it. If you want to get more stuff for me or check out my website X sub q.com. I'll be producing more courses. We'll be doing more webinars online live courses as well as recorded ones. And then also there will be ways of working with me one-to-one as well if you want to. So Excel keith.com is the website if you want more for me. Otherwise, I really hope you loved this course. So for now, Bye. 2. Creating a table of data: Okay, So now I'd like to talk about how to add filters in Excel. So I'm accompanied by my assistant, Bobbi. So hopefully you won't get in the way too much. First of all, let's construct a table of data. So to do this, I'm going to use the random number formula, which is equal rand, open bracket, close bracket. And then I'm going to multiply that by 1,000 just to get bigger, nicer numbers and two takeaway decimal places. Now I'm going to just copy that, copy that and just paste it down. So now I've got all these random numbers. Now I'm going to copy again and paste as values. Now, as you see at the top, all of these are valid, so we give the table some titles. So supposing these months. So I type Jan in the first one. And I can just drag that over and it fills other months, which is great. And here we can add other values. So happened to have a list of the most popular babies names in the USA in the seventies. So I'll just paste them down here for our row labels. This is in order of popularity if you're interested. So Michael was the most popular name in the seventies. And it goes down to the top 400. So I will suppose Harry was the unpopular, but let's delete those. Then we can have a total. So on so many There's AutoSum here. Click on that. It will automatically select the range for totaling from the top range of data. Enter. And copy this over. Paste and end up arrow to get to the top down arrow. Another autism there. And then we just copy this down. So copy. Now to get to the bottom of the table. The easiest way is to end dynamic as the bottom of the whole spreadsheet. Then go left arrow, one column, then do end up arrow. And now at the last row. So now I go right arrow again, then up one, and then Shift and up arrow. And then select that whole area up to the top. And then I could just paste. So that automatically adds a total column there. And now we have a grand total. So also some inter grand total of 30009503413. So don't forget, these are not rounded, so these are two multiple decimal places. So if you tried adding up these numbers manually, you might come to a different answer because of rounding problems. But we won't worry about that at the moment. So end up and to the left. Now let's freeze this View. Freeze Panes. So you see I've selected where I want the movable bit of the table to be. Freezing panes will freeze the row above row or rows to the left of the selected cell. Freeze panes. And then you can see if our page down stays at the top. And likewise forgot to write. That stays, which is very useful. 3. How to filter in excel: So now I want to show you how to add filters. Let's add another column here. So select the clicker and a right-click, left-click insert. Suppose we can type just e.g. location name there. So everything's got a column heading. Supposing these first ones lived in New York, maybe these lived in London. Let's say we've got some in Los Angeles, that's enough. And then we could also say these others, we don't know where they live. So I'll show you how to filter by various fields. So click on data. You see here we've got the filter button so we can filter icon. So if I press filter, all of these now get about filters at the top. So now I can filter by location. So if I click on there are the moments you all is selected. But if I take off select all. Now supposing I just want to see the people who live in London. So I click on London, the London ones. And same thing for Angeles. And as you can see, if you look at the row numbers, it goes 1-20. So basically, all it does is it hides the rows. And I can also of course select multiple ones for London and Los Angeles. And I could also just have the blank ones. I don't know the location, which is all these from most 60 onwards. Now if I select all again, maybe I want to stop us filter by name. Now happen to know of course that because this is a list of the top names, there's no duplicates. So no name appears twice on this. So we should just means that if we select like Adrian, Bruce, Daniel will just get three results. Each of those three, which can be nice sometimes if you just want to quickly print off and just show certain items from your table. So let's Clear Filter from name. So that's how to add a filter in Excel, which some people use a lot. I think it depends on the situation and what you need the spreadsheet for. Fonts, remove the filter, just click anywhere on the table and click Filter, which then remove pseudo to create a filter is very easy. You just click that. 4. How to automatically format as a table with filters: Now, if you want to make a table, it's extremely similar except that it formats it in a certain way. So if catacombs he format as a table, I can just press that and then you have all these different options, formatting options. Or I can create a new table size. So let's select one of these for now. Suppose we like this one. It automatically works out that we want a 12401. So let's use this scroll bar to just check. So one is the table that the column headings that is checked. So that means it treats them as headings in the formatting is called the bottom of the last rows for one. So it excludes the total row. And you see it's formatted everything very nicely. And it's also added this filter bar at the top. Sources. Functionality is exactly the same as before, except we have an added filter bar. And if you want to change the formatting, we've got an extra thing up here now which says Table Design. We can do all these things. Just play around with things like that. Banded columns looks, oh, that's very flash. We can do the format of the last column differently to indicate the fact that that is a total. We can have a total row. Awesome. Oh, and here we get the option. We can have average count max Min 0. So we can have the max. So it knows the maximum number in that list is 998 sum, which is the same as what we had before. And then we can just copy that across. And we can delete this. Right-click. Delete. If I click on that again, then we get this table design at the top. Click there. And then we have our different options available to us. And we can also click on New Table Style, which gives us more options. And then if you want to convert this to range, you can just click there, Convert to Range. You want to convert the tables of normal range or Yes. And then that keeps the formatting, but removes the filter functionality at the top. And as you can see, it's added these numbers, 234 at the end. So because it was a table, it liked to have unique column headings. So it doesn't muck about with your data for bits do this formatting. But on the other hand, is a very quick and easy way to do things like add these bands and, and so on. So I think the jury is out on this. Some people like it. I personally don't tend to use it very much automatically format mine. 5. How to find and remove duplicates: Okay, So now I'd like to show you how to find and remove duplicates. So we know that this particular table, I downloaded these names from the internet, so I'm confident there are no duplicates. So although the word duplicate location, there's more than one person in New York and London and so on. But let's just copy this list or some of it to another page to make it easier to see what's going on. Here we have a smaller table. So select that copy. New page. This duplicate, paste. Okay. So now we double-click there to increase the column size. So let's suppose that we have some of these duplicate it. Let's resemble copied these three down to the bottom, paste these three duplicates. We know that. Let's see if Excel can find out this fact. Go to data. And here you see we've actually got this menu item, remove duplicate, so it's very easy. It's in the Data Tools section under data. So just click there, remove duplicates. So now we have a list of columns here and we can select where we're concerned about the duplicates. So we're really thinking of duplicate names. But it gives us the option of looking for duplicates in other fields as well. These are very unlikely to be duplicates because their unrounded numbers and they go to a large number of decimal places. So if I click on Okay. He said She just taken off those 332 because we're fans and removed. So it's as easy as that. Just did it before we do anything so drastic, we might want to identify them first just to check. So let's click on Undo. And instead of doing that, what we will do is to use conditional formatting to visually identify them. So let's just select these Conditional Formatting highlight cell rules. They see there's an item there, duplicate values. So it's just immediately highlighted the ones where there's duplicate values. And then okay. And so we could just Mandy go in and right-click and delete if it was a small spreadsheet or if it was larger one, we could just go back here and click on Data. Remove Duplicates. We know that we're just do those, those ones, but there is a slight danger of this supposing that this is 94. Suppose it all over type here, supposing this is exactly 94. And this is Melissa also is exactly 94. So these two are duplicates. So if I click on Remove Duplicates, first of all, let's do conditional formatting. So let's just select the whole table and click on conditional formatting again, highlight cell rules, typical values that I click on. Okay? Okay, so these obviously the same as fat because I copied it and these are duplicate. So let's see what happens. If I then click on Data. Remove duplicates, select all, okay, that's fine because it's looking at all the columns. So suddenly deleted it where it's duplicates in every single column. So it's not enough to have duplicates in one column. Let's just undo that again. The other way of doing it, supposing we actually wanted to remove ones where those are duplicate value in column B. And we wanted to leave these James, John Robert the same. Well, let's, let's make these numbers different. So let's copy this. Paste them in there. Okay, so in column a, these are no longer duplicates. Let's just remove two cuts by column a, so B can be anywhere on the table. And then just click on Data. Remove Duplicates. Now we unselect all. And now click on Jan, which is column B. Then if I click on Okay, it will, I think what it will do, it will delete the Melissa line and leave the Amy line, which he did is deleted Melissa one to duplicate value found and removed 16 unique values for me. But maybe we didn't want to delete Melissa, so I will undo that. Something for you to play around with, unless you're 100% sure what you're doing. I would use conditional formatting first. Highlight everything just to check. Because this, as you see, it just removes it. And what could be useful though, it'd be nice to at least it tells you how many it removed. So if you're not sure that it's crack that it's working correctly than just press the unto like I did. And then use conditional formatting to find them and then you can do it manually. 6. How to split cells: Okay, so suppose you want to split cells. So I'm going to paste in here an example of cells we might want to split. So this is just a portion of a CSV file that I had. So as you can see, it's got these commas in. Csv stands for comma separated files. So each data point is separated by a comma. So supposing we want to split each Homer into a different column. So first of all, select it. Then we can click on data, text to columns. We have various ways. We can split this into columns. So we've got two options here to limited and fixed width. So first of all, fixed width is obviously not applicable here because the first portion of each data series of varying length, if I try and do the fixed width and try and split them like that, it would be fairly meaningless because other columns would just not be useful. So let's click on D limited instead. And then next. So we already know the answer to this. This should be separated by comma. So we could just take on calmer to tab. There's no tabs there, there's no semi-colons, there's no spaces. There's no other all the words his coma. So we just need to click comma, and as you see, it just splits it really nicely into different columns. And then I can click on Next. And we can also, none of these are date format, but it's data format. So it's giving us an option. If every format these as text or dates. Or we can also not important column if we like, we can skip this escape. So we can choose how we format it. And also if we decide to skip a column. Although there's not much point in skipping because we can easily just delete later. So I click Finish. And there we go. Everything is nicely separated, so we'll double-click there. Perfect. So what I want to do is I'm going to paste it again. Paste. Now is I've just gone through this data, text to columns. It knows already what I'm probably want to do is to split this into columns. So when I paste this CSV into there, it automatically splits it into columns. Now this male may not be what you want to do. And I personally have problems with this because sometimes I just want to import it as rho texts, like actually I want to do now. So in that case, first we have to click on the column again Text to Columns. And then you have to change this to limited, change it to say semi-colons. Okay, Next, Finish. And now if I click on Paste again, paste. It does it as I in fact wanted this time too. Put it all in the first column. That was an example with commas. You can delimit in other ways, as you can see, data, text to columns, you can do it based on something that you can put your own thing in here. Okay, So I'll give you an example of the, if doing it with the other. Supposing that suppose you wanted to do it on space. If I select it all and click on Text to Columns Delimited. I can split either by semicolon or by swallow. I uncheck that now, so it just will split according to space. Then what that's done is It's overwritten this other data up there. So it's just you back. So that's something you have to be careful about. And he did warn you about it. So what I'm gonna do, I'm going to drag it over to here. So we then have that issue. Select column text to columns to limited by space. And then finish is showing how it's going to do it, which is what we want. Finish in nicely. Splits up by space and it also deletes the space. There's no space here now after words Marilyn, which you can see by clicking their space, has gone. The other way you might want to do it is to do it D limited because sometimes it depends what you're trying to import. So let's just do this and show where it looks like even though it's very wrong. So paste. Let's select that. And then to data, text to columns, fixed width. Next. Then you can just put your columns exactly where he like. We'll finish. There. You see, it's done exactly what we told it to, which is not particularly useful, but in this case, but in other cases it can be very useful because maybe you can see here clearly where the columns ought to go in. It sort of depends really where the data comes from, which software has been exported from, and so on. But normally I just use D limited. And one of these or I can type my own in if needed. 7. How to merge two columns: So now I want to show you how to combine cells. So let's just copy these that we just split. Now supposing we wanted these back again. There were various ways you can do this. The way I quite like, which is, once you know it is so simple is to do equals select the first one. Now we also want to add a space. So we do. And then for them we don't want to set the space. So we do vertical columns, inverted commas space, inverted commas. And then we do, and again, then we do left arrow and then enter. Perfect. And then copy and paste. Very easy. We can also use the concatenate command. So with regarding the space identity that I can catenate command works with that. So the way we would do that is to left-click there, right-click, left-click the word Insert and put a space there to put a space, put sigma V comma there. And then Spacebar. Then if I click on that, you can see that there's a space now. There's a space between the flushing vertical line and the inverted comma presenter. You can't see it there, but it is there. So let's copy that down. Copy paste. So you can paste it along way down. Case we add other things there. Snow can use concatenate command. So the function is, it is there in my list and pull it won't be on yours. So there's just type it here. Concatenate, Okay? And then it gives you these options. So let's do, okay. And what we want to do is to concatenate these three texts strings, the middle text string just being a space. So click on John. Takes two, is that one, b to the space B1. Now click on the text three box and click on Smith. Now click on Okay. And it has done it properly. Let's just make this a bit bigger so you can see what's going on. Now, I can just drag that down. You see it does the others. So these both to exactly the same thing. It just preference really which one you use. I got used to using this ampersand one. But that works just as well. 8. How to search in Excel: So now I want to show you how to search in Excel. So this is a list of the top 400 babies names in the USA is. So I just curious to see if my name is on it. Keith, what position is I think it has it did fall out of favor. Let's have a look. So Find and Select, click confined doping Keith. Next, we go. So it was the 80th common name, which is not too bad. I think it's fallen down a lot since then. One thing you should note this checks by workbook. So if I'm on this page, I want to find it. I do the same thing. Find and Select. Keith, Find Next. And it will find it because workbooks selected, but supposing I was here and find and select worksheet is selected, it will not find it because it does not appear on this sheet. You can also find things with a certain format. Or you can just search in rows or you can look in formulas. You can match case. So it will only find it if it's the same case or entire cell contents. So you've got different options. 9. How to replace text in Excel: And replace is something I use a lot actually. So let's copy this over as values. So copy paste values. Okay? So supposing I decided I didn't want a space between these. So what I do is I select them all. I click on Find and Select, click on Replace. And what I'm looking for is a blank. So press space bar, space bar there. So I'm looking for Space bar. For spaces. I'm replacing it with no space bars. So we just take the space out in this selection only. So let's click on Replace All 27 plate because there were spaces underneath which we can't see. But this is not particularly the spaces, but with other things, it is something I do a lot because sometimes you end up with, especially if you're importing data from other places, you end up with things that you might not want. Let's give another example. Supposing we just want to Jennifer March now, we can, obviously, we could just do select that. We could do data, text to columns, fixed width. Next, we can click there and put them into two columns like that. And then we can just right-click. And so this is now in two columns, which you can see. Now I can delete that. And then we get Gen Fab March. That's one way of doing it. So let's just put these back together again. So equals that and space and that one, drag it down. Then copy paste Fannius. So now we want to copy paste values. So the other way of getting rid of the month of is to left-click there, click on Find and Select, replace, replace those. These three in this selection. Replace all. There we go. Jennifer March. Perfect. 10. How to calculate a percentage increase: Hi there. So another question that's often asked is how to do a percentage increase in Excel. So I've created another tab here, percentage increase. If we go back to the original table and just scroll to the top and just select a bit of sample data to use. Let's pick these, This section and copy and paste. So let me stop the months. Suppose you wanted to see the percentage change between the two months. So first of all, we need to calculate the movement, which is just one minus the other, equals that, one minus that. And then you express the movement as a fraction of the which lumbar, so equals d to divide it by the arrow key B2, enter. So point -0.958. And we just format that as a percentage. So it's gone down 96 per cent. So 349-15 is a -96 per cent change. And then of course we just copy that and paste it down. You can see all the changes that one's got up, 1,813%. It's maybe slightly confusing this data series. So let's try more realistic numbers. That case, let's copy this and paste it here. Let's make these hundred. 2090 will make these just a bit closer. So as easy to see what's going on here. So this has gone up from 100, 210. So movement of ten, he just got up ten per cent, which is L2, divided by J two forms as percentage. And of course this is just rounded to the nearest whole percentage. If we wanted more decimal places, we could do that if we wanted. There is another way of doing this. If we didn't want to have that movement column there. So let's copy this and paste it here. Asleep these two. Suppose if we want to do everything in that column. So you could just do it by a formula, so equal open brackets. And then first we calculate the movement which is left to Rho minus leftover twice close brackets. So that is ten. Good, Let's go back in and F2. So we've expressed the movement, there is a formula and then we do divided by. Then we can do, you can't go left arrow here because it gets mucked up inside the formula. So you have to go down and along and then up again to select Q to presenter. So this is r2 minus q2, all in brackets to get ten. And then divide it by Q2, which is 100. So that gives zero rounded to the nearest whole number. But we know of course that is actually 0.1. And when expressed as a percentage, as 10%. So in other words, it's the same number. And these are all the same as that. The advantage of this is we don't need to do that extra column movement which can clutter up the spreadsheet. The disadvantages that this is a slightly more complicated formula. What we have in here, because it's got three things in it rather than two. So and also the advantage of this is that it has got the movement there if you wanted to see it. So I hope that's clear now, how to calculate the percentage is basically any calculation you want. And then you could just format it as a percentage. 11. Introduction to the course - Formulas: Hi there. So I want to show you how to do formulas in Excel. This is an incredible thing to learn how to do. And once you master it, life will never be the same again. And people will think you're a genius because you'll be able to do so many different and amazing things. So there are many, many different types of formulas. Most are pretty easy, some are quite complex. But there are so many that I can't possibly cover them all. Today in this lesson, I'm going to show you a few, but I'll also show you how to use the formula functions to discover new formulas you can use and how to use those as well. So in other words, I'm giving me we start and I'm going to give you enough knowledge. So you'll be able to learn how to use the others on your own. So let's get going. 12. How to compare two lists in Excel: So now I'd like to show you how to compare two lists in Excel. And especially to see if there's any things which are the same in both lists. First of all, I'm gonna get some data from the table, some names. Let's have our two lists. Okay, so what I'm gonna do, I'm going to give them range names. So supposing these are the students who are studying biology, and these are the students who are studying physics. And let's say that these five people are going to be the same in both. We know these four unique names to sort of faith. But what we're gonna do is we take deliberately going to add these to the other column, copy and paste from there. So let's see if Excel is intelligent enough to work out if these are the same in both lists, which obviously we know it is intelligent enough, is whether we are intelligent enough to tell Excel is where we want it to do. Firstly, we give these names, so select those and I'll type in biology here in the range name box. And click there. Now here I select these and I'm going to give this the name. Physics. Range names are extremely useful. So now suppose whenever I'm in the spreadsheets, put some here or somewhere really long way away. Then I want to get to the physics people again, I just click there and type in physics. Fortunately, it doesn't seem to have saved the biology name for some reason. So I'll just do that again. So select them all. I probably didn't present. Hello. G, press Enter. There we are. Now fronds. Get to biology. Biology, or physics. There's good apology and they're already selected. By the way. So then if we add something to the bottom of this, it will not get added to the range name. So biology now excludes that. The way to get around that is to insert a row in the middle of the list and then type it and then it will be included. Because it just looks at the first and the last. Let's compare them to find these again, we use the function count if fx, COUNTIF, go. Okay. So the range. So we can either select it or if you know a keyboard shortcuts, we just type in F three, and this comes up with a list of ranges. So physics, so the range is physics. We want to search, see if Michael is in this Physics list or not. So we do, okay. So selected that range and the criteria, we just select that Michael is the contents of cell two. Does that appear in the range name physics. And it gives a value of zero, which means no, it doesn't. And it's zero. So now the magic is though, if we just copy that cell all the way down, we get ones against these ones where he does back as Matthew does appear there as to these ones. Likewise, if we wanted to do the same thing with these people into this list, we do the same thing. So fx countif is already there, so we can just do okay, if we remember that F three pulls up the ranges for step three. There we go. There we have biology selected, so Scott appear in biology. Well, let's find out. So clicking the second box, click on Scott. No, no, he doesn't because he's a zero there, so okay. And copy that down about again, we know these five to pay in that list. So this one can be useful. Suppose is like 1,000 long way you can do is you can then copy it, you can paste it as values. Then you can. You see now these values, There's while the formulae. You can sort data in reverse order. And then all these five at the top, you know, are in both lists so you can investigate and maybe delete them from one list if you want it to. Or another way to make them stand out is to use conditional formatting. So all you do is select the two lists and click on conditional formatting highlight cell rules, duplicate values. Okay? And you can change the color if you like. You don't like light red on. You can do yellow, whatever. Stick with that one. Now obviously, these numbers are duplicate because you've got lots of zeros and ones, but it highlights the names as well. So that's a quick way of doing it. 13. How to copy formulas in Excel: So I have touched upon copying formula several times in this, but I just want to go over it a bit more explicitly. So let's call is copying. Suppose we've got some numbers here. What I'll do is I will just copy some of these over some of these. Now, this is, normally we need a formula of any kind. You get this where there's nothing, there's no dollar signs there. So if you copy it's along. The B will change to a, C, change to D. So it automatically, when it copies, it automatically changes. Now, supposing we wanted to copy this down. We don't want that to happen because we want the rows to be fixed at two to 12. We want that in every formula, but supposing we copy this down, that one changes to three to 13, We're ready concluding that 5,000 in this one is in B, four to 14. So obviously this is nonsensical. There's no reason why we'd want to copy it down. In other situations, you might want to copy that down. So this is an example of relative referencing and this is absolute. And we're gonna make it absolute by changing this formula. Well, we call it absolute rows by fixing the rows. So changing that 12th, putting $1 sign in front of it and told us animal to which fixes everything. So now when we copy that down, It's the same because each one is fixed at two. And we can do the same things here, so we can make this one relative. So let's do a total here. So let's just copy that cross. So you've got the same problem as before. It's just changing. So that's B to G changes C to H, which then is d two I, then I to j. We don't want that to happen in this case, so we're just going to change the put $1 sign in front of the column and then just dragging it over. There we go. And this is the same now because B, G stay the same because I fixed it. And of course you can change both at once. So supposing you have here a grand total of everything. This is like this. Let's say you want to fix everything. Now another way of fixing it is if you, if you select it like that, so it's in edit mode and you press F4, you see that's changed, that's the survives dollar signs with a B is selected. So every time you press it, it changes it. So if you don't physically liked and then you could do the same with the G. Just press F4 once and it changes to an absolute reference. If you don't like manually inserting dollar signs, you can use the F4 function. Now wherever you copy this long down, whatever is always the same because all these are fixed by absolute reference. So hope that's explained a bit about copying formerly. Because if you don't understand this, you're gonna get really unstuck. 14. How to count cells in Excel: The first of all, I want to show you is how to count cells. So to start off with, let's put some data in here to make it a bit easier. So this is a spreadsheet I had earlier. These are basically random numbers that are generated and there's multiple decimal places. So let's just make it look a bit nicer so we'll get rid of the format them as commons giving me a decimal places so it looks better. So it's just location name. Now, these, if you're interested, these names are actually the top 400 names of babies born in the USA in the seventies. So as you see, the most popular name is Michael, followed by Jennifer. These numbers are completely random. I put next to them just to give some data to work with. Okay, so now to count the cells. So let's see. Suppose you want to count all the cells in this column. First of all, I'm going to freeze panes, which is one of the first things I do on a spreadsheet. Now press Enter down arrow. Now. Okay, so now we can go to this cell and count the number of cells above it with data. So let's do a formula. So the easiest way to form, just click on the FX there, which says insert function. Here it comes up with the Insert Function box. So let's say, let's just type in here what we want to do. So count the number of cells. Count cells. First go here. Excel is so helpful here because it suggests different ones you can do. I think Paul, Let's go to the basic one which is count. And then it counts the number of cells in a range that contains numbers. So in other words, he doesn't count any blank cell so that you can do that with the count blank command. So let's count these, these cells. Festival choice. So press Okay. So we've selected that function. Then Excel very helpfully comes up with this little box we can use. So we can just enter data in here. So it's given a suggested range of C2 to C4, C6. So C2 is the first item with a value in it. So let's just also scroll down the bottom and we can see 46 is just the line above where we've done this cell. So yes, that looks fine. Let's, let's use that range. Click on Okay. And I actually tells you there the number is going to give you because he knows he knows already without your pressing Enter, it's going to tell you 401. And we know that's correct because the last number those forward to and this one is a text data labels, so it doesn't count that one. It starts in row 23, number 4.2. So that's 401 cells that contain data. So we already know it's going to give us the right answer. There we go. And we can also see the formula up there. And you could also of course copy it to the right if you want. When you get the same number in every column for one, then if we did one down here, of course it will be for two because he concludes that one. So let's try that. So fx, now, because we just use count is already selected that these are the most recently used, so it's already got the suggested range. Except here is Scott. Because this notice there's a gap there, is thinking we don't want to use these ones at the top. So it's just doing it in this range here, 4748, which is one, which is that one. But supposing we want the whole thing as before, then we just select it manually by keeping the left button push down, then click on Okay. And it's given us forward to which is correct. So if we type words in here, this does not count. If we were to type a number in here, this would count. You see it's increased everything. So that's how to count cells with values in. We just look at maybe another quick cell count commands. So just type count in there and see what it comes up with. Count a cancer number of cells in a range that are not empty. So this would include text as well. So if we do this one, Let's come up with a value r. Okay? So because there's nothing in the row above that, Excel is assuming that there's something in that cell, even though it's to the left of it. That is why Excel is choosing. That is the one that's suggesting. But we suppose we want the range to be all this up to the top. It's going to include the, keep the text to the top now. And for three. So it is all those forward two plus that one. And let's do another one equals count. Go, and then it gives you these ones. Let's do blank cancer number of empty cells in the specified range of cells. So if we do 12345, so let's do that one. Okay. Is confused about the range now, just doesn't know it's taking those two, which we know is zero because if we've got something in, so let's do this again. The top and I think the answer is five. Yeah, five cells, they're five blank cells in that range. There we go. So say count cells in Excel. 15. How to round numbers in Excel: So the next thing I'd like to show you is how to round numbers in Excel, which would be really useful for this because these are unrounded numbers, even though they look rounded. Let's go to another page called it round. Let's copy this table here. Okay. So as you can see, this is actually 348.86, 654-48-2353. Okay, so what I'm gonna do, I'm going to use the unrounded numbers. So I'm going to create another table over to the right and put the range of numbers. So let's just select everything and copy and paste it over here. And I'm going to insert a row at the top. I'm going to merge these cells. These ones Merge. I'm also going to color-code it just to make it a bit more obvious. So we'll make this one blue. This one, this brownie orangey color. Okay, so keep those there. So what we do here, we just, first of all, let's delete all these. So now let's, let's, let's round these numbers on the left. So all we do is function type round. Okay? Or we can select it there if it is there for you. If you've recently use it. Selected now, so, okay. Now we have to put the number in or formulas, so let's just select it. So just scroll to the cell we want to round, click on it, or you can select it with the arrows and enter. Now how many digits do we want to round it to? First of all, I started with two and then I'll change it to zero. So this will round it to 348.87. We're only showing the, the integers. It's rounded up to 349. But if we increase the decimals here, you can see that it is actually rounded now it's 348.87. So I'll just show what the original number was in case you've forgotten it. It is that we basically taken all these and converted that into 0.87. But normally we would want to round it to property to zero decimal places. So you can either do what we just did. And rounded zero displays is all we can just set it this formula to change that to two zeros got here, rounded number, number of digits, digits. Suppose is zero, Enter. Then we can comma. Now we can just copy this down. And it will copy all the other ones as well. So let's just do that. Copy. It keeps shift press down turbines, right? Okay. Now one thing, they can be quiet flash, which may be not a lot of people that had to do is let's copy this, keep this here, but I'll just copy this over. Another version of this form is all wrong now because they have different columns here. So let's delete everything good. So let's round to the nearest hundred. Once you're familiar with formulae, it can often be quicker. Just type it in. So round. Then open bracket, and then find the cell we want to round this one and then comma. So if we know that zero is zero decimal places, but we want to round to the nearest hundred. What we do is we put minus two there. So it just shifts to more to the left. And then we know that 349 nearest hundred plus 300. So let's prove that 300. Okay, now let's copy this. Down. There we go. Beautifully rounded numbers and there isn't that much nicer to look at then the first table. So yes, it's slightly less accurate, but most of the time won't make any difference to our decision-making because we mentally go through this process of adjusting entry in our brains. So if it's rounded on the spreadsheet is saves us having to process bow for prayer, which means we can focus on the important things to get the bigger picture by simplifying things, by reducing the amount of data and complexity. So I'm all for rounding things within reason. That's, that's around things now as I turn if you noticed, but you can also looking at the round functions here, utopian round. You could also rounds down and round up if you want. There's other things which are a bit related. But an example of where that could be useful. What I'm gonna do, I'm gonna do another page for this. Keep things a bit tired yet. This is copy these numbers. So first, let's round down to 22 decimal places. Okay? So a lot of the time, if you're working with financial data, it will be in terms of whatever currency or in say, e.g. dollars. And then since this is a normal sort of data that we'd be working with. Now, supposing that we wanted to show the number of dollars in this column, what we would round down to have the exact number of dollars, which is 690. So to get the pens, we can just subtract one from the other plus the one with sense rather than pains minus that one Is 50. And we can copy that down. There. We have dollars and cents. And we can only do that because we use the rounds down function. There. 16. How to use hlookup in Excel: I'll just give you an example of HLookup while we're at it. So this will be due the same, it's the same sort of thing. But instead of searching down here for something, what we're gonna do is we're going to search up here. This is particularly useful in this case because we can actually search for a month. So I am an accountant and I used to be a financial controller and every month I'd have to produce these management accounts. And the way I did, it was odd. Create the account that here the bill, the P&L items, turnover, cost of sales, expenses, and balance sheets and everything. So I'll put everyone's trial balance down there. And then by month. And then on this other table, if I want to point out, I just pulled whatever results I wanted for that particular month. So here we've got names instead of accounting items, but it's the same principle. So again, as always, we need to input cell which is a variable. So in this case we're looking at the month. So we can actually say input month, let's make that blue. So let's type in March. I think they're expressed in the expressed in three-letter format. So now we're going to find out what Michael's score is for March using the HLookup. So let's go H node. So same sort of thing. Press lookup value is here. And we are going to use dollar signs to make it absolute because we know that is always going to be the lookup cell C1 table of a f three select data. So now it's asking for the row index number. The first row of values is row one. So let's do that. Range lookup is zero. Okay? So this are the moment this is giving us knows that we want something from this column. Okay? Because we said Mom, what it doesn't know is which row it's on. So we have to let it know to look up whichever row Michael is on. So we need to add an extra level of complexity for this. So what we'll do, let's move this over. So just drag it over. Here. Let's insert a column and we will, can type here row number. So what we need is to put here whatever row we need to return to for Michael, three, for Jennifer, etc. So and we can do that using, well, let's see how we can find that subtyping row returns the row number for reference. I mean, there are probably many ways of doing this, but the way, I think is quite good way of doing it is somewhere else over here on the spreadsheet where it's not in anyone's way, is to have here row. Now you can type in a formula to return the row two. And we can just copy this down, okay? And that just gives you the row number. The only caveat here is that if you did insert another name here, you'd have to remember. Everything is still correct but it doesn't have a row thing there, so this is going to break. So just remember to copy that down if you insert the row. But apart from that, this is a good solution. What we do here, we can just look up this. And the easiest way of doing it actually is to make this, subtract this over. So what you see, when I mouseover to left hand side and I get that cross there. If I right-click there, then I can just drag it over, keeping my right button pushed down. And then I'll let go. And I can left-click shift right and move. There. We've got the row number. I can even hide this and will still be there. So right-click hide. If I didn't want you to interfere with my printouts. We know it's there. So now HLookup. Then the row number is fx is first we want vlookup. Lookup value is Michael, table of a is f three. Data column index number is two because it's the second column. Range lookup is zero to get absolute. And saying that Michael is in row two, which we know is correct. Now if we copy that down, it gives us the road numbers of each of these. Let's just check Angela, it says is in row 20. End up arrow, down arrow 20. Angelo is in my twenties. That is correct. It's looking up that cell in data in a second, the second column, which we have hidden. If you want to unhide it, you can just right-click and unhide. Hi, So I'll probably leave it on hidden. So if you're looking at this later, you can find it a bit more easily. So now we've got the row number. We can do the HLookup here in so here we've got this to this to make it easier, what's going on? Lookup value is C1, which is March. Table array is data, the row index number. So instead of having one there, we will just click there to select cell H3, which gives the row number, which is two. Then that gives you Michael state or for March. And we can copy that down and round it to white around it just showed the first, the nearest integer, 464. So this is saying this, check this as saying Angeles is 900 in March, which is check that Angela 900 and March. Then we just check what Melissa's is in October should be 397. Let's just check that list there. So it's 373 times opt finance seven. So that is correct. So that's pulling the correct data. I mean, the first time you do this, it looks a bit complicated. I agree, but it's all very logical. And if you follow it step-by-step and you do it a few times, it becomes second nature. And if you have lots of different tables everywhere, it can really simplify things and save you a lot of time. So it's definitely worth mastering. So if you don't need it, don't bother about it, but if you do need it, I hope that this has been helpful in explaining it to you. 17. How to use vlookup in Excel: So now I want to show you V lookup. Now, everyone really loves VLookup. I actually quite like HLookup, but this one is a vertical lookup. One's horizontal lookup. So it just depends which way around your spreadsheet is the basically very close related functions. Let's start with VLookup and then I'll show you HLookup. So supposing you've got a spreadsheet like this and you want to look up what Heather's data is in each month to start off with what I'm going to copy these months over. I'm going to type the word Heather. Now this could be an input box. So to make it clear that it's a place where we input data, we can fill it with blue. And we just let the use of the spreadsheet now that this is an input, but I've had what we can do is if we insert a column here, we can just type in because say input name. Here we can get the results using VLookup. So let's type in VLookup. There we go. Okay? So the lookup value is however, as what we looking up the table array. Now, the best way of doing this is to do a range name. So let's just do that as part of the butt. So we're starting actually with, this is our first column, which is the column we're going to look up, which is the name. So keeping shift press down. You go to the end and then page down a few times. Then let go of shift. We've selected the range which we can type in here, data. Okay, so let's put the results in here. For Heather, function, VLookups are selected, okay? Lookup value is Heather. Table array is status. So if we press F3, that comes up with a range names, we just select on data. Column index number is one item, so we know now that it's looking at names. And for the range lookup, nine times out of ten, just put zero there, which means that it's looking for an exact match or those who would take you to the closest match and easy see if we don't have that selects atom, which is wrong, so just always put zero there. That's my advice. And then click on Okay, so by using this complex formula, it's returning the same name we've already got, which is pretty useless, but at least we know that we're on the right row. Now, we want to do is to copy this over and then to use these to get this data in these other columns. Now, we face two problems doing that, but I'll demonstrate. So if we copy that over, it doesn't work. Doesn't work yet. I should say we can get it working, don't worry. First reason if you look at that next cell is looking up the contents of C2, which is nothing. So if C one way around that is we can just move, you can copy Heather across. But that's a bit cumbersome. Control Z plus two. So rather than just filled cells with unnecessary data, the best way of doing that is to turn this from a relative reference to an absolute reference. And we do that by adding $1 sign before the B, which fixes the B. So that means that if we now copy this, so at the moment of caches to C, D, E, etc. But now, because we fixed it and copy across all of these, stay at B. The other problem we have is they're all looking. Remember this is third one. Was the column. If you could forget what these are, you can access it there. The third one is the column index number. They've even bolded it for you. Or you can press F x again, and it comes up with this. The column index number is one. But we want, we're looking at now to add another column so we don't want it to be one. One way around. This is to create a row of numbers here and use variables instead of absolute numbers. So this one, we won't call them one to be returned. We can do, we can increment it so that equals that plus one. And copy this over. So we can just use these, this row. Second column number here. We can use this number here. We can put a variable in here to refer to this. So it means that it will find the credit column. So instead of that one there, just set it here, just take their backspace plus their Enter. So it's taken one. Now, what's the magic happens? When we copy that over? It's pulled out the correct data. So you see why it's done that. So instead of using one again is taken as evidence in T4, T5, which is a two. Second E, 14, etc. Then we can format these as integers. So what we've done, we've just pulled the data from this massive table for Heather. So we can type in any name here. Keith. This will feel lookup does. And it can be extremely useful. 18. Thank you for completing my skillshare course: Congratulations on completing the course. I really hope you enjoyed it and then you've got a lot out of it. And just to let you know that if you want to find out more about what I'm doing, what other courses I'm offering, other ways of working with me. Maybe one-to-one coaching or maybe you won't hire me to do group coaching for your staff or with a group of people. Or maybe you want to talk about consultancy work. Just go to my site, Excel keith.com and you can get in touch there. You can also join my email list to be informed of any free group trainings that I'm doing. So that's all for now. Happy excelling.