 Hello and welcome to basic to advanced Microsoft Excel course. This course will give you a deep understanding of Microsoft Excel formulas and functions that transform Excel from basic spreadsheet program into a dynamic and powerful analytic tool. While most Excel courses focus on simply what each formula does, I teach you through hands-on contextual example designed to showcase why these formulas are awesome and how they can be applied in number of ways. You will have lifetime access to project files, quizzes, form exercises, and one-to-one expert support. You will start with basics, building a solid foundation that will give you further knowledge as you progress into intermediate and advanced level topics. After completing this course, you will be able to write powerful and dynamic Excel formulas from scratch, and you will be able to automate your workflow with Excel. You will learn unique tips and techniques that you won't find in any other course. You will be able to write advanced conditions, tech, date, and lookup functions. You will be able to create with tables and charts to visualize your data. Who this course is for? This course is for Excel users who want to learn how to write advanced formulas from scratch, also anyone hoping to expand their analytical skillset, working efficiency with data, and take their career to new levels. Students looking for engaging and hands-on and highly interactive approach to Excel analytics training. Excel users looking to build powerful analytical thinking and business intelligence skills, or anyone looking to pursue their career in analytics, data science, or business intelligence. After completing this course, you will get a certificate that you can use to showcase your newly gained skills to your colleagues through LinkedIn or to your potential employers. You can explore the landing page for more details and other preview videos and the curriculum. So, what are you waiting for and roll now, I will see you in the class. Microsoft Excel is a spreadsheet program used to record and analyze numerical and statistical data. Microsoft Excel provides multiple features to perform various operations like calculation, pivot tables, graph tools, macro programming, etc. It is compatible with multiple operating systems like Windows, Mac, Android, or iOS. So, basically Microsoft Excel is a spreadsheet program that is used to record and analyze numerical and statistical data. So, basically you can perform different kind of operations on your data. Either it is mathematical calculation or creating a pivot table or if you want to visualize your data. So, basically Microsoft Excel is a collection of rows and columns. All the columns are represented by alphabets and all the rows are represented by numbers and the point where column and row meets that point is called cell. So, within this plane, each cell have its own location or we can say that its own address. Congratulations on deciding to complete this course. Just by showing up, half of your work is already done. I hope that you will maintain the same energy and enthusiasm throughout the course and you will finish this course. Only the students like you encourage us to keep making these courses and keep on adding more and more value to these courses. Our goal is to continue making five star courses and keep on updating the resources, test series, exercises, assignments and video lectures. Also, we are committed to answering all your doubts and queries. Let me tell you something about Udemy course review system. After a few lectures, Udemy may ask you to rate this course. We request you to rate this course and provide your honest feedback about this course. Your opinion is not only valuable to us, but also to your fellow students. It will play a big role in shaping our future course material and it will help your fellow students to decide the best course for them. If you feel like you haven't watched enough lectures to rate this course, you can skip it and give your ratings later on from here. If you face any technical difficulties like there is no voice or video is blurred, you can change it from here. If the problems still persist, you can contact Udemy's technical team. I assure you that all our videos are checked by Udemy for quality. You will face no problems. If you are not familiar with my accent or you have hard time understanding it, you can see the transcript or you can enable captions. If you want to take a note during the lecture, you can do it from here. If you feel like the speed of this course is too fast or too slow, you can change the speed of the video from here. Other than that, if you have any doubts, questions, queries or suggestions, you can ask them in Q&A section. I will personally answer all your doubts within 24 hours. I want to remind you again that you should rate our course and give your review. It will help us in making a better course. Also, it will help your fellow students to decide on the best course. So, all the best and complete the course with full enthusiasm. So, this area over here is called ribbon. This is called ribbon bar. So, ribbon provides you all the tools that you need to manipulate your data or for calculation and analyzing the data or structuring the data or making it beautiful. You will find all those tools over here and these are the seats and all of your seats will be represented over here. So, currently we are at seat number one. We can switch to seat number two, three or you can create a new seat over here by clicking. You can see that we have now seat number four, seat number five. So, we can create as many as it we want to and you can rename these seats by right clicking on them. You can just click on rename and you can give it a name. Budget, we can give this name. So, also, you can delete this seat from here or you can make a copy. This button over here is zoom button. So, you can zoom your seat, you can zoom the cells and on this ribbon area, we have these different tabs called home tab, insert tab, page layout, formulas, data, review, view. Also, you can customize this ribbon bar. To customize this ribbon bar, you go to the file and if you go to the options, this kind of window will open and if you click on customize ribbon, you can customize your ribbon and as you can see that all these main tabs are displayed on this ribbon bar. So, you can add or remove any tab from here. So, let's say we remove this review tab and add this developer tab over there and if we click OK, so you can see that review tab is gone and we have this new tab over here called developer. So, you can customize your ribbon bar. Also, you can create your own tab for that you go to the file, click on options, click on customize ribbon and click on new tab. So, in new tab, you can give a name. So, to give it a name, right click on it and select rename and you can provide any name over here. Let's say we write job C and click OK. So, you have created a new tab. Also, we can remove these sub tabs, right click on it and click on remove. So, that is removed now. So, that sub tab is removed from there and if we want to add anything, we can add from this pool. You can change this pool from here. So, if you click on all commands, then you will see all the commands that are present in Excel over here and if you click on popular commands, you will only see the popular ones and you can select any option from here and you can create groups or sub tabs from here. If you click on new group, you will get the new group. You can rename it. Either you can select any option from here, any symbol from here or you can give a name. So, if we give it a name, commands and we can select any symbol from here and click OK. So, now our new group is created. So, we can add anything in this group. We can select any command from here and we can add that command over there. So, if we select this one, click on add. So, this command is added into our group. We can select something else, let's say table and if we click on add, then that command is added over there. We click on ascending and we get command over there. So, this is how you can create your own tab and if we click on OK, you can see that our job see tab is created over here and if we click on it, we got all the commands that we selected over there. We can customize our spreadsheet even further. To do that, let's go back to the file and click on options. So, other than this customized ribbon, we have all these other options over here. So, in journal setting, you have all these options over here. So, you can customize your spreadsheet using these options. Let's try to change the theme of our spreadsheet. To do that, you go to color scheme and by default, silver is selected, but if we select some other theme and click OK, you can see that the theme of our spreadsheet is now changed. So, you can customize like that and if we come to the formulas in formulas tab. So, in formulas tab, you can, you got all these options over here. So, we have this workbook calculation. We set it to automatic. So, that is quite handy and save a lot of time. So, there are bunch of options over here to choose from. So, you can customize how formulas will behave from here. Another tab is proofing. In proofing, you can check or uncheck these options. So, while writing should actually check the spellings or what it should ignore or what should it consider, you can decide it from here. So, these are the options that you can use. In save tab, you can decide where your worksheet will be saved once you save your workbook or worksheet. So, these are the locations. So, auto require file location is this one. You can change it to whatever you want and default file location is this one. So, you can change according to your preference and we got language option over here. So, by default English US language is installed. So, you can choose any other language if you want to. There are other options that you can use. You can go through them one by one and you will understand what these are. So, you can customize all other things from here. So, if we close it, so let's click on OK. So, these are the different customization options that are available to you. There are few shortcuts that you should be using while working on spreadsheet to increase your efficiency and the shortcuts are. So, first option is control P. Control P is used to print the dialogue window, which means you can print this window. So, you can use this shortcut control P to print your excel sheet. So, now you can see this line over here. So, this will be one page. So, while printing column A to I will be printed in one page and then columns J to R will be printed in second page. So, this line over here represent the page breaks and this will be one page and this will be the second page and from beyond this page break will be printed into another page. So, if we look at our bottom. So, our first page will be from first row to 47th row and from A column to I column. So, this is how your spreadsheet will be printed. Another shortcut is control N. Control N is used to create new workbook. So, if we click control N, if you see over here now we are working on two workbooks. So, you just created new workbook by using control N. So, let's close this one. We use control S to save our current workbook. We use control C to copy the cells or copy our content. So, if we click on control C and we can paste it wherever we want to. So, you can use control C to copy the contents and control V is used to paste them. So, these are the basic shortcuts that you should use to increase your efficiency while working on Excel. In this video, we will learn about data validation. So, data validation is used to avoid the mistakes that can be avoided. For example, many times when we insert our data, sometimes we insert some wrong values in it or we can insert something that don't make any sense. So, using data validation, we can put a check on our data that we are inserting. So, to use data validation, select the cells where you want to use data validation. Go to the ribbon and click on data tab and here you will find data validation. Click on this option. So, if we click on this drop-down menu, we can select any number from here. So, if we select whole numbers and here we can select what we want to do with these whole numbers. If we want to give some range that our whole numbers must be in between these two values. For example, let's say minimum and maximum value. If we provide that value over here, then you can only insert values between your minimum and maximum value. If you try to insert something out of your range that is out of the range of minimum and maximum value, it will show you an error and you can't insert that value there. You can also select note between that your value should not be in this range or there are other options that your value should only be greater than this or not equal to this or only less than a particular value. So, let's choose less than and if we give a value like 100, we don't want score to be more than 100. That is not possible. Let's say in this case that is not possible. The maximum score you can get is 100. So, you can't insert anything beyond 100. So, this is how you can put your condition or create your check. In the input message, you can provide a message that will be shown whenever that particular cell is selected that you can only insert this kind of value. Let's say the message is values between 0 and 100 and let's create an error message too. We can select whether we want a warning or information or we want to stop someone to inserting any value in that. We can provide the title of our error. Let's say we write. So, if somebody insert a value that is not in between 0 and 100, then it will show this error. So, if we click OK and if we select the cell and if we try to insert 105 over here, it will show an error. The value is not between 0 and 100. So, you can't insert this value over here. So, this is how you can use data validation to avoid any potential mistake. In this video, we will learn how we can do mathematical operations on our numerical data in Excel. To do that, we will do addition, subtraction, division and multiplication. So, for that, we created this table over here. Here we got serial numbers and here we got the names of our operations. Here we got our first number, second number and here we got our result. You can see that the length of this D column is larger than the rest of the columns. Also, you can't see this first number properly in the cell because the width of this cell is lesser than the length of our text. So, you can change the width of your column. To do that, you take your cursor over here, just in between two columns and you will see these two arrows. And if you click on it and drag it, you can increase the width of your column. So, let's do it again for F column, little more. And same we can do for our D column. So, as you can see now, we can able to see complete text for every cell. Now, you can see that the alignment of our text is towards left hand side and alignment of our numbers or numerical data is towards right hand side. We can change that if we want to. For example, if we want the serial number to the right hand side, we can do it from here. So, these are the alignment options we can use. In home tab, we go to these alignment options over here. So, if you click on this, you can see that now serial number is aligned towards right. For middle, we can use this one and towards left, we can use this one. Also, we can choose multiple cell to do any operation. So, if we choose all four of these cells, now we can do this operation on all of these cells. So, when you choose multiple cells, that combinations of cell called range. So, this is the range we got over here. And like if you select these many cells, so now the range will become this big. So, there must be an address to this range. So, the address of this range is the first cell where this range is started. So, this one over here. So, which cell is this? This is D11. And if we mention this cell, that is I22. So, this whole range can be represented by the combination of these two cells. So, address of this whole range is the combination of this first cell and this last cell. This is how we define a range or assign a address to our range. Other thing that you can do is you can choose any font you want to use in your text. So, by default over here in home tab, we got a font that is automatically assigned to our text. You can change this font and you can choose any font you want to. Also, you can choose the size of your text. So, by default it is 11. You can make it like 20. Then our text will look bigger. Let's change back to 11. You can change the size of your font from here too. So, if you click over here, it will increase the size and you can decrease it from here. Also, you can make your text bold if you want to from here. You can make it italic or you put a underline over your text. There are one more option to put double underline. So, if you select it and if you click on double line, then you will able to see double line on your text. As we learned in our previous videos, you can zoom your seat. So, if we zoom it, we can see it properly. So, this is how you can zoom your seat. Also, you can highlight all the cells where your data is present. So, our data is present in these cells. You can highlight these cells from this option where it called borders. So, currently no borders are assigned to it. So, we can assign borders to cells. So, if we select all borders. So, you can see that all these cells are highlighted. You can see border on these cells. You can choose different type of borders. So, you can choose from this list. You can make it really thick border. So, as you can see that the outline is quite thick and line inside these bigger box are little smaller. So, you can choose whatever you want to. So, now let's do this mathematical operations on these cells. So, to do these mathematical operations, we have to use formulas that are already built in Excel. So, you do not have to manually add these numbers or subtract them. You can use inbuilt formulas of Excel. So, to use your formulas, you write equal to sign and whenever you write equal to sign in your cell, Excel automatically consider that as a formula. So, you can add two cells. So, our numbers are present in F5 and E5. So, to add these cells, we select the first cell which we want to add. Then we put plus sign and then we select the other cell and we hit enter. So, if you do that, then these two cells will be added and you can see your formula in this formula bar. So, whatever formula is used within a cell, you can see that formula over here. You will see the result of your calculation in the cell but you can see the formula over here. You can add some other cell into it. So, if I write one more plus, now we can select any other cell and if we hit enter, then that cell will also be added in our result. So, because we don't want that, we remove it and hit enter. So, you can see the result of these two cells over here and same goes for subtraction. To subtract one cell from the other, we write equal to sign and then we select our first cell, then we write minus sign and then we select our second cell and if we hit enter, you will get the subtraction in our new cell. You can subtract this cell from this cell. To do that, you will write equal to sign. This time select the second number first and then insert minus sign and then select the other number. If you do that, you can see that there is minus 33 over here. So, we don't want that. So, let's subtract 3 from 36. So, we got 33 over here and same goes for division. If we write equal to sign, then we select our first cell, then we insert division sign and then we select our second cell and if we hit enter, we got the result and same goes for multiplication. If we, sorry, if we write equal to and we select our first cell and then we insert multiplication sign and then we select the other one and hit enter. So, we got our result over here. So, this is how you can use the simple operators in Excel. In this video, we will learn about filters. So, if we want to filter our data, we can use filter. So, we can put a filter on our data and we can display our value according to that filter. So, to use a filter, let's say we select all of our data and to use filter in home tab, we will go this icon over here, sort and filter. We select it and if we select this filter over here, then a filter is added on all the columns. So, if we select this one over here, we can see all our values over here and like if we uncheck all the age and hit okay. So, all the rows where it was present in the score column will be removed from the display and one thing you should know is that row is not deleted from our data, it's only hidden. So, if we go back to our filter and if we check it again, we will again see that row over here. So, there are other options. So, if we go to number filters, we got all these options that is top 10 above average, below average. So, it will calculate the average of our numbers and it will only show the rows which are above average and so same goes for below average. So, if we select it, it automatically find the average of all these values and it only showing the values that are above average. So, if we go back and if we select below average, then it will only show the value that are below the average and we can provide two values. If we select between, then we can provide two values over here that and we can select all the options from here. You can check them one by one and if you want to see all the rows where score is between 50 to 90, we can do that. For that, we have to select is greater than or equal to. So, if we select it and if we provide our value over here, let's say 50 or greater than 50 and we can select another value less than or equal to let's say we write 100 over here. So, if we click OK, then it will only show the values that are above 50 and below 100. So, you have all these options over here less than or greater than you can choose anything. There are a bunch of options. Also, you can choose custom filter. So, custom filter is very similar to our between filter. So, we can use that. We can also provide conditions like we can choose and or or. So, there are a bunch of options that you can choose from hit OK and if you want to uncheck it, we can just click on select all and hit OK. So, then we can see all of our data and if we try to put filter on our this text column that is names. If we go to the text filters, we got all these options over here. So, if we go to this option, let's say begins with and if we write J, so all the names that start with J will be displayed then and if we click OK, then we got all the names that starts with J. So, this is how you can use filters. So, you can sort your data in ascending or descending order. So, if we select our data and click on sort smallest to largest, we got a voting over here. The voting is Microsoft Axel form data next to your selection. Since you have not selected this data, it will not be sorted. So, basically we only selected this one column. So, if we sort our data according to this column, so Axel gives us two options, whether to expand our sorting to all of our data or continue with current selection. So, let's continue with current selection and click on sort. So, it sorted our data. But one thing you should notice is now scores are changed. Earlier it was not assigned to James. So, basically changed the data. So, you need to be very cautious about it, whether you want to do it or not. So, if we only select this column and click on sort, it will ask to expand your selection. So, if we select this option over here, expand this selection, then everything else will also be sorted according to this column. So, this is how our table looks like now. You can change it to largest to smallest. Select expand this selection and click on sort. So, now our data is from largest to smallest. In this video, we will learn about grouping. So, we can group our data in a particular way. So, we have some data over here. So, if we select our data and if we go to data tab, we can group our data in a particular way. So, if we click on group, we have to select we want to group our data in terms of rows or column. So, let's click on rows and click on okay. So, our data is grouped now. So, you can see is line over here and this minus sign over here. So, all the rows from third to nine is now grouped. So, if we click on minus sign, you can see that all those lines are collapsed. To expand those lines or all those rows, we have to click on plus and we got our data over here. There is one more option. We can ungroup our data using ungroup tab. So, if we click on it and click okay. Now, we have ungrouped our data and we ungroup the rows. There is one more option over here called subtotal. So, we can categorize our data using subtotal and we can separately find some calculation for particular data. So, if we select our data and if we select subtotal. So, we can select the column for which we want to create a subtotal and let's say we want to find out average of scores. So, we want average of scores for genders. So, if we click on okay, what it will do is it will divide our data based on gender and it will also group our data. So, this line shows the grouping of data and the smaller line represent the sub grouping. So, it categorize our data based on gender and it find the average scores so our first entry was James that was male. So, we got average of all the males but here it was only one male then it went to Mary. So, this was only one female. So, we got our average over here but Robert and John both of them were male. So, all the males were grouped and we got our average for all the males then there were two females. So, all the females were grouped and we got our average for those females and we got our total average over here. The better way of doing this grouping is we select our data and let's go to the sorting and let's sort our data first. So, we have sorted our data and if we do the same calculations now let's go to the data and sub total and we select the same things and click on okay. So, this make more sense and this is better way of representing our data. We sort our data so that all the females are combined together and all the males are combined together. So, we got the average for females and we got the average for males and we got average for all of our entries. So, this is the better way of grouping the data here we got only two groups but if our data is not sorted then it will create many groups. So, this is more efficient way of grouping or sub grouping. In this video we will learn about some common functions that you can use in Excel. So, we have a data over here this is a home budget data. So, we have our grocery items over here and we have their quantity and we have their price and if you want to calculate total price for each category we can use mathematical formula we can write equal to then we select our cell then we add multiplication sign and then we select our second cell and if we hit enter we got our multiplication over here. Now, we have to do the same thing for rest of these items but we don't have to type the formula every time for all these items we can just select the cell and go to this corner you see that the plus icon is changed and when it changed you click and drag it down. So, if I click on it and drag it down Excel will automatically apply the same formula for rest of the rows and you can see the formula over here. So, the formula is h9 multiplied by i9. So, this h9 is multiplied by i9 and if we select this cell we can see the formula over here h10 is multiplied by i10. So, we can use this feature of Excel to avoid repeated to work. Another way to fill up these rows automatically we select the cell we go to the corner when plus icon changed just click on it two times if you click on two times it will automatically fill rest of the rows. So, you can either use dragging method or you can just click two times and it will do the same thing and if you want to find out total quantity we can use equal to sign then we can select our cell then we can add another cell to it and we can repeat this process till we reach our last cell and so we reached our last cell and if we hit enter it will provide us with the result. But this is not the most efficient way of doing it if there are thousand rows and you want to calculate the result for thousand rows it will be really repetitive and hectic to select every row to add all those rows. So, if I remove this result we can use inbuilt sum formula. So, home tab if you go over here you can see this addition icon over here if you click on drop down you can select the sum formula from here and it will automatically select the range for you and if you hit enter you will get the result or you can type the formula on your own if you write equal to sum and within bracket if you select all the rows which you want to add then it become a range that is h8 to this column represent to we are selecting from h8 to h12 this cell is represented by h8 and this cell is represented by h12 and if we close our formula and if we hit enter we will get our result and now we can just drag this cell to other cells and same formula will be applied to all these other columns too. So, this is how you can use sum formula you can make it bold. So, we have this total sum over here sub total over here and this is how you can use sum formula. In this video we will learn about minimum and maximum formula if you want to minimum value from these cells we can write equal to min and we can select our range and if we close it and hit enter then we will get the minimum value from all these cells and if we drag it. So, we will get minimum value for price minimum value for sub total and you can see your formula in the formula bar you can also find minimum formula from here in the similar way you can find the maximum value. So, if I write maximum and if we write equal to sign and max within brackets we can select our range and if we close it and hit enter we will get the maximum value and we can drag it down. In this video we will learn about average function so we can find out the average of all these quantities. So, to use average function write equal to average and we can provide the range that we want. So, we have provided the range and if we hit enter we will get the average of all the quantities and if we drag it down we will get average value for both of these columns too. There is another function called average if so we can put a condition that if only if the condition is satisfied then only calculate the average. So, if we use this function then we provide the range so we have this range and after comma we can provide our condition. So, within course if we write greater than 3 so what it will do is it will first check this condition if the value is greater than 3 then only this formula will consider that value. So, there is one value that is lesser than 3 so while calculating average this average if formula will ignore 2 and calculate the average for 6, 5, 5 and 6. So, if we hit enter you can see that we have a new average over here this average is for 6, 5, 5 and 6 so we get 5.5 and if we drag it and you can see that here the average remains the same because all the entries in price column and subtotal columns are greater than 3. So, that is why it is considering all the rows. In this video we will learn about count function so we can find out the number of quantities. So, if we select a range then we can count number of entries in that range. So, to use count function we write equal to count and within bracket we will mention our range and if we hit enter we get the number of entries and if we drag it we will get the same value. Also you can select multiple ranges so to select multiple ranges you just insert comma and then you can select another range and if we hit enter we get the count value of both of those ranges. So, you can select multiple ranges using comma. From this video we will learn about some numerical functions. So, first numerical function that we will learn is checking whether a value is a numerical value or not. So, if we want to check if a value is numerical or not we can use equal to and if we write is number and then we can select the cell for which we want to check whether the value is numerical or not and if we hit enter it will return a boolean value. So, there are two boolean values true and false. These two values are called boolean values. So, boolean values are used to represent whether a statement or condition is true or not. So, if the statement or condition is true then we will get true value and if it is not true we will get false value. So, because two is a number so we get true over here. Let's write our formula name first. So, we got this value and if we check it for text, if we select a text and if we hit enter you can see that we got a false value because the text is not a number. In this video we will learn about random function. So, random function is used to create random values between 0 and 1. So, if we want to create random values then we will use random function. So, if we write random and if we hit enter we will get a random value and if we drag it down so we will get values that are between 0 and 1 and all these values are random values and one thing you should know is these random values will change whenever you refresh your worksheet. If you don't want to do that you can just copy all these values and go to the paste. So, if you go to the paste you will get all these options over here. So, you do not want to paste the formula. If you directly click on paste it will not paste the values but it will paste the formula. So, if you don't want that you go to the paste click on drop down value and click on values. So, if you click on values it will only paste the values not the formula. So, as you can see that now our spreadsheet is refreshed so the values are changed now. So, we got new values over here now because here we are using random function but these are static values these are not formulas so these will remain the same. You can also provide a specific range that you want the values in between. So, you can create random values between two specific values. So, to use this formula we will write equal to random between and then you will provide the bottom value let's say we want to create random values between 50 and 100. So, first value will be the bottom value and second value will be the top value and if you close it and hit enter you will get a random value between 50 and 100. So, if you drag it down you will keep getting random values that are between 50 and 100. So, that was random function or random between function. In this video we will learn about round function. So, round function is used to round off the decimal values. So, if we have a value and we want to round off digits after the decimal let's say we only want two digits after decimal from this number. So, we can use round off function. To use round off function we will write equal to we will write round and within brackets we will select our number and after comma we mention how many digits we want after decimal. Let's say we want two digits after decimal and if we hit enter then it will remove all the digits after two decimal points. In this video we will learn about median function. So, median function is used to find out the medium value from a range of values. So, to calculate medium we will write equal to median and then we can manually give the numbers like two, three, five, eight and if we close it and hit enter we will get the median value. Also we can select a range. To select a range we just write median and then we can select the range and we will get the medium value for this range. So, we get the medium value over here. In this video we will learn how we can find out power of a specific number. So, to use this function we will write equal to power and we will give the number let's say we want the power of two and after comma we provide the power. So, let's say we want two to the power of four and if we hit enter we will get our value. From this video we will learn about string functions. So, first one is left function. Using left function we can take a part from a string or text. So, we have this text over here called job academy and if we want to take out some part of this text we can do that using left function. So, to use left function we will write equal to left and within bracket we provide our text. So, this is our text and after comma we provide the number of characters that we want from left side of our string. So, if we provide let's say five characters then it will take five characters from the left hand side of this string and if we hit enter so we get the five characters from this string from the left hand side. In the similar way we can use right function. In right function we can get the part of our text from the right side. So, if we write our formula and we provide our text and if we give number of characters that we want let's say give three and hit enter. So, we got last three characters from our text. Not only that we can slice the text from the middle to do that we will use middle function and if we write equal to mid and we provide our text after comma we will give the start number means that the place where we want to slice our text. So, let's say slice our text from s. So, s is at fourth number. So, let's write four over here and after comma we provide number of characters that we want from the given number. So, let's say we want three characters from s. So, if we close it and hit enter then we got s, h, i. So, we got s, h, i. So, we got fourth character fifth and sixth. So, this is how you can slice a text according to your needs. In this video we will learn about find and replace function. Using find function we can find out the position where a particular character or a group of character exists within a text. So, if we want to find out this where this ac exists in our text we can do that using find function. So, to use find function we will write equal to find then we provide the text which we want to find out and one thing you should remember is that this is case sensitive. So, you have to mention your text in proper case. So, here a is capital and c is in lower case. So, we provide ac and then we provide the text from which we want to find out this substring or subtext and here we can mention from where we want to start the search. By default it will start from the first character but you can mention from where you want to start. So, let us not mention that it will start from the first character and if we enter it. So, we got this nine over here. So, ac is present at ninth position. So, there are seven character in job C and one in space. So, total eight characters and this ac is starting from ninth character. So, this is how we can find out the position of our substring or subtext. Also, we can replace the substring or subtext to do that we will use replace function. To use replace function we will write equal to replace. Here we mention our text. So, this is our text and then we mention which we want to replace. So, let us say we want to replace this ac a. So, we provide ninth position because a is starting from nine and then we provided number of characters that we want to replace. So, we want three characters to be replaced. So, we provide three over here and then we mention the text we want these three character to replace with. So, let us say we want to replace it with a b. So, we will write a b. Let us make a smaller so that we can know the difference and if we close it. So, we get our string now this a c a is replaced with a b a in lower case and b in upper case. So, this is how we can replace a substring from our text. In this video we will learn about date functions. So, first function is date. So, date function will provide us the excel code for a date if we provide year month and day in our date function. So, to use date function we will write equal to date and we have to provide the year. Let us say 2022 then we have to provide the month less is 10th and then we provide the day. So, let us provide 15th if we hit enter it will provide the date if we mention it will give us the date if we mention year month and day. Another function is day function. So, day function will give us the day if we provide a date. So, to use day function we write day and then we provide the date. So, we provide this date and hit enter it will give us the day. So, here 9th is the month and 5th is the day. Similarly, we can find out month. So, to use month function we write month and then we provide the date and if we hit enter it will provide the month and same goes for year if we write here and if we provide our date and hit enter it will provide the year. In similar way we can get the minutes if we have written a timestamp over here we can get the minutes from it. So, to get minute we will write equal to minute and if we mention our timestamp and hit enter it will provide us with the minute. In this video we will learn about if condition. If condition is used to check whether our values satisfy a specific criteria or not and if the value satisfy the criteria then it should do one thing and if it does not satisfy the criteria it should do the other thing. So, let's understand it by example. Here we have this data in our item column we have these list of items and in quantity column we have their quantity in price column we have their price and in subtotal column we have their total value. So, we have to find out whether a specific item is affordable or not. So, let's say items above 80 rupees are not affordable and below 80 rupees are affordable. So, we can use if statement to find out whether any item is affordable or not. So, to use if statement we will write equal to if and within these brackets we provide our condition. So, we want the value below 80. So, if this value is less than or equal to 80 then we put a comma and here we mentioned if this condition is true then what we want to happen. So, if this condition is true we want to print yes. After comma we will provide what should happen if the condition is false. So, if condition is false we want to print or we want to display no and if we close this and if we hit enter. So, we got our result over here so result is no we can't afford this particular item. So, if we drag it down then we will get the result for all the values. So, we can afford apples but not lemons cooking oil and oranges. So, this is how you can use if condition. In this video we will learn about if error condition. So, if error condition is used to handle errors. So, sometimes whenever we enter some value or do some calculations there may be a situation where error occurs. For example, if we divide something by 0 if we write equal to 5 divide by 0 we know that we can't do it we can't divide anything with 0. So, if we hit enter it will give us this text. So, this is a little scary text. So, it's not easier to understand. So, what we can do is we can display any statement we want to instead of this random text. So, using if error condition we can handle these errors. So, to use if error condition we will write equal to if error and then we select the cell and now we can mention what we want if the function come across an error. So, if there is an error we want to write display this is not a valid calculation and if we hit enter. So, as you can see we got this statement written over here instead of error. But if the calculation is valid then it will show the calculation. For example, if we write 5 divide by 2 and we got this value over here and if we want to use this formula let's say equal to if error and then we provide our value and if we write what we want to happen if it faces an error. So, in this condition it will not face any error. So, if we hit enter it will give us the result. So, if and only if it faces the error it will display that text. In this video we will learn about nested if statement. So, nested if statement is used when we want to include multiple conditions. So, let's say we want to be specific about where we want to buy these items. So, there are different places from where we want to buy these different items. So, for that we can use multiple conditions using if statement. So, to use nested if statement we will write if then we will mention our conditions. So, our first condition is if the contents of this cell is equal to mangoes then we want to display this text that means if there is mangoes in our this cell then we want to specify that buy the mangoes from pacific mole and if this condition is false which means mangoes are not in our cell then we want to include one more if statement. So, we will write if and then we write our condition. So, our condition is if this cell contains apples then we want to display buy from albert store and if anything else is there. So, we have mentioned a specific condition for these two items that mangoes should be bought from pacific mole and apples should be bought from albert store. For anything else we want to display local shop and if we close our if conditions and if we hit enter then you can see that for mangoes we have specified this condition buy from pacific mole and if we drag it down then you will see that the statement or condition is applied to all of these items. So, if mangoes buy from pacific mole if apples buy from albert store and for anything else buy from local shop. So, this is how we can use nested if condition. In this video we will learn about and operator. So, and operator is used to find out if the multiple conditions are true or not and if all of the conditions are true then it will written the value true or it will display a Boolean value that is true and if all the conditions are not true then it will written false. So, to use and operator we will write equal to and then we provide our first condition. So, our first condition is we want to check whether the quantity are greater than five or not. So, our first condition is this and our second condition is we want to check if the price is less than 40 or not. So, in our second condition if we write less than 40. So, we have provided two conditions over here. We can provide as many as conditions we want to and all of those condition needs to be true in order to get the true result from our and operator. So, for now we are only using these two conditions. So, if we close it and hit enter. So, we get the false value. So, our conditions were the quantity should be greater than five and price should be less than 40. So, this condition is not satisfied even though this condition was satisfied. So, still the result is false because in order to get true result both of these condition needs to be true. So, if we drag it down then we get our result. So, both of these conditions were not true for all of these items but for this item both of these conditions were true. In this video we will learn about over operators. So, in over operator we give multiple conditions and if any one of them is true or all of them is true then the result will be true and if all of the conditions are false then only over operator will return the false value. So, to use over operator we will write equal to or and then we provide our first condition. So, first condition should be so quantity should be less than five then we can provide our next condition. Our next condition is price should be less than 40. So, these are the two conditions we are providing. So, if both of these conditions are false then it will give us the false result but if any one of the condition or all of the conditions are true then it will give us the true result. So, if we hit enter we got false value for this item and if we drag it down we got true value for all of these items because at least one of the condition is true for these items. In this video we will learn about VLOOKUP function. VLOOKUP stands for vertical lookup. We use VLOOKUP function when we have a particular value and we want the data associated with that value. For example, when we use a phone directory we have the name of the person but we don't have his phone number. So, to find out the phone number we go through the directory and we find the name and when we find the name then the data associated with that is number. So, we get that number. We can do the same thing in excel. So, if we provide name then we can get the phone number of that person. So, if we have a particular value we can get the values associated with it. So, we have few names and phone number over here in this table. So, to use VLOOKUP we will write equal to VLOOKUP and in parenthesis the first argument is the value we have. So, I mention this cell because we are going to write down name in this cell that we want the number of and after comma we will select the range or all the columns or the complete table. So, we have this table over here. So, we selected all the column and then we put a comma and after comma we mention the column number where our data exists. So, the phone numbers exist in this column. So, this column is at the second position from the names column. So, we write two and after comma we can mention either true or false. True is to find the approximate match and false is used to find the exact match because we want the exact match we will write false. So, if we hit enter as you can see that we got any value over here because we haven't mentioned our name in this cell. So, if I mention the name and hit enter we will get the phone number. Let's make this column a little bigger. So, we get our phone number and if we mention any other name, Olivia and hit enter. So, we get the number for Olivia same way if we write Emma over here and if we hit enter we got the number for Emma. So, this is how you can use vlookup function. In this video we will see how we can create a pivot table. So, we have this data over here. This data is the data of Titanic ship. So, we have these different columns over here. In first column we have passenger ID. In second column we have whether that passenger is survived or not. In third column we have class which passenger class that person belongs to. In D column we have names. In E column we have sex. In F column we have age. Sib as we represent sibling or spouse. Paths represent number of parents or guardians. We got ticket number over here. We got fare and we have the cabin number and embarked which means where the passenger is headed to. So, this is the data we got. If we want to create the pivot table of this data set we can create it from here. We go to the insert tab and you can see the pivot table over here. So, pivot table is created to summarize the data. So, if we create the pivot table for this data set we click on this pivot table. Then it will automatically select the complete data and here you can mention whether you want your pivot table in this worksheet or you want a new worksheet. So, if we select new worksheet and click on okay a new worksheet is created over here and we can create our pivot table over here. So, if you look at the right hand side you can see all the column names that we can include in our pivot table and from here we can decide the layout of our pivot table. So, let's select our columns and we can select survived and if we select the survived column and class column, sex column, age, fare. So, we have selected these five columns. So, this table is created over here and we can modify this table the way we want to. So, automatically it divided the data into male and female. So, we got male and female over here and we got some of survived which means how many people survived in male and female. So, 233 female survived and 109 male survived. So, you can conclude that female survived more in this climate. In this column we got the sum for passenger classes and in this last column we got the sum of the fare. From here we can decide the layout of our table. So, if we shift the sum of passenger class to our row numbers and if we open it, in this data you can see that the age of this female was 0.75. It means she was a baby and her passenger class was three and we got another baby over here of one year, same class, we got a baby of two years. So, you can see that there were children. So, basically children belong to second, third. There was one children that belong to first class. You can see the complete data over here and same for the male. So, you can shift them around and you can decide whether you want them in columns or rows or you want their submission or you want their value to be calculated and you can change whether you want some or any other calculation. So, if you click on it you got this menu over here. In this menu you if you go to this field value field setting you can choose whether you want some count, average, mean, max. So, you can decide how you want to calculate the value. So, if we select count and hit OK. So, you will get the count for number of survived. So, this was the pivot table that you can draw. In this video we will learn about charts. Charts are used to visually represent our data. So, we have this data over here and if we want to visualize this data we can use charts. So, to create charts go to the insert tab in menu and now let's select our data. So, this is our data. After selecting the data if you click on any of the charts we got the options of all kinds of charts. So, let's create pie chart. In pie chart you got all of these different options. So, you can choose any of them. Let's click on first one and now you can see that we have created our chart and now we can visually represent the data. Now we can see the whole data in the visual form and we can see that the estimated expense on our different categories. So, this data belongs to marriage expenses. So, this is how our data looks like and you can change the theme of this graph from here. You can choose anything. So, this is how you can create pie chart from a given data and similar to the pie chart you can create column chart. So, to create column chart we can select our data and if we click on this column and now we can choose any type of column chart we want to. So, let's choose this first one. So, you can see the result of our column chart from here. There are different themes to choose from. In this video we will learn about line chart. So, similar to pie chart and column chart we can create a line chart. So, to create line chart we have to select our data. So, let's select our data and if we click on line chart we can select any kind of line chart. So, if we select this first one the line chart is created over here. Even though line chart can be used for any type of data but commonly it is used when we have to represent change in value over time. On x axis we can represent different times or different dates and on y axis we can represent value. So, this is how we can create line chart. So, similar to these charts we got other options too. So, we have this bar chart option. You can choose any of these charts. We got the area chart or we can draw this scatter plot. Also, we can draw these all other charts. Donate charts, bubble charts, radar charts, surface chart, stock chart. So, you got all of these different options to choose from. So, this is how you can insert chart for your data. Congratulations on completing the course. It shows your dedication and commitment. Now it's time for celebrations. After completing this video you will get your course completion certificate through your registered email or you can download it from here. Please make sure that you have completed all the lectures and make sure there is a tick mark in front of all the lectures. If it is missing for any lecture, you can tick it manually. If you still can't download the certificate, you can contact the Udemy customer support. I request you again to rate this course and give your review from here. All the best for your future endeavors. Bye-bye.