Excel Bootcamp - Learn everything you need to know to get proficient & productive in Microsoft Excel | Thomas Fragale | Skillshare

Excel Bootcamp - Learn everything you need to know to get proficient & productive in Microsoft Excel

Thomas Fragale, Microsoft Certified Trainer - 2152801073

Excel Bootcamp - Learn everything you need to know to get proficient & productive in Microsoft Excel

Thomas Fragale, Microsoft Certified Trainer - 2152801073

Play Speed
  • 0.5x
  • 1x (Normal)
  • 1.25x
  • 1.5x
  • 2x
2 Lessons (4h 29m)
    • 1. Skillshare excel bootcamp

      0:44
    • 2. Excel 5 hour bootcamp

      267:57
  • --
  • Beginner level
  • Intermediate level
  • Advanced level
  • All levels
  • Beg/Int level
  • Int/Adv level

Community Generated

The level is determined by a majority opinion of students who have reviewed this class. The teacher's recommendation is shown until at least 5 student responses are collected.

46

Students

--

Projects

About This Class

Microsoft Excel has become the main program to handle large amounts of data, charts, financial models, math formulas, reports, and just about everything else pertaining to business.

But using it can still be a mystery.

This video will show you everything you need to know about using Excel and getting more productive and efficient with it. The topics include:

  • Sorts
  • Filters
  • Advanced Filters
  • Formatted Tables
  • The Total Row in Formatted Tables
  • Using Slicers in Formatted Tables
  • The Sumif, Countif, and Averageif Formulas
  • The Vlookup Formulas
  • Subtotals
  • Charts
  • Pivot Tables
  • Dashboards in Pivot Tables
  • Power Pivot and PowerQuery
  • Automating tasks with Macros

Meet Your Teacher

Teacher Profile Image

Thomas Fragale

Microsoft Certified Trainer - 2152801073

Teacher

Class Ratings

Expectations Met?
  • Exceeded!
    0%
  • Yes
    0%
  • Somewhat
    0%
  • Not really
    0%
Reviews Archive

In October 2018, we updated our review system to improve the way we collect feedback. Below are the reviews written before that update.

Your creative journey starts here.

  • Unlimited access to every class
  • Supportive online creative community
  • Learn offline with Skillshare’s app

Why Join Skillshare?

Take award-winning Skillshare Original Classes

Each class has short lessons, hands-on projects

Your membership supports Skillshare teachers

Learn From Anywhere

Take classes on the go with the Skillshare app. Stream or download to watch on the plane, the subway, or wherever you learn best.

phone

Transcripts

1. Skillshare excel bootcamp: the Excel boot camp. Everything you Need to Know to Be Productive in Microsoft Excel by Tom for Galley, a Microsoft certified trainer in this video, we really want to show you some of the important toppers that you need to go to be productive and Microsoft Excel. I'm gonna show you how to do sorts, filters, advanced filters, formatted tables, using the total row in for mata tables, using slicers in for mata tables. The sum if count if an average of formulas the V look up formula sub totals, charts, pivot tables, created dashboards in the pivot tables, the power pivot in the power query and automating tests with macros. 2. Excel 5 hour bootcamp: Alright, so this is the first thing that you get when you have Excel 2016. Notice over here on the left-hand side, we can go to our recent documents. Here's a blank workbook, and now there's many templates that it comes with as well. And you see some of the templates. Actually when you're on the internet, which I am of course, then you have a lot more templates available to you. Now, I'm going to maximize this window. And I'm going to go into, I have opened other workbooks over here. Now sometimes during this session I'll make my mouse do that so you can find the mass that way as well. And I'll try to make them a little bit bigger for you as well. So in this case, I'll pick on open other workbooks. And we'll see lots of different workbooks for the class today. Now, I'm gonna go ahead and put them. There were eyebrows. And then you have your classic pointed out is called Windows Explorer or it's also called file explorer. From here I'll pick on the word Desktop. And I'll go to a folder that I use for these classes that's called class files. All right, first I want to show you how to link your sheets together. And then we're going to do something that's called consolidation. So this is when you start to have larger spreadsheets, you wanna start linking your sheets together. Let's do that 1 first. I'm gonna go into one that's called consolidate example. Notice how in this workbook I have xi 201420152016. Let me show you two great ways to link your sheets together. So here I am on Sheet 2014. I'm going to highlight these cells on row 11. So I'm going to highlight all these cells. And we're going to right-click on those cells and picking the word copy. And you can tell what cells are about to be copied here in this version of Excel. Now, I'll get a sheet 2015. And you can see how how I had the space for the 2014 totals for this exact example. So I'm going to click on this cell. I'm not gonna do a paste here. I'm gonna do a paste special, and I'll show you why. So I'm gonna right-click on that cell and then picking the word paste special. And now I'm going to click on the word paste special right there. And then you get a win that is called Paste Special. Now I'm gonna come over here, I'll pick on the word paste, Lake. So we did paste special and then paste link. Notice now it has the numbers from sheet 2014. But more importantly, look at the formula up here. So a references xi 2014 right there. Now the exclamation point is going to separate the sheet name from the cell. So this really means that if cell C 11 on Sheet 2014 changes, then this cell is also going to change. The sheets are now linked together because we did a copy from one sheet. And we came over here and we said paste special and then paste link. Now let's see if that really liked together. So you can see how this number says a 105,500. I'm gonna go back to this other sheet. Now. When you have those blinking cells, you want to get rid of those with the escape k. You're going to hit the Escape key to get rid of this. Now if I change this number, it should change the column total, which machine, the other spreadsheet as well. So I'm gonna type in 18 thousand there. And I'll type in 19 thousand bear. And you can see the new total says a 113 thousand. Let's see if the other she changed. And you can see it says a 113 thousand there as well. So one way to link your sheets together is the copy from one sheet. And then you go to the other sheet and the ESA paste special and then paste link. Now let me show you another way to link your sheets together. Among xi 2015 and over here I'll type in 2014 grand total. What we do at this point it is we're going to start the formula on one sheet. And as you're building your formula, you click on cells on the other sheet. So I'll type in the equal sign, right, fair. To start the formula as always. Now as I'm building the formula, I'll click on Sheet 2014. And then I'll pick on the grand total, which is cell j 11. Now, I could do a lot. I could do more math with that formula, of course, but this'll make the point that I'm trying to make. So it says 2014 is the sheet name and the exclamation point and then J 11. And you can see the number here. It says $1,072,050.32. When I hit the Enter key right now, it's going to go back to sheet 2015, which is where I started the formula. See how we're back in this sheet 2015. And there's that same number. And it has the same kind of formula as the sheet name and the exclamation point and the cell reference. So now those sheets are linked together as well. So I just showed you two ways to link your sheets together. You can copy from one sheet and go over to the other sheet and say paste special and then paste link. Or you can start the formula or one sheet with the equal sign. And as you're building your formula, you click on cells on the other sheet. So now that'll work between workbooks as well. So I can copy for one workbook, go to the other file and say pay special and then paste link. And that will make the two workbooks together. I can also start the formula and one workbook. And as I'm building a formula, click on cells on the other workbook. So that'll help you with that, quieted that. Okay, that's how you can make your sheets together. Now, I want to show you how to add the sheets together. If you notice, I have xi 201420152016. And we can see how those are laid out in a similar way. Well, I'm gonna go over to the total sheet. Now. The total She is completely blank right now, but I want to add the sheets together and put them in, put the results into the total sheet. Let me show you a great way to do this. It's called data consolidation. So I'll pick on the cell A3 in the total sheet. And we'll pick on the Data menu up top data and on the Data menu. Now, my icons might be a little bit condensed because of the resolution I use for these, for these webinars. But under the Data menu, you go into this group that is called the data tools. And then we should have an icon that says consolidation. There it is. And yours will actually have the word consolidate their or you're looking for that icon. I'm going to click on that. And it's going to open up a new window. The whole reason for consolidation is to add the sheets together. So at this time, you can pick a different function. Usually when I do this, I would do a sum or a count, or an average or any of the others, you know. But let's pick on the word sum. Now, what you do is you pick on the space where it says reference. And then we're gonna click on the first sheet that we want to add up. I'm gonna click on Sheet 2014. And then we're going to highlight a very specific range. I'm going to highlight from a3 over to IE nine. The reason I start. And so a three, if you notice, the first row contains my column headers, and my first column contains my row headers. That's exactly why are started on. So a three, all the way they sell IE9. So you want to try to make your data like that. Now on this, whether you are picking the word add and now notice how it adds it into the all references area. Now it becomes easier after that. I'm going to pick on Sheet 2015. Those Howell wants to use the same exact range. And in this case that is correct because the two sheets are laid out in a different way. Now if your sheets have a different layout, then you can have a different range, but these have the same layout. Now we're gonna come over here and picking the word Add again. And notice how we have xi 20142015. Now, I'm going to pick on xi 2016 at the bottom. And notice how it wants to use the same range again, which is still correct. I'll pick in the word add. And now we have sheet 201420152016. And the all references area. You can do that as many times as you need. And after each, you're picking the word AD. Now if you notice our data, the labels are on the top row and the labels are on the left column. That's why I've started. And so a three. So over here, I'll pick where it says top row, and I'll pick where it says left column. Now look at this choice over here. They click that. It says create links to your source data. There's that word link again. So that means if either of the other sheets change, then the total sheets will change as well. So that's a nice choice to half. We got to this window, but by picking on the word data and there were consolidated. And then you can see some other mathematical calculations. I pick them the word sum. Then I click where it says references and I highlight my first sheet that I want to add up and I pick another word, add. Then you pick each sheet after that, that you want to add up. And you pick on an ad after each one. And you can have as many as you want it to there. And then over here you see how I usually I'll check those three boxes when that applies. When I click it. Okay, let's see what's going to happen. Remember how the total sheet was blank. Excuse me. Now this is the actual totals for the three different sheets. Now when we see the pound signs, we know what to do about that. We're gonna make those columns bigger. There. Here's a way I can make them all bigger at the same time. I'm going to click on this corner salary therapy. Then I'll move high mass between two of the columns and get the black Cross. Double-clicking the black Cross and eliminate the cause bigger. Now, let me a column be smaller. Now these are really the totals for the three sheets. Now, look over here on the left-hand side, I'll see the plus sign. If I pick on the plus sign, then we'll see the detail. For xi 2014. Two thousand fifty, two thousand sixteen. And that's really where the lake is occurring. And then this is just the sum of those three numbers over there. So I showed you two ways to link your sheets together with formulas that I showed you a way to add your sheets together using data consolidate. Now in this case, I can also hit the minus sign right there that put that away. Now, next thing I like to show you is a former That's called VLookup. If we go to our slides. Now, we'll get to these first couple of size pretty soon. But a lot of times, it's just about every time I teach an Excel class or an Excel webinar, somebody always wants to know about the VLookup and we're gonna use that right here. So I'm going to scroll down a bunch of slides here does on slides ten and Slide 11. So I'm actually going to put that away, the, put this slideshow away right now. And let me show you an example of the VLookup. So I'm gonna go ahead and pick on file. And we'll pick on. I'll pick on file and I'll pick on open. Click on Browse. Now, again, these are my files that I'm using for these classes. So we're gonna go to one that's called customers and orders. Okay. Notice on she wants to have a list of all the customers, including their customer ID and a company name and the contact name. Everything about the customer is on Sheet one. Then I'm gonna go to Xi2. And she too has all the orders as the order ID in the customer ID and the employee ID, the order date, everything about the order is on xi two. Those are xi2. It doesn't show the company name, it just has the Customer ID. So if whether should accompany name on xi2, we'd have to take the Customer ID from sheet to look it up on xi one, find the appropriate one, and then we turn to accompany the back to sheet till. Well, that's exactly what the VLookup does. So I think you'll see the VLookup is a way to get information from a different sheet. And when you have larger amounts of data becomes really important to do that. Sometimes, let's see how we are going to use the VLookup. We're gonna insert among sheet till. We're going to insert a new column, a column C. I just right clicked on column C and I pick on insert, and we have a new column. So I'm going to come over here. I'll type in company name. That's just a column having that. And then here comes the VLookup. I'm gonna show you a couple of variations of the VLookup. And then we'll do the HLookup because sometimes that one comes up as well. All formulas start with the equal sign. So I'll type in the equal sign right there. Now at this time, I could type in the formula, but let me show you a way to find all of your built-in functions. So I'm gonna come over here and pick up the Fx. And then you get this when yes, window, insert function. I can either search for my functions here or I can find it by category. There's financial functions, time, math, and trig. Now I've used the VLookup 70 times that I know which category is going to be n. It's going to be under the Lookup and Reference category. So I'm going to click that. Good. And I'm going to scroll down. And I'll pick on the word VLookup right there. Now it gives you a brief description right there. And if you pick on the word Help on this Function, it'll give you additional help and additional examples. So those health screens are not bad. You can always show that after the webinar, but I want to share how to use it right now. So in this case, I'll pick on VLookup and I'll click OK. Now the VLookup has for function parameters or function arguments as you can say. So these four pieces of information and make it work. Here's exactly how I will use this window. For the lookup array. I'll pick on cell B2. That's the Customer ID. That's what I'm trying to look up or the lookup value. The table array is the one that's usually on a different sheet. It doesn't have to be on a different sheet, but a lot of times it as another word for the table array is the lookup table. Now watch what I'll do. For the table array. I'm going to pick on Sheet one. Now what I would recommend to you is you should highlight the entire column with the table array. It'll just work out better. So I'm going to start at letter a and then Holiday from a all the way over to letter e. So this is a sheet one exclamation point a through E. Now remember when we see the exclamation point, that means that that separates the sheet name from the cells. So that really means column a through column E on Sheet one. Now, the lookup table can be many, many columns across and many, many rows down. And a lot of times it is on a different sheet. It doesn't have to be on a different sheep. And many times it is. Now the most important part about the lookup table or the table array is the first column. That's what has to match. Whatever here we are looking for. A customer ID knows that the first column of the table says Customer IDs. If you're looking for part numbers than the first column of the table will be part numbers. Now it doesn't have to be column a necessarily. It's just work that way this time. It's not always Column a. Is this going to be the first column of the table that you highlight? So here are just happened to be column a, but it's not always. Now the column index number is calculated in the following way. In the table, you count from the left side of the table. So in the table this is column 1234. I want the company name. The company name is the second column of the table 12. So for that reason I'll type the number two. That means when I find the appropriate one based on the customer ID, I want to get the second column of information, which is going to be the company name. Now you see where it says range_lookup. That's you at that one says range_lookup is a logical value. That means there's going to be the word True or False to find the closest match in the first column, then you'll type in true there. To find an exact match, you'll type in false. I'm gonna type in Falstaff. When you use the VLookup, you almost always want to have the word false there, so we could do an exact match in a few minutes. I'll show you one that'll have the word true there. For the lookup value, I have B2. For the table array, I have Sheet one exclamation point, a colon E. For the column index number I have to. And for the range lookup I have false. Let's see what's going to happen. Now if you ever want to type in the VLookup, there's the formula. Those are the parts of the formula or within the parentheses. And the parts of the former are separated by a comma. So you can always type it in or just use the f x like we just did either way, it's perfectly fine. Now it looks like it found a company name based on the customer ID. So I'm gonna make that column bigger. Now let's make sure that they found that while I wouldn't assume that it did, but let's make sure that they found the right one. So you can see how the customer IDs as v, i, e, t, and see the company name. So I'm gonna go to Sheet one. Now these happen to be in alphabetical order, so I'm going to scroll down. There it is right there in her 80 sex VIN e t. And you can see it found appropriate company name right there. So it looks like the VLookup tennis shop. I'm gonna go sheet till now. What I would probably do is copy that all the way down. So this is called a speed fill or an auto fill. I'm gonna go get the black cross right there. A double-click on the black Cross. When we double-click it, it goes all the way down. And now each different customer ID has a different company name. That's like a textbook example of how to use the VLookup. Now, notice how the VLookup says the word false right there. Let me show you what that means. It's looking for an exact match. So for example, if I come over here to cell B2 and I type in Webinar, notice how it says n a, n a means not available or not found. So it tries to find an exact match. It doesn't find an exact match. So it says n. Let me put that back again. I'll say v i n e t, and then it'll find that again. That was because I feel like I've had the word false at the end. Let's do an example with the VLookup where it'll have the word true at the end. So in this case I'll pick on file and an open click on browse. And this time I'll go back to one that's called grade lookup. Now with this one, the lookup table is right on the same sheet. Sometimes that happens. So I'm trying to convert the number score to the letter grade. So my premise here is I want to take that 361 against the table and find the right score. Now this time we don't have to do an exact match. I just to like here, we just have to match the range. Let's see how it's going to work. I'm in cell C2 and I'll type in equals VLookup open parenthesis. The lookup value is going to be that B2, where the 36 was. I'll type in a comma. There is, of course you can type in the formula as well, or you can always go back to the Fx either way that you're comfortable with is perfectly fine. Now the table array this time is going to be column E and column F. So you can see how it says E and F there. Now the column index number this time will be till. So type in R comma and type into. Then I'll type in another comma and I'll type in true. And then close the parenthesis. Let's see what this formula says. Equals VLookup. Open parentheses, B2 comma e colon F comma two comma true, close parenthesis. Let's see what's going to happen. To 36 gave us an F. If that VLookup said the word false at the end, then it would say N a there because 36 is not exactly on this list. But because it says the word true, we're going to match the range. So everything between 039 will give us an F. Now I'm going to copy that down. Again. I'll get the black frosts in the corner of that cell. Double-click on the black Cross and then it's gonna go all the way down to 77 gave us a c because everything between 7079 will give us a see. A lot of times with the VLookup, it'll have the word false at the end, like we did over here, so we can find an exact match. But sometimes you'll see one with the word true at DIA and like this one, which means it doesn't have to do an exact match. It's gonna find the basically it's going to match the range. Okay. So 50 felt between 4070. That's why they got AD. So hopefully that helps you understand the VLookup look better. It's definitely gets used a lot when we're trying to make our largest spreadsheets are compared data. Then there's another one that's called HLookup. V, by the way, means vertical because the table is running down the screen. H means horizontal because we're going to see that the table will be running across the screen. So let's do an array lookup. I'm going to go to a different file. So the file open browse. And I'm gonna go to one that's called basic look-up examples. And now I'm going to come down to the sheet which is called HLookup over here. Okay, see how the table is running across the screen this time, so it's horizontal. So therefore we're using H lookup. In this case, it's trying to calculate the tax rate based on the salary. Here's the salary over there and cell B2. And then if I go to cell B3 has a formula called HLookup, the syntax of the HLookup will be very similar to the syntax of the VLookup. So let's explore that. In this case, I'll go back and click on that cell and typically FX, and it'll give you some help. Okay? The lookup value is B2, which is the salary. The table array is E1 through J3. So that's what I have in light blue over there, except with the HLookup is going to look at the first row of information within the table. And with the VLookup is going to look at the first column. So that's a big difference right there, but the logic is kind of the same. Now here it says column, row index number, it says three. So that means when I find the appropriate one and I'm gonna go three rows down, 123. That's why it's going to have the tax rate that are just a second. That is how the range_lookup. Is a is not filled in. It's actually optional. Here's why it's optional. Over here. If you see the dark black Dennis required, if you see the light black, it means it's optional. So let's see what the description says. Range_lookup is a logical value, which means it's going to be true or false. To find the closest match in the top row, then you put true there or you omit it. So when you omit it like it is now it's going to assume that it's a true which is going to be the closest match. Otherwise you type in foster If you want an exact match. So I'll click, okay. Now the 21 to five 66 is not on this list. If, if that VLookup said the word falls at the end, I would say and a right. Now, however, we left it blank, which assumes that it means true. Which means it's going to match their range. All right. So let's see what's going to happen to 215 66 is between 2651273, L1. Everything between those two numbers, we'll get 28%, which is the number right over here. So I think you can use the HLookup and the VLookup to look up information on a table or on a different sheet like this. Hopefully you got some insight into how to use that. Let's change the salary. I'll go to 65 thousand or 6065 thousand. Now that gave us thirty-six percent because this 65 thousand is between the 585 and the 1.3.1. Everything between those two numbers will give us thirty-six percent. Alright, so hopefully got some ideas about the VLookup and the H lookup. Now this time let's go back to our slides for a second. If I go back to some of those previous slides. Now we're going to start to talk about how to store it and how to filter in these kinda things. Now, here once just talk about the formatted tables and we'll get to those pretty soon. But if I scroll down further on the slides, there's the VLookup. In the HLookup it was Slide 1011. Okay, let's go to slide 12. Now we'll talk about sorting and filtering. Our data will get a normal sore and then it advanced sort. We'll do the normal filter and then filter. These are great ways to manage larger metadata. So let me go back to my Excel spreadsheet. Good. Now I'm going to start close and some of these windows. So I can close this window and close this one. And we're done with us and for right now as well so I can close that. And then this is where we did the consolidation. So I can close that window and we'll save that one. Okay, let's go back into Excel. Now I'm gonna go back to an example that's called conditional formatting. So I just did open other workbooks. And I'll put in the word browse. And I'll pick a desktop folder and the class file folder. Now I'm going to find with it's called Conditional Formatting examples, whether here. So notice how we have this large list of data. So now we're gonna see large list of data throughout this whole webinar today. And when we have a large list of data is going to carry a common theme. So notice how the field names are up top. That's fairly important. We're going to see that all day today where the field names will be at the top of the data. Now if you scroll down, you'll see one contiguous block of data all the way down. There's no blank rows until the bottom. Now this can be thousands and thousands of rows. You just want that to be continuous with no blank rows into the bottom and thereby columns into the right-hand side. So I'm sure we all have data like that. By the way, here's a keyboard shortcut. I'd like to return back to cell A1. So on my keyboard and do control, home control, and the home button will take us back to cell A1. So there's a keyboard shortcut that I use quite a bit. These have to be expenses from your travel with your job. So let's go ahead and do a sort. Now, sorting has become much easier than it used to be, really has in my opinion. Before you had this like all other data. Now I just have to pick one cell. Let's say I were this sort by expense type. Well, just pick on one cell on the expense type column. Now there's numerous ways to actually sort. But as you go back to the Data menu, so I'm going to come up here and I'll pick in the data menu. Now, the a to Z right here were sorted in alphabetical order. And a Z to a, we'll say a reverse alphabetical order. So I'll pick an a to Z. And just like that, all the breakfast items are together and all the dinners are together and so on. So it's really that easy to sort watch again, I'll pick on one cell on their country column. I'll pick on a to Z. And just like that, all the Austrians are together and all the Francis R together and so on. So it's really that easy to sort. Now here's the mistake that people make and this is what you should try to avoid. I'm going to highlight Column E This time the entire column E. What's going to happen this time is column E is going to soar, but the rest of the row isn't gonna come with it, so everything will have the wrong amount. So this is what not to do. Okay, so let's see what's going to happen. I'm going to highlight Column E. Now I'll pick on the a to Z right there. This time it has a message, Microsoft Excel outdated next to your selection. Since you have selected this data, it would not be sorted. So as shunted to tell us something's wrong, nonetheless, I'll pick on. And continue. And now when I click on the word sort, wash column E and watch Column F, I'm gonna pick on sort. Notice how column E shifted by Column F that moved. So now everything has the wrong amount. So that was what not to do. Let's go ahead and undo that and do his way up here in the Quick Access Toolbar. If you're looking for it, I will click on Undo. Now here's the proper way to sort. Don't highlight entire column, just pick one cell in the column that you want to sort. So I'll pick on this cell over here in column E. Now we'll pick on the a to Z. Now you see how the amounts sorted and the expense types came with it. So we saw different result that time. That's the proper way to sort. Now sometimes you want to sort by more than one column. Here's what you can do about that. Let's pick on one cell that's not blank. Now come up here and picking the word sort, right there. There we get this window from which we can add more than one field. So I'll click on the word ADH level. Now you could do 232 fields or three and so on. Through that one more time, we'll go with the four fields. You can really have as many as you want it to now. Then what you do is you come over here and you pick a field for each different row. So for the first row, I'll pick on country. Then I'll pick on expense type. Then I'll pick on D, And then US dollar. And now it's going to take it in a top-down order. So it's going to store it first by country. Then when the country is the same, it'll sort by expense type within country. And then I want to pick on date here and then buy US dollar amount. So it's gonna sort the Top-Down order. You're going to have as many there as you need. What it doesn't like. It wasn't like if you left it blank. There's a blank row. If you see any blank rows, go ahead and click on that row. Now come over here and pick on delete. Okay? So we'll show you how to do a multiple fields sort. Now you come over here to the right and you can use these pull downs. You see each shell can be sorted independently, either a ascending or descending. So you have a lot of flexibility on this window. Now here's a really important choice. I'm going to make sure it is checked where it says my data has headers. Make sure it is checked. Notice over here we do have the field names are the headers. So when you have that, you want to check this little box over there. Because if you don't check the little box, guess what happens? The first show gets sorted in illusion or headers. So it's fairly important. Let's see what's going to happen if I click on OK. Those are all the Austrians are together. There was an Australia or the incidentals are together. Then with incidental, did it by the date as C2 for the 23rd. And for the 23rd, did it by the ML. Looks like it worked pretty well. So if you want to sort by one column, you can pick on one cell in that column and pick on the a to Z or Z to a. If you want to sort by more than one column, you can pick a one cell that's not blank. You can pick on the word sort up here on the Data menu sort. And then from here you can pick as many fields as you wanted to. That should be all you have to know about sorting your information. So it shows you the basic store. And I showed that the more advanced sore. So I'll click on the word cancel at this time. Now something that goes hand in hand with the sort is called the filter. Let's spend some time with a filter. The filter means the following. What if you only want to see the hotel items only? That's a filter. What if you only want to see the ones that were more than $30 in that column. That's a filter. Or maybe we can do it by date range. Now of course you'll use the same kind of data that we used for the sort. Let me show you this simple filter and in pretty soon we'll do what we call the Advanced Filter. I'm going to pick r one, so that's not blank. And I'll pick on the Data menu up top. And I'll pick up the word filter, right? Alright, let's go with filter. Now watch what occurs. Notice how each field there has a pull down that really helps you with the Sort and Filter. I'm going to click on the pull them where it says expense type. Now here's another way they store, by the way. And then you can pick and choose these as many as you want it to. So I'm going to uncheck it where it says Select all. Then I'll pick on breakfast, dinner, and lunch. You can pick as many as you wanted to. Now, in earlier version of Excel, you can only pick one at a time. So this is a little bit better now you can take as many as you want it to. I picked on breakfast, lunch, and dinner. I'm going to click on OK. Notice how the list got a lot smaller because it's only the breakfast, lunch, and dinner items in that it happened all the way down. And that's what I filter is. So you could use these to go to the exact records looking for. Now the nice thing about a filter, everybody is that they're always temporary. I can always get the records back. I'll show her delivery soon. Now, the nice thing about a filter is you can keep on adding criteria as much as you wanted to really to go to the exact records that you're looking for. So I'm going to click on the pull down where it says contrary. Then I can pick and choose does. So. There's another way to sort. By the way, I'm going to uncheck it where it says Select All. And I'll pick on France, Italy, and Spain. And when I click on OK. Now the list got smaller. Because now it has to be, if I scroll down the country, say either France, Italy, or Spain. And they have to be breakfast, lunch, or dinner at the same time. So you can really use this to go the exact records that we're looking for. This is called a filter. Now, before I continue talking about the filter. I want to show you something that's called a custom view. Custom view allows you to save your filter so you can easily go back to it. So let's say this is a search that you do all the time. Well, I want to make a custom view so you can easily go back into that. So a peck on the View menu. Now on the View menu will come over here and a pink underwear custom view. And we're back to here again. Now, I'm gonna go ahead and add a new one that maybe this is the one that I did in a previous session. So I'll plug-in the word add, and I'll call this for an France, Italy. And now you wanna have, you wanna give us a good name because they could have more than one of these. So I'll pick on. Okay. Now it didn't look like it did anything, but let me show you what it did. Let's get all the records from our filter back again. So I'll pick it the Data menu. And I'll pick up the word filter. And that's one way to get everything back. As you can say. It's always temporary. You can always get the records back from that filter. Now. But you know, as soon as you turn that off, that's when your boss comes into your office and says, oh, let me see those Italy, France, and Spain items. Once again, me, why just turn that off right? Or here's a really quick way to go back to that. We'll go back into our custom view. So a pick on the developer menu, I'm sorry, I'll pick on the View menu and pick another word, custom view. Now you can see why we gave it a good name, cuz you're gonna have more than one of these. So I'll pick on France, Italy, and Spain. I'll pick on the word Show. And now it's gonna bring those records back again. So it's a really great way to, it's a really great way to. I go back to a filter. There's a called custom views. If I save the workbook right now, the custom view will be there for the next time as well. Now, here's another way to get all the records back. Again. I'll pick in the Data menu and pick up the word clear. And that gets everything back from the filter, as you could say. But this time it leaves the pull down, so on. So before where pit delivered filter turned it off completely. But now if I pick an nowhere clearer, that gets everything back again. But, you know, the partners are still there, so I could do another filter. All right, so in this case, Let's, let's continue with the filter. I want to show you how to do it with a number of fields. Let's say, I want to go ahead and see all the ones that are over $30. So I pick the the pull down where it says US dollar amount. And I put in the word number filters. This'll work for any kinda number field. So we have equals does not equal greater than, less than. Let's do greater than. And I'll type the number 30. I wanna see everything that's greater than 30. So I'll click okay. And That's exactly what happened. Notice how it's just going to show us the ones that are more than $30 in column E. Watching going to Hell, we did that a bet. That's when you're going to use all the time. So I'm going to click on the pull down for column E. And I'll pick and there were a number of filters. And then you have all these choices. Equals does not equal, greater than, less than between. Now let me show you a couple of new choices. These are pretty recent additions. Top ten means give me the highest ten numbers in the whole list where I can show the ones that are above average or it could show the ones that are below average. So I'll pick on top ten. And then you get this window. Now, I can say the top ten or the bottom ten ago a top. Now it doesn't have to be ten. Let's change that to say 15. Alright, so I want to see the top 15 items. I'm going to click OK. And now went through the entire list and these are the highest 15 numbers watching. And how we got into there, we clicked into pull downwards is US dollar amount. And this'll work for any kind of number field. Then I picked it up where a number of filters. And then you have those choices, including the top ten and above average and below average. So you can see we have a lot of flexibility when we're dealing with our filter with a number of fields. Let's get everything back again. I'll come over here and pick on clear. And now everything is back. As you can see. Let's see how the filter is going to work with gate fields. So I want to do it a date range. Let's click on this, pull them where it says Date and column a. And this will work for any kind of date field. Then are picking the word Date filters. And then look at all the great choices that we have. Next week, next month, next year. You never had this before. That's very helpful. Let me share with us even more beyond that, I'm going to click where it says year today. And then I can do it by month or we can all use year to date, I'm sure. But if I pick on the word all dates in this period, then we could do it by month or by quarter. So I let the fat, you have all these built in date ranges. Now here's how to do your own date ranges. At the top of the column I can say equals AD. That's for one specific day. I can say before or after they were between two dates. So that's how I this specific date ranges. Otherwise, these other databases rebuilt rate m. Let me show you how we got here. We picked an a, pull them wheres is d. And this will work for any kinda Date field. Then I put them away date filter, and then you have all those building date ranges. So everybody you can mix and match as much of this criteria as you need to to get the exact records that we're looking for. Now let me show you how to search the filter. I'm gonna come over here for the polling where it says expense type. And I'll click where it says Search and I'll type the letter t. The it'll only show me the ones with the letter T inside. Breakfast, entertainment, gifts. See how they all have the letter t. Now I'll type the letter E and then it'll show me all the ones that have letters T and E, like entertainment, hotel, and telephone. So that's how we can search the filter to get a more precise search. That one is especially helpful on a large list of data. At this time, I'll pick on cancel. And now everything is back. So that is the simple filter or it's also called the Auto Filter. Now let me show you one that's called an advanced filter. So in this case, I'm gonna turn the filter off completely by picking on the world filter right there. Now everything is back and the filter is completely off. If I pick on the word filter again, it turns it back on. And if we pick on the red filter again, it'll turn it back off. I want to share so that i