Excel Shortcuts, tips and tricks - Great tips for becoming more productive in Microsoft Excel | Thomas Fragale | Skillshare

Excel Shortcuts, tips and tricks - Great tips for becoming more productive in Microsoft Excel

Thomas Fragale, Microsoft Certified Trainer

Play Speed
  • 0.5x
  • 1x (Normal)
  • 1.25x
  • 1.5x
  • 2x
2 Lessons (1h 7m)
    • 1. Video site excel shortcuts

      0:51
    • 2. Excel shortcuts

      66:30

About This Class

Topics include:

  • Detailed assessment of the most productive Excel formulas and functions
  • Pre-formatting Date Columns for Easy Data Entry
  • Creating Custom Number Formatting
  • Using Conditional Formatting for Real-Time Feedback
  • Color-Coding Automatically
  • Recording Page Layout Macros for automating page setup
  • Nesting an Error-Handling Function
  • Automatic Date-and-Time Deadline Calculation
  • Creating Range-Based Validation for Cells
  • Using Relative References to Automate Analysis
  • Managing Sloppy Data with Text Functions
  • Customize the Ribbon
  • Customize the Quick Access Toolbar
  • Creating Custom Keyboard Shortcuts

Transcripts

1. Video site excel shortcuts: Excel shortcuts. Great tips that help improve productivity in Microsoft. Excel by Tom for Galley, a Microsoft started high trainer. In this video, we'll show you how to find all your functions and formalist. I'll show you had a pre format your date columns for easy data entry. We'll talk about creating custom number format. We'll talk about how Teoh use conditional formatting to call occurred your spreadsheets. Talk about recording a page seven macro toe automated pay set up nesting. An error handling function. Automatic dating time deadline calculation Created range based validation for cells using relative references to automate analysis. Managing sloppy day with text functions. Customize in the ribbon, customizing the quick access to a bar and created custom keyboard shortcuts with macros. 2. Excel shortcuts: I'm gonna show some great formatting tips as faras add dates and number formatting initiated some great new ways to do conditional formatting. The color curd yourselves. We'll talk about ah, page layout, macro another in other words, so you don't have to do a page day up every single time. We can automate that with him. Macro, I could show you have it. Make a function. Be an error handling function when we use the if our function will be able to do that for you. Were you talking about date and time deadline calculations as faras doing calculations with dates works a little bit different than doing calculations with numbers. So we'll talk about that and we'll talk about how to protect yourselves and how to dio validation on the cells. They're gonna type in the proper type of data. We'll talk about using relative references to automate data entry. And did analysis show us and the ways to manage sloppy data. The text Teoh Columns feature will help you with that talk about how to customize the river with your own favorite functions. Your your icons had a customized a quick access Tobar and also creating custom keyboard shortcuts. So let's get started now. I'll be using the newest version of Excel today when you have office 3 65 like Ideo. Then they always given the newest version of Excel. So I'm gonna go back and let's go over to the Excel spreadsheet. Okay, so I'll be using the same spreadsheet throughout the entire session today. Let's talk about for many for dates, so knows column A. This looks kind of weird because even though it's his date up there, they're all numbers. What happens is excel really storage your dates as numbers, so let's make sure that we have some great date formats. Eso I'm gonna highlight column A And then if we pick on the home menu, then you have your formatting over here are someone to click in the polling. Where is his number? And then we have short date long day and I'll show you some or very since so pick on short date. And of course now they actually look like dates. Okay, Now, let me show you where there's some more date formats. I'm gonna were ableto clicking that pull them on the home menu and you have a couple date formats there. Let me share with you for more beyond that. Here's the way I learned how to format a long time ago and it still works. Right? Click on Column A. And I'll pick on format cells when we go to this screen knows that we have the date category and then you have many other date formats. Okay, so, you know, maybe this will have the two digit months in the two digit year. Now this one is more for the European format. That's what they're using the in Europe over that. So you have a lot of great four match for your dates, and then you can actually make up your own, and I'm gonna shut it to accustom format in a few minutes. But if I pick on custom, this one over here is a D is for the day and then three digits for the month and then the year. So you have even more date formats when we pick on custom and in the custom, you can actually make up your own as well. So let's go back to the date format and I'll pick one with the two digit day in the two digit month and we'll click. OK, And now I saw they look a little bit more uniform because before only the ones that had you know the 10 11 12 would actually show the two digits. Now it looks a little bit more uniform. Now, what if you want to make sure that the only typing date data in that cell well, then here's our first use of data validation. So a data validation you can make sure they type in certain types of data. Let's try something like that. So I'm gonna highlight column A and I pick in the deep the David menu. And then over here we have data validation sitting with the red circle there. Good. So in this case, I'm gonna pick on SAT eggs and I'm gonna click where it says, allow any value. And then that's how one of the choices is a date. That means it would only accept a proper date into their cells. And then you can put a date range. So let's say I want the date to between Let's say you want it between one slash one slash 2000 and let's say we'll go to the end of the the end of the century. So 12 slash 31 slash That said 2100. Okay, so it's not only is to go to accept a date, but it has to be between those two day trade fat. So for the input measures, I'll say, please type in a day. And then for the hour alert, I'll say no, try again and that will come up if they typed in something different. So with data validation, we can make sure they're gonna type in a date. So minute clicking. OK, let's see if it's gonna work. So I'm gonna go ahead and click here. Now I'll type in one slash 23 slash 2020 and that one is perfectly fine. So let's type in a date outside of the range, so type in one slash one slash 1999. And there's how it is not accepting that because it's outside of the range. So that worked. The range work from the pecan. Cancel. Now what if I type in something that's not a day typing my first name, and it doesn't even accept that so literally that have to type in the date between us two days because of data validation. All right, so we conform at the data. If I right click on a column A. I can pick on format cells and you see many date formats there. And there's even more if we pick in the custom category and then also, you can make sure they type in a date with the data validation. So the next topic I want to talk about is making a custom format. So let's say over here, I always want to show the exchange with Let's say we're two zeros in the beginning and then a period and then maybe six decimal places at the end. Well, if I just type in a number, if I type in 0.765 it only shows that 10 But what if you want to show two zeros? Well, we could make a custom format, so I'm gonna go ahead and hot McCollum t. And once again, I'm going to right click on column D, and I'll pick on format cells. Okay, we're going to go over to the custom category now. There is some that are already built right in. As we can say, and you can pick on one of these and you know you can always go to your help screens to see what they really mean, but you can make up your own as well. So let's say I'm gonna type in 00 that and then six decimal places. 123456 So that means that no matter what they type in, it'll show to two places before the period, and it'll it'll now for six decimal places at the end. So let's see how that's gonna work. So I went to cost, um, and then you can kind of make up your own, or you can use the ones that are already there. No, this would happen. The column looks a whole lot better now now that 2nd 0 is showing up, and also they all have six deaths in places and that happen all the way down, so you can always use the formats that come with Excel over here. There's some formats, of course. Now here's another way to get to the format cell screen. I can pick where this is more number formats and then you go back to the format cell screen from here, I picked on custom. You could use one of the ones that are already there, or you can make up your own up here. If you go up, Teoh help screen and type in Excel for Matic. Then it will tell you what those characters mean. But like the D, it's for day and men men miss for a month and then year. Some of them are self evident. Here's hour and minute, you know, So the health screens will be able to tell you what all those characters me. But that's how you can make up your own format. Let's say one more example like that Here in the United States, we have ZIP codes and some of those. It could start with a zero, Like in my state. They start with zero. So what type of 12345 or 12340 at the beginning knows, have zero is not showing that. So I want to make it said that the leading zero will show up if it needs to bay so. Mahala column J Once again, a right click on column J and pick on format cells Good then I pick on custom, and this time I wanna have five zeroes. So just type in 12345 zeroes and then clicking. OK, and now that column has the leading zero. OK, so you could certainly make up your own custom formats. Now let's try a really great for ah, really great thing and excel. That's called conditional formatting. This is one of my favorite features and excel, and let's take a look at our slides for a second. So this slide was talking about how to format the dates. Okay, we did that one. I'm gonna come back to this live very soon. That's gonna be a formatted table which will get to, but I want to talk about conditional formatting. So, with conditional for many, you can make certain cells stand out because of their of what is in that cell. So let's go back to excel now. Let's say in column E, if the numbers are more than $30 we want those to be in red. Well when the hollow column E and then conditional formatting can now be found under the home menu. So pick on the home and a pecan conditional formatting. Then I'll pick on highlight cells. Good. So then we could say greater than less stand and there's kind of thanks. So pick on greater than on this one. There will type in 30 and then over here you pick your format. Now, if you want to have your own format, you could say custom format. But let's say light red fill with the dark credit taxed. But I mean, if they're over 30 and you can see that's exactly what happened all the way down, if they're over 30 they're gonna be in red now. What's nice about this is you could have for Mitt at that column before you even type in the data. And as you change the data, the color will change. So that is going to click on this cell type of 35. And now it's in red because it's over 30. Or if I go back to this cell and type in 25 now, it's back to normal cause it's less than 30. So when you apply your conditional formatting, it just kind of waiting there until you type in some data into that column, and then it all automatically color code. Those cells. All right, So let's try another one. Let's say over here in the country column. If it says Spain, we want those to be a different color. So Mahala column B. We'll pick on conditional formatting and in this case will pick on highlight cells equal to if you want a specific value also equal to and then over here on tape in Spain, and then we'll pick a color there. How about green this time? Good. And we'll click. OK, no, that's exactly what happened. If I just scroll down clearly the ones to say Spain are in green, so that's the way we've use conditional formatting for a long time. Let me show you a couple of new ways to use conditional formatting to really bring yourselves to life a little bit so you can keep her conditional formatting there. But let's go ahead and remove at a pecan conditional formatting clear roles from the entire shoot day. These air a little bit more visual, so I'm gonna have a column E and a pecan conditional formatting, and I'm going to say, Try a data bar on and we'll pick one of these colors will pick on Blue. Look what happens this time Now column. He has the bars. Clearly, the big numbers have a big bar in a small number, has a small bar, and that happened all the way down. So it's very visual. It's not that hard to dio vision. Nice results clear to the big members. Have a big bar in this small numbers. Have a small bar that's quite a data bar. Now let's go ahead and remove that. So they're conditional formatting clear roles from the entire sheet, and it goes back to normal. Let's try another one that's really catching on. This is called icon set. So when I had a column e a pecan conditional formatted and we'll say a concept, maybe you've seen people use the arrows or the circles or any of those others. So pick on the first set of arrows and now column. He has the arrows. So if the number is lower in the range than have a red arrow, if the numbers kind of in the middle, they have, ah, yellow arrow and the higher numbers have a green ara, so I could just tell where that number falls within the range of members. But looking at the Ara now, watch how you can change the actual ranges here so you can modify your conditional formatting. So I'm going to click on column E a pecan conditional formatting. And in this case, we're gonna say manage rules. Even if you have conditional formats back at work, you can pick on manage rules. Good. Now, if that day they're range had more than one condition, they'd be listed right here, and it certainly could have more than one. So I pick on edit rule. Okay, so this is where we can change the ranges. So over here, a pick number I read over here are pecan number. This way it will be based on actual values. So instead of that zero type in 50 and instead of this zero type in 10 So this example means that 10 and below will be read between 10 and 50 will be yellow, and 50 and above will be green. So I'm gonna click on OK, and we'll click on OK again Now. This one wasn't green before that screen. Now, because it's 50 or above the ones that are red means it's less than 10 and the ones that yellow are between 10 and 50. So that's called an icon set. So that and the data bar and the other conditional formats is the ways we could meet. The cells are automatically be color coded and automatically stand out based on a certain condition. Now, of course, she could leave there is there for the next time. By saving the workbook, we can also remove the conditional formatting, so picking conditional formatting and we'll say clear rules from the entire ship. Now what's nice about that is you know you can you can preform at the spreadsheet with all of these features, so that when we add data, you know, if they met that condition, it would have the conditional formatting kick in. Or in this case, if you end the date in that column, that would kick in. So you can always do that before there's data or you can do it after their status as well. Now going back to our slides, let's talk about those formatted tables. So right now this is just normal data. I would call that role data because it's not really format in any way. We share a formatted table and show you one of the benefits of it. So in this case, I'm gonna pick on the home menu. And then over here we say format as a table and that you can pick one of these styles now they all work the same way. It's just a matter of how you want to sit. So I'm gonna go ahead and click on this one so that every other Robi that should a color, right. So we're gonna click there. Now you make sure that this is checked where it says my table has had us right there. So I'm gonna click on, OK, now look at this. This is a formatted table. First of all, it looks bad. It's easier to read in my opinion. Second of all has the pull downs for the sort and filter Those were not there before. So assume you should do the format of table that turns the pull down thought where you could do this sort and filter, so that makes the data easier to work with. Let me show you my favorite feature of the formatted table. Yeah, I can get really quick results because we're gonna add something that's called a total row . So now you can tell where the formatted table ends. You should this little corner over here if you look really closely for that, it ends over a 210. Well, what's what I'm gonna dio we're going to click on the formatted table. Now when you click on the Formica table, you're going to get an extra menu of top design. But you only get the design menu when you pick in the form at a table. So pick on design on the design menu we can we can change the different styles, right? Maybe you want to pick a different style, but follow my mouse and on the design me, I'm gonna pick in the world of total row right there. Good. Now look what happened on very 211 assists total that a 209 is the count. So that's the number of records. Now I'm gonna click on this column for the total row. If you look really closely, there's a pull down clicking that pull down and I'll do a some good and you can see how it says 39 45 20 sex. Well, guess what? That sum is going to change when we apply our filter. So I'm gonna go back to the top again. The format of table automatically turns the filters on, so I'm gonna click in this. Pull them. Where is his country? Of course, we can sort here, but I'm gonna I'm check it where it says select all. And I'll pick on France and Germany. Those house on Lee showing us to France and Germany records. But look what happened. The totals changed. So when we turn that total row on the totals actually change when we apply our filter watch again, I'm gonna click in the pull them for the expense type. And then we could filter on that column. So now there's a lot less records, right? It's the ones from France or Germany, and they have to be breakfast or dinner at the same time. Notice how the total row changes to reflect what's on the screen. So I really like that total row of the form at a table. And then the changes as you apply your filter. So you get really quick results. I let that one a lot now to get all the records back. We can. We can turn the filter off completely or I could do it clear. So here's a quick tip for you. I can pick on data. If I pick on the word of filter, it will turn it off completely. But I'm picking the word clear, and that's all the records back from the filter. But it leaves the pull downs there so I could do another filter. All right, so clear is always useful. Let's go back to the total row at the bottom and those house back to the full numbers. All right, so I really like that format a table, and then we can add that total row. We did that under the design menu right over here. So let's go back to our slides for a second. See this slide machine you had to make the formatted table. We pick on home form as a table and you pick one of those formats and then you could see the format of table. And that is how on the next slide, I showed you how to do the total row, and then you can just click on one of those columns on the total row, you would see the pull down, and that's where we picked on some, for example. All right, so that's a really, really nice future. Let's keep on going now. Now this is where I was showing you some of the formats, right? So even there is, um, we did the custom format with the five zeros, but there's actually a zit could built right in if you do format cells and to pick on the special category, then received a ZIP code, which is the five digits that could zip. Code Plus four is +39 digit ZIP code that we have in the United States now and in the phone number and the Social Security and I have presets formats. Remember, you can make up your own under the custom category of format cells, so we actually did that one already. Let's keep on going now Here's some of our date formats that we talked about, So if you pick in the date, category of format cells will see many date formats and you can even make up your own under the custom category as well. And then you could see how there's a custom category. Now, in this case, this is gonna be the data validation that we did. So remember how we made it so that they had the type of a date. So to get the data validation, you're gonna highlight the data first, and then you pick in the data, man. Yeah, and you're looking for this icon. It would be like a red circle or your associated the word data validation there. And then you get this window. Eso Here's another good use of data validation. This one is going to make a drop down list. So let's try that one. I'm gonna go back to excel now. Now, in this case, I'm gonna go over to this tab. That's called data validation. Good. Oh, let me show you a short cut. That's really great with the sheets. When you have a lot of sheets, you could go back and forth with the sheets with these hours, you say. But maybe you don't know about this one. If I right click in that arrow, then I see a list of all the shoots. Now they're not in alphabetical order during the order that appear in the workbook, then you can easily browse through the chutes. So in this case, you can use these hours to go back and forth. But I can also right click on that arrow. Then I see a list of all the shoots that exist. Pick one of those and then clicking. Okay? And then go right to that sheet, like I just said. So let me show you another great use of data validation. And this this will help you automate your spreadsheets as well. So let's say, for the expense type, I want to make sure that pick one of these expense types. Well, watch how we're gonna make a pull down with data validation. So I'm gonna holla column f and then on the data menu pecan data and a pick on data validation. Right? Fair. Yours. You're probably said there were a date of elevation. All right, so I'm gonna click that, and they were back to this window. In this case, I'm gonna pick on the word settings, and then this time I want to use a list. And look what it says when you pick on list in sale dropped down. That's the pulling it we're talking about. Now. for the source. You can either type them in and separated with the comma. So it would say breakfast, comma, dinner, comma entertainment or since I already have that list there, I'm just gonna highlight that list. Clearly, that's the easier way, because otherwise I have to type the man and separated with the comma. So for the input measures will say please pick one from the list. And then for the terror alert, I'll say, you know, try it again. The real key to this one is the sad eggs. Here we have a list, and then I highlight the list of items over here for the source. Let's see how this is gonna work. I'm gonna click, OK, I'm gonna click on this cell, and it says, please pick one from the list. That was the input message, right? And I'm gonna click on that pull down and you can really pick one from the list. So pretty quickly, I was able to make a nice pull down using data validation, and that's gonna automate the spreadsheet. See, you know, facilitate data entry and also now that have to pick one on the less. So for example, maybe because what because of what's happening? Maybe had the purchase of Webcam. So I'll take the Webcam and knows how that one is not on the less that says no, try again. So I really don't have to pick one that's all on the list. So in this case, maybe I'll pick line other. Okay, so we used a data validation to make a nice pull down, and that's what that slide was, referring Toa here said a list and you can see I used that exact list. All right, so we're gonna keep on going now. We'll have questions and answers at the end. So here's the data validation that we did with the icon set number, how we picked on the data. We put the home menu and then I said, Conditional formatting. And there is the icon sets, and maybe you've seen people use those call for icons. So go with me today. This stand up. Now here's a formula that comes up a lot, so let's say you have to look a bit information from one sheet to the other. If you don't know about this formula, then you have to do it manually. But the V look up is a great way to look up one look up information from one sheet to another. Let's give it a shot. So I'm gonna go back to excel. This is a formal you want to get pretty good at because it comes up a lot. There's a good sheet. I'm gonna go to the customer. Shoot. Okay. The customer sheet has a list of all the customers. We have the customer I d and the company name in the contact name. Everything about the customer is on the customer sheet. Now I'm gonna pick on the order. Shit. So the order she has a list of all of the orders have the order i d. And the customer idea in the employee i d everything about the order. She Everything about the orders is on the order sheet. That is how the order she shows the customer i d. But it doesn't show the company name. So if I needed to get the cuffs a customer knee right now, I'd have to look at that customer I d from the order shoot. Go back to the customer. She find the right one and then look at the company name and then maybe bring that back over to the other sheet. Well, that's exactly what the V look up does, except that automate that process for you. So let's see how we ve look up is gonna work. That's how I'm back on the order sheet. Now, In this case, I want to add a new column, a column c So right, click on column, saying I'll pick on insert here. We'll type in customer needs. That's just the column heading. Uh, that's a company name. Okay, we're going to start the formal with the equal sign as all form of this always start. So now you can type it in. But what if you're you haven't learned that yet, So we're going to come up here and pick on this F X. The FX is going to give you a win that is called insert function. I really like this window a lot because it's going to show you a list of all of the built in functions within excel, and there's quite a few of them and they're always anymore. Now you can search for it up there or you can search for it by category. so there's financial functions, time, math and trig. Those were just the categories. Now I've used if you look up 70 times and in which category is going to be in, it's gonna be under look up and reference. So we pick on, look up in reference and then I scroll down and we'll pick on the view. Look up now. It will give you a brief description over here, and you can pick on help on this function, and that'll give you some examples of how to use that. But I want to show you how to use it right now. So do we. Look up and I'll click on. OK, good. Now the look of value is what we're trying to look up. I'm trying to look up that customer I D, which is B two. That's what we're trying to look up. The customer do is to look up Value B two, so the table array is where we're trying to look it up from. That's usually the one that's on a different ship. It doesn't have to be on a different sheet, but many times of this. So watch what I'm gonna do. I won the space for the table array. When I come down here and picking the customer sheet. Now, let me give you a big clue here. It'll be much better if you have it. The entire column. We just work out better. So I'm gonna have a column A and column B And that's how the table races. Customers. Exclamation point a colon big. So when we see the exclamation point in a formula that separates the sheep name from the cells, so this really means the customer sheet column A through column B and the column is going to match this column. A contest. The first column of the table. So if you were trying to find part numbers than the first column of the table will be part numbers now, it doesn't have to be column A. Necessarily. It just has to be the first column within your table. That's the one that has to match here. Just happen to be column A. But it's not always let me show you how you calculate the column index number. You're gonna count for the left side of the table. So in the table this is column one, and this is column too. I want the company name, which is the second column, 12 So over here I'll type into, which means when I find the right one, I wouldn't return the second column of information, which is the company name. Let's see what range look obsessed Range. Look up is a logical value. That means it's going to be the word true or the word false to find the closest match you type in true there to find an exact match you type and false almost always type in the word false here so we could do an exact match. If you're looking for a part number from one sheet to the other, you want to make sure to find that right part number, right? So you almost always type in the word fall so we could do an exact match. We'll click on. OK, now I'm gonna make that column bigger. Okay, Now, here's the V. Look up formula, so you can always type it in. One should learn it. But one way to automate building the functions when we're talking about automation is the FX, which will help you build that function on give you a list of all the other functions as well. Let's think about what it did. It took the customer. I D v i n e t I looked it up on the other shit. Found the right one, and then return the company need back to here. That's make sure it gave us the right company name. I wouldn't assume I want to check that. Especially I should learn here So you can see how the customer D says the i n e t. And you could see the customer name that I gave us. So I'm gonna go to the customer sheet. I'm gonna scroll down and there that summer 80 sex V i N E t. And we see it gave us the proper company name so that you look up, did its job. So, you know, let's go back to the order. She and they really found the right one. Now I'm gonna copy that four minute down. Now, here's a great way to automate your spreadsheets. We're going to copy that formula down. I'm gonna give you a short cut right here. That's going to be really worth your time. I'm gonna get the black cross in the corner of the cell that's called the speed fell or the order fell. Now you could try get down. But let me show you the shortcut. You get the Black Cross and you double click on the Black Cross. Double click, and then it goes all the way down. That's gonna save a lot of time right there. Now let's see what really happened here. This one says be Look up, be to write that. That's maybe the V I N E T. Well, the formula changed now this and says B three, and this one says, before that's called a relative formula. So when we got the Black Cross, of course you could drag it down. But you can double click in that black cross, and it's going to go all the way down, but a copy. The formula, but a cheese each one relative to that cell. So the 1st 1 says B two, the 2nd 1 says B three, Thurman says before it's called a relative formula is going to save a lot of time already had the type in that form of one time, and then I was able to copy it down. The changed each one relative that sound that's called a relative formula. When you see most people's larger spreadsheets, it's hardly ever the case with the type of each Formula one by one. Whenever they can to do it that way, click on you know they do the top formula. Get the black Cross, either drag it down or now you know you can double click in that black Cross and it's gonna go all the way down a copy, the formula, but achieved each one relative to that cell that's called a relative formula. So that's a great time saver right there. But going back to the V. Look up. Now, each one of those has the proper company name based on the customer I d. So don't you think that would have saved a lot of time rather than you having a look of all of this individually? Now, that is how it says the word false at the end There, that means it has to do an exact match. So if I type in a a, then that was going to say and A and Amy is not available or not found. We're trying to find an exact match. It did find an exact match. So it says N A. So let's go back toe what it said before via AT and T. And then you could see it found that one. All right, so that's a great function that you're going to use all the time. It's a great way to look up information from one sheet to another, and it's called the look up. So and then the slide is showing. That knows the exact form that we just step. And then we were able to copy it down. And then that kind of demonstrated the relative formula as well. Okay, now this is gonna be a really going. There's a formula that's called if error, and it'll give you a better our message or you can make up your own our messages. So let's try an example like that, and we're gonna go back to excel again. So remember when I turned in a year says I m, which means not available or not found. Well, let's see, you showed up to your boss. You're department head and they wanted to say something different. They say, Well, no end a what does that really mean? You make it say not found or something like that. Well, here's what we can do so you can use the if our formula to be able to change the our message for an error. So I'm up here on the form of the bar and I quit right after the equal sign and I'll taken if error one board this basis if our and let's see what it says here returns the value if our if the Expressionism error and the value okay, so it's gonna allow to do a better ever message. So doing open parentheses now, this could be any formula in this case. I'm using the V. Look up. So I'm gonna go ahead and click the end of that and I'll type in a comma. Now the word not found here has to be in quotes because it's text within these formulas. Whenever there's text, the text usually has to be in quicks, so they're open quotes, not found close quotes and then close the parenthesis. So let's take a look at the whole thing it says equals. If error open practices, then we had the entire V look up formula there. After the view, look up for me that I have comma open quotes, not found close quotes closed Parenthesis is so What that really means is if the V look up gives you some kind of error, then put the word not found there instead. If the vehicle does not give you an error, then give us the value of the view. Look up so you can use the F air on any kind of formula. Let's see what's gonna happen here. See now it says not found. But if I copy that down with the Black Cross in this case, when a double clicking that black cross. So the ones where the V look up found the right one that has the actual answer that right. But let's change this one. Change that one to BBB, and now, instead of saying and a now says not found so I really like that if our formula or function you can use that with any other formula or function. So if that formula gives you an error, you can make up your own ever messages and it doesn't have to say not found what could have said anything there and then if it doesn't give you an hour. Then you get the actual result of the formula. Okay, so let's go back into our slides and we see the example that I'm using here. If our and then the viewer cup is there after, If you look up, then I have comma not found close parenthesis. So that's going to know about if you have the error messages in your in your cells and you want to have a better message you can use if our let's keep on going Now. Now, in this case, we're gonna do calculations with dates. Eso that's go to a different sheet now and we're gonna do some calculations with dates here . So once again, I'm gonna right click on this arrow and take a look A list of all my shoots here. So I'm gonna go to when it's called a D Formulas and we'll pick. OK, good. Now, when I made this sheep, I use the holidays for 2019. Of course, you're gonna, you know, type in the holidays. Your 2020 these in the United States Bank holidays, so you would pipe put in your own holidays will come back to that in a few minutes. So over here I have one slash one slash 2019. And let's say I type in the eight slash one slash 2019. So I want to see the number of days between those two dates. Well, if I subtract one date from another date, then they will give me the number of counter days. So over here all said equals this the minus this stick. So it's as equals A to minus a one. If I subtract one date from another is going to give us the difference in days. But that is counter days now. What if you want to see the number of business days between two dates? Well, what we can dio is we can use a built in function. It's called networking Days. So watch how it's gonna work here are taping the equal sign, and I'm picking the FX to go back to our Mr Functions. This is gonna be under the date and time category. Good. And then I want to scroll down and we'll find when it's called network days. Let's see what it says returns the number of whole work days between two dates. So that sounds like without when I dio looking. Okay, Okay. Now for the start, they applique on January 1st and for the nd a click on August 1st. Now, that is how the holidays is in light black, and these are in dark black. That actually has a purpose. The ones that they're in dark black, there's a required and the ones that Aaron White black like that one are optional. So I'm not going to fill the holidays in right now because it's optional. Let's see what we have before that. So there's 153 business days, So that's just took off the Saturdays in this Sunday's. Okay, so just by doing it the normal way, we just took off the sides on Sundays. But now you were typing your list of holidays. So again, I must have made this in 2019. He's a list of the bank holidays in United States for 2019. So you're typing your holidays with actual dates, and of course, you make it for the current year. If that was the dates that you were working with now wat