Data Analytics using Excel

hello everyone good evening hope I'm audible under screen visible welcome back to the session so day four of our training day four of data analytics using Microsoft Excel yesterday we had looked at these functions maxif's minutes sumifs countives and then we talked about error handling by using the if error and the if any functions so what's the difference between the two if any handles only the N A errors if error on the other hand handles all types of Errors including the not available or not applicable error okay any error now we also talked about mixed referencing for one particular example we used mixed referencing and we had started talking about conditional formatting okay we didn't cover all of the aspects of conditional formatting but to some extent we did so we will continue with conditional formatting today look at what all we can do and then get an introduction to the lookup functions okay and tomorrow completely we will focus on the lookup functions so with this agenda let's get started we were using this data yesterday I was using this data to explain about the concept of conditional formatting I'll give you a quick recap of whatever we had done yesterday and then we will proceed further okay so I'm doing this on a Windows machine today so that you all can see and understand how it is done on a Windows machine because I think on a MacBook a few features are not matching some of you had told yesterday so here is our data that we were working with yesterday so uh as a quick recap I'm going to take the data that is present in the profit Target column okay here I have selected the entire column by using Control Plus shift key plus the down arrow so you click in the cell hold down the Ctrl key plus shift plus down arrow the whole column gets selected now I will go up to the Styles group under the home ribbon the place where we can access conditional formatting from okay when I when we click here we will see certain options to perform conditional formatting so I'll do a recap of what we had done with highlight cells in this case let's say I would like to highlight those cells having a value of greater than 100 so we'll go in here and select this greater than option will be prompted to enter a value format cells that are greater than and I'm going to give hundreds here we can choose what type of color how we might want to format it I will say greater than 100 should appear with a green fill with dark green text now you can notice how it has done right Excel has highlighted it managed to highlight all the cells where the value is more than 100. now I will again select this particular cell and apply another room the next rule or condition is I would like to highlight the members with a profit of less than 50 and that in red color so you can notice how it managed to manage to highlight it in red I'll apply one more condition that I want to highlight the members whose profit Target profit is in between okay so I'm using the between option now it's from 50 to 100. and such things I'm going to highlight in yellow color the cells whether profit is in the range of 50 to 100 are supposed to be highlighted with yellow color so this was what we had done yesterday the last concept that we discussed was this the complete data that is present in this column is now having a defeat a color based on the value in that field right green for more than 100 yellow for the values from 50 to 100 and less than um 50 are in red color so whatever I did just now is highlighting only the data that is present in that particular column now what if we might want to highlight the entire row based on the condition some condition that we Define if we would want to highlight the entire row then what to do so first I will clear out whatever formatting I have applied how to clear it out by going back to conditional formatting we have we have an option to clear the rules okay these are called as rooms we are setting up a rule that when so and so condition is met do something if the condition is not met then something else so here we will go ahead and clear the rules I'm going to clear the rules from the entire sheet I don't want anything here so I just completely clear everything now let's see how to highlight a complete rope based on some criteria so I will select the entire table Ctrl shift right arrow and Ctrl shift down so the whole data the complete data and the table is now selected in that in that range is selected okay I'll go ahead and apply conditional formatting but now I am going to create a room okay I'm going to create a rule and we'll have to use this option use the form use the formula to determine which cells to format okay and edit the rule description format values where the formula is true so what is the formula the value in this particular cell or in that column actually but I'll just select the cell if it is greater than 100 we need the highlighting to happen but if you notice here the column and the row both are locked in place this is absolute referencing in our case we want only the column to be log the row should not be loud we have to work with the data present in each and every row so I will simply remove the dollar sign before the row number this is mixed referencing now in this case where we Define the rule any uh default color format is in there there is no default color format that we can select from we will have to set up the format okay by going to this format button we can go ahead and choose the format I will just use a fill let's say I want to fill it with this color all right so now I'm going to click on OK and OK again and we should be able to see all the cells wherever the target profit was greater than 100. it's not just that particular cell but the entire row corresponding to it got highlighted okay how to highlight an entire row based on certain value is what we've seen yeah similar to what you do with the if functions right it should be greater than something or less than something equal to something between certain range yeah more or less the same okay one more time this feature okay um the feature of enroll uh highlighting the entire row I think you're asking me to repeat right okay let's see that let me undo what I let me clear the formatting so I will go slow now please see we actually we did this yesterday I'm just repeating what we had done yesterday first we have to select the complete range hold down the Ctrl key shift key and the right arrow so that the cursor moves to the right and highlights the entire row now Ctrl shift and down arrow so that the complete range of data is highlighted okay after the whole thing is highlighted now I'll go up to the Styles group this is called as the Styles group and access the conditional formatting feature from here highlight cell rules and this time I would like to define a rule okay I'm not using greater than less than I'm going to define a greater than less than you can use if you're working with one cell one column here we are working with the entire data we want the whole thing to be highlighted isn't it so we'll have to go here to more rooms now I would like to use a formula to determine which cells to format I think this even if I zoom in this part it will not work okay use a formula to determine which cells to format is the one that I've selected here you can see the Blue Ribbon behind it here we have to type in the formula okay now what is our rule we want the data in column G over here we want the data present over here to be greater than 100 and only for such records where the target profit is greater than 100 we would like to highlight the entire row and I am going to use mixed referencing to not lock the column as the row okay so this is dollar G2 dollar g means the column is locked G column but the row is not logged okay and I'm using a different format a color to highlight the cells okay so I hope you got it this time this is how we can um ensure that the entire row meeting the criteria is highlighted okay all right so if I just use let's say K2 or k suppose what what is the address of this cell it is K 3 if I move down it would become K4 if I move here it would become l 4. that every cell has an address isn't it so this is called relative referencing k3k4 K5 it moves but here we wanted mixed referencing fixing the column changing the royal all right now let's proceed further and look at some more options that we have under conditional formatting so I am going to clear away all the rules that I applied I'll just clear everything so we had discussed about top and bottom also yesterday okay so highlight cells and top and bottom is done now let's proceed to the next set these are uh more graphical or a more visual way of representing the data okay we can highlight the values basically by indicating data bars the first thing is data bar and I will first select the column with which I would like to work this is the column that I want to work with now I'll go to data bars we can indicate the values by giving Bars by placing bars so it's a nice visual way of representing the data there are different colors to choose from as you can see we also have solid fill okay this is a gradient fill and this is a solid fill I'll go ahead with gradient fill and I will use let's say blue color this one now let's understand how to under how to interpret These Bars you can notice bars have come but what are they indicating they are indicating the performance or the target profit relative to each other see if you notice the highest profit I think is 370 which you can see the length of the bar is the longest here and relative to the highest value the rest of the bars have been given a size okay the rest of the bars are in proportion or in relation to or relative to the highest value this is 360. this is 260 this is 220 this is 30. getting it we are indicating the value not just the value but in the form of a bar now um I'll do one thing I'll make it slightly wider all these three I would like to increase the width of all the three columns so by going to the column number when we you know select the columns and go to this format option this is to format the width of the cell or the height of the cell so this is the cells group I'll go in here and change the column width I'm going to fix the column width as 15 pixels okay 15. I think 15 is small um let me make it 25. okay this is better okay this is better now it is also possible to hide these numbers let's say I want to indicate the data only with the bar numbers need not be displayed or for some reason I would like to hide the numbers then what to do select the entire column okay now I will go back to my conditional formatting and I am going to manage the rules whatever rules that we set up on the sheet and on on a particular cell or a column everything can be seen or accessed from this manage rules option so when I go to manage rules here this is a room right what did we put data bar okay on this particular column and that row we have put a data bar now I would like to edit this route so here's an option to edit the room first I'll have to select the rule you can see it has been highlighted and now I'll click on edit rule button once this is done you see here we are representing our visualization through a data bar the format style is the data bar and there is one check box next to it show bar only so if I select this check box to show bar only it is going to remove the values okay once I apply on okay it's not going to show me the data it will only show me the bars look at that so how would this help us what can we figure out by looking at only the parts we wouldn't know anything much right imagine a scenario where you're giving a presentation you're from the finance department and you're giving a report let's say a report where you're indicating the salaries of employees okay you're showcasing the salaries of employees but you're not supposed to reveal the salaries you just have to show a relative comparison okay so with respect to the highest salary that is being paid to an employee what is the salary that the rest of the employees are getting I hope you are getting my point so places where you might want to hide the figures the numbers the values because you don't want to you don't want people to know what is the salary you simply want to know relatively where do this stand okay so in that case you can simply create this kind of visualization where we are not showing the numbers but the bars are going to convey the information the point you need to remember here is this is relative comparison this is relative comparison okay means this is slightly higher than this member this member is significantly higher than this member this number is much much low compared to this member okay so relative exactly while we are showcasing confidential data it is better we can if we go ahead and hide the numbers but we can show still show the performance by using bars okay so that was about data bars under conditional formatting let's move on and look at another feature that we have here this time I will be working with the profit column okay and what's my condition let's go here to color schemes when we use color scales you can see different gradients this is yellow green red green yellow and red right so the highest range higher values will get green color the medium range values will be in yellow and the low range values will be in red this is the opposite of that okay like that we have different color schemas from where we can choose what we might want to use so I will just go ahead and use the first one the first one green yellow red scale but what does this exactly do let me show you okay now what this did is if you look at the data here right the least value is 0 that has got a nice red color and you can see medium values are in yellow high range values are in green and if you see very dark shade of green that is here 367 that's a dark shade of green relatively lesser values are having a lighter shade of green and when we go towards me um you know these numbers it is yellow and very less values are in shades of red so it basically segregated your data and threw a color gradient it is indicating the value this kind of representation can also be called a heat map it can be called a heat map so for this I will show you another example which will be more meaningful here I am on color scale so this is some data that I have okay um this is the the sales let's say this is the sales of various products that coffee chain business is selling and the sales that has happened across different months all the way up to December I have my data okay so I simply want to give some sort of color code to understand this data basically I would like to draw the attention of the users towards the high and low values when you want to maybe significantly highlight the highs and lows then you can go ahead and give a background color color depending on the value that is present in that field so I am going to highlight this entire range go to conditional formatting and simply use the color schemes okay so what has happened here depending on the value we are getting so wherever you see red it is basically indicating that the sales is very less for example for regular espresso for mint for green tea they're not selling much Colombian coffee that is selling a lot okay then we have lemon tea which is okay mocha is fine rest of them are kind of medium Darjeeling uh decaf espresso a lot whereas certain products are not selling much the sales is very very less over here in these cases okay so here we can't see the color Legend as such simply by looking at the colors we should understand okay Karthik there is no separate Legend This itself is conveying the information to us on it so this is about color color gradient okay color schemes data bars is one option color scales is another option I hope you'll understood the significance of color scales well after seeing this example now we will talk about another feature that is present here which is called eye Concepts another interesting way to highlight your data the color schemes are based on the values that are present in the field it is based on the values it is dividing your data into three ranges okay it is dividing the data into high range medium range and low range and based on that it has given the colors no no okay the question here is uh if we have done this month wise how do we differentiate the colors See I have not chosen one single state here I or one single uh month here I have chosen the entire data isn't it If You observe very closely within each cell there is a slight difference in the color gradient if you compare eight thousand Five Sixty eight and seven thousand four sixty four there are two different shades of colors here not very significant but they are two different shades of colors or this one and this one in the same row there are two different shades of colors by E cell is the color depending on the value that it holds did you all understand each and every cell is getting a color based on the value that it holds if you look at eleven thousand nine seventy five it is slightly darker compared to nine thousand there is a difference in the shade of the color depending on the value let me do one thing um let's say here I will just increase this to 12 000. so what will happen or 100 and 120 000 it has changed so now I hope it is clear it's not coloring the entire row with the same color it is giving the color based on the value in the cell let's make this around 5000. so look at the color okay now I hope it's clean so I'll undo that is it clear those of you who connected through YouTube live Naveen is it clear Satish I hope it's clear okay thank you for the confirmation and it will proceed now let's go back to uh the first sheet and we will discuss about something called as eye Concepts which I will um explain using this column marked price so let me go here there is icon sets okay there are different ways in which we can indicate certain things using arrows again we have three we have different things let's go with this actually you can choose anything I'll choose this this is called as traffic lights choose a set of icons to represent the values and selected cells so I'm going to select this three traffic light symbol now let's understand what actually happens here it is basically going to divide your data into three parts okay whatever data I have it is divided into one third and one third and one-third okay so it divides my data foreign divides your data into one third again to one third and again to one third so whatever values come under the first bucket they will be in one color whatever values come in the second one third of the bucket will come in another color water value values coming at the last bucket okay so let's say 0 to 33 percent okay 34 to 67 and this will be 67 to 100 based on the percentages it is going to divide it breaks your data into one third one third one third because there are three symbols right red yellow and green therefore it works like that let me show it to you with another example okay let's say I have data like this but if this is my data and if I apply the traffic signal data icon over here what will happen so it basically divided the data into three parts right these three one two and three are here four five and six are in one color seven eight and nine are in one color no if I change the values so basically what is the overall range is what the system will check what is the overall range of values that we are dealing with the overall range of values we are dealing with is one two nine and when I break this into three parts one two three is one part 4 to 6 is one part and then seven eight and nine is another part of my data only now I'll make a small change here suppose this is also three suppose I change this value to 1 to 3. look at or two two look at what is happening so when I say one third it is not the I mean it's not like you have nine records so three three three records no it is based on the values when I say one third of one to nine one two and three all the three numbers will go into the first 33 percent of the data that could be even half of your values which fall in that range is this point clear it could be even half of the values that could fall in the one-third range physically half of my data is going there more than half however this is the range that is the one third of the entire data range okay when you're breaking your data into three this is called Data distribution where do we use this this feature that we are talking about is called as data distribution we are breaking our data into buckets okay we're taking the complete range of data and breaking into three buckets and seeing how many values are there in each bucket imagine this is some um the ranks that people have scored okay let's say some college or some Institute um nine children appeared for an exam and of the nine children who appeared for the exam these are their Rhymes so we see that there is a tie in the first position there's the tie in the second position but five out of the nine people have managed to stand in the first three places is it clear now when to use data icon sets it is related to data distribution you are breaking your data into buckets and then you're looking at how many observations are there in each bucket okay so if I change this uh if I change the final number itself let's say this is only six then what will happen Everything Will Change accordingly because now the range that I am dealing with is not one to nine now the range that I am dealing with is one to six so what will happen accordingly things will change okay here it has removed the middle part sorry to eight this is one two eight right let me change this also I'll make this five and I will make this six now look at that so if we are dealing with this kind of information here what happens this is one two six the overall range right first you have to check the overall overall the range of values are from one to six and when I break this overall data into three buckets one and two will be in one bucket three and four will be in by one bucket five and six will be in another bucket isn't it so you can see one and two all in red color three and four of which I have only three in yellow five and six of which I have five and six occurring three times they are in the next bucket so we are bucketing our data over here yeah this this is all discrete data only yes it can be continuous also it will work even if you have decimals and continuous data it will work here I'm trying to explain it with whole numbers it could also be decimal numbers accordingly it will break the data all right so I hope it's clear can you show three colors in one Circle no we can't show three colors in one circuit there is a reason there is a purpose why these circles are kept separate to indicate the values right okay so now here the what is the range of data that we are looking at is we have a very very low values like 40 Etc okay that is I think we have quite small numbers then we have medium range values which are coming in yellow and we have the high range values also which are here coming in green like 678 494 546 Etc all right so this is the purpose of representing the data using icon sets yeah yeah it does not matter you can have positive numbers negative numbers you can have decimals whatever it might be it will divide your data into three parts by looking at the complete range of data it is going to break it into three parts so let's say you have negative 10. okay under negative eight when you have negative five then you have 0 then you have two three then you have let's say five and ten okay let's just make this numerical okay this is my complete range of data what is my entire range of data from a positive 10 to a negative 10. and if I go and apply the color icons over here okay so far we are doing only three parts so like this if I want to break it down into four parts quarter quarter quarter quarter right one third one third one third is what we saw now if I use four then it will be four quarters of which we don't have data in one of the quarter as you can see so let me change this to maybe negative 3. you're getting it so like that we can go ahead and use this data basically this is distribution data distribution we will dive deeper into the concept of distribution when we start creating visualizations okay just remember that the distribution is where you take the entire range of data and you break it into smaller intervals okay and you're looking at how many observations or how many values are there in each bucket okay so that was about uh icon sets fine so now we will do some Hands-On part I will show you some more things related to formatting and then give you an introduction to the lookup function formatting data that we have been looking at is all based on certain conditions so far right based on certain conditions now let's just take a few minutes to understand just normal formatting of the data okay this is my data okay and there are a few steps that I am supposed to perform here in order to format this data not conditional formatting just general formatting this let's say to present it to present the data in a better way so this is like going back to the basics first we need to autofill the employee numbers into cells A6 to A8 so a 6 is this up to 8 we need to automatically fill in the employee numbers for that we have the Flash Fill option in Tableau isn't it so this this icon here represents something called as Flash Fill so quick analysis dude all I need to do is go to this border corner and over there on that handle do a double click it will automatically fill in the values so so Excel is a very intelligent tool it can understand the patterns and it can replicate the patterns it can you know fill in the series it can fill in the series then we have what is the next question given to us set the columns width and rows height appropriately you can notice that in some of the columns the names are truncated like hourly rate whatever this is all truncated it's not very clear so in one go to set the column width for everything you can just start with the column First Column and just increase the size okay select everything and then double click on the edge of any one of these borders on the border between two columns you can see a line right so anywhere we can go ahead and double click all of them will automatically adjust in such a way that the whole data fits in okay now here I will reduce the size given to a we don't did not give so much I will use it you see neatly it has automatically arranged now these three are not looking so good each one with a different columns suppose you would like to fix the column width then you have to go to format option in the cells group here column width I'm going to adjust and increase it to 25. too much 25 is too much okay you have to check it relatively you have to change it maybe 15 would be sufficient okay next the row height row height also has to be adjusted so I will select all the rows and in similar fashion I'm going to double click here between any two row numbers you can go and double click the height of all the rows in the selection will be adjusted appropriately next third statement uh third question is to set the label alignments properly so these are the column headers right we need to set the alignment there's no specific uh request here just properly we have to set it means let us enter a line this is a group on the ribbon that talks about alignment this whole thing talks about alignment so I want it to be middle aligned Center aligned so I'll click here all of them move to the center payroll that you notice right payroll let us make this as the heading of the entire table so rather than having separate columns payroll should become one heading here so what we can do is we can merge all the columns merge and center it will combine all the cells and it will also move the text to the center like that I will make it slightly I'll make it bold you can see the font group okay and I'm going to give it the background color fill it with yellow or whatever color of your choice you can use for formatting okay and you can color it all right what next use wrap text and merge cells I've done that apply borders grid lines and shading to the table as desired so we've given shading to this particular cell this is shading icon okay similarly let's say all the cells I am going to select and this is stable borders sorry the Border to the range of values so when you click here there are different types of borders we can get I'll use all models so you can see the borders have appeared nicely to this payroll I'm going to give a thick outside border okay and to this also this whole thing just a thick outside border and uh these headers let us highlight them by giving them a shading so maybe like this we will highlight them okay so like that you can explore the font the alignment uh group and all the features that are there in these two groups next format cell B2 to short date format this is B2 is it column B and Row 2 we need to change this date representation and we have to show it in short date format so what to do if you have to change the format of a field the let's say the data type or how it presents the data you select it and go under the number group here now okay here it is custom I will change that to short date if I use short date it will come like this and if I go and use long date it will show you the name of the month here when the space is not sufficient this kind of representation is something that might that you might come across pretty often so if you see these hash symbols like that it is simply meaning that the space is not sufficient to show the data in there okay space is not sufficient to show the data in it so I will go here here this is column B right so I'll go to this line here double click on it the width will get automatically adjusted so when you double click on these the width of the column gets automatically adjusted how to give the border is the question you have to select the data around where you might want to give borders after selecting the data where do we have the borders feature we have it over here in the font group we have it and here this particular icon is for borders and when you click on this drop down icon you can see a lot of options will come up and I've I've taken this all borders option because I wanted borders around each and every cell in that particular selection is it clear that's how you give borders all right so I've taken long date okay no problem format the cells E4 to G8 to include dollar sign with two decimal places E4 to G8 we will do that after we put some values in there so calculate the gross pay for employees we need to calculate the gross pin which will be the hourly rate multiplied by The Hours worked so equal to yesterday I showed you how you can directly select both the cells use the multiplication symbol right what did I show you yesterday for multiplication this this multiplied by this we can do it like this okay and we will get the product which is basically the grocery or we can use a function called as product let me show you that today okay there is a function called as product and the inputs that it will take is the numbers that are supposed to be multiplied so I need the product of I need the product of first number now okay we'll have to give the first number you can see the argument number one in bold this comma you can see second number in bold right this is the second argument so we are going to get the product of the data in C4 and D4 this is relative referencing okay this is relative referencing and when I copy that down by doing a double click on this handle here it just gets copied down so what is the formula here you can see in the formula box product of C5 and D5 what is the formula used here product of C6 and D6 the formula used here product of C7 and t7 so on and so now we are supposed to compute professional tags calculate the professional tax with six percent that is going to be six percent of the gross pay so what I will do is I will keep it in one column and reference this column okay I will keep it here and I will reference this column to compute the professional tags it is computed as what it is computed as the product so whatever is the pay that we are giving to the employees okay this pay multiplied by the professional tax which is over here and this is supposed to be an absolute reference it should not move down right for all the rows we need to multiply the Gross State with six percent only no relative movement is required so I am going to lock that in place by using absolute referencing how did I do this I just hit the function key F4 so when you press down F4 function key dollar symbols will come before the row and the column means it has been logged in place now we'll apply it and then simply I will copy it down to the remaining rows finally we have to compute the net pin net P would be the gross pay minus the professional tax that is what we get in hand right from the gross the tax is deducted and then it is given to us so is equal to this number minus this number enter and I have to copy that formula down so I just simply go there and drag it down now look at the form let's do the dominant formatting we are supposed to format the cells from E4 to G8 by including a dollar sign so that would be currency by default it is a rupee and then after choosing currency we go to this accounting symbol and then we can choose let's say English United States because we need a dollar sign here all right what else it must have two decimal places it already has decimals if we don't want the decimals we can click here and remove the decimals decrease them so in this case we need the decimals to be there I will leave it okay I hope you'll understood so simple formatting is what we've done here whenever you people get time while you're practicing create some sample dummy data sets like this and you can practice all the uh options that are there in the home ribbon the font group the alignment group the number group now that we have understood about Styles explore the Styles group and the Sales Group okay um what else so we'll now proceed to the next concept how do we get the dollar part is see first of all you have to change the data type I change the data type to currency and because my system local by default is India the currency is a Indian rupee this is the symbol for Indian rupees we've been asked to include the dollar sign in the requirement so what I did is after choosing currency I went to the accounting symbol just below currency you can find it okay and here it gave Indian rupee by default I want dollar United States dollar so I am selecting the second option and this is how it appears okay and yesterday I think there was a question on can we sort the data we can do that we will be looking at that a little later into the training sorting is possible and uh someone kept asking what if profit is zero that question was not clear you might have to elaborate on that question okay so that's it about formatting I hope it's clear now how do we include some new add-ins packs into the tool like for example analyze data um if this is not already present in your version of excel that first you must check if it is there in the add-ins sometimes if it is um not available at all it could be a version issue okay like yesterday someone said that uh you were not seeing ifs ifs wasn't there on your machine right so that could be a version issue whether or not a function is available on the version of excel that you are using you can simply check by type just doing a double click on any cell put the equal to sign and then type in the name of the function if you find it in the list it means the version of excel spreadsheet that you are using has that feature and if you are not able to see the function it means the version of excel that you are using does not have that feature as simple as that okay it's as simple as that to check whether or not something is available now to get the add-ins you will have to go to the file menu okay so here we are on the spreadsheet right I am going to the file menu before whom we have it on the top left corner so go there and then down here bottom left corner there is an option called as options you will see something called as options now when I click on options here um under adults you have to go okay you have to go under Adams and then you have to manage Excel add-ins is what we are trying to manage so if I click on the Go Button it will show me the other add-ins which are available so I had added this analysis tool pack because I added analysis tool pack earlier on my screen on the top right corner you can see this analyze data okay if it is not present by default then you may have to use this similarly solver item because later on we will be working with what-if analysis um maybe towards the end of next week somewhere around 7th or eighth session we'll be working with solver add-in so that time you will need to know these things okay you'll you'll require so you can add them not now later on you can add them okay Satish I hope that clarifies your doubt now when you can select cells adjacent to payroll also you can type in additional data over there if you have two yes these are still available you can enter more data over here all right so I would like to give you all an introduction to the concept of lookup function just I will show you what it does okay raksha no problem you leave it if it's not showing anything it means we don't have enough data to analyze whatever is going on over there we don't have enough data to analyze okay you can close it it's not required for now we will use it only when we get a little further into the concepts okay I simply introduce you all to the concept of lookup but we will be focusing completely on lookups tomorrow okay a very very interesting feature which um Excel has so look at this data here look at this data that we have over here okay so I have information about certain products the states where they are being sold and certain statistics certain information there now you can notice that product type is not available I don't have information about what is the type of that product whether it is coffee whether it is tea whether it is green tea or is it espresso the product type is there in another worksheet okay each of these products are classified into different types and the type of the product is in a different shape which is not here now what I need is let's say my my client has given me this data and they want me to look up they want me to search for the product type over here based on whatever is the product I need to search for the product type from here and I need to update this information in this column so what am I trying to do I am going to look up meaning search for the product type in from some other sheet fetch it from the other sheet and display it in this sheet and to be able to use the lookup function there has to be one field in common there has to be a common denominator okay I'm trying to search for the product in this sheet so product should be available in this sheet also based on the name of the product the product type has to be fetched and it has to be pulled back over here so when you are using lookup when you are trying to get data from one table to another or when you're trying to get data from one sheet to another sheet we are merging data from two different sheets over here isn't it what is lookup itself it's going to help us to merge data from different sheets bring data from one sheet into another sheet all together so for this we use a lookup function and there has to be a common thing please do remember that if there is no common field between the two tables then lookup will not work so let's see how lookup is how to write a lookup function here are different varieties again different variants in lookup there is vlookup there is H lookup and in the latest versions there is something called as X lookup we will be looking at all of these functions in detail tomorrow the difference between them and how they work so here I will simply give vlookup as you can see the function has come I will select it first thing we need to give is look up value meaning what are you trying to search what is it that you're trying to search I am going to search for the data in the product column this one where am I supposed to search for it that is table array okay you want to search for decaf Irish cream but where am I supposed to look up this is the value that I need to look up but where that will be the table array so I need to look up for it in the different sheet so I go to that sheet and select this data look up in this range search for decaf Irish cream in this range okay now it knows that it's supposed to search in this range because the lookup range is a completely different table or a completely different sheet you can see how the address signals right in the product type information the name of the sheet exclamation mark and the range so we'll lock in this range because it has to always come to this range only I am going to lock in that range by using absolute referencing okay after that comma column index number column index number so we should not give the name of the column we give the index number um in that range it has to look up for column number two okay if you have noticed product is one column and product uh type is the second column so it starts numbering The Columns from the left side starting with one the leftmost column gets number one so this will be suppose this is my data this is column one this becomes column two this becomes column three so on so I need to get data from column number two and then we have to give one other variable which is called as range lookup or it's basically whether we have to look for an approximate match or are we trying to go with an exact match I need the product here to exactly match with the product on the other sheet isn't it it has to be an exact match so for exact math match we need to give false okay so once I do this it has pulled back coffee from the product type information it pulled back coffee for decaf Irish Cream it pulled back coffee now I'll simply double click on this to copy the formula down and you can see the correct product type is being copied back depending on what the product is okay I didn't I'm not my idea today is not to teach you vlookup it's just to give you a demonstration of how this feature helps us to bring data from different tables together to merge data from different tables together you're searching for something elsewhere and showing the result over here so that is the beauty of a lookup function okay tomorrow the complete session we will be focusing on lookup functions and try to understand them well so thank you friends that's it from my side and uh please do subscribe to the channel if you haven't done it here already don't leave without hitting the like button okay uh it's a request okay please do uh give a like and share it with your friends whoever you think would benefit by watching this series of videos that we are doing on Excel okay that is the only thing I'm asking of your subscribe like and share nothing more thank you all so much yeah any questions from your outside I'll take up data will be uploaded in the LinkedIn platform so you need to register to our you need to follow our LinkedIn page from where you will be able to access the data why we give false and the significance of false we will do a deep dive into that tomorrow Avinash and Karthik when should we use true when should we use false how the results will vary depending on that we will do that today was just to give you a demonstration of how lookup verbs Works nothing more we are actually going to learn lookup tomorrow data sets will be uploaded in our LinkedIn page and tomorrow I will share the link with your if you are following the LinkedIn LinkedIn page you might find it there if you have not started following it please do and tomorrow I'll give you the link also okay I just need to get the link from my team I I couldn't get it today okay don't worry about the data sets I'll make sure that they reach you yes I'll I'll share okay I will send it in the comments in the YouTube channel okay thank you all so much then we'll end the meeting here okay please do leave a don't forget to hit the like button before you all go hit the like button and then go thank you bye