Microsoft Excel is one of the oldest and most popular spreadsheet application for data analytics and Reporting hello everyone welcome to this full course video on data analytics in Excel in this video we will learn everything from Basics to Advanced in Excel our experience instructor a will help you learn in detail you will start off by taking a tool to welcome to excel by you will learn the basics then you will understand the various crucial functions available in Excel such as vlookup hookup sumps cters if error and others you will learn how to sort and filter data in Excel then we'll focus on conditional formatting and data validation moving further you will get an idea how to create po tables and po charts in Excel and finally you will see how to use the data analysis tool pack to perform various data analysis operations such as regression sampling descriptive statistics and much more over to a now hi everyone welcome to this tutorial on Microsoft Excel so we will learn about functions and formulas we will learn about conditional formatting data validation pivote chart and pivote table now let's look at a scenario here so one day in a startup One Professional speaks that their business is growing and they would need an efficient way to work with the data they would have to find a way to work faster with storing and analyzing data now to that another colleague responds well we can make use of Microsoft Excel to do this job the question is will excel be able to cater to their business needs now the colleague responds well we can make use of excel in several ways and it also is a cost efficient option now in that case the colleague who posst the question says let's go ahead with Excel and let's train our employees in Excel and the suggestion is welcomed which would make the job easier for them and they would basically decide on using Excel so they decide on taking a training right away way and basically starting to learn Excel now before we move to excel one of the question is why should we use Excel so let's look at some of the points here so Excel proves to be a great platform to perform various mathematical calculation on large data sets which is one of the biggest requirements of various organizations these days various features in Excel like searching sorting f filtering makes it easier for you to play with the data and Excel also allows you to beautify your data and present it in the form of charts tables and data bars now when it comes to reporting reporting accounting and Analysis can be performed with the help of excel it can help you with your task lists your calendars and goal planning worksheets Excel also provides good security for your data Excel files have the feature of password protection this way your information can be safe now when we talk about what is Excel and how it can be used so Excel or you might have heard as spreadsheet can be basically used for lot of different tasks than just storing the information in so-called tabular format now Microsoft Excel is an app application that is used for recording analyzing and visualizing data it is in the form of a spreadsheet let's have a look at few of the functions and formulas used in Excel and before we do that we can also quickly take a small tour to understand how to work with Excel now to do that what we can do is we can type in in our search say for example Excel and just select your Excel sell app which is installed and here you see you have lot of options which says take a tour drop- down list get started with formulas make your pivote table going forward with pie charts and much more so we can click on this one which says take a tour and that basically pops up a window which says welcome to Excel and if you have always wanted to be better at Excel you have this which can help you so let's click on Create and that takes us to the store window now that says instructions for screen readers which basically talks about 10 different steps in which we can learn Excel and using the spreadsheet app so there are more than 11 sheets which we see here at the bottom end and each one gives us a simple example which we can work on so for example if I click on ADD now that takes me to this page page which says how do we add numbers now you might be provided data which we can upload by loading a file from our machine or getting data from a web Source or even connecting to a database so there are various options which we will see in some time so here we have an option which is called Data you can click on this one and this basically has options where you can use existing connections if you have created some you can always click on from other sources and you can get your data from SQL Server from analysis services from o data data feed you can get in from XML from data connection Wizard or also from Microsoft query you can be running in different queries here which shows up in the option which says new query there are connections which you can use and that basically will display all the connections for this particular workbook which we do not have as of now but we can create them but let's look at simple examples now you can follow these instructions here which says basically adding up the numbers and that could be easily done by just placing your cursor here and what you could do is either you can type in the formula that is from which row to which row you would want to add the data so for example I could just do a sum here and that shows up all the different functions which are available then we can open up a parenthesis and we can say I would be interested in totaling the amount from column D and I would select for example D4 so I could be doing this and then I could say D4 onwards till D7 so that's the data which I'm interested in you can close your parth is and hit enter and that gives you the total there is also a shortcut for this which you can always do is we can first delete this and you can just place your cursor here and just use your alt and equals that automatically selects the numericals which we can anytime expand or basically collapse so I will basically select this which says this function needs two numbers which is number number one and number two and then you can hit on enter and that gives you the total so similarly we can be getting in the data here by selecting all the fields so here it also says that you can use a shortcut now what we can also do is we can add numbers over 50 by selecting the yellow cell here and then giving a condition such as so I can basically use something like some if and then open a parenthesis I can select I would be interested in this row and then I can even drag and drop till here so that tells me d11 to D15 you can then put in a comma here and you can give your condition say for example we would say I would be interested in numbers only above 50 and we can select this close your codes and then just close your parenthesis and that's your formula so you can do this and that basically gives me the total is 100 now similarly we could do that for the amount here I could select this now there is also an option I can click on home and I can go for something like Auto sum so that's one more way of doing it which anyway says sum is alt plus equals so it automatically adds up your values and I can try doing a auto sum that automatically selects my rows and then I can get my total now as per this activity here it says try adding another sum if formula here but add amounts that are less than 100 and the result should be 160 so what we can do is we can basically select all the numbers which are lesser than 100 so the way we did earlier here there can be always a shortcut so you can always for example if you would want to avoid typing in the formula you can always copy it from here and then just hit on enter so you are back into this cell and then I can basically go here and paste the number and then as per the requirement we are required to select anything which is lesser than 100 so what I could do is I could select here I could say let's say G and then I we can change this value to g15 and that's one more way now we see our selected rows have been changed so I can hit on enter and I can check what is the result so we would be interested in looking for numbers which are lesser than 100 so I will have to also change this one to a lesser sign and that basically gives me the total which is 160 so that's how you get simply add numbers you can use Auto sum you can type in the formula you can select the fields or you can just place your cursor where you would be looking for a sum and then you can just do a alt equals and that basically populates the sum now let's look at some easy options of filling your cells or automatically populating the values in your cells within your Excel sheet now here we have an option which says 100 now we can click on this and that basically says it is making a sum of column C4 to D4 so if I click on this one I can check that this is row number four which shows up here and I also know this is column C I also have D so this equals is basically giving me a sum of C4 to D4 now what we can do is we can always place our cursor here at the right corner and then we can just drag and drop and this basically gives me a total of all the numbers for all different rows so this is one shortcut which we can do to get the total Excel will automatically give the totals which we call as filling down now what we can do is in the same way if we would want to get the totals here we can first check what is this 200 and this tells me it is C1 to c14 total so it is totaling the rows from C11 so column C and 11th row till 14th row and that's the sum now what I can also do is I can similarly like above we can do a filling right which basically means bringing your cursor here and then just dragging and dropping it all the way where you would need the totals and this basically gives me the total there is one more quick way to check if this is right so the easiest option would be to select this cell now what I can also do is I can just select all of these fields by just highlighting and selecting all the fields once it is selected press on controlr and that gives you the total now if we would be doing this stop down then I could select all these rows for this particular column and then I could do a controll d so that's your filling down and this one was filling right so this is an easier option of doing a fill when you would want to have the formula applied to every row as it occurs in the first row or the last row we could test this by for example selecting these fields I could delete them and I I have here which says 130 I could just place my cursor here and I could drag it all the way up and that should also do the same magic which we were seeing from top down so this is a simple way wherein you can fill up your cells and you can also automatically propagate or move your computation to all the cells let's look at the split option which basically helps us in splitting the data when we have some kind of pattern or when we have some kind of D limitter in our data in say one particular column and we would want to derive the values out of it so we can always use the splitting option now the easiest option would be so for example we have our email column which has the email IDs and which we can clearly see has a first name do last name now I see that there is a last name Smith filled up here first name is empty so what I can also do is I can just type in say Nancy here now that's the first name I can again start typing the second name and as soon as you do that you would see a faded list of numbers and that's your clue to hit enter and once you do that you would see all the first names have been filled in here if you would want to maintain the case sensitiveness you can just go ahead and delete these and let's type in as it occurs so let's say Nancy as the first name go down to the next cell and just type in Andy and there is your grade list so just hit on enter and that basically fills up your first name what we can also do is we can just select this particular field and either we can type in control e which basically fills up all the options now I can just do undo by typing in or clicking control Z and that's basically gone what I can also do is I can select a particular field and then I can go into home option and under home you have an option here which says fill so you can select this and then you can do a Flash Fill which is what we are doing here so click on Flash Fill and that automatically fills up the values so in this way you can work within your spreadsheet and you can be filling up the values where a d limiter by default is understood and we can split the data now however sometimes you might have some data which has a different kind of delimiter and there is again a smarter way of splitting your data so you can always scroll down here and that says splitting a column based on D limits so we have some values in the data column and these values in each row are separated by comma so select this your data is already selected text to columns Del limited comma is selected and now click on next so it basically says what is the destination let's select this one and I can choose what would you want to have so that shows me this would be my data preview now I can basically select this one I can say finish and say okay and now if you see our data has been placed in in the columns appropriately so this is how you can split your data based on a d limiter and then organize your data in a better way now there are some Advanced options which we can learn later but this basically tells about using a formula so this is something if say if we have some name in one cell and if you would want to split it into first name your helper column your middle name last name so that can also be done using formulas and this basically tells how would you extract characters from your left cell and how would you place them in your right cell so you can try this activity which is a little more of advanced option the benefit is that you can always use this wherein if you do some kind of transformation using your formulas if your original data gets updated then the split data will also get updated and that's the benefit of using formulas where you can place values from one cell into multiple cells based on execution of your details in the formulas how about using the transpose option now you might have heard of situations where you would want to switch or turn your rows into columns and your columns into rows and that's where transposing comes into picture it might be useful when you have your data uh in your X and Y AIS or as I would say in rows and columns and you would want to switch your rows to become the columns and columns to become your rows so what we can do is the simplest way is you can select all your values so here we basically have six columns and I would say two rows now I can select all of these and then then I can select an empty field for example the one which is highlighted here well you can always do a control alt V that's a shortcut what you can also do is once you have selected all your Fields you can just copy them so just do a contrl c and then click on an empty cell and then what you can do is you can do a special paste or paste special so under your home you have the paste option and here you can go for paste special and once you do that you need to select the transpose option over here and click on okay and now you will see that the columns and the rows have been transposed so your row name was item and that has become the column heading you had row name as amount and that has become the column heading and all your values have been transposed in this particular format now there is another way of doing that and again again that's using your formulas so what you can do is you can transpose with a formula also and that basically works when you have similar kind of data so this has six columns and basically two rows so you can basically do this so you can select this and earlier we were doing a copy but now what we would want to do is we would want to just look at the row numbers which tells me it is C3 33 c34 and it starts with c and ends in with your H column so what we can do is we know that we have six columns and two rows so transposing that would actually give me two columns and six rows so what we can do is we can select two columns and six rows in our Excel sheet I can then basically start typing in the message or I can just go to the address bar and here I can say transpose let's go for c33 h34 it basically selects my data and now I can just do a control shift and enter and now if you see all the values have been populated now you can just place your cursor in one of the cells but if you see the address bar the formula Remains the Same this is because this is an array formula so we can read more about an array formula here it's basically something which performs calculations on more than one cell in an array and in the example here the array is the original data that is c33 to h34 so your transpose is just changing the horizontal orientation to the vertical orientation so this is a very simple way in which you can basically use the excel's capability to transpose your data and convert your rows into columns and columns into rows apart from working on additions subtractions filling up your data sorting the data or basically splitting your data transposing your data one of the other requirements is sorting and filtering your data now that can be very handy when you're working on huge data and you would want to sort it in a particular order say ascending or descending or might be based on a particular field or if that field was or if the cell was highlighted with a particular color sorting the data so let's look at how Excel can be used for sorting and filtering examples are pretty simple here so let's check that so if we're going to sort and filter and say this is the data I have say for example I would want to sort the values in the department column alphabetically so what I can do is I can select Department column and I'm already in the Home tab I can straight away go here which says sort and filter I can then say sort A to Z and that's basically alphabetically sorting your department column and once once I do this you would see the data has been sorted but it's not just this data we can just do a control Zed and check what are the values we have so here we have meat which is beef and 90,000 110,000 the values then you have Bakery which should ideally be the first row if we sort it in the alphabetical order which goes with Bakery is deserts you have the values so we can check this again so select department and then just do a sort and filter and let's say sort a to zed and if you see the data has changed but it's not just in changing your First Column but then it has taken care of all the data however the data has been sorted based on the department column so you have Bakery which aligns with deserts which has the values and now we have all the data which has been filtered now what we can also do is we can sort December's amounts from largest to smallest so what we can do is we can basically click any sell in the December column let's say 20,000 and then what I can do is I can go into sort filter and then I can say sort largest to smallest so if you see Bakery breads is the row which has the smallest data or might be you have Dai sandwiches so that one looks also smaller so let's do a larger to smallest and if I do this you would see the values have been shifted now so it is no more based on the department column because now the data is being sorted based on the values in the December column and you see Bakery which was alphabetically the first one has become second last so either you can sort the data based on a department column which goes based on the values these are all string values or words so it sorts alphabetically if you have numbers mightbe you can give some values and you can sort the data you could anytime do a custom sort and you could basically select if you would want to select the data so I could do a custom sort and then I could choose which is the column which we would want to use for sorting what is the Sorting needs to be done is it cell values is it cell color font color conditional formatting and then you can also choose the order so that's one more way to do that now if you scroll down that also shows how you can sort by date or a color so for example if you would want to sort based on the expense date so there are different options so what I can do is I can select this date field I can just do a right click I can go into sort and then I can choose I would want to sort oldest to newest so since I selected the date field it basically has sorted all the data and it has taken the expense date into consideration now there are these filters which you see on the row headings we could have also used those so I could have selected this and that basically says or mentions which are the dates I would be interested in looking at I also have sought by color here I can do a sort oldest to newest or newest to oldest so I could also use these filters which have been applied here now we have the data which is in color so if I would want to basically select the color columns or color cells I could select this I can basically do a right click here and when I go into sort I could choose put selected color or cell color on the top and that basically will make sure that my data is sorted and it has also sorted that in a descending order so in this way you can sort or basically filter your data what we can also do is we can add filters so sometimes we can go for formulas which we would want to use what we can also do is we can basically select the filter which has been applied here now how does the filter come in there so if I would select a particular row I could select a particular row and then I could decide if I would want to just add a filter to this one and that's how the filter has come in so we have the filter what we can do is we can basically click on this drop down and then you have something like number filters so we can always go here we can basically choose one of these so we can basically choose above average so I could select this and then basically it shows me the values we could also delete the filter by clicking on this one and we could say Well I'm not interested in this filter anymore so I could clear the filter and that shows me all the values or I could say that let's click on some other field for example food I can go in here I can going to number filters and then I could say well I'm interested in values which are below average above average might be greater than and then I can choose what is the value so for example if we say I'm interested in food which is greater than $25 I could give a value here I could say okay and now I have applied the filter similarly you can select this and then you can just clear your filter and your data is back so remember no data is lost it is just hidden or basically based on the filter not shown so that's good enough for us and in this way you can sort and and filter the data so for more details obviously in all these sheets you have the links which point to more information on the web and you can always refer to these so this is simple way in which you can sort and filter any amount of data which has been stored within your Excel within a particular sheet now that we have learned about add fill split transpose sorting and filtering it will also be good to learn how to work quit tables or basically converting your data into a tabular format and then doing some easy computations so click on this tables option here now here we see there is some data which is in five columns and N number of rows so I can basically select this data and then what I can do is I can insert choose the table option and then it says my table as headers and we'll be okay with that I'll say okay and now if you see this is the table created it basically has different filters which we have learned earlier how to use and this is basically my table which is a collection of cells which has some special features so we can easily add rows to this table we can add columns to this table and we can even do some calculations so for example here I can click on this one I can basically Al enter some field and then I can hit on enter and we see that this row has been inserted where in we can easily fill in values for example I would say chocolates I could give some value here say 25,000 might be 35,000 and then basically I can give in some values here now what you can also do is you can continue adding rows in this way and say for example you would want more columns so you can select this option here in the top bottom right corner you can just drag it towards the right and that basically has automatically created columns for my next months wherein I can feed in the data so this is a simpler way wherein you can keep adding more rows and columns to your data if that has been converted in a tabular format now let me just do a control Zed that basically deletes the columns again control Zed deletes the last row which we added and I can stop here or I can even remove my values by doing multiple control Zed I'm removing my rows so this is how I converted my data into a table and then I can easily work on this what I can also do is I can do some calculations so what we can can do we have a table here we have a total field and what we can do is we can just select one cell here now as we have learned earlier we can do a alt and then equals and that basically says what is this doing so it says it is calculating the sum of the last 3 months and if that's what you would want to do just hit on enter and it has automatically calculated the totals for all your rows for these three columns so the sum formula is getting filled up now I can select any particular cell and I can look in my address bar so it has already given me the formula where it has started calculating the sum from the October column till the December column and has given me the calculated values of the columns what we can also do is we can get total row rows in the table now that's a simpler option so what we can do is we can select any cell in this particular table and then we see that there is a table tools design option showing up here now I can select this and then it says well let's get a total row so let's select this and it automatically populates the total here and if you would want the average then we could select this and from the drop-down I can select what I'm interested in so for example I would want the average values and not the total I could just select this and that gives me the average of these values so we can always do simpler computations here by converting our data into table format let's learn about one more efficient way of working with the data and that's using your drop-downs so let's see how drop downs work here now say for example you have this data which has the values in the food column and department is empty and say for example you would want to enter the values in Department however you would want to select the department should either have produce or meat and bakery and these are the only three options which should be available for any user to fill in the values how do we do that so we can into existing worksheet you can also say add this to a data model if you're doing some data modeling so click on okay and now this data is automatically inserted in my Excel file I can basically save it into this particular sheet now what we can also do is we can also start a new sheet and that does not have a data and we can get some other data from web so what I can do is I can go into my GitHub and let's say I would be interested in this CSV file so I can select this and this is my GitHub path a path on web so I can click on draw and that basically gives me the raw path where this particular file is now you can select this copy this particular path and here you can come back to your Excel sheet we would be interested in getting the data from web might be from a text file where we will have to specify the D limiters or let's go to web and here I can give in the web path from where I would be interested in getting the file let's give the GitHub path which is publicly available and then click on import now once you click on import it tells me there are two Fields data and value these are within double quotes separated by comma so first let's click on import now once we do this it will basically get the data from web and put in here it says existing worksheet so we had already created a new worksheet so let's click on okay and now you have the data coming in but then this basically shows me in one particular field so what we can also do is we can just do a control a that selects all my columns here and that's my data so we can then basically filter this out so we can say text to columns it's a Del limited file click on next we can select comma and let the text qualifier be codes it shows me data preview click on next so you have the general format it shows the destination that's the column click on finish and now your data has been split and you have the data which you have imported from web so this is the data which is coming in from web this is the data which came in from my local machine and similarly we can even create connection with an existing database so I can basically click on connections if I would want to do that so I have an option called connection here and it says where the selected connections are used I can basically click on ADD I can basically choose if I would want to get the files from Network or from computer like we did earlier I can click on browse for more which should show me different other options to create connections say you would want to create a new SQL Server Connection you can connect to a new data source coming in from different place you could basically choose what kind of connection would you want so these are all the different options which we can go for and and we can basically connect to a database for example if I have some database and say for example access database I can see if there are some files with that particular database and I can import it so similarly we can also uh click in here which says new query and that also gives you an option of getting the data from your files from all these folders from databases so you can basically click here and then you can import data from a mySQL database provided that is set up on your local machine or on a particular server you can go from cloud you can get it from online services you can get it from other sources which says from web from your Hadoop file system from active directory from a blank query and you can even combine queries wherein you can run a power query editor you can get the data from different sources and then you can bring it into your Excel so in this way you can get your data from different sources into your Excel into your spreadsheet and then you can continue working on those data sets we have uh already learned some basic operations which you can do in Excel and let's Implement our knowledge by working on this particular data which is coming in from housing data set now here if we see some fields we have agent date listed area list price and this is basically the data which has been sorted in newest to oldest order of date listed so how do we arrive at this so what I can do is I can just click on data listed and then I can either go in here and I can select sort I can get into custom sort and then I can choose the column based on which I would want to sort the data so I would look for the newest data to the oldest data that means that would be in a descending order of dates or you could say the oldest date or the earliest month will be towards the lower side of your sheet so here we can select date listed now I can say let it sort based on Cell values and the order what we have here so we have newest to oldest so let's select this I can say okay and now if you see the date has been sorted so we have your 1018 2007 on the top so that seems to be the latest date and as we go down we will see an earlier month hour and earlier month than that in this date listed so we have sorted our data into newest to oldest order and that's based on your date column so the result shows up here now what we can also do is we can have different questions which we would want to answer so for example I would want to sort the data in ascending order of area and descending order of agent name how do we do that so let's look into this so this is here I already have the result here and how did I get this so I'm looking for ascending order of area and descending order of agent name so we can start with any particular column that does not matter so for example if I look into this Excel sheet I have my agent name select this which we want in a descending order so we could either do a sort and then go for descending sort z2a we could also use the filter option here on the top right and we could do it or I could just say sort Z to A and then it has arranged the data based on the agent column being in descending order now I can go into area and then I can again do a sort and I wanted my area column to be used for sorting the data in ascending to descending and that basically not only changes the order of this particular column but for my complete data so let's do that and now if you see we have the data which has been sorted so we can see how many values we have here and the area values which we see and this is how you get your result so I'll just do a contrl zed and again and I'm back to my original data and this is the sorted result which we are seeing at similarly we can answer other questions for example sort the data according to the following order of area that is we are saying County Central and then your county so we can basically choose in which particular way we would want to do it so it is County Central and then again County so if I look into my sheet three so here I basically have my data which is having some South County then you have your Central and then you have your North County so we would want to sort the data to solve our problem which is according to the following order so first we go for area then we go for South County Central and North County so what we can do is we can basically have area field selected and I would want to sort this particular data so I have South County Central and North County so I can basically go for custom sort and then I can choose which is the value or column which I'm interested in let's go for area we will go for something like cell values well you can also try to explore conditional formatting icon if this is what you would want to use or we can basically go for just sell values and here we can say if I would be interested in first getting my values for South County so for example I can say custom list and then I can basically given the new list here so I can say s dot County and then I would want Central and then I would want North County so let's select this as it exists we can basically say add and that's basically the order which we want say okay and then say okay here and now what we would want is we would want our data so we can compare that with the values which we see here it starts with Kelly you have in the 12th row something like Lang and that's what we are doing so we can basically arrange the data in a particular order by choosing a custom list and then sort in your data so that's one more simpler task what we have done where we have sorted the data in the order where under our area column we first wanted South County then we wanted Central data and then we wanted North County so this is how you can do it now let's look into one more problem so it says find all the houses in the central area and we would want to basically apply a regular filter let's let's see how do we do that so we can click on this and here we have the data so the problem statement is we would want to find all the houses in Central Area now how do we do that we can do a sorting but we would want to use the filter which you see here is implemented so how do you do it so you can select this area and say for example I would want to apply filter I can just go in here and I can say let's get a filter on my first row and now I have filters applied so we are interested in looking into the Central Area houses let's go in here it says all these fields are selected that means it shows everything wherein your area has all these values let's unselect this and then I will only be interested in the central so let's say okay and then say okay so now you see the area filter has been applied and we are looking at the central column so what we have done is we have applied a simple filter and we are looking at our data at any point of time if you do not want the filter then what I can do is I can select this and I can say well I'm interested in all the data so I could do this or you can clear the filters from area and you get your data back so that's in one way you can filter out your data so let's look at an example of sort and filter where we might have to filter the data based on two columns or multiple columns with different kind of values where it could be and and or condition now say for example this is the data I have and this is the question which we need to answer such as find the list of all houses in the central region with pool and South County without pool now if it was a simple filter based on one cell I could have just selected my header row I could have then applied the filter and once I have the filter I can look in area where I have three regions so I'm only interested in Central and South County so I can get rid of North County and that's fine but then we have two different conditions here so we need the data in central region to have the value for pool being true and for South County the value has to be without pool now how do we do that so what we can do is we can first create a copy of these headers here so let me do that now the area has to be South County so basically I can either just select this and I can choose this value and then I can select this and then I can choose Central so that's the criteria which I have and the pool value has to be so central region should be with pool so then this one basically has to be true and this one has to be basically false so south county is without pool so let's select one of the values here and this is my criteria now to get my result uh we can always place your cursor here and you can check this is M column and eth row okay so we would want the result here so let's go ahead and now click on data and then in filter you have an option called Advanced and here what I can do is I can say I would want to filter the list in the place but that's not what I would want to do so I'll say copy to another location and here if you see the list range will tell me that this is the data so A1 to J 126 so a to J column selected and all the rows criteria range is basically based on what I've given here so that is M from 1 to V which is three so I'm selecting these columns and then I'm saying all the way whatever criteria I've given and copy two I'm saying M8 to V8 so that basically will give me my filtered result so you could basically just say okay and now I have my data which has been filtered and I have my area which is South County that is without pool Central with pool again South County without pool and then if you look at your central value that's pool so this is an advanced filter which we have applied where simply we have filtered the databased on two columns and then we have our result so in this way you can have your customized filter applied on two different columns and get your data which can be either replacing the existing content or or in the same sheet in different set of columns and rows you can have your result let's look at one more example of filtering where you are trying to filter the data based on a and condition condition being met in two different columns and then you would want to filter out the data for only specific columns so the situation is the agents with a house in North County that should be County area having two and a single type family so we are talking about two bedrooms and we would basically have a single type family and here the criteria is that we would want to only populate these columns which is Agent area bedroom and type now what you can do is as I explained earlier that you can get your result in the same sheet in a different location so here I have created these headers which says agent area bedrooms and type now this is basically a copy of all the columns what we have here agent data listed area list price bedrooms bath square feet and so on so you can basically create a copy of the headers here and this is where we will give our Advanced criteria to filter the data so the conditions which need to be met is we need to look at North count County so for example here in area I can basically go ahead and select one of the values North County now the criteria is having two bedrooms only so let's say bedrooms and let's say the value should be two and then basically I am saying a single type family so when you would want the single type family so here under type I can give the criteria single type so this is my and condition so we are saying North County area having two bedrooms and the type is single family now this is the criteria filtering so it's pretty easy you can click on this one and then you can go into number filters and you can choose between I have applied my filter and I have my data which is filtered based on my criteria right so that's one easier way of doing it or let me do a controll z now let the filter be there which you can anyways use but what we can also do is as we have seen earlier methods so get a set or this set of columns which I have 1 2 3 here we have 4 5 6 7 8 9 10 11 right so whenever you want a add condition you will basically add the columns where I can give add condition if it is the same column if it was a different column then it would be same number of columns but and conditions will lie in the same line and or condition would lie in a different line now here I can give this value so I'm looking for listed price being between 300,000 so I'm saying it should be greater than or equal to 300,000 and then I can say less than or equal to 400,000 now that's my criteria and then I need my result here which is in M7 so what I can do is once I've have given my criteria which I'll be using to filter I can get into Data I can get into advanced and then I can say copy to another location so it is selecting my A1 to j26 criteria range is based on M1 to W2 and then I would want my result from this particular column so let's say okay and now you have your data filtered out in a different location in the sheet which has been filtered based on your and condition so you can filter out the data in this way or you could just apply a filter on a column and give the conditions now let's solve one more interesting problem and here we would want to use Excel where we would have an and and an or condition so say for example this is the data given to you and the question is that you would want to find all the houses in North County again that's a spelling mistake but then they North County area with a list price greater than 300,000 and having three or four bedrooms so the bedroom has conditional so it has R three and four and then then basically you have list price which is greater than 300,000 now I could have obviously selected The Columns and then basically gone for a filter so I can just do a filtering here and then I'm looking for list price being greater than 300,000 so which we can always give a number filter and I can say greater than and then I can say greater than or equal so I can say greater than and then I can give 3,3 300,000 and that's basically the filter which we would want and here I would want to select the bedrooms which should be just either three or four so if for example I go in here and I unselect this and then I say three and four right so I am getting my data which is greater than 300,000 and it should have the beding values which will be either three or four selected now that's one way of doing it let's do a contrl zed and get it back to as we were or you can even just say clear filter so you get your data back as it was so what we can do is we can here give the criteria so for example I have my list price now this is what I would want as a condition so let's say greater than 300,000 300,000 and bedrooms should be three and then I can say greater than 300,000 and then I can say four so this one basically gives me a situation where your list price has to be greater than 300,000 and bedroom should be either three or four so we have given our fil in criteria now to get the result what we can do is we can go into Data we can go into advanced and we can say copy to another location so our list range is selected which is columns a to J row number 1 to 126 your criteria range is given in M1 to V3 where we have specified and we are saying the result would be in M7 to V7 so if I do this now I have got the same data which we were seeing earlier and here the bedroom values are three or four and basically the list price is greater than 300,000 so this is a simpler way in which you can create your filters and all this Advanced filtering what we are seeing it will be saved with your sheet you can always go back and change this value or you could do filtering where one person has to look into the filter to see what values have been selected now that we have looked into some operations which we can perform in Excel using filter or sorting the data creating your tables let's also quickly look at functions and formulas which can be used for doing some easy calculations or computations now Excel can be used in different kind of data analysis so for example you have different inbuilt functions which can be used and we can always check for a particular function so for example if I had if I wanted to look at a particular function I could just type in here something for example is and then it shows me all the possible functions and you can always have a look at the detail of the function for example you have is even which will return true if the number is even if we would say is logical so I could search for is logical and that tells me whether a value is logical value true or false and returns your value true and false now we can obviously say subtotal so you can search for any of these useful functions and that tells me what this function can be used for so returns a subtotal in a list or a database you have many other such functions such as integer sum average you may be interested in working on truncating some data getting the absolute value getting the square root basically getting a count or getting a max value you can look for any particular functions within your Excel sheet now you also have other functions such as now or time for example let's look at Now function so I can search for now and here it is so this is Returns the current date and time formatted as a date and time so this is the function which we would want to use and if I just give the function it tells me what is the current time let's first look at the description of time here so say for example I would want time it says converts hours minutes and seconds given as numbers to an Excel serial number formatted with a time format so for example if I would say 2 hours and then 30 minutes and 30 seconds and if I do this it has basically converted this into your time format so you can always use different inbuilt functions for your work now we will also look at some Advanced functions like some if or some ifs you have count F and count fs and you can be working on various functionalities of excel to easily help you in doing some calculations computations working with your data working with your different cell values so let's look at some example of using functions like sum or Su if so for that let's go to this sheet and here we have some data now I have already applied a filter which can allow me to filter out the data so it says find the total units that were sold in the east region now we know that in region we have east and I have multiple regions I could basically be saying unselect all and select only East and say okay and that basically gives me the units which which were sold and if I plac my cursor here and then if I did a auto sum so it would basically give me the function which is being used so something like subtotal and it is basically working on your rows which is E2 to e44 and here we can just do this so that gives me the total but this is this is fine you could do that but it would be good if we know how do we use a function like sum if to do that so here I'm seeing this is the subtotal where I'm looking at the values and basically what I have done is I have filtered out the region and then basically I'm getting a count but this does not give me clearly how a sum was calculated from all the values which were listed what we can also do is let's do a controll zed and let's get it back so now we have our data and we would want to get the total units that were sold in the east region so what I can do is I can start typing in my formula and for that I'll use a inbuilt function so for example I would be interested in going for sum if now it says sum if adds the cells specified by a given condition or criteria when you talk about some ifs this is is when you could give set of conditions or multiple criterias so let's look at some if let's do this now obviously this gives me an error because the formula is not right so we have to basically come in here and let's start with some if now when I say some if it shows me there is a function with Su if which we would want to use and here once I open up the bracket it tells me okay what is the range of data which you are interested in so I'm interested in all the units that were sold in the east region so we are interested in the region which is here so I can basically be selecting this and this tells me you are interested in the data here so let's not take the header value so let's say B2 and then we can go all all the way to the end so we can basically select this way that's the data we have select this and hit enter so now here it has selected B2 to B4 but we need to basically now give the criteria so the criteria is either a value or you can point it to a cell which has that particular value so as per our problem statement we are looking for the units which are sold in east region so I can select the value East here and then my sum if needs the range on which you want to calculate a sum so let's select this and now we are interested in finding out the sum of units so that's basically this column e column so I can basically type in instead of selecting so I can say e and I'm in E2 to e 44 so that basically selects the area or all the values and now let's do this so that basically gives me the sum is 691 right now this is the criteria where I have pointed it to a cell and whatever value that cell has well I could have done something like this so I could have selected East giving the value and then doing it it still does the same thing and in this way you have more clarity that you are using Su you are filtering the data so you have given the rows you are given the criteria and then you have given the range on which you would want to sum up the values now similarly if the question was what was the total revenue generated from binder now we would want to find out what is the total revenue generated from binder that means my filtering criteria will be binder and then I want to find out the total revenue generated so we have the revenue generated field also here and we have we don't have any region to be filtered we are just looking for binder so let's again start doing the same thing so we can go for some if we can open the bracket now it needs the range so we are interested in revenue generated now that's the summation we want and we would want to get the range of data so here we can basically select uh D2 to D 44 so that's the data selected now I would want to give the filtering criteria so let's say binder and and then we need to give the range on which the sum needs to be calculated so that's my Revenue column so that's G so I can say G2 to g44 and that basically selects the column and then you get your sum so it tells me what is the total revenue generated from binder now I could be doing this for other things also so say for example if you would want to filter out something else you could basically just drag and drop here and then I could come here and change this to say instead of binder I would be interested in say pencil if that's the criteria you are interested in remember to change this so that you take all the values and here we will change it to select the relevant rows and then this is the data I'm getting so I know that this is the revenue generated from pencil this is the revenue generated from binder now this is a simple use case where we are using some if what if we would want to use some ifs so some ifs let's have a look at how we get to some ifs so some ifs is where you would want to work on doing some calculation but then you would want multiple criterias to be met so let's see how we get this so here what I can do is let's work on this problem statement which says what is the total revenue generated from central region where the item is a pencil so that's something which I would want to check now when we are answering this question we can also look at the order in which things have been asked in the question so it says what is the total revenue generated from central region so we need the total revenue generated we know there is a revenue column we are interested in getting the total revenue generated we are saying the filtering criteria is central region and we say in that we would be interested only in the item if it is pencil how do I do it so I can use some ifs where you can pass in multiple criteria so let's start with some ifs and when I start with some ifs let's open up the bracket so it says some range it says criteria range then it gives one criteria and you can give in any number of criterias so for example we are interested in total revenue generated now that's my G column so let's follow in the same order so let's say G2 so that's my first value and then I know there are 44 rows here so I can say G 44 and you can obviously check if that has selected all the rows now that's my total revenue generated so I would want the total revenue generated so I'm saying setting this sum range then I need to give the criteria range so it says from central region so central region comes into column 2 that's B so let's say B2 to B44 so that's my criteria range then you have to give your criteria but we need to filter out the region being Central so let's select this now either I could point to a value in the cell or I can just give the exact value here we can also give a wild card or matching pattern so that also works now this one is fine we are now also interested in finding the total revenue generated when the region is Central and the item is pencil how do we do that so for item we know the columns the column is d 2 so let's select D2 2 d44 so that basically selects all the rows in the D column and we need to give the filtering criteria so let's do a comma and then just given our value so let's say pencil and then let's close your bracket and that basically gives you the result so we need to just follow the order of our our question which says what is the total revenue generated so we are looking at the revenue column we are selecting all the rows then it says from the central region so we need to select the region column and give the filtering criteria Central or point to a cell which contains that value and then it says we would be interested only in item being pencil so then you select the column which has all the items and provide you a filtering criteria that is pencil so that's your easy calculation of using Su if which we compare with sum if here so Su if here was just having your criteria so basically you are selecting your rows giving your filtering criteria and then your sum range in some ifs we are giving multiple condition now same thing can be done here it says how many units were sold by sales representative Jones or Jones where the cost of each item was greater than four so how many units were sold by sales representative so when we talk about how many units that's your e column so let's start with that so let's say some ifs I would be interested in E column then let's let's give the range so it says Su range so those are the number of units on which we would want to find the sum then it says you need to give the criteria range so we say sales representative where the name is Jones so sales representative is in sales rep column C so let's say C2 to c44 now then we need to give our filtering criteria so let's say Jones is the sales representative where we are interested about whom we are interested in and then we the question says where the cost of each item so cost of each item is what we are interested in you have unit cost so that's what we are interested in so that would be F and then say F2 to f44 and then you need to give your cost so it says where each item is greater than 4 so let's select this and let's do this so this tells me 301 now similarly let's answer our third question which says how many units did Jones sell excluding pencil item so we would want to find out what was the total number of units units that were sold and that units or that should not include the pencil item how do we start doing this so let's start with some ifs now we know that you start with some ifs you need to give the sum range so we are interested in the number of units so let's basically go in and select our number of units which were sold so that's your column e so I can say E2 to e44 that's where I would want to perform the sum now I'm saying how many units that were sold where we are talking about sales rep being Jones so let's see let's select the columns C and then give the range after that we need to give our giving filtering criteria which is Jones and then we are interested in the items but excluding pencil so items is in column D so let's say d to d44 and then we have to give our criteria so we can say well that should exclude pencil so I can basically say pencil and let's close this and let's check so that's my formula which says that these are the number of units which the sales representative whose name is Jones had sold and that does not include pencil as an item let's also look at an example of using count if or count ifs now both of these can be very useful when you would want to calculate certain values so for example if I would want to work on count if let's try solving this problem now remember you can answer these questions using filters and that can be an easier way but then sometimes you may want to get the formulas so that you can make your spreadsheet and your calculation more dynamic in nature and that will basically depend on the values in the columns or rows so for example if I have find the total number of times Gil has made a sale now if I look at my data here it tells me that for every sales representative there is some value in the sale and it says sales has greater than three so for example Jones you have sales greater than three or you have Jardine which is sales greater than three and so on so what we are interested in is doing a quick count in finding out the total number of times Gil has made a sale how do we do that so we can use this count if function and if I go into count if it says counts the number of cells within a range that meet the given condition now what's our condition our condition is Gil and we would want to find out how many times the name say Gil appears or Gil has made a now I could just say count if and then open up a bracket I need to give a range so let's say we are interested in looking at the range so let's say we will choose sales rep so I can say C and then I can say C2 to c44 now that's my range let's not give that inod so you have to give a range so let's do a count if that selects the data and then we need to give the condition so for example let's here give the name which is Gil and then close this so that basically tells me it is five times skill appears here we can check this so I can go in here I can add a filter and then might be I would be interested interested in looking at only Gil and that basically gives me five right so we can always do that and we can be using formulas like this now what about this question so which basically says with sales representative made a sale more than three times now we it might be looking little confusing when I say for example let me clear out this filter now we have sales greater than three and we would want to find out which sales representative made a sale more than three times now I could basically check for every sales representative here if they have made a sale more than three times and what I can do is I can just say equals I can start with count if then I need my filtering criteria so that's your range so first thing is we will choose for example let's choose C2 to c44 and then we need to give and criteria what is the criteria we need basically a sales representative so I can choose the value here in C2 and then I can close this and then I can say the sale has to be greater than three and let's check so it tells me the Boolean Val value that yes this guy has made sales more than three times and what we can do is we can just drag and drop which basically gives me the value for other sales rep you can always check the value is automatically changing to this value in cell and for example let's go in here so this is obviously two so it says me false right and you can basically get the values for all your values so that basically tells me which sales representative has made a sale more than three times now like some ifs we also have count ifs where you can give multiple criterias so for example the question is how many orders were placed from the east region after this particular date so we have a date criteria we also have the region criteria and we need need to basically get the count of number of orders which were placed now I can in this case use count ifs and this basically says that you can start within criteria so it says how many orders were placed from east region after particular date so date is in My First Column so for example I could say say a start with two and say for example let's go a44 that should have selected all the rows and then I need to once I've given the criteria range I need to give the criteria so we are saying the date has to be greater than 10th Feb so let's Give It 2 10 2019 and then you need to say how many orders were placed so you need to give the criteria second criteria range so we are looking at the number of orders which were placed from the east region so when I would want to look at the region that's your column B so I can basically say B2 to B44 and here I would basically give my criteria so the criteria is East let's give that and once you have done this you would want to find out the total number of orders so let's select this and if I do this it tells me 13 now is that right so we are looking for your date your region being East and then getting the total total number of orders so here I can just do a count if I'm saying A2 to a44 wherein I have given the date criteria that it should be greater than 10th Feb because I do not want to count 10th Feb it says after 10th February and then you're saying the region has to be East so we would want to find out the total number of orders so my region is east and that gives me the result now similarly you can also find out how many times Gil sold pencils so here we will have to give the range so let's start with count ifs now here I would want that item is pencils so we can as well select column D2 d44 then you have to give your criteria so that's your pencil and then we are looking for sales rep which is Gil so that is my column C so let's say c 2 2 C 44 and the value should be just kill so it tells me it's twice where Gil has sold pencils so we can obviously check this by going in here choosing my filter and then let's search for rep being just Gil okay and now we are interested only in the item being pencil so I can say well let's get to pencil only and that tells me twice so you can obviously reverifying filters but using functions or using formulas it is always good to calculate and that can be making your computation and calculation more Dynamic let's look at one more interesting feature of Excel and that's your conditional formatting now as you see on the screen conditional for formatting has different rules which can be applied on your data and that allows you to basically differentiate or easily identify data values which are based on certain criterias or rules so when you talk about conditional formatting you have different options such as you can highlight cell rules you can get top and bottom values you can apply different rules apply different color scales and you you can easily manage these rules so conditional formatting is very useful for people who would want to work on huge amount of data and easily perform some data analysis it's easy to use as it is shown here and with your conditional formatting you can format cells based on a preset condition you can perform conditional formatting to identify cells you can highlight a few significant cells and you can easily perform conditional formatting as shown on the left side now how do we work with conditional formatting let's have a quick look so say for example we have our Excel sheet and if you see here I am highlighting the salesperson who have generated Revenue greater than 10,000 so we can be looking at the value where the revenue generated by a particular sales person is greater than 10,000 it has a particular color and how do we get here so for example let's select this data and what I could do is I could go into conditional formatting now I could basically highlight cell rules and we could just say greater than that's an easier way I could also go ahead and create a new rule but then I can use one of this option I can say greater than and let's give some value might be we would be interested in looking at any value greater than 12,000 so let's choose 12,000 and here it says what color would you want to select so for example I would say something like yellow fill with dark yellow text and let's say okay so right now what I'm doing is I have all the values where the revenue generator was greater than 10,000 but then I have also selected all the sales people who have made or who have generated Revenue greater than 12,000 so I can just do a control Zed to see the previous result now here I had the values which were greater than 10,000 and the one which we did just now basically highlighted the values which are greater than 12,000 so this is one simple example now we can look at some other examples say for example you want to format cells using three color scale so if you look at the values here I have a three-c color scale mainly in green yellow and red and how do you do this so for example I can go in here and I can go to conditional formatting so I would want to go for color scales and here you can create different rules so we can set up a two-color scale so we can say format only values that are above and below average I can format only cells that contain something I can get the top and bottom value so these are different ways in which I can have a three color based scale now what I will do is I will select this and let me show you the rule which I have so for example I can go into manage rules and if you see here there are certain rules which have been specified now what does that mean so you would want to specify a three-grade scale so for example if I would want to look at my first rule it tells me that I'm choosing three color scale I can choose lowest value percentile and highest value and that basically will select the cells based on their values so what we could have done is I can basically use one of these values I can delete these rules which I have created so for example I have all these rules but you should always carefully remember that the rules will be applied in the order shown so for example if I just just delete these rules and then say apply and say okay my data is back now it does not have any highlighting now I can go in here I can say condition sorry conditional formatting I could go for color scales or I could basically go into new rule so I would want the cells to be using three color scale so let's choose three color scale now when you say three color scale it says what will be the color of lowest value and we could choose might be any one of this let's choose red I can say midpoint is percentile 50 and then the highest value is green and if that looks good let's say okay and now if you see the lowest values have been highlighted as red you have mid values and then you you have the positive value so this is a three-c color scale and that easily helps me in identifying the data based on the cell values now in conditional formatting what you can also do is you can basically color the sales based on their value so what we are seeing here is if the revenue generated is greater than average then that shows in green and if the revenue new generated is lesser than average that's shows in Orange now how do we do that so we can basically again manage some rules so I can basically create a new rule now here I can select one of the options which says format only values that are above or below average and that's the option I would want to select now I can select this and it says format values that are above average so in our case we had it in green so for example I'll say above average and then here I can go for a particular color so you can go for a particular size so let's go and look into the formatting so for example let's choose yellow say okay now I'm saying wherever the cell values are above average it would be yellow instead of green and let's go in here let's go and look into manage rules so this is basically the rule which we are applying now we can also add a new rule and I need to select the values so for example I will say here so we had gone for above now we'll go for below we'll go for format we will we will choose red we'll say okay we'll say now these are basically the rules which we have created and here it says applies to your data so right now it has not been applied so for example if I select this and then I could basically choose my area just hit on enter and similarly you can go in here and then select your area hit on enter and say apply say okay and now if you see I have really chosen bright colors but then I have said wherever my revenue generated is above average it should be in yellow and below average should be in Red so we wanted above average to be in green and below average to be in Orange so that's what we have here right so you can always color code your cell values based on some rules which you are setting up now similarly you can also find the top 10 and bottom 10 values and that's pretty easy so you can just select this and then you can go into conditional formatting you can go for top and bottom values top 10 items bottom 10 items or you can go in for more rules so you can say format only top or bottom ranked values so you have top 10 now you can choose the color and for example I'll go for blue and I'll say okay so now if you see my top 10 values are blue now similarly I can add one more rule so I can say new rule and I can say let's go for top for bottom let's go for bottom let's go for format let's say orange say okay say okay and that's it so now you have your values which are top or bottom 10 values so you're using conditional formatting where you are basically highlighting your cell values based on different colors and here easy conditional formatting based on different rules helps us to do that now similarly you can also have the values which is basically showing you how the values are increasing so what we can do is we can select our colums either you could apply this to all the columns now here I have applied this only to Jan and April now I could apply this for June so let's say June so you can go for gradient fill you you can go for solid fill you can obviously just select the color and that takes care of the things you can say for example select this and now this is selected but I would want to might be format this so I can go in here I can go into manage rules now that will tell me what rule has been applied in the order so I can just do a edit Rule and that basically says this is a solid fill which is color you have no border this is basically color is black now I can go for something like gradient fill and I can say okay and now if I say apply and okay so this basically is Like Your First Column so you can use conditional formatting for various use cases and you can highlight the values so anyone who would look at the value would automatically notice which are the higher values which are lower values vales might be here the revenue is getting generated or was getting generated but did not grow Beyond a particular value and so on now similarly you can also go in for different options say for example here we would want to see if the revenue was dropping or if the revenue was if the revenue decreased or say for example if the revenue Vue was going up for this particular sales person so here we are looking at Carol so in Jan the revenue Generation by sales was very high then in Feb it was falling down in March it was kind of stable then in April it went way below so we can obviously work on this wherein we can grade our cell values so what we can do is we can go in for highlighting the cell values now you can go for color scales you can go for Icon sets and this is where you can choose your different shapes so you could choose one of these shapes so for example I would be interested in looking at the indicators like directional I could go using this three arrows I can go in for this color I can choose directional and then my values are automatically using directional now what we can also do is we can then go into manage rules and that basically tells me what rules have been applied so for example the latest one is the icon set which I have chosen it shows the selected columns I can obviously do a edit Rule and then I can choose so I'm saying the format style is Ion con sets I'm not using a data bar I'm not using color scale now here I have chosen the style of icons and then here you can basically give some values so you have icon which is green when the value is greater than or equal to 67 percentage when I say hyphen or minus it is less than 67 it's way below 33 but percentage then you give this value so you can obviously edit and easily highlight your cell values based on this icon set so I can apply this and that's how I use conditional formatting so conditional formatting can be very useful if you would want to use icon set if you want to use your datab bars if you would want to highlight particular values if you would want to color code based on some calculation if you would want to use a three-c color or a two color scale or if you would want to just find out values based on some simple calculation so conditional formatting is used extensively by data analysts or people who are working business intelligence teams or people who would want to use Excel to easily identify the data easily identify the cells which contain particular value or finding out less significant or more significant cells to then pull out values and carry out your computations calculations or analysis let's continue learning on using Excel for various things now we have learned on conditional formatting and seen how that can be very helpful let's learn one more feature of Excel and that's basically your data validation now this can be very useful when you would want to work on validating the data which is being fed in the cells so you could limit it to basically a number between a particular value you could also add some messages to it if you would want or you could even Circle invalid data or clear validation circles so data validation really helps us in validating the data which is being fed in two particular Fields now it's a feature in Excel which is mainly used to control what a user can fill in a Cell you can decide what type of values must be entered you can also restrict user to enter only valid data and if any invalid data is entered an error message will be displayed now that's where you can use your data validations so let's see how that can be done so for data validation let's see some exercises here so for example you have a name column and you would want to restrict that the name should accept only 15 characters now how do you do that so you can basically select the cells or you can just select a particular cell and then we can later drag the property to other fields now here once the cell is selected so for example let's try this out and let's see if that works so for example I will say Peter johnon okay and that is basically five and 9 and 12 characters so let's say junior and if I do this it says input length is greater than 15 do you want to continue if I say yes right so basically it is allowing me to add the value here but then it has basically violated the rule now this is giving a message to the user that the username should be entered less than 15 characters now how do we do that so for this we can basically select the column and then we can search for data Tab and get into Data validation so this is where you can create or select different kind of rules so for example I can go into Data validation I can go into settings and I can say the text length and that should be less than 15 now this is the maximum I'm giving and it says apply these changes to all other cells within the same settings so I can do this or I can just drag and drop so I can basically apply this formula and now you can in fact randomly check in any particular cell is the rule applied so it says text length less than 15 so we can basically Control Data validation in this particular column and that will allow only 15 characters it will pop up a message if the user really wants to go beyond the particular limit now you also have similarly date of birth so the Restriction is date of birth should should be between 10 Jan 1990 to 30 December 1998 so this is what we want to restrict how do we do that so we can select the field we can click on data validation and if you see here I have selected date and then date is between and then obviously you can give a range that is 10 Jan date 1990 12 30 1998 so that's the start and the end filter which has been applied and once this is done you can also check your input messages which says when sell is selected show this input message enter a valid date so if you see here on the left there is a popup which is coming up which says enter a valid date now I can also say when user enters invalid data show this error alert so I can say stop I can say invalid entered and this is how I have set a rule so for example if I just do something like this and that says invalidate entered I can do a retry it will take me back here but unless and until I do not give the right format the date will not be accepted and again the same thing applies to all the celles similarly email so we are saying the email should have at the R present in the value provided now for this we can use a formula and we can select well I would want to apply this rule to all these rows starting from C2 to c14 so let's get into Data validation let's look in settings and here we are choosing custom now within custom I'm choosing what is the formula so I'm saying is number and then I'm saying find at the rate for the rows C2 to c14 so the only thing we are concerned about here is the value should have an email icon you can input a particular message you can say what has to be done for error alert so for example we can basically go in here and we can say invalid email that's the title I'm giving and we can say email should contain at theate so if I do this and if I say okay now you can test it so you can say A B C D and that says email should contain at the rate and basically that will not allow me to add the values so now you have the field called salary it says salary should be greater than 50,000 now we can limit the values by choosing a whole number so for example for salary I can go into Data validation I can go into settings so here I can say something like decimal or I can go for whole number so both of the things are fine it depends on what kind of values get into this particular field so if if I say whole number and if I say it has to be greater than 50,000 so I'm saying the minimum is 50,000 or I could have given a decimal and then I can say greater than less than equal or anything or even between so I can select greater than and then I can just say okay now for rank the rule is rank should be between 100 and 200 so again we can use a whole number so ranks will generally not be decimal so this would be whole number salary is can be a whole number or it could be decimals so we have chosen decimal here and in rank if you click on data validation I have chosen whole number I've set data between 100 and 200 input message nothing error alert nothing but that depends I can give this so this is how you can just do a simple data validation and control the values which land in the cell okay so now let's also understand how we can restrict the values in a particular cell which might be based on a list of items now for example here if you see I have two columns so one column basically has the values of city names and then you have places within that particular City so if I would want to implement a data validation based on this so for example if you see here City and that basically shows me only the four values which can be entered and if I go to place then it tells me for Maharashtra I can only enter Pune Mumbai nask now how do I do this so say for example you take an empty field and you want to restrict the values of city names so I could select data valid I could basically select list and then it tells me you need to enter the list values that is the source so you click here and then I can just select these fields now if I do this and if I say okay it has implemented a data validation but if you look into this it will show me the same thing but then it shows me some empty cells which did not have any value so this is fine but it would be better if we do it in a different way so I can select this and I have already given my city names here so I can just do a data validation and here in the source let me get rid of this now I can just have my cursor here I can select these values and then if I say okay so now if you see my city names have been restricted to these values and that's how you can implement your data validation so I have this data validation here but I will get rid of this one by just doing a control Z now I'll come here and say for example I would want to implement the same data validation now the easiest way would be in doing this for all my four cities now if you see here I have data validation I could choose Bangalore and if you come and check here there is no data validation but we have implemented data validation here now how is that done so I could select this or for example I can go in here I can select data validation I can select list and then here in the source I will say for Bangalore the values should be these and then if I basically say okay now if we see these are the values which are fed into bank so we could do this or like what we have done here so if you can check the data validation rule I have used list and then I have said indirect F2 so basically I'm giving in a formula which relates to the value which is in for the city Maharashtra so we could do this or in a simpler way we could do this and then just drag and drop here so we could check for m Maharashtra what are the values let's choose a different city so for example Kolkata and here I will choose now since we did a drag and drop it has basically taken the values of Bangalore so that's not right so we select this we go into Data validation and here I can either feed in the values that is such as Bangalore I could basically say something like this Kolkata and I could say okay so now if you see it shows me the places in Kolkata now here we have let's choose a different city so for example let's go for Delhi and here I can go to data validation and I can just say Deli so this is an easier way of doing it or if you remember the formula then what you can do is you can just given indirect and then basically give the value of the cell for which you would want to keeping the values so this is how you can do list validation so you can provide a list of values and then you can restrict the values in a Cell which should be belonging to a particular list so this is how you do a simple data validation by restricting the data in the form of a list now let's learn about pivote charts and tables which is one more very useful feature of Excel and which allows you to work with your data or perform data analysis now pivot table is a summary of your data it is used in cases where there are numerous rows and columns in your data set and it allows you to group your data in several ways so that you can derive meaningful information from it now the visual representation of a pivote table is termed as a pivote chart now how do we do that so let's see an example now here is some data here you see we have some row labels which basically gives me some category of items here it also tells me the sum of sales and basically it gives me per item what is the total sum of sales which were made now how do I get this here so for example if I would want to delete this and I would be interested in getting the total sales under each category of items now we know that we have different data here and it's huge for example on the row end if I just do a control and write Arrow it tells me what's my right last column and if I just do a control and down arrow it tells me there are 9,994 rows now I can use this and what I can do is I can basically work on insert and go to pivote table now this basically tells you need to choose the range now since I have chosen my first row and first colum it basically has selected the data and that's fine now do you want the result in a new worksheet I'll say existing worksheet and I can choose a location now that basically needs me to select a field might be we can just select this field and that should be good enough so for example if I close this or if I say okay so this is basically an indication that my pivote table will be created now what are the fields we are interested in so as per the problem we need to find out what the total sales for each category so let's go in here and let's select the field category now as soon as you do that it shows that the rows which are being selected are for this column category and now we are interested in getting the sales so we would want the sales per category so let's select this so you see some of sales is selected now close this and that's it that's that's your data so it shows you row labels it tells you what are the different categories and it also gives you sum of sales which is per category now this is one easy example where we have solved the problem where we have tried to find what were the total sales under each category of items so for example let's have another data set so again you can just check how many rows we have and it is basically the same 9,994 and if I look in columns it basically shows me profit is the last column and here we would want to find out which category so for example let me get rid this with subcategory of items sold the maximum under each category so we have some categories here which says furniture and it shows some subcategories and then it shows me office supplies and then some subcategories and same thing with technology now we know how we got this data but this is different than what we were seeing here so here we were just getting the categories what but we did not have any breakdown of subcategories within this particular category and here you see there is a breakdown and then basically you can also apply a filter which basically says what is the data you want and if you are interested in finding out a particular value now how do we do this so for example let me get rid of this now we know how we can find out the category of items but we would want to find out which subcategory of items sold the maximum under each category so here we are not looking at sales but we are looking at the quantity okay so what we can do is we can go to the first row and First Column click on insert click on pivote table data is already selected let's choose existing worksheet let's choose a location in our worksheet where we would want to have this so mightbe I can select this particular field and then just close this so it says this is the existing worksheet say okay and now you need to select your columns so for example if I would want the data for categories so I can select this now I know that within category these are the options which we have and then they is a subcategory so let's select that and if you see here now we have the data which will be filtered So based on your category and subcategory and then we will choose quantity so it automatically understands that this is something on which summation can be done so let's select this and close on this now if you see here we have got our items where we can see the subcategory within a particular category now I can basically close this and that just shows me high level categories here so here I can again select a particular row and column I can go for either a new pivote table so we could basically select this the row is already selected existing worksheet is what I want so so now I will select this particular place say okay now we do the same thing like what we did earlier so we will select category subcategory and quantity so the data has already come in just select this and now you already have all your data but this is not what we want we want to basically apply a filter to this now how do we do that so we can go for slices so I can go for insert slicer and then it tells what are the subcategories which you are interested in so for example if I go for something like subcategory and say Okay so this tells me what are the categories you are interested in so for example we know that furniture has Furnishings with the highest value so I can select say furnishings and then I was interested in office supplies So within office supply I can basically search for here binder so I can just do a control and select binder and then I can also know for technology I can select phones so I'll say control and select phones and now if you see the filter has been applied and we have the sub categories which are selected so we have our data here now we don't need really this so I can just delete this and I have my data which shows me Furniture being the main category and that has only Furnishings office supply having binders and Technology having phones and that basically is a filter applied on the result of your pivot so this is how we are seeing which subcategory of items were sold maximum for each category so this is how you can easily use pivote tables and you can do some analysis let's look at some more examples now let's see if you have to answer this question which says which were the top three states for each region that made the highest average profit now how do we calculate that so for example this is the data we have and as I instructed earlier you can select the first row and First Column you can click on insert go into P table and that basically selects all your data we will choose the result should be in this existing worksheet so select this click here and then basically let's select this cell and I will say okay so now my pivote table will be here so we are interested in finding out top three states Within in each region which have made the highest profit or which have made the highest average profit so for that first we will select region so when we select region it shows here region and within region you will then select state So within every region you have multiple States so that's what we are selecting and then we are interested in looking at the profit so let's select profit and that automatically shows up here so we can now say close this so basically we have all the data but we are looking at the sum of profit but that's not what we want we are looking for top three states from each region that made the highest average profit now how do we change that so here we have sum of profit so I can do a right click and basically I can say summarize values by every average so now I'm getting the average here and that basically tells per region I have the average profit but first is let's sort this data so let's go to sort more options and here I have average of profit or for example as I said you can select region say okay and now it is basically having the regions and then you have have your state value so again we can do a sorting here and I can say let's go for more sort options I'll say descending the state values should be based on average profit so let's say okay and now if you see in particular region we have the data which has been sorted wherein you have the highest average profit on the top now what we can also do is we can basically basically then go for filtering we can say top 10 but I'm not interested in top 10 so I could reduce this to top three and this is the items average of profit say okay and now you see per region you have the data which is showing me top three states what are the average and then you are also looking at the subtotal so which basically says average profit for or your West now if you would want to arrange this your west and south and east and Central in a particular alphabetical order if you would want to sort it you can always do a sorting and you can choose sort Z to A if that's what you would want to do or you can go for sorting more sort options and you want to sort the region which is as of now based on descending z2a let's make it ascending say okay and now you see the data has been filtered so it says per region now you can always select this and you can say what is this so this is basically your average of profit now we are looking at each region and the states the top three states as we wanted within the region and what is the average now we have one more question what is the percentage contribution of each subcategory of products under each category to the total sales so we know there are different products there are different categories and all the categories contribute to the total sales so we would want to find out what is the percentage contribution of each subcategory of products under each category so we not only want the category percentage but we would also want what is the contribution of each subcategory to each category to the total sales how do we do that so for that we need to again just place our cursor here or select the first row and First Column click on your insert click on your pivote table and now the data is already selected existing worksheet place your cursor here and then let's select this cell say okay now your pivot table fields are here so we are interested in category so let's select category and then within a category we will have subcategories and we are interested in the sales data wherein it is already going to give us the sum of sales but we would want this in the form of percentage rather than just the sum now we can click here value field settings and it says if you would want to summarize this it says show value as and what are the values you would want so you can straight away go ahead here and say percentage of grand total so we could do that because we know we will get a sum of sales and grand total now I could basically say percentage of grand total say okay and then just close this so once you have have closed this you see already the data is in percentage so now we have our data with subcategories but we also want the data to be sorted might be in a descending order which tells which subcategory is contributing more than others we can select how many subcategories we are interested in so we can basically select one of the subcategory Sals right click now you have option filter and you have sorting so we can say more sort options now here I will say descending and you want the descending to be sum of sales which is already showing as percentage click on okay and now you see for each category it shows the percentage contribution towards the sales it shows the subcategories contribution to a particular category and also to total sales so technically speaking if you look at all these values which are subcategories and if you would total them that would be your total grand total so this is how we can solve a simple problem like this what is the percentage contribution of each subcategory of products under each category to the total sales the next question is which customer made the lowest profit in the home office segment in each state now here we know that we are looking for home office segment we are looking for customer which made the lowest profit we are also looking for the state as the main criteria so in each state we would look for home office segment and within that we would look for customer which has made the lowest profit that means the lowest value of the profit so how do we do this so let's say let's get into selecting our row and column we will go into insert I'll say pivote table I'll say existing worksheet and let me get my result here so let's say okay now that gives me the P table so the first thing is we are looking at per state and I would be interested [Music] in home office segment or I could first start with segment now here we have segment and in segment I can basically select home office so I can just do a uncheck and select home office only and now I can select state so that will be my subcategory within segment and then within State we would want to find out the customer so let's get the customer name and we are interested in finding out the lowest profit so let's select profit and here we have some of profits so the fields are selected close this and now you have your data which basically shows me the names of customers which made the profit but it's it's not sorted so we can sort this and we have state entries also so that's fine so what I can do is here I can basically say sorting go to more sort options I can say I want to do a descending or I want to do an ascending an ascending based on sum of profit so let's do that now you see the topmost value is the lowest value per state within home office segment now what we also want is we just want one value per state so how do we do that so we can just go for filtering go for top 10 values I'm interested in bottom value and in that also I'm looking for only the lowest value so let's select one say okay and now you see we have data for home office segment per state customer who has made the lowest profit so we have easily answered this question using our pivote table now say for example we have a question which says find the sales made in each quarter of 2016 for all the regions so the data has to be divided quarterly and use order year as a slicer we can do that by selecting what data we would want to slice and then we would want to create a pivote chart also so for example how do we do that so for example I will get rid of this I will get get rid of this my pivote chart which we can recreate so find the sales made in each quarter of 2016 for all the regions so this is what we want now how do we do this so for example let's select this we will do a insert pivote table we will basically say existing worksheet and then I can select this particular cell I'll say okay and now I'm interested in data quarterly so first let's select our field here so we should basically have if all our fields are selected say for example if I say order date now you see we have quarters and years which are selected now we could be selecting these fields which says qu qus years and if I would want data to be filtered based on years I could do that or I could get rid of years so I have quarters I have order date so that's being already selected now what else we need we need to find out for every region so let's select the region and we can also add region as a column here so that will basically give me all the regions as different columns and then finally we want the sales so let's select this and now let's look what we have so if you see here we have our data which is for each quarter it gives me the total data but what we would be interested in is not looking at the total data but we are only interested in quarter of 2016 now how do we do that so what we do here is we have row labels so let's click on this so we have date filters and then in date filters we can go for between and then we will say 2016 and the value has to be one and then let's select this and let's change this to 2016 and this is 12th month and then we can say 31 so we are saying the date has to be 2016 and it basically says not a valid date so let's select let's select a particular date and see so the month has to be here so let's change this and let's make it date and say okay and now we have our data which is for 2016 so for example I could basically select this and here I could be looking at what are the filters we have so it says clear filter from order data so we have applied the filter on order date and these are your date filters which is basically date 16 oh this is wrong so this one has to be changed to 20 2016 so select this and now we have our data for 2016 so we have found the sales made in each quarter of 2016 for all the regions the grand total for a particular quarter if we are looking at the value or for individual months in a particular quarter and we have sliced the data now we could have also introduced a slicer by selecting which field we would want to implement so for example if I would select a particular field and then I could click on slicer I could choose I would want to slice the date based on order date and then could have done it or we have just given the date for 2016 now we just need to plot a graph for this and that's very easy so you just need to select the complete pivote data and here you have the chart options so let's go for line chart and that basically shows me the line chart so we can select this we can just drag and drop it here and then we can basically check if that is showing us the data so if you see here this is line chart where it has divided the regions in different colors so we have all the regions we could filter out in chart a particular region we have the quarterly time here in order data the filter is applied it says it is 2016 in quarters it tells me it is 16 so we have our data we have created a pivote chart and basically we have sliced the data for 2016 let's answer one more question and that's finding the profit made in each year for all the categories of products in east and west regions only and then we would want to create a histogram for the same pivote table so histogram usually gives us the frequencies so let's look at how do we calculate the profit made in each year for all the categories so what I can do is I can basically select my row ID here and now I'll do a pivote table so the data is already selected I'll choose existing worksheet let's select this particular cell say okay now what are we interested in now if you look at the fields we don't see any years or quarters and so on so for example here I can just choose order date and then basically I can choose say for example years or quarters the data which is coming out from my order date so here I will just select years now then I have my order date so we are interested in the Years orders and we are interested in let's look at our column or the question what we had here so we have years find the profit made in each year here so let's select profit so that basically says sum of profit but then I'm also interested in for all the categories of products so what I can do is here I have my field which is category and that I'll add to the columns so that shows me what are the different categories and this looks fine now I'm also interested in east and west regions so that is basically in the region so either we could add a slicer or we could do a filtering here by adding the filters so first is I'll click on region and here it says select all I'm only interested in east and west so let's select this take this put it here let's say okay and now we have the data which has been filtered based on the region so here we have region so you can always look at the filter it is east and west and we have our year data per year we have different categories you can always look at what are these column labels so this is my different categories and this is the profit per category and then you have your grand total so we have already our data now let's say close this so we have our data for all the these years different categories the profits made per year and if I would want I could go into an year and I could look into different months so that also is showing up and now we would want to basically plot a histogram with this data so I can just place my cursor here I can select this and then I can go into insert and here I have different options so we can go for bar chart we can go for insert hierarchy chart we can go into waterfall funnel stock surface or radar chart so there are different options what you have here and we will go for a simple histogram which is two-dimensional let's select this and here you see you can obviously select different regions you have your ears which shows up you have your order date and then you have your category which can be chosen for your histogram or for your bar chart and that basically is how you use your pivote table you use some calculations and then you can plot your needed graph to visualize the data and understand it in a better way hi everyone welcome to this tutorial on Microsoft Excel let's look at an example of uh one more useful function in Excel and that is we lookup now vlookup is used to see if a value exists in a range of values and we can create a range of values which is nothing but a block of cells which has a name and can be referenced so mainly to use V lookup we need to create a named range and that can be referenced anywhere in the workbook or the other option is you can always drag and select uh the range of data in which you would want to search something now there is also U other functions such as hookup which pretty much does the same thing however it does it horizontally that is left to right instead of vertically there are other functions like look up and X look up and so on so when we talk about we lookup let's see how we do that so for example this is the range of or this is the data which has been given to you and here is the question find the employee name with employee ID 107 now assume that you might have huge amount of data and you may want to search something so how do we do it now here I have created some column and then some basic formatting I've given it as an employee ID I've given employee name now what I can do is I can create a range of data now how do I do that or a named range so what you can do is you can select the data and then you can do a right click now here you have something like get data from table range so you can do that you can define a name so you can click on this and here you can give a name to the range what you would want to call it so for example let's say our search now that's the named range I'm creating and just say Okay so the scope is workbook and I've have created a named range which you can see here so it shows for search our search so you have created a named range now how do we do a v lookup here so for that we can use a formula so for example I would want to search for employee name where employee ID is whatever you would want to search for so employee ID we can give here 107 now that's what we would want to search the employee name for and employee name is what we are interested now what we can do is we can use the function option here I can do a insert function and that shows me different functions which are here now V lookup is showing up because I have recently searched for it or you can basically just type in here look and you can do a go so that shows you all the lookup options what you have like lookup H lookup match V lookup and so on so we would be interested in V look look up so let's select that and click on okay now that basically opens up this function arguments and it shows what does this function do so it says looks for a value in the leftmost column of a table and then returns a value in the same Row from a column you specify by default the table must be sorted in an ascending order now you can basically say this is what you would want to search for what is the lookup value if you place your cursor here it says what is the table array that is nothing but the range which we can use our named range here we have the column index number which basically tells me what is the column number in the range which we want so what is the value we want from which column and then basically you have range lookup which basically is the fourth option and range lookup is nothing but it can have two values so true or false so true basically means closest match and then false should be an exact match so let's do that so for lookup value we would want to give a particular value which is nothing but we would be interested in say employee name so I can basically search for a value based on the lookup value now how do we do it so we are saying we would want to search for employee ID which is 107 so what I can do is I can select this cell here which tells that this is the value or employee ID for which I would want to search the employee name I could actually select the cell here which is H so that whatever value you feed in here you can search for that value so mightbe we can say select this one which is H6 and whatever value we key in here we will be searching for employee name for that employee ID now table array is we would want to give a range so what we can do is we can give our range so for example we had selected something like our search and if you see here that already selects or shows me employee ID employee name email address so it gives me the range now column index number so which column are we interested in that means what is the value we are searching for so we are searching for employee name which is the second column so I can just say two and that basically is the column number and then you have to give a range lookup which is a logical value so either you give an exact match or you can basically give a closest match so we can say for example do a zero which basically means false or you can say one which is true so let's go for zero now that's our formula so it says we lookup you will search for the value as per S6 in the range and then you're looking for employee name which is column number two and then true or false so let's click on okay and that basically gives me the value which is peral birge so here I'm searching for employee name and you can basically drag this for other columns and you can do that so if it finds employee ID here now say for example if we had an employee ID somebody enters an employee ID which is not in the range for example let's try 121 and then you see this is basically an error right because it does not have any employee ID with 121 now how do I take care of this so there is a Formula which is called if error and that can be used to take care of this so if the value is not found then it will show whatever I would want to show here so what I can do is I can still select this and what I can do is I can search in function say for example this is the function function we are looking at but what we can do is we can test it here so for example if I look in function and if I search for error and say oh go so there are lots of functions here and this is the function we are interested in if error so if I say okay now this takes the value and what is the value if error so for Value what I can do is basically I can select the value from here which is nothing but our V lookup formula and value if error so for example I will basically say for example not found so we can try this and we can say okay now here it says not found so whenever it is na it is not found now if we look at the formula here it says if error it takes the value of H7 which is nothing but our V lookup and then it is showing us the value which we have entered so for example if I would say 107 like earlier and then hit on enter so it says panel of cruise and it shows us the value right and if we enter something else then basically here it shows Na and it shows not found but what we want to do is we want to merge these two so what I can do is I can get rid of or I can basically take this one now what I would want is I want this formula to be here so what I can do is I can say if error and then basically given your vlookup option or function here and then say for example not found here not found now that basically will take care of any kind of error if the value is not found so when we say 121 it basically says not found but if I say 107 which is found then it shows me the value so we are using V lookup but but then we are also using IF error which will take care of our error message or basically the not applicable pound not applicable replaced with whatever value we key in similarly we can do the same thing here so we have the find the job title so we are interested in finding out the job title once the employee name is given now employee name can basically have a value from the cell or whatever value you would want to key in how do we do it so we basically start with our V lookup so that's the function we are interested in so you can just do a double click now you are interested in finding out the value whichever is given here so that's the lookup value and then you are interested in finding out the job title from the range so I will still give our range which basically also shows for example let's do a double click here and that basically selects the range and now I'm interested in finding out the job title so that's column number four four and then I can just give zero which is for false so now we have given our V lookup but that shows not applicable because we have not given any employee name so we can key in the value here which says Fred Stone and now if we are basically giving the value so Fred Stone now we will have to search if there is any job title which is Fred Stone so here is the value so that should be showing up let's check what has gone wrong so we are giving our range we are interested in finding out the value but what the mistake what we did here was that when we want to search for a value say for example let's take this we look up and let's replace this value of h12 with the value here so Fred Stone and then basically we will give our range we will say which is the column we are interested in and then give true or false so we can also basically take care of this error message so for example let's take this complete formula from here and just just copy this formula and now we can put in here the formula but then this is where we were searching for S6 which was employ ID now that's not what we are interested in we are interested in finding out the value so let's take this one and replace this with the value given here and then I give my range I say which is the column I'm interested in so I'm interested in looking for job title and then let's search so it basically tells me not found so let's check this now the error which we were facing was because when we talk about your V lookup let's look at the formula again and here say for example I go for function and then I select we lookup and say for example I say okay now just look at the description here looks for a value in the leftmost column of a table now earlier the range which we had selected had the leftmost column as employee ID and that's why it was giving an error so how do we fix that so for example let's basically remove this function from here now what we want to do is we would want to search for the stock broker now if you see here I have given a range which is B1 to D21 so we would want the value for what we would want to search say employee name that should be the leftmost column so so that's how we have our function so let's do that so say for example the name is given here so we want a range so either we can create a range like earlier by selecting say for example this dat now am I interested in salary no I'm interested in job title right so I can basically just select these three columns and then I can define a name so I can say our search to and let's give a named range and that's only because we would want to easily reference or use that named search or named range now here we would want to use vlookup but we would also do an if error now within that we can say V look up and then you need to select what is the value what your function takes so basically this is my function we will say if the value is not found then we will give this so you can create an external structure and then within your V lookup we will say the leftmost column which we are interested in so that's your employee name so in employee name we have the value so either I can directly select the value here in the column b or I will give the value whichever is fed in here the employee name then I need to give a range so let's select our range so that's your our search to and then you need to select the column number so we are interested in finding out the job title so that's the third column so let's say three and then just give a zero and then hit on enter so it says not found because you have not given any employee name now let's try to find the job title of fredstone so I can just select or I can say equal and then I can just given a value and that basically shows me the job title so this is how you use we lookup we have also used if error which basically takes care of error message if the value is not found so for example we can test this here we have Fred Stone so for example if I just empty this and I point it to something which does not exist so that shows me not found or if you just select this and then key in a value it shows you this might be we can just type in so if I say Fred then basically it says not found because we are looking for exact match now if we had changed this to the closest match something like this then it would have tried to find something but then we don't have anything which is just with this so there are different ways of doing it so what we can do is we can always go for the exact match and that's basically the value and here we can basically key in any value for example I would be interested in finding out this one and let's give this so you can search in this way so you can also have statements which can be searching one related to other now here the question was to find the job title of fredstone so let's type in Fred Stone and that basically shows me the job title which we are interested in so let's search for job title let's go for exact match and then search so it is stock broker so if getting your learning started is half the battle what if you could do that for free visit scaleup by simply learn click on the link in the description to know more now that we have looked at we lookup it would be interesting to see how horizontal lookup or hedge lookup works it basically works in the on the same lines but here instead of vertically searching through the columns the range is more of horizontally defined so we search in rows rather than in columns so for example this is the data which has been given to you and you have various columns so you have employee ID you have your employee name you have employee email address you basically have the job title of different employees and then basically you have the salary so this is the data which has been given to you and you are asked to ser search or you're interested in finding out say the salary based on employee name or job title based on employee ID so what we need to do is we need to define the range here now range can depend on what you are searching for so for example when I'm searching employee name and salary so I am interested in these four rows when I'm saying employee ID and job title then might be this is the range and I don't need salary so I can create a named range if we are interested in so that we can easily search for it or we can straight away give the the first cell value which is basically A1 here and then the last value here which is G and then you can give five so you can do that or you can create a named range whichever way you would want to do it so for example we would want to create a named range for our first question which is find the salary of Eden Connell using H lookup so we are interested in this so let's select these four rows all the way for your columns now like we did earlier we can just Define a name mightbe we can call it our search three and uh we can just give okay so that's the named range which we have created now what we would want is we want to search for the salary based on employee name so here we can insert our function so let's look up and let's search for look and just click on go so here you have H lookup and basically that's the function I'm interested in say Okay so this says what is the lookup value which you are interested in so we are interested in finding out the value based on the employee name so for example let's say select this cell that gives us more dynamism and you can add in any name and you can search for the salary or you can basically give the employee name exactly from the employee name value here which is D2 so we can give addin conal from here so let's select this now table array so we need to give our range so let's say our search three then you need to give the row index number now this is different than V lookup in V lookup you have column index number and here you have row index number so what are you interested in searching for so we are interested in searching for salary now in our range that is basically the fourth row remember we created a range from employee name to salary so let's give the row index number which is four and the range lookup we'll say zero which is basically exact match and that completes our formula so click on okay and that basically gives me the salary for Eden conell now that's basically because we have fed in the value here and that is basically giving me this so I can again do if error if I would be interested in to basically take care of error message if the value is not found for that you can look into the previous example which is we lookup now similarly we are interested in finding out the job title for employee ID 1 05 how do we do that so we need to use Edge lookup well we could have done with v lookup but here the data is more arranged in the form of rows so how do we do that so we can again use hookup but and we can give a range or named range or let's for for this time let's basically key in our range manually so we can do that so let's go for H look up now what do we want to search for so we will say this is the value then we need to give a range now as I said if you would want to give a range manually so for example I'll say a and then I'll say A1 colon and then I'm interested in G and then the row will be four so salary row is not what we are we are not interested in so let's say G and then we can give four so that basically gives me the range right instead of having a named range so we can do that and then we need to give the row index number so we are interested in finding out the job title which is basically in our range the fourth row and then give zero for true or false so if you see here it tells if it is one it is approximate match false is an exact match and we can close this bracket that completes our formula it takes the value which is given an employee ID and then it will try to search for the job title so that basically shows wherever the employee ID is 105 the job title is fabricator again you can use if error wherein your if error can then be working on hookup and can take care of error so that if an employee ID is key in for which there is no matching value then it will not show you a not applicable error in instead of that it will replace with the word whatever you have specified so in this way you can easily use hookup to search from the range of data which is horizontally arranged and in the previous example we were seeing how you use vlookup when the data is arranged vertically so we have looked at V lookup and hookup and what we have seen is V lookup has a limitation in one way that we need to basically give a range where the leftmost column is the one which is used now instead of that we might be interested in searching in specific columns and not all the columns well that can also affect the time taken to search for the data wherein we can select particular columns or particular ranges rather than selecting all the data which has to be searched for so for example we have this question find the salary of Hank Saunders now that's the employee name which we have here and we would want to find out the salary so what are the columns we are interested in so one is your employee name and one is your salary and these are the only two columns which we are interested in so in instead of using V lookup now like I've shown earlier you can create a named range for these two columns and you can use that or we can manually give the range here so let's go for lookup function here and lookup function will basically help us in searching what we are interested in so we are interested in finding out the employee name and we are interested in finding out the salary based on the employee name so how do we do that so here in lookup I can first give the value which is here employee name now whatever employee name is keyed in here we will be searching for salary of that employee now lookup function needs a lookup value so we are searching based on employee name then it says lookup Vector so we will basically then say what is going to be our lookup Vector so here we need to give a range so what are we interested in we are interested in finding out the salary based on employee name so first goes our range based on which we need to search so that's your employee name so let's for example select this one so that's our range B1 to B21 so that will basically have the employee name in which we will be searching for and then we are interested in searching out the the result Vector that is basically the column from which we want the value that's our salary column so let's select this and that completes our lookup function so now if you hit on enter it shows that for this employee where the name is hunk Saunders the salary is 5,400 now you can test it by basically giving some other name so for example if I replace this with say Fred Stone and that basically shows me the value for Fred Stone we can replace it to the question which we have that is Hank Saunders and that shows me the salary so that's a simple example of Lookup which allows you to search by selecting a range of columns rather than having the leftmost column which has to be referenced similarly we have one more question find the employee ID of the this particular email ID so we need to again use our lookup function so we'll say lookup now what are we interested in we will search based on this email address so I will select this then I need to give that based on email address we are searching the employee ID so email address is in this column so let's select this range and then we are interested in employee ID so then goes our result Vector so select this column and that basically completes your formula so close this bracket and hit on enter so that basically shows me the employee ID for an employee with this email address so this is how you use lookup now as I've explained earlier you can be using the if error and you can basically see how your index match can be used now here is an example of if error so basically this shows how if error helps so say for example example as I showed in your sheet one so check for employee James Bond if the value is not present display not found and that what are we doing we are basically nesting our V lookup inside if error with a not found option which basically says if the value given in the cell Ed is not found then basically it will display not found so here you also notice that the range which has been given has been selected that is B1 to c21 now we could have used a named range which basically helps us in searching for multiple functions we are searching for two that is the column number two here that is employee name so check for employee James Bond if the value is not present or no so we are basically just searching for employee name here and then give your zero or one so that's basically usage of your if error which I've shown earlier and the same thing can be applied for your Edge lookup also now suppose you have been given some additional questions for example you know instead of using your V lookup or H lookup or lookup examples what we have seen say for example you are asked to find the salary of Peter Daniels now we we already know how to find that using we lookup right so for example we want to find out the salary for this employee name which is Peter Daniels I can use a vook lookup however sometimes we may also want to use a index match and index match is also one useful function to do that so we can basically search based on that so we have to define the range of data we have to then say what do we want to to match so that's basically an employee name and then we are interested in finding out say for example based on the range of employees you would want to find out the value of salary so we are then giving a range and doing it how do we do that so for example here I can use a function which is called index and if you see here it says returns a value or reference of the cell at the intersection of a particular row and column in a given range so this is where index function can be useful now we can start with this function it says it needs an array that's nothing but a range so we can basically select all our data so I can say A1 all the way to E21 so that basically is my range now I would want to find the salary of this employee name which is Peter Daniels so I can use internally here basically you need to give a row number so what we can do is we can do a match so we can use an internal function match now we need to basically give it correctly so we need to say match now how does this match function work so we need to basically match for value so for example we'll select whatever employee name is given here so that's Peter Daniels now that's basically what we would want to match now where do we want to match this so we want to match in the column B which has employe names so we can give a range which is B1 to B21 so that's basically within the employee names we are looking for a match which is given here that's in H6 and we want to do a match so we want to do a exact match so let's do a comma zero and then we are interested in finding out the salary based on this match and salary is in our fifth column so let's basically give the column number here and then just close this bracket so that basically completes your function and just just hit on enter so that shows me the salary of Peter Daniels right so this is an easy way wherein you can use the index function remember first you give the range then you need to give your matching condition So when you say match I would take the value whatever is given in this cell I will search in the range of values which is employee names I will find an exact match and what I'm interested in is is finding the salary for Peter Daniels now similarly say for example you have been given a question find the employee ID with salary 10,000 so you want to find out the employee ID wherever the salary is 10,000 so we are obviously looking for an exact match now how do we do that so we can start with our index function and within index function we need to give a range so for example I'm interested in finding out say for example our range which is A1 2 E21 so that's what I'm interested in or you are actually interested in searching based on say your salary so you're interested in searching the employee ID so mightbe if I just do a a21 let's see what happens here now then I can just do a match so match will be now done based on the value which is here h13 so I will do a match for this one now I need to give a range so let's basically give a range which will be we are interested in searching employee ID based on the salary column so that's your e column so I will say E1 to E21 that's what I'm interested in searching finding a match for this salary we want an exact match so first let's close this one now that's fine I mean we are interested in finding out the employee ID based on the match which is within this range and then what we would want to do is we would want to basically find the employee ID employee ID is in my column A so what I can do is I can additionally give a match here and then I can say I'm interested in the finding out the value for employee ID and that basically needs a range so the range here will be just the the column headings so we will give A1 all the way to E1 so that's the range in which we would want to search and we would want to find a match for employee ID we want an exact match so let's close this one and that that basically closes my match and we need to close our index function so now that shows me employee ID where the salary is 10,000 so this is how you can do your index matches where you can give a range then you can say what you need to match within which particular column whether it is an exact match or approximate match and then the column from where you are interested in finding out the value so same thing we did here we we basically gave a complete range and if you closely see we were only interested in finding out the employee ID so here I give a range A1 to a21 which is basically this particular column so that's where we are interested in finding out match basically will give your lookup value so that's h13 then we gave a range which is salary that's what we are interested in and then we said when we are doing a match we are only interested in finding out the employee ID which is in a range of column names and then doing an exact match so this is how you can use index match instead of lookup which can also give you uh some exceptions in searching for specific columns or specific rows now if error we have already discussed where you Nest your V lookup or H lookup inside it so this is how you can use your lookup functions h lookup we lookup lookup or index match to search for data some function like this which shows up xln dox lookup and then you're trying to search for something so don't be uh surprised with that so xln is basically a prefix added to functions that don't exist in the version of excel you are using you have Excel 2013 and say your friend has a newer version of excel say Office 365 or Excel 2016 and basically he uses your new cool function and sends the file to you so that's where you will have this xln prefix as I'm showing up here so for example what is the job title of Xavier power is what you would want to search and here the function which shows up Xcel and X look up and then you're searching so this is basically something which is the function now if I just select what it has selected here and if I hit on enter it shows that this does not work in my Excel here and that's because of the version but what I can do is I can fix this by removing this details here and that basically shows lookup we are searching for job title based on employee name which is in this field H6 and then we need to give a range so we need to give a range of names employee name and then basically the job title range which is basically the job title so now if I just hit on enter it gives me the value so it's as simple as that similarly here what is the employee ID of the person with this email we have already looked at the lookup function so if it shows your xln X lookup all you need to do is remove this and then keep the the lookup function searching for the value based on the range of values and then searching for a value within a range of values so just hit on enter and that gives you the value and same thing goes for this job title so what is the job title and salary of employee name so we have this function now what I can do is here I'm interested in finding out the value for this particular function how how do I fix this I can basically select this and then just do a lookup based on the values and it basically says you can't change part of an array so we can fix this now what we need to do is we need to basically search based on this particular value so we will basically say I want to do a lookup now lookup should be based on employee name whatever name is given here then you need to find the job title and salary so for job title I want to find based on employee name so I will give a range and then basically I will say I'm interested in finding out the job title so I will give a different range and then basically close this so that basically shows now this is basically because of the function which we have here so basically to fix this what we will have to do is we will have to delete all the columns here which were selected such as employee name job title and salary because that was giving you an error you cannot delete a part of an array now what we want to do is we want to find the job title and salary based on the employee name so we can do that using lookup as we have done earlier so we will use a lookup function now what we are interested in finding out is the job title based on employee name so we can select this cell which does not have any value then we need to basically say from which range or based on what you want to search so we want to search based on employee name and we want to find out the job title so we can basically give a range of values and then we will be interested in finding out the job title so let's select the job title range and that basically completes our lookup so let's close this and hit on enter now it says obviously error because this does not have any value we will fix that by giving in a value here same thing we can do for salary so for example I can just drag and drop it here and in this one instead of basically having a range which is your we will be interested in finding out the salary so let's see what is the range we give so the range should be employee name so we will basically select employee name as the range and then we are interested in finding out the salary so we will select this particular range and what we are interested in finding out is is basically employee name or we are interested in finding out based on employee name so let's select this particular column and then hit on enter so both of these are na values now just feed in an employee name so we will say for example Eduardo and that basically gives me the job title and salary so that's how you can fix your Excel FN error so same thing goes here so it shows you xln is what you would want to search might be if you don't find something you would want to push in name not found so first is we will have to fix this part so we will have to remove this because that's not supported in my Excel and then I have lookup I'm searching based on h24 which is basically employee ID you giving a range which is A2 to 821 because we would want to find name name not found for a particular employee ID so I'm giving a range which is A2 to a21 I'm trying to search for the name which is from B2 to B21 and then I'm giving name not found so say for example we can do this lookup but then with lookup you don't have this direct option what we can do is we can use if error if error and then basically try to search so that gives your lookup value and then you will have to close this one and then again if it gives you some error like this we can obviously go for vlookup and fix this so here we are searching for a particular range and then let's search for this value and basically to fix this what I did was I just use a v lookup so we know that employee ID 121 does not exist so I'm just using IF error I'm doing a v lookup where I'm searching for the value given in h19 and then I am saying that I would want to search from a range which is basically B1 to c21 that is in employee name and say email address for example and we would want to basically search for name not found so so we can instead of giving B21 or B1 to c21 we can restrict it to just B1 to B21 we are searching in column two we are searching for exact value and if it does not find then it says name not found and that's how we do it so we can always replace and fix this EXL FN errors if you are using uh some spreadsheet which was or Excel which was sent by um someone else who is using a different version so in this way we can use your D different lookup functions take care of your errors use a index match welcome to this tutorial on Microsoft Excel let's quickly see some more examples of doing data analysis using Excel and for that we can use some inbuilt addins which can be added to our Excel sheet so for example if you would want to do a descriptive analytics or descriptive analysis on your data say for example getting your descriptive statistics such as your mean median mode and so on so we can do that and we can use Excel for it so for example you can if you are given some data say I have temperature price of ice cream units sold and I would want to have descriptive statistics on this what I can do is I can click on file and here in file you can click on options and within options click on addin now within addin you have Excel addin which is selected here so click on say go for example and that shows what addins are available and you can choose which ones are you interested in so for example I have chosen analysis tool pack and solver added and click on okay now that basically should add more options to your Excel so if you click on data so here you see data analysis and solver and this is what we would want to use to get our descriptive statistics for these three columns so for example let's say temperature or you can even give the names later once you get your descriptive statistics so for example let's go for data analysis and here it says what are you interested in there is a two factor with replication you have correlation co-variance descriptive statistics you have histogram so let's click on descriptive statistics click on okay now this one basically asks your input range so while your cursor is blinking here also it is said grouped by so let's give it a range so for example I will say say temperat Fe now if I do this and I have selected The Heading just look at that and now you need an output range so let's just select this and then you can have your cursor blinking in here let's select say Fields here and this is where I would want the output now it also said what options do you want so it has output range we can then select summary statistics confidence level so I will say summary statis is what I'm interested in say okay and this says input input range contains non-numeric data now why is that because we chose temperature The Heading also so click on okay and here we will alter the range so this one is our range should be only the values numeric values on which we would want the descriptive statistics we have output range already selected we have summary statistics and now you can click on okay and that basically gives your descriptive statistics for temperature so here I could basically given a value for this so I can say temperature and that's my descriptive statistics for temperature might be I can just do some formatting and that's it so that gives me descriptive statistics for the value use here now similarly we can do it for price of ice cream so what we need is we need to basically go for data data analysis descriptive statistics say okay now you need to give a range so here I will change my range to these values output range is already selected now we are interested in summary statistics six click on okay and this says output range will overwrite existing data press okay to overwrite data in range I will say cancel no that's not what we want to do we need to give a new Range so let's select our new Range which is here and now click on okay so now we get the values which is for your price of ice cream so again we can basically select this and say price of ice cream and we got our descriptive statistics for price of ice cream and like we did earlier I can select this I can basically do a merge and center and that gives me descriptive statistics for price of ice cream so we could also basically change this now I can go into data and I can go into Data analysis descriptive statistics so we know that we had selected this B2 to B8 and this one which is H6 to h19 we would want to shift it might be two columns up so mightbe I can just say H5 and I can manually change it to H 17 and let's say okay and we will basically get this and I can get rid of this so I can have it in the same range so similarly so this one will have to be renamed and I can basically say price of ice cream and that's basically my descriptive statistics for my price of ice cream and similarly we can do it for the third column which is units sold so we would want to have this now let's see we can click on data we can click on data analysis descriptive statistics so we need to give the range correctly so this time our range changes to unit sold now we can also say labels in first row okay if we were selecting The Heading so let's do it in this way so in my range in my range let me empty this I can basically select this which we know has non-numeric data in the first row row say for example I'll say labels in first row I'm interested in summary statistics and this range will now have to be changed from H to basically something like J so let's say J and let's select these values so that should take care of things and now you see you have your unit sold you did not have to manually rename it and you have basically got the descriptive statistics so this is how you can simply perform analysis using data analysis here you can basically get your descriptive statistics for your columns and then you can do whatever needed formatting you need to basically make your data look in a good way now let's look at one more example of data analysis where we may want to look at the frequency of values or frequency of values occurring in a range of values so for example if you have been given temperatures you have been given some pins where you would want to identify how many values fall into the range of 0 to 20 20 to 30 30 to 40 40 to 50 and the easiest way to do that would be creating histogram now histogram is usually used for data analysis where you would want to look at different variables or say features for example temperature is one such feature might be there might be one more variable or feature such as sale of ice cream and you would want to see if uh the increase or decrease in temperature affects the increase or decrease in sale of ice cream might be sale of ice cream is a is a response based on temperature so it depends so sometimes you may want to find a relationship between two variables whether they are positively or negatively related or you would want to do different kind of analysis and in certain cases we may want to first to analysis on one single variable look at the frequency of values might we also look at the defects and for which we can use something like par chart so we can go for histogram and that basically gives us the frequency of values now how do we do that so we have already added the addin which is data analysis earlier so we can just use the same thing again here we would want to create a histogram so let's say okay now I have already selected input range so if you see here my input range is temperature which is also with the headings and I have bin range which is basically the range of values so for example I can select this and that's my pin range I am selecting in or the option labels because I'm using the first row which has the heading such as temperature and bins now we need to give a output range so for example let's say I would want my data here and that becomes my output range so you can have a sorted histogram or basically a parto chart so if that's what you interested in looking at the frequencies for your different ranges and here I'm also selecting chart output because I would want to have a visual histogram which gives us the frequency and it's as simple as this just click on okay and now you get your bins so it basically tells you frequency of values which is basically 20 but that does not mean it is only talking about the values 20 it is basically talking as a range of 0 to 20 so we have 0 to 20 that is 2 so we can basically say that there is one 20 here with the 20 being the maximum value and then there is one more 20 so that's your 0 to 20 then you have 20 to 30 which shows three values so might be in that case I can say 26 is one thing then I can say 30 that's the second one and then basically I can look at 22 so basically this one does not select 20 as the lower range but it basically selects 30 30 as the higher range so I do see 20 to 30 there are three entries similarly we can see values for 40 and 50 and since we have selected parto or sorted histogram that shows in a descending order what is the highest frequency of values within a particular range so that shows me highest frequency is five and then you have three and three and then two so this is how we can create create a histogram and we can perform analysis on a single variable now as discussed earlier as I said sometimes we may be interested in finding out the correlation between different variables such as say here we have temperature price of ice cream and units sold and we may want to find out the correlation between one variable to another variable or we would want to find out the relationship between variables are they linearly related are they positively related negatively related and so on and for that we can use the correlation of your data analysis addin so for example you want to find out correlation of temperature and units sold and what we can do is we can find out that using a formula so for example if I search for whether you want we can also choose the other options which we would want to see as a part of this regression analysis so basically we have selected input y range which is your dependent variable you have selected X range which is your independent variable and if we are building a multi- regression model then we have chosen temperature and price of ice cream both now we can choose the labels which we have done we can choose your output range which is basically on this sheet and then we can select the residuals so that basically gives you your residuals now we can also get your line fit plots which you were seeing earlier you can also go for your normal probability plots and then basically just say okay and let's say okay and that basically gives you your regression analysis output so we basically have your summary output we also have a Nova we have a residual output that's from the previous one and uh basically we can get rid of this that's not required I can get rid of this one yeah so now we have our regression statistics we also have our plots so we can basically look into these so we have our normal let's get rid of these let's get rid of this let's get rid of this and then what we can do is since we had some existing data it has overwritten so let me get it of this and I'll repeat this step again and uh let's say no water yeah so now what we want to do is we want to do rection analysis so we go get into Data we get into Data analysis we choose regression we have already chosen our y range which is column c x range is multiple variables we have gone for labels output range which we would want to select a particular cell so let me select this I'm going for residuals line fit plots or normal probability plots and say okay so now we have our plots we can just separate them to have a better understanding yeah and that basically shows you the variables so here it shows me units sold and then what are the predicted units sold and here the variable is temperature here the variable is price of ice cream right so that's your one variable and second variable and then you also have your normal probability plot now to interpret this or to understand what we have here where we look at your summary output so it is good to understand what Excel is helping us out so basically when we do a regression in Excel it is performing all the calculations automatically the interpretation is something which we need to understand so basically we are looking at multiple r value here and we can let's also fine tune this one yeah we have our values here and if you see we have our R squ now we can then basically look at our multiple R so what is your multiple R that's your correlation coefficient which basically measures the strength of linear relationship between two variables so this can be any value between your when you talk about your correlation coefficient it can be any value between minus1 and one and its absolute value indicates the relationship strength so the larger the absolute value the stronger the relationship so one basically means a strong positive relationship minus one means a strong negative relationship and zero means no relationship at all now when you're looking at r squ that is the coefficient of determination which is used as an indicator of the goodness of the fit so it basically shows how many points fall on the regression line so your R square value is basically calculated from the total sum of squares uh when we say precisely it is the sum of squared deviations of the original data from the mean now when we talk about looking at the value of your R square so it is basically we can be uh seeing uh that this is uh 45788 that's the value which I'm talking about so it basically means 45% of our values fit the regression analysis model so in other words 45% of dependent variables that is your y values are explained by the independent variables that is your X values so nor if you have r s value higher then that is considered as a good fit and here we have considered multiple variables now when you talk about your adjusted R square it is the R square adjusted for the number of independent variables in the model so uh you would want to use this value instead of R square for multiple regression analysis such as in our case now you also have standard error so that's basically goodness of fit measure that basically shows the Precision of your regression analysis so the smaller the value the more certain you can be about your regression equation so when when we talk about your R square that represents the percentage of dependent variables variance that is explained by the model and standard error is basically an absolute measure that shows the average distance that the data points fall from the regression line so you will have to look back into regression statistics terminologies to basically understand more of this and if you're already from data science background that then you already know what I'm talking about we are just seeing how Excel easily helps us in doing these things now we can also look at observations here which is basically the number of observations in our model which is seven now when we talk about regression analysis and if we look at analysis of variance that is your Anova values here so we basically see DF you have SS Ms F and significance F now to understand these so basically what we are doing is or what Excel is doing is it splits the sum of squares into individual components that give information about the level of variability within your regression model so when you talk about your DF that's basically the number of degrees of freedom associated with the source of variance you have sum of squares that is your SS so smaller the residual SS compared with the total SS the better your model fits the data right now when you talk about Ms that's your mean Square you have f is basically the F statistic or you can say uh F test for the null hypothesis basically it is used to test the overall significance of the model and then you finally have your significance F which is the P value of f so uh when we when we usually talk about simple linear regression the Anova part is uh not uh really used or it's rarely used right but when we look at the significance F value that gives an idea of how reliably or statistically significant our results are so if significance f is less than 0.05 that's 5% our model is okay if it is greater than 05 we will have to better choose another independent variables right now we can also look at the output put of coefficients and that's what we would want to look at so you basically have your intercept here so when you talk about your intercept temperature price of ice cream so we know we are looking at our variables and then we are also looking looking at our intercept which basically gives you more details or your coefficients so the the main important thing when we talk about regression analysis is basically your coefficients it basically enables you to build a linear regression equation uh in the form of y equals BX + a which I explained earlier and then basically we can understand our regression analysis output better now when we look at the residual output which we are seeing here so that gives you your observations how many observations we have it shows what is the predicted unit sold and then it also gives you your residuals so we can compare the estimated and actual number of sold umbrellas based on the observation so we have 11.29 here and if you compare that's basically 12 the second one is 15 and here it is 13.5 you have and similarly you can compare all the values of actual units sold and what our regression has predicted so basically what we can do is we can compare these and we will see if there is any difference or no right so obviously there will be or there would be some difference because independent variables are never perfect predictors of your dependent variables and the residuals basically help us in understanding how far the actual values are from the predicted values so that's what we see here in the residual section so for example when we look at this one which is 11.29 and the actual is 12 we see what is the residual and how far is the predicted value from the actual value so similarly we can also look at the percentile values so that gives you your probability output and this is what we have in our graphs so we have basically our variables we are looking at our variables and also the units sold and the relationship between them we can basically uh add in more details to this particular plot and you also have normal probability plot which is basically getting your values from your probability output here so this is how you can perform a simple regression analysis now what you can do is you can as I said you can basically be looking at your scatter plot you can be looking at your y and x and your R square values right but remember what are the variables which you are selecting and then you can compare these with basically the values what you see here so remember here I've have chosen my both set of variables and you can choose one variable and then basically see what is its impact on the response or on the target so it's pretty simple here you can add more details to your scatter plot or the plots what we have created or Some people prefer to do it using formulas so basically we have some statistical functions that can help you do linear regression analysis such as you have linest you have slope you have intercept you have correlation and so on so basically you can use these particular functions and what you will have to do is you will have to basically going for a function if that's what you would want to do and then you can search for functions and say for example I'll just say linest and say go and here I have my function so if I would want to really use this one I can do that what I will have to do is then select the variable so if you just say okay and that basically says your known y's known X your constants your stats and basically you can use the formula also but we are not covering that here might be in the later videos we can do that so just try out and perform some regression analysis and try to look at your different variables and try to interpret that so that basically gives you an easier way of Performing regression analysis using your data analysis addin in Excel and that brings us to the end of this video in data analytics with Excel I hope it was useful and informative if you have any questions then please feel free to put them in the comment section thank you for watching and keep learning hi there if you like this video subscribe to the simply learn YouTube channel and click here to watch similar videos to nerd up and get certified click here e e e e e e e e e
