Excel Formulas & Functions: VLookup, HLookup, XLookup, Index-Match & More

1. Introduction : Hello, everyone. My name is Suzanne, and I'm thrilled to welcome you to this Microsoft Excel formulas and functions course. Whether you're a beginner looking to build a strong foundation or an experienced user aiming to refine your skills, this course will help you master essential Excel formulas and functions with ease. What you'll learn in this course, the difference between formulas and functions and how to use them efficiently. Master essential lookup functions like V lookup, H lookup, and X lookup to retrieve data with accuracy. Work with index and match for advanced lookups and data management. Understand dynamic array formulas for more flexible and automated calculations. Use logical functions like IF and or and countif to make smart data driven decisions. Learn how to clean and validate data using text functions, trim, find search, and data validation tools. Enhance productivity with time saving functions like SUMIF, countif, and round DIF. Format and present data professionally with structured tables, charts, and conditional formatting. Why take this course, hands on practice with real world examples, step by step guidance to ensure you understand every function deeply, boost your efficiency and make Excel work for you. By the end of this course, you'll be able to confidently use Excel formulas and functions to automate tasks, analyze data, and improve your workflow like a pro. If you ever have any questions, feel free to ask. I'm here to support you throughout this journey. Let's dive in and start mastering Excel together. 2. The Difference between Formulas & Functions : Hello, friends. In this lesson, I will teach you difference between formulas and functions. A function is an expression which calculates the value of a cell. Functions are predefined formulas and are already available in Excel. A function is a predefined formula that performs calculations using a specific value in a particular order. All spreadsheet program include common function that can be used for quickly finding the sum, average count, maximum value, and minimum value for a range of cells. In order to use function correctly, you will need to understand the different part of a function and how to create argument to calculate value and cell references. So formulas. When a user types an equal sign in a cell, they are starting to create a formula. For example, equal a two plus B two plus C two and functions. Why not use a type equal sign followed by a predefined set of letters. A function being to be implemented when a user type an equal sign followed by a predefined set of letters, a function being to be implemented. Here I see how I can write functions. For example, equal sum bracket A one B two. Here show formula function. If you need any help, just let me know I can help you see you on the next video. 3. Understanding the Error Function: Today's tutorial, we are going to tackle a common challenges that many Excel users face. Error in Excel function. Don't worry. We have got you covered. By the end of this tutorial, you will be equipped with the knowledge to identify and troubleshoot error in Excel functions. Let's dive right into it. First, let's discuss the most common error you will encounter in Excel. Like you can find the values error and individual data and also the division error and it's the invalid function name, and this is the error of you can find it is all like this is the not available error. Typically when data is missing, then you can find the referral errors or you can find this kind of errors. Now let's look at each error in details. First step, the division error. This happened when you try to divide it into a number by zero. We will demonstrate how to handle this error. Now if I use here, say something, or I can just say it will be the ten, and here I say the 10. Then if I use this one, then divide it by zero, it on the. Here a simple example. We got it and which I have show you this, give us zero in there. When this happened when you try to divide the number by zero, you will get this kind of error in there. You can easily handle this error by using here and use the number and it can give you and take out the zero. Next, the value and we will use. Error occurs when you use an inappropriate data type in a formula or functions. And this call, we can say, this is the referring wrong type of cells. Let us see how to address this error. We will create a like scenario where this zero appears when you use the if you know, you can also use this one. Your function again to handle it gratefully. Like if I type here something like this, instead of the or, I can just say the text is one and you share the few number, then try to multiple this one and use this one, then close it. Hit on the Okay. So now you can see it gives here this 10. So by this, you will get the value in there and you can do it by handling if error, or you can just say the value. You can use this one or you can use here like the different one, or you can easily use instead of writing the text, you can use here the number like the four. And here we got that and we can easily handle it. Okay. Now let's move into another error and we call. This will be the name error. So it means referring a wrong cell name or misspelled functions. This error is thrown when Excel cannot recognize the function's name. You enter and we will reveal how to troubleshoot this issue. Like here, if I type here and just few number and use something this one, then if I use here and want to add it like it with the sum and instead of sum, if I write here the sum and then close it and with a parenthesis then, or I can use this one then. Here, you got it. Now we got the name error function in there. So this happens when you type the misspelled function in there. Okay? So how can you handle it? Make sure that you have write the right error in there. Instead of the sum, if I write here, the sum hit on the Okay, now it's handle and you can now see it does not have this error. Okay. Now let's move into the lookup formula. Finds no more results. So it's mean, so it's awkward. When Excel cannot find, you are looking for and we will teach you how to work with this. Sometime you may working with the up function and also the age lookup function in your dataset, sometimes is come this error. So if I type here something like the text or this one, then writing here, the up functions, this one. Just try to come out this error. Then let me use this one column index, then close it hit on the Okay. Here we got it. Because according to our lookup functions, we don't have this requirement in our dataset. For this, you will get these kind of errors. You can handle this error by using something like if error or I can just say the if error function is this one. The next one you can use the value of error. What kind of data you want to use the no data and close it hit on the Okay, so here you got it. This way you can handle this kind of error. Okay. Now let's move into on the reference error. This means the linked cell has been deleted or is invalid, which happened? Or when you reference a cell that is not valid or has been deleted, we will explore how to fix this error or I will just show you how can sometimes when you can get this error like I am using here, the number is one, and also I used here this number. Then I will just multiple this one and with this number then hit on the Okay. So here we got it this number. But when I delete this row and now you now see it gives you this error because we have deleted the cell from our dataset. It's happened when you deleted your selected range or working with the formula and you have deleted the range. Okay. Now let's move into another error. And this is the final error. This means the cell formatted as number, but it's too large to be displayed. Date and time that are negative or too large. I want to show you how. It's like, if I want to write here the two days days or I want to use here the two day function here, the two day, then close it with the parenthesis and hit on the Okay now here we got it because we don't have enough space or enough width and height for showing up this result. So you can handle this by fitted with the width and you can get the right one. But when we use this one, so it cannot be displayed days and times that negative or too large in your cell. Okay? So by this fixing or double clicking, you can easily fix this error. So to summarize Excel error are a part of data analysis process. But with the right knowledge, you can manage and correct them efficiently. We have covered the most common error and how to deal with them using various Excel functions and that our wraps up for today's tutorial. So if you need any kind of help, just let me know I can help you. So see you on the next video. 4. What is INDEX Formula: This lesson, I will teach you what is index formula. The Excel index function returns the value at a given location in a range or array. You can use index to retrieve individual values or entries, row and columns. The match function is often used together with index to provide row and columns numbers. How can I write index functions? Here I show in this formula, you can write equal index bracket, array, row number, then column number, and also array index argument array, a range of cells or an array. Custom row number, the row position in the reference or array, column number, the column position in the reference or array, area number, the range in reference that should be used. So easy to say, returns and item are collection based on provided row and columns index. So if you need any kind of help, just let me know I can help you. So see you on the next video. 5. Using IF Function in Excel: Come back. In today's tutorial, we are going to dive into one of the most fundamental and versatile Excel functions, the I functions. The F functions in Excel is incredibly powerful and allows you to perform logical tests on your data. It's often used to create conditional statement that automate processes in your spreadsheets. The sentence for the I functions is simple. So if I use here and right here, the sentence or the type here, the if functions, then the first one is the logical test. So this is the condition you want to check. Then the next one is the if true is the result. If the condition is met, the next one is the below it falls, so is the result if the condition is not met. Let's see the basic example to understand how this work and we have the data set with the names, areas and sales report. According to the sales report, we want to find out here the bonus and if they are eligible for the amount, so they will get the bonus. For this, I am using the I functions right here. The I then the first argument is, say the logic, lets me select this one, the logic if it is greater than or I will say the equal, say the 70 or 70,000, then the next argument, we want to move the value if true. So what message you want to show off? I want to show stuff like the get bonus, this one. Then if the argument is not with the naught greater than 70,000, what the result will come. So it will come off, not get result. I just say this is the example. You can use the another one, close it, then hit on the, according to our logic and with the I functions, this is the get the 70. For this result, he gets the bonus. Then next one, if I just drag it down, and here you can now see here, according to our rules, this is not greater than the 70,000. For this, you say that, not get the bonus. With the I functions, you can easily stand out and get the result with the logical function. You have now mastered the I function in Excel. It's a versatile tool that can help you automated process, make data, driving decision, and create more intelligent spreadsheet. In our next tutorial, we will dive into more advanced Excel functions and techniques. Thanks for your watching. 6. SUM, SUMIF & SUMIFS Functions: Today's tutorial, we are going to dive into one of the most fundamental and useful function in Excel, the sum function. Whether you are new to Excel or looking to refresh your skill, understanding how to use the sum function is essential for working with spreadsheet effectively. All right, let's jump right into the Excel. I have a simple spreadsheet open here, and you can follow along with your own spreadsheet or use the one provided in the video description. Okay, so the sum function in Excel is used to add up a range of number basics like the sentence. It's simple. If I type here, the sum function right here, the sum, and here we use the number one, number one. Number two, you can include as many numbers as you like within the parenthesis or separated by column. You can also select a range of cells. Let's select this one and close it with parentheses. Then hit on the ok. Now you can see the according to our range, and this is the total number of the cells. Here we got the total in this with the sum functions o. So Excel also offers a handy feature called autosums, which is automatically suggested a range of number for you to add. So let's see how it works. If I take my cursor in here and go to the autosums. And now here, it selected the range automatically, and here it gives you the total cell report in there. Okay. So that's it for today's tutorial on how to use sum function in your Excel spreadsheet. So thank you for watching. 7. ROUND, ROUNDUP & ROUNDDOWN Formula: Hi, everyone, and welcome back. Today we have an Excel tutorial for you. We'll be diving into the world of Excel function, and in particular, we will be exploring the versatile round functions. All right, let's jump right in. The round functions in Excel is a powerful tools that allow you to round number to a specific number of decimal places. It's especially handy when you are dealing with financial data, measurement, or any situation where appreciation is crucial. Okay? Now we are going to start with the sentence of the round functions, the sentence of the round functions. If I say here the round and use here the round, and here first one is the number. So this is the number you want to round. Then the next one is the number digit. This is the number of decimal places. Two is you want to round the number. Okay. Let's select this number. Then use the number of digits. If I say this will be the one, then close it with the zero, and here we got it. Now, this is the result. And if I drag him down now here we got after the decimal, it is the one number. Now, if I want to change instead of the one, if I say this will be the zero, then hit Okay. Now we have a dozen. After the decimals doesn't have any digit in there. So if I drag him down now, you can find it gives you the round number after the decimal, or it doesn't have any number after. So that is for today's tutorial on the Excel Round functions. I hope you found this tutorial helpful. 8. Convert Number to Words: Today's Tutorial, we will learn how to convert number to word in Microsoft Excel. This can be incredibly handy for invoice check or any other documents where you need to represent number in words. Let's get started. The first step is to open your Microsoft Excel worksheet. Make sure you have the number you want to convert in Excel. For this demonstration, I will make her the same number like the 40 or six, this one, or I can just this one. Now, I will convert this number into the word to convert number to word. We will be using the macro. So let's make sure macros are enabled, and you can find it by developer and use the visual basic in there. And from there, let me use the new modules. And here I will use and make it macros or VBA code, so you can easily copy this code. So it is in your description here that what I'm going to do, I'm going to paste in there, so let me use and paste in here. Now, what I'm going to do, I am going to write here the spell. Now here, you can find. This gives me this one. Use the tag. Now selecting this one. Then close it, hit on the Okay. So when I use the spell and the code, now you can see it gives me here like the $456 and there is no cent, okay? Now, if I use here, like the 67, what happened? Now you can see it gives the result $456.67. Okay? By with the dragging down, you can get the word in there by using the spell function or spell number function in your Excel. So by using the code with the VBA editor, you can easily get this function to convert the word to the text. Okay? And there you have it. You have learned how to convert numbers to word in Microsoft Excel using VBA Macros. This can be incredibly useful in various functions or various financial and accounting taxes. So thanks for watching this video, and I will see you in the next tutorial. 9. FIND and SEARCH Functions: Today's tutorial, we're going to explore some powerful features that will make your spreadsheet work more efficient, finding search and replacing data. This function can save you a ton of time and help you clean up your data quickly. So let's dive right in. So let's get started. First, we are going to discuss about the finding function and finding data. Finding data in Excel is simple. So you can use by using the Home tab, and they are under the editing, both find and selections. Click over here and click over the Find options, and you can use this one. Or you can use the Control F, and it will pop up with the same dialog box. Find and replace. Now type the text or value you are looking for in the find what box. If I use here, if I write here the find, just write and use the Fine tool or find next. Here we got it. We have here, we can find the find option. The next one you can find with the work the finding here. This way you can easily use it. Now move into the search function. This is the similarity, you can go to the Find and selection and on and from there, use the find and now moves into the options in there. So here, the option to specify search criteria such as the matching case or match entry or sales content or matching case, or also the whole world. What you are looking for with the whole world or even using the old class in there by using the Find tool. So here you can get the find. So where you can find this one. Now let's move into the search or replace functions. So this is the same way. You need to go to the find answers and use the replace. So it will pop up the same thing. Then from here, what you are looking for, type here, the into file there. And if I use here, like the into this one and let me use and just use the find all. Now you can find. So you need to or you can easily check out. By using replace, so you can easily find out where you cannot find the match. So what I'm going to do, I am going to need to use here into this one. Again, I will use this one or just write, find case and replace all. Let me use again in here. I need to use the A, I can just say in here and you can now find this. So here we got it. Now, instead of the intro, if I say something like the Andrew this one, then use the replace. So now you can see it gives the replacing one, okay. And there you have it finding searching and replacing data in Excel. In Breeze, these functions can save you a lot of time and effort, especially when dealing with large dataset. Okay, so thanks for watching this video, and I will see you in the next tutorial. 10. TODAY Functions: So on this lesson, we will learn how we can use two D function in Excel. This is the most easiest function that we can use. All right, so let's go to the video. Let's go and see how can use two D function in our Excel sheet. This will be exciting because this is the most easiest part. So over here, you can see, I have created a table as known as two D, and I will bring column on over here I will bring is equal to two D. And you can see that able to get the two D option over here. So let's me bring write two D over here, and you can see that I already got a function, and I will use the parenthesis, close the bracket, and I will use parenthesis close bracket. Because on this two D function, there is no other parameter. I will just hit Okay, and you can see that I have able to see our date over here. So let's check whether this date is actual date of today or not. Let's go and see over here. As you can see, it is 71 2022, and it came over here. So this is how you can bring to the date. So in the scenario of having a lot of data in the same day, you need to put all the date one by one. Which will take a lot of time and waste of your energy. So with this method, you can very easily bring out to this date. Also, there are shortcut key which you can use, which is Ctrl and semicolon option, and you will get the date easily. So I will be writing you the shortcut over here. City Hour, and the people who are using MAC, you can use Command plus onsine. All right. So this is the shortcut key that we have. And for CMD Command plus, this is for Mac and this is for Windows. Alright, so through this shortcut, you can very easily use or bring to this formula to your sheet. So let's go and use our shortcut again over here, and you can see that we have able to get our shortcut. And as I drag it down, you can see that will start increasing. And so very easily, we can use this to this function in our Excel sheet. So see you on the next video. 11. NOW Functions: So we will be learning about how we can use now function in Excel Sheet. So this is also similar to the function, and these are very easy formulas. So let's go and see how we can do that. So over here, I want the current time and date. So let me go and right now and let's bring the parenthesis opening bracket and close bracket and hit Enter. And you can see that we have that exact date and time over here. And this is how we can very easily bring out now function to our Excel sheet. So for the shortcut key, as we have been learning, let's write Control plus Shift plus clone. This is for Windows and for Mac, it's Command plus Shift plus plus. So let's go and try it for Window as Am we sing Window PC Control Shift and Clone. And you can see that we have able to bring out the exact time for now. So through this way, we can very easily. So I hope you have understood how you can use today and now function in your Excel file. 12. DATE Function: Come back. Today, we are going to dive into one of Excel most essential functions, the date functions. This function allows you to work with date in Excel, making it easier to manage data related onto time. By the end of this tutorial, you will be effort at using date to handle date in your spreadsheet. The date function is Excel is a powerful tool for managing dates. You can use it to create date values, work with date data, and perform various calculations involving dates. It's perfect for managing schedules, project timeline, and much more. Okay, now let's move into in our Excel worksheet, and we are going to use here the date functions. So this is the easy sentence, also the argument. And if I use here the date functions, then here you see the year. So this is the first argument, the year you want to create a date for. Then if I say this will be the 2022, then the next argument month, the month you want to create a date for. So I use here the three, and you can use also the one to 12, and the next one is the date, the day you want to create date for one to 31st. If I say this will be the 15th, then close it with the parenthesis. Okay. So here you got the date, and now you can easily format this one by using the number format and use the year by using the custom. And if I use this one the M, or I use the day for this one, and this way, you can easily make it. Okay. Now here we got it the day in there. So that's it for today's tutorial on the date function in Excel, and we have covered the basic sentence and example, okay? So you can effectively manage date related data in your spreadsheet. So thank you for watching and I will see you in the next tutorials. 13. DATEIF Function: Hello, everyone. In today's tutorial, we are diving into our very handy Excel functions that will make your data related calculations are based. We are talking about data functions. By the end of the tutorial, you will be able to use it like Pro. Let's get started. The first thing, let's understand what the data function. Is all about the dative function in an Excel formula that calculates the difference between two dates. It's particularly useful when you need to know the deductions between two dates in terms of day, month or year. Now let's move into the example. Here I use the data with the customer's name, cities, department dates, start date. And this is the end of date and I want to find out the year of service and month of service or day of service. Right here, the dative function then I'm going to use here again, dative in parenthesis. First argument, I use the started. Then next argument, I want to use the Internet. Then the last argument I want to find out here. So use here the Y, then close it with the parenthesis. So here we got the years of service of or during this time. Now, if you want to apply the rest of data by using the tracking down, and you can get the year of service according to the data. Now let's move into the month of service. So we will use the day to day function and use here the same way, then use the end date. Then the next one, we want to find the month. So I used here the month and close it with the okay. So here we got it. During these days, it has the 36 month of the year. And if I want to apply now, you can easily drag in the year. Same way we can use for the day of service. Just write dated then use the start date, then use the end date. Then we will use here the day four, and it's also symbolized the day, and this is the unit, then close it hit on the O. And here we got it. The days of service during the start date and also the end date. Okay, now apply to the rest of data, and here we have the days of service between the start date and also the end date. That is, you now know how to use the data function in Excel to calculate date different. It's a simple but powerful tools for various applications. Okay, thanks for watching this video, and I will see you in the next tutorial. 14. EDATE & EOMONTH Functions: In today's Excel tutorial, we are diving into a powerful function that can simplify your data related calculation, and I am going to show you. And while exploring the math function in Excel, first, let's understand what a month stand for in month stands for each of month. It's a handy Excel functions that help you calculate the last date of a given month based on a specified start date. This function can be incredibly useful for various taxes from financial planning to project management. Now let's move into the example I used here, the few data with the date and also the month, and I want to find out the end of the month according to the value. So I will use here the month functions. So right here with the equals and use here, the month just write and use this one. Then it says the start date, let me use this one. Then I will use the comma. Then here it says the month. So I select this month, then close it, and here we got it. This is the date, but it now in the general format. Now let's move in to convert as a short data or short date. And here we got it. This is the month of November and end of the month in the 30, okay? So same way if I want to apply the rest of month and with the month, I can easily drag it down and it can give you the according to our conditions and with the month. Here we used also the negative three. For this, it also takes you back into the before the June. It was the June. Now it takes two into the march in there because we used here the negative three. Let's wrap things of the month. Functions is a valuable tools for working with data in Excel. By mastering these functions, you can streamline various success and save time on data related calculations. Thanks for watching this video and I will see you in the next tutorial. 15. EDATE-Functions: Today's tutorial, we are going to dive into our powerful Excel functions. Edith. Edit is an incredibly useful function for date calculations in Excel, and by the end of this tutorial, you will be able to use it with confidence. Whether you are managing projects, analyzing financial data or simply keeping track of debts, Edith can be a game changer. Now let's move into with the example and I'm going to use the init function with this data. Here, we use the date month and I will write here the edit functions. Now if I use here the edit, then it says, then returns the serial number of the date that indicates number of month before or after the start date. Now, first one, it says the start date. I use this one for start date. Then it says the month, select this one. Then here we can find and here we get the month. It was 14. Now we got it the 11th August and now we got it the 11. Okay. So by this way, you can easily calculate or add the month with your date. Now, if I want to apply the rest of this one, you can easily drag and down, and it can be give you the edit functions. Okay? And here we say, and we found here that it was the Jones. But it takes into the March because we use here the negative three. And for this is give you the negative month in there. And we got the result the March 30 and 2023. That is for today's tutorial and how to use the edit function in your Excel worksheet. Thanks for watching this video, and I will see you in the next tutorial. 16. Dynamic DROP DOWN List: This lesson, we will learn how we can create dynamic drop down list in Excel. As we have learned about how we can create dropdown list, we want to see how we can make it more dynamic. Let's create a drop down list over here and then we will understand why we need this auto object list. As we know how to do it, you have to go to your data and then create data validation over here. Let's go and select the list, Let's select list and then let's click Okay, or let's click the source and hit on the O. Over here, you can see very easily we are able to see all the list on this normal list. Let's go and insert a new name such as Japan, right. All right. Now let's go over here and let's see whether we can find on this list Japan, but it's not there. So that's why we need this auto object list. So whenever we are updating something, this list needs to be updated because we might have thousands of items that we are listing. So you cannot use auto update or changes all the formula all the time. So for doing that, let's go over here and let's select Insert option. And let's select this all and create a stable. Over here, you can see that it has selected from item till the name. And over here you can see the dialog box. My table has a header. If you doesn't save it, this item will come under the list. So you have to select it. Let's click Okay. And as you can select that, we have able to create an item over here or a table. So now let's go to our data option, and over here, let's go and do our data validation. Let's go and select list. And after selecting list, let's select the data validation list. Let's go and select it. All right. Let's click, okay. So now let's go and write the name Japan. Hit Enter, and let's give our name as England as well. All right. So we will see whether we find this Japan and England on our list. So let's go down below, and you can very easily see that we have able to get Japan or any other sort of England, and the Excel sheet or this list is being updated because we have the option, which is dynamically create a new list on our auto update list. So through this way, you can use your dropdown list properly and hope you will practice it. So thanks for watching this video, and I will see you in the next tutorial. 17. DROP DOWN List & Filtering: On this lesson, we will learn how to create dropdown list and filter them on our Excel file. As you know that how to create the dropdown list, we will how we can filter as well. For doing this dropdown list, I have this option. This is a brand and this are the product of those brand. Let's go and create the item name over here and create the dropdown list. For doing that, we will click on this cell and then go to our data and then data validation. On the data validation, you will find the list option. Over there, we will be selecting, Let's go and select these options that we have. Let's go and hit Enter. After that, I will click Okay. Over here, you will be able to see all the options that we want to use for this section. Let's quickly make Let's just copy a few of them, copy, and let's paste it over here. All right. Now you can see that we have able to bring the item name from here. So now we want to do filtering for doing filtering before that. You don't want this one to show on your Excel sheet. The easiest thing you can do just go right click and you can hide this cell from here. Now, let's do our filtering. So let's go to our insert option. So let's go to our data and the filter option over here. Let's click on the filter option. And on this filter option, when you click on this down arrow key you will find here the filter available. So let's go and remove most of them. And we want to see gigabyte product. For example, let's click Okay. And very easily, you can see that you are able to find the related item that you are looking for through this filtering option. So very easily, you can create drop down list and filtering in your Excel sheet. So I hope you have gained something out of this lesson. 18. FILTER Function: Hi there in Today we have an exciting tutorial for you. We'll be diving into the world of basic filtering in Excel. Whether you are new to Excel or just need to quick refresher, this tutorial is perfect for you. Let's get start. To begin, let's understand what basic filtering is and why it's so useful in Excel. Filtering allows you to quickly shot and display the data that most relevant to your needs. It's like having a magic wand, and that helps you focus on specific information within a sea of data. Okay? So here I have an Excel workbook with a simple dataset, and we are going to learn how to use basic filtering to see through this data. Okay? So first, you need to select or click on the range or in this dataset if you want to filter. So in our case, this or entire dataset now simply click and also you can easily drag down by this way for selecting and go to the data tab. And from there and you will find the sort and filter group. They are sort and filter group. Click over the filter button in there, and when I click over, you will notice that the dropdown arrows appears on the headers of your columns. These arrows are your filtering tools, Now click on the arrows for the columns you want to filter like here, let's say we want to filter the dataset to see only from the hair, the types and we want to only show up the camera and just select this one, then hit on the Okay. Now here we got it. The information is all about the cameras and they're okay and also you can select the multiple or you can categories with it by using the multiple filtering it. For example, if I take out this one and if I filter out only the four laptop and the projector that hit on the. Now I will filter this one as from the equipment details by selecting from here. If I say I will only use this 117 or 17 series, X ten laptops and hit on the Okay. Now, here we have the only this one. By this way, you can easily filter it. If we want to take out the filter option by clicking over the filter, then it will take out from the filters options from the dataset. You have just learned the basic of filtering in Excel. It's a powerful tools to help you quickly make or make sense of your data. Okay, thanks for watching this video, and I will see you in the next tutorial. 19. TRANSPOSE Function: Will be diving into the world of data manipulation as we explore how to transpose data in Microsoft Excel. Transposing data can be incredibly useful when you need to organize your spreadsheet. Whether you are a beginner or an experienced user, this video will help you master the art of transposing data in Excel. Let's get started. Before we dive into the practical steps, let's verify understanding of what transposing data means. When you transpose data in Excel, you essentially flips a row into columns or columns into rows. This is super helpful when you have data organized in the wrong orientation and need to change it for better analysis or reporting. Okay. The first step is, of course, to have some data to work with. In this example, I have some sales data organized by the product in row and quarters in the columns like the first quarter, second quarter, and third quarter, and the fourth. Now click and drag to select the data you want to transfer. In this case, I am selecting the range A one to E five in there, okay? Now, right click and use or select the copy from here, use the copy. Now select the cell where you want to paste the transposed data. So make sure it's in a blank area where it won't overlap with the original data. So I will select the cell like the G one, and I need two, or I select the right click. And when I use the right click, now you can find there, it says the first spatial, Okay. Right click on the cell, choose the pay special and the pay special dialog box from, check the transpose options and click on the ok. So there you have it. Your data is transferred when I press the O. So now they are built in function in Excel. That can make transposing even easier. So you can use this one by using the formula, and I will teach you in the later or the next tutorial. So that's it for today's tutorial on how to transpose the data from row to columns or columns to rows. This is the easy way and you can follow the steps. That is for today's video on how to transpose data in Excel. Thanks for watching this video, and I will see you in the next tutorial. 20. INDEX Function: Today's tutorial, we are going to dive into one of the most powerful function in Excel, the index functions. Whether you are a beginner or an Excel pro, understanding how to use index is crucial for managing and manipulating your data effectively. Now we start with try to understanding what is the index function. The index function in Excel allow you to retrieve data from a specific cell within a given range. It is a simple sentence like if I type here the index, then use here the s. The first argument is the array. This is the range or array of cell from which you want to extract data. Then the next one, the row number. Okay? This is the row number within the area from which you want to extract data. Then the last one, the column number. This is the optional argument representing the column number within our array if omitted. Excel, assume you want to extract data from the specific row only. Okay, now let's look at how to use the index function in extract data. So I will start with example. And here I use the sales person and with the year wise sales report in there. Now I will use the index function to find out. So writing here, the index in the first one is the array. Okay. Now let me select this area. Then the next argument, which row number you want to use. Okay, if I want to use the four row like here, you will start from the selected arms here, 123 and four this row. So I will write here the four. Next, we say the column number. So from which year you want to use, okay, if I want to use the three or the 2022, and this is the column three from the selected area from the one, two, and three. I am writing the three, then close it hit on the Okay. So according to our index function, we use the four like one, two, three, four, in here, and after that, we select the column three, so one or 23. For this, we got the result here 28, right? Now instead of this one, if I use this one like the five and instead of the three, if I say this will be the two, and here we got it, the 100 because we say we use the row number is the five here, one, two, three, four and five. And then next we say the two. So from here, one and two column in there. Now instead of the five, if I use here, the six, so what happened? Hit on the Okay. And here we got the error because we don't have the six row number in our selected area. We have only here one, two, three, four, and five row in there. So for this reason, it gives here the error message in our index functions, okay? And if you want to handle the error message, you can also use the different like the error message or the different one, okay? The index function becomes even more powerful. We can combine with other Excel function like the mass function or the sum. And you can also combine index and the match functions to find out or use your dataset for the various options, okay? So the index function in Excel is a versatile tools that can help you extract data from table, navigates through dataset, and build dynamic report. With a little practice, you will be able to harness it full potential. So thanks for watching this video, and I will see you in the next tutorial. 21. MATCH Formula: Hello, everyone, and welcome back. In today's, we are going to delve into one of the Excel powerful lookup function, the math functions. Whether you are Excel novice or a seasoned P, you are going to find this function incredibly useful for finding and manipulating data in your spreadsheet. Let's dive right in. First thing, first, let's start with the basic. The match function is an Excel function that allows you to search for a specific item in our range of cells. It then returns the relative position of that items within the range. This can be incredibly useful for taxes like creating dynamic formulas, looking up data, and more. Okay. Let's begin by understanding the sentence of the match functions. I'm going to just right here the match function. Then here first argument is the lookup value, the value you want to find. The next one or next argument is the lookup array. So the range of cell in one to search within. Then the next one is the match type. This is the optional. This parameter is specified the type of match you want. Exact match, less than or greater than. We will get into more details about this shortly. So I used here and I prepared a dataset with the cell and also the cell report with that 20, the here. Then I want to find out here, the salesperson with the index functions. Just I can say this will be the result. Then I use here the mass functions. Typing here the first one if I want to use here something. This one then use the lookup array. I want to use this array. Next one, I want to use the exact match. Right here, the zero and close, it hit on the Okay. Here, got it. The error because we didn't input any name in there. Let me use N, if I'm going to say this will be the Harlan caliber. I can say this is the example and hit on the O. Then again, I'm going to use this one, then make it this one and correct it on the ok. Now we got it. The positions of the according selection in here for O. So by this way, you can easily use it. But here, I used here the zero because I use this one the exact match now instead of the zero. If I use here like the match type, or I can say the negative one, or I use this one. So before that, I want to change here the different one. Okay? So I want to use here. Then again, I just say this will be the 47, or I can just say the five, this one, then use here the mass function, then use the lookup value, this one, then use the from here, the next one. So I want to use here the one, then close it, hit on the Okay. So here we got it. The result is we got the one in there. But I will do first here and use here the different way, like, make it descending the current selection. Or I can use the ascending this one. Use the continuous selection. And here we got this is one because we say that this is less than. And here we got the position in three. Okay. Now, instead of the 45, if I'm going to say this will be the 4,700 hit on the Okay. So here we got it therefore. So this is the way you can easily use. And if I use here, instead of for one, I can just the negative one. Here we got it the not available because we use here the descending order, or I can just to in here the selection, this one, and make it the current selection. And here we got it. So this is the ascending voice. Okay? So that is for today's tutorial on the mass functions, and you have learned to using the exact match and approximate match. 22. CHOOSE Function: So on this lesson, we will learn how we can use the choose function. So as you can see over here, we have example one and example two. So you have seen in many supermarkets that they use code to find out their product details. So similar way, we want to find their color through a simple code over here. So let's see how we can do that. For doing that, we will be using this choose formula C or choose formula. Let me click over here and we have to place the index. The index would be our code. So let's go and see. On our function argument, I have click on this function and we have able to bring this box. The index number would be the code and value would be color red, and the second value would be green. Third value would be orange and fourth value would be yellow, and then we will hit Okay button. And after then we have selected one. It came as red. Let's do it similarly over here. Let's go and copy it. And as we will place the value over here, we will able to see. As we will place the value for mango as green, orange for code three, and for port four will be yellow for banana. Easily we can see that we have able to find the through this code, we have able to bring our the value of that color. So now let's see about the name. So over here, let's go and select choose. Let me select the choose option. Then let's go over here. For this index number, you know what to do. Go to the point and the value one, and value two, value three, and value four, and hit Okay. You will see there are no value because we haven't bring out the point for Alvason. I want to value him as two, let's insert and his rating is good. Now let's give value to David William of four, Florence and Melissa. Over here, I want this is the grading system that I have created just now. It is very simple task. Now I want to give a value of David as. Let's give the David as a value as excellent. Let's give this point as one. She has worked very bad and for post will be used as three. We are giving post as three point. Let's give David point. For David, I want to give him around four point, his rating would be excellent for Florence here. I want to give her one point and her rating is very poor on for post. We want to give three and you can see very easily. We are able to change the rating through this point, and through this code, we can change the color. So this is how you can use Choose function for your Excel spreadsheet. So hope you have understood. 23. AVERAGE, AVERAGEA, AVERAGEIF & AVERAGEIFS Function: Hello there. In today's Excel tutorial, we are going to dive into one of the fundamentals of Excel, the average function. Whether you are a beginner or an Excel P, understanding how to use the average function is essential for data analysis and calculation. Let's get started. First, let's understand what the average function does is calculate the arithmetic means of a range of numbers. In other words, it helps you to find the average value of a set of data point. The sentence of the average function is quite simple. It is just use the average and select the range and like the range, represent the cells or numbers where you want to calculate the average of. Okay, let's start with the example. Here I use the total sales in there, and this is the sales report. Then I want to use it was all other range. This range for use the average. So you share the average function, this one. Then I'm going to just select the range and close it with the parenthesis, then hit on the Okay and here you got it. So this is the average of those numbers. Okay. So by selecting the range, you can easily get the average of the selected range in your Excel spreadsheet. Okay. So there you have it. You now know how to use the average function in Excel to quickly calculate the average of a set of numbers. It's a handy tools for data analysis and reporting. Okay, thanks for watching this video, and I will see you in the next tutorial. 24. Exact formula: Today's tutorial, we are going to dive into one of the Excel powerful and laser exit function exits. This function is incredibly handy when you need to compare two text staring to see if they are identical character by characters. Whether you are a beginner or an Excel Pro, this tutorial will help you understanding and utilize the extract exit function in its full potential. What is the exact function? Simply put, it's used to compare two text settings and determine if they are exactly the same characters by characters. It's particularly useful in scenario where decisions is crucial. So now let's dive into the details. And before that, let's break down the sentence of the exits. So the simple one. So it has the two or few arguments like the text one and also the text two, or there is a loss. So the first text in string you want to compare and also the second text string you want to compare. Okay? So I will show you how to use this argument in Excel and discuss a specific requirement or constants. So in the result, I use here the exit, this one, then you see the text one, so the select one, then use this one, then close it. Okay, so here we got it false because this is not the exit or the similar data, one data, two, because we used here, the M is lowercase. If I use this one instead of lowercase and use the upper case, hit on the Okay. Here we got it. This is the true. Now, if I want to apply the exact match, the rest of the data, so I just drag it down and here we got it. But here you can see it is the 100%, and it has also the 100%. But here we got the result, false. Why? Because when I take my cursor and click over this cell, now you can see it has the hundred percent. But when I move to the data too, and here you can see 99.9%. But in our shores or the roundup is count up the hundred. But this is not exactly or similar with the data. So for this, it gives here the false. So there you have it. We have covered the exit function in Excel from start to finish. This function is invaluable for precise text comprehension and is a powerful tool in your Excel tool skills. So thanks for watching this video, and I will see you in the next tutorial. 25. VLOOKUP with Exact Match: So on this lesson, we will learn about Lookup with Excel Match. So as you can see over here, we have data, which are product name, product code, and stock and price. So I want to get a result from here. I want to get all the result which is related to this data. So when I search for speaker, it should give me the product code name, whether the stock is there or not and the price. So with a very easy way, we can get that through Lookup with Excel. So let's go over here and we want to create a table. So for doing that, we will go over here and click Data Validation. And after data validation, we will list it and then the source that we want. So I want this as a source, and then I will hit Okay. And very easily, you can see that we are able to get the product name or product list, but these are product code. We want to find product code as well. So over here, let's go and place the formula, find out product code. And let's go over here, and we look up. Let's click over here. And then we have to put Lou value. So our value is I five, so I and under five because we want to look the value of the mouse, right. So after that, we will write visit V list. And this is the visiting list that it will look from, and the table array and the code index number would be one, and this is two. So we want to find a product code, so it will be two and then comma and we want to see exact match. All right. So let's close the bracket and let's hit Enter, and you can see that we are able to find the code number product code number as five. So let's go and change it. Let's go to table. And you can see it is coming eight, right. So now we want to find the stock that we have, whether the table stock or not. So same formula is equal to V lookup and then lookup value would be table. Then the table area, which is V list, then we want to find the index number, which is three, and we want that exact match, so zero. And you can see that our table have stock. So let's go to router, whether it have stock or not. Let's see. Let's go on top Router. You can see that it does not have any stock on them. So let's try the price now. So same formula. Again, we loop. Let's go over here and see a little bit nicer. So over here we have a lookup value. The lookup value is router. The table area that we have is Vlist, right? So VList is equal to product name and product code. And so on that after the column index number. So number will be 41, two, three, four is four, and the range lookup is zero because we want to falls, right? So you can see very easily we have able to get the price over here. So the price is 14 50. So let's go find out for laptop. Let's go and see about not speaker. Let's go and find for laptop, and you can see $33,000. So through this way, we can very easily find out the result of our we look for exact match. So hope you have understood the lesson. 26. VLOOKUP with Trim Function: So on this lesson, we will learn about up with trim. So why do you need trim? So as we can see here, we have trim with first travel and trim with second travel. So over here, let's go and see our first table. You can see that we have a space on every cell that we have right. And over here, as you can see, we don't have any space. So this table is consists of space. And if you use our formula, let's just use the formula, the original Lou formula. Let's go and write Lou a 20, and then F two key on the 44 making this table fixed. So after that, we will use comma, and we want to find the price of this table that is value for index number four, and we want the exact match. And then we will close the bracket, and you can see we are able to see this. So we are able to see the price. So let's go and copy this and let's see whether it's work on this table as well. Because on this table, we have space on the second portion table. With second table, we have space. But over here, we don't. You can see the cursor over here. So let's go and try whether it works or not. So over here, we have to change it to Ace 20 and this fixed one, two over here. And then for fixing the table, then comma the value that we want to find is price. That means 12 34 index four, and then zero, right? Let's hit Enter. And you can see very easily we are able to find the price of every table, right? So with these two formula, I have already given the formula with the product name you need to change them. So with these two formula, we can very easily trim any Excel sheet that you have, and it will be very handy for you when you are working on your Excel sheet. 27. VLOOKUP Approximate Match: On this lesson, we will learn about the loop approximate match. So as you know, we look up is vertical lookup system where the table is done vertically. So over here, you can see that we have an incentive rate over here and all the sales item and the incentive every person will get. So this is a formula where if somebody earn 491099, they will get no incentive. But if there are from 50, 99, 99 tents, they will get $1,000 incentive. This is the incentive and the approximate match option. For doing that, let's go incentive. Let's place our formula for doing that. We will give o and the lookup value. So the lookup value is the sales value that this person have. Then we need to find the travel area. So the table area is this approximate number or incentive rate that we have. Let's select it and press four. And then we have to find incentive. So for doing that, this is the index number, which would be 123 incentive. So let's press three comma. We want approximate match. Let's click over here and let's close the bracket, hit Enter. And you can see that Watson have earned one lag 56,000. Right? So he have earned OLC 56,000, where he gets the incentive of $3,000. So let's autofill them, and very easily you can find that out. The approximate number have been displayed over here. So when you have incentive system very easily through Lookup system, you can find their incentive and use this as a theory. So hope you have understood. So thanks for watching this video, and I will see you in the next tutorial. 28. HLOOKUP with MATCH Function: So on this lesson, we will learn how we can age Loup with match function in Excel. So age Loup are the horizontal lookup system through which we will learn how we can match function in Excel. So over here, you can see that the student name on top and the subject on your left hand side. So I want to find Kim's chemistry mark or any of the English mark, how much he got on this box. So how you can do that. We will write A is equal to age lookup and then look up value, which is Kim, then we will select the table, and then give a comma and then we will write match. Functions. Let's click it over here, and then we want to find the English of Kim. Then we will find the box over here. Let's select them all and we want to give a comma. We want the exact match over here. And then we will close the match function sub bracket and then give a comma, and we want exact match. And we will hit Enter, and you can see that Kim got 70 on English. So if I change this Kim from Sonny, you can see that he have got 75%. So let's see how this match works. So let's write match, and then we'll use the bracket and lookup value which will be. English, and then we want to select the subject, and then we will have exact match. So we are finding out which of the subject will have which position. So let's close the bracket. Hit Enter, and you can see that English on the second position. So let's change it to history. And you can see that history position on four, and the marks of Sonny became 68. So this is how you can use Ace lookup with mass function in your Excel. 29. Your Project : Hello, everyone, and congratulations on reaching the end of this course. You've now learned some powerful Excel functions that will make your work much more efficient and organized. We started with the basics like understanding, relative and absolute references. Then we dove into logical functions like the IF function and even nested IFs for more complex conditions. We explored advanced formulas like IFS and how to handle errors with the IFERor function. Plus, we learned how to fix cell references and add leading zeros in Excel. Now that you have a solid understanding of these essential Excel functions, it's time for you to apply what you've learned. For your final project, I want you to create a comprehensive Excel sheet using these functions. It should include logical conditions, error handling and cell references. Remember, the best way to master these skills is through practice. Take your time to experiment and explore new ways to use these functions and don't forget you have a community of learners here to help you if needed. Thank you so much for joining me in this course. I'm excited to see the amazing projects you'll create. Good luck and happy excelling.