Transcript for:
Mastering Excel from Basics to AI

welcome to this ultimate Excel tutorial which is literally the only video you will ever need to master any and everything for learning Excel from scratch we will kick things off with the basics introducing you to the Excel worksheet first before moving on to covering fundamental formulas and functions then we will move on to more Advanced Techniques like we lookup power query and data analytics you will learn how to format visualize and even create Dynamic dashboards and the best part is at the end where we have curated fantastic applications where you will see excel in action from budgeting and managing your finances like a pro to generating Financial reports using Advanced Excel techniques to top it all we also thought why not show you how Ai and chat GPT could supercharge the way you work with Excel and boost your productivity watch this tutorial till the end because that's where we have revealed all the pro tips from Basics to Advanced so grab your spreadsheet and let's get started [Music] hello Learners welcome to the course on Excel so we have heard this Excel so many times correct if you are working in organization or for your personal life or for any other purpose okay like for financial budgeting or if you want to prepare enough financial reports or if you are going to create any workbook or if you are going to report you need to analyze for that visually we need to show that whatever the financial reports or whatever the budgeting information or whatever the data so we are going to use this Excel correct so it is a worldwide accepted software okay so we'll start with what are the basics information of excel so how we can use the Excel or how we can start or save or whatever the basic things we can do in Excel okay so let's start so you can just go to start and you can type Excel this is the one way to open the Excel software okay okay so here you can see so many templates are there correct so either it is a blank document or take a tour drop- down list formulas power query P table pie charts whatever the various uses we are going to do in the Excel like some they are going to add the template for their monthly budgets also okay so if you want to create a templates also you can create and you can save it here okay so first I will start with this blank work prooof look so this is the Excel interface look like okay so this we call it as columns this we call it as rows correct so this we call it as ribbon okay all these whatever the commands are there like file home insert draw page layout all these are commands Okay under that we have commands groups also you can see this gray line correct so so this paste Cut Copy format this is a command group and this is one more group like what is the font bold italic underline font size all those things were there correct like this these are the groups of commands this we call it as ribbon and this we call it as commands and commands groups okay so first time if you open here like file here you can open new file if you want to start newly okay or if you want to create fresh any data or if you want to record the datas then you can go for this new open first time if you are saving or first time if you are going to fill any information and then you are going to save then this will work save okay if you click on that it will go to save as because on which tab or in which like desktop C drive or which drive you need to save that information it will ask once it is saved that will be there in that particular place okay print share export publish all those things were there okay so this is the first one file after that if you go to home button like for all these things we have shortcut keys also that we are going to learn in the coming video okay so paste Cut Copy format painter font caliber that is the font and we have the font size bold italic underline all those things were there okay so so these are the command groups over there if you want to do conditional formatting that also we are going to discuss in the coming videos okay next we have formulas and tables exclusively we are going to record this format that is table and borders also so how to create a formatting the whatever the information or datas are there and how to do the tabling like for sort filtering conditional formatting all those things we are going to cover in this particular video in the coming slides okay next if you go to insert so here also like pivot table we have covered this video also in this next the tables okay for this we have shortcut keys and all whatever the pictures you need to insert here that you can do it here outline shapes icon all those things are command groups were there here you can see the charts and graph P diagram were there candles line chart all those things were there correct maps all those things are the command groups in here okay next draw page labout like if you want the margins or whatever the size okay border you need to place left side or right side or the header and footer all those things we can do it here okay next if it comes to formulas okay so here also this is the command groups we can easily type the formulas also here or else we can use this whatever the Autos some recently used or whatever the financial logical text all those things okay we can easily apply the formula and you can check also so here Auto sum option is there for example in this F column we are going to enter few numbers here okay then you can apply a formula is equal to sum is equal to sum and whatever the tabs information you have given or whatever the number you have filled select that close the bracket click enter then you will get the total of all those information or else just if you go here select that whatever the numbers are there click on here Auto sum directly it will come the information that is also one more way to create the formulas or applying the formulas for the numbers okay next if you go to data whatever the data we are going to type here or the information are there in the Excel worksheet okay from that information we can do this filtering sorting this is also we are covered in this particular video in the coming slides okay next review view looka function this is very very important for visualization of the information or the data or the reports okay so this is the main tool we are going to use for the visualization this is also covered okay you help and if you want to sign in then you can protect your workbook or you can share all those things we can do it in this Excel now I want to expand a little bit more on uh referencing and tell you guys a little bit more and I will go into it over here in discount pricing so let's actually take this and there's one more cool thing I want to show you guys is that you if you select something you can always move it right and none of the things will change so that is another awesome thing about Excel you can copy this you can come to class marks and paste it here right this is what we wanted all right cool so this was I was talking about class marks now there is one more thing I want to mention let's take this and let's do a highlighting let's say how bad were their marks so we can do conditional formatting in which we color their marks right so this tells us which was the lowest Mark they scored we can do the same with the rest also we can just copy this and take the format painter and paint the rest of the cells and you guys can see that they also scored uh you can also see the score marks of there also so 99 was the highest for satam 54 was the lowest and 65 was the average and 89 was the highest for shuti 78 was the second highest and 55 was the lowest so you can see that shuti did overall the best but that is all right Max really don't matter it's about your skills and your passion about certain things that's me putting my own Moto on you guys all right so we can also do a lot of different formulas so there is uh min max formula so who got the highest marks in maths so we can do Max over here and I can type Min over here and I can do an equal to Max and it will take these values close them 89 was the highest we can stretch this to know who got the highest in English highest English was 99 highest in physics was 55 we can do the opposite over here we can put Min Open brackets select these close the brackets and we can just stretch this out 65 45 and 46 was the lowest so this is how you can go ahead and work with that and uh let me show you one more thing if I do 1 2 3 4 and I just keep on stretching these right so it goes to 15 and here also conditional formatting would look a little better you can see how it goes from one is the lowest and 15 is the highest now if I want I can change this to one to be uh to have one to be the highest the opposite right so you have different kinds of conditional formatting so this is also very helpful because visual aids are very important to humans because we are dumb when we see just a number of just numbers right so we want to visualize and there is another thing that we can do to visualize if I take this and I go to insert and I click on chart so let's say we want a pie chart and we select the pie chart and here we can see the total amount of marks scored right so we can see satam Cod this much but this is generally used when you have like a singular uh total so in maths maybe the total is 300 and out of 300 actually this does not work very well but I'm just showing you you can even make uh charts also over here and there is another interesting thing that if you select some group you will always have a quick analysis option over here or you can just press control Q so all the things that I showed you over here if you want to add like formatting data bars if you want to do color if you want to do icon set if you want to add greater than less than uh those kind of things or if you want to go and add more charts if you want to do a line chart to get to see the marks of these students how how do they fluctuate based on maybe time so instead of maths English physics we only have maths and we have multiple tests of maths so in first maths test uh satam scored this much in second he's third and third third and fourth he scored this much so we can see their you know growth and this thing so we can see any Trend so this is again very useful to understand how a particular student is scoring or not right okay with a solid grasp of excel Basics you are now ready to unlock the true potential next we will explore formulas and functions essential tools for performing complex calculation analyzing data and automating task mastering these will enable you to handle data more efficiently and effectively making your workflow smoother and more productive if you found this video helpful subscribe for more videos all right guys so let's get started with the agendas for today's video so at first we'll be understanding what exactly is a formula in Excel and then we'll be understanding how Microsoft Excel figures out that an user is going to enter a formula moving on to the technical part of the formula we'll be understanding the precedent order of operators that will be used for entering the formulas after which we'll be directly jumping into the live Excel workbook to understand the concept of entering copying editing and hiding a formula moving on we'll be understanding the next major part of the video where we'll be discussing the functions in Excel and how functions and formulas are correlated to each other and then we also will be discussing the three major parts of a function also where we'll be discussing how to view s different functions that is provided in Microsoft Excel and then coming to the concluding part of the video that is the most exciting part of the video too where we will be discussing the top 10 Excel functions that every Excel user need to be aware of to make their life easier so guys I think you understood the structure of agenda for today and I hope you guys are really excited to move forward so let's let's get started so now let's discuss what is a formula in Excel so as you can see on the screen a formula in an Excel is an expression that operates on values in the range of cells or a cell and on a mathematical point of view it is a calculation for a specified purpose so you might be wondering why am I explaining a formula to you because we all know that we have been exposed to a ton loads of formulas right from our school days but to understand a formula in Microsoft Excel you also need to understand what is a range so what exactly is a range a range is simply a rectangular box of selected cells so if I take the example of the snapshot that is provided you can see there a red highlighted box and that is exactly a rectangular box of selected cells so now how to name or identify a range a range is identified by the name of the cell in the upper left corner of the range and the name of the cell on on the lower right corner of the range for so in this example the a column intersects with the first row and that's how it got the name A1 and the B column intersects with the row that is B3 so to identify or name a range that is A1 colon B3 that's how you name or identify a range so how do you select a range so to select a range you just need to drag across the range or the cells you want to be in your range so now I think you guys have understood what exactly a range is and you might have understood what is the relation between a formula and a range now let's move on to the next topic that is how to enter a formula before entering to the topic of how to enter a formula we'll first discuss how Microsoft Excel understands that the user wants to enter a formula so in the first example the user inputs 22 + 44 + 55 and Microsoft Excel understands this input as a text only because there is a lack of equal to sign so the equal to sign is the factor which tells msxl that the user wants to enter a formula the second example you can see that the user as inputed equal to sign and then moved ahead to entering the formula that is 22 2 + 44 + 55 and as soon as this is done you can see Microsoft Excel has identified that the user is wanting to enter a formula that is sub this is the correct way and the on the left side this is the incorrect way of entering the formula so now I think you have understood how Microsoft Excel understands the user is wanting to enter a formula or not moving on now we'll understand the two different ways to enter a formula so the first way of entering the formula is by using the actual values and the second way is by using cell references so in the below example you can see that we have the data sets of marks obtained by students in particular subjects and we are trying to calculate the score obtained by Ram in the first example we are using the actual values obtained by Rams to calculate a score and in the second example we are using the cell references to calculate his total score to understand this concept even better we will move on to the live workbook so here let's start off by calculating this score using the actual values so let's hit equal to and enter the scores obtained by Ram 89 and then let's hit the enter button as you can see the total score of ram was calculated now let's you use the cell reference method to calculate the total score obtained by Ram so to do that we'll hit equal to button click on the cells that are required hit the plus button and then click on the enter button so there you go we have calculated the total scores by both the methods so now let's talk about a situation where in a faculty made an error while entering the marks that is 76 instead of 90 so now we'll go ahead and correct this mistake and as soon as we did this we can see that the total score that was obtained by Ram which was calculated using seller refences got updated but the score that was calculated by using the actual values didn't get updated so this is one major advantage of using cell references in formulas because if any updation in the cells which are under reference in a formula gets updated the final descided output also gets updated all right guys so now I believe that you might have understood the two different ways to enter a formula and also the advantageous way to do the same now moving on to the next topic that is precedence order of operators the mathematical operations in Microsoft Excel works on the principle of board mass or ped Mass board Mass Accords to the bracket order of division multiplication addition and subtraction and ped Mass Accords to parenthesis of exponential division multiplication addition and subtraction Microsoft Excel imposes a hierarchy on the mathematical operations and it goes like first comes exponential and Then followed by the division multiplication addition and subtraction but addition and subtractions are both equally ranked and whichever Microsoft Excel encounters first gets executed first so that's how all the priority of operators takes place in Microsoft Excel and this plays a major role while we use mathematical operators in a formula now let's come to the final topic that we going to discuss in this video in vup function the final topic is nothing but to bring in the largest or the smallest value of our concern to the particular cell so let's see how we'll be able to implement that I'm going to look at the price ranges and get the maximum price and the minimum price so that will be the example which I'm going to take to implement the smallest value and the largest value through V lookup function so that in order to have a minimum value let me write minimum here and then here I should be getting the minimum value of the prices okay so here let me apply the V lookup function equals V lookup and then since we going to find the smallest value let me let me put Min minimum Open Bracket then we'll be selecting the range the range will be this much so this is the range I selected the Range close bracket comma and table array so the table array would also be the same so let me select the table array I have selected that as well comma and column index number since the range of selection and the table array are the same so the column index number would be one comma tab close the bracket and hit enter see we have got the minimum value in the price range so let's check that 10 3 3 120 250 10 40 70 230 80 55 210 540 225 so in all these ranges the minimum value would be three and we have got the right minimum value so let me find out how to find the maximum value so in order to find the maximum value I'll be finding that here for the price range as well maximum value for your reference so I'm applying V lookup function here which is nothing but equals V lookup a maximum value since we are finding the maximum value I'm writing Max Open Bracket and I'm selecting the range in which range from which I need to find the maximum value close bracket comma table array since here the table array and the range would be the same so I'm selecting the same thing again and the column index number since the table array and the range is the same so for us the column index number here would be one comma and here we need the exact match right so I'll be putting zero and then closing bracket hit enter and now have got the maximum value which is 250 let me check here 225 210 230 250 there's no value is greater than 250 so we have found out the method to implement V lookup function for maximum and minimum values as well coming to the first function that is count so let us enter the count function that is equal to count Tab and let's select the range and hit the enter so nine so just to cross check whether the count function is properly checking just we'll just count one 2 3 4 5 6 7 8 9 yeah so the con function is working perfectly fine as proclaimed and then we'll now move on to the sum function so to calculate the sum of the marks that will be equal to sum hit the tab button select the range where the sum needs to be calculated hit enter and there you go the sum is 698 so now we want to understand what was the maximum score that was achieved by a student in the subject object so to understand that we'll go to the max function Max tab hit the range for the maximum value has to be found out click on enter and there you go that is 87 so 87 is the highest score of a student that is being scored in this test so moving to the minimum function so the minimum function tells us that which was the minimum on the the least score that was coded by a student in the test and we'll be selecting the range and clicking on the enter and there you go so that was 64 so 6 64 was the least score achieved by a student in that test so moving on to the next set of functions that are average and F so basically what average function does is it Returns the average of its arguments so basically what are these arguments these argum are none other than the cells or the range of cells now coming to the if functions it is actually a logical functions so if you are a computer science students you might have used more a lot of if Loops or if else Loops if El statements it basically works the same in an Excel 2 it is a logical function which displays a user defined output according to the condition so to understand this better let's move to the live workbook so average function let's find the average score of the student in this tatch so to do that first we'll enter equals to average then we will enter the range head hit the enter button so here we can see that the average of the class is 77.5 now to work upon the IF function I would like to say that if a class average is below 75 then the class can do better so and if the class average is higher than 75 the class performance is good so if I have to put an if condition or an IF function I'll put it as if tab so the condition is the first they're telling for The Logical test so here we'll be tell telling that if average is less than 75 then we'll put a comma and uh they asking if the value is true like if the average is less than 75 what should be the output that you want the Excel cell to give so if average is less than 75 I want the output to be can do better C DB so that is the output I want and then I'll hit a comma and then now they are asking for the value if false so here uh they're telling that if uh the average is above 75 so the condition is getting false so I just want to put the output to be good performance now we'll see what is going to happen and just click and enter button so here you go so that's the output that is been received good performance so I would just want to know whether uh I would certainly for a change I'll put the score and alter the average so I'll put the alter the average Mark to the 60 and now you see you can see that the average has reduced to 74.6 6 and that is actually less than 75 and the conditions becomes true and the if output comes as cdb that is can do better so that was the output that I had given that is the first condition if it is true then the output is cdb and before the condition was false that is the average was above 75 so it was good performance was the output so so now moving on to the next function that will be concatenate function so basically concatenate function attaches or concatenates the values of the selected cells so to understand this better we will directly jump into the live workbook so here you can see the first name of the students and the last name of the students so if I want the first name and the last name to be attached and to get a full name of the students I will be using the concatenate function so let's try it out so concatenate tab so they asking for me the first text that is Raj then I'll hit a comma then Kumar and then hit an enter so here you can see that has printed out Rajkumar but I feel that the the text that has come up is very congested and I believe there should be a space between Raj and the last name Kumar so what I'll do I'll just go and edit the function and I'll put a small space using two double codes and a space between them and hit comma and then hit enter so now you can see that there is a small space between them so now there is a question does concatenate function only work on text so the answer is no it it can also work on numbers too so if you want to concatenate a number to the uh like if you want to add add this code besides the rajkumar's name we can do that so to that to do that we'll just edit the function we'll go there hit a comma then we'll tell them to add 78 as the text and then hit enter so see there you go so Rajkumar both the first name and the last name and we also have the score that has been scored by him so I'll just drop down this to copy everything the same way and there you go we have the full name of all the students who attended the test and we have also got their marks on besides them so now moving on to the next set of functions so so we have ctive functions sum if functions and drop- down list function so what exactly does a ctive function do so ctive function counts the number of cells according to the criteria provided by the user and the Sumit functions adds the value values of the cells within a range according to the criteria the drop- down list is also a kind of conditional functions all these three functions are too much into conditional so I think it'll be really easy to understand if you do it in a practical live workbook so let's jump into the Practical live workbook to understand it better so first let us work on with the count fun count if function so count if and then tab so here here we can see they're asking me for a range so I am selecting the range as the section and I'll hit the comma and they're asking me for the criteria and I'll give that batch a as a criteria so what exactly am I trying to get an output for here so here I'm actually trying to find out how many students from section A as attended this exam so that is what I'm trying to find out so when I hit the enter key let's see how many it's showing three let's just cross check so here it's 1 a can see two and we can see three so that's exactly the number of students that had attended the exam so I'll just drag down and see whether the uh other section students how many of them have attended the exam so it's showing that b b section there are four students and the c section there are two students so let's just cross check for confirmation B1 2 3 and four and C it comes to be two yeah so this is exactly what we had the counter functions have produced the output for now let's move to sum IF function so let's put sumf hit the tab yeah so they're asking me for a range so I'll put the section as a range then move to comma and hit the criteria so criteria will be this a a uh batch and then I'll hit they're asking me for the sum range so sum range will be the score achieved by the students so yeah so what exactly am I trying to get output here in this case so in this case I am trying to understand that the score that was obtained by this students studying in only section A so the what is happening here the summit function is actually selecting the students only from section A and then summing their uh scores that they have obtained so when I hit the enter button I'll get the students score uh the score of the students summed up who are studying in section A so there you go that is 239 so you can see that section is 79 + 76 plus 85 that will sum up to 239 and I'll just drag down and see to get to get the results of other two there you go B section students four of them are doing and then you can see the results are like if you sum up the scores you get 288 and C you can see there are two students and if you sum up their scores you get 145 so that's up all for ctive function and Summit function so here we come to the last function that has to be discussed in this video that is drop- down list so how does a drop- down list work so now uh I'll create a drop down list function so first I'll select uh cell then I'll go to data and then I'll go to data validation and here I'll go and select list and they asking the for the source so I'll click here and tell the source as the names of the students and I'll hit okay and there you go now we can get to select any student name here so basically how a drop down function works is conditionally with respect to any other function so if I had to like say if I use the Su function here and I say that the range is the name and I put comma and I say the criteria is the uh name that pops up in the drop- down list then I tell some range so some range I'll say that I'll want to show the output of the student that I want that is that I'm going to select in the drop down list so I'll hit enter and there you go so there's Rams score in this particular test so if I want to get to know the score of suchin I just click here and there you go there I get that is such score that is 76 and I just don't need to search here in this table so here drop- down list if I want to check for lnas that is 64 so I don't have to search every time for the score of a particular student if I can get it very easily with the help of drop down list now let's understand in V lookup function how we'll be able to bring in multiple values at once for example what if you need to apply vup function at once for product name and price for all the cells here so in one go you need to get the product name and the price so you need not go for product name at once and then Implement that for price again so here by looking at we look up at multiple values we'll be able to bring in both of them in one go so we'll see how we'll be able to implement that so let me Implement that here so we look up function so the vup value will be this comma so let me fix the column here so that you know the column will not be changing for so for that I'll be implementing this and I have to sel the table aray right so I'm selecting the table array here yes that is done and I'm also locking that pressing F4 comma and I need the column index number here so for product name the column index number here would be two and for Price the column index number would be three so since we are having a dynamic column number what we should be doing is we should be going for columns columns Open Bracket and we should be selecting the initial two cells that is this one is to I4 right so make sure that you're fixing the product ID cell here so that for that we need to fix it here by pressing F4 you fix that as well close the bracket comma tab close the bracket hit enter now we have got the value here which is bucket for product ID the product name is bucket here right so let's copy that to here see we are getting the value 250 here we have only implemented One V lookup function where we'll be getting the product name and price at the same time and let's go down and see if we'll be able to copy the till the end see we have copied we look up function for the product name and the price in one go we can also copy by simply double clicking on the right right side bottom corner see so we have copied the product name with respect to the product ID and the price with respect to the product ID from this table array which we have selected so we have learned how to implement multiple values at once using we lookup function in Microsoft Excel now moving on to the next topic that is how to enter copy edit or hide a formula and to discuss that we'll be directly jumping into the live workbook in my earlier example if you you remember I had actually shown you how to enter a formula now you'll understand how editing a formula works in the same very example if you recall I had actually changed the mark of ram from 76 to 90 and as soon as I did that the total score of ram got updated in the cell which used the cell references to calculate his total but the cell which used the actual values obtained by him didn't get updated so in order to update the total score in this cell we have to edit the formula there are three different ways to edit a formula the very first way of editing the formula is by clicking on the cell and moving your cursor to the formula bar and making the required changes and when I hit hit the enter button you can see that the form logouts updated and Ram's total score is also changed to 444 now the second way of editing the formula is by double clicking on the cell and when you you do that the formula which is beneath the cell comes in front of it and the required changes can be made and now the third and the most simple way of doing an edit to a formula is by using a shortcut key so first we'll move to the Excel that requires the edit we hit the F2 button and as you do that you can see the formula which was beneath the cell comes in front of it and we can make the necessary changes and click on the enter button so that's the three ways to edit the formula now I would like to ask you a question what is the first thing that strikes your mind when you hear copy and paste I bet it isrl C and contrl V yes these Keys also work in Microsoft Excel let's go ahead and try it out so when you click on the cell which you want it to be copied hit the keys contrl C and move it to the cell that requires the pasting and hit the key is control V now as you can see the copy is made and the important part over here is it's not only the content that is getting copied it is also the formula that is coming along with it the another simple way of copying a formula is by right clicking the mouse button going to the copy option then moving to the cell where you want it to be getting copied go there and use the paste option now let's discuss the third and the most widely used way of copying a formula that is drag and drop now if you can see on the screen when I hover my mouse in the corner of the cell that I that requires to be copied you can see a black plus that is getting formed now click on the cell drag it till the cell where you want the formula to be copied and drop it off there you go you can see both the content and also the formula is coming along with it this is the way where you can use drag and drop to copy a formula so till now we were copying a formula from a cell that was non- dnamic in nature now let's move ahead and copy a formula that is dynamic in nature as we do that we can see it's not the value of 444 that is getting copied below but it is some other values that we don't know where it is coming from to understand that we'll go and double click on the cell so as you can see here it is the scores obtained by R in particular subjects that is getting added up and her total score is published in that row so what actually is happening here is since we copied a cell that was dynamic in nature it is still using the same formula that we used to calculate the total score of ram but it is using the cell references of the scores obtained by Ria and adding them up to calculate the total score obtained by her in the same way Mohan and MMA scores are also added up and it is published in this cells so this is the another major advantage of using cell references in formula as you can see in the example as we drag down the formula it automatically calculated the scores of the other students and published it in the respective cells and this actually makes a life of a Excel user a lot more easier okay guys now we'll understand how to hi a formula in Microsoft Excel to do that I'll first enter some random formula into a cell there you go and now when we click on the cell you can actually see the formula in the formula bar so in order to hide the Formula First we'll have to format the cell and we'll go to the production Tab and we'll check these boxes and hit on the okay button now we'll go to the review tab go to protect sheet and enter password of our own click okay then again confirm the password and there you go now when you click on the cell you can actually see the formula so now if you want to hide a formula from any other third party user you could actually use this function and go to produ sheet to hide any complicated or secretive formula that you have found out so in order to get back your formula you have to go to the unprotect sheet and hit on the formula that you had entered earlier here and when you do that whatever formula was beneath this cell you can retrieve it back all right guys that's awesome now I believe that you guys have a clear idea of how to enter copy edit and also how to hide a formula here we are going to discuss about tables and borders in Excel so how to draw a table or how to do the borders for the whatever the datas or whatever the informations are there so in that what we can do is sorting and filtering for that it is very useful okay so first we'll see how this table and borders will work okay so tables in Excel the Excel table command to convert a list of data into named Excel okay so whatever the raw datas are available okay that we are going to put in a single worksheet or in a spreadsheet and then we are going to put a name like whatever the just example I will give you I'm going to take a details of students okay 10th standard students and how much they have scored in the physics and in the mathematics these two subjects okay that information so for that we can give whatever the 10 student whatever the batch or section that we can mention as a file name correct next these tables have useful features like sorting and filtering so this sorting and filtering also especially in this particular video series we have have this filtering and sorting options also or functions of of sorting and filtering also explained in this video okay to help organize and U data so we need to organize the data if it is a raw data so easily we can't search anything correct so on that time we need to organize this data whatever for our feasibility or for our readability we need to organize the data and we can see so for this doing tables or we if you want to create a table and border so to convert any data into Excel table the step one hit insert ribbon and then hit table you can have these commands group okay in that command group you have table you need to hit that Ribbon or in that command groups next step two check or selected cells of the table and hit okay so which one you want to do or for which data you need to create a table just select that and you can click enter then you will get the what is this whatever the table format or borders for sorting and filtering function also we can enable in this table format so shortcut is control T so whichever the details are there just go and click in any one cell and just press control T that will select the complete data and click enter then you will get the table format or else we need to go for the step two that is check or we need to select whatever the data is required and then we can apply the tables and borders the these are the two methods we can do so that I will show you in the Excel also so this is the data I've have taken so this just will give the headings for this serial number faculty name student name gender next the date on that day they have submitted assignments or whatever okay after date we have numbers right so that we can give marks of physics marks of maths okay so next if you want to create a table we have two steps correct either step one or we need to go for step two so in the step one we can go and select complete this whatever the details are there then you can can create a table correct or else we have a formula that we can apply or whatever the shortcuts are there just using that shortcuts and we can select this data okay just go to insert okay this is the table correct so the shortcut is control T see my table has headers so for this headers if you already have the headers it's fine if it is not there then you need to create headers and then you can tick or untick if you untick that will not take if you tick then the first whatever is there that will take it as a ers then you can click okay see now we can have the options of all the stable function like here whatever the drop- down menu is showing right here you can go for sorting and filtering also like from ascending order to descending order or filtering only you requir from 10th number to 15th number all those things we can apply and we can see here and see all these styles were there so for whatever for your visibility you can select whichever is looks good to you or for your data that you can select like colors or whatever the formatting all those things you can select so this is also the table function okay and the borders also like row and column wise only column rowwise or only row with the middle numbers or whatever the color also the line borders okay that you can select here and if you want more Styles you can go and you can check here and if you want any new style also you can go and you can use anything is feasible for your data okay or which is looks good to you okay that you can go and you can do this tables and borders and about this filtering and sorting okay here we have sort and filter option or command correct so this is also so in the coming videos we have covered okay you can check this filtering and sorting also so now we are going to discuss about date and time function okay so here in this video we are going to cover how to calculate the today Now function year month day hour minute second weekday function week number function ISO week number work day hour minute and seconds okay so these are the information we are going to discuss in this particular date and time function in Excel okay so here just have given some examples data or you can call it as dummy data okay customer name ABC Enterprises invoice number 1565 invoice date so here if you come for the date okay here we have date month and year that is the format correct so if you want to change this okay you can go here right click format cells then you can go here date here you can see 14 that is DD mm y y y okay here also DD mm y y like this we have so many other options are there so which one is feasible for you people okay according to whatever the flow you are going to follow for your organization or for your personal purpose or any other things okay select that one I'm going for this day with DD month and the year okay this is the format I have taken next if you go for a credit period so this ABC Enterprises they are allowing these people to pay the money within 5 days and the invoice amount is 150,000 okay so this is the basic information for calculating this this information okay so first we calculate day like how many days are for this particular this wedness day 4th December 2023 so which day if you are going to calculate in a bulk this is just one information so we easily we can tell that 4th December 2023 so which day 4th day that is number four date month is December and the year is 2023 and which date is Monday we can easily tell but if you have the bulk number of data if for example thousand datas are there then IND usually we can't find which day it is or which month it is or which year it is correct so that is the time we can use this day function and the formula is is equal to day and open the bracket select just this information and close the bracket click enter this is same like fourth okay date is fourth fourth next if it comes to month for month is equal to month Open Bracket next select this particular cell close the bracket click enter so December means 12th month correct next year is equal to year Open Bracket select this invoice date so 2023 so once you apply this formula next whatever the bulk data they have given for example here 180 datas they given so just you need to drag and drop like this you need to drop then you will get the what is the date month year okay next weekday function so for this formula is is equal to weekday just select this close the bracket and click enter so this is the second day so if you go for Advan Excel also we have the option like Monday is the zero day or Sunday is the zero day like that we need to select Sunday is the week first day or the Monday is the first day if you select that option whichever is feasible for your organization or for your information then you can select here Monday is the second day of the week okay that is first Sunday they have given second is Monday they have given like that whichever the number you want or you are going to start your organization books or your books of accounts with Monday or some companies they are going to start on Sunday so whichever the number of days you are going to start that number you need to specify in the advanced Excel that option is also available next week number so in a week in the seven days this is the second day Monday and in this particular year 2023 this 4th December in which week it will come that also we can easily find out with this formula that is is equal to week num select that F3 column close the bracket enter so in a year it is 49th week okay why all this information is very very important means so if you're are working in a manufacturing company so all the stocks or the inventory or warehousing all those information we need to record in Daily wise or week- wise monthly wise quarterly wise off yearly yearly wise correct so on that time this all information calculating for each and every raw material comes to the business or whatever the finished it goats going out of the business for all those stock inventory maintenance these formulas are very very important for day-to-day life for our personal things we are not going to use correct so which week of this year which month month of this year or which day of this month all those things will not matter correct but in a manufacturing company or in any other companies if you are calculating the data or if you're going to store a data like monthly wise yearly wise weekly wise on that time this formulas will help us very very crucially okay so next ISO weak number so as per the iso that is ISO if you typ you will get the formula also open the bracket select this information and close the bracket enter so why I will tell you what is this ISO week number okay this is we are going to as per our calendar year this is the 49th week of the year but here we have a special option or in the feature of in the Advan Excel that will be enabled okay just you can select the first day of the year that is 1 January so from Thursday onwards if for example 1st January 2023 is starting from a Thursday then you can select from Thursday onwards we need to start the books of accounts so week means that will start from first week of Thursday to second week of Thursday that is complete one week like that they are going to calculate correct so on that time this ISO week number is also very very important next we are going to calculate the payment due date okay so if for example this is only one inform information is there correct so Monday 4th December 2022 and they are allowing credit repayment period is 5 days so what is the next or after 5 days what is the date easily we can write here what is the payment due date but some companies will give 15 days of credit repayment period 30 days of credit repayment period 45 days or 90 days and if they have 150 or 200 information the data on that time calculating that it is very very difficult correct so for first we need to change this date and format okay right click format sales select any of these okay ddmm YY okay next go here formula is is equal to work day that is if in between whatever the Sunday or whatever the holidays are there that you can select here and you can select this next how much or how many days they are allowing credit period that you can select close the bracket click enter so they have sold Goods on that is invoice date is 4th December 2023 and they need to repay that whatever the 150,000 on 11th December 2023 okay so days overdue also that we can find out like what is the current date okay as per the current date if they are not paid then they can calculate this days overdue also so for this days overdue calculation that is is equal to today function close the bracket and minus this payment due date okay so this is there in the date month year format correct that we can easily convert into numbers that is General format sales go to number okay so they have to pay on 11 December 2023 but till the date they have not paid that is what is the number of days overdue that is 239 days is overdue okay like this we need to calculate this overdue okay next we have discussed today now year month day next we can calculate our function okay our second weekend or week days that we can calculate now for calculate the current date and time for that formula is is equal to now close bracket enter see 6th August 2024 and the time is 18 hours and 4 minutes okay so first we can calculate our function okay so for for our is equal to our close the bracket click enter so 18 hours okay so here only you can easily find that 18 hours I've already told you also so for calculating one information or one details or one data that is very very easy but if it is in a bulk then these formulas will help you a lot okay next the minute function so for minute function is is equal to minute select this okay close the bracket click enter so 18 hours 5 minutes and we can easily find out the seconds also here we have only 18 hours 5 minutes seconds they have not given correct so seconds also we can find out if is equal to Second select this close the bracket click enter so 52 second this we have Now function we have enabled on 18 hours 5 minutes and 52 second we have enabled this function okay so this is how we need to calculate hour minute and second function so these are the date and time functions okay so whatever we have used the formula and calculated it may be payment due date date overdue day month year week day week number ISO week number our function or minute second all those things we can go here in the formulas bar and that in that command date and time here also you can go and you can check the formulas and you can easily if you're not knowing okay the formulas then you can easily go here check this formulas and you can apply and you will get the values okay so this is is how the date and time function Works having mastered formulas and functions you can now perform complex calculation and data analysis next we will dive into conditional formatting a powerful feature that visually highlights your data based on specific criteria this will help you quickly identify Trends patterns and other outliers making your data more insight and actionable please sub subscribe for more videos so friends so what are we waiting for let's get started with the video with introduction to conditional formatting formatting what is formatting formatting is actually nothing but playing around with some of the tools which we are having including font style font size font color or fill color this enables us to make sure some of the contents are standing out from rest of the data this is mainly done to make sure that the viewers during this presentation does not miss up on any of the contents which we would like to share so only such important key points or data are being highlighted using formatting conditional formatting before knowing how to implement conditional formatting can you guys tell me what is conditional formatting now we know what formatting is and we also talked about change the font style font color fi color and all those things so is there anything else which we might have missed out which would make conditional formatting actually conditional formatting is formatting itself the only difference is that by following conditional formatting it would be more easy and convenient for us to apply formatting in any data sets or worksheet here in conditional formatting we'll be giving certain conditions and only the cells in which the data satisfies this condition would be formatted and rest of the cells would remain the same therefore by following conditional formatting we need not go to each of the celles and apply formatting for them separately we could simply give the conditions and the cells which satisfy the conditions would directly be formatted according to our commands I can give you a simple example for you to understand how conditional formatting is much efficient than formatting consider that you are given a marksheet of certain students and you need to filter out the students who got 80 plus so if you're following formatting you need to go through each of the cells and find out who will code 80+ and apply formatting for each of them separately but if you're using conditional formatting you could give conditions and all the cells which satisfy these conditions will be formatted automatically according to our commands now that we know what is formatting and conditional formatting let us try to understand how we'll be able to implement conditional formatting in Microsoft Excel so here file home insert page layout all those things are the various ribbon which is available on Microsoft Excel so conditional formatting is made available in the home ribbon and in the home ribbon we'll be having the option conditional formatting the various options available to us in conditional formatting are highlight cell rules top bottom rules data bars color scales and icon sets the other sets of options include new rule clear rules and manage rules highlight cell rules highlight cell rule actually helps you to highlight the cell which satisfy the condition top bottom rule the topmost or the bottommost values can be formatted here which satisfies the conditions data bars data bars are actually pictoral representation or similar to bar graph which is present within the cell the length of the bar depends upon the magnitude of the value which is present within the cell color scales in this formatting the cells will be given a particular color based on the value or based on the Range in which they fall under icon sets are also similar to color scales instead of colors here icons are given to the cells depending upon the range in which they fall under the other options which include are new rule where you'll be able to create a new rule based upon your requirement clear rule helps you to delete the existing rules which have already made manage rule helps you to modify or make necessary changes to the rules you have already made data bars color scales and icon sets I hope you have understood what these three are from the previous slide anyways we'll be diving deeper into these three while we are implementing conditional formatting in the platform my Microsoft Excel now let's take a look at what as cells that contain here you'll be able to format the cells that contain a particular value text or belonging to a particular range you'll also be able to format the cells which are empty or occupied as well in more rule types you'll be able to format the cells which are having same values or unique values you can also format the cells based on top 10 values bottom 10 values top 10 percentages bottom 10 percentages or values which are having above average or below average formula based apart from all the other methods in conditional formatting Excel also provides us to give formula and only the cells which satisfy the condition in the formula will be formatted all the other cells will be remaining the same now that we have an idea on what is conditional formatting let's move on to the platform and try implementing these methods of conditional formatting on Microsoft Excel so guys now we are in the Excel platform and here we'll be trying to implement formatting and and conditional formatting to the data sets which we are having so let's start with formatting so in the data set which we are having to implement formatting we can see in the column A we are having name and these are the these many names of these many people and column B we are having their age so these are the ages of these many people and in column C it is a sex and these are the sex of these many people so we are having the name age and sex so by looking into the data we'll be able to understand that name age sex are the headings of the three columns right so those need to be high reled so I'm trying to give bold phone to each of the heading that is name Ag and sex so what I'm going to do is so I'm going to select these three cells that is name agent sex and try to give it bold so in home ribbon so the file home insert page layout formulas are all the various ribbons which are available in the platform Microsoft Excel so in order to give bold we can go to the home ribbon and here we'll be having the option bold you can also press control+ B in order to get the font bold now we got the headings all in bold right so we have got the headings right now formatting is actually done for the heading name age and sex now what if I need to give a particular formatting for these datas based on their sex so John Robin and Sam these three people see John Robin and Sam these three people are male and Gina and Amy are female so I want to give a particular formatting for male and female separately right so what I'm going to do is I'm going to select the the male people out of these data that is John Sam John Robin and Sam and then trying to implement fill color this is the fill option this is the text color option okay in fill color I'm going to give um this color maybe so I've given a particular formatting to the information which is being represented in the table so this is John Robin and Sam so we'll be able to recognize easily who are male who are female so what if I need to give one more color to female also so what I'm selecting their data and then implementing with another color right so I'm giving dark green for female light green for male so I'll be able to depict or I will be able to interpret very easily who are the male ones who are the females right okay that is done I would also like to teach you one more thing how you'll be able to copy formatting from one cell to another that can also be done in simple steps right so what you need to do was you do formatting to one cell so John have selected the three cells from John and I'm implementing this color for them so what if I need to copy the formatting done to these three cells to Robin and Sam that can be easily done so in this case the only formatting done is fil color so what if a lot of formatting is being done to copy each of those formatting is going to be very difficult right so we'll be able to use this option which I'm going to teach you so that you'll be able to easily copy the formatting which is done to some particular cells of the selection and then directly Implement those formatting completely to the cells which we require so I have already implemented a kind of formatting to these three cells so I need to copy this formatting to Robin and Sam right so what I'm going to do is I'm going to select these three cells and in the home ribbon I'm having an option called format painter so format painter enables you to copy all the format which is being implemented to these three cells which we have selected so now we know that these three cells have been highlight so all the formatting which has been implemented to these three cells will be copy and in order to paste this we can simply select these three cells which in which we need to paste this so after releasing your mouse you'll be able to see that the same formatting which is being implemented with John is directly being copied from John and pasted at Robin okay so in order to paste this to Sam is all all these three cells are initially copied so you need to go to the home ribbon And format painter and the format will be copied and then it can be pasted simply by clicking onto the cells which we require to the cells which we need the formatting to be pasted right so I hope you have understood how we'll be able to do formatting in Microsoft Excel so what we have implemented is so we have given bold to these three names that is name age and sex which is the heading and we have given fill color depending upon the sex right so and we have also learned how to copy and paste formatting from some particular cells to another particular cells which we require right apart from that there are a lot of other tools as Wells so in here we you'll be able to change the text style here we'll be able to change the font size here underline italics field color text color all these are basic options which is being available in Microsoft Excel so you can easily go through those and understand now we are done with formatting and let's see how we'll be able to implement conditional formatting in Microsoft Excel all right now we'll be trying to implement sales that contain so with these data we are trying to implement Sals that contain in Sals that contain we'll be able to conditionally format the cells which are empty which are not empty that is one option which cellet content provides okay and we'll also be able to format the cells which has values ranging from one particular value to another particular value so we'll be able to give a range or give a particular definition to the range and then all the cells which defined to that range will be formatted we'll also be able to highlight or format the cells which contains a particular text so if the cells which we have selected contains a particular text we can give that text as an input and if any cells contain that particular text it'll be able to highlight that cells as well so let's see how we'll be able to implement those things here all right so right now I'm trying to implement conditional formatting cells that contain to blank cells so here I want to highlight all the blank cells which have being made so in quantity we'll be able to see that there are some cells which is blank so this cell is blank and this cell is blank as well so I need to highlight these particular cells okay for that what I'll be doing is I'll be selecting the cell ranges and I'll be moving to home ribbon conditional formatting and then here I'll be choosing new rule so when you choose new rule you'll be having a new box coming up so in this box you should select format only cells that contain so after selecting that you'll be able to do all those things which I have told you right now so in this First Column you can drop down and choose blanks so so after this we'll be able to highlight or format a particular cells which are completely blank so to format that we can select the format option and if you need to have a number font border or fill you whichever format you want to give to those particular cells you can do that so here I'm going to choose fill so I'm going to give a color red to highlight that in these particular cells there are no value being input okay so I'm giving okay and then okay has has here as well so see now we are able to highlight the cells in red color where there is no values at all in those particular cells so we are able to highlight that now what if you want to do highlighting or formatting to particular cells which range from one value to another value we can try implementing that so that also what we should do is Select this range go to home ribbon conditional formatting new rule and format only celles that contain therefore to give formatting to the cells which fall under a range of value so for that we can be choosing format all cells that contain and you can place cell value itself here and in between you can give the starting value and the ending value so here the data range from 1 to 11 I guess so I'm choosing a values between 7 and 9 so all the values between 7 so I'll give let me give 10 okay so all the values between 7 and 10 will be highlight since we are using between option here and we can choose the format in which we need to highlight or format that so format um you want to give border so outline I can I have choose outline and then I am choosing one color as well so I choose an outline and color as well for those cells in which the cell fall under the range of 7 to 10 so I clicked okay see we have got the cells which are in the range from 7 to 9 we have got that fill color in blue and we have also got the outlining for each of the cells which satisfy the given condition so we understood how we'll be able to format the cells which are blank how we'll be able to format the cells which fall under a particular range of values now let's see what can be done to the text we are having here so we are also being able to format the cells that contain a particular text right so I'll be teaching you how we'll be able to implement that as well let me undo this and then so for text I am choosing this column customer column and I'm choosing these many cells all the cells in these column or the selected area are to be formatted using conditional formatting a with cells that contain so I've already chosen this home ruon conditional formatting new rule sells that contain so since I need to look for particular text let me select specific text and containing so I'll be able to select the specific text containing let me say two letters array so if there is any name which contains array that will be highlighted and it which format it should be highlighted can be selected using this so font let me make it let me make it bold um fi color should be say blue okay okay see Rakesh AR is there Ram AR is there Rahul AR is there so in these three names we we have got the text specific text ra so these are the ways in which we'll be able to use cells that contain conditional formatting so what we have looked at here with cells that contain are we'll be able to highlight cells which are blank and not blank cells which belong to a particular range at the same time we'll also be able to look into the values which is greater than less than minimum and all those things can also be implemented there and after that we went and looked upon cellet contain specific text so if some specific text are being Incorporated in any of the values of our selection that cells can also be highlighted now let's try to find out how we'll be able to format the cells which are greater than a particular value from the selection which we have made so the selection which we are making here is going to be from B2 to B13 which is nothing but the quantity and I'm comparing this value to a value in D1 which is nothing but 700 so let me just make the comparison initially in this in the column D itself so I'll be writing the formula equals B2 is less than D1 I need D1 to be absolute reference so I'm giving dollar sign before and after d so it is getting true 526 is less than 700 so I'm going for the autofill as well and this has been updated to throughout the table okay now let's see uh let's apply conditional formatting to the table now I have selected the cells in which I need to apply the conditional formatting and then I'll be formatting conditional formatting uh from it's not empty to custom formula and here I'll be giving the formula the formula which is nothing but the formula which I have taken here so I can directly copy the formula from here and paste it here see now we have we have seeing that all for all the values which are true we'll be getting the highlighted in the particular month so in these months the quantity is being less than 700 so in January March April June July September October November the quantity of sales have been less than 700 that is what we have come to know by doing this conditional formatting now if you want to change the formatting default you can go and say you want to make this blue or say pink or something of that sort you can select some color and then click done so by doing this we'll be able to format or we'll be able to format the cells in which Which is less than a particular value so if you want to do formatting for the greater value so which are which are the values which is greater than 700 you can directly give the formula you can change it directly here by giving the lesser than formula to greater than formula and while implementing the conditional formatting so you can copy the same formula from here and paste it there so that you'll be getting the formatted for the values which is greater than 700 now let's try to find out how we'll be able to find out the top three or the bottom three values in Google Sheets so as I always mention try to select the cells which you need to be selecting to perform conditional formatting so after selecting this you can go to the option format conditional format and there you know you'll be able to see the selection which you have made and then the format rules is not empty so let's change that to custom formula and you know we'll be giving a formula to identify the top three values and the bottom three values so first let's understand what the top three values are so here we'll be giving the particular formula so that we'll be getting the top three values so the formula is is equal to rank and the column is B right so we'll be writing B2 that is the starting of the column comma and the range is from B2 to B13 so B doll2 is to B dollar3 and we'll be closing the bracket and we'll be giving the condition less than or equal to three so by giving this formula we'll be getting the top three values from the selection which we have made so if you want to change the format so let's say if you want to give this color see the top three values in the selection will be highlighted by using this formula so if you if you want the top three values you can give done and then you'll be having the top three values highlighted what if you need to have the bottom three values that the minimum three values so the only difference here is that to the formula you'll need to add comma one comma one actually makes the software understand that it should be in the ascending order so that's why we'll be getting the minimum three values out of this range which we have selected so if we if you need to have a minimum value just add comma one to the bracket or else if you don't need just remove that and since I need the top three values I'll be removing comma one so that I'll be getting the top three values and I'll be opting for this colum maybe and done see now I've got the top three values out of the selection which I have made now let's see how many unique values and how many similar values are there in this particular selection we need to check so that is what I'm going to teach you right now so in order to identify how many cells contain unique values or how many cells contain similar values to understand that we can follow the formula in the particular cell here so in D2 I'm going to write the formula which is equals count F and I'm going to open the bracket and see I'm supposed to give the range there so I'm going to select the particular range here which is from B2 to B13 so I'll be pressing F4 to log those and then after that I'll be giv the comma after getting the range and the Criterion so what I'm you searching for so I'm searching for the quantity if it is repeating or not right so I'm since I'm searching for quantity I I'll click on here so which is nothing but B2 and I'll close my bracket so after putting enter I'll be knowing that 526 only comes once in the selection so there's no other 526 so autofill is actually suggested here so that if you want to autofill you can press sck so that all the datas will be given here which is repeating or not so in all the ones shows that all those datas are unique and all the tools shows that it is not unique it is repeating in the it is repeating in the selected cells so all these ones depict that these values doesn't repeat in any of the cells when you come to two see 339 see here is also 339 so 339 appears two times and all the rest other values are appearing only one time since the values showing one will be unique and values showing more than one will be similar it can be two 3 4 5 6 or any number of times because you know how many times it appears in any other cell it will be adding upon like that so whenever it is one it is showing that it is unique value and whenever it is not one it is showing that it has some similarity with other cells as well the first thing which I would like to introduce you to in conditional formatting is databas so here we are already done the basic um formatting and now we need to do is conditional formatting to start off with databas so databas can be implemented to these sales depending upon the value okay so these are the products Apple Samsung real me redmi and these are the quantity of these products okay so to implement uh datab Bas the first thing which we need to do is select the cells to which in which we should be implementing databas all right so the selection is already made and now to implement databas we can directly go to the home ribbon which is we are already in the home ribbon and then we can directly move on to conditional formatting and then select databas so in databas we are having gradient fill and solid fill so the difference is that in gradient fill the color you know uh diminishes towards the end and solid field the color Remains the Same so let me take one of the gradient field that is this so this is the data bar data bar is actually a pictorial representation bar graph within the cell itself depending upon the value or the magnitude of the value which is being present within the cell so the length of the bar depends upon the value within each cell and so by comparing the values they'll be drawing the bar graphs and the length will be decided upon by the magnitude of the values which is present in each of the cells so what if you don't want to see these numbers only what you need is the bar graphs and not the values so that can also be implemented here okay we'll able to eliminate all these numbers and we'll be able to see only the bar graphs for that to happen we can go to home ribbon conditional formatting databas and here we have an option called more rules select that and here you will be getting a dialog box here and what you need to do in order to remove these values are show show bar only you're having a option here just give tick here and you can give okay so by now you know you'll be having an idea on which is the lowest value which is the highest value a comparison is being made possible here using databas where you need not exactly know the values which are being present so if you want to have the bar graph along with the values that is also possible and if you don't want to have the values just the bar graph that is also made possible using databas now we'll be trying how to implement color scale and icon sets Okay so in the same data set I'll be teaching you how to implement color skills and icon sets so in the First Column here we are having month these are the months from January to December in the next column you're having quantity from January to December so these may be the quantity of items which have been sold in the month January to December and this may be the revenue which they have generated in these many months by selling these many items or these many quantities okay so I'll be trying to explain how we'll be able to implement color scale so initially the first thing which we need to be doing to implement color scale is that we should be selecting the cells in which we need to be implementing those right so to to apply color scales I have selected these Min cells now after selecting the cells we can move to home ribbon and then in that conditional formatting and then we have the option for color scale so in color scale we have three color scales two color scales depending upon us you know we can SEL select one so in this this three color scales are green red and yellow and here also green red and yellow the order is different that is only difference here so here we'll be having uh the highest colors will be given to green and the lowest colors will be having red here the vice versa the highest color will be having red and the lowest color will be having green so I choosing this okay so now we have implemented color scales so what if you want to edit or modify the color scales which we have given so you want to change the color or you want to change anything else okay so that can be done as well so to do that we should be selecting these and go to conditional formatting and here we have an option called manage rules select that so all the rules which we have applied will be present in this box okay so the Only Rule which we have applied is graded color scale so we can select this and give the option edit Rule and then we have a dialogue box here where we'll be able to control the number of colors so it is a three color scale if you want to make it two you can make it to so the color scale will be bearing from red to Green you if you want to change the color from let say light blue to dark blue that is also made possible so the two colors which is available is light blue and dark blue and we can change the number of Colors by here and if you want to give the here what is being made is minimum value will be assigned light blue highest value will be assigned dark blue so if you want to reverse that that can also be possible so lowest value so here we have having the lowest values given light blue color in the highest value we are having dark blue color so if you want to reverse it you can just select uh here and go give a dark blue color to the minimum value and the highest value we can change it to a light blue so we'll be able to control the number of colors in which we need to have the color scales and we can also change the order in which will be assigned to each of the cells so we press click okay and if you want to apply and see how it'll be looking so it'll be looking like this and I said okay so these are the formatting which has been done to these Min cells and the two colors if you have taken the two color options so if you want to implement three color that is up to you and now coming to Icon sets Okay now in icon sets initially what we need to do is nothing but select the cells in which we need to be implementing icon sets so after selecting this we can we need to go to home ribbon conditional formatting and then here we have icon sets so we have directional icon sets shapes indicators ratings and all those things okay so here also we'll be implementing the icon sets based on these many things so I'm trying to give this here so the maximum values will be in green up Arrow the minimum values will be in red down arrow and the median values will be in a hyphen mode with yellow color right so that is what we learned in icon set so what if you need to modify this as well so like we modified the color scales what if you need to modify icon sets as well that that is also made possible so select the cells in which the rules which you have already applied you need to edit that or change that you can select those particular cells and then move to conditional formatting manage rules so these are the so so here you'll be able to see what rules are you applied so by clicking this and edit Ro in here we know that icon sets in icon sets we can change it here if you want to change so I can show icons only so if you don't want to you know show the values here you'll be able to do that show icons only so okay apply so values will be raed only the icons will be there okay so you don't need that so I going to change that again so since I need to have the number there so I'm going to untick this button and then these type the type in which it is being segregated is based on the percentage so the percentage so the values which is above the percentage of 67 will be given in Green Arrow upward arrow and the values which is in the between the range 33 to 67% will be given in the high Hy size in yellow color and the values Which is less than 33% will be given in red down arrow and I'm giving okay here and okay again see and now we have got the icon set and now we have also learned how we'll be able to implement color skills and icon sets and how we'll be able to make necessary changes to the already made format the last method of conditional formatting which we'll be discussing here is Formula based so we can give formula and the cells which satisfy the given condition in the given formula will be highlighted or formatted using our commands and the cells which does not follow will be remaining intact so here I'll be using formula to check on these values and I'll be highlighting based on these values to this column okay so what I meant to say is that so depending upon the values in this columns so if that value are being satisfied it will be giving this column will be formatted according to that value so let's begin so I'm selecting these cells to be formatted in home ribbon conditional formatting new rule so use a formula to determine which sets to format so that will be the option which I'll be taking so the formula here which I'm going to do is so here we are seeing values from 12,000 13,000 8,000 6,000 11,000 7,000 so I'm giving a formula where I need to highlight the cells which are above 10,000 okay so what I'm going to do here is I'm going to press uh present my formula here so every formula starts with an equal to sign so I put my equal to sign there and I chosen this cell so see the CH cell which have chosen is ah3 right hh3 okay so it is in absolute reference right now so all the values will be compared to 12,000 itself so I don't need that so I need the column to be remaining the same but rows need to be changing right so that absolute cell reference can be changed by removing the dollar sign before number three so the column will be fixed and the row will be moving so that should be greater than 10,000 right so and the format in which it should be done is bold and italic border outline border should be given and should be given in this maybe this color okay okay okay see so we have what we have done here is we have done the formula for these specific values and the column which have been highlighted is this so we selected these column and we compare the formula to the values which is present to these cells and then after that whichever cell satisfies the condition the corresponding row or the corresponding cell will be highlighted we have done in Formula based conditional formatting more rule type is the next phase which we are going to be talking about in conditional formatting so the features available in more rule type are we'll be able to format the cells which are similar so if there is any similar values in other cells we can be grouping that and finding or formatting those cells or if you want to format cells which doesn't have any similarity or if you want to format the cells which are having un need datas that can be made possible or if you want to format as top 10 values bottom 10 values top 10 percentages bottom 10 percentages or above average or below average that can all be made possible using the option more rule type so we'll be starting off with top 10 values which you want to be sharing so let's say instead of 10 we can say take uh three or four the first thing which you should be doing is selecting the datas in which you need to be applying this formatting so the quantity is the column which I taken for that and then in these selected cells I need to be implementing this conditional formatting so in I have made the selection and I'm going to home ribbon in conditional formatting so top top and bottom rules in that option itself we are having the option to get the top 10 items top 10 percentages bottom 10 items bottom 10 percentages above average and below average so if you're having any requirement of these things we can directly choose this option or if you having any requirement apart from the top 10 bottom 10 all those things and if you want to customly design a rule you can go to more rules over here so I am choosing to choose top three values so I have chosen format only top or bottom ranked values so here we can select top or bottom so since I'm having top three values so I'll be choosing top and instead of 10 I'll be writing three so if I if I want top three percentage of the values so I can click on the button here so that will be showing top top three percentage values okay so that can also be made possible here so since I don't need that I'll be unticking that and I'll be choosing the format in which I need to be doing the format so I gave bold format I'm giving a fill color of light blue and then I implemented so the top three values will be highlighted after giving this so press okay see 9 11 11 are the top three values in the selection which I have made which is this so see without even going through any of the values itself we'll be able to directly Implement formatting do these cells in the selections which we have made and here the top three cells are 9 11 and 11 right so now we have made the top three values so if you want to make it bottom three values do the same selection home conditional formatting um more rules top um yeah format only top or bottom ranked values instead of top give bottom and what are the top what are the bottom three values which you need or bottom five values which you need type that and you'll be getting that so since I don't require that I'm not doing that I'm hitting cancel and the next option which is available in more rule type is that you'll be able to find out the unique cells which is not being repeated in any of the other cells or you'll be able to you know choose the similar cells which is being repeated in other cells and you can group those and according to your requirement you'll be able to format so if you need to say format the unique cells let's choose the cells initially these are the selections I have made home ribbon conditional formatting top bottom rules more rules so here I'll be having format only unique or duplicate values I am choosing that so if you choose duplicate all the similar values will be highlighted in the format which we are giving so since I need a unique one so I'm choosing unique and the format in which I need to depict that as now fi color let it be this color I'm giving an outline border and the font I'll be giving italic okay press okay and press okay see 200 is not repeating in anywhere here 200 is not repeating 450 is not repeating 250 is not repeating 400 is not repeating 350 100 these many values which are being highlighted right now in the FI color slight yellow with outline border and italics are being highlighted because you know these are unque values these values are not repeating in anywhere around the cells which we have selected okay that is why we have got these cells highlighted and these the rest of the values might be repeating see 300 is here and 300 is here 550 is here 550 is here and see 0 0 so since it is not unique it is repeating so that is not highlighted so we'll be able to get the top values bottom value top percentage values bottom percentage values we'll be getting to know about about the above average below average so we understood how we'll be able to implement unique and duplicate highlighting or formatting right so now let's see how we'll be able to implement above average and below average okay for that let me undo and after undoing I'm making the selection this is the selection which I need to make and home ribbon conditional formatting uh top bottom rules more rules so above or below average okay I choose that so if I need to have an above average I'll be choosing above or below or equal to or equal to or below and I can choose depending upon whichever I require right so I choosing above above average uh format a bold and italic and then I don't need border I need a fill color of say maroon okay okay see the values which are above the average of the selection which I've made all those values are highlighted in the FI color and bold and italics so it is going to be very simple for you so you don't need to go through each of the cells and identify which is the average which are the values which is above average so you need to find out the average initially and you need to go through each of the cells and you need to compare the values in the cells with the average right all those things can be eliminated here by using condition formatting more rule type now we have understood how we'll be able ble to implement conditional formatting in Microsoft Excel so let's move on and see how we'll be able to implement conditional formatting in Google Sheets so in Google cheet we'll be talking about color scales more rule types and formula based conditional formatting now let's move on to the platform Google Sheets and see how we'll be able to implement conditional formatting in that platform now in Google sheet we'll be trying how to implement color scales with the data we are having here so as I said in the case of Microsoft Excel as well the first thing we should be doing in order to perform any conditional formatting is that we should be first selecting the cells in which we should be performing the formatting all right so here the cells which I choosing here is from B2 to B13 so I'm selecting all the cells from B2 to B13 and for conditional formatting in Google sheet is available in the option format conditional formatting after choosing conditional formatting and since we are trying to get the color scale we can simply go to the tab color scale here and by going to the color scale itself we'll be able to see a depiction here according to the default settings so in here we'll be able to see the range the selection which you have made is from B2 to B13 B2 to B13 so that is the particular selection which we have done to apply the formatting here that is which is being depicted here and here uh the minimum value will be given a green green color and the maximum value will be given white color so if you need to have a midpoint you can add that here as well so since this is the default setting it is showing like this so if you want to change the color to let's say a light blue so so the minimum value be light blue and the maximum value let's say let it be dark blue so here the minimum value will be in light blue and the maximum value are depicted in dark blue so that we'll be having a clear idea on what is the magnitude of the values in particular cells just by seeing the color and if you're happy with the color settings which you have provided you can simply click on done so that you have already created the conditional formatting for color scale for this particular cells and if you feel that one of the conditional formatting which you have implemented in Google Sheets need to be replaced or need to be deleted you can simply go to the condition format which is being present here if you want to edit you can simply click on that and edit and after that you know after doing all the edits you can click on done so that the conditional formatting can be done so if you feel that the conditional formatting which you have done right now need to be deleted you can just hover around the conditional formatting which you have done and then you'll be able to see a trash can so just by clicking those you'll be able to delete with conditional formatting you have learned to visually highlight key data points next we will explore we lookup a powerful function that allows you to to search for specific information within your data this will enable you to efficiently retrive and analyze data across different sheets and workbooks enhancing your data management skills Please Subscribe for more videos of excel let's begin with what is weup function let me honestly tell you that we lookup function is the most simplest method to bring in the relevant data to any worksheet you can bring in data from the same worksheet you're working on or even from a different worksheet at the same time you can also bring in data from a completely different workbook as well isn't that interesting so let me explain to you how we lookup function Works in actual case consider that you are given the ordering details of certain products which contains product ID ordering date and all those stuffs are there but you need to bring in the product name with respect to the product ID of each of the products which have been ordered that can be implemented or the product name can be brought into that details by implementing V lookup function so with respect to the product ID you will be able to bring in the product name by using the vup function in the Microsoft Excel so I'll be teaching you how to implement this in the platform Microsoft Excel before that let me explain on what is the Syntax for using V lookup function initially we should start with we lookup and in Brackets we should write lookup value so with respect to which cell are you trying to bring in the data so that cell should be selected in lookup value after selecting that you can give comma and select the table array you can select the table array from which you need to bring in the data to that particular cell right so you can make the selection of the table array after this after comma you can get the column index number so you have already selected the table array from which you need to be bringing in the data to that particular cell right so in that table array from which column are you trying to get the data so in the column array if it's in the third column you can write three or if it's in the fifth column you can write five then comma true or false like if you want to get the approximate value or the exact match that can be selected by giving true or false close the bracket and hit enter so that you'll be getting the relevant data in that particular cell now let's directly move on to the platform Microsoft Excel and try implementing V lookup function there now we are in the platform Microsoft Excel and we'll be learning here how to implement vooka function in the platform Microsoft Excel let's begin so in this sheet we are having the data of the ordering details and towards the right here we'll be having the data of the products which they have been ordered so in the left side we are having the order placing date these are the order placing date in the column A in column B we are having the customers who have placed the orders in column C we have the product ID refers to the product which they have ordered and you column D we are having the units number of units which they have ordered right so towards the right we are having the details of of the product which they have ordered right in the column H we are having the product ID and in the column I we are having the product names corresponding to the product ID and in the column J we are having the price of each of the items now let's see how to implement V lookup function in the platform Microsoft Excel see in towards the left we know these many datas are there but there is no product name so by looking at the datas on the left we'll not be able to identify which product they have ordered but only we'll be knowing the product idea right so in column B say I want to get the product name there so product name so I'm telling that in column E I should be getting the product name corresponding to the column C which gives product ID right so we'll be able to implement V lookup function on column e we lookup syntaxes we lookup you can plus Tab and then here we'll be able to see look up value so with respect to which cell are you trying to get in the value in the the particular cell that is what is meant by lookup value so I'm trying to get the product name with respect to the product ID right so in this Row the product ID which is coming is five so I selected C2 comma and table array table array is nothing but selecting the number of cells or selecting the table array from which we want to import the data to that particular cell so here the table array would be this all [Music] right so we have selected the table array where it contains the product ID product name and price that is the data from which we need to be importing the value to that particular cell so I have selected the table array then comma and column index number so we have already select the table array right so in that table array in which column is the relevant data to which it should be transported so in the table array we know in product ID will be the column one product name will be the column two price will be column three so we need to be getting the product name the product name is in which column in column number two right in column number two in the given selection so I'm writing two comma and pressing tab closing the bracket and hitting enter see here now we got the product name as bucket let's confirm in product ID five see it's bucket itself so with respect to the product ID we are getting the product name without even going to this data and finding out which is product ID five it is bucket so without even going to this data we directly getting the product name by using the formula V lookup function let's say like this we need to bring in the price as well right so in column F I need the price so price and in that column let's apply the V lookup formula V lookup let's just press tab there we look up look up value the look up value will be the same product ID comma table array table array will also be the same so select the table array I'll select the table array comma and and S the column in column index number so price comes under which column column number three 1 2 3 so column number three comma press tab close the bracket and hit enter see let's confirm that in bucket how much is the price five bucket 250 so just by giving the V lookup function we are getting the value from another data and trying to implement that data in the particular cell which we want it to be now let's try to Implement vup function in a different sheet and then in a different workbook as well so let's move to a different sheet so in details I moving to a different sheet where we are having the same details here product ID product name and product price okay so now what we need to do is from this table array we need to be importing data from this table array to that particular cell so let's see how that can be done so let's say for Bobby for the purchase Bobby has done I am using the data from a different sheet I'm getting the product name here as well so equals to we lookup press Tab and the lookup value will be 15 here since for Bobby the product ID is 15 I'm choosing the cell C3 which contains 15 comma and now is a place where we going to be implementing the change the table array from which we need to be importing data is from a different sheet right so what we need to do is go to that particular sheet and then simply select the D data so we have already selected the table array and hit comma and select the column index number so we are trying to get the product name right so it is in column number two number two comma tab close bracket hit enter see let's confirm that product ID 15 will be stapler it is stapler right so we have taken the data from a different sheet to this sheet so let's try implementing that for the price as well equal to we look up tab um product ID will be coming in C3 comma table array table array moving to the different sheet selecting the table array have table array column number three the price is in column number three column number three comma tab close the bracket and hit end up see we have got the price for the stapler the save lock cost 225 and it is showing 225 itself now let's try implementing vup function from a different workbook itself so for that let's open a new workbook yes I've opened a new workbook let's copy the details here so I'm copying the details copy paste I'm arranging it yeah now we have copied the details to a different workbook right now let's start implementing it in a different work workbook so in row four for Kieran I getting it from a different workbook so let's see how that can be done equals to we lookup press Tab and now let's look for the lookup value which is nothing but this one which is C4 corresponding to that row and comma let's select the table array so here by selecting the table array we need to import the data from a different workbook right so we need to go to that workbook and select the table array so selecting the table array so this is the table array which I want to be selecting I've selected that hit comma so in which column is the data which I want to be importing so that column index number of product name will be two comma hit tab close the bracket and hit enter see I've imported the data of product name corresponding to the product ID 4 product ID 4 is bottle bottle itself I've implemented V lookup function from a different work book itself let's see that for the price also we look up press tab so the lookup value will be this the same comma the table array I'm moving to the different worksheet sorry different workbook I've selected that comma the price will be in column three comma tab close the bracket and hit enter see we've got the price of bottle from a completely different workbook so now we have understood how to implement we look of function in the same sheet in a different sheet and also in a different workbook as well now let's see how we'll be able to copy the V lookup formula in Microsoft Excel right for that let me do the V lookup formula right now we lookup lookup value would be C2 comma the table array table array which I would want to be selecting is this comma column index number for the product name the column index number in the table array is two Right comes in the second column two comma and then tab close the bracket and hit end up so we have got the V lookup function in this particular cell so try to copy it vertically down so if I'm doing that see there is a error coming here right so mainly that is because in the cell the table array is not locked so we should be locking that or else you know while we moving uh down the cells the table array will also be moving so we don't need that to happen so we are locking it here press F4 press F4 and hit enter and then copy it once again see now we are able to get the names of the product with respect to the product ID in the column e so we have copied the we lookup formula to all these cells let's check it here so for eight it's called ring book is it it yeah in the table it is eight it is called ring book four is bottle four is bottle and then say five is bucket five bucket yeah so that we cross checked as well now what if I'm trying to copy it to the right side see it's showing calculator so why is it showing calculator when I'm copying it where I should be getting price here let me check for that so in this cell the formula here is the value which they looking for is D2 the column in which they looking for is two so in two you'll only be getting the product name that is one thing to be changed and the product ID they're looking for is D2 which is 12 which is not the product ID but unit but so what we need to do is we need to change that to C2 C2 and this two to three since the price is coming on the third column right press enter see we have got the value for the price of a bucket so for the bucket we are getting 250 so 250 is the price and 250 is reflecting in the cell as well and try now try copying this down see now we are able to copy this down as well for Bobby who purchased 17 stapler and the cost of a stapler would be 225 see 225 so for chocolates it's five let's say where a chocolate chocolate is on 13th which is costing five chocolate is on 13th which is costing rupes 5 see we are able to copy the vup function to all these cells and we able to get the relevant data in each of the particular cells right so now we have done copying V lookup formula next we look into lookup functions the first one is V lookup or vertical lookup so the function that searches for a certain value in a column to return a corresponding piece of information of another column in the same data set so I'll explain this with an example let's say that you are working in a firm that is currently restructuring and you are assigned with this task to arrange the different salaries of employees according to their Department s now you are the data set that you are given only only has employee IDs and the corresponding salaries now your task would be number one to find who all employees work in what all departments and number two extracting those salaries from that employee IDs so in order to extract that from the employee ID if you go one by one manually it would take you a lot of time but this can be cut short through lookup functions so let's see how that works first we look at the syntax of this so the syntax would be equality V lookup open parenthesis now V lookup functions have four different parameters the first one is lookup value second is table array third is column index number and the fourth is true and false we'll know what that is one by one so the first one is the lookup value so lookup value would be the thing that we already know so in our case it would be employe ID second is table array so this would be selection of entire data set third would would be column index number now what it is let's see so let's say that in the data set you have two columns number one is your employee ID and number two is the salary okay so in order to extract the salary that would be in column two so you would put the column index number as two and true and false so true is if you want a approximate match and false is if you want an exact match of it so now let's look at V looka functions in Microsoft Excel now we have a spreadsheet which contains employee IDs years of experience and salary of the employee now we need to find the salary of employee and we know what all employee ID is there so employee ID would be the thing that we already know and to find is salary employee now let's say that I want to find uh a salary of this Cale 2161 so I'll type here 2161 and I want to know the corresponding piece of salary so that corresponding piece of salary is 90140 90140 so now with looka functions we want if we just put the employee ID the corresponding piece of salary should automatically come right so we'll use V lookup in that case so in the salary column I'll use the syntax equality V we look up open parenthesis now the first is the lookup value so our lookup value would be employee ID so I will type employee ID here so I'll click on this cell comma second is a table array so our table array is the entire data set so I'll select the entire data set next is I'll use the column index number so since the employees is in column number three so I'll put as three comma Now I want now it says that you want an approximate math or a exact math so I would go with an exact math so I'll write false false close parenthesis and enter so it says na since I have not input any employee ID so let's cross check it so let's say 21 2126 right so I'll type here 21 2 6 and press enter so now it gives me 49,942 Let's cross check so the employee ID 2126 has a salary of 49,942 absolutely correct so this is the way in which we use look up functions and we can type any other thing let's check of uh let's say 3773 and press enter 9,29 let's see 73773 99,2 9 it's absolutely correct so in this way we can use it we can use it for years of experience as well so let's say I want to type experience exp exp so I can use the same function we look up open parenthesis lookup value is again I'll put the employee ID to find the years of experience comma we'll select the entire data set as a table array comma so years of experience is in column number two so I'll put two comma I want an exact match so I'll write false or instead of false I can also put zero that is also exact match so I'll put zero and close parenthesis and press enter so now it says that 3773 with a salary of 9,29 has an experience of 5.4 Let's cross check it 3773 99029 has the Sal has the experience of 5.4 years so this way we can use lookup functions and it is very efficient to use in longer data sets as well next we'll talk about H lookup so similar to V lookup H lookup is the same except instead of column it searches row here so rest Remains the Same the certain value in a row would return a corresponding piece of information and the syntax of it also Remains the Same but instead of V we have H over here and also uh the column we used to search in V lookup here in this case in h lookup we look at horizontal rows so the column would be replaced by row so let's look at the example in Microsoft Excel and know more about it so this is a spreadsheet here we have a table but now instead of columns we have rows what does that mean so generally we used to have column headers over here but now in this case these are horizontal headers or row headers we can call so the table is horizontally now similar to V lookup we'll know the price of different office supplies using its lookup so this is the Cale in which we need to find we'll we'll type the values the known value the known name like stapler pins sheets pens erasers or binders so we'll get the price of it automatically so let's click on this price tab press equals to H look up H look up parenthesis open now it says lookup value so our lookup value is this cell comma our table array is the entire tables range comma row index number now instead of column index number we have row index number so our first row would be this second would be this third this and fourth this so we we want the price of it which is in the fourth one so we'll write here four comma now we need a exact match so we'll type false or zero whatever suits you then parenthesis close and press enter so since we haven't typed anything it shows in a we'll write here pins press enter it shows me the price of pins right we'll look at one more example so let's say I put St press enter it gives me the price of it so similar to V lookup hookup also works exactly the same but in this case we have horizontal tables you would not get much examples of this while working on data sets but uh still as an example you need to know that hookup also exist after mastering we look up to efficiently retrive data let's move on to power quiry in Excel power query is a robust tool for importing cleaning and transforming data from various sources this will streamline your data preparation process making it easier to work with larger and complex data sets Please Subscribe for more videos on Excel introduction to power query now let's have a basic understanding of what power query actually is so if I ask you a question what is power query right so a basic layman terms the answer to this is that it's basically a tool that helps you transform data into simpler terms right through that if you attach a lot of different things like powerbi it can also help in your visualization of data so power query is a data transformation and connection tool what does connection mean here so connection basically means so I'll just give you a visual representation of it so if you have one Excel table right Excel sheet in which you have three columns right let's take an example of employee IDs so your First Column is employee ID your second column is employee name and the third column is employee gender let's say okay and we have another Excel sheet that has again two columns this time one is the employee ID the other one is the employee salary right so now in this case what connection means basically it it will connect these two Excel files and make it as one file so this is exactly what connection means here so this is good for a smaller data set but if we have a very large data set then it's best to use other database management tools like SQL or mongodb so something of that sort but when we talk about smaller data sets in which you just have very small sort of data in two different Excel files or two different sheets in one file so all that you can manage you can connect those things through Power query very easily so it's basically transforming the data and connection that is built in Microsoft Excel right so it is also available in other Microsoft products like as I mentioned powerbi and other tools of Microsoft it is also useful so it is designed to help users effic effciently import clean and transform data right in the further modules I will tell you the importance of data cleaning because data cleaning is something that everybody has to do with data and to have the understanding of how this data can be used can be cleaned into usable formats considering our problem statement at hand right so all these things we will be talking about in the coming uh few modules so in a nutshell it is designed to help users efficiently import clean and transform the data from various sources before using it for analysis and different types of reportings right so if I talk to you and if I ask you that what are the key characteristics and functions of power query so the answer that would be importing cleaning and transformation as we just discussed so what is data import right power query allows you to easily pull data from a wide range of sources including Excel files CSV databases web sources and more it simplifies the process of retrieving data and even from multiple locations within the system so what exactly this helps in right is that you don't have to worry about that for example if you have a SQL database you don't have to convert it into uh CSV files or Excel files you don't have to create that it's already will understand which format it is in and it will transform the data as per your needs so one such new feature that has come in this particular power query is now it can retrieve data from PDF files as well so this is something that is new which was not there before but now if you have a PDF in which you have a sort of data that is there it can retrieve or the data from the that particular PDF as well I'll show you that as well then comes data cleaning so data cleaning it provides the tool for cleaning shaping data which can involve removing duplic licates filtering out unwanted information sorting data and correcting errors then comes data transformation Now power query enables users to manipulate and reshape data you can split or merge columns convert data types apply custom calculations and create new columns to suit better your various analytical needs now what happens is that in a normal functioning of excel there is a heavy usage of various functions that you need to type out right for example if you want to sum a few columns right what do you do you write equal to sum function and then the entire uh the cells drag and drop till the time you want it right so that's how you get the sum of that particular column or the particular rows so in this case what you not need to do is use your keyboard everything can be done with just the clicks from your mouse that's the beauty of power query so when I talk about data transformation right reshaping of data you can split and merge columns right why is it it important so in a nutshell it is important because there are various problem statements to what you would be doing and to understand that what is the way of thinking you should start with when you receive a data set identifying the problem statement removing the various elements that are not required and also transforming it into ways in that can make your analysis easier right if you need to create additional columns if if you need to hide certain rows or delete certain rows remove certain rows so all these things can be done through data transformation in power query right then comes data combination now users can combine the data from various sources by joining various tables merging queries and appending queries right so I just talked about this in this particular slide that you can merge different tables as well right so joining tables merging queries and appending queries right this feature is particularly valuable when you need to consolidate data from various places into a single data set for analysis right so when you will be entering the corporate space especially when you're talking about consulting firms right if you have the dream to go in consulting firms then this particular element in which you have to use a lot of different Excel sheets Excel tables and then find your analysis so opening all those different files Excel files can become a a big task to simplify that analysis to simplify that task it is easier for you to have that in one single data set rather than split is split it across various files so what you will be more efficient when you're using power query in that regard the interface right the power query interface is such beautifully designed that it is very user friendly the visual interface for performing all the various Tas Tas that you you want to do it doesn't require extensive coding or technical skills making it accessible to the worldwide range of users right so this is the beauty now I can give you an analogy of this how is that the case so for example earlier right we have seen that to do any mundane task you have to do it again and again right you have to do it how you're supposed to do through manual power but the introduction of various simplified tools right various artificial intelligence tools has made the life easier right charity is one such such thing in which your most of your day-to-day task can be simplified even in your corporate space so this is the reason why automation of various things are so important in today's world because nobody wants to do the task again and again it becomes mundane and it's not very efficient to do the same task with the same effort again and again when that task can be simplified or when that task can be broken down into steps and can be can be done more efficiently so efficiency is what every company looks at and in today's world and being efficient is one of the most important elements for your career growing growing in the corporate space so user friendly interface is what we were talking about so the the powerquery editor offers a userfriendly visual interface for performance all these task right so just how in chat gbt you do not have to learn any programming language you do not have to learn anything you don't have to Learn Python C or R you don't have to learn any programming language just what you know right what you what your basic task is you just type it out to chat gbt right you give the instructions in your language and not in computer language so that is what the change has been in the past one year where everything is now being automated is now being transformed and we are leveraging the AI the power of AI in building the corporate companies building the corporate space and growing it further so it's very important to be in the zone in which you can automate things and power query is one such thing in which you can really automate a lot of different elements here so uh there is one catch though when we talk about power query that there are some prerequisites for power query and various system requirements right power query is not readily available in all the Microsoft versions but if you are someone who has the Microsoft of 2016 plus uh version 2016 plus then it would be good to go in your uh particular Excel but if your Excel is of 2010 right or 2013 also for that matter in some cases cases then you need to have a different sort of add-ons from Microsoft to add it in your Excel files so this is what what is very very important now when we talk about the prerequisites so it's basically you should have an idea of what functions are so I will have a a session here after this or repeat so I'll be talking about functions in the coming slides but what is most important right now here is to have basic sort of understanding what is a cell what are rows what are columns I assume that you would be knowing all that so that's the basic knowledge that you need then the system requirements what do you need as system requirements it's basically Excel stable connection of internet if you are working with online data sources if not then that's also not needed right so one important aspect if you are of 2010 or 2013 then you have to have certain add-ons in Excel how to do that I will tell you but there are various things that may not be there right so for example PDF thing I'm not sure that it will be there in 2010 or 13 version but you have to check it out but uh even power query you have to add as an addin there understanding functions for power query now in this module we'll talk about what are the various functions what type of functions are there that you have to manually write first in Excel right what is the thing that runs an analysis right that is the functions so first we'll have a brief look before going into Power query and understanding how this can done quickly but first have a look at what functions are so when we talk about functions right they are predefined instructions that perform specific calculations in a particular order in the spreadsheet right what is the basic syntax of this that we talk about is an equal to sign which starts it defines the start of a function right then what type of analysis you want to do right is it sum is it minimum is it maximum is it average do you want to count things you want to trim things you want to concatenate so all these things come under functions right so this is the analysis part of it so the second thing is the name of the function right average and everything then is the range of cells now this element it starts with an open parenthesis and ends with an open parenthesis but what is inside is the cell right what you have to determine is the cell or a particular string that you have to give right that depends on the type of function right all types have different sort of understanding so for example what is being seen on screen there's a particular element right called E15 so this cell number is E15 and this cell number is E20 right so you can drag and drop till from E15 to E20 so the range will automatically come from E15 to E20 right so this is one sear thing that you have to put inside the parenthesis what else can be there is what we'll look at when we discuss the type of functions right so this is how you would have a standard thing set up so this is all manual task right please note that in power query some of these elements would be just the click of a button you do not need to add manually equal to average open select the range no it becomes really easy in power query how to do these analysis so the first type of function that we will be looking at today is count if so function that counts the number of cells that match a specific condition so count if is there then range is there and condition is there right so if you have a range if you have a condition you can count the number of things so it will give you the count of it right the second one is equal to length if you want to find the length of a particular string then you just have to click on the cell write typ in equal to Len and then just select the cell and close parenthesis it will give you what is the length of that particular thing right then comes left and right so again all these things that are there right a function that would return a specific number of characters from the left side or the right side each of these things that I'm talking about is a manual effort and exactly these things can be automated using power query right so we'll have a just a brief understanding before you move to power query so uh from the left right side of a cell so we can write left cell number of characters right cell number of characters and this would be done then concatenate a function that combines multiple text cells into a single cell equal to concatenate cell one and cell two then comes trim function a function that removes extra unwanted space from the cell equal to trim cell one so basically it would remove anything that is there extra that is not required in the analysis so it will remove that right concatenate is also very important aspect if you have to merge two columns and this is quite helpful right so we'll look into this in detail as well so now let's look at this uh file that I have over here so in this we will be typing out these particular uh things that we discussed but this is normal this is not power query this is normal what happens in Excel so I just wanted to give you a gist of how the things could be automated there in Excel so now if I have to look into how to get the things here so what will I do I'll write a function in this particular thing data set I have a movie title and a language and I want to figure out what is the total number of movies in English French or Italian right so for English what what thing I have to use is you have to think about it that's the human part that would be required to know which function to use right in this case it is required as count if right then this would be the criteria then I would add a particular criteria right I want English so I'll write English close close parenthesis enter so basically in this particular range There is five times that English is repeated right you can check it manually 1 2 3 4 okay 1 2 2 3 4 and five right so there are five times that is repeated similarly I can do this to them as well right the criteria would be zero because I typed in English so I'll change it from English to French and in this particular case it's case sensitive so if you type Fringe in small F right it will not show you the reason is that it's case sensitive so now so this particular thing right I had to use B so the column is mentioned is wrong so I'll just change it to okay so it's five times it is there similar to this I'll just write it again it's saying D because I drag do there so it changes the column cell I'll change it to B B22 that's our analysis thing and then I'll write here since we want to know about Italian so I'll write right I press enter six is the number of times that it has repeated so we'll take an example of this Excel file over here so what it does is it has some unwanted spaces in the beginning of it now when you talk about The Unwanted spaces that are there you have to remove it because this will be a hindrance when we work on the analysis part then this could be in hindrance so what you do is you create a name movie movie title upd then in this particular case what we'll do is we'll put a function equal to trim right the best part is Excel would give you the suggestion itself add it plus should be equal to trim then which is which is the text I'll put this put this so it'll give me this so how I can check it I can just drag and drop so every unwanted space would be con right the third is we talked about left and right so I want to extract the let's say left uh six digits of the PIN code right so what I'll do is I'll just write here equal to left left open parenthesis this number of characters six close will just give me the first six elements from this cell right otherwise it would be very difficult to manually move it so it becomes easier over here second part is that I want to extract the last six digits that is County written right so again the characters from the last one is County itself so what we'll do we will add in the right function then I will use click on that cell then write here six again because six is the number of strings that are there so then it will extract me that particular letter come T from this right so this is how left and right work then you have concatenate in which if I want to merge all these first name and last last name what I'll do is I'll type in equal to concat so it will give give me both both works the same so you can use either this is for a list or a range this is for a text string so we can use both so I'll just click on this and then I'll put my first comma second and then close parenthesis so it will give me the merged part of both of these right so this is how concatenate work then you have conditional format this is how concatenate work right now if I have to look at the length of this string so I'll do equal to Len then open parenthesis click on this then close parth so this is how you do and deal with functions so this was about the type of functions that are there in Excel data transformation and data cleaning so in this particular module we will be talking about the importance of data cleaning and why is it important to have data transformation and cleaning in place before you get on with your project the simple answer to that is data set should be as clean as possible because at the end of your analysis it should not be like you are at the end result then you come to know that there was a issue in the initial part of your analysis due to a bad data set so that's the reason everything you do has to be from a data in which you do not have to get back to right so cleaning is one such thing that is very very important so in this particular module we'll look at what are the various cleaning steps that are required and the data transformation aspect right so I want to take you to a journey of how an analytics project is thought after what are the various steps that are involved in in that so if you see what I've mentioned here is there are essentially three key steps to any data project right the the first element or the first step is data cleaning now data cleaning forms the base of anything that you do right what does this mean this means that if you're doing data cleaning then it's important for you to understand the factors that are involved in it right there is a list of names and there are gaps in front of the name right when you're doing your analysis that gaps would also come right maybe there's a case in which you're receiving a particular data set from someone maybe a third party data set that has uh in terms of gender male female you have various numbers that are there right that is not accurate so then you have to go back to the source of the data and fix which column was converted into which column what is the various things that have happened to that data set so this is what you need to figure out from your end what are the various things that can happen to a data set there are lot of things in which the PIN codes if they have asked in a particular data set that should be of six digits maybe in some cases they are only of three digits or four digits right which is incorrect so that you need to figure out from your initial analysis of the data and then then clean that data aspects second is the analysis part of it in which you'll be transforming the data to the needs that you are building right and the third part is so is data visualization so what would happen is that if you're working on a data set right you're working in a company so in that particular project that you're working in there would be an end result right there will be a problem statement right problem statement now your manager or anyone who's leading the project or someone who is at the board of directors of your company if they want to look at the data it would not be in the form of tables rows columns no what they would be more interested in is the dashboard at the end what you have built or for that matter various charts and graphs that would be involved so this is one case in which you have to be very very cautious right so the first step actually the P0 step or the Step Zero right I like to call it Step Zero is to understand what the problem statement is right then move on to the step number one that is data cleaning why is understanding the problem statement so important because just imagine a situation in which there there is a data set of let's say six columns and according to your problem statement only three of those columns are being used right so you do not want to spend your extra time or cleaning the values that are there in the three unused columns that would never be used in your analysis right so in that case you only have to clean the things that are in the columns that would be required for your analysis right so that's why getting the structure ready is very important understanding the problem statement taking key notes from it describing okay these are the elements that would be required right so in in all of the forms that are out there that talk about data and talk about data analytics this is one key step that is missed by every absolutely everybody the importance of thinking beforehand before actually doing the analysis part right understanding the problem statement then going on to data cleaning then having the various models set up in data analysis it can be regression models it can be different types of higher order ml models would be there analysis model exploratory data analysis and everything would be there right so having that thing done in the first process would simplify and make your project more organized right you should not be in the analysis stage and go back back and see okay this element was not clean I'm cleaning it oh this is not required and I don't want to use it these are the things that we have to avoid that's why having a structured approach to it is very very important the third element data visualization as I already mentioned that the final element that board of directors or anyone in the company who is at the higher level management want to see they would not see Ros in columns they would see what is the analysis that you have come up with what are the hidden things that you are doing so that they can make more improved informed decisions for the growth of the company so that's why it is very very important or the client for that matter so it's very very important to have this structure in play when we talking about the steps or the flow the phases of a data analysis project so I mentioned data cleaning being the most important aspect now let's dive deep into understanding data cleaning now data cleaning we also known as data cleansing is a process of identifying and correcting the various errors that are there inconsistencies that are there and the inaccuracies that are there in the data set now these missing values duplicate entries formatting errors outliers and inconsistencies in data right so you you might encounter various missing values right values that are not present these mostly occur from open-ended forms right you must have seen how is data generated first of all is generated for me and you like we humans generate data most of it if it is a business to Consumer company b2c company then most of the data that you require for your marketing and all that things are comes from the customers B2B clients is come from the businesses but let's focus our attention to b2c segment so in this case the missing values how would they be created they would be created let's say a a signup form is there right on any of the website there is a signup form for that company you would uh type out an email um you type out your details first name last name and everything right so there are some values in which you do not have an ASC Mark right Aster indicates that it's a mandatory field to fill in the forms right if the aster form is not there most of the people tend to not fill if it is not present this element they do not fill it right which remains as a missing value in your analysis right that's how a missing value is generated when we talk about majority I'm just talking about majority of the aspects then you have also an element called duplicate entries right what are the duplicate entries maybe one person has filled a form a particular Google form teams form or let's say um different sort of form online right web page form they have filled it multiple times right so the duplicate entries would be generated formatting errors would be there upper case lower case different types of things right there's some there may be some outliers I mean that I talked about right columns having different names if if a person may have filled his last name in terms of first name so all these different things would be there right then it would be in consistencies in the data that you would find you would find numbers in places where the qualitative information should be there right so all these things are a type of things that would degrade the data set that would make a data set or right also there are two elements to a data set when I talk about data set there are two elements from where you receive your as as a budding uh business analyst you would have access to two types of data sets one is the one that would be trusted that would be basically you would know the exact source of the data set you would have the say how to make that uh source of the data set better the other one would be third party data sets that your company would have taken from somewhere or they would have got for analysis from somewhere right mostly in cases of Consulting you would have data set that would be coming from the client side to you right you do not have much say into how the data set should be in the first place right you have to analyze it you have to clean it you have to have your basic checks done right so third party data is something you do not have most control over so The Trusted ones right the ones in which you know the ends of it the in and out of the data set that is the one that's better but it's okay if we have to do with the third party data set we don't do not really have a choice but we have to do it so poor quality data set can lead to incorrect analysis it can lead to the faulty insights that would be there and of course unreliable decision making so these are also the elements why data cleaning matters so what is the importance right I already discussed the importance of data cleaning but uh now let's just look at in more organized way what it helps you in it helps you in improved accuracy so clean data ensures that more accurate analysis and decision making is done then also talks about the cost saving it prevents costly errors and mistakes based on the inaccurate data that is there then we also talked about the enhanced efficiency so it saves times in data anal analysis and Report generation data Integrity ensures trust in the data and the insights drived from it so this is what we talk about data Integrity now saving time why is it important to save time when we talk about uh data right so you might have parall running many projects at a single point of time so in order to make sure that everything is done tracked properly it's important to have data cleaning measures is in the first place because otherwise you would have to struggle a lot and then you would be mentally fatigued doing that and also various key things you would lose out on right so that's why enhanced efficiency cost saving so what happens in cost saving so it prevents costly errors and mistakes based on inaccurate data so I already talked about that at the end right the board of directors would make informed decisions for your company or for the company for the client right so understanding the importance of data cleaning so that you do not make such mistakes and then the end result is not skewed right also one element in data cleaning that is after you do certain projects you would get to know is that uh there is numeric data that's very easy to understand then there is qualitative data right what would be the example of a qualitative data so for example if you're running a restaurant and then you want a customer feedback on on your uh dishes or your on your restaurant or your various food type right so you You' give them a feedback form so let's say if the customer fills that feedback form then you would have a set of questions right that they have to answer some would be numeric like and or some would be non- numeric right qualitative data but how you figure out as a restaurant owner is to write in the questionnaire part of of the feedback right the way you write is also what matters right I'll give you an example so if you're writing something like how good is the food of a restaurant rate from the scale of 0 to 10 right so when I say this word how good is the food let's say right if I'm giving it and I write it to rate from let's say 0 to 10 so if I rate it from 0 to 10 right what is the thing that is there right what is the thing that you pause this video for a second and think about what is wrong in this particular question so once you do that I'll tell you what it is it's the word good in this entire question now if you're taking the feedback you cannot write a thing that is biased biased towards your preference right if I write how good is the food the rating would be in terms of not taking into the bad P perspective of the food it is only the good elements of the food right so this would become a bias and one important such thing in your analysis that you should look after is understanding the various types of bias that could be there in the data and eliminate in those bias you do not want your end result to be squeed in any manner so it's very important to know that there are various biases that would be there in your data and the importance of cleaning those elements as well now what are the steps that would be involved in a data cleaning process so it would be assessment data assessment understanding the data and the quality of data that is there then is data validation identifying the errors the various inconsistencies and the missing values that would be there right when I talk about understanding the data and its quality it's basically running through through the entire data maybe have a check done various checks done that if it is there you just put a tick mark So various checklist right your checklist should be there then identifying those errors inconsistencies and the various missing values that would be there right so that is data validation then is data transformation standardizing and formatting the data so it's very important to know that you have to make the data very consistent for your analysis so having that consistency having that standardized sort of elements is also important then adding any missing information or any missing context right so for this element for this part what you can do is you can go to the source of the data and identify what the missing information is as to your client or ask make changes to the to the information that is being provided to you right so what would do it would increase the quality it would significantly increase the quality of your particular analysis then documentation now this is also one important element that people tend to miss is documenting the things that you have done thankfully if you're working in power query you would know the documentation automatically comes what changes were made but let's say if you're working with a team of five to six people and then you have to figure out which person has made the changes so it's important to have that checklist in play in which and the documentation done so that you know that these are the people who have made the changes these were the changes made right so having that habit of documentation really increase the efficiency of of yourself and the time that is spent on the project also sign significantly reduces increasing the efficiency and the productivity of the project power query dashboard overview now in this particular module what we'll do is we'll have a Hands-On session in which we'll open Excel we'll see how exactly the power query editor window is opened and then we'll transform some data and find some meaningful analysis out of it right so let's get started with it so for this what I've done is I've have taken three dummy data sets now these are in the form of three separate Excel files now what I'll do is I'll Transform this data into a new Excel sheet and merge all those three separate Excel sheets into one how that is done I'll show it to you so for that all the Excel sheets that you have all the Excel files they should be in one single folder so for that I have a folder named over here as power query so in that I have all the three different Excel files that I have put so to give you a context this is about a e-commerce shipping data of a particular company so the customer ID would be there the dates would be there the cost the revenue everything would be mentioned and the status if it is shipped or not so in this particular case what we'll do is we'll analyze the data from three different years 2007 2008 and 2009 and now to tell the power so if you have three different data sets the usual way to do it is to use a SQL file or to use SQL to analyze this kind of data or python for that matter but in this case since the load of the data is less since the number of rows are less than what usually is used in SQL we'll try and analyze this part from Excel so in this case what I'll do is I'll open a fresh file of excel so now I have opened a blanked workbook note that I have not saved this blank notebook it is just a blank workbook that I have opened so in this case what I'll do is in order to access power query we have to go to the data tab in the ribbon and then click on get data now one benefit of using the Excel that is uh after 2016 is that you have power query inbuilt inside the Excel if you're using Microsoft Office versions of 2013 or 2010 in that case you have to add this power query as a Excel addin option right it would be there it would be slightly different from what it is in terms of the looks but the working would be exactly the same so in this case what we'll do is we'll click on get data now we have to see from what kinds of files you can upload so from the recent ones you can have an Excel uh workbook which is quite obvious then you can also have any uh Google sheet CSV files would be there you can have Json files would be there also you can have the Azure databases that also you can connect to excel from the database of SQL you can also upload various types of files so that can also be done from MySQL you can do that from Oracle you can do that from different kinds of even Microsoft access database you can uh upload the file here so uh one thing that has come up in the recent ones which I don't see over here is if you have the latest version of excel you would also have data extracted from a PDF so which was not there in the previous models and which is not uh here but if you have the latest version of Microsoft Office 365 you will definitely have that that PDF extraction option so in our case right now what we will do is we'll upload these files from a folder so I'll click on from folder option now I'll look at where my files are stored so I have stored it in the desktop under the name of power query so I'll click on this I'll click on okay so now what will happen is all the three files that have been uploaded first I'll get to see the path of it so it is stored in uh desktop and power query then all the three files the details of the content would be mentioned over here right so everything is mentioned over here that uh the name of the file the extension type also the date it has been accessed and modified so different types of things are there that are mentioned over here now you get few options to know what kind of transformation or what kind of analysis you have to do right so the first step for my analysis what I want is to combine and merge all these three Excel files into one so in that case you have an option here as combined right you can directly load all these files together but I want to make changes to one file and I want to replicate into other two files without actually making change in those files so in in that case what I will do is I click on this combine option over here then it will give me three different options combine and transform combine and load and combine and load to right so for my particular analysis what I really want is to transform the data and then merge all these three together right so I'll click on combine and transform so it will evaluate where the path is in the local repository now note that if you're using a local server or or a host that has to be accessed from a common server in that case this would not be possible what you have to do is in the local repository itself the file should be maintained the reason behind it is even a slightest change in that repository can cause all your analysis to fail so now we have this particular window that is opened so in this case it asks me the sample file what it should be now what is this sample file basically what happens is that when you have three files or let's say x number of files it would first say that okay if these three are common or they have some common columns in that in that case what would happen is they would want you to First make some changes or do your analysis in that sample file when you are done changing or making changes to that particular file then you can load and extract it to the rest of the file that you want to merge so in this case uh the option comes for all the three files that I that I have but as a default it takes the first file it recognizes that 2007 would be the first file so it automatically takes the first file that it suggests it uh you should load so for my analysis as well I'll just click on this first file itself that is the 2007 data so I'll click on order data over here it'll give me a sort of preview over here about how the data is now what I'll do next is I'll click on okay so now what has happened is it has opened a new window known as the power query editor so all the things that you want to transform in all those three Excel files can be done at one place over here now whatever you do outside this right if you make changes to the data set after this it would not be reflected over here so you have to make sure that first your data set is complete it's all the changes are done in that data set then you come to the power query window now as you can see my data set of 2007 it has some rows that are 999 plus so it shows that there are some rows over there then you have all the columns that are listed right the source name that is basically the name of the file then there is order ID then there is the volume of the shipments that are there then the revenue is mentioned the cost is mentioned and you have the order date shipping date order status right so now this is the basic layout of the file I can see that the data is not particularly cleaned over here that we can do in this transformation but what I really want is to have that thinking on what needs to be done next now this is a e-commerce data set what would be my thinking seeing this particular thing the problem statements need to be defined first and then the analysis needs to be done right so since the sample data set it does not have lot of complexities or I'm not running a e-commerce store to know what insights I want but as a dummy problem statement I can have that I want to know what the profit is from each order that is there and also what is the shipment days that are there right now since this data is not clean first we'll have some sort of modifications done in that case and then we'll move on to the analysis part so in this case I see that the name is of the file is mentioned over here which is insignificant to me so what I'll do is I'll remove this column because it was not of my any use now I have the order ID I have the uh shipment details I have the revenue I have the cost I have the order date and shipping date so the next thing would be to make sure that see I see there that the row number six has a missing value or a value that is not correctly stated so in that case what I'll do is I'll just go to the bottom of it since it's not a digit so what I'll do is I'll uncheck that part and click on okay so basically that particular row is of no use to us so I'll just remove that then the next aspect is to find the profit of the particular thing or if any right so in that case what I'll do uh from General thing we know that Revenue Minus cost would be your profit so in that case I'll select this column I'll click on control I'll click the second column that is cost then I'll go to the add column option over here in the power query window so in this particular ribbon we have something known as from numbers so since these are numbers and I want to have a sort of subtraction done so there are different statistic and different numerical methods to do that right so I'll click on this standard tab over here and since it's a basic subtraction between revenue and cost I'll do subtract now the subtraction is already here this would be our gross profit so now how would the power query know that you have to subtract revenue and cost right so in that case it could have also done that cost minus Revenue right so in that case there is no such thing that it would know by itself it's basically what you select first would be subtracted from the second one that's the basic understanding of it so now I have got at the end of it something known as gross profit so I'll just move it to the tab near cost right so there are some areas in which you do not have basically your Revenue was the cost was greater than the revenue so you did not really get uh any profit in that you are in loss so that is also being maintained over here now what else can we do in this case right so in this case uh we would also need to know that let's say that uh as a business owner I want to know what time is the order being shipped from the order date right I want the shipping date to be as close as the order date right now let's take an example if you're going to buy something on an e-commerce store you would want your shipment to be as soon as possible Right you be it Amazon or flip cart or any other e-commerce website that you're looking at as a business owner I also want the shipping time to be as soon as possible to make some sound decisions if my inventory uh has some issues or my supply chain network has some issues so I can optimize that part so in that case what I will do is I'll again subtract the shipping date and Order date right shipping date minus order date would give me the number of days that are there in between if any so what I'll do again I'll do the add column part I'll select both the things but this time I'll select the shipping date first because that would be later and the order date would be earlier so I'll click on shipping date then order date then click on something known as date over here so now you see that it understands that this is not a numerical value it understand that it is date that's why the option of subtraction or any numerical methods are already not given to us so in that case we'll just go to the date option then there is something known as you have a lot of different options you can change modify it in the way you want but what I really want is subtract dates so I will click on subtract days over here and it will create a separate column named as subtraction so I'll change that to number of days um between shipment generally try not keeping big headers in the columns because that could also it would not be as optimized in your end result of visualization as as you would want so keep it as less as possible so now in this case I have gotten the number of days between the order date and shipment date now if it is two right now as a business owner if it is as low as possible well I would know that this is going good two should be the standard that maybe my company would have set now there are some days that are six that is almost a week five almost a week so now I would want to know what are the underlining factors that would be there so I would probably see that okay if the same day right has delays what were the reasons be it was there no electricity in the ship in the let's say my fulfillment center right my big warehous so what would be the underlining issue that would be there that could be extracted from this part now the next step what I will do is I have got what I wanted now you would see that Revenue cost and profit right these three elements should be in terms of currencies right so what I'll do is I'll select all three rows over here then right click then there's something known as change type now it change in into currency it will automatically come in currency if you want to change the currency that can also be done if you want to change it in the currency of your choice now what I'll do is now this extraction for my 2007 file is done right my analysis is done what I wanted to see I've done I extracted the things I've I've removed all the uh factors that would hinder my analysis part uh missing values or values that are incorrect now what I'll do is this I'll go to the Home tab over here then I'll click on close and load now what will happen in that case is it will automatically combine this type of analysis in both the files of 2008 and 2009 now which would save me time so this is also the process in which I can automate what I'm doing to save time in terms of my analysis now this is the sheet that I have received from the power query editor now you would see that if I click on let's say uh any window that is this I would see it has already selected 7 8 and N so already 8 and N are already analyzed in the way I want so now I can also check I can also see the status of what this has done so in this case I do get all the things that are there I'll also get the data source of this is in my local repository now what if I have to add the next year let's say 2000 or maybe the previous year 2006 or 20 0 if I add it in the window after my analysis after click load and save if that file is present in that local folder that will also be analyzed in the same way so you do not have to do the entire process again it will automatically understand that this file has been added in the folder also needs to be uh extracted to Excel and then the modifications would be done right so now our power query analysis is complete now what will we do next is we just have to have the table more structured so structuring table is a task of pivot table so we'll click on pivot table over here and we'll let what the default setting is as it is and then we'll click on okay so now what happens is that a new window of pivot table has come now what we'll do is we just have to click on what we want to see if we want to see the gross profit we'll click on gross profit and or we can just change it into columns let's have the date also order date also mentioned so we here now have all the things that we need we have 2007 2008 2009 what is the sum of gross profit we can see that 2008 to 2009 the gross profit as significantly increased while it had a dip in 2009 I won't blame it if it is the recession of 2008 but uh nonetheless whatever the the reasons might be that the owner of the business would know better since it's a dummy data set I don't have to go in that integrities in detail so now it also gives a better option that is it divides it into quarter so it understands that okay you're talking about date and date are in sequence and all the months are mentioned there what I would do as a power pivot would to extract those quarter values as well so now this gives me a pretty significantly good idea of how my business is doing to in order to let's say if you are working in the company and you're analyzing this kind of data and you want to make sure that this data is clearly visible to the higher management or to the higher uh board members what you would do is you will add a pivot chart over here because they would not have the time to go through all the things uh all the raw data that would be present so in order to make it more simple you can use the line chart or you can use the bar chart line chart I believe uh would be better in case of showcasing the trends that are there now in this case 2007 2008 2009 you can clearly have what the sum of the gross profit has been right in this case if I have to change my analysis I can do that as well also I can make a dashboard out of it if I want so this was about how the power query is useful in automating the task that we have in hand and we necessarily do not have to use SQL to do that with power query you have streamlined data importing and cleaning now let's deal into data analytics in this section you will learn techniques to analyze and interpret data uncover insights and make informed decisions mastering these skills will enable you to transform raw data into actionable business intelligence Please Subscribe for more Excel videos hey guys welcome to today's session on data analytics using Excel now data is everywhere right from texting someone to buying something online or even posting something on social media every single thing involves creation of massive amounts of data every minute every industry be it Healthcare e-commerce technology or Finance has data at its Center of operations so now it becomes increasingly important to analyze this data so that we can find some meaningful Trends and opportunities for the future data analytics in the recent times has been the most sought after domain to make sure you are ahead of this competition we here at Great learning have brought this wonderful course so that you can begin your data analytics journey and land your dream job before moving on let's talk about our learning objectives so we'll start by discussing three different phases of a data analytics project and have a bird's eye view on each one of them then we'll discuss about the importance of data cleaning and the different ways in which we can clean data next we'll talk about the basic functions and formulas that are a must to know when it comes to data analysis furthermore we'll be talking about different ways in which we can sort and filter data in Excel next we'll learn about two different types of lookup functions vertical lookup and horizontal lookup functions then we'll know what is conditional formatting and also so how can we make color scales data bars and also highlight cells then we'll know about data validation how to create drop- down list and fixed length in a Cell then we'll know about what are pivot tables what is the importance of pivot tables and how can we use them to analyze data in the end we'll talk about data visualization using Excel we'll learn how to make different types of charts like charts scatter maps map charts Etc phases of a data analytics project now the journey of raw data from to meaningful data can be categorized into three phases the First Data cleaning second is data analysis and the third is data visualization now for data cleaning our first step would be to set up a problem statement that would be aimed in solving a specific business problem of your client now what is this problem statement for example a phone manufacturer would like to know how many customers actually bought their product while seeing an ad on YouTube now this would involve data the Second Step would be to perform an initial analysis on data so we need to check whether the data is complete consistent and accurate next we'll check for any mispellings and typos so the raw data can consist a lot of errors and mistakes that need to be removed as soon as possible so we would remove any misspellings typos or even duplicates row can be duplicated columns can get duplicated so we need to remove that as well last is checking for bias now what is a bias for example let's assume that your data set is drived from a survey conducted by a food company to know how good is their food so one of the questions in that column was how good was the food so this statement is inaccurate because it contains the word good now which is biased towards the positive side of the company and would not give them a clear picture of how their product is which is why it becomes essentially important to remove these kinds of bias as soon as we get the data next phase is data analysis so this is the core of your operations since your data is already cleaned and organized in this phase you will be playing around with data to find some meaningful insights so we'll start by deeply understanding the problem statement finding appropriate relationships between data sets and also adjusting and formatting data by using certain methods and techniques like lookup functions conditional formatting data validations and using pivot tables going forward we'll look each one of them in depth next phase is data visualization now Studies have shown that human beings are more more adaptive in understanding via visual representation now data visualization provides your analysis to be delivered to your clients and stakeholders in the most efficient manner so that they can make well informed and thoughtful decisions and also find some greater underlying Trends and opportunities for the future so in this phase we'll be learning about different types of charts like Scatter Plots tree charts map charts bar charts and many more next up is data cleaning let's assume that you are given a data analytics project now as a data analyst your first objective is to make sure your data set is out of any irregularities misspellings or typos consider it like a bowl of salad that you are preparing the ingredients would be salt pepper fresh vegetables and some sauces but now if the vegetables are not fresh your final product would be dull and unappetizing which you won't even eat which is why it becomes essentially important that your effort does not go in vain same is the case with the data analytics Journey if you do not incorporate data cleaning process then your final result would be hampered and even consisting of a lot of irregularities that your your stakeholders won't even like which is why data cleaning plays essential role to make sure your end results would be in line and uniform with the stakeholders expectations moving on we look at some of the ways in which we can clean data now Microsoft Excel has been the most dominant platform available in market for decades now and it still stands at number one so we'll do all our analysis henceforth in Excel so giving you a basic tool so this space is called a spreadsheet space then we have different tabs on the top right so these are known as ribbons so you can perform various activities choosing these so this is a data set verticals are called as columns horizontals are called as rows the first row is called a header or a column heading so this is the heading of different columns that we have here so now we have this raw data with us and we need to clean it so in order to do that we'll perform certain task so the first one being we need to check if there are any duplicates present so I'll look around it but I don't think so I see any but uh since it's very confusing this way we'll Excel already has one function called removing duplicates so we'll click on data here and then we'll click on remove duplicates so it will automatically select the entire data set and you can also check which all columns do you want in our case we'll Click on each one of them so that entire data set is selected then we'll click on okay so our naked eye could not see that there were duplicates but there were so there were two duplicates that got removed and there are 23 unique values left so we'll click on okay so the duplicates have already removed now this is a very small data set uh for understanding purposes but while you will analysis data you would not get this small data set ever so then certain things like remove duplicates becomes really important to know next up we'll just glance through it and we'll see if there are any irregularities present so I don't see any but here we go so in this 14th tab we see years of experience as 90 so now let's just think so 90 is not feasible value as year of experience so it would be a mistake on the client's part or from where you received the data set so you need to cross-examine it so whether it is 9 or 19 or something like that so you need to check with you should not make any changes to it you should ask your client first that what is the correct value and then make changes and also while you're doing these changes you should also maintain a log book or you should just create a duplicate of the raw data just in case if your data cleaning you miss certain values so that you can recover it later okay so another thing is I would arrange my salary employee column from let's say lowest to high highest so this is from lowest to highest this is sort function so lowest to highest I see that 1 lakh 1, 7002 here employee of the salary 99,000 is below it so I don't think so that this was correct so there's a gap in between so considering all other salaries they are in five digits one is in six digits so it can be a possibility that is this empty space is by mistakes we'll remove that empty space and click okay right then we'll again click on this and do sort so this gets sorted but again you need to check with your client that your the removed space was correct or not so these are certain things that can be an issue when you analyze it in the later stage so it becomes essentially important to correct everything in advance at the first phase itself next is data validation so data validation is a very interesting tool which allows you to control what can or cannot be entered in your spreadsheet so data validation is a very important tool when it comes to data handling in process like collecting information so let's say that you are rolling out a survey in which you want the user to fill only certain values in particular columns so data validation allows you to do that so let's see how do we manage data using data validation okay so here we have Microsoft Excel in this spreadsheet we have a table in which we need to fill certain columns so let's say the first one is name so in this column let's assume I only want the name should be a maximum of 20 characters long so for that condition I need to choose this range click on data data validation so here the tabs appears so in that settings tab we need to fill the criteria that needs to be filled for the particular range so I'll click on since we need characters so I'll choose text length minimum character one maximum character uh let's say 12 and say okay now let's check this out now if I type a Word let's say sing so it gets filled yes filled but if I try to fill something that is large let's say uh abake PA click on enter so now this says that the value does not match the data validation restrictions defined for this set so since this name is more than 12 characters long the input is invalid so now we'll retry and we'll put something a little less let's say shake enter now it satisfies the condition so in this way we can set certain conditions for this column similarly we can make a a drop- down list for gender column so in order to do that I'll again go to data validation column rest data validation now I will put allow as a list so now I need to select the source so I can just write here uh let's say male comma female and then press okay so I only selected one column I did not select the range but I could select the entire range and do that but in this case I can choose it's male or female so it will automatically come here in whatever we choose so there are only two things we can choose we can't write anything here that would be invalid so in this way we can choose list now comes uh we can put whole numbers or decimal numbers in marks criteria so we can set the range from what number to what number we want in that particular column so let's do that as well let's see data validation allow only whole number or decimal so since Marks can be in decimal like 10.5 12.5 50.5 so we'll choose decimal so in between let's say 0 to 100 and then we'll press okay so in this case let's see one enter 10 enter 55.5 enter everything is being taken by Excel but if we choose anything beyond our range let's say 115 so it gives me an error that this value does not match the data restrictions so we'll retry and put 1.5 it satisfies in this way we can do it but let's say if it's a survey and you're rolling out to the user how would the user know what values can we fill so the error me message should be customized right so in that case we can do a thing let's say data validation so this condition we said so we can put here error alert the title would be let's say invalid input input and uh error message would be please input values m between one between 0o and 100 and press okay now we go beyond our limit and press enter so it would give us the error message that we customized so our title was invalid input and the description was please input values between 0 and 100 so in this way the user can know that what values we need to feed right or we can do one one more thing so we can just do again data validation or we can just put an input message title uh let's say input input message only values between 0 and 100 and press okay so in this case if if I go here so it will give me the input message that only values between 0 and 100 are allowed so the user would beforehand know what values we need to feed before getting an error message so in this way we can do that and we can also set definitely we can set our data validation time also we can set here date on time so I'll set date between let's say date of birth between 01 10 1998 2 01 uh 12 2,000 and press okay so now they can do it in the same way 01 1 2 1999 it will be okay entered so in the same way we can do that now let's come to uh another most important concept so what if we need to create a drop- down list for for the subjects so the number subjects are these math English physics science physics Hindi and biology so if I need to make a list what was our method we used to write here maths comma English comma physics comma Hindi comma biology right so we'll click on okay so then it used to come this all the details that we can fil now let's say that the data set is quite big that you can't manually put every single name with comma in between if you want to make a list so in that case we can put the source as another data set link so let's see how to do that I'll just go back I'll click on this I'll select the range then I'll click on data validation I'll allow it as list the source now I can actually select the source so I'll click on this I'll select all these and press enter so now the range is selected and I'll click on okay so I will I will automatically get what that uh secondary link that is generated the data set from there will be retrieved here so let's say physics or we can set anything maths but it has a disadvantage so in that case you cannot delete your primary data set so let's see if uh we delete what happens let's say I delete physix okay and now we see the drop- down list so now maths English blank Hindi biology so it will remain blank over here so that is why this is one of the drawbacks in which we can't delete a primary data source next is sort and filter functions so what is sorting and filtering sorting is basically rearranging your data in the in your spreadsheet in the way you want so it can be ascending or descending you can also Define your own set of rules by using custom sort option now let's look at these examples in Microsoft Excel so here we have a spreadsheet of employee ID birth date age city name Department job title and gender so now we need to sort this data according to whatever we prefer so let's say I want the city name to be in ascending order so I'll click on any of the sales in this column and click go to the data ribbon and click on lowest to highest so it will give me the names in ascending order alphabetically now we'll if I want it to be in the reverse order reverse alphabetical order so I'll get that as well now if I want my city name to start from descending order from uh reverse alphabetically and also I want my birth date to start from the oldest to the newest no now how would we do that so we'll go to sort option here so it will open a tab so as we can see it automatically selects the entire spreadsheet now in this we can put a preference in the way we want so our first preference is to make sure the city name is in uh reverse alphabetical order so I'll put in sort by city name we can choose any other but we want city name sort on now you can choose cell color font color cell values Etc but here I want cell values so I'll click on here next I want what type of order I want so in this case I want reverse alphabetical so I'll go from Z to A right next I also want that my birth date should start from the oldest to the newest now I'll add a level and then my second preference would be birth date again we need to do it by cell values because I haven't created any other cell color or font color so I'll go with sell values next I'll do it newest to oldest or oldest to newest I want oldest to newest so I click on that and I'll press okay so now the city name are in Reverse alphabetical order as we can see and the birth date are from the oldest to the newest so we can see that here as well from starting from 1942 going ahead till the last section of 1994 so here this is the use of sort function now if I need to sort by different color sets so let's take an example of that as well so let's say I want my department to be color coded so I'll put produce as let's say which color click on hor ribbon I'll choose yellow then I want the meat stab to be green next I I'll use the bakery as red as red now I'll go again I'll click here now I'll go again to data and sort now I want I'll clear all of these now I'll add a level my first preference here would be to code it by Department name next I have color coded it so I'll click on sell color okay now Excel automatically detects what all color I have chosen so I have created three colors red green and yellow so it shows all three of them here now I can click on any one of them that I want to be at the top so I click on red on top on bottom I'll choose on top then click okay so the red color coded scheme gets on the top of my column next if I want to create I want green to come up next so what will I do I'll go again go to the sort tab now I'll create my second preference then by again Department name cell color this time I'll choose green on top okay so after red my second preference is green so and so on and so forth I can do multiple color sets and sort accordingly next we'll know about filter functions so filter functions basically Narrows down the way you want your specific data to be viewed so let's say in this example this is a movie set in which we have the title ear JRE language director and average ratings if it has got now I only want to see let's say English movies what all movies are in English so now sorting It Out by English would be one option but to make it more easier I'll click on this filter tab so it will automatically create a drop-down list in which we'll have different types of values that are stored in that particular column since we need only English we'll unselect everything else and click on English and hit okay now we'll only get the movies that are in English language and nothing else similarly if he wants any other language let's say French so we'll unclick English click on French and press okay now we'll only get French movies so this makes our job little bit easier on what we want to do with this data third and the final phase of your data analytics journey is data visualization now considering your data analysis is done and you are fit to represent your data to your internal and external stakeholders now internal stakeholders are someone it can be your boss your senior managers the top brass of your company and external stakeholders are your clients for whom this project has been allotted now considering the time constraints they might have in their day-to-day work they cannot go through your data sets one by one so in order to make them understand more quickly and in a more efficient manner we will use pictorial representations now these representations can be charts right it can be pie chart it can be bar chart line chart and different Scatter Plots and even some Advanced charts like map charts andent Tre charts so considering all these scenarios now it becomes your responsibility to make sure all your analysis is pictorical represented so let's move on to data visualization the first chart that we will be talking about is the pie chart now pie chart represents part to hold relationship so since it's a very basic type of chart in Excel we learn about it with an example so let's say you have three friends and all of them have apples right so you have the maximum amount of apples that is four the other three have three apples two apples and one apple respectively now in order to represent this data pictorially what you would do you would make a pie chart right so all your three friends and including you will be represented on this pie chart now since you have the maximum amount of apples that is four you would get the maximum chunk so this area would be yours with four apples your friends would get the one with three apples will get a slightly lesser chunk three apples the one with two apples will get slightly smaller slice and the one with one one apple will get the smallest slice so this is how a pie chart is represented based on the value in the data set the more the value the bigger the slice or the bigger the percentage in of that particular slice so it has three major components uh pie chart the one is area the second one is central angle and the third one is Arc Length so Excel does it automatically but let's take an examp let's just look at what this is so area would be this chunk here central angle is the angle it makes from the center and the arc length is a circumference of that particular slice so these are the three major components of a a pie chart but Excel does it itself so you just need to feed values and Excel will make a pie chart for you the second chart that is the most important and most widely used is a bar chart now bar chart uses bands of different heights to show different amounts so that they can be distinguished so as you can see one side the Y AIS here has uh values numerical values in it and x-axis has basically the categories in which it is divided so this is represented in x-axis you can do Vis Versa also then it will be a horizontal bar graph this is a vertical bar graph so you can compare different uh like months or different categories of food items or any number of things so this is generally used in areas where you want to find how much much difference is there between two categories so this is the most effective one there the third one is line chart now line chart represent shows the changes over a certain amount of time so it connects one section to the other section in case of bar grass there was no interconnection between these things but in a line CH you can see in January to February you have a line drawn over here so this gradient shows what is the difference between the two months right so this is how line chart operates so each categories are along horizontal and the value is over vertical so this is the most favored method to do because otherwise if you do vice versa in line chart it would not make sense right so it is also preferred when one parameter is non-numeric which is the horizontal one and the other one is numeric the vertical one right now let's look at these examples in Excel now let's look at the example of these charts using these tables so this table I used it during the hookup session so I'll use it again so we here we have office supplies product ID quantity and price mentioned so in order to make pi and bar charts let's select the range of price and office supplies then click on insert and then uh as you can see under charts here we have different types of chart you have PIV with charts we have map charts and all these types of charts that are represented here so so I'll choose pie chart so I'll click on pie chart then they it gives me an option to choose either 2D Pi 3D Pi or dognut so in this one I'll choose 2D pie chart so I'll click on that now it makes it's a selects it highlights me the different range that we selected and automatically mentions it over here now in this case I can cannot see the percentage in which it is divided right in in order to do that I'll go on chart Styles right I'll click on the downward arrow and choose whichever one I want okay so I see that this one here is clear okay so I'll choose this one right so now I get percentage I get the different categories in which it is divided and I get get the name now as we can see this is not the name of the chart right so I can double click and change to Pi chart or something or pie chart prices something like that so I can change it to whatever I want and uh so this is how it is represented now coming on to next chart that is bar chart so I'll just delete it I'll click again on this the same thing I'll select both the ranges that we selected earlier then click on insert and here the first section is insert bar chart so I'll click on the downward Arrow now again it gives me different options I can get 3d effect I can get horizontal this is what I mentioned you can use horizontal bar charts as well or vertical bar charts so 3D horizontal 3D vertical I think vertical looks much more clear to understand so I'll use 2D I click here so now I again get this chart and since it is not me mentioned any percentage or it's quite clear 500 here but if I want to change certain things I can again go to chart Styles I can change how visually they appear and different see this is the quantities are the price is mentioned in the bar itself so I can choose whatever this one looks pretty good I'll choose this one so now I can see that it is clearly mentioned everything I'll change it to probably price bar chart something like this but as you can see there are no mentions of uh what is in y axis and what is in x-axis so it is not mentioned okay I I want to put something here x's on on the axis I want to put a category or something like that so how to do that so we click on on the left side here add chart elements so I'll click on that X is titles so now I can have a horizontal axis title I can have a vertical axis title title right so we have all learned in our Junior classes that the access titles are an important ones so I'll write here price I'll write here category so in this way we can make bar charts so in order to make a pivot chart let's take an example so for pivit charts this is a data set that we analyzed before so I'll use the same data set so we made this kind of pivot table over here right so I'll use this in to make a a line chart and this is also give you an example of what pivot charts are so making a chart from pivot table is called a pivot chart so let's see how this works so it's basically basic charts but in pivot table I want different uh months to be represented via line chart in a pivot table so I'll go to again insert then so since we are already in pivot chart we would not click on pivot chart but we can use different uh charting patterns so since I want line chart I'll use uh 2D 3D area does not look very distinctive so I'll use 2D line okay so here also as you can see if I hover over this one it says is sted line so it shows me the sum of sales and uh the months right if I click on this it also shows me the sum of sales but with a different uh y- axis so the values are different the various bars in which it divides is different so in case of percentage also I can get so in this case you have different percentages mentioned so I I can click on any one of them I'll click on this one now let's say I only selected this this range but it gave me all range so in pivot chart it gives you all range you can actually filter it from this filter option here so if I unclick uncheck all and I only click motorcycles and click okay so it will only give me the data of motorcycles in every month and its values right and here also the pivot chart also only uh summarizes it into motorcycles and months so this is very good here in which we can do it you can actually CH the month also if you want to see a particular month we can do that as well so this is how a line chart and a pivot chart works next we'll look into two of the more advanced charts that are not commonly used in our analysis but are a must to know so the first one is tree chart or tree map as we say so it provides a hierarchial view of data so just as the example shown in the picture so we can see this is the example of tree map and here we have different countries and some value in which they are arranged okay this value I would know because it is the data set which I am using but this tree maps are not used to depict the exact data that is in the data set so if you just want to know okay which proportion is maximum to which within the hierarchy so this would tell us that so the first one uh is India so so India has the maximum amount of value in a particular cell that that's why it has the maximum maximum area second is Saudi Arabia then Russia Germany Australia China and America and Spain so the ones with bigger values are towards the left side of the map and the ones with are less value are towards the right side so that is why it gives a good comparing proportions within the hierarchy the second chart is called map chart so it is used to navigate values and shows categories across different geographical regions so in this case uh we will use only the names of countries to represent any data set so the countries will be highlighted according to the values so this is most commonly used by uh let's say World Bank or uh United Nations categories to represent datas across the globe right so and also some of the companies who are involved in logistical supplies Imports exports use these kinds of maps to represent data let's look at the examples in Excel so here we have a spreadsheet sheet in which there are different the countries and GDP per capita are mentioned here so in order to create tree Maps or map chart let's uh select the columns and uh then click again on insert and here we have the first one here it represents hierarchial chart so this is a tree map so we'll click on tree map now it automatically uh extracts the values from the range we have selected and makes a tree map out of it now in this case uh we can change the the layout according to our wish so I can choose any one of the ones in chart Styles so as we can see the ones with the greater value Qatar and us are towards the left side while the ones which are quite small are towards the right side right okay so I choose this okay I can make changes from this plus sign here if I want to see something if I don't want to see something or or if I want to change the layout how it is styling here also you can change it and color also if you want the scales to be of a different color multicolor how do you want to change it it's upon you so you can do all these things using this now coming to the second type of chart that is the map chart so we'll undo whatever we did okay again we'll select the data set then we'll click on insert and uh towards the middle of the charts we have a thing called Maps so if I click on this fi map so it will generate the countries and their values according to how much it is there so we can see in this case all the countries which are mentioned are are in the map right so we can change the layout again from charting Styles we can create whatever uh we desire and also we can change the way it is uh there so wait I'll just if you double click on the chart you will get format plot area so you can actually change the fill how it is filled you can put a solid gradient fill or pattern fill so in case I want the countries which are not selected here I can actually change make them the way uh I want so okay okay it is not represented in this but in this case I can do it pattern fill yeah I can change the way it is so the ones which are not selected will get uh changed so the ones so it is clear for us to know which one of them is selected right this was all about map charts having explore data analytics to derive insights let's now focus on graphs and charts these visual tools will help you represent data finding clearly and effectively by mastering them you will be able to communicate your data stories compellingly and making your analysis more impactful and understandable as you can see the user has already clicked on the insert tab which opens up a ribbon of options the rectangular box highlights the different chart options that are available in Microsoft Excel so the first option that is provided is line and column charts coming to the next one it is line and area charts the third one it is pie charts or you can also say donut charts and then we have this option that is used for combo charts that is for example line versus column chart Etc now we have a separate section that is dealing with pivot chart and we have an exclusively different video for the same now let's see how we'll be able to implement insert slicer I told you before that insert slicer is nothing but it's actually a dynamic filter which we'll be able to bring in right next to the pivot table so that we'll be able to dynamically change or filter out the datas which need to be depicted in the pivot table let's see how that can be done for implementing insert slicer we need to go to the ribbon analyze and then here we are having the tool insert slicer so after selecting the insert slicer you will be given the option to select which element you should be selecting in order to have the slicer based on that so I'm selecting price so price is what I need to filter using insert slicer so I have choosen price and then select okay so now I have got the slicer so this is what a slicer looks like and you know it can be moved around and all the size can be adjusted here by doing this or you also have a specific options to modify their sizes for the buttons here and for the table size here so for the buttons can be arranged so if you want to increase the number of columns that can be done so I need have three columns so I gave three columns and height of the buttons need to be reduced and also the width of the buttons also need to be reduced so I have reduced that as well and then coming to the table table height need not be this much so that can also be reduced here yes I'm reducing that and uh this looks perfect so we have got this price slicer here so if you want to implement so if you want to filter out the data on the pivot table based on the price you can select those so now all the datas in the PIV table are based on the price 55 so 55 is nothing but books and and books have been bought by these three persons okay and what if you need to select more than one you can just press control and select any other tools as well and you'll be able to add those elements to the P table that is what a slicer does and you can have more than one slicer as well so if you need to have more than one slicer you can simply go to the analyze ribbon and then insert slicer and then here you'll be able to insert any of the slices which you would like to add so that's it on slic now let's let's move on and see what style tool provides us so row headers column headers banded rows banded columns these are the things which is being provided in the style tool okay so what if I untick row headers all the row headers will not be highlighted so when I'm taking it all the row headers are highlight column headers when I'm unticking column headers sum of price column labels online offline I mean store Grand to all those things are not being highlighted because that box is untick and if you want to make that highlighted you know just give a tick and all those things will be highlighted and banded rows is nothing but giving alternate band to all the rows so that is what is being implemented by banded rows and banded column is the same thing which is being implemented to alternate columns the final tool which we'll be talking here in this video is the refresh what if you have made any change the data set which You' have already selected what if you added any new product to the list or what if you need to add any new customers to the list you have already made and so any change to those data sets which have already selected and you need those changes to be imparted in the pivot table as well those things can be done only after using the refresh tool and you can Avail this tool of refresh in the analyze ribbon and you can have the option right here so after clicking here any changes which is being imparted to the data set which have already selected all those things will be updated in the pivot table as well so what I'm trying to say is you know this is the data set which we have already selected what if you need to add any new customers instead of Kiran you need to make it Kumar so any such changes if you have made in the data set all those things let it be not just a customer name it can be any product if you're adding any new product or if you have sold any other product apart from the data which have entered here so if you need to add that as well all those things can be done all the edits and modifications can be done and after doing all those modifications if you need that data to be reflected in the pivot table you need to refresh the data now let's see what are the various tools available to us in Excel so that we'll be able to create pivot table in a more convenient and ideal manner some of the tools mentioned here are layout style insert slicer data source and refresh coming to layout tool layout tool helps you to change the layout of the table without affecting or without imparting any change in the data which is present within the table you will be able to change sub total grand total any other layouts any other rows which is present within the table in style tool you have the option to modify the headers and also you'll be able to implement banded rows or column insert slicer is actually one kind of filter it is more convenient and ideal for us to use insert Slicer in order to easily change the input and get the updated output very easily data source now you know that before creating pivot table we need to be selecting the data source from which it need to be considering the data to the pivot table right so what if you need to change the data source which you have already selected what if you updated the data source and you need to completely change to the completely new data source so that is made possible in data source so you will be able to change the data source which you have initially selected while creating a pivot table refresh tool enables you to impart any changes which you have imparted to the data source while creating a pivot table you have already selected the data source and what if you have added anything or anything new let it be any new product or let it be any new customer or anything of that sort if you have imported any modifications or any change to the data source and you need that changes to be reflected on the pivot table then you need to apply the tool refresh in order to get that reflected in the pivot table now let's see how we'll be able to implement these various tools in the platform pivot table and its importance pivot table is actually a small table consisting of few rows and columns which provides the complete idea on what the data sheet is trying to convey we need not go to the data sheet and give a study on it to understand we could just create a pivot table of our requirement to understand what the data sheet is trying to convey thereby it'll be more easy and convenient for us to know what is being conveyed to us in the data sheet we'll also be able to interpret the data based on several factors using pivot table consider you are given a data sheet consisting of a lot of details like ordering date manufacturing date product ID product name quantity customer name and all those datas are being depicted in the data sheet which is given to to you from the sheet what if you asked to get the details of the customers who have bought a particular item so usually what you should be doing is you should go to the particular data sheet and then try to find out who all has bought that particular item so sell by sell you go and check who all has bought that particular item right so instead of doing that that is a tiring process right so you need to go through complete cells and identify who has bought the particular item so instead of doing that let us use use pivot table so just by creating a pivot table you will be automatically getting the details of the customers who has bought that particular item isn't that more easy and convenient for you rather than going to the data sheet and identifying from each of the cells in the sheet so that's all about a pivot table and that is the main reason why why is it important for you to use a pivot table rather than moving to the data Sheen and trying to find out the details from that sheet now now let's move on to the platform Microsoft Excel and learn how we can create a pivot table there now let's learn how to create pivot table we are already in the platform Excel and using this data which is available to us let's try how to create pivot table so that it'll be easy and convenient for us to interpret the data which is being conveyed to us in this particular sheet so in order to create pivot table it will be made available in the insert ribbon pivot table just by clicking on on pivot table we'll be having a pop-up box coming and it will be asking for the table or range of selection from which the data should be segregated right so the selection is actually made automatically using the software and that is the right selection made so I'm not doing anything there and after that it also asks where you want the pivot table to be on a new worksheet or in the same existing worksheet so I need to have it on a completely new worksheet so I'm going forward with new worksheet and clicking okay see now I have been transferred to a completely new worksheet where I'll be building my pivot table based on the data which was available to us in that particular sheet so it is made available in sheet one and in orders we'll be having all the datas which was imported so I am making it like this and here if you look through the data we'll be understanding that in the data they convey the order placing date the customers who have placed the orders product ID units product name price and locations So based on these informations we need to be creating the pivot table and in the pivot table section we are already having those elements here Order placing date customer name product ID units product name price location all those things are made available in the pivot table options as well right so we'll be able to start creating a pivot table just by selecting any element and dragging and dropping into to any of these columns okay so let's try so I'm dragging price into the value column so I'll be getting the sum of price in the pivot table but actually this doesn't make much sense right so let's try adding more things to the pivot table so let me try adding customer name so towards each of the rows I'm adding customers name so I'll be getting the pivot table with the respective customers and the amount of purchase which they have done in that particular store okay so I've have already got that so what if let just try moving this customer to columns see all those things will be appearing in column so all those customers and the price which they have spent on the store will be coming on each of the columns but this doesn't find so attractive or convenient for us to read through so I'm making it reverse so I'm getting it back to the rows so all the customers are coming in different rows we can increase the understanding of the data just by increasing the column so that we'll be having more data to us and more convenient for us to understand the data so I'm adding location to the columns so online and official store so both these are coming in separate columns now we are having a detailed breakup on the purchase so we not only getting the prices or the amount which is being spent by each of the customers we also getting idea on where they have spent have they spent through online mode or offline mode that idea or that information is being passed on very easily using pivot table now the only one remaining is Filters we haven't tried filters but everything else let's see what filters do let's try adding product name name to filters nothing has happened to the table apart from a new cell being added here which is product name right so all the data in the table Remains the Same at the same time one cell is being added let's say you want to filter out the data which is being depicted in the table so what if you don't want to see all the datas or all the purchases made by each of the customers you just want to know who has bought a particular item if that need to be made possible we have to use the option filters and in filters we have already added product name right so in product name we should be choosing which particular product you want to see so only those customers who have purchased that particular product will be depicted in the table so I've already dropped down product name to filters and it is already being depicted in the first cell so go to the drop- down arrow here and then select the product which you want to be depicted all the others will be eliminated and only the customers who have bought that particular item will be depicted in the pivot table so let's say I am selecting stapler so after selecting stapler and clicking okay all these datas will be modified in such a manner that only the persons or only the customers who have ordered or who have purchased stapler will be depicted let's see so I have selected stapler and clicking okay see all those customers I'll zoom in a bit see all those customers who have purchased stapler are being depicted now all other customers are not we'll be able to filter out all those things using filter option which is made available in pivot table now let's go back to the initial condition which showed all the customers and see in how many ways we'll be able to sort the data in the pivot table or in how many ways we'll be able to summarize the data in the pivot table so I'm going to the normal position let me select all and then okay so I have selected all and I have got the all the details of the purchases which is being made by each of the customers Hello friends welcome to Great learning in this video we'll be spending some time together on learning pivot table in the platform Microsoft Excel let's move on and see what are the topics which is being covered in the video what is pivot table and why is it important I know there are a lot of people who use the software Microsoft Excel and still don't know what pivot table is for them we'll be explaining briefly what pivot table is and what is its importance we'll be talking about its significance and advantage of using a pivot table in the initial part of the video itself after having an idea on pivot table we'll be directly moving on to the platform Microsoft Excel and learn how to create a pivot table after understanding how to create a pivot table there are a lot of ways in which we'll be able to implement pivot table in the platform Microsoft Excel so we'll be talking on the ways or method in which we'll be able to sort or summarize data from a data sheet using pivot table after understanding all all these things towards the end of the video we'll also be talking about the various tools which Microsoft Excel provides us for implementing pivot table so let's get started with the video with knowing more about pivot table so the next tool available to us is data source so we know that while creating a pivot table we need to select the data source right so what if you need to change the data source in between so that can be made possible using change data source tool in Excel we'll be able to change data source by going to the analyze ribbon and then we'll be having the option called change data source here we'll be able to change the data source and and see this is the initial data source which we had selected so if you want to change it you can simply change the address of the data sets which you want to implement so these are till row 27 so yeah these are till row 27 so what if I need till only row 21 so what I can do is I can go here and say 21 so I'll be having a new data set and that will be only till row 21 you can also change the data source just by selecting it as well so now see we have given 21 and it is only till 21 here you can also choose the data set by clicking over here and making your necessary selections so even if you have selected the data set once you can always change the data sets whenever you need after mastering pivote tables to summarize and analyze data let's move on to budgeting in this section you will learn how to create and manage budgets using Excel helping you track expenses plan for future financial goals and make informed financial decisions effective budgeting is crucial for both personal and business finance Please Subscribe for more financial Excel videos Hello Learners welcome to the course on personal budgeting with Microsoft Excel so what is the importance of budget for our individual life okay and how to create a budget using a Microsoft Excel that we are going to learn in this course okay so let's begin with the agenda so in this session we will discuss the following topics first we are going to discuss about introduction to budgeting okay next we are going to discuss about the financial goals that is how the goals we need to create or how we need to put or how we need to maintain a goal in our individual life okay next what is the difference between savings versus Investments so how will be different from saving and the Investments that we are going to discuss next income and expenses how the income and expenses is differ from each other that we are going to discuss and what are the various kinds of income and expenses will be there for as an individual that we are going to discuss next we are going to discuss about emergency fund and savings so what is the meaning of e emergency fund and what is the meaning of savings and what is the difference between this emergency fund and the saving okay that we are going to discuss next we are going to discuss about the budget categories okay what are the different or various categories of budget is there that we are going to discuss and the various methods of budget also we are going to look and then last and the final we are going to look at the creating a budgets using the Excel that is in how to create a budget or how to maintain our day-to-day activities or transaction recording everything we are going to do right so that we are going to learn in the last okay first we go to budgeting okay before that first what do you mean by budget so the budget is nothing but it is a estimation of income and expenses or cash inflow and cash outflow as an individual or on a company or join stock company government tourament or any other things okay so budget is nothing but the estimated cash inflow and outflow for the coming year or for the next year because already this year is started right so if you plan also it will not work out because already five 6 months are gone so we need to create or we need to Freshly create the budget for the next year or upcoming years or upcoming months okay we need to create for the future that is called budget Okay so so for example what are the cash inflows we are going to receive in this next month okay what are the set of expenses are there okay that we are going to forecast and we are going to maintain a list of all the inflow and outflows in the our personal life or as an organization everything we are going to forecast that is called budget next we'll see what do you mean by this budgeting so the budgeting is the process of creating a detailed plan that outlines and individuals or households expected income and expenses over a specific period usually on a monthly basis if for example if company is going to prepare a budget or the government is going to create a budget then what they are going to do they are going to create usually one year budget correct that is 12 months but if it comes to personal life or an individual okay so individuals they are going to create usually for monthly basis only because our regular income is also we are going to receive monthly wise correct so that is the reason personally we are going to preferably we are going to go for this monthly basis of budget because next month what is the salary or what is the income we are going to receive and what are the expenses we have for the next month okay all those things we are going to create in this budget so usually for the personal or an IND usual we are going to maintain a monthly wise or monthly basis of budgeting next it involves estimating the amount of money that will be earned and spent and then allocating those funds to various categories such as housing Transportation groceries savings and entertainment so whatever we are going to earn so we have some categories of expenses correct so housing is nothing but it may be rent we need to pay water bill electricity bill other charges will be there correct or if you have the H house then we need to pay Emi whatever the water bill electric bill that we need to pay correct so for housing that category what is the expenses or how much is needed for that particular category that we are going to separate the amount and next if you go for transportation so day-to-day wise we need to use the transportation whether it may be for going to office or going to outside or whatever we are going for outside correct so for that transportation is required so if you have the own vehicle then the fuel charges the maintenance or it may be driver charges whatever the other necessary expenses will be there for the transportation if you don't have the own Vehicles then we have to go for a rental or rented one or for cabs correct for that we need to spend money all those things will be comes under the transportation groceries that is your food and groceries items okay that vegetable fruits or whatever the food grains for everything how much is required for particular month and next savings this is very very important for an individual correct so whatever the amount you are going to earn in that some portion we need to keep it as a savings because after retirement or after the future whatever the unexpected things will be happening correct for that we require money for that purpose we need to maintain this savings and entertainment entertainment is nothing but going for a shopping or it may be for movie other things will be there correct so for that also we need to keep some amount of allocation or some reserves we need to keep for that particular Entertainment also so these are the things or these are the categories are there for that we need to allocate the whatever the amount we are going to receive or whatever the income we are going to generate for that we need to allocate the money that is called budgeting the primary goal of budgeting is to ensure that financial resources are managed wisely and alignment with On's financial goals and priorities so according to our financial priorities or whatever the goals we have for example in the next month we have entertainment expenses is also there that is that category is also there and Sons or daughters educational fees will be there so which is the priority for us entertain m is the priority or education is the priority so according to that priority wise we need to allocate the money okay so whatever the amount or whatever the income you are going to get so as per the whatever our financial goals will be there and through the priorities we need to check what is the priority which amount is required on that particular month or necessary to spend that money okay all those things will be decided and we are going to allocate the money in this budgeting okay overall the budgeting is nothing but whatever the income and expenses are there okay so that we are going to allocate all the expenses money and we are going to prioritize whatever is required for the next or the future thing okay that is called budgeting hope it is clear so we'll go to next topic that is importance of budgeting what is the importance or why we need to maintain this budgeting or why we require this budgeting as an personal or as an individual why we require this okay so first One Financial awareness what do you mean by this financial awareness for example that X person is going to earn 50,000 Rupees per month and he is not going to keep any budget or he's not going to record whatever the amount he's going to spend simply is going to spend the money and the next month also is going to get the same amount as an income correct so on that time he not knowing how how much he has spent in the last month how much is remaining in his account or in his bank account okay and how much is need to spend for this particular month okay that awareness will not be there if you have a proper budget or if you are maintaining a proper budget then you have a clear idea of how much we have spent last month and how much we are expecting to spend next month or how much money is required to spend for the next month for each categories it may be for house Transportation entertainment educational all those things okay that is called Financial awareness this is very very important for an individual so if you are earning a money that is your hard earning money correct so if you want to spend that particular one rupee also you need to have a idea okay for which we need to spend that money and which is the priority for that particular month okay all those things we need to check that is called Financial awareness this budget will create a financial awareness for an individual next one control over finances finances is nothing but debt if for example that X person whatever earn the 50,000 Rupees that amount already is spent before 15th of that particular month so remaining 15 days are there correct so for that also he required money for food or Transportation or for any other things correct he required money on that time what he's going to do now easily he can use credit card or he can go for personal loan correct so that increases the debt that he needs to repay correct that is not his own money it is owes from the others that is from the banker or financial institution he borrowed that money but he need to repay for the next month or next coming months he need to repay correct so if you have the proper budgeting then no need to go for finance or no no need to go for any borrowings correct because you have a fair idea of how much is spent and how much is remaining and how much is required for the next 15 days or next week correct so that is the reason this budgeting is very very important next Point goal achievement if for example I have a goal of purchasing one land or one site or take an example of purchasing of land and building in next 10 years so for that next 10 years if I want to purchase that land and building what I need to do right now I need to plan in 10 years if I want to purchase that land and building that value is XYZ value is there so for that reaching the XY Z value in 10 years from day one or from the month one how much I need to save to purchase that after 10 year correct that is very very important so we need to plan today and we can purchase after 10 years because every month we need to save some amount or whatever the forecasted money is there to purchase that particular land and building we need to save every month correct so if you do that budgeting or if you do that planning then only we can go for this achieving or we can easily reach our goals correct so for that purpose also this budgeting is very very important next when it comes to debt prevention and debt reduction we already discuss in the second point that is control over finances correct so here also the debt prevention debt is nothing but the borrowing money or borrowed money or loan okay so that money will be prevention because instead of going simply we are going for loan or using the credit card or going for a personal loan okay that will simply increases our debt value or whatever our borrowing capacity it will increase and we need to repay in the next coming months correct so instead of going for the debt we have to plan accurately and we need to smoothly we can run our life or we can balance our cash inflow and cash outflow in our day-to-day life and reduction also if you prevent the debt easily it will be reduction also it will be reduced why because we are not going for any loans whether it maybe a credit card personal loan or any other loan then your debt will be reduced correct that is also one of the importance of this budgeting next Point emergency preparedness nowadays future or tomorrow is unexpected correct that we already learned from the covid-19 correct so whatever the diseases or whatever the natural disasters or natural diseases whatever it may be happened for the future for that we required money because in the lockdown also if you have the savings or if you have the emergency fund then you can utilize that money for your day-to-day operations or day-to-day activities otherwise you don't have money correct like that whatever the unexpected things will happen in the future for that we requir emergency fund that emergency fund not only for the whatever the unexpected things for example if I want to purchase new bike or if I want to purchase a cloths or whatever the other purpose also we can use this money okay so that is the reason we need to save this money or we need to keep the reserves for this emergency fund also so whatever the future unexpected things will happen we are ready financially we are ready for tackling that whatever the expenses will come or whatever the risk will come in the future that is the one more importance of budgeting next improved savings so how this budgeting will help us to or improve our savings because we have the estimation how much is required for household how much is for grasser entertainment education everything correct so the remaining money if for example your income is 50,000 and all your expenses is 40,000 rupees so remaining 10,000 is there right that is your savings that you need to have a habit of keeping that money separately that saving a money you can keep it in your home also for any emergency purposes okay that is called improves the savings Habit in the generally with the individuals next reduced Financial stress the financial stress is like for example any unexpected thing happen Okay it may be for for example car insurance car insurance is going to expire for the next month on that time we require money to renew that car insurance correct it may be 15,000 20 30 whatever the money okay so for that whatever the next month salary is there in that we need to manage that next month expenses also so on that time whatever the money we have saved that in emergency funds or it may be in savings okay earlier we have saved right that money we can use it for this other expenses so that will reduce our financial stress otherwise we are going to to take so much of stress so next month I'm going to get 50,000 so my savings is 10,000 expenses is 40,000 and this additionally we have car insurance of 15,000 rupees so where I need to get other 5,000 rupees that Financial stress will comes to our Mind Correct so on that scenario if you have already saved the money as in the form of emergency funds or Savings in the past years or past months okay then you can utilize that money for the future whatever the expected or unexpected expenses are there so on that time it will easily reduce your financial stress that is the one more importance of this budgeting next informed decision making informed decision making already given an example of purchasing land and building after 10 years so if you want to purchase after 10 years means the value you need to or whatever the money you need to save every month from now onwards correct so that is is called informed decision making we have a formal decision making things we know what we need to purchase after 10 years so for that I'm going to save this much of money every month so after 10 years without going for any loan or debt we are easily purchasing that particular asset that is called this informed decision making so these are the few importance of budgeting like Financial awareness control over finances goal achievement debt prevention and reduction emergency preparedness improved savings reduce Financial stress informed decision making next topic is budget categories okay so budget categories are very very important because what are the different or various categories we have and we need to allocate the fund accordingly correct so first we'll look at the what are the different budget categories are aail aable so first one housing so this housing is nothing but the for example if you are there in a rented house so you need to pay rent you need to pay electricity water charges or any other expenses related to the house okay that is there or if you are there in the own house so if you are taking a home loan then you need to pay the Emi electricity and water charges that is remain same correct like that we have one categories called housing second one is transportation so transportation is nothing but like for our own Vehicles it may be Insurance fuel maintenance any other charges or if you are going for a cab or if you are taking a rented car or rented Vehicles then it may be the cab charges or for a driver charges or whatever the expenses that you need to Bear utilities utilities comes under your all your food and groceries okay that is there in the utilities and next comes to healthcare and education for our own life or for our spouse or children and education of Childrens in sometime our educational also so if you are going to do the higher studies then it is required for us also then next comes to debt payment category so debt payment is nothing but the amount okay we need to repay whatever money we have borrowed and we need to repay that money that is what category and the last category is savings and Investments so we need to save the money for the purpose of investing in the other one okay that is called savings and investment categories next allocating funds so we have already look at the what are the different categories are available correct so next we need to allocate the fund according to the categories so allocating funds to various budget categories involves determining how much of your income you will assign to each category so we have saw almost 6 to 7 categories correct so in that six to seven categories how much money we need to assign or we need to allocate allocating is also place a very crucial role in the budgeting this requires prioritization based on your financial goals needs and the lifestyle of your personal life okay so the priority like what which one is the very priority or which one is the need and which one is the want for us our personal life all those things categories we need to remind in our mind or remember in our mind and then we need to prioritize the categories allocation of funds see here also in the image it is shown as like in transportation or it may be in the healthcare sector for housing for entertainment and savings also Okay so that all these things we need to keep it in mind and then we need to allocate the funds next the various methods or budgeting methods we have namely 50 302 rule zero based budgeting envelope system percentage based budgeting and priority based budgeting so these are the five major categories of or major methods of budgeting are there so we'll see first one by one the first one is 50 302 rule what do you mean by this 50 30 20 so if you are for example if you are earning 100 rupees so in this 100 rupees like 50 rupees one category 30 rupees one category and 20 rups one category so in the 50 category we are going to spend that for household food and entertainment for these three categories we are going to spend this 50 rupees okay next with when it comes to 30 rupees so in this 30 rupees we are going to for our Healthcare or for our day-to-day expenses and for our any emergency funds or emergency requirements for that we are going to use this 30 and next 20 so in this category we are going to use the 20% or 20 amount in the like debt repayment and the savings purpose we are going to use this 20 so this 50 is household this 30 is Healthcare and other educational and other utilities and this 20 rupees for debt repayment and the savings so this is called 50 3020 rule so next point is zero based budgeting what do you mean by this zero based budgeting so if for example in this take this example only in 100 rupees we have used uh 50 rupees for household 30 rupes for emergency that is medical education and other things in 20 rupees we have used 10 Rupees for debt repayment and remaining 10 Rupees is our savings correct so in the next month once we are starting the next month what we need to do that 10 Rupees also we are not going to consider so freshly we are going to start for the next month that is from zero or from scratch we are going to start so again that 10 Rupees with that savings amount for the last month we will keep keep it separately and we are going to start with the whatever the income we have earned in that particular month okay again we are going to start with this same 100 rupees for the next month so this 10 rupe savings for the last month that is we are going to keep a separate in account okay and again we are going to start spending that 100 rupees into 50 rupees 30 rupees 20 rupees in 20 again we are going to save with the 10 rupes okay but that initially we need to start with the zero only so whatever the amount spent or whatever the amount saved in the last month budget that is entirely different again we need to start this month freshly that is from the zero that is called zero based budgeting next envelope system so for example in envelope system house educational and medical transportation and other utilities okay these are the four categories these four categories just I have taken four categories we have other few categories also these four categories only we call it as envelopes these different envelopes will be there so we are going to allocate the fund or an individual is going to allocate the fund as per this envelope if for example for this household we are going to allocate 30 rupees so after this 30 rupees completed if for example I have spent all 30 rupees after after that I'm not going to allocate any money if you are going to save in that 30 rupees only yes you can save otherwise if you are going to spend all 30 rupees that is also fine after 30 rupees we are not going to give any extra one rupe also for that particular envelope so in general term we call it as categories but in this method envelope system each category we call it as envelopes for that envelope we are going to put some cash usually this envelope system is also called as cash basis method if for example if I earn 100 rupees means that 100 rupees I'm going to withdraw from the bank and I'm going to get the cash to the home for all this envelope I'm going to keep the money 30 rupees for household 20 rupes for education or medical 10 Rupees for transportation and 10 Rupees for other expenses all these envelopes we are going to put the cash so whenever the need arises then you need to take the money from the envelope you need to utilize that money if you have any additional or excess of money is there then you need to keep it in that same envelope so once that money is empty or in that envelope is empty then there is no amount we are going to allocate for that particular envelope that is called envelope system next percentage based budgeting so here we have taken as example of 50 302 but in the percentage basis we are going to use it as 50 % 30% 20% so if you are earning 10 rupees or if you are earning 100 rupees in that 50% for household 30% for medical and other expenses and 20% for savings and the debt repayment so percentage we are going to allocate the fund that is called percentage based budgeting and the last one priority based budgeting so as for the priority which is the need and which is the want for that personal life so which is very necessary for that particular person so based on that priority they are going to allocate the funds for the individual in their budgeting system these are the five major methods of budgeting are available for the personal budgeting first one 50 3020 rule second one zero based budgeting third one envelop system and the fourth one percentage based budgeting and the last one is priority based budgeting these are the few methods of budgeting are available next the types of financial goals so usually we have two kinds or two different types of financial goals we have correct that is first one your were short-term Financial goal and second one is long-term Financial goal so short-term Financial goal is nothing but it is less than one year if for example this year I am planning to purchase one refrigerator or take an example of washing machine I need to purchase one washing machine for my home that is for my personal use correct so that is called short-term financial goals so if you want to purchase one washing machine so how much is required so it maybe 40,000 50 60,000 correct so it is less than 1 lakh rupees for example so for that 1 lakh we call it as short-term goal if for example take an example of long-term goal if I want to purchase a land and building for that purchasing land and building we requires near to crores only correct so for that one CR okay that is called long-term goal and within one year or two year we can't save that money or we can't adjust that money so we required huge time it's maybe 6 years 10 years 15 years or 20 years correct so that is called long-term financial goals not only this example for example in the short term we can take an example of purchasing a vehicle two wheeler or you can purchase the household equipments it may be new Furnitures or it may be any other new things okay that is within one year we can arrange that money and we can purchase or we have have the estimation of purchasing within an year that is called shortterm longterm is nothing but like purchasing land and building or a site or commercial real estate property or it may be a Farmland okay or car also sometime okay purchasing car also comes under a long-term Financial goal so what is the significance or importance of short-term and long-term Financial goal so first we will look at the short-term Financial goal okay so in short-term financial goals I already explained you within one year or less than one year the goal should be reached or it may be completed okay so first one this is immediate gratification so immediate gratification is nothing but we can do it easily or we can easily do it in a short notice period okay if for example if I want to purchase a new bike the value of bike is 1 lakh 110,000 rupees so if every month you are going to save 10,000 means within 11 months only you can purchase that bike that is 1 lak 10,000 you can easily pay correct so no need to wait for complete one year within the shortterm period or within that very short time only we can purchase or we can complete that goal or we can easily reach out that goal that is called immediate gratification and next when it comes to Second importance that is finance Financial discipline so if you have the financial discipline Financial discipline is nothing but we need to plan for that particular short period also okay every month I need to save 10,000 to purchase that particular bike okay within one year I need to purchase that or how can I purchase so how much I need to save so that discipline will be there and whatever regular expenses will be there it may be hold household or it may be grasser food everything we need to complish and then we need to save that money to purchase that particular vehicle next emergency preparedness So within short period also some emergencies will be there I have already taken the example of car insurance we have like we are not remember next month I need to pay the the car insurance that car insurance if it is expires then we have other procedure only correct to renew that insurance policy we need to take car to that insurance party or insurance agency they are going to take photo of all the four angles okay they are going to take a photo and then they are going to do the idv value check or ad estimation all those procedure will be there so to avoid all those things what we need to do we need to take insurance before expiring that whatever the existing insurance plan are there or insurance policy are there so for that emergency preparedness also this short-term financial goals will help out that is a important of this short-term Financial goal and next debt management instead of taking the debt okay sometime we can easily manage that whatever the financial requirements are there within that money only or whatever the savings will be there in that only we can easily avoid the debt otherwise if you want to take some amount as a debt that also we can manage in this short-term Financial goal for example that credit card so if you want to pay some money or if you want to purchase then what you can do you can use the credit card and within this short period you can repay that debt also so these are few importance of short-term financial goals next when it comes to long-term financial goals this long-term Financial goal is also very very important for an individual or for our personal life only for dayto activity how we are going to manage that will not make much difference yes it will make a difference but not that extent so if you want to purchase a small small item for your household expenses for your day-to-day activities okay you required you are going to purchase but for your long-term Financial goal will help us to purchase the like the real assets called land and building car or farm land or any other things for your children educational expenses or for whatever it may be okay for all those things it will help the long-term financial goals first one wealth accumulation so every single rupee you are going to save that will be after 10 years or after 15 years or after 20 years it looks huge amount or the bulk amount you are going to look correct so every month you are going to save ,000 rupees or 2,000 Rupees so per month will be 2,000 means per year it is 24,000 for 10 years it may be 240,000 like that how many years you are going to save the lumps some money you are going to get with that money you are going to purchase something or purchasing the assets that is your fixed assets called land and building or Furniture equipments okay all those things you can purchase correct so that is accumulate our wealth that is the first significance or the importance of long-term financial goals next one future planning so for our children for example they are going to have the ambition of do the medical or they they have the plan of doing the doctor or mbbs okay so for that we require huge money okay whether they're going to study here or they're going for abroad okay but they require money to do that doctor or mbbs okay so for that study they required money so if you are going to plan from today so for their future it will be helpful okay that is one more importance next consistent planning so if for example today I will decide that next 10 years I need to purchase a land and building so every month I need to save 10,000 rupees only for this month or next month or other three months I'm going to save after that let me see after five years also I can save money and I can purchase the land and building if you have that planning or if you don't have the consistency in your planning that will not reach the whatever the final the aim or final goal that reaches will be difficult you can reach but also it is very very difficult so sometimes what happens the consistency in the planning also makes huge difference so this month I'm going to save 10,000 next month I have some other expenses so I'm not going to save 10,000 rupees for purchasing a land and building so there is no consistency correct so on that time reaching that whatever the estimated value is there that is very very difficult so the consistency should be there next stability and security so whatever the amount you are going to save if you just keep it in your home home or in your locker that will not give you any returns correct so you need to plan that money also earns some money if for example per year I'm going to save 1 lakh rupees that one lakh rupees if you keep it in your cupboard that will not give any return just that same one lakh rupees if you check after 2 years or after 3 years the same one lakh will be there correct so you need to invest that money in some other avenues it may be in stock or it may be insurance or it maybe now we have sip that is mutual funds we have or it may be fixed deposits or your government bonds will be there correct so you need to invest that money in that particular Avenues and whatever that one lakh you are going to invest that will also earn some money it may be interest of 10,000 per year 10,000 will be earned correct right so that 1 lakh next year it will be 1 lak 10,000 that money also earns some money so you are the aim you can easily reach how the money is also compounding or the money is also getting the other income so that is how we need to stability and we need to maintain the security also if for example if you are investing in any Securities okay that is having the huge risk and huge return also will be there so if you go for a government bonds there is no risk correct they are going to pay the risk-free return to your money so you can get the return also and your money will be secured also like that you need to plan in the long-term security next topic is fixed variable and discretionary income and expenses so we'll see what do you mean by this fixed variable and discretionary income and expenses the different meanings we are going to look in this particular slides so first one fixed income what do you mean by fixed income before we die into fixed income I will tell you the example of income and gains what is the difference between income and gains or it may be take the example of profit income and gains these are the three words correct first I will take an example of profit if for example you are running a business okay it may be a sold trading partnership or what whatever it may be okay from that activity or from that business you are going to get some money you have invested 10,000 rupes and every year you are going to get a profit of 5,000 rupees in that particular business that 5,000 we call it as profit take an example of income now so income is nothing but the money we are going to receive regularly if for example that person is going to going for a work or going for a job okay so from that job he is going to get 20,000 every month that is called income or else that X person is going to invest his money in some Securities and every month he is going to receive 2,000 Rupees as an income from that particular security that is called income that income or that money recurring in nature so every month regularly we are going to receive then only we we call it as an income if occasionally we are going to receive that is called profit and what is the difference between this gain profit we are not going to receive regularly okay occasionally we are going to receive and income also every month or regularly we are going to receive that is called income then what do you mean by gain okay so gain is nothing but this is also we are going to receive occasionally okay 5 years once or 15 years once or 20 years once we are going to receive for example I have a two land and building one is for my residential other for I have given for a rent so what I'm going to do I purchase that land and building 10 years back but what I'm going to do right now I'm going to sell that property because already I have one more house so I'm going to sell that particular land and building so I have got the profit of 20 lakhs so the 20 lakhs we call it as gains why because I have hold that asset or hold that property for a long term or long time after that I'm going to sell that particular asset and I'm going to get the some amount of profit okay that is called gain so next we will come to what do you mean by this fixed income so fixed income is nothing but the regularly we are going to get that money if for example I have a money of 20 lakhs rupees so what I'm going to do the 20 lakhs rupees I'm going to deposit or I'm going to do it in the fixed deposit I've invested so every month for the 20 lakhs whatever the interest rate they are going to give for example take an example of 20,000 every month I'm going to receive as a fixed income so this 20,000 I'm going to receive every month correct that is called fixed income next variable income so this variable income regularly if you are going to receive regularly also but it is not going to get the same amount if for example depending on the market condition or Market variation that money is going to change if for example if I'm going to invest the 20 lakhs rupees in fixed deposit so every month I'm going to get the fixed income or fixed amount of money we are going to receive if the same 20 lakh rupees if I'm going to invest on Equity shares so if I invested the 20 lakhs rupees on equity share so we are going to receive as an income but not the same amount if for example the shares were made very good in the market so on that time we are going to receive high profit if for example one year I'm going to receive 20,000 one year I'm going to receive 40,000 one year I may receive 10,000 or it may be 2,000 okay it is going to vary but that income will be there okay that is called variable income next discretionary income so sometime it is like unexpected it may happen or it may not be like for example that 20,000 every month I'm going to receive as a fixed income because I have deposited 20 lakh rupees here I've invested 20 lakhs rupees I'm getting Equity whatever the dividends from the companies I'm going to receive in the discretionary it may be generated or it may not be generated also so we don't have any hope we are going to receive that money like for example we have given some money for our friend or for whatever the ex person I have given 20,000 rupees so I have given long back like two years back I have given but that money is not received yet sometime what happened suddenly he came and he's going to give 300 ,000 rupees or 5,000 rupees whatever is there with him he has given that money so that 5,000 will comes under this discretionary income we don't have any hope that money will come in business we call it as doubtful debts whatever we have given for the particular person that maybe we are going to receive or may not be also in some situation so if it is comes that money that we call it as discretionary income next fixed expenses if we take an example of personal life okay what is the fixed expenses for example rent water electricity charges or it may be fuel transportation or any other expenses okay that regularly we are going to spend so these are the fixed expenses next when it comes to variable expenses variable expenses is nothing but that is an example uh like insurance insurance every month we are not going to pay correct so yearly once we are going to pay if for example I have two bikes okay so for that two bike every year I need to renew the insurance okay while renewal of insurance we need to pay premium money so that is the variable expenses and sometime take an example electricity bill also comes under the variable expens how how we are going to use if for example 1 month 500 rupees will be there other month 700 rupees some month 800 rupees okay that is also varying not the fixed expenses correct and when it comes to discretionary expenses it is also like unexpected it may be some health regarding or medical emergencies will occur so on that time we need to spend that money that is necessary but the that is discretionary we don't have any prediction that we require money for that particular expenses that is called discretionary expenses so in the next part whatever we have discussed the theory part correct so we'll see how we are going to create the budget using the Microsoft Excel okay so in the Excel sheet if you are aware with the the basic formulas like sum or whatever the charts creating the charts and other things correct so if you aware of this then you can easily prepare this budget in your Excel sheet okay so this is an example of envelop system so in envelope system I've already explained you in envelop system what we are going to do we are going to allocate some money okay we have different categories like here I have taken expenses categories of housing food Transportation entertainment saving utilities debt or loan okay so these are the different envelopes are there so for that each envelopes I'm going to keep some money so I'm getting the salary of 50,000 per month and interest on investment if you have any other income like for example rent you are going to receive as a rent or if you are going to receive any interest or dividends or any other sources of incomes are there you can add it in the income side and you can allocate the money also how much you are going to receive that is your cash inflows so total income is 53,000 so from this 53,000 I'm going to allocate the money as for the envelope system housing 10,000 food 6,000 rupees Transportation 3,000 entertainment envelope 3,000 savings 2,000 utilities 7,000 and debt or loan 16,200 rupees so total expenses is 47,2 so our total income is 53,000 and 47200 is the total expenses so remaining money is the savings okay here also we have saving category so this savings also called as emergency funds and this savings okay this savings is whatever the expenses okay after all the expenses whatever the money is remaining that is called savings okay and this is the best example for envelope system and in the priority based planning or priority based method or priority based method okay in that also whatever these categories are there so depending on your priority or your needs you can reallocate or you can reshift the money or values whichever is priority for you people okay that is one thing so if for example in this emergency fund instead of 2,000 I'm going to type as 4,000 rupees see here in the chart also it is increased and in the total expenses it is comes to so this is an example for envelope system and in the pi diagram also you can see here the total income and the total expenses the first category is that is your income and second category is your expenses so that you can easily verify how much is the income and how much is the expenses with this Pi diagram only you can easily analyze it is almost equal only some bit is savings is there correct like that we can analyze we have one more template I will show you that also this is an example of zero based budgeting I've already explained you right zero based budgeting is nothing but so whatever the money we are going to spend for example this is the budget for the January 2023 okay so in this January month whatever the amount like these are the few incomes I'm going to receive and these are the few expenses I'm going to made and these are the total savings that is emergency plan or savings retirement plan share and Investments education other these are the few incomes I'm going to save okay so next I have the total expenses of 50,06662 so remaining money is 20550 rupees so in zero based budgeting what we are going to do so if you are moving for the month of February that is for the next month whatever the remaining amount of this 20550 that we are not going to carry in this February month whatever is saving is there for January month that is for January month only we are not going to take that savings money that we are going to keep separate like in the bank account or if any other sources are there right so from that we are going to assign we are not going to touch that money so here we are going to start with the freshly for example I'm earning the money of salary of 50,000 next interest on the income 5,000 rupees like that whatever the incomes are there just you need to enter and here also expenses category that is your insurance premium is 4,000 next when it comes to electricity charges 500 rupees and gas 1,200 rupees and if it is comes to water and other charges 800 rupees like that whatever the different categories are available okay we need to allocate the money so your total income will come here and total savings will come whatever the amount you are going to save and the whatever the total expenses you have made that is available here so like this individually we need to maintain the all these categories of money okay so the summary is total income 7572521448 9 rupees we have saved additionally okay this money we can use it for other purpose also here see this this color is like total income this is for total savings 14% And this is called total expenses 34% that is your percentage based method also so 52% is your in 100 rupees 52 is our total income in that we have spent 34% remaining 14% is the savings we have allocated that is as an emergency F remaining 34 + 14 is like 48% is the total expenses so remaining 2% is your additional savings okay that is how we need to analyze this P diagram okay and here also in bar just it is showing as total income and total expenses it is shown here okay like this we need to create and individual wise like February month March month budget of April every month we can easily shift our whatever the amount amounts are there as per the whatever the income we are going to generate as per our priorities or as per our percentage based method or as per our envelop system method or whatever it may be okay all these categories we can easily allocate the money and we can get the final output and whatever the total savings the 2% is total savings are there no that we are not going to carry for the next month like this we need to create a budget so next topic is Introduction to savings and Investments it is very very important for an individuals or for our personal life it is very important because how much we are going to save and how much we are going to invest because with that savings only we need to invest that investments will fetch more returns in the future so what do you mean by savings if for example I'm going to earn 5,000 rupees every month so with that 5,000 rupees I'm going to spend 3 ,500 rupees every month it may be for household or whatever the categories will be there for all categories including that I'm going to send or I'm going to spend 3,500 rupees so remaining 1,500 that is called savings next Investments Investments is nothing but the money whatever the from savings we are going to save that money we are are going to invest in some Avenues it may be stock or it may be in the real assets or it may be any other avenues or different Avenues are available right so in that we are going to invest that is called investment we are going to invest or we are going to put some money in other avenues that is called Investments what are the importance of the savings and the Investments so this is related without savings investments will not be there that is investment is nothing if you have the savings then only you are going to start investing your money or you are going to invest your money so first One Financial Security so if you have the Investments okay that will have the financial security also so whatever the for future or it may be unexpected things will come in the next year or for the next future okay for that you have the financial security next future planning that is after 10 years if you want to purchase something or after 10 years if you have any dreams to fulfill the dream it will help that Investments compound growth okay the money whatever the single rupee you are going to earn today that is the same value will not be there for tomorrow that is the reason your money needs to earn some money that is the reason we need to invest our money in the Investments so the 10 Rupees if you are going to invest that will gives 12 rupees in the next year because the two rupees increased is income for your Investments next wealth building whatever the assets it may be real assets or it may be intangible assets whatever it may be okay that assets wealth is going to increase if for example today I'm going to purchase only one share of rupees 10 Rupees tomorrow I can purchase two more Share three more share five more share so that will create as a huge wealth okay next retirement Readiness so after our retirement we need to prepare for our retirement also correct because future is unexpected after a retirement also we required some money or we required money for some expenses it may be for medical or for traveling or Transportation any other things correct so for that we need to be prepared for our retirement age also financial goals financial goals in the sense we can have the dream of purchasing a land and building or purchasing a dream car or it may be any other dreams also okay so for that reaching that goal or to fulfill that goal we required money so through this Investments or with the savings we can easily reach out that whatever the goals will be there reducing debt okay without going for a debt or without going for any loan okay we can easily reach the whatever the goals will be there okay with the help of the savings and investment we can reach that flexibility and opportunities so if you are started investing your money you have lot of other opportunities will be there in the market okay if you are for example I'm going to invest only my money in the debt security we have other avenues also like preferent Shar is there equity share is there or government bonds will be there mutual fund is there so few others opportunity will be there so you can easily explore that and which is safe and it is given good return also you can invest your money in that next peace of mind so for a future for example what is going to happen next or how much money is required next day or for next month or for the next year that will have the financial stress in Your Mind Correct so if you have the Investments or if you have the savings then you can easily reduce the burden or reduce the financial stress in your mind next that is Legacy planning for example whatever my parents are following for the budget okay how much we need to spend how much income is there how much we need to save all those things sometime that Legacy will continue for their children also if for example my parents are following some budgeting techniques means I'm also following the same pattern because that Legacy will continue because how they are going to spend money or how they are going to earn money and they are going to allocate the money that is very very important that Legacy planning will be helpful next what is the difference between savings and an Investments savings for the shortterm I've already told you right so savings we are going to made only for the shortterm period that money we need to invest then that money is also earn some money that is short-term savings is for shortterm and investment usually used for the long-term goals with this shortterm only we need to save and we need to invest on the long-term funds next ready access to cash because whatever monthly we are going to save that is there like usually we have the bank account correct we are going to keep the money in the bank account or sometime we are going to keep the ready cash in the house so whatever the money is required easily we can take that money correct that is the ready access to cash but investment longer wait to access invested fund so if we are going to invest money means we need to wait for other 5 years 10 years 15 years whatever the maturity period is there till the time we need to wait savings earns interest because whatever the money we are going to keep it in the savings bank account so we will get the minimum return or minimum interest we are going to get when it comes to investment earnings potential so potential is nothing but how much it can be get for example if you are invested on the equity High return high risk like that debt we have a fixed income correct according to the potentiality or market and condition so it is going to get the returns and next when it comes to savings involves minimal risk so because we are going to keep it in a savings bank account or we are going to keep it in the home correct so we have very minimal risk and when it comes to investment it always involves risk because if you are invested your money in the stock market or real estate or any other assets okay you have a risk okay always you have a risk in the Investments next topic tips to avoid debts you all known right debts is nothing but the loan or credit or usually we call it as Finance okay so how to avoid this debt how budgeting helps to achieve financial goals and avoid debt that is we need to reach our financial goals also and we need to reduce our or we need to avoid our debts also that is our loan so the first one is clear prioritization each and every activity if you take any activity or any task okay before we start that activity or task first what we are going to do we need to plan planning is the first step for all the activities correct here here also in the budgeting we need to First do the planning or we need to forecast for the future correct so for the forecast also or for that forecasting also we are going to plan what is the expected expenses will be there that is few are in staggered okay that is like your rent or it may be for your electricity bill water charges or monthly expenses for food okay or for transportation that will be in staggered manner correct so every month it is required it is like recurring in nature but sometime other expenses unexpectedly will come IR recurring in nature for example yearly once or yearly twice it will come correct so for that all those things we need to plan and we need to prioritize for example our children education okay so for that school we need to pay educational fees or tuition fees correct that is very very important that we need to prioritize other than that any other like entertainment or for transportation or for service of our vehicles all those things will be deprioritize and we need to prioritize the children educational or tuition fees correct like that we need to take the prioritization clear prioritization we we need to make that is the one formula to avoid our debt or going for a loan second resource allocation while allocating the fund if for example we have 50,000 of income every month if you have any other income sources or any other sources also you can Club you can take the total of that income with that income only you need to match with your expenses and you need to save some amount in that correct like that if you are not going to save if for example I'm earning 50,000 and I'm spending all 50,000 Rupees then there is no use we need to allocate the fund accurately and we need to keep some savings and emergency fund also then only you can avoid your debt if for example in the last example I have taken as the car insurance okay next month we need to renew the car insurance that is the unexpected we have regular expenses in that it is IR recurring in nature that car insurance correct so for that whatever 50,000 every month you are going to spend for that paying 15,000 rupees of car insurance what you are going to do you need to use your credit card or you need to go for a personal loan or you need to take any other andson like from others or third party you need to take the money correct that is a debt instead of going for that debt every month 55,000 rupees or every month 22,000 rupees if you save okay last 10 months if you save 2,000 Rupees 20,000 almost your insurance was already paid in that money and you can save other 5,000 also that is called resource alloc location you need to keep some savings and emergency fund next debt awareness we need to have the aware of how much debt we are going to take how much we have the capability of repayment of that money and why we are going to take the debt and what is the use okay or what is the need of that money on that particular month if for example after 10 years if I want to take a land and building every month I need to save so we need to calculate the the future value also that is your present value and future value the money what is money value today that is not same for the next day or next month or the next year correct so instead of that if you take the loan today and if you purchase that land and building and within 10 years we are going to repayment of the debt that is also a fair idea correct so instead of waiting for 10 years we can purchase that land and building today by taking the or by borrowing the money from the financial institution and next 10 years we are going to repay that money or repay that debt as an Emi or an installment we need to repay that is also one kind of avoiding the debts correct so that awareness we have to maintain or we need to have that updated what we required for the next next debt repayment with the same example purchasing the land and building next 10 years we need to to repay whatever the X amount we have purchased or x amount we have borrowed to purchase that land and building so every month equally we need to distribute and we need to pay the debt installment or the Emi we need to repayment or repay to the financial institution so easily you are after 10 years or within the 10 years you are going to repay all that money what you have borrowed next savings growth so I just give an example of every month if you are going to save 2,000 Rupees that emergency fund is different the savings amount is different okay every month you are going to save 2,000 Rupees complete one year you can save 24,000 rupees correct so that savings will be growth or will be grown for example every month 2,000 I'm going to save that 2,000 I'm going to keep it in our bank account only so for that money also you are going to receive interest your SB account also they are going to give the minimal interest rate correct so every year you are going to get the interest for that particular savings amount for example 24,000 so 24 into whatever the monthly wise they are going to calculate the interest and they are going to give the interest also or you can do some other activity also like for example you can invest on sip or if you can have the interest of investing on the real assets or cheat fund you can invest that 2,000 money every month and you can get the lumsum value also that is we'll create the saving habit and that money is going to grow or that money is going to earn some money that is called savings growth so that is the one more way to reduce or to avoid the debts next emergency preparedness so whatever the future that is unpredictable correct so for that future we are going to be prepared that is preparedness is nothing but financially we are prepared to avoid or to tackle that risk or tackle that whatever the unexpected things will happen so that is called emergency preparedness and the last one long-term planning so we are going to borrow money and we are going to take the land and building and within the 10 years we are going to repay that is the long-term plan or if you have already have the own house then we can plan it for next site or maybe it may be farmland or it may any Vehicles like car some other things okay so for that longterm also we can plan not only for purchasing if for example you have a child okay for their educational or for their future whatever the money is required for going for a higher education like medical or it may be for engineering or any other things or if you're are going for study abroad also for that you require money right so that planning also you can do using the budgeting and that will avoid the debts so after 18 years or after 20 years if your child is going for studying abroad on that time they requireed huge money correct so if you plan today and you are going to save next 18 years or next 20 years then you can easily give that money to your children or child and they can go for abroad studies otherwise what you can do after 18 years you are going to take a loan and you need to send your children to study in abroad and you need to repay all the debt correct so instead of taking loan you need to save from today and you can easily send your children or whatever their requirement or whatever their dreams are there you can easily fulfill the dream okay so this is one more way to avoid the debts with budgeting mastered it is time to explore how chart GPT can further elevate your Excel skills learn how to use this AI tool streamline tasks automate processes and enhance your data analysis integrating chat GPT with Excel will help you work smarter and unlock new capabilities in managing and interpreting your financial data Please Subscribe for more finance and Excel videos now in this module let's try to understand whether the key components of artificial intelligence what does the artificial intelligence world look like in the current scenario so let's have a brief introduction to AI so what is what exactly is artificial intelligence now art artificial intelligence is the simulation of human intelligence in machines it's basically incorporating the human brain in machines that what that's what artificial intelligence is now ai involves the development of various algorithms and computer programs right that can perform a variety of different tasks such as natural language processing recognizing objects and images making some logical decisions and solving various complex problems that are a bit difficult for humans to solve so this is a bit of a new thing in the current scenario that the computers are working for us and we can interact with computers in the human language right the chat Bots that are built we can interact with the computers in the human language we do not need to understand python we don't do not need to learn coding for that right we can just in our own language right English Hindi or any other language we can just type in the the prompts and we can get the results that we desire from computers that's the Crux about artificial intelligence now what are the key elements that involve artificial intelligence right the first one is machine learning now machine learning is is is basically a subset of AI that focuses on developing the various algorithms and models that enable computers to learn from data and improve their performance without explicit programming that's what I just mentioned that you not need to know a lot of coding you do not need to have an understanding of of various coding language right be it python C++ you just need to know your basic language be it English be it any other language that you prefer right so machine learning involves tasks such as classification regression now I won't get into deep deep into uh machine learning and all but it just involves task like classification regression clustering and reinforcement learning right so this is what machine learning is about now it's a subset of artificial intelligence then the next step to it comes that is deep learning now deep learning is again A specialized field within machine learning that utilizes neural networks right with multiple layers to extract the complex patterns that are there representations from tremendous large amount of data that is present now data is being created on a day-to-day basis right right from you using your phones to generating using your laptops computers you're generating data on on a minute to minute basis right so someone in the world needs to analyze that data needs to find Trends from that data and have logical solutions to the business problems or the problems that you are facing right you can have a logical answer to that now deep learning has particularly been successful in areas like computer vision and natural language processing right these are the two areas in which it has been really significantly important for us to learn about it then uh now NLP is a branch of AI that deals with the interactions of computers and human language right this is the bridge it just Bridges the gap between the computers and human language now it involves task like language understanding sentiment analysis right for example you have a customer feedback you're running a company you want the feedback from your customers right so you can generate a customer feedback form you can understand the sentiments right now the sentiments would not be numerical right it would not be Quantified it would be a qualitative data to understand the reasoning the tonality of those feedbacks is sentiment analysis right then machine translation enabling the chat boards to understand what we are talking about the tone in which we are talking right so NLP enables computers to process analyze and generate the human language now the next part is computer vision now computer vision focuses on enabling the computers to understand and interpret the visual information from images or videos now it involves tasks like object recognition right if I just keep a egg in front of that in in front it just input an egg right it should understand that we talking about an egg it should not give it as a thing called as ball right so the recognition the object recognition is the first part that comes under computer vision then image classification segmenting those images right and facial recognition now computer vision has applications in areas like autonomous dying we all know about Tesla Tesla is fully automated right you don't need a driver to drive it essentially right in the US per se so how are these things evolving it is invol falling through AI right the lane assistance that uh which lane are you driving at driving on what is the speed limit that is there so everything is captured right by the sensors on the on the on the car on the vehicle and that inputs that the the the network the chipsets inside that vehicle would understand okay now this is the speed limit we cannot go beyond this this is the lane in which we need to keep we have a right turn ahead so we can't be standing in the middle of the road right we need to come to the right if if we want to take a right turn S so all these human understanding is now done through computers right so computer vision has various applications in autonomous vehicle surveillance and argumented reality now these are the key elements of artificial intelligence now what we talk about why is it important right what is the importance of artificial intelligence number one comes the Automation and efficiency it provides right we can automate a lot of mundane tasks that we do on a day-to-day basis for example if I if my flight got cancelled and I want a refund right and the the airline is not providing me the refund so I would have to write an email to them right this can be done very easily if we have ai Incorporated in our emails right we can just have a basic information given and they can generate an entire email asking for refund in a primary account so these mundane tasks that you not require a lot of attention to but you have to do it can be done through computers so that's the major advantage that's the most important thing that we talk about in artificial intelligence now ai enables automation of repetitive and Mondays task allowing human to focus on more complex and creative work by automating processes AI can improve efficiency reduce a lot of errors that humans tend to make and increase the productivity across various Industries the second part is data analysis and decision making now a lot of companies have started in this in the sector of AI in which so they have a lot of clients that have lot of data that is is being generated and the work of those companies is to analyze that data find the various Trend find the underlying trends that are there there so that the company can make sound business decisions for the future of the company right so decision making and data analysis now ai can analyze large volumes of data and extract valuable insights patterns that are not easily identified by humans the third aspect is personalization and customer experience now ai powered systems can analyze the user Behavior and also it can refer to the historical data to deliver the personalized experience right for example you're using phone on a day-to-day basis right and you would have noticed that you've searched something on on on let's say Google on on something of that some product you have searched now it com it continuously comes to your feed on different channels right be insta be Facebook be any other medium it would just pop up as an ad right so these are the AI underlying things that are happening behind machine learning understanding what what is happening what what are the personalized customer preferences so a lot of these companies are tend to make your make the customers more feel uh connected to the brand right so this is how personalization and customer experience AI is affecting in that regard now improved efficiency in manufacturing when we talk about a lot of big Industries be it steel plants be it power plants be it a lot of different other manufacturing processes of small or or big Industries right now ai powered system can optimize these manufacturing processes predictive the maintenance and quality control that is there now this would lead to the increase production efficiency reduce downtime and improved quality product quality right now the advancements in research and science now ai is transforming this scientific research by accelerating the data analysis simulations and hypothesis testing now it enables scientists to explore the complex problems that are there make significant discoveries out of it and develop the innovative solutions right in Material Science climate how it is affecting how it's changing how it how how global warming is affecting so all these patterns can be now analyzed by computers using artificial intelligence now let's just talk about the newest tool that is out there called chat GPT now what is chat GPT so it is a AI powered language model which is developed by a company known as open AI now open AI is a nonprofit AI research organization that is founded with a goal of promoting and developing friendly AI in in the way that could benefit Humanity as a whole now chat GPT is based on GPT what that is that is generative pre-trained Transformer architecture and has been trained on large amount of data to generate humanik text in response to the prompt that we provide so that's the whole Crux behind chart GPT in which we humans would provide them certain sort of prompts the things that we want the computer to answer and in the text based form it since it's a text based model so it will give us results in a text based format so now let's just dissect the term GPT what does it mean in the more technical way so now GPT stands for generative pre-trained Transformer right so it is a type of artificial intelligence that can generate a text so it does this by being trained on massive amounts of data so once it's trained it can be used to generate new text by simply starting with a prompt and let the model predict the next word so I'll be giving you a Hands-On of this tool how it works in a more simplistic way so that you get go more comfortable with using charb as a tool so the examples of what it can do it it can generate poems it can write codes scripts musical pieces emails letters and much more right now it has a special ability to translate text from one one language to the other right so for example if you're in a new country you want it to give it in in a way of the language in which country whichever country you are right so it can translate that text for you now it can answer questions about a variety of topics now this has been trained till September 2021 so all the amount of data the all the knowledge that it possesses is till September 2021 so after that it has not been trained to that kind of data right what else it can do it can summarize text that is there for you can rephrase various text in the tone you want like for example if you want a for formal polite tone of any of the sentences it'll give you in that regard it can rephase your emails in a much more better attractive looking way in a more catchy way right so all these things a a GPT model can do right so now when we talk about GPT right let's just dissect these words generative pre-trained and Transformers what does this mean now generative means it create new information for you that's generative pre-train means that initially the models undergrow and unsupervised pre-training phrase where they are exposed to vast amount of data right now it is just given random sort of data is given to it now the model would then you know divide it into segments understand it in its own way for example if I just give a data that has like a a set of fruits like a lot of fruits in a ball right now I just tell it to identify what they are right so how it would do that it would not know what is an apple what is an orange it would just divide it first with the shape then the color so it will have its own steps of understanding right so that is how it would be right then Transformer now deep learning model such as Transformer utilize a technique called tracking relationships in sequential data to learn the context of how it is done now in this case of gpds they observe words or tokens within a sentence and make predictions about the following word now chat GPD does not understand like how we count Words so it it count Words in a form of tokens right so it has a set format set transaction like for example $1 is 82 rupees right similarly one word is equalent to x amount of tokens right this is GPD understands in a token way right so what does chat GPT mean what what is the behind what is the rational behind introducing this Transformer and what are the steps in which it trains right what is how does it evolve right what are the training what are the steps involved in it understands what we are talking about right so the first part is training GPT now GPT is trained using a process called generative pre-training so what happens in this is in generative pre- trining the model is given large Corps of text and is asked to predict the next word in the sequence right so Ram is going to do Dash so now it has to predict what it is right so this is how it works next word prediction comes the first step in training the GPT right so now this process is repeated a lot of times over and over again right on in the sentences that we give them as as we are generating a lot of data that is all over on the internet right so it trains on those on those data on those data sets that are there right so the model learns to predict the next word with increasing accuracy now it takes a lot of time for it to understand to get trained but once it get gets trained it's very less to making errors in that regard then is splitting the data set now the data set is split into two parts number one is the training data set number two is the test data set now the training data set is used to train the GPT model and the test dat set is used to evaluate the GPT model the test set is not used to train the model so it can be used to measure the models accuracy on the data that it has not seen something that new comes up it gets used to that the third step is training the GPT model right now the GPT model is trained using a technique called supervised learning so in supervised learning the model is given set of input data and set of output data so the model learns to map the input data to the output data so in case of GPT the input data is a sequence of words and the output data is the next word in that sequence right so this is how it develops itself this is how it grows itself now evaluating the GPT model now this this step the GPT model is evaluated on the test set the accuracy of the model is measured by the percentage of times the model has predicted the correct next word in the sequence now then comes the fine tuning of the GPT right so the GPT model can be fine tuned on a specific task the fine-tuning is a process of adjusting the model's parameters to improve its performance on a specific task for example GPT can fine tune to generate text tuned to translate the languages and answer some very generic questions that it can get from a day-to-day basis now the GPD model can be used to generate text translate languages answer the various questions and perform C certain tasks right so the model can be used to generate the humanlike text translate text from one language to the other answer various questions about a variety of topics and summarize those texts so this is what GPT actually means what are the steps involved in its learning so this is what we have learned so now in this module let's start by understanding Excel using Char GPT now before that as we all know that Excel is the most popular tool when it comes to data analysis or this creating something out of data in a more quantitive manner Excel Microsoft Excel is the tool that we all go to right now how is it possible that we can integrate chat GPT in understanding excel in this in in a very layman's term right if I do not know how to do complex operations on Excel how can charil help me and make my life easier right I can consider chat GPT as my AI assistant and it can help me do reduce a lot of mundane tasks ease my process and improve my productivity right if you are at office or if you have your own business starting in Excel and charp right integrated in Excel can ease out a lot of your work so so let's just have a quick overview of the dashboard that Excel has then let's switch on to chat GB understand using various case studies right so I'll start off by uh opening up Excel starting with a blank uh sheet and making understand the different things that are there in the dashboard and then switch on to chart gbt so I'll go to new and then I'll open Excel right Microsoft Excel right I don't need to name it I'll just open right so a blank Excel worksheet comes into play right so what this is these are rows numbered from 1 to n and from a to n number uh that is what a column is right so now each cell in that worksheet has a specific number right for example I just choose one random column that that in column e right one column one random cell I click on so it becomes E1 so that is the number that is given to that cell right so if I combine a lot of this that this becomes a combination starting from C5 right so these are the things that basic things that Excel has right so you can insert tables you can create a lot of tables tabulations from it so we'll we'll now shift on to chat GPD understand if we have certain data set how we can analyze it or how we can integrate a lot of things right understanding Excel through CH jpt so I'll go to my search engine I'll randomly click on open AI because that's the company that makes chat GPT as a product so I'll click on open Ai and click on the tool that you want right so now chat GPT is the free version that they provide and GPT 4 is the paid version that they provide so today for for understanding both 3.5 and the four model just Char GPT and GPT 4 model I'll go for the paid version that is GPT 4 right so let's just uh try CH gbt plus that is gbt 4 so I have already logged in so basically you have to log in and then pay x amount certain amount and then be a premium customer to chart GPT right so I'll uh here I'll start with uh clicking on the new chart option so now as you can see in this dashboard that there are two models that it shows up that is GP 3.5 and gbt 4 so now what is the difference between the two so the basic difference is that gbt 4 has a higher order thinking it has a higher order logical reasoning to it right we can see in the company's uh this website you can see how advanced this is right gb4 can solve difficult problems with greater accuracy number one it has better broader knowledge and higher order problem solving logical reasoning right so these are the things that are better in gp4 as compared to chat G right so for example both the inputs for both the same inputs both the models have different outputs so gbd4 would give you more crisp better understanding uh results better understanding outputs while chart GPT would give you a bit of wag answers right it would not give you the pro complex Pro solutions to complex prompts right so now in terms of the benchmarks that it has set now chart gbd4 ranks 90th percentile which is like an advanced level student in your class and in the uniform bar exam normal charge AB score 10th percentile that's like the average of average student of a class so these this is a difference it has more higher order thinking it has more higher order logical reasoning to it then similarly in biology Olympiad it is 31st percentile while gbt 4 is 99th percentile that's basically like the top 1% of the class so this is how it is it is um developed with much better understanding right so one disadvantage of gbd4 is that it will give you accurate results it will give you crisp results with better logical reasoning but it would not give you fast results it would give you results but in in a little slower Pace while gbd 3.5 would give you it's a fast model it will give you instant results right quick results so chat gbd4 since it's paid right but it has a cap of 25 messages in every 3 hours so that's like a demerit that you can if you're paid you can use you cannot use it n number of times for a 3-hour period you can only get in 25 prompts right so these are the differences between the two models so let's just for us Excel we do not need that kind of higher audit thinking so we'll just we'll start with GPT 3.5 right so now I'll consider I'll take uh I'll just ask GPT 3.5 to generate me a table right because Excel understands language of tables so we'll have tables of uh we have we will have three or four columns and we'll have n number of rows so what can be the prompt that I can give right so I'll just give it a random command that uh generate me a list of what do we say 100 movies 100 movies rated by uh let's just take IM DB right I am rated by IMDb in a table form form right now it will give me 100 movies in a table form but now if I just give it this this prompt it will just give me one column with 100 movies but what do we want we want three columns right so I would give them a specific prompt right specific prompt creating First Column as creating First Column as movie name name second as uh rating third as as year of release right so I'll just ask it to generate me this right so as we can see they it just created three columns with movie name rating and year of release right so now it will give me all the movies that are rated now it is also giving me rated in ratings in a descending order the highest the Shank Redemption it says it's the highest then it just gives me in an and in a descending manner now now we can see in this prompt that just ends here in an very abrupt manner why is that the case because this the case because it has reached it limit per prompt right now if I want it to continue I'll just write continue right yeah just go on it'll just keep on generating me the results that we asked for so now what we can do is we can just go and just copy this table right there right either we can copy it from here or we can just you know randomly take this input contrl C go to our Excel worksheet and paste it right so now we have asked so basically we asked chart GP to generate me data right now for a test purpose this is good to do but when you are considering uh a much more serious way for analyzing any data that is there for your company you cannot just rely on Char GPT to give you data you need to have your own sources your trustworthy sources of of data right to analyze because this may not be as accurate as it seems so it can have a lot of errors in in play because because it still takes it will take a lot of time to develop that kind of model that would give you uh peer reviewed uh results but till the time it is there have the data that is trustworthy right so just like that we have a lot of things that are there right so I'll just copy the data what's there so the first step that we did was we just asked it to create me a list a a table of three columns and N number of rows that's 100 right so this is what we did now what if we have to analyze other things right like maximum minimum sum of things right if I do not understand how the Excel formulas functions or how does the functions in Excel work right so what do we do in that regard right so I'll give you a Hands-On on that as well okay so now what if we want to calculate the average of let's say column number D that is quarter 3 Revenue what if I want what is the average of it and considering that if I am a lay man and I don't know how to use Excel how would I do that in in a shorter way right so I'll just so what will I do I'll just go to chart GPT and I'll write in the prompt right so now I I I'll just check in the data set so if it is column number D right D2 to let's say D20 right I need a average of it right so I'll just mention the row numbers as D2 2 to D20 right so I'll write this so so it will give me what what needs to be done so right so the the function that is used is equal to average now I'll just simply click on this cell right here and paste in that formula the syntax and it just gives me it SE it automatically selects the range right so if I give them the starting and the end it'll just directly do that right so that's that's how Char gbd can enhance the way you working on Excel right it will make your life much more easier right so here it gives me 54619 is the average revenue of quarter 3 combining all the companies right here right so if I want to do it the other way if I just want like to give me the the average of uh of the same company in all the four quarters what would the How would that go would just have to mention them okay if it is the same row then it will be B2 C2 D2 E2 if I'm considering the first company right here right so that can also be done now what do I have to do is that I need to calculate how many times is like let's say what are the total number of movies in English French and Italian right how would I do that right so if I know okay if there are four languages right here or five languages or how many other languages I want to segregate I want to find how how many are there how many let's say English movies are there how many French movies how many Italian movies how would I do that okay so I'll just type in the prompt that how to calculate the total number of times a word has been repeated in a column Comm okay write a Syntax for the same Exel right so it tells me that the the the function that I'm going to use is Count F right so in the previous module when I talked about a generalized bit of key functions that are important I talked about count if right to count the number of times a certain thing has come up right so the same thing is what is mentioned over here right is just that I gave you that reference so that you would have an idea of it if I was a lay man if I was just starting out I definitely would not know what count if is now in a very simplest of the terms what I did I just added a prompt here to ask GPT the computer how to calculate the total number of times a word has been repeated in a column write the Syntax for the same right this is basically I'm telling that okay this is the problem that I'm facing find a solution that's your job because this is like an AI assistant it'll help you in whatever ways that you would want right so it has given me an example also how to do that right so I'll just just copy this right here it has given me an option so I'll just let's just put it over here right so it'll give me zero because nothing is there so I'll just edit it out the range right now I'll just remove the range section I'll just type I'll just drag and drop all this entire age from B2 to B22 right then what are the criteria I'm looking for for for English I'm looking for English right so I'll write English English right so I'll just print and enter right so now it has given me the exact number of counts that English is repeated in column B right so if you want to check it if you want to cross check 1 2 3 4 and five so that's accurate so similarly if I have to find how many times the word French has repeated in column B how would I do that I'll just copy the same syntax that chart GPT provided me change the range from B from A to B then A2 22 B22 right 22 right then Apple changing apple to French right then I press enter so now the French has also been repeated five times similar thing I have to do for just removing Fringe and just adding Italian and now it's selecting a different range I'll just give it the range that I want right and I'll just press enter so six is the number of time Italian uh is repeated in column number B so we can state in general if we have a problem statement how many movies are in English five in the entire data set how many are in French five how many how many are in Italian six so we'll get a generalized way so I did not do anything on my own I just asked chart GPT as an AI assistant that okay help me solve this part so it did right the second thing that we talked about was trim right so I'll just ask chat GPT if there are unwanted spaces right I'll just ask if there are unwanted space in a Cell at the start what syntax should I use right I'll just type it out and let's see what it has to say right so it gives me the exact command that I was referring to it gives me that okay it knows you want to use trim because this is how you would get rid of The Unwanted spaces that are there right it just mentions gives me a exact thing how to do it so I'll just copy this I'll just create another column right here right and I'll just write that thing here right so trim a trim this right trim a I'll just start with A2 right so now now just write here correct range right I'll just make another column bold it maybe right now this is the thing equal to trim and the cell I have to click on that cell I'll just type out the cell name so what I will do I'll just since it understands so I'll just drag and drop till the very end and now as we can see right here in in row number eight there was this much unwanted uh space right in number 12 that was there in number 17 it was there 19 18 so that extra space that unwanted space is now gone right now you get a clear uncluttered view of that movie title column right this is how you use trim right now now let's just take another example right if I want a set number of characters right if I want to extract let's say this is a data set that gives me the name of that state right Indiana Delaware and all all this and the county names with their PIN codes now if I want to just extract the PIN codes right I just want the PIN code to be out there right how would I do that I know that PIN code is of six letters right here right six all it is the same now I want to extract it how would I do that I don't know I would ask charg how to do that so let's just ask how do I exract five letters in the left of a cell and add the same in a new column right so let's just see what chart GPD has to answer so as we also read before that we can use a syntax called as left right how do you use that again it has mentioned number of how to use that I'll just copy this I'll just paste since I want from the left of this cell I'll just paste this right I'll just change the the cell to B2 I'll just add add the number of characters I want right I want six letters so I'll just move it to six and there you go I have extracted the first six letters here and I can drag and drop to the entire thing right so this is the thing so basically now the change the shift of using Excel is how either you can learn how to use Excel that's number one but if you don't want to sacrifice on that if you just want a shorter way ahead if you just want that my workflow is is smooth it's faster it's quick then and I do not have the bandwidth or the or the time to learn Excel what I would do I would just add CH GPT how to do that so now I've extracted so I now what would happen is that I'm given a business problem the first approach is to go to char GPT of course use your brain but the first element is to just know what uh Char GPT can answer right it'll give you some idea of or the other right so next we can also talk about uh in cat right we we spoke about how it is used but again let let me just show you how you can use it what if I want to [Music] combine two cells and add it in a new column how would I do that tell me so it it mentions you have to use the function known as concatenate the syntax is mentioned right here I'll just just copied the syntax right here how to use it now the best part about Char GPT is it would give you a Hands-On V just needs to be done also it will give you a brief bit of a brief of how how things are happening right it would not give you anything too wi that is not true of course you can try it out you can check it out but yeah you can get what you want from it right so I I have used skin catenate here in instead of this I just changed my uh range right I just I want this first name and the second name to come in the same cell so there you go I got it I got the the name together combined right similarly I'll just drag and drop till the very end and it'll give me a the entire combination of column A and column B right right so next uh let's just say another thing let's just find out the number of characters that there there right so how would I do that how do I find the count the count of characters in a single cell how do I do that just type in so it's basically typing out what you are thinking what you're thinking right it just gives me the the thing that I can use equal to Len and just try it out so right count make another column count add my syntax it's just taking another cell I'll just delete it it's just a dummy cell I just add cell name E2 and press enter so it tells me okay Miss Jerry 1 2 3 4 5 6 7 8 9 gives me the correct order nine and similarly I can do now the point is that would also take into considerate the space if I have a blank here right it'll increase it to 10 right so it takes in consideration of all the blanks that are there as well right the similar thing if I just don't want this I just want another cell let's say this one so I just put enter right and then the best part I can just drag and drop and it will come off the column B right so these are the things that you can work on right what if I want to calculate the weighted average of a range of sales how would I do that right so I'll just type in calculate the weight weighted average right so it would give me what is the parameter in which I have to do right so it says that some product is the the aspect that I need to look at right so I have to mention my range one range two then sum the sum of range two right so this is how the the exact weighted average will come right so we can get that as well if you want to right what else can we we do let's just look at some other things what if I want to create a drop- down list of a cell right of a range of a column right so what how would I do that how would I create a drop down list right so I would ask it to tell me how it is now one thing that you have to know about it is that the things that are here right it in a in a single thread it has its own memory right so if I'm just writing it down in a thing I don't have to mention that okay tell me in Excel in Excel in every prompt it would have the memory of the entire the this entire thread that we are talking about right it knows that the context is about Excel it knows that okay the things that we are talking about is of excel right so it will just give me that the entire thing that will come up here right so it it is stating me that to create a drop- down list with the values from B20 you can use data validation here is the thing right select the range of Sals you want to drop down list for example assum that you want to create go to the data tab in the Excel click on data validation data validation dialog Bo open settings tab allow drop- down choose list so so you can do it in this regard so anything that you have in your mind that that is even a wild thought that okay this is the things that we can change in Excel right but you have no clue how to do that you can just type it out in Excel it would definitely have some of the other way to assist you on what you want to do right this was about the general basic prompts in which you can know what is happening how it is happening right hello everyone so in this session we are going to discuss about Excel using CH GPT so in Excel how we are going to use financial statements if for example if you have a very good knowledge on finance and accounts or on the financial statements then you know everything on the financial statement how it will be look likees and what do you mean by that assets liabilities current liability long-term liability correct so if you are not knowing anything then how the chart GPT will guide you through the financial statement let's begin so we'll see I will give one prompt to the CH GPT so CH GPT is a expert correct so it is known all the financial statement or all the finance accounts all terms that known by CH GPT so we'll ask CH GPT how it can be assist us regarding these financial statements so firstly the step by-step process we are going to follow for preparation of financial statements correct so in general also the process of preparing financial statements is first whatever the transaction day-to-day transactions the books they're going to maintain in the accounts or in the company correct so with that information only we are going to start correct so first we will pass the journal entry that is our primary books of records from that journal entry we are going to pass the The Ledger account so after that whatever the closing balances of Ledger account we are going to get from that information we are going to prepare trial balance with that trial balance we are going to prepare our financial statement this is the process or stepbystep activities or step by step reports we are going to prepare for financial statements first general entry second lger account third trial balance fourth we are going to prepare income statement or pendl account and then finally we will go for our balance sheet so we'll see one by one so here see gather the necessary financial information I already told you these are the few steps we need to follow to prepare a financial statement but in this session I will explain you only income statement that is your pnl account and balance sheet preparations using chat GPT so next that is your all financial information like assets liabilities Equity Equity is nothing but shares okay that information we need to gather first that is the first step second identify and classify assets so for example if he called as an asset that is the having the net worth and it may be appreciate or it may be depreciated in the every year that is called assets correct we have long-term assets that is fixed assets and we have current assets so these are the two categories of assets we have so that we need to classify next list all the categories of liabilities in the liabilities also short-term liabilities long-term liabilities your equity share Capital also comes under the liability side only debt and Equity so that we need to categorize calc shareholders Equity so your shareholders Equity is include of your share Capital retained earnings reserves and surplus okay these are the few things it is included in your shareholder fund arrange the balance sheet format so I will explain you what is the balance sheet format so first we'll look at the what is the format for balance sheet see I have asked the questions on chat GPT that how I need to prepare the balance sheet that is the format for balance sheet okay with the whatever the rows columns and categories that is subcategories of asset subcategories of liabilities that format is asked for chat GPT so it is given here like mark down table format so it is given in the table format see here assets liabilities this is called your horizontal format of balance sheet we have two kinds of balance sheet one is horizontal second one is vertical balance sheet so this is the information they have given for the balance sheet format if you want copy just click on this copy code then it will be copied and you can paste it in the Excel sheet also and you can add your values that is the dummy values you can add in the value columns are there okay so I will go through with the this information and I will show how to prepare the balance sheet or how to formatting the balance sheet in the Excel this is the balance sheet format you have already seen in the chat jpt that is also given the format of the balance sheet correct so this is how it looks like and I have taken 3 years of information so mainly first we need to write this heading whatever you are going to prepare it may be balance sheet General entry trial balance Ledger account for each and every account you need to write the headings first so this is we are preparing the balance sheet correct so balance sheet as on we need to write if for example only one year that is 2021 we are preparing on that case or in that scenario balance sheet as on 2021 of ABC Limited or XY Z limited whatever the company name is there that we need to take okay that is the first thing next in the vertical format first we need to write the assets okay assets under that we have subcategories current asset fixed assets liabilities that is the next heading one is asset second heading is liabilities under liabilities current liability long-term liability and owner Equity so after that you will get the total assets and total liabilities and Equity so this is called dummy balance sheet and we have given the values by ourself only it is not any company's balance sheet okay we have created the balance sheet and we have given the whatever the items listed under the all these categories subcategories okay and we have given the values so this is the format of balance sheet okay and I will show you the in income statement also see here also first we need to write the heading income statement of ABC limited Aon so if you are preparing only one year then you can mention the year that is 31st March whatever the year because we are going to close all the books of accounts on 31st March of the every year correct so we need to mention the 31st March whatever the year so your Revenue expenses and then gains losses net income or net loss this is the format for income statement okay so we'll see for example if you have the basic knowledge of financial statements then you can easily figure out what do you mean by asset what do you mean by lability everything correct so if a Layman perspective okay we are not sure about the financial statements then what you are going to do you are going to ask CH gbt to assist the financial statement how to financial statement or how to analyze the financial statement so that we will do right now so in this conversation only we will move so next if I'm a Layman I'm not sure about the financial statement then I've already generated like how to create or what is the format for balance sheet correct so here also we can ask how to create a balance sheet just an example or like performer we call it as format correct so first we need to open the Excel sheet in that first one is column A account names see here in the First Column the account name cash is an account account receivable that is your bills receivable inventory short-term investment prepaid expenses long-term investment land buildings these are all our account names correct we need to write it in the first column B account types so if you have the different types of accounts okay in B column we can mention but here in the B column we have mentioned the values that is we have taken three years of information correct so that is the reason we have taken the yearly wise informations in column C amounts so we already taken the 3 years information of amounts correct so next in column a list all the account names in your financial data this would include both assets and liabilities that is example cash accounts inventory account payable long-term R retain common stock so I've already shown you right so these are all the assets and these are all the liabilities we are already taken down or list down in the balance sheet okay so next in column B classify each account as either an asset or liability to enter the amount so we have already segregated and we have classified and we have given the amount for each assets and liabilities correct so finally as for this if you prepare then sell A14 enter shareholders equity in sell 14 subtract the total liabilities sell C12 from the assets so from your total Equity you need to deduct the total current liabilities then you will get one value that value should be match with your total assets okay this is the first thing see here also this is your total current assets total fixed assets okay this two total we have taken as toal total assets and total liabilities and Equity so your total current liabilities total long-term liability plus owner Equity that should be the 3 lak1 19,200 so this two will be matching this we call it as tallied okay if both assets and liabilities are talled then only your balance sheet or financial statements are correct if it is not talled then you miss some transaction in between of the accounts okay so once we know how to prepare this balance sheet what are the items are there in the balance sheet next question is how can I use Excel formatting tools to make my balance sheet look professional and easy to read so if you prepare like this okay so we are not going to get any information for example what is the asset what is the year which year everything we need to check correct so I'm asking the question to chat GPT using the Excel tools or whatever the formatting tools are there how I can increase the visibility of my balance sheet okay if you look at that balance sheet easily we can understand so first one so chat GPT has given few examples or few instances that apply cell formatting use bold or larger font size for column ERS to make them stand up out so here we have done right so the Eder this we need to bold and it should be visible largely okay so next one apply currency formatting to monitary values to display them with appropriate symbols and decimal places if for example in Indian we are going to use as a Indian Rupee symbol correct so in balance sheet of us so what they're going to use dollar symbol for example the same balance sheet if you given to any of the lay men in India they can easily recognize and they can tell it as in Indian rupees it is mentioned correct so if the same if it is given to any other foreign citizen for example US citizen what is going to think it is in the dollar symbol or it is in the dollar values correct so we need to mention which value or which currency it is representing that is also very very important next add borders and grid lines so we have already did this correct so border or like whatever the balance sheet is there just select that go to home here we have borders so all border it looks clean correct so use conditional formatting apply conditional formatting to I highlight specific values based on certain condition for example you can highlight negative amounts in red or use color scales to represent varying magnitudes so for example I used green color that is light green color for assets that is total of assets not all the values correct so this we can easily recognize oh asset total this is total fixed assets if you want you can give different colors also current assets different and total fixed assets that is also you can give it in the different color and liability long-term current and owner's equity in the one color and these two are in one color so if you are highlighting these colors then you can easily understand oh that is very important Point see why I have here light gray color I have made is so this two needs to be matched or two needs to be total or talled that is the reason adjust column width modify the columns okay so if for example if it is like this it looks very clty correct so it is look like very small small values we can easily identify if it is in a large number then it is very difficult to check correct on that scenario give me the space for each columns or you can autoit or you can do the adjustments in the format okay cell merging okay if you want to do you can sell merge and free Spin this is very important for example if your balance sheet very lengthy then what will happen this balance sheet header that will go correct if you scroll down here then the balance sheet heading will be gone so what you need to do you need to select which this Ro I need to freeze correct so next row you need to select and go to U here we have freeze pain select freeze so if you scr scroll down also this balance sheet that will be fixed okay so that will not move anywhere okay so if you want to check see this is also you can do or year wise this information also you can freeze so if you want to freeze this both the columns for example ear wise is also very very important so on that time what you need to do if you want to freeze this row then you need to select the below that row don't select this row if you select this row it will freeze the above that row okay so you need to select the below the row and then you need to go to U and freeze SP see this is freezed okay this is also looks very good and if you are analyzing on the time it will be useful also apply Color and font style if for example if you want this two in it italic like this you can make italic or if you want to select any color or if you want to quote any color so you can coloring also it looks good so next after preparing the balance sheet we need to analyze the data correct so how to prepare the chart or graph okay if for example I need to prepare a graph for are this total current assets and total fixed assets so if you want to insert a graph so go to insert so here if I want to create a graph for total current assets and total fixed assets okay so for that 3 years I need to prepare a graph so for this we need to go to insert and here we have best suitable for all these comparison you need to go go for bar diagram see the total fixed assets is high comparing to current assets so if I want to compare for this 3 years information then how to do it so let's see I need to select the entire values and then I need to go for insert and go to bar diagram so this is how we need to create the bar diagram to analyze the financial statement so in the last session we have discussed about PV tables correct so how we are going to use the PV tables using the chat GPT and how we are going to analyze the financial statements correct that we have discussed in the previous session so in this session we are going to discuss about dashboard I think so you have heard this word dashboard correct so how we need to create dashboard and why we are using the dashboard what is the importance of dashboard that we are going to see in this session so I'm going to ask first question to chat GPT that is what is dashboard in Excel and how to use or why we are going to use or how we are going to use for creating the professional reports in chart GPT so the Excel dashboard is a visual representation of data so in the last session also we have discussed in the pivot table also how to analyze or how to create the P table and P chat and analyzing our financial statements that is our balance sheet and income statement correct so in this session also how we need to like represent our data in a simple manner in the dashboard or in the Excel using this creating dashboard options okay so it provides a Consolidated U of key metrics and information it it typically consist of chart graph table and other data so in the earlier what we are going to do in the pivot table we are going to use only like pivot table aside and one side we are going to look at the pivote charts or pivote P chart or diagram correct so here in the dashboard what we are going to do we are going to get the all these sorts that is table also one side we can visible and we can show one side the charts or your Pi diagram bar line whatever you require okay that we can create and we can show our data more visualizing and easy to understand for everyone this is the the importance or the major feature of dashboards so how to create this dashboards in Excel so these are the few steps are there so just I will go through with the steps and I will show in the Practical also first determine the purpose what is the purpose of creating this dashboard I already told you to visually represent our data it may be your financial statements or whatever the data it may be for example if you are going to collect the 2 years data of the particular share or particular company stocks or dividends debenture or sensex Nifty or top 50 that is Fortune 50 companies information you are going to collect so in that how to visually represent all 50 companies in a one Excel sheet that is also possible in the dashboard okay that is the first thing we need to understand about this dashboard what is the purpose of creating this dashboard okay so if for example only simple line graph for that no need to prepare any dashboard correct so if you want to compare or if you want to analyze few datas okay then only this dashboard will be helpful Next plan your layout according to your wants and need needs okay we can prepare the labout also so if for example if you want only Pi diagrams okay that also we can or in the same Excel sheet we can create all these sorts okay that is as for your requirement you need to plan the labout also and next gather and prepare data so I already told you right if for example Fortune 50 companies of one week data that is your opening value of the particular company shares and closing value of the particular company share correct so on that time what you are going to do you need to collect the data first if you have the data in your hand then only you need to go for creating this dashboard without data or only few data you can't create dashboards you can but that will be not the complete information correct so if you want to prepare anything the complete information you need to hold and then you need to start with this dashboard activity so next choose the appropriate visualization so appropriate visualization is nothing but we have bar diagram line diagram pie chart in the bar also candle is there okay so multiple options are available correct so for your data which is feasible okay or else which is more suitable for your data you for analyzing that particular data so you need to think and then you need to select that appropriate visualizations okay so create the dashboard add interactivity include supplementary information that is any other information for example in the chart or in the pi diagram the data table we can add correct so what just it will shows the the coloring but in that color also what is the value or what is this data represents that all those things we can do okay that is the supplementary information formatting and styles test and refine distribute share all those things are the few steps involved in the creating the dashboard in Excel so in next we will see how this dashboard looks like we'll ask CHP to give an example how this dashboard visible or how we need to do that dashboard okay so I already told you if for example that Fortune 50 company's information One Week information of opening and closing value of each stock that we can prepare so here also they are given same like examples we can create for sales performance if for example the main company having the subdivision of five units correct the main company called XYZ and subunit of a bcde e okay so these are the five subunits are there on that time the management need to analyze all these five companies monthly wise or weekly wise quarterly wise or half yearly ones they need to analyze this company or sales information or sales performance then what they are going to do for all that five subunits information they going to take or they are going to create the dashboards so that will be helpful for them to analyze all five units otherwise what we what they need to do they are going to prepare anyhow they will prepare the financial statement or income and expenses account or your pnl account they are going to prepare correct so from that also they they can analyze but it will take time but if you create this dashboard then easily visualization it will represent which company or which subunit is giving more sales or more Revenue to the organization and which is the poor and then they need to management need can analyze what is the reason for the sales is very low or how they can make better sales in the future quarters or future month or future week that is the use of this dashboard not only the sales performance Regional sales if the same company is performing entire Karnataka or in entire States then what they're going to do the region wise they can get the sales information in Bangalore also South what is the sales north east west whatever the regions are there according to that they can get the information and they can create the dashboards next whatever the product sale if one company is there means they're producing or manufacturing n number of products correct so all the product information they can collect and they can create this dashboard that will also helpful for the companies for this analyzing the product sales monthly performance it may be employees or it may be whatever the transportation whatever the product Services sales everything monthly wise also the company can use this dashboards and they can analyze and Visually they can represent the data last sales by channel so the channel of sales you know right manufacturer to dealer dealer to wholesaler wholesaler retailer agents correct so that channels also we can easily analyze through this dashboards so these are the like theory part of dashboards so we'll go to Excel sheet and we'll practically we'll show you how this dashboard looks like and how we need to create the dashboard in Excel so in the earlier session also we have created pivote tables pivote charts and we have how to analyze the financial statement through this balance sheet and income statement only we have done correct so now also we we can do this same so next we need to select whichever the data you are going to take for the dashboards or you are going to analyze the data so I'm going to take this income statement from revenues to whatever the 2020 21 information so next we need to go to data in that we need to go for from table or range we need to select this it will show which column to which column which row to which row so we need to click okay so if you create the filters correct so it is visible in the all these tables that is the first step next we need to create the pivote tables correct so we need to select all this data so creating pivot table or P charts the shortcut key is control NTV so this dialogue box will appear on the screen so it will ask select the range that is table whatever the selected table is there correct I have selected all these table because in that whatever the data we want we can drag and drop correct so that is the reason I've selected all the table information if you want to use any external data also that is also possible for example I given the Fortune 50 company share information correct so if you want the external information you can click on that and you can browse the data and next here if you want to create in a new worksheet or if it is in the existing worksheet so I need to create in a new worksheet so I clicked on new worksheet okay so next this dialog box will appear or in the new worksheet it is visible correct so now we what we can do we can select the revenues information correct the revenu is the total values so that revenues you can draag and drop to the rows column and whichever the year information you required for example you required 2019 just drop that value to the 2019 if you want for 2020 just drw that also 2020 and 2021 these are the three values correct so for the values column we have dragged up all this information next we can go here P chart is visible correct so click on pivote charts here all this information in the beginning only I told you right so you you have like columns that is bar diagram line diagram Pi a area XY Z scatter map stock surface Rader tree map all these kinds of charts are available in the Excel so you need to select which one you are going to or which one is required for your reference so now I'm taking all these three information three years information correct so on that time I can use the bar diagram if for example I'm selecting only 2019 removing all the other information that is 2021 and 2020 I removed only 2019 information I have taken so only for this I can select the bar diagram or I can select the pie chart correct so I can go to pivote chart pie chart okay see now you can easily visible all this information if you want the datas also I have already told you right so it is like look like a plain one if you want to select or if you want to check which is the orange color expenses which is the blue color cost of goods sold if you want to check like this that is also possible and values also here only it may be displayed for example go to add so here it is there data labels Legend and chart title so if you want to give chart title that also we can give or else see that is the I can give 2019 income statement so this is the title of the P diagram and then if you want the data label see I've already told you right so whatever the required information you want that may be you can okay so here see if you want the values also visible here if you want the values or if you want the which is this that is also we can type whatever is your comfortability okay to analyze the data that you can create like this okay this is I created for 2019 if you want to create for 2020 removing the 2019 see the 202020 information is available and we need to change the title because we earlier we have done for 2019 now we are doing for 2020 okay so this is how we need to create the diagrams okay this is the just one example next like this you need to create individual like 2019 1 P diagram 2020 1 P diagram 2021 1 P diagram you can select and you can paste it in the new sheet if you want to create for 20209 see this is for 2021 same copy paste for so 2019 is there 2020 is there 2021 is there so we can prepare it for 2019 also so now you got an idea correct so all this information we can create like this okay and we can put it in one sheet so in this sheet also we can remove this grid line and whatever the layout you require according to your needs okay and whatever the requirements that we can prepare here so next how to create the bar diagram horizontally okay so if for example bar diagram means only the candle one it will be looks like correct so area also we can select all those things just click here so it will comes you can go for this revenues that is the heading this is the base here correct so here we can select first you need to unselect the main information in the income statement is what is the total expenses total gains total losses and total revenues these are the four major information we required correct so that we can click okay so it will be visible here the total expenses total gain total losses total revenues correct so for this which year you required 2019 20 2021 so for this we can go and we can create the pivot charts here the pivote charts are there so in the analyze column we can see the pivote chart there we can go for bar that is clustered bar diagram so or if you can go for area also area not looks good for this information so we can go for broad diagram and click okay so look at the information here so 2019 2020 21 that is sum of sum of is nothing but the total sum you know right the formula of to calculate sum is equal to that is is equal to sum open the bracket which row to which row you need to take the total and close the bracket that is called sum so here we have taken sum 2019 2020 and 2020 21 the total revenues are there total losses total gains total expenses so you can easily compare or you can add the whatever the AIS AIS titles see titles you can add if you don't want you can remove chart title that is yearly wise yearly wise income statement correct so like this and then if you want the data labels so values what is the values just now you can see only the coloring of the bar diagram correct so if you want the label also you can add or data tables so whatever data table is visible the same table you can click on here also that will be visible here error in bars okay Legend the trm line or whatever the information re just I will click on data labels because it is useful for ours analyze the income statement so this is how we need to create the bar diagram and that also we can copy we can paste it here so you can ask one question here or if you have the clarity then well and good so I will give you Clarity here so here here we have taken like 2020 2021 and 2019 information if you want you can add the pi diagram so if you look at the pi diagram okay what is the income statement so your income statement that is total expenses L is total revenues correct so if you want to look at this 2021 information the total expenses more than your total revenues correct so revenues minus expenses what you will get you will get the profit almost similar year also in 2020 there is no major difference correct so here also expenses okay around in 50% around it is 45% of expenses is there here also around 45 to 46% of expenses is covered in this P diagram so remaining is your income that is your if you are going to deduct your Revenue with your expenses whatever these remaining part is visible correct in the white color that is your profit so weily you can analyze 2020 and 2021 information so here why this bar diagram we have created yearly wise means easily we can recognize or we can interpret see in the total revenues the gray color is visible 2021 Red Is 2020 and blue is 2019 correct so comparatively yearly wise the revenue of this whatever ABC company is there see gradually it is increasing year by year here we can easily recognize that particular year how much is the expenses how much part is the revenue correct but if you see look at the here you can easily analyze see 1ak 60 1 lakh 95 and 230,000 so gradually it is increasing yearly wise correct so this is how we need to create all these Pi diagrams or whatever the bar diagram everything we need to create and we can do it here and here also we can do if you go for here so in the view Axis or in the view tab so you can visible all these grid lines right you can remove or how you can require for example the companies what they are going to do after analyzing each statement they are going to take the print out and they will make it in the file or they're going to make a file correct so but for the future reference also they required on that time how it will be looks like when they go for printing all these informations you can easily create and you can easily do in the dashboards and in the balance sheet also we can create for example so we can select and go to data format table or range so we have selected only this information correct you can give the information here like balance [Music] sheet so in the First Column that is 2019 2020 2020 21 you can give the year also 2019 2020 and 20221 so and then you can go for creating the year also you can create the pivote table or pivote charts whatever the required for you people so we need to select all these data and then go to insert click on pivot charts okay now it is visible click on the balance sheet or whatever the information earlier also we have discussed that we required only for example the total assets total current assets total current liability total fixed assets total liabilities and total long-term liability and total owner Equity so just click on okay so select it will be visible 2019 20 2021 we can easily like if you compare with the earlier image or earlier bar diagram we have created in the horizontal format correct so it is in the vertical format so it is very easily we can recognize which is feasible for our company or to analyze the bar diagram or analyze the horizontal or vertical bar diagrams here also whatever the required you can give the chart typ title or data labels or data table error based whatever you required you can just click on this you will get it okay so and then you can copy paste this also in the whatever the we created for analyzing the data just you can click here this is your yearly voice income statement and this is your yearly Voice balance sheet so and then we can create the dashboards or in the one sheet we can use this all this information visual represented data and we can easily analyze the financial statements and we can easily interpret the future datas also thank you so in this particular video we will look at the what are the ratios okay so we have so much varieties of ratios are there and we can easily analyze our financial data through this ratios so first we'll know what are the ratios are available and how it will be helpful to analyze our financial data so so our ratios first they are going to give us the performance evaluation so how the company performance is or how the company's netw worth is going on so in this data we have taken three years information correct so in that three years how it will be reflecting or how it will be the growth or it may be declining or fluctuating variation that we can easily evaluate through this financial ratios profitability analysis so current year we have 45% of grass profit and 30% of net profit for this current year and we can compare with the past data so in the same last year how much we have got the grass profit net profit that to last year how much we have got so using this financial statement or financial ratios okay in that financial statement and analysis we can easily find out the profitability analysis liquidity and assessment efficiency and asset utilization so if proper utilization of assets and liabilities in the organization then only you can get the more profit or you can get the good profit correct so if not then you're not going to get the good profit also debt and solvency analysis The major portion or more interesting part in the financial statement is debt and equity so how much is your own fund that is owner's fund and how much is third party's fund that is others Fund utiliz in the organization and how they are going to enhance their profitability position that is very very important market performance Trend analysis okay benchmarking and comparison for example last year the company made a profit of 50,000 crores that is the net profit of 40% okay okay so based on the previous year profit percentage or net profit so we can set the goal for this current year how much is required or how much is our the target okay we can reach the Target and we can make the profit that is how we need to use this financial ratios so for our data that is our income statement and for our balance sheet okay we'll see what are the ratios we can prepare so next we see how we can use this ratios and how we can in the Excel we can prepare this calculation okay so Roi Ro is nothing but return on investment if the investors or the public if they're going to invest their money in the organization or in your company so what they requir they require the return on their investment how to announce their return on investment return on equity so Equity is nothing but the owners okay they have invested their money so how to increase that money or how to increase the value debt to equity I already told you this is very very important how much is the debt and how much is the equity is combined in the capital or making the capital structure in the organization profitability ratio liquidity liquidity is nothing but easily convert into Cash okay for example stock or inventory that e easily we can convert into Cash correct but the fixed assets land and building furniture or your machineries equipment that we can easily convert yes that also we can convert but it requires time correct so this liquidity is nothing but easily we can convert your accounts receivable or bills receivable that are the few examples for liquidity and solvency ratio solvency ratio is nothing but so they need to repay the money for example they need to repay the debenture holders or they need to pay the interest or they need to repay the debt for example if they takeen loan from bank they need to repay that loan correct so all those things will comes under the solvency ratio efficiency ratio the turnover ratio so for example inventory turnover ratio so inventory turnover ratio is nothing but the one cycle if for example if you want to produce one product so first what you are going to do you are going to purchase raw material from that raw material you are going to process that will be work in progress and you're going to add some other products or anary Services you will get the finished product so that finished product what you are going to do you are going to sell it to customer or your are dealers okay you are going to sell and then you are going to receive the money correct this is called one complete cycle so how many times we are going to make this turnover in a year the more number of turnovers are there your company is having the more profitability that is the sign of this turnover ratio so here we can easily check the total debt total debt is nothing but the long-term debt and your total Equity correct in this balance sheet for the year 2019 so they have the total of 150,000 of equity the debt is 135,000 okay the owner fund is 1 l50 and Outsider fund is 1 l35 here the owner fund is 1 lak 65 and the this one 1 lak 126 1 L 157500 and 13,500 so yearly wise it is like decreasing and in the last year it is increased so the balance so first year it is 1 l35 and the second year it is comes to 1 lakh 126 so 9,000 value okay that is they have repaid okay that we need to check and current ratios current ratios is nothing but the current assets divided by current liability total of your current assets just an example here your current ratio current ratio is equal to current assets divided by current liability your current assets total is 41,00 divided by current liabilities is 1 lakh 6,200 so into 100 so 38794 per. so your current ratios for the 2009 is 38% so if you compare with the next year that is 2020 46,800 divided by 1H 16,800 in to 100 40% and the last year that is 2020 21 current assets divided by your current liabilities into 100 so you can easily understand that so first year it is 38 second year 40 and 39 so the ideal ratio for this current ratio is 2 is to 1 if your current assets is two your current liability should be one so in this case 41,00 is the current assets and the current liabilities is 1 6,200 so more than current assets current liabilities are there so this company needs to work or they need to focus on this current assets because ideal ratio is 2 is 1 asset should be two liability should be one but in this scenario it is vice versa correct so here assets is 41,00 and liability is 1 lakh 6 so 60,000 approximately 60,000 is the difference correct so it is more than three correct current liabilities is three current assets is one but ideal ratio 2 is to one asset should be two liability should be one so this company needs to take care of this current assets and liabilities their current ratio is very poor so like this we need to compare and we need to check the information so next debt equity ratio for example debt equity ratio the formula is equal to debt divided by Equity into 100 so your debt is 135 divided by your total Equity is this value into 100 90% so because your total Equity is more than your debt you use the bonus fund more and you use the debt Outsider fund is very less so like this we need to calculate the ratios and we need to check so now nice basic level tip that I'm going to tell is go ahead and click on the corner of the tip and then click the any column that any of the column that you want to get it aligned see now the entire column gets aligned properly and now you can read the entire columns perfectly so now we have to calculate the discount so to in order to calculate the discount let's put in the formula so equal to I'll put in a bracket so the number of qu quantity of items into the price of the item and then I'll put in the discount percentage Yeah now I'll go ahead and hit enter now you just have to select the range of data that you want to remove duplicate from now go to data and then you have an option off here that is remove duplicates now the remove duplicates can be if if the names are the same you just want to remove the name or if you just want to remove the places which are the same or if this is a kind of and and or filter now let's move on to the next category that is shortcuts for navigation so under this category I've listed down the shortcut keys that are used for the navigation purpose on the Excel workbook and on the right side I have given the theoretical explanations for the roles of each key that it performs next we have the shortcut keys that are control page up and control page down these keys are used to switch between sheets inside a workbook so basically control page up moves to the previous sheet and control page down moves to the next sheet so basically the F2 key is used to edit any preious existing content in a cell [Music]