Learn Excel for Data Analyst Jobs 2026 | Complete Guide for Beginners | Intellipaat

Key Takeaways

Microsoft Excel is one of the most important and powerful tools when it comes to data analysis. Almost every company whether it is finance, marketing, sales, operations or HR uses Excel to work with data. And in this complete and detailed course we are going to learn Excel from absolute scratch and step by step we will move all the way to advanced topics like formulas, important Excel functions, pivot tables, dashboards and even query. By the end of this course, you will be able to build a professional portfolio dashboard where you can analyze data, for example, a call center data set and visualize the results in a clean, interactive and beautiful way. We will also learn how to automate data cleaning and transformation using Power Query, which is one of the most powerful features of Excel. Now, let's begin with the basics. When you open Excel for the first time, it may look simple, but it is extremely powerful. The screen is divided into three main areas. At the top you have the ribbon. This is where all the tools and commands are available. In the center you have this large grid area. This grid has rows and columns. Rows are the horizontal lines with numbers like 1 2 3 4. Columns are the vertical lines with letters like A B C D. Where a row and a column meet, that is called a cell. A cell is where you enter your data. You can type numbers, text, dates, formulas or even insert images. And this entire grid is organized into sheets. At the bottom, you can see sheet tabs. You can add multiple sheets, rename them, and organize your work properly. This is how Excel stores and manages data. But this is just the beginning. In this course, we will go far beyond just typing data. You will work on real data sets. You will perform real analysis. And you will build projects that you can confidently add to your portfolio. So without wasting any time, let's get started. >> Okay. So uh let's start with the Excel. Okay. So guys before I start with the topics I just wanted to ensure that Excel is pretty much required in the analytics industry. Okay. If you take BI or analytics whatever you name it Excel is much required in the real time. If you want to learn any visualization tools like uh Tableau, PowerBI, Clicksense, Excel is must. Okay. whatever you learn it here 30% of the uh Excel subject will be there in both uh Tableau and PowerBI I'm just giving a heads up as you're starting with the Excel uh this is uh pretty much uh uh the starting step for your analytics journal okay so uh and also even if you learn SQL uh where also you can use your Excel knowledge okay so just a quick introduction to the MS office versions you all know that right Microsoft Excel is a part of uh Microsoft office. So the first version of MS Office was released in 1995 then 1996 and then uh from 99 it has gone through the several updates. Okay, Microsoft generally release updates on every yearly basis so that we get a newer version. So right now we have I think office 20 office 365 that is the latest version. Okay. So you can have uh let's say for example uh what version you're supposed to have it in your system. So any version above 2016 is okay but below that I recommend you to upgrade your version. Don't buy uh any office premium suit. Just you know go to YouTube and search you will find uh some cracked versions. If you have 2013 also fine 2016 also fine but uh anything above 2016 is uh you know pretty much recommended. Okay. Because there are couple of functions which will be there in the newer versions. I would like to touch upon those functions as well. All right. So moving on. What is Excel? Basically Excel you can think of it like a spreadsheet. Okay. Which is a sheet developed by Microsoft. So in Excel what do you have? You will have rows and columns. Okay. Let me open Excel application. So you can press a Windows button and start typing Excel. Okay. You you see see this Excel. If you're using older like you know Windows any any version before Windows 10, you will have something like all programs. Go to all programs you will find your uh you know Microsoft office inside the office you will see something like uh you know excel okay these are the uh steps to open excel so right now which is there on my desktop so I can straight away open this application okay now see I have office 365 once I open the application okay I don't want to talk about theoretical concept as much as possible I'll try to show practical things okay so this This is called the backstage area guys. Uh okay, if I press escape, this is the basic Excel screen. Okay, how do you go to the backstage area? Basically, backstage area means which is there uh behind the Excel home application. If I go to file, this is the backstage area. Okay, here you can insert a new workbook, open a new workbook, you can look at the information, save, save as, uh okay, save as PDF, print, share, you can close, you can do all these options. So you can open a new blank workbook. You can open existing trackers. Okay, this I'll discuss in a detail. Any open any recently opened workbooks will be shown up here. I want a new Excel workbook. So click on this, double click on this so that a new workbook will be opened. Okay. So generally here as I mentioned in uh previously the data is going to store in the form of rows and columns. Anything that you see 1 2 3 4 5 are rows. Okay, rows are always shown one underneath another. Columns will always shown one beside another. So these alphabets represents columns. These rows represents rows. So that's what I mentioned in my PDF. Okay, Excel organizes the data in rows and columns that allow you to do mathematical operations. You can do calculations. Okay, you can do formatting based on the conditions. You can do anything. And yeah, it works on both Windows and Mac OS and Android and iOS. Okay. Uh as I mentioned previously, the first version of Excel was released in uh 1995. Then after it went through lot of changes and therefore we have got a newer versions of Excel. Now why Excel is used? So you can use Excel for many task. Let's say you have a huge data. You want to analyze that. For example, I'll open any one sheet where I have a lot of information. I'm just going to the file. I'll try to open any file where I have a lot of information. Okay. I have a file called sample supertore. So basically this is an online stores information. Okay. Here you see uh we all go to supermarkets and buy different products right where they scan the barcode and uh store the record. So same information. Okay. I have different orders. Okay. When it is shipped, who is the customer, where did he buy the item? That is geographical details, product information. Okay. And then uh I have sales quantity, all of that. For example, how many rows are there? If I hold control and down, I'll go to the last row of the data set. So there are around 10,194 records are there. Okay. Now out of this data, I wanted to see I wanted to analyze how many uh regions are there. If I go to the column region and uh you know if I just explore this data, I have four regions of data, right? So how did I check this? If you just open this dropdown, you can see I wanted to analyze only central data. Just select center and click on okay. If I go to the sales column and select this column, I can see the total sales. So here I'm doing analysis. Okay. I'm analyzing the data looking at this data. So Excel is used for that analysis purpose. You can also use Excel for data entry. What do I mean by that? For example, I'll go to new Excel blank screen. Okay. I wanted to enter uh you know a student uh information here. Okay. I'll start typing it. Okay. S N O student number okay and then I'll type something like a student name okay marks see I'm just typing it and come to the cell where you wanted to enter the actual information okay serial number okay done now I'll say 1 or 1012 you can start typing it the way you type it in notepad the way you type it in phone same thing here I'll type naga santo okay this way I would like to enter some marks 50 60 35 okay like this for example at any point of time if you wanted to change this word let's say I want to replace Nagasandra with another another name start typing uh the new word like this okay if you wanted to modify only this Kumar from Ranjit Kumar so here you can say double click on it okay or press F2 it will go to the edit mode you can just select the word that you wanted to replace with uh Ranjit uh Na Okay, this way I can also type it. So, uh anytime if you wanted to enter the data, you can also use it. Data management, let's say this is one data set. I wanted to hold another data set, I can have it over here. Okay, as simple as that. So, for data management purpose also you can do. You can also even use it for visualization purpose. Okay, in the real world, we don't present the raw data as it is. We create private tables, we create charts, we do conditional formatting. Okay, we'll filter the data. We'll sort the data. Lot of data management techniques we are going to perform and then present it. So right now I want don't want to present it like a uh simple text. I would like to show it as a chart. So I'll go ahead and insert a chart. See now HL is also used to create charts. Again we all will be doing this in practical. I'm just showing you why Excel is used. Excel can also be used for programming. Let's say every day I'm doing a task manually which requires 10 different steps. You can also write VBA macro coding to automate the task. Again in Excel there is a programming called macros. Using macros any Excel manual task can be automated and yeah you can do many other things using Excel. Okay. So why do why do I need to use Excel? Uh Excel can contain uh you know uh more than 1 million records. Okay. And it is easy to work. You can integrate Excel with SQL okay or any other uh databases okay you can easily create a visuals you can easily create a private tables in the real world you any any technology you work okay uh when I say any technology if you're part of if you're trying to be part of analytics domain right any technology you use let it be excel ms access server tableau powerpoint clicksense end of the day at least 10 20% of your work will be on Excel only. Okay, there are some reports definitely in every organization which can be prepared uh using Excel only. So you can't avoid Excel. The more Excel you learn, okay, the more other tools, the more easy other tools are going to be. Okay, please keep this in mind. Okay, now we will try to understand the Excel interface. Okay, when you open any Excel application, okay, let me close this off. Okay, when you open any Excel application, right? So uh the first thing that you see is a menu bar. Okay, what it is? This is a menu bar where you will have each different menu options. Okay, why am I calling menu bar? When you go to any restaurants when you collect a menu from them, okay, first they'll have a beverages and they'll have so like you know starters, veg, nonve okay and curries uh and ice creams all them are segregated to different divisions, right? So here also Excel has a menu option where each of these options are going to provide you different sub options. If you take home as an example under the home you will have formatting options. Okay. So each option provides a different sub options. If you take home generally under home we'll have the you know formatting options. But for example if you see here all these are like a self-explanatory. Okay. Font size. Okay. Font style, background color. This is background color, guys. Whenever you see this, you know, a symbol which is a background color. If you just mouse over also, you can see what it is. See font color. These are like a borders. Okay. Bold, itallic, all of that. This is all about the alignment, left alignment, right alignment, center alignment, all those things. Okay. Here you have number formatting and you'll have also have conditional formatting, inserting rows, deleting rows, formatting rows and columns. Okay. See here for row height formatting rows and columns is nothing but like adjusting the row height, adjusting the column width, okay, renaming the sheet name, moving the sheet, hiding the sheet, all of that. Here you can copy the data down. Okay, copy the data to the left. Okay, uh all such options. Sorting and filter we are not going to discuss discuss it over here. Okay, because sorting and filtering is there in a great detail under the data tab. Okay, and then what else? We have this you know finding these these are the things we are going to top and if you go to the insert under the insert you are going to have you know tables private tables different charts okay and also hyperlinks okay some slices you can insert it page layout is typically useful whenever you wanted to print the page setup under formulas this is a very important section where I'm going to spend most of the time so I'm going to talk about these two types of functions okay so Here you will have a logical functions, text functions, date functions, reference functions, I mean lookup and reference functions, mathematical functions under data. Okay, this is very important. Uh this is called Power Query. Okay, if any uh if any of you are aware of PowerBI, okay, this you would have already heard this word called Power Query. So Power Query is used for ETL activities. Power query is equal to ETL. That's it. Okay, I'll discuss about this and you can also learn about sort filter okay data validation text to columns under the review basically we use this protecting sheet all of those things and then view tab is used to print uh okay split the view to the multiple views and you can uncheck the grid lines okay you can also use a free spins all of that developer tab is maybe you can't see this on your screen I have enabled this this is used to write the VBA macro programming okay all right this is pretty much about the home uh you know menu bar file is like a backstage area. Next just to above this you have something called quick access toolbar. What it is? Uh this is called quick access toolbar. Okay. Uh quick access toolbar. Okay. What is this quick access toolbar? Any option that you are using very frequently you can add them to this screen. Okay. Something like a bookmarks. Let's say if you see here uh every time I wanted to use uh something called this text to columns. Okay, I'll right click on this. You see uh add to pickaxis toolbar. Notice what is going to happen over here. Okay, I'll simply say add to quick pick access toolbar. See I got this quick access toolbar over here. Okay, as simple as that. If you wanted to remove right click and remove from the quick access toolbar. So any option if you are using very frequently you can have them it over here. That is what the quick access toolbar and here you have a formula bar for example here you see 20 and 30 I have now I wanted to do the summation so I'll click on 20 here you notice you are getting the cell reference okay plus this cell so whenever you select a cells right you don't see the actual value inside of the cell there instead it is going to show you the reference of that cell okay if I hit enter I'll get the result so whatever the formulas that you write you can see the formulas under the formula bar okay you can write it on the formula bar. That is the one thing. Next one, this is a name box. Whenever you select any cell, okay, this name box is going to show you the cell address. Remember, cell address is a combination of row and column. Okay, cell address is a combination of row plus column. Okay, this is the cell address. For example, if I select all of the cells, this is going to show you the active cell. Active cell means the cell which is showing which is in the white color. Now here if you see I have selected from D4 to okay D14 this is called range okay what do we call this is called range okay clear so this is a cell address okay that you have to remember so whatever you see it in the alphabets are called columns whatever you see it in the uh uh numbers it is called rows okay at the bottom by default you will have only one sheet but you can click on this plus sign to add as many sheets as you want on the right hand side you have a zoom bar where you can zoom and you can also put the screen into multiple uh uh views. Okay. But yeah just keep it into its original. Now okay uh we understood what is row and column right? So how many rows will be there in Excel? This is the general question. Okay, how many rows and uh columns in Excel. So this is a general question guys. Okay, rows are like you know you will be having 10 lakh 48,576 in another words it's it's a two power 20 okay columns you will have something like uh you know 2^ 14 okay you will have something like 2^ 14 16 oops hang two power 14 see 16,000 uh 380 uh 384 okay these are you can remember it like a two power 14 okay so these are number of rows columns if I if you go to any cell and press control down here you can see 10 lakh 48,576 but when it comes to the column the last column is going to be XFD so you'll have to remember this okay in general uh these are the equations that you'll be coming across understood so okay now let's uh let's take some data and try playing with the formatting okay we'll try to uh check a home under home we have a different formatting options now so let's check that okay I'll be taking a sample data set uh just to uh walk you through this uh let me take uh employ okay here I have this data I'm just pasting this data over here okay let me remove some unnecessary columns okay all right let's assume this is our data set guys this you need to format it using all the formatting options which are there under the home tab okay see now here we have different font quant techniques right so let's understand how are we going to work with this so let's select the data okay I for the headers how did I do this selection select from left to right okay you select one cell use your mouse to select this or if you wanted to use keyboard shortcut hold shift key and move on if you want to select from first to last to hold control shift and press right arrow so that it selects the amount of data so I would like to provide a background color background color is nothing but a four color Okay, if you mouse over here, you can see fill color. So go to this and you can provide wide variety of colors. I'll just choose this color. Okay, done. Now that is done. Font color, I wanted to give maybe dark shade of a blue. You can provide that as well. Okay, next you want to make it bold, you can absolutely make it bold and even you can italicize it. Okay, whenever you apply these options, you can see these buttons are turned on. Okay, next here you have a font style. Okay, at any point of time if you wanted to change this, you can absolutely change this. See, I have modified it. Okay, so whatever the form that you want. Okay, whatever makes sense to you, you can always go ahead with that. So if you wanted to increase the column width, you just to put your cursor in between these two columns any anywhere and you know you can just drag and drop. See, you can drag right and drag left. The moment you're dragging, you see column width is getting increased. Okay, that way if you wanted a row height to be increased, put your cursor in between over here. You can increase the row height as well. Okay, makes sense, right? So if if you wanted to increase the font size, you click on this capital A, okay, with upper cap something like this and this is a lower cap. This will decrease the height of the column, height of the Excel value, okay, that is a one thing. So all this can be done from here. Okay, if you want to change the color of it, you can change it. Next thing, uh, alignment. Okay, right now all of the text is aligned on the left side, right? So, this is the left alignment. See, toggle is turned on. Center alignment. You see the word is sitting in the center of the cell. Right alignment. I'll make it center. Then how about I put this? This is like you know this is left, right, middle. This is top, center, and bottom. You see when I click on this, you can see center at the bottom. If you wanted to see this change, you must increase the row height. Then only you can see it very clearly. Okay, this is all about this. Next here I would like to have one uh header for all of these cells. Let's say if I say this something like you know sales report, okay, 2025. See this header is sitting on the left left corner of my entire data set. Therefore, it is not looking good. Right? I would like to have this okay center align somewhere over here. To do that first I'm deleting this text. I will just merge all these cells. Merging is nothing but what? Taking multiple cells and making it as one cell. To do that you can use this option here. Okay. You see merge and center. When I say merge and center now see this entire cell has become one cell. Do you notice? Now here I'll write something like a sales data analysis. Some some adder like you know sales data analysis 2025 to 2026. Done. Now I wanted to make it bold. You can make it bold. You can also provide a same color or maybe let's have a different uh color. And I can also make it something like a white. Okay. Like this. Maybe I'll just make it red color only. See this way you can do it. Okay. Now how about these two options? Okay. These are like indents. Right now my header uh my uh this header is sitting on the center. I don't want either on the left side or right side or center somewhere here I want. So what you can do you put align it on the left side. Now you see when I click on this see it is moving one one tab. See each time I click on it it will keep on moving wherever you want you just leave it there. So these are called indents. Okay. Left indent and right indent decrease indent and increase indent. Okay. Now let's talk about merging options. Okay. Let's talk about merging options. Whenever you are merging, if you can merge two empty cells, okay? You can merge two or more empty cells. Okay? But if you have text in two cells, you should be careful. Okay? Uh merge across. Okay? Merge across meaning that it will have text on the left side. So these are the different options we have. I'll explain. See, I I'll just select these two cells and provide a background color. See right now these are these are two individual cells. I'm pressing tap to show you. See this is F D34. If I move it here is it is E sorry D43 E43. Now I'll select these two cells. I'll say merge and center. When I'm merging it, this has become one cell. I'll type something like Micro Soft Excel 365. But what if I have something like this? Okay, Microsoft in one cell and in another cell. If I merged these two cells, you see, but if you have text in two cells, you should be careful. Why? Because when I merge the cell, two cells will have the content. See, merging cells will only keep the upper left side value and discard the other values, which is going to remove the right side values. Okay, we should remember that. Now, see, I'll say merge and center. Click on okay. See, it's going to keep only upper left side value. So when you're merging the data within different cells, you should be careful. Okay. What is merge across? If I say merge across, see both the cells will be merged. But whatever I type, the data is going to be aligned on the left hand side. It starts across. Okay? Same way merge cells. If you select two cells and say merge cells again, it will do the merging. Okay? But if again if you type, it's always going to be on the left side only. So merge cells merge across always gives the data from the left side and merge center will only center align the data. Okay, you can just mouse over here and read the uh text. Unmerge cells means it will simply unmerge the cells. See unmerge the cells and it's going to keep the text in the left side value. Not only two cells, you can merge you know as many cells as you want. Okay. Now I'll say hey welcome to Excel module. Hope all of you are doing good. See this way I can merge it. Again for me is a single cell. I can put it in the center. I can provide a background color. I can change the font color. I can also change the font size. Increase it. Whatever you want, you can do it. Next, I'm not going to talk about these styles. Okay. I would like to talk about something called Okay. Uh let me copy this data and show you. Okay. I would like to talk about something called format tab. Okay. So see now the data pasted it here. No you see uh this employee is not properly visible. Okay. In that case what you can do? You can adjust the column width like this. But if you want all the columns to be auto adjusted. Select the entire data. How do you select the data? Okay. Select the data set. You can use shortcuttrl A. Okay. Select the entire sheet. Okay. Press Ctrl A twice. See I'm pressing Ctrl A and Ctrl A it selects the only data set. If I do it again you see it's going to select the entire data. Now just double click on the first column. It automatically adjust all the columns. Okay done. Now here if you see you have something called insert. What is insert? You can insert columns sorry insert rows columns sheet and insert cells. Now let's say I wanted to insert a column in between C and D. Okay. So, select a D column, right click and say insert. See, another column is inserted. I'm going to say uh something like uh okay, department. I can start typing. Let's say HR is one department, marketing is another department, sales is another department, administration is another department. I'll select this data and I'll simply double click over here to send this data down. Okay. See, I have inserted one column. If I wanted to insert a row, see if you want to enter the data anywhere over here, you don't need to insert because all are empty cells, you can start typing it. But let's say if you wanted to insert one more row over here, select this data, right click and say insert. Okay, that's it. Select the select this column. You have to click anywhere over here. Okay. Anywhere over here. Okay. Now once you select it, use control plus. Okay. Control + uh insert. Okay. Control minus to delete. Control minus to delete. See, I'm selecting this data.trl minus. Control + Okay. Next, if let's say I'm deleting this, how do you get it back? Ctrl Z. Ctrl C is undo. Okay. Ctrl C is what? Ctrl Z is undo. Control Y redo. Okay. See, I'm just deleting this. Ctrl Z. Control Y. Okay. Here you see here also you have options. Okay. Ctrl Z to redo. I'm sorry. Ctrl Z to uh redo. And this is undo. Okay. See here this option undo and redo. Okay. So these are the two options that you can use. And same thing can also be done from here also. See you can right now I have selected the column. Now therefore rows is disabled. Okay. If you you can also insert sheet columns. Okay. Again you can select this column you can insert it. But yeah instead of that you select it use shortcuts you can easily insert it. Same way delete. If I say delete cells, see that column is deleted. Again, you can also delete sheet columns. If you wanted to delete this sheet, let's say here I wanted to delete this sheet. So I can go here and say delete sheet. It will ask you Microsoft is going to delete the sheet permanently. Do you want to continue? Yes. Okay. Or else what you can do, you can simply right click over here and simply uh from here also you can delete it. Okay. But remember here right now I'm just right clicking and deleting. The sheet got deleted. Why? Why it did not give me the prompt? Because there is nothing on the screen. No, no data on this sheet. If I have any data, it will give you a warning. See, it will give you a warning. But if there is no data, it it will straight away delete this. Okay, this way you can delete it. Okay, this is about uh you know inserting you know sheet, row, sheet, columns, sheet. If you say insert sheet, you can also insert a sheet. Or else you can simply click this plus sign to insert the sheet. Okay. Same way you have another tab called format. Okay. So here you see row height. For example, I'm just selecting this first row. Okay. If I say row height, you see that here the row height is 14. You you can also increase it. If I say 21, you see the row height has been increased. This is one way to increase the row height. Or else what you can do? Simply place your cursor here. Okay. In between one and two and drag down. You see the row height is getting increased. Right now the row height is 30. This way also you can increase it. Okay that is about row height. Auto fit row height. Let's say I have increased the row height. Now I want to go back to its original height. So simply select the row. Okay. And you can say see automatically it has adjusted the row height to its default height that is 14.4. Okay. Next column width. Right now this column width is how much? Let's check that. Okay. column width. Column width is showing 8.89. If you wanted to increase, one way is to use the mouse. Okay. If you wanted to auto fit it, auto fit column width. It will automatically adjust it. Okay. If you wanted to check the default width, you can also check. If you wanted to increase at any time, you can increase it. Okay. Column width, I'll make it 21. I want it to again go with the default width. Okay. Uh 8, I'll make it. Or else you double click over here. It will automatically adjust it. Okay. If you wanted to increase the column width for all of this, select all the columns. Go here and say column width. I'm going to say 25. See, all the column widths have been increased. Okay? It's up to you. Okay? I hope all these options are clear how to adjust the whole sheet. Right? So, you select the data, put it in a new sheet, select control A once. Okay? If you if you are outside of the sheet, press control A. All the data will be selected sheet. If you are in the data, press control A twice and you just drag like this. Okay? Whatever you column by 10 width pixel, right? All the columns will have 10 width pixel. You see, you go to this column and check a column width. You're going to see 10 pixel. Okay. This way you can do it. Okay. So, next one is all about hide andhide sheet. Let's say this sheet is for my uh you know personal analysis. Okay. I'll say this is personal analysis. I don't want this sheet to be visible to the audience. So I can hide this. So you simply right mouse click here also you can insert the sheet. Whenever yeah uh whenever you insert click on insert this insert window will come. You can select the sheet and click on okay. A sheet will get added. Right mouse click and delete it. If you wanted to rename the sheet you can right mouse click and rename or simply double click and rename. I would like to provide a color to this. I'll say uh red color. Okay. Sometimes you know we color the sheets for our analysis. You can even do that. Right mouse click and say hide. Now that sheet is hidden. How do you get that sheet back? Again right mouse click on any of the sheet and say unhide. You can see all the hidden sheets over here. Let's say I'm just going to hide this sheet as well. Okay. Now if I'll right mouse click and say unhide. Okay. I wanted to unhide this sheet. Click on this. Now you'll get this sheet. Not only from there you can even go here and say you can hide the rows, hide columns, hide sheet. I'm going to say hide this sheet. Again, if you wanted to get it back, go here and say unhide sheet. It will show all the sheets. Simply click on okay. Now let's say for some reason you wanted to hide these three columns. Select the columns. Okay. Select the columns. Right mouse click on any of the column and say hide. If you wanted to unhide them, select one column before this and one column after this. How do I know that this is one column before this, one column after this? If you see here, you see two lines. Now, so whenever you see these two lines, select the two lines including one column before, one column after, right mouse click and say unhide. Those columns are going to be unhidden. Okay? Again, hide them. Or you can go here and say hide columns. See columns are hidden. Select the column. Okay. You simply double click, you will get it back. Simply double click, you will get it back. Double click, you will get it back. Not only here, you can even hide from here. Select one column above, one column below. Wherever you see these two lines, no this this lines. Okay? Just double click over there, you will get it back. Okay? Or else you just go here and unhide rows. Rows will be hidden. Okay? That way you can do all the formatting things. Renaming sheet can also be done from here. Okay? And the tab color also can be done from here. It's all up to you. Okay. Last option, move or sheet. Move or copy. Let's say I want a duplicate copy of this. Hold control key, drag and drop. Another copy that gets added. Right mouse click and delete. If you want to move, just hold key, hold this and move wherever you want. All those options are available over here. Protecting. I'm not going to discuss this now. Uh except this arrest all options. I would assume it is clear. Okay. Now uh we will discuss something called autofill. Okay. See here I have uh something called uh you know one here. When I drag this down what will happen? I have one here. When I when I drag this uh when I drag this down like this what will happen? We are going to get series of values. See whenever you select any cell if you are in the cell you see this plus symbol. Okay. But whenever you are on the corner you see this darker plus symbol which means that you can drag this word down. Whenever I drag this word I'll get the same value. Okay. But if I go to this autofill option see it is saying that I just copied the cell as it is. However you can fill the series. So how many values I have selected? I have selected nine cells. Okay. From here I have nine cells. I'm just looking at the count and it's saying that if I just to go with this and fill series, it going to increase uh it it by one and I'll get nine. Okay, if you want automatically do it add one two values at least to provide two values and drag this down. Now it will understand that there is a one difference between the two values and it prints. For example, if I type 1 three here now see I'll just select both the cells and drag this down. you will get by it is it is going to get increased by one value. Okay. Not only that, here if you have something like a marks one, as long as there is a number at the end, if I drag this down, I'll get a series of values. Okay. This is autofill option. For example, here I'm just adding today's date. I'll use the today function. Okay, I'll get the today's date. Now, if I send this down, see, I'm going to get uh the same date. But if I go here, okay, maybe see I got uh let me delete all of this again. Let me drag this down. Okay, see I'm getting it. I'm I'm going to get it increased by one. Okay, this is fill series. If if there is only formatting, you can also fill the formatting. Right now, this is filled days. 6 7 8 9 10 we have got it in sequential. If you want only weekdays, right? You see only weekday. See 13 14 are weekends. If you just look at the calendar, 13 is a Saturday and 14 is a Sunday. So those are remote. So you can fill week days, you can fill the month. September, October, November, all of that. Okay, you can even fill only years up to you. Okay, you you can even do that. This is autofill option. Okay, now here I have one, right? If I hold control key and drag this down. See, I don't have to again go with the autofill. Holding control key will give you the sequence. Holding control key will give you the sequence of values. Make sense? Okay. Now we will see copying. Copy in down, left, right. Okay, for example, here I have something like apple. I want this word to be copied to the rest of the cells. What I supposed to do? So you can one option is you can drag and drop like this. Okay. Uh not apple. Let me take a new name. Okay. I have this new word. If I drag this down, you can copy the word. Okay. On on the bottom. If you want this to be uh pasted towards the right side, select the word and drag right side. You will get it. Select the word and drag up and select the word and drag left. Okay, this is going to get pasted. But what if Okay. Uh but what if I want to use the shortcuts? So if you just select the cells where all you want this word to be copy pasted, hold control D control D. Okay. Down. Okay. Control. Okay. Right. Okay. See now here I'm typing something like a pink. Now I'll just drag towards the right side and I'll press CtrlR it pasted down. Now you tell me okay if I wanted to paste this up. What is the shortcut to paste up? Okay it's not control U. If you if you press Ctrl U see it is getting underlined. If you want this word to be left side you can't do it using Ctrl L because Ctrl L shortcut is given for creating tables. Okay I'll give you the shortcuts. Okay. Alt E I L. It is for left. Alt E I up. It is for up. Okay, it is for up. Now see, I'll just select this. I'm I'm not using any shortcuts. Alt. See, whenever I say alt, you can notice that on the menu bar some keys have been highlighted. Alt E. See, when I'm saying E, nothing is highlighted because this shortcut is from the previous version of an Excel. But still those shortcuts works in the newer version. Okay. Alt E I U paste it up. I'll select like this. I'll select like this. Alt E I L pasted left. Okay. So you can remember now. So one is a drag and drop. One is shortcut. I'll tell you another option that is you just select the data. You just select like this the cell wherever you want to paste. Go to this dropdown and set down. Select the cells toward right side and you can simply say right. Okay. Select the cells you want to paste above and you can also say something like up up or left whatever you want. I want this to be pasted left. From here also you can paste left. Understood? Next one I would like to discuss about clear option. Okay. What is this clear? For example, I have this data set and I'm just pasting it over here. Okay. Uh let's say I'll also provide you uh web email address. Okay. is equal to this. So here I have uh this uh you know data. Now there is something called clear option guys. If you go to this clear what do we mean by clear all? If I just mouse over here you see clear everything from the selected cells. Contents formatting comments are cleared from the selected cells. You see I'm just going to select this entire data set and I'll simply click on this icon or I'll go to this dropdown and say clear all. See everything is going to get removed. Whenever you wanted to delete that you can or you press a delete from the keyboard. But pressing the delete from the keyboard you see it is uh not clearing the formatting. Okay. Even if I type it here ABC it is going to form as a link. Okay remember that. So clear all clear remove everything. Clear format means it will just remove the formats. Okay. But data is going to be there. Okay. That is one thing. But you see still this hyperlink is going to work. If I click on this hyperlink, you see the hand symbol. Whenever I mouse over here, you can see the hand symbol. When I click on this, that will take me to the gmail.com. Okay. Yeah. Ctrl Z. Next, clear contents. Only content is going to be removed. Again, if I press anything over here, it's still going to get as a link only. Okay. So, clear all everything is going to remove. Clear format will remove the formats. Clear content is going to remove the content. Clear hyperlinks. Here you see I'm just selecting this data. Clear hyperlinks means these are now no longer going to be hyperlink but it keeps the formatting. What is the formatting? It is still looking like a hyperlink only. Now hyperlink is nothing but a clickable link. But if I press Ctrl Z now see these are links. Now I'll say remove hyperlinks. Remove hyperlinks. Okay. Remove the hyperlinks and formatting from the selected cells. It you you you see it over here on the right hand side. You can see the popup now. So if I say remove hyperlinks, it will remove the hyperlinks and also format. Okay, clear will just clear the links. Okay, remove hyperlinks will remove links plus formatting as well. Okay, now I would like to explain you one more thing. Okay, now we are going to talk about a little bit about uh you know functions in Excel. Okay, before that we should understand one thing called aggregations in Excel. What are aggregations? Okay, basically whenever you hear the word called aggregate. Okay, aggregation is nothing but summarizing the data. Okay, uh you can use different aggregate functions like sum, average, minimum, maximum, all of that. For example, here I'm just going to add couple of numbers. Okay, 250, 240, 230. I wanted to see the summation of this. How do you do it? One thing whenever you select the numbers here at the status bar it is going to show you some aggregations okay like some average minimum maximum count but yeah every time we cannot ask the end user to check in this way right yes or no okay so we should provide the aggregated result here you add up all these numbers and do the summation you come to this cell okay you go to this option called aggregations okay go to this dropdown and say sum now it's going to sum all of these values see It has inserted a function called sum. Within that, it has selected the range from i 10 to i12. Okay. Now if I hit enter, you will get the total. This is one way to do this. Or you write a function called sum. Every function, every calculation is supposed to be start with equal to sign. So you can say is equal to sum of. Okay. You start typing sum. It's going to list out all the functions that starts with sum and press tab automatically inserts a bracket. You can provide a number one comma number two comma number three. But it is go if there are 10 numbers we cannot select the 10 numbers separately. No like 1 comma 1 comma 1. So instead of that you can do uh simply select the range like this. Okay. And close the bracket you are going to get the sum. Similarly, if you wanted to do the average is equal to AV. See, as soon as I type AV, you can see all the function. Select the function that you want. Press tab. It autofills the rest of the function. Select the values. Done. If you wanted to select the maximum, type maximum. Open the bracket and select the value. If you want the minimum value to be printed, you can also do it. If you want the total counter to be calculated, you can also do this. This way you can perform the different aggregations. So aggregation is nothing but summarizing the data. What is aggregation? Aggregation is nothing but summarizing the data points. Okay. So here you can do sum, average, minimum, maximum, town all of those things. Okay everybody? I'll go very slow. Here you need to understand one more important thing. Okay. What it is referencing. Okay. what it is cell referencing. Okay. So there is something called absolute versus relative referencing. What it is? Let me explain that with uh you know uh a basic example. So for example here you see guys I have something called different employees over here. Okay. I have different employees over here and their salaries. I wanted to calculate the bonus. Okay. Let's say these employees have made uh okay sales on this particular day. Okay. Now I wanted to give then 10% of the bonus amount. Let's say you go to the shopping and you're buying a shirt. The share cost is 5,000. They say that you will have 10% of discount. So what do we do? We calculate 5,000 into 10%. It can be something like 500. So I want that 500 to be printed over here. How do we do this calculation is equal to this should be multiplied with this 10%. Yes or no? Done. Now I'll hit enter. Now I got the 500. Now I want the same thing to be calculated for the rest of the cells. What should I do? Do I need to write the same formula in the rest of the cells or do you think that there is any other approach? Copy paste. Right? So once you in in Excel you have to write the formula in one single cell. After that you can drag and you can double click you can copy paste the formula. How to apply the formula? How to apply the formula for rest of the cells? Okay. One is copy formula and paste into the cells. Next one drag down. Okay. Next one double click. These are the different ways that you can perform. See I double click on it. But unfortunately it is showing zero. Why is that? Okay. If I double click on this 2515 is expected to be multiplied with 10%. But here you notice one thing. When you're copying the formula down here, we are expecting this value to be multiplied with this value. When the cell is moving one cell down here also, obviously uh 10% is also goes one row down, right? So this is where by default this kind of a behavior is a relative referencing. Now you need to change it to absolute referencing. You have to lock this cell. Here it is okay to lock both row and column. How do you lock it? You have to press F4. Okay, it is going to lock the row and column. Now if you send this down, see every cell is going to be multiplied with same only. Now you may ask me Santos, do I really need to put dollar sign in front of row number? Yes, row is getting changed here, right? It is coming down. What is what do we have at the down row? So row should be locked. Do I need to lock the column? Since you are not moving between the columns, even if you lock it, nothing will happen. Even if you don't lock it, nothing will happen. See, because the column is going to stay back. But whenever you are moving right side then you should be careful with the column locking as well. So here drag down is working fine. Okay. You can also copy this cell. Copy. Select all the cells. Okay. Right mouse click. Go to paste special. You can also paste it as formulas. Click on okay. This is also fine. Another way is select the cell where you have the formula including the cells where you wanted to paste it. Press Ctrl D. It is going to work fine. So these are the different options that you should know. Understood this example? So by default this is relative referencing. This is mixed referencing absolute. Okay. One more time I repeat. Okay. When I simply multiply this value with this. Okay. I'll get the result. When I copy this down I get zero. Why? Here I'm expecting this formula to be copied here. Yes, it is copied 2515. But again when it is uh this cell is coming down. Okay. Here also it goes one row down. No, it works with row by row. So you need to lock the second row. Go to the first row. Go to the first cell where you have the formula. Put a dollar sign in front of four. Why four? Because four is becoming five. Five is becoming six. So lock it so that it is going to stick into that particular cell only. Now see if you come to this cell, this cell into this cell only here it is moving. Blue color is moving not the red one. Okay. What if I wanted to calculate this here? Sales one should be multiplied with this and sales should to multiplied with this. How do you need to write a formula in one single cell that's supposed to be dragged towards right supposed to be dragged towards down. How do we do this is equal to this cell? Do I need to lock this cell here? Do I need to put a dollar sign in front of HR 31 or not required? No. Right. Okay. Multiply with 10%. Do I need to put dollar sign anywhere? Where should I put the dollar sign? Before M or before 26? before 26 absolutely if I put okay I'll just log both of that let's see what is going to happen I'll send this down okay it is working absolutely fine see but if I drag right side set see what is going to happen this value okay from here I'm when when when I'm in this cell it is 1,000 when I go here this should become 20,000 so it has become but since you put dollar sign in front of M m is not becoming n okay so what you have to do go to the first cell where you have written the formula don't make modification over here. Okay, always go to the first cell. Remove dollar sign in front of M so that M becomes N. When I drag this right side, okay, send this down. See, 10,000, 20% is 2,000. See, that's it. So, this is absolute versus relative referencing. You should know where to put a dollar sign. See, when you want one cell to shift to next cell, okay, do you want reference to be changed? Don't put a dollar sign. No, you don't want reference to be changed. put a dollar sign. That's it. You want a columns to be changed, remove dollar sign in front of columns. That is a B c. R row to be changed then don't put. If you want not to be changed, put a dollar sign. As simple as that. Clear? I I'll tell you something. See here, I'm removing everything. Earlier I told you that whenever you double click on this, okay, this formula is going to be pasted down. But you see when I'm double clicking this, why why my formula is not getting pasted? So whenever you have any empty column okay in between the data this is not going to work okay here there is empty column that is why it's not working so you can drag and drop double click won't work okay you have to remember that okay yeah that is a one quick tip I just wanted to give okay now we will try to understand uh you know uh some uh functions okay we'll try to work with uh you know different functions okay see here the first function that I would like to discuss is Count if. So what is count if? Count if is nothing but which counts the values based upon a condition. Count if. Okay. So here you see I have some data over here. Within this data, how many binders are there? So binders is one subcategory. So I'll go to this dropdown. How did I apply this filter over here? Go to data. Okay. Uh go to data. Okay. and click on filter button and go to this drop-down and say select a binder. See there are around 10 binder products are there. So I wanted to get the 10 over here. How do I do that? You say count if. Okay, you start at count if there are two versions. Count the number of cells within a range that meets a given condition. See this is the description I did not write. This is the description that were that was given by Microsoft. Okay. So count if is for single criteria. Ifs ifs count ifs is for multiple criteria. Just open this bracket. See it is asking a range. Range means criteria range. I'm looking at the binders where that binders is. Binders is there within this range. So don't select holding shift key like one by one. Hold control shift and down. Now you are at the bottom of the screen. No lock the range. Okay. I don't I'm not like I wanted to drag the formula down. So I'm locking it. Whenever you lock this entire range by pressing F4 in some computers function F4 is required. Okay, locking is done. Next comma criteria is what? Binders hit enter. Now see I got it. I'll send this down. Now see there are nine art products. What happens if I don't lock? You may ask me that question. So you see count if. Okay, count if my range is this. Okay, I'm not locking it. I'll scroll up. Okay, comma I'll say binders again. I got the 10. But when I send this down, you see here I got nine. Here I got eight. Why? Because I moved one row down. So here also this range is also going to move one row down. See since it moved one row down, I lose the I lost the art to be counted. So therefore what you have to do, you just select this entire range or if you don't lock it, write the formula, select only the column and press F4. entire thing is going to get locked. Do I need to lock H7? No. H7 should become H8. H8 should become H9. Binder should become art. Art should become storage. Therefore, I'm not locking it. Okay. Therefore, I'm not locking it. I lock only this. Now, I'll send this down. I'll get the correct answer. Okay. Now, here I want to get the total number of values which are greater than 500. You see there is a sales. I'm applying a filter. You can use shortcut key control shift L. So here I would like to count all the values. So guys we are going to at least fight an examples. So at least five so that you'll understand. So I would like to apply a filter. Control shift L or click on this filter. Go to this dropdown number filters. I'll say greater than greater than 500. I would like to count. Now see how many values are there. There are eight values. Let's remove the filter. I would like to count it over here. Is equal to count if. Okay. Range is this. But I'll just lock it. Function F4. Now how would I provide the count? You cannot say greater than 500. It's going to throw an error. So this greater than symbol is an operator. Okay. That is a text. That text should be within the double quotation. Okay. So how do you provide that? Put a greater than symbol in the double quotation. Join that with the value. Okay. Greater than should be given in the double quotation mark. And that should be joined with what? 500. Now if you hit enter you see you will get eight. As simple as that. Now let's say one more example. How many technology words are there? So that is also simple. I have to just perform count operation. Right. I I just have to perform count operation. So here come here I'll come and say count if range is this. Okay. Press F4. So that the range is going to log automatically. The cursor goes up. Technology. Now I can also provide a technology directly as well. But whenever you are quering technology word as a direct word, ensure that all the text is in the double quotation mark. Now I'm not providing the cell reference. I'm typing the word. Hit enter. See, you'll get a six. Okay. If you wanted to see, go to any cell, apply filter and select. You already know. I'll show you another way. Right click on this. Go to filter. Filter by selected cell value. How many are there? There are six words. Did I get six? Yes. Understood? Okay. Move on. Let's move on and see couple more. Okay. Here I I have multiple criteria. Okay, I have multiple criteria guys. Okay, here I would like to count furniture bookcases and in furniture tables. So this is one criteria. This is another criteria. How do you do it? You can simply say count. Do I need to use if or ifs see furniture is one criteria, bookcases is one criteria, furniture belongs to category, bookcases belong to subcategory. So you you should say ifs. Here it is asking criteria range. Whenever you say range, you can select a list of cells. Lock it. Comma. What is criteria of furniture? What is criteria range to subcategory? Lock it. What is criteria to book cases? Hit enter. See, you got six. Okay. Next one. You just copy the same formula and double click and paste. What all you have to change? Range remains same. You put your cursor on this uh you know red color line. Drag this down and drag this down. See you got it. Now you can sum up these two. Okay. So that the whole values are going to be 10. But we have done it in two different cells. Now okay uh complete formula in one single cell. I'll show you how do you do it. You see is equal to okay count ifs. What is the criteria range? This is my criteria range. What is my criteria? You select both the rows. Okay. We select both the rows. That is the logic. Criteria range two is this. Now what is your criteria? This. Now it is going to spill the value. Meaning that how many furniture bookcases combination are there? How many furniture table combination are there? 6 + 4 it is given. It is spilling the result. If you if you just go to this cell, okay, for example, if I have a here, you can see a over here. But when it spills, you can see the formula is graded out. Okay, that is spilling. It spills the result. Now what you have to do? Double click and put this enter function in the sum. Now we are going to get the total. Okay. Criteria range to be selected for multiple results. Done. Make sense? Easy. Now instead of doing it multiple ways, you can do it in one single cell. Move on. Okay. This is also same example. Okay. What if I wanted to search with the patterns? Okay. For example, here you see I have lot of products here. Lot of means I have 30 products here. How many products are starting with certain word? How many products are ending with certain keyword? How many products contain certain words? For example, here I'll say text filters. Here you see begins with any product is begin with something called peel. See there is one product starting with the peel. Any product that is ending with envelopes ends with envelopes. See there are these many products ending with envelopes. Any product contain envelopes text filters contains okay envelopes. See these many products. So if you wanted to calculate that you can also use count if function count ifs. Okay criteria range like this. Now I wanted to count how many products starting with envelopes. So you can say here uh envelopes. Okay, naps. But you cannot close this. You should say asterisk. Meaning that first envelopes word is going to start with. When I say star, which means that it can be anything. >> It can be anything guys. Okay, it can be anything. Star means first it starts with envelation and end. See, there are two words. If you wanted to check that okay go to texture filters begin with I'll type something like envelopes see there are two words similarly I'll just copy this formula okay and paste it here not here ending with if I want to end with put star in the starting okay it can be anything at the end it should contain envelope see it's going to contain envelopes make sense I want uh you know in between whenever you want in between what you have to do Put end key uh star key. Okay, put envelopes between first star and end star. Starting it can be anything but in between envelope should be there. At the end also it should be star. Now see there are 15. This way you you can even use a patterns also. What is this star? Star is called a wild card character. Uh there are star question mark underscore. I'll discuss them little later on. But yeah this is how you need to do. I hope this makes sense. Okay we'll take a last example in this here. I would like to count the total number of uh in between star envelope star. Okay. Yeah. So what I wanted to do I wanted to see how many art products are there where the sales greater than uh one lakh and profit greater than 20,000. How do we do it? You can say count ifs. Okay. Criteria first range is going to be this. Within this how many oops is equal to count ifs. I would like to count from here to here. This is my first criteria, comma criteria is going to be r. Next criteria range is going to be this. This one should be greater than equal to okay greater than equal to the double quotation or one lakh comma criteria two range is going to be this. Okay, which should be greater than or equal to amp% symbol and this value. Hit enter. But it is okay. Oops. See there are three values okay if you just look at art this is one criteria uh this is one okay and here is another one so whenever you wanted to do numbering you should provide that greater than less than sign within double quotition marks so is equal to first I'm using countips what is my criteria first within subcategory it should count only art product so how many art are there seven that is done what is my second criteria uh say criteria range two you select this value this should be greater than one lakh no so but here okay that greater than sign that is criteria two should be put it in the double quotion mark and then join with the value that's it okay criteria range three here you see the syntax criteria range three is this one comma what is the criteria greater than or equal to and join that with 20,000 and close the bracket That's it. See, something went wrong. Greater than equal to. Okay, here I made a mistake. See, I got three this way. Clear. I will be explaining sum if and sum ifs function. So, same like a count. So far, we have dealt with the count, right? Now, we are going to deal with the sum if. See, sums based upon the numbers uh based upon a condition. Sum ifs is for multiple criteria. As simple as that. Here you see I have uh a uh you know a company information where I have employee name, employee role, salaries, department, gender, join date, city and state. Now I wanted to know how much salaries I'm paying to each department. How do you answer that? If I give you a question, what is the total uh salaries that I'm paying to billing department, finance department? You have to sum up all the finance related numbers. No. Yes or no? For example, here uh this is billing number. Okay. Plus uh this is another and this is another like that we can't count it. No, like that we can't do sum. No. So how do you do it? You need to sum if sum these numbers if this department equal to this value. As simple as that. So is equal to sum if. If it is for single criteria use sum if range and sum range. Range means criteria range. Sum range means numbers only. No. Sum can only be done on the numbers. No. So don't get confused. Sum range means numbers. And this range means criteria range. So this is the range. Lock it. Criteria is going to be this. Lock it. Sum range is going to be salaries. That's it. Lock it if you want. And close it. That's it. See, now I got the total summation for each of the department. Okay. If you want thousand separator, you can add it. If you want a dollar sign also, you can do it. As simple as that. Okay. Yeah. We'll do couple more examples. Let's move on. Okay. Here you see I would like to get the salaries for these regions. Okay, here I have a different columns and one of the column is a region. I would like to get the region wise sales. Okay, how do we get it? Okay, I'll show that now. Okay, is equal to uh let's do it here. Is equal to I'm going to say sum if. Now I'm going with the single criteria range. Range means which column I need to select? Do I need to select G column or J column? G. Very good. So I'm going to select the first cell. Hold control shift and press it down. Lock it. Okay. Uh just uh lock it. Comma, you are going to provide the criteria that is this and the range column is this. Okay. Lock it and close the bracket. See now I got the same. Send this down. Okay. Send this down. You're going to get the salaries. What if I have two two criterias here? You see I have a criteria called uh category. Okay. Within category I would like to take furniture and then in the region I would like to select east. Now what is the total? Total is 208291. I want that number to be printed. How would I do that? Easy. So write sum if or sum ifs. Which one I need to select? Second or first ifs version. Okay. Now here the criteria is going to be different. Sum range. Which column I need to select? Some range. It is asking which column within the given data set which column I need to select J column. Very good. So here you can also select the entire column. There is no wrong. When you select the entire column no need to lock comma criteria range. My first criteria is going to be category because I'm looking at the furniture. Furniture is there in the H column and criteria is going to be this. Always follow this syntax guys. Okay. Comma. Criteria range two. Whenever it says range you select the range. Comma. Criteria 2 is going to be this. Close the bracket 208291 you're going to get the result. You want thousands operator add it remove decimals and even you can put a rupee or dollar sign up to you. Okay here you all know this right? This is equal to okay this is not equal to and this is also not equal to greater than greater than equal to less than less than or equal to. These are all different operators right what do we call okay yes we can lock using f4 these are called operators. Now here I wanted to get the total sales between this range. So how do we write it? So I'm going to say sum ifs. Sum range. I want these values to be summed up. Lock it. Criteria range is going to be this. What is the criteria? Lock it first. It should be greater than or equal to. Okay. Greater than or equal to this date. Okay. Comma. Criteria range two is also going to be the same range. criteria uh here criteria 2 is going to be less than equal to join that with this date so and close the bracket that's it now see you are going to get the sales between this range if I select from here to here 9 94 649 oh oops uh maybe okay this date won't fall under this so you're going to get the proper answer okay if I just to provide this range a date and if I select from here to here you would end up seeing 94 690 same result so it's just that providing like a count if only but instead of count if we are just doing the sum okay yes it's always safe to lock the cells is it clear everybody okay now I'm going to ask you the same question again here I wanted to sum up all the numbers which is not equal to this date so I want you to only sum up these cells so how do you do that is equal to sum ifs sum range is this very good criteria range is what is very good. I want criteria not equal to this. Then how do you do? Not equal to symbol is this. Join that with this particular date. That's it. Now see if I just select only these numbers, you are going to see the same output here as well as over here. Make sense? So this is not equal to operator. Okay. So this is related to the tables example. But tables example I'm not going to do it right now. Tables let's discuss tables in another session. Okay. There is an advantage with the tables. Okay. Now look at here I have a different dates. Okay. What are the different months that I have it over here? September and October. But here I have something called October. You need to get the sales for October or September. How do you get it? Is there any word called September here guys? Is there any word? If there is a word called September then I can sum do the summation, right? There is no. So what you can do? You can insert a column and take a helper column. So I'm using a function called text function. This is my value. It is asking what format you want. I'll simply provide four m that gives me the month name. Again in the next two words we are going to learn this text function and send this down. Now that I got this column I can do the summation is equal to sum if ranges range criteria range done. Okay. Criteria September done. Sum range this one done. See I got zero. What happened? Why did I get zero? September spelling is incorrect over here. That is the reason. I'll just copy this value and paste it over here. Now you'll get the result. See only September sales you'll get it. Make sense? All right. So the next I have uh one more example over here. I want you to get the furniture and bookcases sales and furniture chair sales are there. If you close a look at the problem here is uh two you have two criterias under the same bucket that is furniture. So if I write the standard sum ifs function sum range is this. Okay, great. Lock it, comma. Criteria range one is going to be this. Lock it. And uh Okay. Oops. Criteria. I'll give this. Okay. And criteria I'm going to say book cases first. Okay. Oops. Let's write that formula over here. I'm saying sum ifs. Sum range is going to be this. Lock it, criteria one range is going to be this. within this I would like to count sum for the bookcases come I have one more no chairs so chairs is also part of the subcategory and I would like to count the chairs now see I'll get zero so whenever you have two criterias in one single column you can't do the summation here what you have to do I'll tell you so instead of pro here you see you have provided criteria uh bookcases once and criteria uh chairs as once Don't do that. You add it as a range. Sum ifs. Sum range is this. Okay. Comma. Criteria range one. Okay. For furnishings, you select furnishing two cells as one single cell. Comma. Criteria two is this criteria range two. Criteria two is going to be this. When you do that, it's going to spill the values. You see, you got the two values. Later, what you have to do? You need to put the entire calculation in the sum function. then you are going to get the total summation. So when here I have written you will get zero when you have two criterias in single column. Okay. What you have to do use range as a criteria. Okay. You have to use range as a criteria. Okay. Understood this as one range this as one criteria. Range use range criteria. This is the formula. Okay. Here um I want to get the total sales for America whenever the uh wherever the America has. So here you need to use wild cards. I'm just giving it as a uh use sum if with wild cards. Okay, use uh sum ifs with wild card star. I already explained you uh this uh in the count if function use that take it as a homework. Okay, just a quick info guys. Intellipath offers data analytics course in collaboration with iHub IIT Riy. Through this course, you'll get to learn data analytics from industry experts and IID faculty in depth. You'll learn tools like Excel, SQL, PowerBI, Python, and K9. This data analytics course is praised by our learners and their user testimonials are available on our Intellipath Achievers channel. You can check them out. The link is available in the description below. So, if you're really interested in learning data analytics, this is your opportunity. Click on that apply now button right away and get started in your data analytics career journey. >> Yeah. So on we are going to talk about a new set of functions that is text functions. Okay. So what are these text functions? How they are going to help? We will take a look at it. So if you go to the formulas window there is something called text functions. Okay. Not all the functions are uh required as you are not going to use all of them. Okay. But you know there are some functions like concatenate exact find uh left right mid okay and concatenate concat text to join trim upper lower okay uh value all these functions are going to be helpful okay first I'm going to start with a function called find and search okay so basically find and search functions are useful to identify the character position for example if you take a Look at it here. I have something like Microsoft Excel. Okay. Here I have something like Naga Santo. Okay. Ranjit Kumar. I have different verbs here. Now if I ask you what is the position of a space? What is the position of a space? So space is located the 10th position. Here it is fifth position. Here it is eighth position. How do you I how do I identify that position? What is the need of identify that position? What if I wanted to extract the first name of this word? Okay. So in that case the space position is going to be helpful. How do you identify that? For that you have two functions. Search and find. Search is not a case sensitive whereas find is a case sensitive. That is the one difference. I'm going to say search. You see returns the number of the character at with a specific position. So I'm going to say search. What is our find text space? I already told you whenever you wanted to provide a text that should be in double quotation mark. So put space in the double quotation. Okay. Comma where where is your text within text this okay start number this is optional. Whenever you see anything in this open and closed bracket now that is an optional you can ignore it or you can also provide it. Now see space is located at 10th position. So I get 10. Okay. Here it is five. Here it is eight. Then now if I say if I wanted to extract the first word, what I what I can do? I can say left. I can use a left side. I can use left function to this is the text extract the left side word. How many characters I want? If I say here 10. And if I drag this word in the next name also it is going to pick 10. But instead of that instead of providing 10 if I provide this number that's going to extract the right word now understood what is the importance of search function for example I can also use find see find text is what a space where here see it's going to get me this but if you use find function and you're finding for small end is small n there in the nagasanto no see okay it is there over here let's do one thing I'll type here just naga I'll just uh move this formula over here. See, since small m is not there, it is throwing an error. But if I use search function and type uh small n within this, it's going to say one because n is located at first position. Therefore, find is a case sensitive. Search is not a case. Understood what search and find can do? Okay. Now I have all of these names. I wanted to get the first name from here. See how do you get the first name? You can write see first name is nothing but left side word. I can write a left of this text. How many characters are there? Naga four characters. No I can say four. Number of characters four. Okay. Done. Now I got naga. But is it going to work for the rest of the names? No. For for every name it is going to extract four characters only. How do I make this dynamic? If I find the space position, I can get everything before the space. No, if I find the space position, I can get everything before the space. Right? First identify the space position. Let's use the search function. What is the targeted text? That is space. Put that in the double quotation marks from where within this text starting number one. See, I got five. Now, what am I going to do? I will remove this four from here and select this. Enter. But do you notice okay how many characters are there within naga? There are five characters. Why five? Because we have take we have provided five space is also there. To check that use another function called length. Now see you are getting five because we have taken everything till space. No. So what you have to do? You need to subtract one from it. Okay. Now see you are going to get four. Done. This way you can use left function. What if I wanted to extract the right side word? Okay. That can be done in many ways. Okay, you already know how to do it for the left. So let's write left function. This is the text here. Number of characters. What you can do here? You you don't write search function another cell. You start typing it here only. Search find a text is space rare within this text. Okay, starting number one. Anyhow it uh now if you just select number of characters that is search function, it's showing you the output. Okay, and at the end I'm going to say minus one. Close the bracket. See you got the left side word. Now you guys tell me how do I get the right side name? I want Santosh Kumar and V. How do I get that? If I say write function. Okay, write of this is text. How many characters I give Santos is seven. Okay, I got it. But when I send this down, you see I get incorrect result. How do I deal with this? Think of it again. You can't write search function here. So how do you deal with this? Okay. So here what you have to do first you have to do this. Okay. Write off you will write we we take write of this word length of the total word minus length of the first word. It's going to give the length of the second name only. No yes or no. Look at here. I'm going to say is equal to length length of the total word. Okay. How many minus if I say length of my first name I got eight characters. Okay. from here length of uh here I'm just going to say + one oops sorry minus one there is a space now so minus one now see I got seven if I send this down here I'll get five now if I just to use a write of okay a right of this whole word comma seven characters that's it now I'll get Santos as simple as that so we are just taking the total length of this word minus length of first name so when I say total length 13 minus 4 that is going to give me eight only now it works right okay this way you can extract the right side name also okay now next function there is a function called text function okay I'll share this file so that you can see the definition also basically the text function takes the number and returns the format okay based off format it going to give you the value see from the snapshot you can easily understand 100 is the B5 and I have formatted this way okay now it has come up with dollar sign. Next one 08.5. Now I'm just turning it into percentage. This time I'm changing it to minutes. Okay. And this one I'm rounding up this date also I'm formatting it. So let's explore those things over here. Here you see I have a name and you know uh project start date. Here I would like to something like project was assigned on this date. How would I do this? Okay. Basically I need to combine these two, right? So not these two. I would like to combine project was assigned on this date. So here I'm writing first I would like to provide a piece of text. Always text should be in double quotation marks. Yes sir or no always text should be in the double quotation marks. Right? So I'm going to say okay here I'll say uh the project was assigned on quotation marks. I need to join this with this date now. But see the problem whenever you join text and date is also going to be treated as a number. Now this 45708 is this date but it is showing like a number. How do you how do you how do you make it look like a table? You need to change the format. Write a text function. This is the value in which format you want? I would like in something like month mm dd y y y y y y y y y y y y y y y y y y y y y y y y y y y y y y y y y y y y y y y y y y within this format I want. Now see the project was assigned on this particular date it is going to show. Okay, let's change it as per this. I'll say DD and here I'll say mm. Now you would see it in the same format. So DD mm Y byway format. So whenever you want a text number formatted, numbers are going to be as dates are going to be converted to number. But you can use a text function to convert that. Now here I have different output. Here I have the same thing. Here I need to change the date format to this. Of course I can do that using text function. Okay. Text of this is my value. What format you want? Let's say I want year. So provide four years in the double quotation mark. Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y comma. If you say 2 M you'll get 0 1 02. If you say 3 M you'll get Jan F something like that. Okay. And DD. Now see the format is going to be like this. If you say 4 M note, you are going to get something like entire month name. This also you can do it. Okay. Now here I want my phone number to be in this format. How do you get that format? It is very easy. Just go to text text function. Take the value. You want first uh a open bracket. Put that open bracket. First three digits. Now enter three zeros. Another four zeros. Another three zeros. Okay. And then you want closed bracket into double quotation. That's it. So this entire format should go in open and close bracket. The numbers to be separated by zeros. See? Okay. Moving on. Here I have 0.1.2 0.35. I wanted to change them to percentages. How do you do it? It's very easy. Write a text function. Click on this cell comma. You can say in the double quotation 0.0 percentage. You just need to provide the percentage sign. That's it. See this has been converted to nice percentages. Okay. As simple as that. So here I have a date along with the time. I wanted to extract the time text function. This is my date. How do you extract the time? Okay, hours, colon minutes. If you have seconds also, you can do that. See, now it's going to extract time. If you have uh if you wanted to extract the date text of this, if you want only year and month, year, four years and month, that's it. Year and month you are going to give. Okay. Same way here also text. This is my date field. I'm going to say DD - mm - Y by why why did I start with the DD if is 13 14 are the dates? No, that's it. Now I want the time. So here what I'm going to say text off this value, hash minutes, seconds I'll say am. PM. So let's see this is 554 PM. So AM and PM I have got it. Understood how quickly we can generate these values using these functions. Okay, left and right already we have seen. Let's go with the mid function. See here I wanted to uh get the middle word. Okay, let's say this is product code two digits. For that you have something like mid middle word. Okay, a mid function text is this. Where do you want to start? Starting number I wanted to start at fifth character. T is the three characters. Hyphen is the fourth character. Start number five. Okay, I'll give five. How many characters you want from five? Two characters. Now see it's going to get me phi all of that. So at any point of time you wanted to extract the middle word you can do the mid function extract the given number of characters from the middle of supplied string. Mid takes three arguments that is the text starting position number of characters. Okay. Similarly you have a length function. You all know what length function is. It will just to give you the total length of the word. Okay. But yeah on a standalone you cannot use this function. But this function can be used with left and right functions. Okay. Now we will take a look at a replace and substitute function. Very important functions. Please pay attention. Okay. Replace and substitute functions are used to replace a particular word. But replace function require starting position whereas substitute function doesn't require. For example, here I have a text 830 some numbers I have. So after okay I wanted to have a word uh something like ABC. How do I do that? So if you start with the replace see old text is what? This is my old text. Starting number where do you want to start? Okay what will be my starting position. I want right after this hyphen. So hyphen is located at the fifth position. Is located the fifth position. I want after hyphen. So starting number is going to be six. number of characters. How many characters you wanted to insert? I wanted to insert three characters. What is the new text? New text. Let's say I would like to add ABC. Now see exactly after. Okay. I got ABC. Okay. The problem with this is it replaced uh you know uh iPhone with uh sorry after it has inserted ABC. But I don't want that. I want uh you know like this. Okay. Okay, I want something like this. Let's rewrite it. Okay, is equal to replace. Okay, this is my old text, comma, starting number. I'll say at the sixth position number of characters. I'm not going to give this. If I say number of characters as a three as I'm inserting three, it is replacing after it is replacing the first three characters. So, I'm I'm leaving it. New text, I'm going to say ABC. Now see it's going to insert ABC. I'll say ABC space. Now see it has inserted. So ignoring the number of characters is solving our requirement. Okay. Same thing. Can I do with substitute? Of course you can substitute text is what? This old text is what? In this case old text is hyphen. New text is what? Again hyphen a b c space. Okay. Done. I'll close this. Instant number is optional parameter. I'll close it. Now see I'll get the same output but the problem here you see wherever there is a hyphen it is replaced with that yes or no here is there is an hyphen here is there is an hyphen it is replacing with ABC to avoid that second time repetition instant number you if you say one it only replaces in the starting position okay first occurrence of a space if you say instance number as two it will add ABC after the second iPhone did you all understand the requirement but yeah In a nutshell, replace and substitute functions are used to replace something. Whereas replace function require starting position and number of characters to be replaced with. These are easy functions. Okay, whenever you do data transformation, these are going to be really helpful. Okay, let's move on. So, okay, here you see I want this year to be replaced with this year. Here 2000 should be replaced with this. Uh 99 should be replaced with this. How are we going to do? is equal to replace old text is what this okay starting number where do I need to start five number of characters four new text is what this see I got it as simple as that you're understanding this very well okay let's go back here so if you look at here are one more example I have these numbers here 830 should be replaced with Naga 831 should be replaced with Santos and 8330 8332 should be replaced with kuma. How how do we do that? Here replacement is based upon a condition. So first what you have to do you extract the four characters and then use a v lookup. That's also a purpose right? But that doesn't that is nothing to do with the substitute function. You're getting my question. First four characters should be replaced with naga. How do I do that? So I'll say here is equal to left of I'll say four characters. See this is aligned on the left hand side. Okay. Now if I calculate 830 equal to this it's saying false. Why it is false? Because whenever you extract number from a text represent a number it still keeps it as a text only. You need to convert this to number. So use a value function to convert this to number. Now see as we discussed earlier numbers always align on the right hand side. Now if I just compare this equal to this it says true. Now see both are numbers. Okay. Now I got it. Now what do I need to do? First I need to get this naga over here. No. Yes or no? So to do that can I use V lookup? V lookup of this is my lookup value. Okay. Comma. What is my table array? This is my table array. Lock it. I within this table array column index second column I want and I want exact match. If you don't understand V lookup, no problem. I'll discuss this later on. See I got nag and all. Now what am I going to do? I'll copy this entire function. Copy. Let's remove it. I'm going to say replace. This is my old text. Starting number I want one. Starting number I want uh one. Number of characters four. New text is what my V lookup answer. That's it. Now see I got the output. This way you see you have used uh left function, value function, VLOOKUP and replace. This is called combination of formulas. If you don't understand V lookup in some time we are going to discuss I want you to understand uh how we can write you know all these functions together. So look at the syntax what is the suitable function for that particular syntax you write that okay clear again we look up I'm going to discuss in a great detail but I'm just giving you this example okay now what if uh I have something like this okay okay I have employee name here I wanted to get the first name you already know how to get the first name right so first you write search function get the space position enter you got it right now what you have to do You you just copy this function except is equal to you come here you say left of this value number of characters you provide this function including minus one okay you got the first name how do you get the last name you don't need to write uh you know write function always here you can use substitute also see is equal to substitute old text is this okay old text sorry substitute this is the text this is the old text what is the new text. New text is nothing. So that old text D is going to be removed with nothing. Okay, it's going to be removed with no value. Now see, I'm going to get over. But if you look at here, there is a space though. How do you remove that space? There is a function called trim. Okay, before there is a space, trim is going to remove that space. See, even without using without using right function also, we we are able to get the answer. Make sense? Okay, let's move on. And yeah we have a very pretty easy functions okay uh to format your text that is upper lower okay proper lower means it will convert the text to lower case upper means which which will convert the text to the upper case okay so is equal to you can use lower converts all the letters in a text string to lower case see everything will be converted to lower case upper means everything will be converted to upper case proper means first letter of the first word is going to be in a capital letter you see proper of I'll use this see first letter in naga is n that is in up capital words s also in capital let's select three and send this down okay easy functions now this is used to format the data now so next we have a few uh you know concatenate functions one is concatenate so what is concatenate function what is the meaning of a concatenate guys concatenate is nothing but merging the two cells okay a concatenate is nothing but merging two cells. Here you see the Excel concatenate function concatenate or joins as merge the cells up to 30 values. Okay. And returns the result as text in Excel 2000 and later versions. Okay. We have concatenate and text join functions which are much better much better and more flexible. Okay. So first let's stick to concatenate function. If I say concatenate, okay, joins several text strings into one text string. It is saying this is my text one, this is my text two. See, it has combined both the words. What if I want a space in between? You can say concatenate this is my text one, text two is going to be space that you can put it in the double quotation mark and the text three is this. Okay. Now the problem with this function see the concatenate function cannot handle range of values. What if I have something like this? A B okay C D okay E. Do I need to select each and every value? See, even if I select ABC like a range, see it's not going to join. That is the problem with first function. So selecting each and every value like you know 1, 2, 3, 4, it's going to take lot of time and of course it is annoying right? Yes or no? So that is the limitations with this function. Okay. Initially back in 2013 14 of time we used to have we used to have this concatenate function and we used to feel little uh you know difficult concatenating the words. Okay. So uh it doesn't accept the range therefore you cannot use the ranges. But if you have one two values you can concatenate using concatenate function. See the items can be strings numbers or cell references in one cell. Okay. So if you wanted to manually use okay manual approach I'll tell you okay what is the manual approach is equal to this first word use amperson% and a space again amperson second name see now there are three values naga is one word space is another word and uh you know uh santo is another word this is a manual approach using amperson sign okay using amperson sign this is amper Okay. Now what is concat function? See the Excel concat function can concatenate join values. Okay. Supplied as a reference unlike a concatenate function. Concatenate accept uh you know concatenate accepts ranges as well. So concat function can accept the range of values. Okay. Meaning that look at here whatever the limitation that we had with the concatenate function is addressed using this function. See is equal to concatenate. Okay. Text one is this. Okay. Text two is a text two is a space. Text is this. Again this is possible with regular concatenate function also. Right? But look at the match concat. Okay. I'm just selecting this range. But what if I want a space in between? you should join that with the space like this. Okay. Now Excel will understand that okay he's expecting Santos is expecting a space in between words. That's how it understand. Now see okay even if I have a b c d e now see I'm going to select this entire range using this function. Select the entire range. I want a space in between. Enter. See it is accepted. This is the advantage of concat function. Okay. Now here I want uh uh output something like this. First name is Naga and I have a date. I want output something like uh you know year. So how do we do it? We can use a concat function. Text one is this. Text two is going to be year of this value. Okay, year of that date. Now see I'm going to get na 2023. If I want a space in between I'll just put two double quotation marks with space. Okay. Year function will just help you to get the year. Okay. Year of date. Okay. Here function will fetch the year. Okay. Moving on. So this is okay. So concatenate function not going to accept the ranges. Concatenate accept the ranges. But here we are providing the uh delimiter. Okay. That is space or whatever you want you are providing. Right? In between the words if you want space you are providing it. Okay. There is another function which is more advanced. Okay. that is text join. What is this text join? Okay. So here look at here the text to join function concatenate multiple values with or without a delimiter that is separator. Text join can concatenates values provided as a cell references ranges or even a constants or you can also ignore empty cells. It accepts a single cell like A1 comma B1 comma C1 or ranges A1 B1 C1 or even a constant value like in double quotation marks for example here you see I would like to add an AA and Santos okay look at here I can say text join you see you can provide your own delimiter like a space you want you want or anything so let's say I want a space ignore empty cells what if there are empty cells do you want me to ignore or include Exclude or exclude. I'll say uh ignore empty cells. That option is also there. Text one is this. Text two is this. I can select a single cell like this. Okay. See nasanto. And I'll send this down. Okay. I can also instead of providing these two as a separate cells, I can even select the range. See, it's still going to work as simple as that. Okay. For example, here I have initial something like that. Okay. Santo Kumar, Ranjit Kumar G. Here I have you know Sidisha Vanapali and Rosh Sharma Makkumar. Okay Virat Koh. Okay. Now if I if I add this cell also reference if I just expand this see what is going to happen. If you look at here sirisha there is only one space but if I make it as a false see include empty cells. When I include empty cells I see double spacing. So using concatenate function it's up it's in your hands whether you wanted to include this space or exclude the space but this won't be possible with the earlier functions like concatenate and concat. Is it clear? So you can include the cells or exclude the cells using text to join function. Okay. Next we have a value function. Okay. This is very important function. So whoever working with Excel at some point of time in your career you see numbers like this. Now you tell me is it a text or a number? It is a number only but a text represent a number. So Excel is creating is that a text and I I I have to mention this whenever you see anything that is aligned on the left side is a text. Anything that is aligned on the right side is a number. Anything that is aligned on the center is a boolean value. Now to check whether it is a text or a number you have two functions. One is is text. See this is a text. It's in true. If you wanted to check whether it is a number or not, you have another function called is number and select the value. See it is saying false. Now since it is a text, how do you convert this to proper number? You can use a value function. See, converts a text string that represents a number to number. It is looking like a text string that is looking like a number. Sorry, it is a number which is looking like a text and it is saying I can convert that to proper number. So value of see it is saying text. You provide this text and hit enter. See now it is aligned on the number. Now if you have any doubts you can use is number function to check whether it is a number or not. Okay. In the real time extract first four characters from a number. See what is going to happen now. There is no green color symbol. If anybody aligns it on the right hand side you might think uh it's a number but see it is what it is saying it's still saying it is not a number. Okay. only when you copy these values okay and paste as a values then only you can see this green color symbol so in the real time whenever you do V lookup no V lookup this is your lookup value and this is going to be your array okay now whenever you print you'll get an error but when you check 831 is there and is there over here why am I not getting it to evaluate that first you know here if you just select this entire word and see 831 it is saying okay but if you just select this word simply what you can do copy and paste as a values guys whenever you see this symbol no what you have to do instead of providing a simple lookup value you provide a value of this now it converts the text represent a number to number therefore you will be getting the answer okay this problem you come across you see the number in both the places but you don't you get any error how do you resolve it because it is convert whenever you extract a number from a text it still treats as a text only so use value function to convert it to a number once again okay see I wanted to do a V lookup and get the answer first what am I going to do I'm going to extract the first four characters I'll say left of this comma 4 okay I got the answer now you're tempted to do V look up here okay uh let me move this uh a little Right. Okay. Now here you can do V lookup. This is equal to V lookup. This is my lookup value where this value within this array lock it. I want second column and I want exact math. See you are getting any error. Why any error? If you just select the lookup value you see 831 is put showing up in in the double quotation marks. So whenever you see anything in the double quotation mark what does it mean? Which means that that is a text yes or no. So you need to convert that to proper value. So what you can do you write a value function and provide this this now see if I select this entire lookup value it is showing as a number no double quotation therefore you get the result that's the importance of value function okay now I'll show you some interesting functions text before and text after what is text before and text after there are some new functions I know that uh some of you can't see these functions in your Excel because these are available I think only in the latest versions. Here you see I have some text here. Okay. I wanted to use another function called the text before. See the delimiter. What is my text? This is my text. What is my delimiter? This is my daily limiter. Instance number you want it uh before the first slash or second slash. If I say second slash, see that is uh instance number. Match mode. It is a case insensitive. This is not a case sensitive. Okay, you can say match to end I mean to the end of the word if not found you can say no value. Okay, that's it. See before the second uh slash whatever you have it is providing if you say okay these are all not required okay you can say match in u sorry instance number one it will give you anything before the first uh you know this thing that's it now let's say I want everything after the first you can use the text after that's it text is this what is the delimter this I want after the first hyphen whatever you have it after the first iPhone it's going to give you that. So even you can use a text before and text after to split the words. For example, here you see I want a first name and last name. Instead of writing left, search all of that you can say text before. Okay, this is my text here. What is the delimiter guys? Space you provide the space instance number. I'll just uh say one. See, I got naga. Send this down. Okay, here I'll say text after. Okay, this is my text is a space. That's it. Sis Santos. Here I would like to separate uh you know the initial with the name. Okay. How do I do that? Okay. Look at here. I want first to get the initial title. So here I'm going to say text before. This is my text. What is the delimiter? If you look at each and every word dot and space is the delimiter. First one is a managing director. Second one is a director. Third one is a third one there is no uh matching pattern. Fourth one is a director dot. So I'm going to say doctor not director doctor dot space. Okay. In instance number I'm going to say one. Okay. This is case insensitive. I'm going to say match to end. Okay. I'm going to say match to end. If I close this you see I got only one here. I want to I want this entire designation in a reverse order. So what I'm going to do this instance number I'm going to make it as minus one. Okay. Where is my instance? Instance number minus one. Okay. Uh this is case insensitive dot director dot and space is also there. Okay. Uh and case insensitive is done. And you need to say match to end because I'm going from the left to uh I'm going in this direction guys. Whenever you go in this direction, you are going to get it correctly. Let me do it again. Text before. Okay. Oh, oops. I need to use uh sorry, text after. I guess this is my text. Oh, no. No. Text before. Okay. Uh text before text is this. Okay. Uh my delimiter is doctor space. Okay. Instance number minus one. You ignore this. Match to end is important. processes. Okay, here I'm getting this. Let's send this down. Okay, I'll just check this. Why it is not working? Uh, we can extract this. Enter run. I'll just check this example. But yeah, you understood, right? What is text before and text after? You can extract the text before this and you can extract the text after this. Okay. So there is one more uh there is again one more function called the text split. So this is the uh you know last function uh within this uh text function. You see now in order to split this word we have left right search mid functions and we have text before and text after. Now you see you like this function a lot. Okay I want to separate these two words. You see text split. What is that text this one? You have column delimiter and row delimiter. Now I want a in one column and Santos is in one column. So what is the delimiter between this? What is the common character between this space? So you provide that. Again I don't want any road limitations. I'll remove this. If there is an empty cells, you can ignore empty cells. Okay, match mode case insensitive. That's it. Now you close it. See, without writing left right functions, you are going to get everything very nicely. See, no functions are required. Using one single function, I can split the data. Even uh if I add any other names also, those are also going to be split very nicely. Which one is easy now? Text before or text after or text split? Text split. Right. So initially you liked left left right then after text before text after now you are liking this. So that's how it is actually. Okay. Now there is one common interview question. What if you have multiple delimiters? Okay. If you just look at this data you have comma, you have colon, you have a space also. Okay. How do you deal with this? You have multiple delimiters. Okay. In this case, is it possible to split this data to multiple columns? Okay, look at here. Very simple text uh text split. What is the text? This one. Okay, you you have a column delimiters. Now, you provide all the column delimiters. Okay. Within this open and closed bracket. First delimiter is what? Comma. Done. Next, another comma. Second delimiter is what? Colon. Another comma. Third delimiter is what? space. Okay, once you provide all of them, okay, you close the curly braces and double quotation mark. Okay, row ignore empty cells, include empty cells. That's it. You close it. See, something went wrong. Uh, first comma, second comma, hang on. Text split text is this, I would like to open curly braces. Okay, within the curly braces, the first one is a space, comma, the second one is colon, comma, the third one is a comma, curly braces, double quotation marks, close it. Somewhere I'm making a mistake. Okay, I think these are not required. I guess see within the curly braces you have to provide all of that. Now you see each and every word got separated nicely. Okay, even if you add uh in here space, I'm taking power bi. See as soon as I do this it's going to split this. Okay. So even you have multiple also you can easily do it. Uh why it hasn't taken this properly? Uh there is a space. Okay. So PowerBI there is a space that's the reason it's considered as one otherwise it will be considered as only one. Easy right text split easily splitting the values. Okay. Uh here for example look at here I have data like this. I wanted to uh split Mr. and miss dot I can split that. Okay, here I'm going to say text split. This is my text. What is my column delimiter? Column delimter I I'll open the bracket. I'll say Mr. I'll open the bracket and I'll say Mr. dot and space. That is one and another one is miss dot and space. This is another one. And curly braces close the bracket. See now I got nagasant all of that. But here there is an empty cell. Now I wanted to exclude that. So what you can do uh ignore empty cells. Whenever you do that naga will come in the first place. Send this down. There you go. Easily you can replace the initials as well. Okay. Now we are going to talk about logical functions. Okay. What are logical functions? Okay. What type of logical functions are there? We will take a look at it. Okay. So what are logical functions? Okay. Logical functions in Excel are used to check whether a condition is true or false. Okay. What if it is true? What if it is false? These logical functions will help you to do a few things like you know conditional formatting, data validation, automate decision makings. All these you can do. So these are the different types of logical functions. If function, if error, if na. Okay, these are like you know what if uh the result is error and what do you want to show and or functions are used to deal with the multiple logics and ifs is for the you know more than one condition and event switch is also the same okay now let's uh I'll take some examples and try to understand as you see on my screen here I have different applications and I have a sales my requirement is to show if uh the value is above 25,000 I wanted to show something like you know profitable Okay, what do I want it to show? I want to show something like a profitable. Anything less than that, I wanted to show nonprofitable. Okay, how do I do that? Okay, to do that you have to write uh you know if function. So let's write this. Okay, is equal to if. See once you start with the if. Okay, how do you write if logical test? This is my value. If this value is greater than or equal to 25,000 this way you have to write. If this is true, what do you want? I want profitable. Profitable is a text. No. So you put that in the double quotation. Okay. Comma. Value if false. What do you want to do when value false? I would like to say nonprofitable. So you can say nonprofitable and close the bracket. That's it. Hit enter. Now see this is about 25,000. Therefore we are going to get profitable. If I send this down, you see nonprofitable, nonprofitable, nonprofitable. And profitable as well. Okay. later you wanted to highlight only profitable words you just go with the conditional formatting where we can say highlight sell rules okay uh text that contains okay uh text that contains only profitable I need to say so in that case okay text that is equal to if I say profitable no even nonprofitable word also contain the profitable so therefore it highlights it that is the reason I'm taking text that is equal to profitable okay here by default it will provide a light red color. I'll go to the custom format. I'll I'll go to the fill and take the color which I want. See that's it. Now let's say in case if I change this value to 26,000 immediately it becomes profitable. Therefore it even highlights into this light orange color. Okay. Now let's move on to another example. Okay. Here same example only but output is going to be different. here if the value is above 25,000 or 30,000 I wanted to calculate 10% of the value I wanted to give them the bonus only if they exceed the sales of 30,000 if not I want I want that cell to be blank so how do I do that is equal to starts with if logical test is this value if it is greater than is equal to 30,000 what do you want to do this time no text calculation of this value should be multiplied with the 30 sorry multiplied with 10%. So you need to enter 10% like that. Okay? If not I want I don't want anything. So when you don't want anything don't leave it empty just put two double quotation marks. Enter. Now see two double quotation marks will give you empty. Now see wherever you see the number where the sales are exceeded greater than 40,000. Okay. Even you can highlight all the sales which are above greater than 30,000. Here see greater than here you can say 30,000 okay where only you'll get the output makes sense even you can do the calculation also not only a simple text even you can perform the calculations as well okay now let's deal with one more example in a different uh data set okay here the requirement is slightly different okay so I have a different states where the sales are done and some profits also we have got here I wanted to return profitable only when the sales are above uh certain threshold that is um 80,000 okay when the sales are above 80,000 and profit is greater than 30,000 then only it should be profitable how do I write that statement whenever you have two logics know you need to use and operator and function okay how do you write it see so if you just say is equal to I can use and function see checks whether all the arguments are true then returns true. If all the arguments are true. So what it is saying it will just check all the logics. When all the logics are true then only it returns true. Okay. Logic one this value should be greater than 80,000. Comma. Logic two. This value greater than 30,000. When these both conditions are true no then only it returns true. When these two conditions are satisfied then only it returns true. How many logics it will accept? As far as I remember it will accept 255 logics. Yeah. H lookup, x lookup, v lookup all those functions are covered. So here this way you need to do it. Now see here the logic is true. When I send this down, wherever it is true, it says true. Wherever it is false, it's going to say false. Now what do I need to do? You join this. If if this entire logical test is true, you see when I select the entire logical test, I see the output over here. When this is true, what do you want? I want a profitable. Okay. What if I ignore the false condition? If you ignore the false condition, you are going to get something like a false. But it doesn't make sense to show false. No. So you should either say nonprofitable or you should keep it empty. When empty, include two double quotation marks. That's it. See how nice it is. This way you can even do it. Okay, understood how to deal with the multiple conditions. Okay, here you see I have some employees. I have their first name and last name. Okay, and their birthday. What I wanted to show here? What do I wanted to show? Any employee who joins before 1980, I wanted to mark them retired. For example, this person joined in 1973, so he should be retired. Okay, this guy joined after 1980, so I don't want anything. How do I write this condition? Can you write a formula and show me? See, you can take a random dates in Excel. Okay, anything before 19. Okay, let's for example I'll just copy paste this data in Excel. You can copy the same thing in your Excel and write the calculation. Okay, here I'll give you a clue. You need to check with the year. Okay, the hint is you need to check with the year. Okay, now I'll tell you. See here the problem here is first I need to uh I cannot say like this. No, if this is uh less than 1980, okay, if this is true then retire not going to work. Why? Why it is not going to work? Because this is an number, right? And this is a date. So number and date can I compare? No. Okay. So what you have to do here instead of providing the cell reference, you should extract year from this. Okay. I I'll show you year of this. I got the year. Now it is showing in a date format. Let's make it general. See, I got the year. Now what I need to say if this entire thing is less than 1980. Okay. anybody who joined before that then I wanted to say retired else I'll make it as an active okay something like that see I'll get retired and active if you take active employees no all of them join would have joined after 1980 only okay you see no value less than 1980 this way you can date okay look at here what I need to do I need to say any employees who have joined before 1980 then they should be marked as retired so First you should extract the year from here because I cannot directly compare date versus number. No. So let me extract the year. See I got the year. After that what am I doing? If this logical test this value is less than 1980 then I wanted to mark it as retired. Okay that's it. Now see I'll get a return and I'll also get false. But I don't want to show false. No. So I'll make it two double quotation marks. Enter. Now see like this. Okay, we still have a few more examples. Let's do them. Okay, look at here. This time I I have a different requirement. I have some students their IDs and their marks. So, if you look at the marks, any student who gets greater than 900, I wanted to print a grade A. Anything greater than 700 grade B. Anything greater than 600, grade C. Anything greater than 500, grade D. Any who else gets less than 500, I wanted to mark them fail. So here I have multi multiloggics right okay I need to check if this okay do this if this do this so here I need to write what nested if condition how do you write nested if condition okay here if this value is a greater than 900 what do you want I want something like grade A right I'll say something like a grade A done now if you can also provide grade B but that is not the requirement no I still have four conditions So whenever you see value if false where you write another if condition wherever you see value if false you write one more if condition and follow this syntax again logical test if this is greater than 700 then what do you want? I want a grade B. Done. Again when the value fall section you write another if. If this value is greater than 600 then I want a grade C. Again you are in the value fall section you write if this value is greater than 500 what do you want grade D now do I need to write another logic no because already I reached grade know there is one rate that one can be put it in the value if false section so here you can say something like a fail okay now you open the bracket with black color bracket you need to close the moment you see the black color bracket see failed right yes or no now if I send this down this guy also failed He's above 500 so he should get grade D. See like that you can okay then let's move on let's go to the another example okay here uh again this also comes with the same uh multiconditions see if the revenue is above 15,000 I want a good if the revenue is between 50,000 and less than 20,000 then I want good how do we do it let's see okay is equal to if this value is greater than 15k okay we know this right I can directly write good for false condition I'll make Make it empty. This we know. How about this? Greater than 15 and less than 20. How do I write this? If in here how many conditions I have? Two. When I have two conditions, how do I write the function? Not nested if. You can use and function. Okay. Is equal to and this value should be greater than 15,000. That is first condition. First logic. Second logic. This value should be less than 20,000 only. See then only it says see this is false because this is greater than 20,000. So when this whole statement is true when this whole statement is true I would like to say something like uh you know profitable anything up to you if not no nothing see only when it falls between my criteria then only it says profitable okay so I'm giving you this task first I'm providing the names you copy first okay you copy all of them once done okay next to that you copy these values okay done next So that you copy these values. Now you tell me >> okay here my requirement is if any student gets less than 15% or if he or she is absent for the exam they should be fail. Okay requirement is if a absent or less than 15% then he should be fail otherwise pass. How do we write this condition? Okay, here the requirement is okay either the student okay should get uh I mean if the student uh get less than 50% or absent any student is absent which means that he did not attend the exam so when he did not attend the exam how can he get less than 15% so when okay that is one thing if he getting less than 15% which means that he's not absent so less than 50% or a then fail no so use or condition. So or this value should be less than 15 50%. Okay, that is a one requirement. Or if he's equal to a then it returns true. See true truth and this is also true. When it comes this is false. See a is also true. If is true means what does it mean? He's fail. No. So if this is true then you can simply say fail. Else the student is pass the exam. That's it. So wantedly I okay gave this example. So any student who is absent is failed. Any student who is less than 50% is fail. So either of these conditions are going to be true. Okay. This way you have to write. So the correct formula is this. Okay. See here I have some applications and their sales. Here you see whenever the revenue is greater than 50 and less than 20k then good. If the revenue is 20,000 then it is exceptional. If none of this I want same value. Okay, I'll explain. So is equal to first one and condition. Okay, if this value is greater than 15k, okay, then this value should be less than 20k. Then what? It returns true or false then what do I want? Good. So what am I going to say? If whenever this condition is true, I want a good. Okay, I have one more condition. No. So whenever I have value false I'll write another if if okay this value whenever it is greater than 20,000 what do I want I want exceptional okay done if none of this criteria I want the same value so I'll click on this cell close the bracket see it is exceptional now I'm going to get the same value see this example will tell you numbers are aligned on the right side text is aligned on the left side that is the distinction between the text and number but yeah going Back to this example. Do do you understand how do we write the logics? So when multiple logics in one condition go with and one single logic you can go as it is. Okay. Similarly you have if error function and if a function what these functions are basically whenever you get an error what do you want to do? For example, okay look at here. This is my lookup table. Okay everybody. So I have uh you know employee number here and I need to get the name here. Okay, for this uh let's say this is uh employee number. Okay, this is employee name. Now I can use VLOOKUP to print the answer here, right? So here let's say I have something like A31. Here I'm going to say V lookup. This is my lookup value. E lookup value where we need to search this lookup value. I need to search in this table. Let's lock the table. How many uh columns are there within the table? Two. I want second column. So second column exact match zero. Now I got the result. For example, 831 there. If I type 835, see I got an error. But it it doesn't look good if you show the error as it is. So what you can do? You can use if error function. If error if entire this value is an error. Okay. What do you want to do? Put a comma. You can say no results. Okay. Now end user can clearly see it. No result. For example, if there is any number, it prints the answer. Okay. If uh you know there is no number it prints the no reason not only this okay even if you you can use if na also if na na is only for purely any related errors okay na is purely related to the any errors that is about uh you know this function understood how do we deal with this okay uh this example you can use if function also okay I okay not this one this one also you can use ifs okay try with function Okay, for example, if I say is equal to ifs a logical test. Okay, if this is less than 50%. Okay, what do you want? I want something like a fail. Logical test to if this is equal to a then I want something like a fail only. Okay, else I want to pass. See, I got a fail. Wherever I'm getting any error, I need to write another logic. So logic test the if this is greater than 50%. Okay then I want something like a pass. Okay I can uh get the same result using IFS version also. So wherever it is required you can also write IFS version as well. Okay. So we are going to start with the lookup functions. So the very first function that I would like to discuss is a VLOOKUP. Let's uh try understanding what this function is all about. Okay. So what is VLOOKUP? VLOOKUP is an Excel function that that look up a value in a table which is organized vertically. So basically you can look up a value and find the answers from a table. So VLOOKUP supports both approximate and exact match. What is this approx and exact match? We'll discuss. Okay. And remember your lookup value must be the first column within the table. So these are the three uh things that you need to remember. It supports exact and approximate match. The lookup value should be the the lookup value should always be always be available before your answer. Okay. So uh there are four arguments within VLOOKUP. So first one is a lookup value. For example, the value that you are finding. Okay. Second one is a table array. You can look up for a table array. Let's say you have three four columns like this. Okay. This is A. This is B, C and D. Okay. And here I have employee names here. Okay. Names. For example, if you're looking for the C column for this employee. So C column is located in the third column. So you will be printing this answer. That is column index. Next uh do you want exact match or approximate match? 99% if you go with the exact match only. There are only some scenarios we go with the approximate match. So when I go to any particular cell and start V LC, I get the VLOOKUP lookup value. Okay, that is the first requirement. Then lookup array, then column index and then uh range lookup that is uh approx or exact match. So you know when you read the definition, this won't be fully clear. Let's go ahead and explore the examples. Okay, if you look at here, I have this table. Okay, where I have product, supplier, price and quantity. My requirement here is I want you to get the uh price for this particular product. So if you look at here, okay, V rank is there. V rank is the product name that is the first column here. Okay, V rank that is the first column and my answer is in the third column. So always your lookup value that should be left side to your answer. Okay. Now here I'll go and type V lookup. So here also we are just finding the value and its answer. So what is my lookup value? V rank. Okay. Comma table array. You can select including the header like this. So this is going to be your table array. Okay. This is going to be your table array. Within this table how many columns are there? 1 2 3 4. Now tell me which column you want? Column C. In other words, column three. So I'll just press escape. Here you see wherever you see the range just lock it. Okay. Comma column C. Here I need to provide the column index number. So we cannot say C. It is asking index number. So you need to say column three. Okay. Comma. The last one you see range lookup. Do you want approximate match or exact match? I want exact match. So I'll take false or I'll take I can also take zero. Zero for the exact match. True is one, false is zero in Excel. and hit enter see I got 18.95 if I change this value to okay car lot see car lot is available over here so I get that answer so right now these are five records even if you see on the screen you can easily identify the number what if this is a huge list we cannot uh you know go by record by record no this way it will fetch the answer for you it takes the lookup value and print the answer one more time is equal to v lookup lookup value is this okay lookup array is this lock it I want this time I want quantity quantity is four comma 0 now it's going to print 65 that is the answer okay 1 2 3 4 but this this lookup array is there no that should always be left side to your answer okay one more time so I have seen many students typing the entire but that is not required so once you type v l see it is showing v lookup now press tab the very First one lookup value. My lookup value is over here. When you select a lookup array, your lookup array should start with this word only. So that word is there over here. So I can select my lookup array like this. You can select like this. You can select only values. Absolutely fine. Okay. Lock it. I want a quantity that is four, I want exact match. So zero or you type a false. Both are one and the same. See I get 65. If I change here something, okay, I get that answer also. Okay. Why why did I give four here? So what column I want quantity within this table? What is the column number of quantity? Four only. No this is one. This is two. This is three. This is four. So I should provide four only. Right? Here you can provide false or zero. Both are one and the same. See in Excel true is one. False is zero. Okay. Now let's move on to another example. See here the same example I have. Okay. But end user is asking okay. uh same is asking to do VLOOKUP over here. So we already know right. So v lookup uh oops is equal to v lookup lookup value is this okay lookup array is the what this is my lookup array within this what is asking price is located in third column and I also wanted to show exact match so I'll select false okay now see I got it but here v look up with the data validation I have provided what is data validation okay data validation is a big concept okay you can restrict the user input okay let's say if user is entering any value within this highlighted cell. He can enter one, he can enter 10, he can enter 20 also. But I can restrict what value to be entered by the user. Okay, that is data validation. In data validation, you can also create a drop-down list. Okay, a list of values. For example, I'll go to the data. This is data validation. I can go to the data validation. Okay, here I'll just select whole number, decimal, list. If I select list now here I can provide the values. Let's say a comma b comma c comma d like this I can provide and close it. Now say this is a drop-down list. I can use this as a lookup value. How I'll tell you. So here right now there is nothing but now see I'm going to add a data validation list. Go to this drop-down data validation list. Okay. Here click on the source. I wanted to add all of these values because user might select any item. Therefore your answer is going to display. So click on this source here and just select the range like this. Now see A6 to A10 are going to get added. Now see I got all the values. If I select quad I'll get the answer because here my lookup values this cell reference though there is a drop-down list here. You see though there is a drop-down list the cell reference is F6. So when I selected here F6 it is going to display. Now I I also have an option to change it. If I want V vang I can. If I want balance I can. If I want car lot I can. So this also you can add. So how to add a data validation. Okay. How to add a data validation? Go to data. Okay. Data validation and then uh select list add values using range or manual values. You should follow this. Clear? Let's move on. Okay. Now the very important thing. Let's say here you see I have this data set. Okay, where I have some student names and student marks also. Now my requirement is there are couple of students here for which you need to print the subject marks. Okay, let's assume this is the entire data set out of this data set. You are interested to see only marks for these students. So every student marks you can Okay. Okay. Naga Santos Naga Santos let's copy it over here and put it over here. some other student paste it here like that you can't do and if I change the name here I want to display the values automatically so you should fetch all these values over here so can I do V lookup is V lookup going to work in this case let's see it's equal to V lookup what is my lookup value here with cell my lookup value A22 yes okay for this guy okay a22 done where is my array this is my array okay do I need to lock yes because from here I will move this formula right side from here I'll move this from here I'll move like this I'll move no so I'll have to lock this entire range okay lock it okay now which column index I want what is the column index what is the column index two right very good because I want subject one subject one is the second column in the array right very good now for exact match what is the value I need to take okay zero close the bracket done but if If I drag towards the right side, why did I get any error? Okay, now I'm showing you the issue. What is the error here? So, we need to lock A22 because I'm looking for Naga Santos. Since I moved from here to here one, okay, I actually moved from here to here, right? So, therefore, this reference got moved. So, I need to lock you know this first cell. No, A should not change, right? So, put a dollar sign in front of A. Why not in front of 22? because 22 should become 23. I'll also drag this formula down. Now I'll drag this down. Okay. But unfortunately the problem here is I'm getting only the same value 62. Why is that? Because here I have provided the column index two and which is same across all the columns. So to avoid that what you can do you can take uh you know uh a reference column over here. Instead of providing reference like this you provide this. But again it it should change. Okay. Where to put a dollar sign in B20? Before B or before 20? Before 20. Why? Because I drag this formula down. No. So 20 should become 20. Uh sorry, this two should not become subject one. So two should be like that. So I'll put a dollar sign over here. That's it. Now see when I send this right, I get all the values. When I send this down, I'll get all the values. Why did not I get first? Because is not there. Here I have shinasumar. But here it is. One more time. I'm repeating first one V lookup. Okay, I'll select this value. Now I move this formula right now. So this Nagasantos will become this. This cell will become this. This cell will become this. So A should not become B. B should not become C. Everyone A should not become B. B should not become C. So where do I need to put a dollar sign? I don't want to shift between the columns. I want it always to look up a value that is Nagasanto. So put a dollar sign in front of A. Okay, very good. Comma. Next lookup array. This is array. So always lock your array. Okay. Very good. Which column index you want? Second column. I'll select this. Now when I copy this two will become subject one. No, that is wrong. Right? So two means 20. Row 20. Yes or no? So this row 20 later it will become 21. If you don't trust me, I I'll not lock it and I'll say zero. Now you see it is 62. No. When I drag this, it is working fine. But when I drag down, see I got error. Why? Because see earlier it should be two. No. Now what is column index? Column index is showing as subject one. Okay. Now if I just select this you see column index is showing as subject one. If I use insert function I cannot show all of this. But you see it is it subject one? No it it should be two right. So it is coming one cell down. So go to this formula put a dollar sign in front of 20. That's it. Now again you have to copy this formula right. Okay. And down. Now it works fine. Now uh here it is also did not find the bra. So what I have to do okay what you're supposed to do use if error function if if this is an error. Okay come to the end. See value means this entire function is an error. What do you want? I don't want to show it in anything. So put two double quotation marks. Now you drag it right. We send this right and send this down. There you go. Okay. But you may ask me question do I need to always create this helper column? Not required. But again this is a basic step. In the next example we'll try to improvise this like that we can go to complete dynamic array. Is it clear? But if you notice what are these 1 2 3 4 5 6 7 those are column numbers. Yes or no? Okay this works. Okay this works as long as you have subjects in the sequential order. What if you have subject two in the first place like you know uh subject two here. Okay, subject one here and subject uh you know five here and so on and so forth. Is it going to work? Absolutely no. It's not going to work in that case. So in that case, what do you do? You need to identify subject to position within this array. Okay, that's why we are going to make it dynamic. Okay, let's move on. Now here we haven't discussed about tables. Okay, I told you that we are going to talk about tables but we have not discussed. Let's first understand tables and let then see then we will see what are the advantages of using a table. So here I'm inserting a sheet. I'm pasting this data. Okay, right now this is a simple data set guys. Okay, I'll just move these two items over here. You can this is a simple data. You see you don't see any table options over here. Okay, you don't see any table options over here. But you can select this data either go to home and you know you can mark it as a form as a table. Okay, you can select either of the options here it is saying create table and it is saying that you are we need to ensure that my table has a header. Yes, of course my data has a header. Cancel it. Or else you can go to insert and click on a table. Okay, a table has been created. Now what is the advantage of a table? For example, here you see I'm just summing up all these numbers. See when I when I selected all the values except the last value it is G6 to G11. But the moment I select the last value you see it is showing the entire column. Now I I got the total but later if I add any new two rows. See this total I don't need to adjust. It's automatically going to get updated. That is the first advantage of the table. You're getting me. Next advantage for example here I'm creating a column called bonus. I'll say this into 10%. Now see 10% got calculated. Later if I just add okay later if I add any numbers here you see this calculation oops this calculation automatically going to takes place. See okay calculations will automatically takes place. That is the second advantage. Third advantage I'll tell you okay for example you have this data like this. Okay, I'm just converting it into Okay, I'm not converted to sorry, I'm not converting it to table, but I'll go to insert and create a chart. Okay, you see a chart has been nicely created. Very good. Now, if I add any new product, okay, Samsung. Okay, 35 uh 10. See, did I get Samsung over here? No. Yes or no? Because when I select this chart, you see only this amount of data is taken into consideration for this chart. But look at the magic. Now I'm just deleting this. I'll convert this to table. Insert. Okay. Table. Click on okay. Now I'm creating chart on top of it. Go to insert and recommended charts and click on it. Now this chart is based upon what? This this uh you know data set. Now I'll say Samsung. You see the moment I add I got Samsung here. The moment I type value I get it over here. See that dynamic it is. Understood. So these are the couple of advantages working with the table. Make sense? Again I'll discuss this table in a much detail before I discuss the private tables. But here I I I also have a already table. See once you create a table there is something that gets created that is contextual tab. What do we call? We call it as a contextual tab. Okay. So if you go to table design. Okay. You should be within the table then only you can see that. So I'll go into this table. I'll go to table design. See this is the table name and you get some options here. Right now we don't have to deal with all these options. But yeah I have a table here. Now if I do V lookup V lookup this is my lookup value. What is my array? This is my array. See right now this is still a range but the moment I select the last row you see it is showing table array. So here you can select a table or you can also select a range that is array. Okay done. within this which column you want? I want a third column. Okay. Do you want exact match? Yes. Zero. Enter. Now see I got 995. And and if I just change the values here, this is automatically going to work. Now if I add a new for example, I'll say OPO Reno 14G. Okay, this is my model. I'll say this is a mobile product, mobile supplier and the price is 35.69. Okay, I'm adding uh 1 + not 5G. Okay, this is also mobile. This is 41.25. Right now, see here, I'll simply say uh I'll simply copy this value. Okay, just copy paste it over here. See, I got it automatically. Why? Because I have selected the entire table. If I if I would have selected the range, it wouldn't have worked because my range was still here only. No. But right now since it is a table even when you add a new record right that automatically comes in. This is the advantage of the table. Okay. Now I'll I'll move on. Okay. This is good. Now I told you right. Okay. Uh the problem with this for example here you see here I have okay different subjects. Subject one, subject two, subject three, subject four and subject five. Okay. And my requirement is you need to fetch the marks for only Okay, sure. I'll go a little slow. Okay. I'll go a little slow. Okay. So here you see I have again uh five I have different students and I have five subject marks. What if you need to print a subject four, subject two and also subject one. Now these are not in a sequential order. No. But end user is asking I want report in this way only. Yes or no? So subject four is located in which column? Starting from here 1 2 3 4 5. So subject four is five. Subject three is four. So I can't do you know something like this. No. So when end user look at this report he might question what is this? So somehow you should include this column index number in the formula only. Okay. You may say that Santos we can hide this row. Yeah we can hide that. There is no problem. But what if end user changes this to subject five? In that case, this number is incorrect, right? Yes or no? So, you should come up with a way to get these numbers in a dynamic approach. Yes or no? Okay, let's do step by step. I'm just going to say V lookup. What is my lookup value? This. Where to put a dollar sign? Where should I put a dollar sign? Before. Okay. Very good. Okay. So, I'll say before. Awesome. Okay. Comma. Where is my table array? my table arrange this. Okay, let's lock it now. What is the column index? Column index right now. Okay, this is 1 2 3 4 5 6. Okay, six is my column index. Yes or no? Because I want subject five. So six, comma zero. Then enter. Now I got 40. This is well and good. Now I I can also send this down. Very good. But I can't drag this right. The problem here is I'll get the same result. So this five, this six is what? Okay, this six is what this six is. Okay, column index. So how do you identify that six manually? Sorry, how do you identify that six? Dynamically based upon the value that have that you have it over here. So this is where you are going to use a function called match. Okay, what is match function? Match function will give you the uh values position. As simple as that. What is match function? So v look up with match is a great combination. you should remember. Okay, match is Excel function which is used to locate the position of a lookup value in a row or column. Okay, that's it. So match function is a match is an Excel function that identifies the position of a lookup value. See Kumar is my lookup value where he is where is he sitting in this table. Okay, so if you count 1 2 3 four. So I want four over here. How do you get that? You write a match. What is lookup value shinas? Where is he? Within this entire range. Okay. And I want exact match here. So you can use a zero. Now see I'll get four. Same way I can also identify the position of subject five within this range. Now yes I can. But remember you are starting with the name right? So here also your array should go like this. See now I'm writing here something like a match of what is my lookup value? This subject file. Okay. Comma lookup array is what? Lookup array. You need to search from here. So always select from the first name. Lock it comma zero. Now see I got six. If I move this right. See subject two is located in third column. Yes or no? 1 2 3. See I got three. Next I'll move it like this. Now what you can do? You can simply instead of six you select this cell for example and lock it. Put a dollar sign in front of 55. That's it. Now you drag right. You can send this down. As simple as that. Or if you don't want this six to be shown up here, you copy the entire match function. Wherever you have that reference, just lock it. Okay. But here B56 should not come down. Put a dollar sign. That's it. Drag right drag down. As simple as that. Even if you delete this, nothing will happen. Okay? Even if you change this to subject three, automatically the result is going to be updated. Okay? I'll explain. So the very first first we will try to understand where these values are located within this array. Okay. So let's write uh v lookup. Okay. Uh so sorry match function. Okay. Match of what is my lookup value? Lookup value is going to be this cell. Okay. Let's lock it. Okay. I want it to I don't want it to come down. Table array is going to be this. Okay. Very good. Lock it. Exact match zero. So we have got the column indexes here. Right. Then I can do simple V lookup. So is equal to V lookup. This is my lookup value. Okay. I know where to put a dollar sign before A. Very good. Just select this array. Okay. Very good. Okay. Column index is going to be this. This one should not come down. So I'll put a dollar sign in front of 55. And for match I'll use exact match. That's it. Now you drag right side and uh you send this down. Okay. Here there is no one right. So use if error function. if error or if any if this entire function is returning any error don't show me anything okay for that I'm providing two double quotation mark send this right send this down that's it now if you want you can copy this enter formula and replace this column index that gives you a combination of V lookup with match again I'll show you much easier versions just wait for it okay let's move on okay so here uh this already we have seen if with any error so if If you get any error, how to hide it? You can use ifna or if error also. Okay, here I have a part number and I have a sales. I should get the product name over here. Okay, so if you look at here I have part number. Here also I have a part number using which I need to do the product name. Can I do it straight away? Is it going to serve the purpose? No. Why? The problem here is here the part number is different. Here the part number is different. But if you closely observe the part number is going to be first four digits then it should match right. So what should I do now? Because this and this should match now. Okay. So here I need to get the first four characters. So use left function not right. Uh guys left function. Okay. Left of this is my text. How many characters I wanted to extract? Four. Okay. I got four characters. Okay. Now I got four characters. Here I'm using V lookup. This is my lookup value. Very good. This is my array. Very good. Comma. I want a column index is two because there are only two columns. Exact match. Done. But I'm getting any error. If you look at here 1498 1498 is there. No. So sunshine supposed to be here. But I'm getting error. What went wrong? So I told you right whenever you extract a text from the like you know a part of text from the whole text it's going to make it as a text only. See right now 1498 want I aligned it on the right hand side just to confuse you but this 1498 is there. Now when I select it do you see 1498 is showing up in the double quotation marks at the bottom here. You notice guys? Okay 1498 is showing in the double quotation. So we have already learned anything that is within the double quotation marks is a text. How do you convert that to proper number? Either you paste these values copy and paste it as a values or what we have to do value function. So wrap up this entire left function in value function. See the moment I do and send this down I'll automatically get this. Yeah 90 is not there. Therefore I can use if error function. Okay. If error if if um this entire V lookup is an error put a comma at the end and say no here I'm I'm writing something like no results you can write anything you can keep it empty you can write anything guys okay up to you okay uh for no results uh I I'm just highlighting okay I'll say something like no results done okay so these kind of uh things also will come now now this is a text string so these are like a text strings right so this is a text only this is not a numerical number because numeric alpha numeric this is because there are some alphabets there are some numerical values whenever you extract a number from a text right it is going to keep it as text only see I'm going to say now for example I'm using right okay this is the text from which I'm I'm looking to extract right side eight numbers okay see right side eight numbers are extracted okay but my when you look at This this is looking like a number. But when you copy them and right click and say paste as values, you see you get this green color indication. This means that this is a text. If you don't believe me, you can use is text function checks whether a values text and returns true. Okay. And provide this value. See it is saying true. How do you convert them to proper number? You can use the value function. Value of this. This will convert a text represented number to number. So immediately this will be aligned on the right hand side. Now this is a number. If you want to check you can check is number of this. So we are asking whether this is a number or not. See it is a number. This way you can do it. Make sense? So whenever okay I'll write it here. Whenever you extract number from alpha numeric value, it makes it like a text. Okay, you should use a value function. Okay, you should use a value function to convert it to number. Okay, let's move on and learn couple more examples. Okay, here if you take a look at it, I have a two tables guys. Okay, one is state table, one is city table. Okay, alt hmc. I'll make it as a state table. Uh this one is a city. If you look at for every customer there is a state and for every state there is a city. Okay, my question is I want you to print the state for city for all of these. What should I do now? If you look at output tables for every customer I have a state. So what you have to do? So first print the state and look up the state and print the city. So V lookup inside another V lookup. Getting it? Look at here. First V lookup. This is my lookup value. For each of this lookup value, print the state. Okay. Second column. I want zero. Done. I got K. No. So here I'll write another V lookup. This time what is my lookup value? K. Where is my uh return array? Here. Okay. Lock it. 2, 0. That's it. Lock the ranges. So let me lock the ranges as well. Lock it. Done. Now if you send this down, see you will get it. But instead of doing it in another cell, what I can do? I can remove this. This is going to be my lookup value. So I'll say V lookup. See the lookup value. This k is going to be my lookup value. And this is going to be my array. Okay. Comma table array is going to be this. Let's lock it. Second column exact match. That's it. There you go. You can send this down. That's it. Understood? So V lookup inside another V lookup. Y2 I'll tell you the first using name I'm printing state. So here I'll get a state that state will be my lookup value and I can print uh city. Understood why I use two lookups. So the answer of first lookup value is going to be my lookup value for the second lookup. Okay. So now we look up with so far we have seen with exact match because in the real time most of the time we deal with the exact match only. Okay. Now we will try to understand with approximate match. Here you see I have some sales. Based upon the sales I need to print the commission rate. Okay. We have already headings over here. You see uh V lookup with approximate match. If I go here see if and V lookup nested function. If I see nested VLOOKUP function use the customer name to get the city. Okay. If I go here you see using the table to get the product name partial lookup. I have provided the headers for a better understanding. Okay, see here in this example what I wanted to do. So here I'll enter the value. I need to get the bonus commission. But is 58 over 58,000 there anywhere over here? No. Right? If I use V lookup with exact match see the problem 58,000. Okay, I'll just provide this two. Okay, this is my table. Lock it. I want second column. I want exact match. Since 58,000 is not there, it is returning an error. But instead of zero if I just say room what will happen? See I got 6%. So what it is going to do? It will just look out for the I mean it it will just look out the higher value than what we have. So what is the higher value than 58,000 60,000 so the cursor goes here and then it goes one cell back. So for 50,000 what is the given percentage? 6%. So it returns that. Okay. So 58,000 what is the highest value? 60,000. So the cursor comes here and goes one sale back. So what is assigned? 6%. That 50,000 or more all the way up to up to but not including 60,000. Okay. Now if I add 47,000 here. Now tell me what is the percentage here? I'm going to get so what is the value of 47,000? 50,000. So what is the previous value to the 50,000 is 5%. See 5%. If I add 5,000 now tell me what is the percentage? Zero. So this way approximate going to approximate match going to work. That is the reason I have given given this limits for better understanding. Okay. Now on the same lines you also have uh something called HLOOKUP. So in the VLOOKUP you are going to enter the column index. In the HLOOKUP you are going to enter the row index. That is the only difference. Okay. And yeah H lookup we rarely use in the real time but it is good to know. Okay. Let's let's do one HLOOKUP example also. See here the uh the excel hookup function finds the relative value from the data okay in a horizontal table. H stands for horizontal. Now here if I say h lookup okay lookup value signs. Now look table array is this but this time we are not going to see 1 2 3. Instead we are going to see 1 2 3 because it is giving row index. Okay. Lock it. Which row index you want? Science know starting from 1 2 3 4 science is four okay approximately zero okay done okay science spelling is incorrect why did I get uh this error s c i e n c e okay let's validate that lookup value is signs that is okay table error is this okay row index is four okay range lookup is zero row index is three yeah but whatever even if I provide a three or four it should print Oh, once again let me write hookup lookup value is going to be this. Look up a table array. Uh let me enter table array from here. This is my table array. Now you will get it. Comma row index is going to be three. See always a row number. This is my lookup value match. This is my table array. Okay. Row index is going to be 1 2 3 4. Okay. This is row index not 10. Okay. You should count within this array. Okay. Not the entire one. Uh range lookup is zero. Okay. Okay. I think uh I think I should do it in a reverse way. Is equal to H lookup. Lookup value go. Okay. Uh I'll have to look within this array. Okay. For this row index is going to be maths. Maths is located uh in third position. Exact zero. See now I got it. I did it in incorrect way. Okay. Here my lookup value is goina. Gopina there in within this range. Okay. Row number is what? 1 2. Okay. Three. So for science for gopina what is the answer? 53. Okay. So did I get 53? I got 74. Why? So lookup value is okay. Table is okay. Row index is three. 1 2 3. It should be four. Right? Okay. Let me enter four. I got 53. See? Uh, okay. This is not maths. I should show it as a science. Okay. Yeah. This way you will get it. Okay. It is just a horizontal. Okay. It is just a horizontal lookup. Okay. In the same way here also I have you know uh different employees. Okay. Their year uh and you know some employee name. I have employee contact number. All of that. Here I need to print all these results. So simply I can use hookup. My uh lookup value is going to be this. I'll just lock it. Okay, I don't want this to move by number. Uh table array is going to be this. Okay, let's lock it. Okay, row index is going to be I'm going to say uh here row index is going to be two comma 0. Let's say what am I going to get? I got naga center. But when I drag this down, row index should become three. Yes or no? So what do you do? You do one thing here. You add 1 2 3 4 and five and six. Something like that. So instead of row index number, you provide this and lock the column B. That's it. Now you can send this right and uh you know send this down. You will get it. But yeah, we need to use V lookup, Hookup, index match, right? All this can be avoided by XLOOKUP. Just wait for it. But as of now you understood this. Instead of VLOOKUP, you use HLOOKUP. Instead of column index, you provide row index. That's it. Okay. So, uh this is about VLOOKUP and HLOOKUP. So, whatever we have learned so far, I am going to show you another version. Okay. Uh let me explain you with another function. So, already you learned like you know match function, right? What match function does which gives the position of a value. Similarly, you have one more function called index. What index is going to do? A match function will take the value and provide the index. index will take the value and provides its number. Look at here I have for example I wanted to know for example what is the position of B? We know right? So it's two. I can say match. This is my lookup value. This is my table array. I want exact match. See it's saying two. But what if I what if I say tell me what is there in third position. So third position we have seen know how do you print? You can use index function. See index array means where it it has the two syntaxes. Let's go with the first one. Array means wherever your answer is. Okay. Comma. Row number. Which row number you want? What is there in the third row? That's it. Column number. If you have a column then you can provide otherwise you can ignore it. Close it. Close it. It takes the number and prints the answer. Okay. Opposite to match function. When we do some examples you will understand the very first one. Okay. Look at here. I wanted to print uh the sales for art. So how do we do it? Let's see now using V lookup this is possible. Yes, using index function also this is possible. See index array means our answer. So where is my answer here? In which column I have my answer column B. So select it. Okay. Now I'm not dragging right left. So I don't lock it. Now for what row number? Where is art located sixth position within the selected array. Meaning that see you have selected from here to here. Right? Now count from here 1 2 3 4 5 6. So I should provide six. No. Now tell me is there any function that gives six? What function can give me six? Match function. You write a match. What is lookup value? Art. Where are you looking? Within this array. Both array should be in sync. Exact match. That's it. Now see without using V lookup also I got this answer. Okay this is the first example. Now I'll show you another example. What if your look okay for example here you see I have category you know category called appliances and I wanted to print the profit. So here I have two criterias yes or no. One is profit I need to print one is category. Right? So if I do V lookup what is my lookup value in this case here my lookup value is going to be appliances because it is first starting with the subcategory only know so appliances is there over here right so lookup value here first appliances table array is this now this is where you need to think what is the column index which column you want first column second column or third column which column you want why three because we are looking for profit how can I print three over here I want I don't want to manually input I want automatically it should come use match function match lookup value is profit but you should just start from here okay lock it zero that's it okay now for exact match I'm taking zero close it now see you got for appliances you got it now if I change this to sales see how did I add sales and profit over here again go to data this this option data validation Click on a list. Add two values. What are those two values? Sales and profit. Click on it. Now if you select sales or profit, you will get the exact value. See how dynamic it is. Okay, this is using V lookup. Without using V lookup, how do we do it? Okay, that we will do it over here. Is equal to index. Now array means wherever my answer is. Yes or no? My answer can be anywhere over here. No. So I'm going to select both of the columns. Lock it. Now I need to provide the row number. What do I have it in the row number? I have my subcategory. So write match function. Lookup value is this. Okay. Lookup array is going to be this comm, zero. Done. I also have to provide column number because I have two columns, right? Sales and profit. Write another match function. Lookup value sales where you need to select from year to year because why did I select only sales and profit? Why not subcategory? Because my array has only two columns. No. So here also it should be two columns. Okay. So comma zero. Now see one one match function will provide me the row number. One match function will provide me the column number. Therefore you will get the same result. Okay that's it. So you need to use match function. Let's move on. Okay. So here you see I have a different descriptions here. Looking at this description I need to print the product. Is this possible with VOC? Okay. If no why it is not possible with V lookup because your lookup value should be always on the left hand side and your answer should be on the right hand side but here your answer is on the left side of the lookup value therefore it is not possible say we look up lookup value is this but when I select a table array even from left to right you see the first I'm getting B5 so it's not going to work in this case then what is the alternative approach index so my answer is over here what is my row number use match function to identify the row number of this. Okay, exact match. See if you have lookup value which is there on the left side your V lookup is not going to work. If your answer is on the left side of lookup value this is my uh lookup value my answer is over here. Therefore V lookup doesn't work. So your answer should always be on the right side then only V lookup works. The better alternative is index and match. Okay moving on. What if I have something like this? Okay. What if I have something like this? This is a slightly complicated example. See here I have a different applications. Okay. And their actual sales, actual profit, budget sales and budget profit. Here I have a budget word. Okay. And I also have actual word. So here I have something like sales and profit. Okay. Here I have appliances. So for that I need to print the value. So what I have to do here? So here the lookup value is going to be actual sales as one word. Okay. So for that what am I going to do? I'll say index where my answer is. My answer can be anywhere over here. Okay. Lock it. Comma. What is the row number? Row number I can easily identify no yes or no. Where is appliances located that I can print using what function? Which function? Match function. Lookup value is this. Lookup array is this. Very good. comma 0. Done. Row number we have provided. Now the column number. See this is one column. This is one column. This is one column. This is one column. Budget profit is fourth column. But how would I print that budget profit? You can write another match function. Okay. Here budget join that with profit. Okay. Now the lookup value is budget profit. Okay. Comma lookup array you can select like this. This join that with this. Okay. Now when I select this lookup array, you can see actual sales, actual profit, budget sales and budget profit. S or no. So your lookup value is there within this array. Yes or no. See this is budget profit that is there in this. This way you need to select. Okay. Com, zero. That's it. Close it. Now see 363. Okay. 363 for appliances. If you sales to actual profit, see you'll get 240. As simple as that. Okay. If you're not able to do it one go, you can do it like this. Okay, you can join these two words over here. Okay, now this is going to be your array. Okay, here I'm doing it one more time. Index, this is where my answer is. Okay, row number I'll write match function. Look out for appliances within this entire array. Com, zero. What is my column number? I'll use another match function. Lookup value is combination of these two. Okay, use amp% operator. Lookup is going to fit this comma zero. This way you can even take the helper column also. It's one and the same. Okay, let's move on with some more examples. Okay, here I have employee ID. Okay, employee names. Here I have employee uh name and employee email address. Okay, email address table. Okay, now how can I get this email address? See Naga Santos is located here. His email address is located here. Romesh is located here. His email address is located next to him. So can I use index or V lookup to do this? Is V lookup going to work? No. Right? Because my lookup value and answer is available in the same position. Let's use index arrays where this here my answer is there. So let's lock it. Comma. Now I need to provide the row number. See if I am able to identify the position of Naga Santos and adding one to that answer is going to give me his email address only. No, yes or no. Okay, I'm saying match look up for Naga Santosh within this. Okay, lock it, comma zero. Now if you look at here, what is the answer? If I just select the row number one. If I add one, 1 + 1, that is Nagasanto email address only. Now, so let's add one to it. Okay, close it. See, I got Nagasanto. If I send this down, Romesh Babu is located in the third position. Okay, if you just look at the row number three, Romesh Bob is located in the third position. Adding one is is going to get me the email address. As simple as that. You can send this. Sometimes you'll also get interview questions like this. So, you should just think of a logic. That's it. I'm just finding this guy's position and adding one to it. Okay, definitely under his name only we will have the email address. No, under Nagasant we will never have Romesh email address. That doesn't make any sense at all. Right. Okay. So, so far uh you know we have seen VLOOKUP and the better version index and match. Now I'll show you the very advanced version that is XOOKUP. Okay. Now what is XLOOKUP? It is one of the most powerful and flexible functions in introduced in the recent versions of recent versions of Excel. Uh I think that is from 2019 and even you have it in the office 365 also. Okay. Now what is XLOOKUP? XLOOKUP works like a VLOOKUP. XLOOKUP works like a HLOOKUP. XLOOKUP works like index and a batch. Okay, you can do in all the all the you know possibilities. For example, here you see I wanted to print the central sales. Now using V lookup I can easily print it right. But see I can also do it with X lookup as well. Okay. If you say X lookup what is my lookup value here. Here the advantage is your lookup value array is different and your return array is also different. So both are independent. Lookup array. Lookup array means where is your lookup value available within this array. Done. Where is return array? Here it is. There. Now see when you look at the central what is assigned to central that it is it is going to print. See lookup value lookup array and return array. Okay. If not found. If not found you can say no results. Okay. No results. That option is also there. Match mode. You want exact match or you also have advanced options. Exact match or next smaller item. Exact match. next larger item. Okay. Like this. Like that also you have you can ignore it and you can say search first to last large last large to first. You can also do this. I'm ignoring that now. See I got central sales. For example, if I say central north this is not available now. See now you will get no results. See that is it is let's do some examples here. I need to print south sales. Okay. South sales uh for sales 4. Okay. is equal to x lookup lookup value is south where is south available that is your lookup array written array is what this is written array wherever south is there it is going to print that value see 324 for example what happens if I select this entire range okay let's check that now so I'll go here instead of uh this I'm going to select the entire array so now what is going to happen for a south we have sales 1 2 3 4 now you'll get all of the four values into separate cells. But ideally, if you go and see, you can't see anything in that cell. Okay? Because it is spilling the values. Let's say I want total sales. Total sales for S. Yeah, it it's spilling all the values. Now, what is your task? Put the entire function in the sum function. Now, we'll get the total summation. Is this possible with VLOOKUP? Definitely no. Okay. Not a straightforward option. Okay. Let's move on. Okay. So what if my lookup value is on the right hand side? In the previous example, my lookup value is the left hand side to my answer. But this time, what if my lookup value is on the right hand side? No problem. I don't even need to use uh index and match also. Lookup value is south return lookup array is this return array. I want sales one. No, I'll select sales one. That's it. Okay. I don't have to worry whether it is left or right. It's still going to work. See for south sales one is 891. As simple as that. Which one is easy? V lookup or X lookup? So far X lookup. Now you are going to see more uh more options. Okay. Now I have student marks here. Okay. English, maths and social. What if I want to print the entire list for the student? Okay. It is easy. Simply say XLOOKUP. Lookup values lookup array is this. Now return array. Do I need to select all the three columns or only one column? Right. from English to social all three right so select all the three like this okay let's lock it okay comma if not found I would like to say student name student name is not available okay like this I would like to say okay see 76 17 56 if I change here something like you know student name see I'm getting the result for example if I say Kumar see not available it is printing how dynamic it Okay, this way you don't need to drag the formula, right? You have you don't have to worry about the indexes, all of that. It is going to work. Okay, here there is no dependency with respect to the columns. Even if you delete it, nothing will happen. There is no dependency. But we are printing it in a sequential order. No, that is the reason I have provided the names. Okay, because here nowhere you have something like you know if even if you see this is not into the next cell which is spilling the value. If I go to the edit mode, no value is seen here. But this way, this is one of the way where you can print the result. Okay, the formula is this. Look up value, look up array, return array. Okay, let's assume uh we we got 73 84 all of that. So for example, what if okay I got these three values. Now these three values should be based upon this headers. Then what you can do? You can write x lookup lookup value is English lookup array is this and okay your return array is going to be this okay the output of this for example I I'll do one thing first I'll copy the this entire xookup copy I'll go here okay see lookup value is English lookup I is this my return error is going to be this okay close it now see I got 73 for English if I just to put maths over here. You see I got 84. If I put social over here, I get 57. Okay, this way also you can do it. Okay, this way also you can do it. For example, I'll just copy that formula. Uh I'll just move this over here. Okay, now I'll drag this right side. Okay, now uh if I just come here. Okay, it is maths. Okay, I just need to lock uh this one lookup value H. Let me lock it. Yeah. Now if I just to move this towards the right side. What went wrong? Okay. Ranges also I need to lock. Wait a second. Uh this range let me lock this range. Let me lock. Okay. Let's send this right. See 84 and 57. If I take nasi for example if I change here okay wenat ramsh. See for example I'll change here something like you know social. Okay. Uh English. Okay. Maths. See I'm getting it. No, for wa tish social is 88 50 to 50. So first you print the answer okay this return array that will be your uh you know return array okay and then you look for this headers one more time first we will print the x lookup okay we will print uh this is my lookup value this is my return array okay lookup array this is my return array okay now see I got all the values now what am I going to do next time my lookup uh lookup value is going to be is return array is going to be this and lookup array is going to be that written array is going to be this okay see I'm going to say x lookup lookup value is social lookup array is this return array is going to be the answer of my first lookup okay and uh lock it now see I got 88 now in order to move this first I'll have to lock all of them properly okay let's lock it uh let's lock it also Okay, done. So now I can drag this right side. See how nicely I'm getting. That's it. If name is not there, you can also say here you can say no result. For example, if I change it only Romesh. See no result. That's it. Okay. I mean again you have asked with respect to the header that is the reason I have shown this example. Okay. Yeah. Let's move on with another examples. Okay. So uh yeah this is also the same example. Let's move on. Okay. Uh okay if we don't find we can also say something like a not available that already we have done but yeah let's do it in a different way. Is equal to x lookup my lookup value is ten over there here let's check written error is going to be this. Okay, see I get I'm getting any instead of any error what I can say if not found I'll select this value join that and say is not exist okay that's it now see doesn't exist but for example if I type here my name I'll get the result as simple as that you can provide the headers here it makes more sense if you type uh something like uh Ranjit Kumar see you're getting the answer let's do few more example so that you can understand it is easily. Okay. Now see here I have two tables here. Okay. And I have one person here. I want to check within this table. If not I wanted to check within this table. Okay. Can can this be done using VLOOKUP? No. Right. Now look at here. How am I going to do? Okay. So here I'll come and I'll say XLOOKUP. This is my lookup value. This is my return array. Lookup array. This is my return array. See that person is not there. Okay. when is not there when this is any error what I have to do okay if not found no see the fourth argument is if not found no here I'll write another x lookup because I haven't found no so x lookup lookup value is going to be ranjit within this array okay written array is going to be this that's it okay close it now you can close it and say enter see for ranjit 6743 what if I type nagasantos nagasanto in the first table I the marks. So if not found see lookup value lookup array return array. Very good. If not found you start another v lookup with another table. Okay. When value is not found. When value is not found use another x lookup. That's it. Okay. So this is not possible with V lookup. Therefore I am showing with the XOOKUP. Okay. Move on. So here you see I I let's explore the other parameters of a V lookup. Here I have different names and the different departments and the hire date. Now what if I want most recent hire date? Okay, most latest hire date. Meaning that for example if I filter right click go to filter and select a cell value. I filtered by operation. What is the latest date here? What is the highest date? 15th only. No I want that 15th. Now think of VLOOKUP. Can V lookup get that highest date? Do you think that V lookup can do this task? Okay. Now see X lookup can do that. Look at here X lookup lookup values operations within this range of cells. Okay a return array this date. But here you see we know that see whenever we are printing the dates. Let's say today you go to the shopping market one record will be created with okay uh with your name with today's date. Tomorrow you go with your name this and after tomorrow you go your name and date. Okay. So dates also going to going to be there in sequential order or no? So the maximum date will be at the bottom only. No. So now here you see return error also we have printed comma. If not found you can say no data. Now here you see exact match I'm taking. Search mode. Do you want to search first to last or last to first? Which direction you wanted to go? First to last or last to first to get the maximum which direction top to bottom or bottom to top. So you have to go from last to first. So select last two first that is the use of this parameter. Now see I got I got this number but again this is in the general format that is the reason you get uh uh you know this number but every number is a date in Excel. Go to this dropdown and select a short date. See you got 15. If you say first to last you'll get the starting date that is 0 to 01. That's it. Understood? What is the importance of uh matching match mode starts first to last last to first this is the advantage move on with last two example okay here you see again I have approximate match if any sales is above 5,000 if it between 5,000 to 10,000 what percentage I'll get let's check that okay x lookup lookup value is going to be 6500 lookup array is this okay return array is this if not found no data Huh? Okay. Here you see you have more option in VLOOKUP. It goes to the highest value and comes back down. Now, so here you can decide exact match or next to smaller item. That is V lookup. Right. Here you can also have option to choose exact match or next larger item. If I say next smaller item okay done. Okay. Close it. Now say 6,500 why did I get 2%. What is the smaller item compared to 6,500? 5,000 only. No. Okay. For 10,500 what percentage I get? 4%. For 39,000, what is the smaller item? 7%. But if you want larger, you can also choose. But do you have this feasibility with the VLOOKUP? Vlookup is limited to only smaller item. No. But X lookup, you have you know option to choose next, smaller or bigger as well. Okay. Now tell me V lookup is better, H look sorry index and match is better or X lookup X only. Right. Yeah. That is the reason I wanted to take in this hierarchy. First V lookup then match then index and match then X lookup. But if we would have done X lookup starting it won't be uh you know that much clear. Okay. Now what if the direction is different? Now we have you know HLOOKUP direction. No can I get this? Yes. You see XOOKUP I wanted to print all okay this person marks here. Lookup value is Alex return array is this. You can okay look up return array I want all of the marks to be printed close it now see for Alex I got Alex marks here okay for example within this I wanted to look at this one okay this will be our answer write another lookup function at the starting x lookup lookup value is physics return array is this lookup array is this return array is going to be the answer of the existing lookup now close it now see you are going to get only 88 that is physics physics. This way you can do it. At first you you find multiple values out of that you restricted to one subject as simple as that. Clear? >> Just a quick info guys. Intellipath offers data analytics course in collaboration with iHub IIT RII. Through this course you'll get to learn data analytics from industry experts and IIA faculty in depth. You'll learn tools like Excel, SQL, PowerBI, Python and K9. This data analytics course is praised by our learners and their user testimonials are available on our Intellipath achievers channel. You can check them out. The link is available in the description below. So if you are really interested in learning data analytics, this is your opportunity. Click on that apply now button right away and get started in your data analytics career journey. >> Now we are going to discuss about private tables. So basically private table is used to summarize the data. Okay. A private table is used to summarize the data. So when what is summarization? For example, let's say I have a different products. Let's assume I have a different products and sales. Uh my product let's say you know uh mobiles, okay, uh laptops, okay, keyboards, okay, and pen drives something like that I have. Okay. Now, uh each of these products is sold in many places. So collectively I have their respective sales over here like this. Now what if I wanted to summarize the total sales. So if I wanted to know what is the total sales amount that I have received out of these products. What do we do? We sum up these numbers. Right? So here we got the summarized value. Even private table can also do the same thing. It summarizes the data by different um you know products. For example these two products. Let's say uh uh you know let's imagine these are like a hardware. Okay, this is also hardware. Next for example, let's consider this is a software item. This is also software item. Now if I wanted to get the summarized view for this, so this is somewhere around 11,000. Okay. And uh uh oops yeah 11,500 and the other one is 7,000. So here if you see we we got the total summation for my hardware and total summation for my software. Okay, for example, if this is my data set, let's say uh I have uh Okay, for example, here I have a Samsung phone. Here I have OnePlus phone. So these two belongs to mobile, a product called uh mobile. Okay, this is also mobile and this belongs to computers. Okay, for example, if this is my data set, end of the day, if I ask you what is the total sales that I am getting from these two products, so what you are going to do? you will just sum sum up these numbers right now in total okay I'm going to get the uh total sales for mobiles total sales for computers so here I'm summarizing the data in another words grouping the data okay so whatever you are doing with your sum if same thing your private table also going to do so if you get any question asking about what is private table you can say summarizing the data or grouping the data now there are two prerequisites in order to Here the private tables. One, your data set should contain headers for all the columns. Okay? And there should not be any blank rows and columns. Okay? There should not be any blank rows and columns. For example, here uh I have this orders data set. Okay. I I earlier also we have used this. So here I have this orders data set. Okay. What is this data set all about? So here I have a sales information okay uh I know from different stores okay let's say here I have a region okay and I have some geographical details like country state city and I have some product information and I have some measures like sales profit okay so like we all go to supermarket and buy products right so and they will scan the barcode so these are the some of the details which will be entered into the database so in this case all these details are captured into uh Excel. So a row ID, order date, month name, who is the customer, okay, what is the segment that product belongs to, all of this information. Now if I ask you what is the total sales, okay, from this data set, what do you do? You can simply go to this column, select the sales at the bottom, you can see the sales, right? These are the total sales. What if I ask you the total sales in east region for example? So what you can do simply apply a filter over here. Select the region. Okay. How did I apply a filter? So you just go to the data and click on this filter. If I go to the region and select central and click on okay. All the data that belongs to central is filtered and you can just select the numbers. Here you can see the total yes or no. But every time do I need to select uh at I mean apply the filters and select the data and uh give the numbers. No. Right. So we need to provide it in a summarized view. We need to provide it in a summarized in table view. How do you do that? In order to do that, you need to make use of private tables. So where do I find my private table? If you go to the insert, there is a section called tables. Here you can click on private tables from the table or range. Okay. If you click on the recommended private table, so what it is going to do, it will just select the data. You see the moment I click on the recommended private tables, it is giving some samples. Okay, region wise sales. Okay, segment wise sales. How do I know that this is a segment? For example, if I just click on here, you can see sum of profits by segment. If I click on this, see sum of profits by uh you know, state or province. It is giving uh you know a couple of suggestions. If you want you can go. Okay. For example, I'll just choose region wise profits. Okay. See in a new sheet it has created a private table. Okay. So all the regions in rows and profits into the value section. I'll explain this but for now let me delete this. If you want your own private table to be created what you have to do simply click on Okay. To uh to create a private table on your own, click on insert and click on this pivot table. Once you click on a private table, it will first ask you is it your table or range? Yes, orders A1 to A10195. Okay. If you look at here, the the last row is uh 10,195. Yes, that is my range. Now, it is asking where you want the pivot table to be placed. It is asking where should I place this pivot table in a new worksheet or existing worksheet. Let's say since this is a huge data I would like to have my private table in a different sheet. So I'll click on a new worksheet and just click on okay. Now you see here a private table got placed. Okay. Along with the pivot table you'll also get another window that is pivot table feeds. This is the okay this is this should be our first observation. This should be our second observation. The third observation should be okay. The third observation should be this one. Okay. Contextual tabs. Okay. What are these? These are contextual tabs. Contextual tabs. Okay. So this pro these contextual tabs provides a different options. We will take a look at them little later on. Now what do we call this is a canvas. So whenever you drag and drop your fields, right? So here is where the private table is going to get created. Now coming to the second second observation that is private table fields. Here you can see all your fields. Okay. Row ID, order date, month name, customer name. So that is the order that you have it in your data data source. Row ID, order date, month name, customer name, segment. Okay. So all of your fields are going to be shown in the same order. Okay? If you want to search any particular item, let's say I wanted to search something called category. You will see the moment you type it here those things are going to get uh filtered out here. Okay. All your fields are going to be shown here in the same order. Next you will have four different shelves. Okay. We call these four as a shelves. One is filter. Okay. filter. Uh you once you create a private table, if you want your private table to be filtered by any specific field, you can drag that field and drop it here. Rows and columns talks about the orientation. How do you want to see your private tables? Then you can use rows and columns. Values is the section where you can drag your measures. For example, I wanted to show region wise sales. Now I can search for region, drag and drop it into the rows. Now here if you see it is showing an icon here. Okay, rows. Now whenever you drag any field, it is going to show you the members of the field. For example, region will have four regions. So east, west, south, north, these are going to be shown one underneath another. Why one underneath another? Because rows are one under another, columns are one beside another. Right? And second thing is whenever you drag any field private table is going to show you unique items only because it has to summarize the data. No for example east region. Okay for example east region is there in 20 2986 times. But however it is going to show you only once. Now if I search for sales and drag and drop it here. See it is a sales. The moment I I drag and drop it over here, it becomes sum of sales. You see now it become it has become sum of sales. It is going to show you the summarized value, aggregated value. Okay? What are the central sales? What are the east sales? What are the west sales? It is going to show you. You can simply right click on this, go to number format, click on number, remove the decimals, use thousands operator and click on okay. Now see it is going to show you very nicely. Okay, make sense? This way you can summarize the data. Okay. So p what are private tables? Okay. Summarizing the data. Okay. Summarizing the data. So private tables will always gives unique values. Okay. Measures will be aggregated. So using this raw data I'm going to okay I have summarized this. Now I have a view. Now if end user asks what is you know central sales I can quickly show show this value. Okay if I just provide this table they can easily get to know how much sales are coming from each of these regions. Okay for example you have closed this window private tables fields. How do you get it by you have to go to private table analyze click on this field list or else simply right click on this pivot table you can see the show field list it will come back. Okay. Now I wanted to filter this by for example category. Drag category and drop it into the filter section. Here you see you get again select anyone and click on okay. Now this is the central sales for furniture. If you want select multiple items, click on this select multiple items and make another selection. So here you see multiple items meaning that you have selected more than one value. Right now I don't want this. So I'm just removing it. Now we'll try to create a private table and we'll make it look little better. Okay. While doing that we can learn more options. For example, uh I Okay, let me take this entire data set into a new sheet. Okay. Let's say this is my data set. I'll say orders data set. Done. Now I wanted to create a private table. All right. So here I want I would like to create a private table that shows region wise, statewise sales. Okay, something like this. Okay, as you see on my screen. So here I would like to see region within the region. What are the different states and their sales and profit I would like to see and I would like to apply some formatting over here. So how would I do that? Let's check that now. So where do I need to go and create a private table? You have to go to insert. Okay, you can also check recommended private tables, but it may not give you the private table that you want for your analysis. So, I'm just going to create this. Click on this pivot table. Since I have a huge data, it is always better to put this private table in a new sheet. Later, we will also create the private table in the same existing sheet. But for now, I'm just clicking on new worksheet. Click on okay. Okay. As I said, this is the canvas and this is my private table fields. Now I'm going to give you a tip. For example, if you wanted to change the look of this window, okay, you can do that. Simply go to these options. Here you see field selection and area section side by side. Whenever you click on that, here you can see all your fields. Okay, here you can see all your fields and you know uh the shells also side by side. Okay, field section only. You can see only field section. But what do you do with this? Okay, where do you place all of them? So not so useful. Area section only 2x2. Okay. And uh area section 1x4. Like this you can do but I always go with the default one. You you Okay. And if you want all your uh fields okay to be in a sorted in ascending or descending order sorry a toz order. You can choose like this. Now see if you want a category you can search on the top. If you want a subcategory or state you can see at the bottom. Okay. Now I'll just put it in the default mode. Now I would like to see region wise sales. Now let's drag region or else first I would like to create a view with the subcategory wise sales. Now I have a subcategory. Now you tell me where do I need to drag this subcategory into the rows or into the columns? Definitely not filters values means only numbers. So if I drag this subcategory to the columns and add the sales over here. Now to your eyes, is this report looking good? Is it really uh looking good to read? See, we can't read the information from left to right. Right? So, but instead of this, if I just uh you know move this subcategory to the rows, at least you can read the numbers at one go. Right? Okay. So, it depends on you how do you want to visualize the table. Okay. Now, at least I can read these numbers. Second thing, Okay, you if you want you can also format these numbers. So where to move where to put um row uh a field into the rows or columns depends on you. For example, same private table I wanted to see by region. But if I drag region into rows, see this is not a great report. Okay. Or else if I just to move uh subcategory underneath the region you see if I wanted to compare um accessory sales across all the region I need to scroll down and see instead of that if I move region to the columns I can see the accessory sales across all the regions now. So whenever you want this something by sales by subcategory across regions then you need to use it. Okay columns this is one example I just wanted to provide. So when to drag into the rows and when to drop into the columns up to you how do you want to visualize. Okay, going back to our previous example, I would like to see region wise sales, right? So I'll drag uh region into the rows and the sales into the sales into the values. This is good. I also would like to see state, right? So here I have something called state of province. I'll just to put it in put underneath the region. Okay, this way I got the report. Now I'll try to enhance this report much better. First I'll go with the design option. Once you go to the design option here you have a different private table styles guys. Okay, if you just go here you have you know lighter lighter uh you know background and a darker shade of a background also. You can choose whatever the style that you want. Okay, I'll go with this. Now see here I got uh you know uh my headers like row row labels and sum of sales have got a different color and and the subtotals also have got the different color. If you want you can also change it. Here you have a row headers. When I uncheck the row headers you see uh the row headers have got the gray background color. When I uncheck this that background is gone. If I just uncheck the column headers again that formatting is gone. I can also say banded columns banded rows. Every alternative row will get a border here. So this you can just give it right. Next one very important option. Go to the report layout. Right now this is in a compact form. What if you click on outline form? You see outline form you will get you know something like this. But if you just change it to tabular form. You see this is how you are going to get your tabular form. Okay. But for now I'm just removing uh no color no background. So let's say clear and now you can observe outline there is no borders nothing like this. But if you say tabula you will have a nice borders all of those options. Okay. Uh for example I wanted to apply this number formatting. You can right click on this. You can go to format cells where you can just click on the number remove decimals use thousands operator. These are the two things I'm removing the decimals and I'm just applying the thousand separator. Click on okay. The problem with this is u it will only format that particular number. If you go with the format cells either you have to select all the cells or okay you have to select all the cells to apply formatting or you just go with the number formatting. So there is another option called number formatting. Whenever you go with the number formatting right it will apply for the entire column. Okay this is also done. See this way. Next the repeat uh all items. No, for example, if you want central to be repeated for all of these cells, you just select this cell and click on repeat all items. See, again, each and every region is repeated. Another thing that you can do is do not repeat item labels, you can also remove it. If you want any specific background and formatting should be done, you can also do it from here. Okay? Absolutely possible. Okay? This way you can create a table. Let's say, so we have covered this one. Uh let's say uh this is my example two, but I'll take small data set and show you. Okay, again I'm creating a private table. Go to insert, click on a private table into new sheet. I would like to create a private table for category. See whenever I click on it, it directly goes to the row section. You see category, subcategory. See how nicely it is going because it knows okay uh it it understand that okay these two should go to the uh rows that creates a proper private table and if I click on sales that always go to the column section. Now instead of showing this private table as it is you can slightly format this private table. The very first one option first option is show in a tabular form. Okay. Next one, number formatting, number remove decimals. And this one, if you want a profit also to be shown, drag the profit or just click on this. This will automatically go here. Okay. Uh, simply right click and number format number. Okay. Any negative numbers I would like to show up like this. Or else you can also uh take the you can also slightly format negative numbers in the brackets. Okay. Just go to the custom and whatever you see negative just you know put it in the brackets. This way you can format it. Now here if you see there are some totals. No these are like sub totals. This is a furniture total office technology and this is going to be a grand total. From where do I find this total? If you go to the design here you see off rows and columns. If I say off rows and columns the grand total is gone. If you wanted to get it back uh on for columns only. See these these are for different columns now. So you can on it. If you want to remove the sub totals do not show subtotals. See if you want a sub totals at top of the group. Okay. Above you will get a group. So above or below you can also do. So here you can remove grand totals. Here you can remove subtotals. Here you can change the layout. Let's say uh I'm I would like to make it much better. So I'll go to the view and remove the grid lines. You see there are no uh cells at the background. This report is looking good but okay end user is asking after every category can you add a blank row so that whoever belongs to that respective category they can focus on it. He he or she is saying that this this is uh little cluttered. I want a space in between after every category. You can do that. Go to design. If you go to the report layout here you can say sorry if you go to the uh blank row insert a blank line after each item. See it has nicely insert a blank line item. Now this is making much uh clear right end users can easily read this report. Now this is category is okay subcategory is also okay sales I don't want to show sum of sales. So what I can do I can simply double click on this. See when I double click on this this value field settings have been opened. I can remove this. I'm typing something like sales open bracket and provide a dollar sign. Okay, this also same thing or else you can also do it from here. Okay, profit open dollar sign and put it. See this way you can nicely format it. Now end user can easily read these numbers. Okay, see uh how we got and now how we have that is the difference. Okay, this way you can create a nice private table. If you wanted to change the text of this pivot table, see not able to select the pivot table. If I press control A only this portion is selecting to select the entire pivot table. Go to pivot table analyze. Here we have wide variety options. From here also you can change the header. This is your name of your private table. Here I'll go to the select and enter private table. See from here entire private table you can select. Once the selection is done I'll go here and change the uh font style here and I'll slightly decrease the font size. See how nice my private table is looking like. So we have covered about the design tab. Okay. Now we'll try to explore. See you can see only those two tabs only when you are in the within the private table. Okay. Uh if you're outside of the private table you can't see it. Now I'll go to the private table analy here you can change it. For example tbl products that is my private table name. Okay. Now now we will try to understand how do we filter the private table. Okay. So again you select any one cell okay I told you in the starting step right so if your private table misses any headers for example if let's say country is not there over here okay maybe if you can't see I'm just highlighting it now there is no header I'm selecting the entire data you see entire data set is uh entire data set is selected go to insert click on private table okay when I'm clicking on it okay even if you search for country. Okay, I'm getting country. Okay, one second. I think I need to save it. Okay, wait. See, I'm just adding some blank rows also some blank columns. Now I'll just select this and try to insert a pivot table. See the pivot table field name is not valid. To create a private table report, your data must be organized as a list. List means it should always b