Transcripts
1. Lesson 1 Introduction: Hey, welcome to the course. I, you are basic user in Microsoft Excel and looking to take your Excel skills to a whole new level. Are you trying to learn the most advanced formulas, functions, and types of financial analysis to be an Excel Power user. Then this is just the perfect course for you. Welcome to Microsoft Excel, advanced course for professionals and corporates. So why learn Microsoft Excel? The most impressive thing about Microsoft Excel is that it can be used anywhere for any kind of work. Made me Data Management and Analysis, Inventory, finance, business tasks, and even complex calculations. And while it may seem intimidating at first, once you have mastered the basics of Microsoft Excel, it can make your life a lot easier. So basically, Microsoft Excel is a spreadsheet program developed by Microsoft in 1985 with the sole purpose of helping businesses compiled all the financial data, yearly credit, and yearly debit sheet. Microsoft Excel can help you solve a lot of business problems. Microsoft Excel can help you get a lot of stuff done. Xl can not only make you better at your job, no matter what job it is, but having a know-how of Microsoft Excel can instantly increase your job prospects as well as the scope for your group. Now let us look at the applications of Microsoft Excel. Excel can be used for data entry and storage. It can be used for managing a large volume of data with a lot of 0s. It can be used for collection and verification of business data. It can be used for scheduling and forecasting purposes. Xl can actually help you build great charts and professional dashboard. It can help businessmen developing future strategies. The best part about Excel is you can have an online access to all your data 24 by seven. It not only helps in data analysis and decision-making, but also helps you in managing budgets, accounts, and other financial data. And that's not all. Excel can be used in any type of business and in every sphere of life. So let's learn a little bit about the course. This advanced, excellent training course builds on our initial basic Excel course and is designed specifically for spreadsheet users who are already proficient and looking to take their skills to an advanced level. This, it wants tutorial will not only make you proficient in using it, advanced techniques, dues, and formulas in Excel. But by watching me, Will all the formulas and function right on your screen. You can easily pause, rewatch, and repeat the exercises until you have mastered them. So let's look at what will you learn in this particular course. The Excel advanced masterclass is in at learners who have basic knowledge of exit and are looking to take your X's gives to a professional level. The second part, which is an advanced part of the two-part series, is aimed at preparing you to become a power user of Microsoft Excel. The topics prepared in this particular lecture series. Challenge you as you learn how you use advanced formula techniques and sophisticated lookups, data validations, PivotTables, what if analysis and data security in Excel? So let's look at the topics that are covered in this particular course. We will start off building further from our basic Excel course and start off by learning some advanced time and database functions in Excel. Further, we will move on and learn conditional formatting techniques and rules in Microsoft Excel. We will take it further and then start creating, formatting and processing pivot tables and pivot charts. We will also learn advanced functions like VLookup, edge lookup, matched, and index. Further on, we will learn data validation techniques and rules. And then moving on further, we will learn what if analysis, data security and password protection in Excel. And no Excel advanced course will be complete without having an introduction to the macros, wherein you can go ahead and automate your daily repetitive task and not only save a lot of time, but increase your productivity. The best part about this course is every lecture comes with ample of resources and solved examples and raw data for practice and projects. There are two separate projects at the end of the lecture and the techniques to solve the project are well explained in the series. All the resources needed for you to go ahead and practice and become proficient in Microsoft Excel are available in the resource section of the course, I would highly recommend that you download all these resources in the beginning of the course. The first resource over a year, which is the new workbook worksheet, has all the resources that you need while you go ahead and follow the lecture. The raw data file will then have all the raw data to practice the techniques that you have learned through the lectures. The final lecture is about introduction to macro. And therefore, there are two resources that are attached specifically for your macro practice. And finally, last but not the least, two different projects that you would have to go ahead and complete using the knowledge and the skills that you acquire through this particular course. Each of the projects will have a certain number of tasks that you would need to go ahead and complete in order to complete your project. And the skills, techniques, and tools that you need in order to go ahead and complete the project are well-thought across the lectures. So I would highly recommend that you go through all the lectures in detail before you go ahead and start attempting this particular project. And I would be really excited to go ahead and see you complete the project and pays the screenshot of the projects in the project section of the course. If you have any questions, queries, I would recommend you use the discussion section of the course and start a conversation. And I will ensure that I get back to you as soon as possible. So what are we waiting for it there is a lot and lot that we have to cover in this particular series. So let's get rolling and I shall see you inside the course.
2. Lesson 2 Day and Time Function: Hey, welcome back. So I'll focus in this particular lecture will be on understanding the day and time functions in Excel. Now the ability to store and manipulate data and times is one of Excel's most powerful features. Using Excel's built-in Date and Time Functions, you can easily add days and months to a particular date, create dynamic database fields, and perform complex time-based calculations. However, date and time functions in Excel can be a bit difficult to wrap your head around due to the complex ways in which the program Stowe's them internally. So let's understand it with an example that we have over here. So what I have done is I have gone ahead and copied this raw data onto a blank Excel sheet. So I will straight away go to my blank Excel sheet over you. And I will start off with a function called today. And the function goes like this. So I'm gonna say equal to today, open brackets and close brackets. So I don't have to give any information between the brackets over you. And I will hit Enter. The moment I did that. It's going ahead and returning today's date. And this is second of October 2020, the date when I'm going to ahead and recording this particular video. Now let's move on to our next function over you, and that is the day function. So the function goes like this. So I'm gonna say is equal to d open brackets. And it's asking me for a serial number. In other ways, it is asking me for the date. So what I'm gonna do is I'm going to select this particular data we, you, and I am going to close this bracket and hit Enter. What it is telling me is that today's date is the second. So the day function will actually go ahead and return the date for the date that you have gone ahead and made a selection for. Now let's find out the month. So for that I'm going to use the min function. I'm going to open brackets. I'm going to select this particular date. And I'm going to close the bracket and hit enter. So it says that the month is the tenth month, which is October. Now I'm going to use the o function. So for that, I'm going to say is equal to u. I'm going to open brackets. And once again, I will select this particular date and I'm going to close it. And it returns the year, and that is 2020. Now let's look at something that we call as the NOW function. Now what does this now function do? The NOW function returns the current date and time, and it will pick up the date from the system data, whatever data and time is showing us current on my system right now, it is going to go ahead and pick those values. So I'm gonna say is equal to now open brackets, close bracket and hit enter. So it says today is Friday, October second, 2020. I will go ahead and change the format of this. So I will right click on it, click on format cell. And oh yeah, I will scroll down. And I will select this particular format. Ok. And then click on, okay. Now it tells me today's the second of October and the current time is 02:56 PM. And now let's understand what does the date function all about? Now the date function returns a date from the user supplied year, month, and day. So what I'm gonna do is I'm going to type date. Will open brackets is asking me for the year. So I'm gonna select the year from year. Okay. I'll give a comma. Now it's asking me for the month and I'm going to pick up the month from this particular cell over here. I'll give a coma one more time. Now it's asking me for the day. So my days over a year. And I will go ahead and close the brackets and hit Enter. And what it has done is it has gone ahead and combined the day, month, and the year. And it is returning those values to me in a date format. Ok, cool. Now let's go ahead and look at the dais function over you. Now, I have picked up a start date and end date over you. And what I want is I want to calculate the number of days between these two dates. So how can I do that? So for that, I'm going to make use of the day's function. I'll just go ahead and increase this row a little bit. And now I'll type my day's function. I'll open the brackets is asking me for the end date. So I'll give this particular end date and then I will give a comma. Then it's asks me for the start date. So I'm going to select this particular date and then I will close it and hit enter. So it says that between this particular start date that is first of Jan 2019 to the first of October 2019. There are overall 273 days. Now we'll look at something that we call as the network days function. Now the network, this function returns the number of whole network days, which excludes weekends and holidays between the two satellite dates. So I will select this particular cell and I will say is equal to network days. Having open the brackets, it's asking me for the start date. So I'll give my start date from year. Then it is asking me for the end date. So I will select my end date and I will close the brackets and hit Enter. So now it says that there are overall 196 network days between these two dates. Okay. What it has done is it has gone ahead and excluded the weekends and any other holidays that probably might be falling between these two dates. Now, if at all I want to go ahead and calculate network days with holidays, then what I can do is I can say equal to network days. I will open the brackets. I will give my start date from you. Give a comma, select my end date, which is over here. Then I will give a comma. And now it is asking me Where am I holidays. So my list of holidays is over here. So I will select this particular list and then I will close the bracket and hit enter. So now it says that with holidays, the total number of network days is 194. There is a possibility that one of these dates have already been calculated while calculating the network days. So even though there are three Holidays over here, it has gone ahead and selected the network days to be only 194. That's about that day functioned in exit. Now let's go ahead and look at that time functions in Excel. Now that time function in Excel is represented as a fraction of a day. For example, if I say 1 fourth of the day, that is 0.25, and I go ahead and convert it into time. Let's see what happens. So what I'm gonna do is I'm going to convert this particular cell into a time format. And what I'm gonna do is I'm gonna say is equal to this particular value. It says 1 fourth of my day is six AM, which is actually right. If I go ahead and say half of my day, and I just go ahead and drag this value. It returns the value as 12:00 PM, which is half of my day. So effect all values are given in forms of numbers. How do we go ahead and convert it into hours, minutes, and seconds? So that is what we are going to go ahead and learn in this particular section now. So, oh yeah, the numeric value is given as six o'clock. I want to go ahead and convert it into hours. So what I'm gonna do is I'm gonna say is equal to this particular value. And there are 24 hours in a day. So what I'm gonna do is I'm going to divide this by 24 and I'm going to hit enter. Okay? So now it says six means six AM in the morning. If I go ahead and drag this value, okay, it shows that 12 represents 12:00 PM. After 30 hours, it will be six AM again. After 60 hours, it will be 12:00 PM. Again. After 120 hours, it will be 12 AM again. Okay. So in fact, what it has done is it has gone ahead and converted my numeric values into hours over, you know, let's say if at all I want to go ahead and convert this into minutes, all I need to go ahead is say, equal to this particular value of a u. I'm going to divide it by 24 because there are 24 hours in a day. And then I'm also going to divide it by 60 because in every hour there are 16 minutes and I will hit Enter. So what it says is after six minutes, it will be 12-6 AM. After 12 minutes, it will be 1212 AM. And if I go and dragging this, it tells me that after 30 minutes it will be two L 3M after 60 minutes, it will be one AM. After 120 minutes, it will be 2m and so on and so forth. And similarly, if at all I want to know what will be the value of time after six seconds, 12 seconds, 30 seconds, et cetera. I can go ahead and once again say equal to this particular value divided by 24. Then I will once again divide it by 60 minutes. And in every minute that are 60 seconds. So I'm going to divide it by 61 more time. And I will hit Enter softer six seconds, it's 12 hours and six seconds. If I go ahead and drag it, it will return the value accordingly. Okay, so exactly after 240 seconds, it is 124 AM, so it returns the value exactly. Now let's look at this particular example. I just delete this. Ok. Now let's say somebody starts working at 08:00 AM in the morning, works up to 05:30 PM in the evening. And I wanted to find out how many hours as the person logged in for the day. So what I can do is, I can say is equal to 05:30 PM minus eight AM, and I will hit enter. So it returns the value as 0.40, which is in form of a fraction of the day. All I need to do is convert it into hours. And for that, what I will do is I will go ahead and put this calculation in brackets. And I will just multiply it with 24 because I need this particular time in hours. So I will hit enter. And it says that the total hours worked for the day is 9.5 hours. So that's a quick introduction to the way DN time function works in Microsoft Excel. And the way you can go ahead and use it to go ahead and display your date and time in various formats on the Excel interface. Using this knowledge, you can not only create dynamic database fields, but also perform complex time-based calculations. But that was a quick introduction to the datetime function in Microsoft Excel. I hope you enjoyed this lecture and I shall see you in the next one.
3. Lesson 3 Database Functions in Excel: Hey, welcome back. So in the basic section of the course, we got introduced to the sum, average, and count function. And in this segment we will learn how to find and activate Excel data functions, setup spreadsheets to facilitate using these functions. And then actually going ahead and using these functions in our Excel worksheet. Now these are more complex versions of the basic functions like sum, average, and count. They are part of a series known as the database functions. And they are used to interrogate list of data held on the worksheet. For a selected table of data, these functions will perform the processing only on those records which meet a specified criteria. So let's understand that with an example that we have a way you so I have gone ahead and copied this data onto a blank Excel sheet. So let's go ahead and look at our blank Excel sheet right away. So what I have is a certain database wherein I have the name of the sales representatives that a certain units that have been sold, that is a price per unit. And then based upon the sales that have been made, that our total sales that have been calculated over a year. Now you will observe that the name of the sales representative is repetitive because I can see Bob over here, then I can see Bob over here. I can see Bob over here again. And similarly I can see Cameron Novaya again. I see camera Novaya. Okay, so the data is kind of repetitive. So maybe it is the sales figures for the same representatives over a period of time. Now what I'm interested over you is I want the total seals that have been made by Bob only, okay, out of this entire dataset, I only wanted total sales value of the sales made through bulb. So what I need to do or were you in order to go ahead and use my DSM function, is first of all, I will have to use this particular header over here. Okay? And I have gone ahead and pizza. Do you? And I need to ensure that I don't make any spelling mistakes when I go ahead and do that. Okay, so sales rep, a warrior should be sales rep away your as well. And what I have also done is I have gone ahead and copied the name of the sales representative or where you're and peace to dW. Ok, so in fact, this combination is actually my criteria, okay, so serious threat equal to Bob is my criteria. And let's see how I go ahead and use it in my D some function now. So my DSM function goes like this, is equal to the sum. Then I open brackets. Ok? And now it is asking me for the database, okay? Now my database is a warrior. Okay, so I have gone ahead and selected my entire database, and then I give a comma. Now the second thing that it is asking me is field. So what do I mean by that? So if I'm going ahead and clicking on this particular function or were you, you will observe that it opens up a function argument window over you. And when I go ahead and highlight the field over here, it says it's either the label of the column in the double quotation marks on the number that represents the columns position in this particular list. Okay, so if I look at the total scenes column, it is fought in my list because this is Number one. Unit sold is number two, boy unit cost is number three and total seals is number four. So what I will do is in this field column, I will return the value as number four. Okay? And now it is asking me for the criteria. Now my criteria is listed away you okay, sales rep equal to bulb. So I will just select from h ten to 11 and then I will go ahead and click on OK. And the moment I did that, it has gone ahead and return the value as 16086. So now let us go ahead and confirm that. So for that, what I'm gonna do is I'm going to press the Control key on my keyboard. And I'm going to select the total sales made by Bob only. Okay, so I'm going to select this cell. I will select this one, like this one, and then I will select this one. And then if I go to my quick info dual, it shows the value as 16,086, which is exactly the value that has been returned by the dea, some function over u. Ok, so based upon our criteria, the sum function has gone ahead and return the value accurately. Now can I go ahead and use the logical AND function also a warrior? And for that, what I'm gonna do is I'm going to select the criteria as sales rep equal to Cameron. And I'm gonna say unit soul is greater than 230, which means I want only the total sales value where the sales representative is Cameron. And if the unit sold is greater than 230. So what I'm gonna do is once again, I will say is equal to the sum. I will open the bracket and we'll go ahead and click on this particular insert function. Then it's asking me for my database. So I will go ahead and select my entire database. So VO, it's asking me for the field. And once again, my field is total sales, which is the fourth column in my database. I'm going to select the number four and the criteria. I'm gonna go ahead and select all these four cells over here, ok, which means I wonder criteria to be set as sales rep equal to Cameron and units sold should be greater than 230. Okay? And now I will go ahead and click on ok. So it returns the value as 10688. Let's go ahead and confirm that. So I'm not going to select this value because this is less than 229. I will select this particular value here. Okay? And then let's go ahead and select even this particular value. And I'm not selecting this value because this is less than 230. And if I look at the total sales value, it's 10688, which is accurate. Okay. Now since we have learned how to use the AND function, let's also understand how do we go ahead and use the R function. So let's say I wanted total sales if at all the seals are made by Bob orderBy, albert. Okay. So what I will do is I will just add one more criteria. We'll, okay. And once again, I will go ahead and c equal to d, some having open brackets. Okay, click on the insert function. I will go ahead and select my database so we're okay. My field is number four. And this time I will go ahead and select the criteria like this, okay? Which means if my sales rep is either Bob or Albert, Just give me the toggle seals. Okay. So I will go ahead and hit enter and hit OK. One more time. Okay? And that returns the value as total sales equal to 24,722. Vetted, either the sales rep is ball or albert. Okay, so now let's go ahead and look at the D average function. Now it's a common sense that if the sum value return the sum, the D average will return the average value. So I want the average of total seats made by Cameron. So for that, what I'm gonna do is I'm gonna say D average open brackets. So now I will not make use of the inset functional option and I will just go ahead and select my database. Give a comma, give the number four. And then I will give a comma once again. And I can just go ahead and select the criteria, close the brackets and hit enter. Okay, so it returns the value as 5206. Let's see if it is correct. Okay, so I'm going to select all the total sales made by camera on a wheel. And now if I look at the average value over here is 5207, okay, which is approximately close to 5.5206. Okay? So our value is correct. Let's use the D average with the add function now. So I'm gonna say D average. Open brackets, select my database, so we'll give a comma, give the field as four. And then my criteria is a warrior. Okay? One only needs to remember that you said the criteria correctly because you're giving the criteria from the sells itself. Once you set it correctly, these functions will return the accurate value. So I am going to close this bracket and hit enter. Okay? And now it says the average value of the cn's mean by Cameron that the unit is greater than 200, is 6158. And now let's look at the d count function. And just like what some did for us, some gave us the sum, average, gave us the average. And now the count function will return the count for us. We wanted to find out the count of total sales made by Cameron in our data range. So for that, what I am going to say is equal to d count. Open brackets. Once again, select my database. Give a comma, the number four. Give a comma once again. And now I will go ahead and set the criteria as sales rep equal to Cameron. And then I will close the bracket and hit enter. So the total count of sales made by Cameron in this database is full, okay, so 1234. Okay, and now if I try to use it with the add function, let's see how it goes. So I'm gonna say is equal to d count. Open brackets, select the database. My field is number four. So I'm going to return that, give a comma. And now let's set the criteria. So I'm going to select this particular criteria with sales rep equal to Cameron and unit sold is greater than 200. Let's close the bracket and hit enter. Okay, now let's look at the data. We're, so this is one place where the total units is more than 202nd, 1 third 1. And we cannot count this because the total size is less than $200 a year. So the total count where the sales is more than 200 is three. And that is the value that has been written by our d count function. So that was a quick introduction to the database functions in Excel, where you get the sum average of the Count from a large cumbersome database based upon certain criterias. And you can also use logical functions like N and R in a combination when you are using these functions as red. So I hope you enjoyed this lecture and I shall see you in the next one.
4. Lesson 4 Saving and Retrieving Data in Excel: Hey, welcome back. So in our basic section we learned how do we go ahead and create our first excel file? And how do we save it? And if at all you might have gone ahead and done that exercise, you will realize that any excel file gets saved with an extension XL excess. Ok. And whenever you want to go ahead and open that file, all you need to do is just double-click on it and open the file. And Excel will immediately load that file in Microsoft Excel. However, sometimes you might need to go ahead and save a workbook in other file formats, like a text format or CSV, which stands for comma separated value format. Now why would you do that? Why would you actually go ahead and save and excel file in different formats? Now that is because these files serve a number of different business purposes, they help companies export a high volume of data to a more concentrated database. Now CSV files are plain text files, making them easier for the website developer to create. Since they are plain text, they're easier to import into a spreadsheet or other storage databases regardless of the specific software that the system is using. In other ways, it helps to better organize large amount of data. So in this particular lecture, we will understand how do we go ahead and first save the data in different formats. And then if at all we want to go ahead and retrieve the data and bring it back into Microsoft Excel. How do we do that? So I have this particular file away you, first of all, I'm going to go ahead and save this as a normal Excel file. So for that, what I can do is I can go to the File option. I can click on Save As I'm going to save this file on my desktop. So I will go to this pc. And I'm going to save this file on desktop. And I've gone ahead and created this folder called Excel. And I'm going to call this file as a testfile. Okay? And then I'm gonna save this. And now if I go back to my Excel folder over here, you can see that this particular file has been saved as a Microsoft Excel worksheet. Okay, I will just go ahead and minimize this. And once again, I will go back to my Excel sheet. And now I will try to save this particular file in a different format altogether. So what I'm gonna do is I will click on file. I will click on Save as. Once again, I will save this file to my desktop. I will go to the Excel folder. I'm gonna name this file as testfile one. Ok? And this particular file, I'm going to go ahead and save it as a CSV file. Okay? Now CSV stands for comma delimited file. Okay? And I'm gonna go ahead and click on Save. Ok. So now if I go back to my folder over you. I have one file which is in Microsoft Excel worksheet format. And then I have another file which is in Microsoft Excel comma separated value file format. Okay, let's go back to a file one more time. And this time I'm going to go ahead and save this file as a text file. So I will go to File, click on Save As, and then I will go to the desktop. Once again, go to my Excel folder, this file, I will name it as testfile to. Okay? And I'm gonna go ahead and save the file as a text file. Okay, so I will select this particular option that says Text tab-delimited. Okay, I haven't go ahead and save this file. And now what I will do is I will go ahead and close this plane. Now let's once again go ahead and double-click on our Excel folder where you and I can see that there are three different files over you. So one is saved as an Excel file, the second one is saved as a CSV file, and the third one is saved as a text document. Now, if at all, I want to go ahead and open the first file. It's pretty simple. Once I double-click on it, XL will go ahead and open this file automatically, ok, and the file is loaded. Now let's come down to our CSV file over u. Ok, so I will go ahead and double-click on it and opens up in exit, but I get a message away. But it says possibility the loss, some features might be lost if you save this workbook and comma delimited format to preserve these features, save it in Excel file format. Now why does that happen? Now? Now just to give a heads up over you, a CSV file is a comma separated value file, which will allow the data to be saved in a tabular format. However, CSV has a limitation. Csv file can be used with most any spreadsheet program, such as Microsoft Excel or Google Spreadsheets. However, they differ from the other spreadsheet file types because you can only save a single sheet in a file. If at all your worksheet has multiple sheets, then CSV will not be able to go ahead and see if that particular file. And another disadvantage of going ahead and saving your file in CSV format is that you cannot say formulas in this particular format. So you fed all your worksheet, has some formulas like addition, subtraction, product, or any kind of mathematical functions that you might have used in your worksheet. When you save it as a CSV format, those formulas will be lost. So I'm gonna go ahead and close this. And I'm gonna go ahead and click on don't show again. And then I will close this. Now let's go back and open up a text file over here. So if I double-click on it, you will observe that it opens up in a text format and it has gone ahead and by default, use Notepad to go ahead and open that file. If I just go ahead and enlarge this file, you can see that I can see my headers over u. So I have my statesman product category location units sold, and everything has been separated by a tab. Okay. So there is a CS month and then there is a tab after which I have the product and then I have a tab, then I have the category than a tab. So everything is delimited by a tab in between. And let's say if at all I want to go ahead and open this file in exit. Then how can I do that? Now that is a way in which I can go ahead and open a text file in Excel. So let's understand that. So for that, what I'm gonna do is I will open Excel once again. And over here, what I will do is I will go to this particular tab called data, and then I will click on this particular button called Get Data. When I click on Get Data, I get this particular option that says from file. And I'm going to select this particular options, that's it from text or CSV. And then what I'm gonna do is I'm going to go ahead and double-click on my desk file away. And when I do that, let's see what happens. What happens is this particular window opens up in front of me and it shows me the kind of format in which my data is available in that text file. So if you observe that I have my salesman product category, everything in my header, and also my data is arranged properly. Ok. And this particular option delimiter, I never have to go ahead and change it because Excel will automatically pick it up. Because their data is usually separated by a colon, a comma, or a space, or a tab. But Excel will automatically pick it up. So I really don't need to go ahead and make any changes over you. If I'm satisfied with the way the data is available, this particular preview, and I want to go ahead and load the data. Then I can go ahead and click on Download button. If at all. I want to go ahead and do some kind of an editing over here. I can go ahead and click on Edit. And now what will happen is it will go ahead and open up a power query window. Ok? Now this particular window gives me an option of going ahead and making some modifications to my data. So for example, if at all I want to go ahead and change the headers, I can go ahead and do that. I can right-click on it. I can see Rename. And instead of salesman, I want to go ahead and just call it a month. Okay? And let's say I want to go ahead and change my total revenue into whole numbers. So I can do that. And let's say this location, I want to go ahead and rename it as city. Okay? And now let's say I'm satisfied with this particular data and I'm okay with the changes that I have made. Now I want to just go ahead and load this data on my Excel sheet. So what I will do is I will just go ahead and click on this button over here that says Close and load. So I have two options over your clothes and load. And the second option is close and load two. So I'm gonna go ahead and click on the second option over here that says glows and load two. And now it is asking me, where should it go ahead and load the data. So I'm going to say loaded on the Existing Worksheet and start loading the data from cell A1. Okay? And then I will click on OK. And you will observe that now it has gone ahead and loaded my data in a beautiful table format. And also the changes that I made to my data. For example, I made some changes to the header or to the types of data that changes are reflecting in my data table as red. Okay, so that's how you can go ahead and save your data in different formats in Microsoft Excel and also retrieve it back to Microsoft excel in the format that you intend to go ahead and work with. It also gives you an option of not only loading the data, but also going ahead and making some modifications to it before you actually start working with it. So that was a quick introduction in terms of how you can go ahead and save data in various formats and also how you can retrieve it in Microsoft Excel. So I hope you enjoyed this lecture and I shall see you in the next one.
5. Lesson 5 Conditional Formatting Highighting Cells: Hey, welcome back. So excel provides us with several formatting tools that will give a better view to our data. And data formatting depends upon the purpose of your data elements in the data and much more. So data formatting innovate enhances the visual appearance of your worksheet. So in this section of focus will be on learning the various conditional formatting tools that Excel offers and which can be used as a stepping stone for going ahead and creating a professional dashboards. So now we're going to jump into working with tools. And the first one we are going to reach out is Conditional Formatting. Now what you see on your screen is a data of some ABC Corporation. And over here I have the data that shows me that ABC Corporation has certain products. There is a price, units, totals, discount, and some other parameters that belong to each of these products. Now, out of this entire dataset, I'm only interested in looking at certain parameters. So let's say out of this entire column, which shows me the total sales for all the products. I'm only interested in knowing the top 25% products. And when I do that using conditional formatting, you will observe that these products, which are my top 25%, are reflected in a differentiate and colour. Similarly, in this particular column where I show my discount value, I'm only interested in looking at the top ten products with the top discount values. What we use in the net value section, I'm only interested in looking at the bottom 15. Whereas if I go ahead and look at my projected sales and the different section over here, I'm interested in knowing only those values which are greater than 10 thousand. Whereas in the different section, I'm only interested in looking at those values which are less than 500. So this is what I can achieve using conditional formatting. So Innovate conditional formatting in Excel enables you to highlight cells with certain colors depending upon this cell value. So let's get on with learning these techniques one by one. So I have this same data available in my Rashid over you. And let's go ahead and start doing conditional formatting with it. So the first thing that I want is I want that top 25% values. It might DO pill section over you. So what I'm gonna do is I'm gonna go to my raw data. We're, I'm going to select this entire section. And then I will go to this particular tab. Click on Conditional Formatting. And then I will come down to this option that says top-bottom rules. Now over here, I don't have anything that's a stop. 25. But I have something called as the top 10%. So I'm gonna go ahead and click on that. And oh yeah, I have this scroller. So I'm interested in knowing that top 25%. So what I'm gonna do is I'm going to click on this scroller away. And I'm going to select 25 as the value. And oh yeah, I also have a drop-down that will help me select the kind of shade that I want are the colors that I want based upon my conditions. So I'm interested in showing my top 25% in a green sheet. So I am going to select this particular option over here that's a green fill with dot being texts. The option that you like based upon your choice. I'm going to select this optional video. Click on OK. And then you will observe that it has gone ahead and highlighted my top 25% values. Let's say I go ahead and K this value of 6,348 to maybe something like 63 thousand. You will observe that immediately the 63 thousand gets changed to a green sheet because now it is reflecting in my top 25%. If I go ahead and reverse these changes, you will observe that the colors have dynamically changed. Now let's go ahead and format this particular section. Over here. I am interested in knowing the top ten values in my discount value section aware. So once again, I'm going to select this entire column over here. And then I will click on Home tab, come down to conditional formatting. But two top and bottom rules. And oh yeah, I'm interested in looking at top ten items. And I'm going to select a particular shade over here. So let's say I want yellow shade with dark yellow a year. And I click on it and then click on OK. And you will observe that immediately it has gone ahead and pulled up my top ten values and showing them in a specific shade. Let's go ahead and do that for up bottom 15. Once again, select the entire column. Click on the Home tab. Go to conditional formatting top-bottom rules, and then go ahead and select bottom ten items. And I'm going to use the scroller. And I'm going to change this value to 15. And let's say because these are my bottom values, I want to go ahead and show them in a red font or a red shaded. So I will not make any changes over here. And I will just go ahead and click on OK. And you will observe that my bottom 15 items are now reflecting in a range. Let's go ahead and look at the projected sales. Over yeah, I'm interested in only going ahead and highlighting those values which are greater than 10 thousand. So once again, what I'm gonna do is I'm gonna select this entire column over here. I will go to conditional formatting. And this time I will go to this particular option. Nazis highlight cell rules. And you will observe that a lot of rules over you. In fact, later on in the course, you can play with all these rules. But right now we will be focusing on some of the basic rules. So I'm interested in looking at the values which are greater than 1000. So I'm gonna go ahead and select this option, we would say is greater than and I will click on it. Now what I'm gonna do is I'm going to change this value. I want my values to be greater than 10 thousand. So I'm gonna click on this up arrow over here. And I'm going to select this particular cell over here. Okay? And then I will hit Enter. And now what I want is I want to go ahead and give this a green shape. So what I'm gonna do is, and we'll click on this drop-down over here. I'm gonna go to this particular option that sees custom format. I will click on that. I will select a green shade over you. And I want my font to be in a white sheet. So I'm going to click on the Font tab. And I'm going to go ahead and click on the drop-down over here. And I will select the white point over here. And then I will go ahead and click on OK. You will observe that the moment I do that, all these values in my specified Cell Range, which were greater than 10 thousand, are now showing up in a format of my choice. Now because this value dependent on this particular cell over here, if I go ahead and change this value from 10 thousand to 15 thousand, you will observe that only those cells will get highlighted, which are greater than 15 thousand. I'm gonna go ahead and change it back to 101001 more time. And you will observe that the colors of these cells will change dynamically. Now let's come down to a different section over you. And our objective is to go ahead and highlight only those cells which are less than 500. So I'm gonna go ahead and select this entire data range away. Whenever you want to go ahead and select the entire data range, you can go ahead and press Shift control and the down arrow key. And all the cell values will be selected in one go. Then you can go ahead and click on conditional formatting. Click on highlight cell rules. And this time I'm going to select this function over here that says less than. And once again, I'm going to go ahead and define the value as 500 over years. So I'm going to click on this up arrow. And I'm going to give this particular cell as the reference. And I will hit enter. And now I'm gonna go ahead and select my custom formatting. So I will go to custom format. And this time I'm going to select a red shaded area. And for my font, I'm going to select a white sheet. And then I will go ahead and click on OK, and then click on okay one more time. And now when I do that, you will observe that all the values that are less than 500 are now showing up in a spreadsheet with a white phone too. So this is how I can use conditional formatting for highlighting. With a particular color and shape based upon a certain room. Now, there are few more things that I want you to understand. Let's say, if I have gone ahead and set a rule for a certain set of says, Can I go ahead and modify those rules in the future? Of course you can. Let's say over here, in this particular range, I want to go ahead and find out only those cells where the value is less than a 1000. So all I have to do over here is once again, I will highlight all these cells. Go to my conditional formatting. And then I will come down to this option that says Manage Rules. And then I can see my rules over year that had been applied to this particular selection of cells. I will click on Edit true. And this time I will go ahead and manually give a value of 100. Which means that I want to highlight only those cells where the value is less than 100. And I also want to go ahead and change the format. So I'm going to click on format. And instead of red color, I want all those values which are less than 100 to be shown in a blue sheet. So I will select blue over your click on OK. And click on OK. One more time. And you will observe that all the values which are less than 100 over you are now showing up in a blue font. Which means that once I set a rule, it is not a hard and fast rule. I can always go ahead and modify my rules. And at the same time, if at all I want to go ahead and clear a rule for a particular selection, I can do that. Lets learn that two real quick. I'm going to keep this same selection. Click on Conditional Formatting. And this time I'm gonna go ahead and click on this particular option that says clear rules. Now I have to opt one that says clear rules for selected cells. And the other one that's his clear rules from the entire sheet. If I go ahead and select the second option, it will remove all the rules, all the conditional formatting rules that I have set for this particular sheet over here. So I'm not going to click on that. I'm just gonna go ahead and click on this particular option. That's it's clear rules from the selected since. And once I click on that, you will observe that all the values are now showing up in a uniform manner because there is no conditional rule over you and the blue shade has just vanished. So by now you must have understood it that conditional formatting in Excel enables you to highlight cells with certain color depending on a cell's value. And now we have also learned that we can go ahead and set up certain rules. We can modify these rules and if required, we can remove these rules from the specified selection of cells. So that is all about the basics of conditional formatting. I hope you enjoyed this section and I will see you in the next year.
6. Lesson 6 Conditional Formatting Data Bars: Hey, welcome back. So the next tool that we will learn in our lecture series now is called as a data bar. So what exactly are data bars? Now data bars in Excel makes it very easy to visualize values within a range of cells. And a longer bar represents a higher value and a smaller been represents a small value. And what databases do is they allow you to get a real look at your data point as it compares every other data point in this selected range. So what do I mean by that? So let's look at our data sheet over you. So I'm looking at the same data sheet that we saw in the previous lecture. I might have made some modifications or video to explain the concept of data bars over here. So if I'm looking at this particular column over here, which is called total. Now, if you observe that in this particular range, all the values are compared against each other. Now within this range, this particular value of 67 thousand, It's showing up with the longest data bar. Whereas value such as 4,200 or one hundred and eight hundred are shown in small data bars. So in a way, it gives us a kind of a quick view of the entire data range. Because it is doing some kind of a comparison between all the values. And based upon that, the length of the data bar is determined. So by default, the cell that holds the minimum value has no data bar or a very small data bar. And the sale that holds the maximum value has a data bar that fills the entire cell and the other cells are filled up proportionately. So let's go ahead and learn this. So I'm gonna go back to my raw data sheet over here and create the data about what I'm gonna do is I'm going to select this entire data range. And then I will go to my conditional formatting, coming out with this option that says data bars. And let's say I'm going to select this particular option over, you know, even before I clicked over here, you will observe that the data buzzer already created. I have different Gradient Fill options over here. I can select green, red, yellow, blue, any shade of my choice. So let's go with the blue one and let's click on that. The moment I click on that, you will observe that it gives us a kind of a quick view where all the values are compared against each other and the data bars have been created proportionately. Now let's go ahead and look at the values in the discount section. Now I'm purposely looking at these values because all these values are negative values. And let's see what happens when we apply data bars do negative values. So what I'm gonna do is I'm going to select this entire data range. You haven't go to conditional formatting. Click on Data Bars. And once again, I will go ahead and select the blue gradient, Phil. And I will click on that. Now you will observe that even though I have selected the blue gradient fill, it has picked up that ledge shield over here. Let's see what happens if I select any other shape. Let's say if I say like green or maybe yellow, you will observe that the color is red. The bars odd enrich sheet. Now why is that? Now that is because all our values in this particular data range are negative. And one more thing that you will observe that the values are negative. The bars are cleared then from right to left rather than from left to right. But can I go ahead and make any changes a wheel? Let's go ahead and see that. So I'm gonna select the same data range. And now I will once again go to my conditional formatting. And then I will click on Manage Rules. When I click on Manage Rules, I see the rule over you and the color sheet is yellow, but this entire data range is still showing in red. So let's go ahead and fix this. So for that I will click on edit tools. And then I will come down to this option that says negative value and axis because our current values are all negative. So I will click on that. And when I click on that, a new window pops up. Well, when I see this particular option that's is negative Barfield column and all your other candidates selected as read. And then I see another option that says negative bottom border color. So I'm going to keep the border color as red. But the bar color, I will change it to, let's say blue. And then I would go ahead and click on OK, and then click on okay one more time. And then I will click on Apply and keep on, ok. So you will now observe that even though the values are negative, they are showing up in a blue shape because we made those appropriate changes in the Manage Rules option. But now let us observe what happens when the values are both positive and negative. For that, what I'm gonna do is I'm going to select this particular column, that difference column, because some values are positive over here, while some values are negative. So I'm gonna select this entire data range. I'd be good to. My conditional formatting would do data bars. And let's go ahead and select the first option itself. And when I click on that, you will observe that all the positive values are showing up in a blue shade, while all the negative values are showing up in red. And that is a line that divides the two. And all the negative values start from a right-to-left and all the positive values start from left to right. Can I go ahead and do some modifications over here? Let's go ahead and check that out. So if I go ahead and select this entire data range, go to my conditional formatting. And then I will go to this option that says managed rules. Then I will select this particular rule and then I will click on Edit Rule. Then once again, what I will do is I will click on this particular option that says negative value and axis. And let's see what changes can be made or were you. So once again, it says that for negative values, it will fill up the color as red. Let's go ahead and make some changes over here. So philip, all are negative values, which may be a yellow sheet. And then let's make some changes over your as well. So I have this option that is automatically display as really able position based on negative values. If I click on cell midpoint over your, let's see what happens. Let's go ahead and click on it, and then click on OK. And click on OK. One more time. You will observe that the center shifts exactly in the middle of the cell. Let's see one more option out there. I'm gonna go to Manage Rules. Click on Edit rules. And then I'm going to go ahead and click on this particular option that says none showed negative value boss in same direction as the positive one. And let's see what happens. Then. I go ahead and click on OK. Click on OK one more time. You're going to apply and then click on. Okay. So now you will observe that all my bars start from left to right. But the negative values are shown in yellow bars, whereas the positive values are shown in blue shading. So you must have observed that I can use the conditional formatting of my choice. And based upon that, I can meet the modification in my dataset and make it look the way I want based upon what is the kind of data that I'm interested in looking at. And another thing about data bars is just like highlighting the sales, the Viet dynamically to any value changes that you make within a specified salary. So that's about data bars. I hope you enjoyed this lecture and in the next lecture, we learned some new conditional formatting tools. See you there.
7. Lesson 7 Conditional Formatting Icon Sets: Hey, welcome back. So now in this particular part of the course, we're going to get introduced to something called as the icon sets. And then I can set in Excel makes it very easy to visualize values in a range of cells. And each icon represents a range of values. In other words, it tells us whether a given value in a cell is good, bad, or an average based upon certain rules. So let's understand what I'm trying to say a wheel. So I'm going to refer to the same dataset that we had used in our previous lectures. But let us understand the data we are first of all, so for every product out, yeah, what I have done is I have total sales value. And then I also have something called as a projected scene. So I was assuming that this much sale would be made, but in the end, I was able to achieve only this c. And over here what I have is that difference. So it is a difference between the project that sales minus the total sales. So, oh yeah, I have a deficit of 5,133 for product one. Whereas if I look at something like product two, I have made a surplus scene because my project at Target was vitals and 903. But I made a sale of 6,348. So there was an extra seal that was made. So similarly, I have gone ahead and plotted these values over here. And over here, what I have is the percentage of the achieved value. So this is nothing but the percentage of this particular value divided by this particular value. So it is nothing but my total sales divided by my projected sales and converted into percentages. So what I'm trying to say over here is I have achieved 89% of my projected value. But over here, I was able to achieve a 107% of my project ID value. And based upon these values, you will observe that these values have certain indicators. Either it's a red, yellow, or green indicator. So it does not require a lot of common sense to understand that green is something that I'm looking out for. Red possibility indicates that I missed the target. And yellow possibly indicates that it is maybe just an average or something that is not so good are probably not so bad. So how do I go ahead and add these? Which can become a quick visual indicator for me to understand whether my values are good, bad, or average. And that is what we are going to learn in this particular section over you. So once again, I will go back to my raw data sheet and I have the same dataset over here. And I'm gonna go ahead and apply these icon sets to the value in my raw dataset forced. So before I do that, let's go ahead and look at the rules that we have applied a wheel. So what I'm gonna do is I'm gonna select this entire data range, go to conditional formatting, and go to Manage Rules. I will click on Edit rules. And the rules over your says. When the value is greater than 0, then it should be indicated by the green icon. When the values are between minus three hundred and fifty and zero then should be represented by this yellow icon. And any value which is less than minus 350 should be represented by the red icon over here. And that is the same formula that I'm going to go ahead and apply to my raw data. So let's go ahead and do that. So I will come down to my raw data over here. I'm going to select this entire data range, go to my conditional formatting. And then I will come down to this option that says icon set. And I will go ahead and select this particular sit over. You know, you will observe that by default it has picked up certain rules and gone ahead and applied the icons to my dataset. But now I want do modify those rules. So for that what I will do is I will click on conditional formatting again. Go to Manage Rules. And I have my rules over you. So I'm going to click on edit rules. And these are the default rules that have been assigned by Excel. I want to go ahead and change these rules. So what I'm gonna do is first of all, I will convert these percentages into numbers because my values or numbers and not percentages. And I will start setting up rules over you. So any value which is greater than 0, I want it to be represented by a green icon. Any value which is between 0 and minus 350. I want that to be represented by a yellow icon. And then finally, any value which is less than minus 350 should be represented with the red icon. So I have gone ahead and set my rules over here. Then I will go ahead and click on OK. I will click on Apply and click on Okay one more time. And you will observe that based upon the rules that I have said, it has gone ahead and assigned the icons to the respective cell values. Now let's come down to the percentages and let's go ahead and assign some I concepts to them. So in the sheet that has been provided with the course, I have gone ahead and applied design concept. So this is exactly what I will try to go ahead and apply to my dataset away you. So let's go ahead and do that. So I'll go back to my raw data sheet. I will go ahead and select my entire data range. And then I will come down to conditional formatting, go to Icon Sets, and then I will go ahead and apply the icon set value away you. But you will observe that in the sheet that has been provided these values or towards the right. Whereas over here, the values have been applied to the left-hand side. So how do I go ahead and make those changes? So let's go ahead and modify our eye concept one more time. So what I'm gonna do is first of all, I'm going to remove these I concepts that I have applied. So I'm going to reverse these changes. And then what I'm gonna do is I'm going to add an extra column over you. Okay? And over here, I'm gonna go ahead and select the same values that I have in this particular column. So I'm gonna say is equal to this particular value over here. And I'm gonna go ahead and drop down these values. So the same values replicated in the next column. And then I will go to conditional formatting. I will come down to this option that says high concept, and I will select this particular icon set. And now I need to go ahead and change the rules over here. So once again, I will click on Conditional Formatting, Manage Rules. And then I will click on Edit rules. And then I will change these percentages into number once again. And over year, since I've changed it to numbers, I will put the values as 0.95, which means that any value which is greater than 95% should be represented by green icon. Any value which is between 90, 95% should be represented by a yellow icon. And then finally, any value which is less than 90% should be represented by a red icon. And then I will go ahead and click on OK. Click on Apply, and then click on OK. One more time. Now my eye concepts have been applied. Now my objective of going ahead and shifting the data to the right and applying the icon sets is because I wanted my icon sets to appear on the right-hand side of my data. And that is what angle to achieve to the next step over you. So what I'm going to do is with the same data set selected, I'm going to click on conditional formatting. I will go to Manage Rules. Click on edit tools. And oh yeah, that is an option that says Show Icons only. Have a click on that, and then click on OK. Click on Apply, and then click on okay one more time. And now I will just go ahead and reduce the size of the cells and then click anywhere outside. You will observe that now the icon sets have been applied to the right-hand side of my data. It is a small trick that I have used, but something that can come in really handy in case you want to represent your data in a different way. So I hope you enjoyed this lecture and in the next lecture we will learn some more tricks. See you there.
8. Lesson 8 Conditional Formatting Color Scales: Hey, welcome back. Now the next Conditional Formatting tool that we're going to learn in this particular section is what we call as the color scale. And the objective of color students is very simple. Color scales and conditional formatting applies the color to cells in a range based on your values. And this lets you easily spot maximum and minimum values as well as certain trends. Now what do I mean by that? Let's understand that. Put an example over here. So what I'm gonna do is we're going to jump into my raw data with you. And I'm going to select this entire data ranges. So this particular data range is basically my sales figures for the different flavors of ice cream that I sell. And this data ranges from the month of Jan to December. And based upon this data, I want to see if there is a certain trend that this particular data is trying to tell me. So what I've done is I've selected this entire data range. I'll go to my conditional formatting. Then I will come down to this particular option that sees color scales. And once again, you can see that just like any other conditional formatting tools, I have a lot of options over you. So let me just go ahead and select this post option itself. Ok, you can select the option of your choice, but for a better understanding, I'm going ahead and selecting a red, yellow, and green scale over you. And I will click on it. The moment I click on it, you will see that it has gone ahead and created a kind of a heat map chart over you. And just by the glimpse of this particular chart, that is a lot of influence that I can draw from this particular data. So for example, the green's over your shoulder, items that are selling the best out of the lot. Whereas the red ones are something that is grabbing my attention and compelling me to go ahead and take some action over you. For example, if I'm looking at walnuts, I can easily say that wallets is my highest selling brand. And it has been consistently doing well throughout the year. If I'm looking at something like element, I can see that it started off pretty well. But in the last four months, the seals have dropped down. And that is something that I really need to go ahead and investigate. If I'm looking at flavors link, green, apple, and vanilla, I can see that consistently the seals have been done. So possibly I need to go ahead and do some modifications over there are probably stopped the production of these two flavours. If I'm looking at something like chocolate, I can see that it did not really start off very well in the beginning of the year. But gradually as the year progresses, it started picking up pace, which means people not like that particular flavor. And possibly I can go ahead and increase the production a little bit. Now purposely while going ahead and doing the conditional formatting, I haven't gone ahead and selected this entire dataset. Now the reason being what happens is these values are nothing but the sum of these values. So if I go ahead and incorporate this entire dataset in my heat chart on my color scales. Let's see what happens. If I go ahead and select this entire dataset. Go to conditional formatting, go to color scales. And then I'd go ahead and select the color scales. Now you will observe that because this is the sum of all these values, what happens is these values are showing up in green, which is a misleading data. So whenever you're going ahead and creating a heatmap, are using data scales to see the trends, are finding out the maximum and minimum values within a data range. Make sure you do not include the totals and subtotals in your value. Rather, we will look at these data separately. So I'm gonna go ahead and diverse these changes. And now what I'm gonna do is I'm going to select this particular data range. And I will apply a separate conditional formatting to it. So let's say I will select this particular color scale over here, blue, white, red. Ok. And when I applied that, you will observe that it gives me a lot of inferential V0, which again says that wallet is something that has done the best sales throughout the year. Whereas vanilla and green apple are something that we really need my attention. If I look at this data throughout the year, let's see it there's certain trend that I observe will be u. So if I go ahead and apply conditional formatting to it, Let's say I go ahead and apply the same conditional formatting. I will observe that in the month of FEM, ideally did not do very well in terms of my seals. So probably because it was winter, the sales of ice cream was down. But however, throughout the year, there has been lots of ups and downs. And June, July, and August have been the month that I have done my best performance. So the probability of people going ahead and buying ice cream is probably more in the month of June, July, and August. Something that I should be really looking at. Now, just like any other conditional formatting, there are ways in which you can go ahead and do some modifications over year as well. Let's say for example, I want to go ahead and apply a different kind of a color scale to the total sales value. So what I will do is I will go to my conditional formatting. I will come down to this option that says Manage Rules. And what were your IC, the rule that has been applied. So currently I have used the three color gradient Dojo, which includes red, yellow, and green. I can click on Edit true. And over here you can see that there are a lot of modifications that I can make. I can go ahead and change this color from maybe a light rail to maybe a doctorate. And I can go ahead and change this value to maybe something like this. And let's see what happens when I click on OK. Click on Apply, and then click on okay one more time. So I have just done these modifications to show you that data options where you can go ahead and manage the rules and choose the parallel combination of your choice. But however, I would like to warn you that, you know, when you use a lot of colors within a same chart. One, it can give you a lot of misleading data. And secondly, your dashboard can actually lose the professional look. So be very cautious in terms of what colors you really want to go ahead and what is the kind of inputs that you want to draw from it? Probably you would want to go ahead and use a color combination of bellows that represents the logo or maybe the symbol of your company. That's really fine. We'll just make sure that you keep the colors to the minimum. Otherwise, the dashboard is really going to look messy and probably it would not give you the kind of information that you really wish to draw out of that, to be very careful about that. That's a very quick way of understanding palace gills. I hope you enjoyed this lecture and I shall see you in the next one.
9. Lesson 9 Conditional Formatting Using Logical Functions: Hey, welcome back. So in our previous lectures, we got introduced to conditional formatting and then we also learned the logical functions. Now what we're gonna do is we're going to use a combination of some logical rules and see how the function in coordination with the conditional formatting. So what we're gonna do is we're gonna make use of both our logical conditions and the conditional formatting techniques. And using them, we will go ahead and format the data that we have at our hands. And let's see what are the kind of results that it produces. So I have some data at my hand, which is a data of certain products which are being sold in different cities by different vendors. And then I have the unit sold, unit price, and then the total sales. So what I'm gonna do is I'm going to apply these rules, these conditional formatting rules, to this particular data. And then we will see what are the kind of results that we get. So first of all, what we need to do is we need to go ahead and highlight all the data within this particular dataset with a vendor name is good works. And we want to highlight it with a green shade. So what I'm gonna do is I'm gonna select the first cell in my dataset over here. And then I will just press shift control, but it's the right arrow key. And then I will press the down arrow key that will select my entire data. And now I will go ahead and apply the conditional formatting rules. So I will click on Conditional Formatting and I will click on New Rule. And this time I will select this, but he could option that says Use a formula to determine which cells to format. And over here is where I will go ahead and put my formula. So my first formula is my vendor name should be equal to woodworking. And my Vendor Name is in this particular column over here. So what I'm gonna do is I'm going to click on this up arrow over here, and I will highlight this particular cell. Now one thing that you need to observe is when I highlighted that particular cell, it has picked up the cell with the dollar value, which means my set a D6 is showing as dollar d. And now that dollar sign means that this particular cell is long, the dollar and next to the D means my column is locked. And the dollar next to the number six means my row number six says lot. Now what will happen in that case is whenever I set a rule, the rule will only be compared with that particular cell. And I don't want that to happen. I want this particular rule to apply to my entire dataset away you. So what I need to do is I only need to go ahead and locked my columns and not mired rules. So for that, what I'm gonna do is I'm going to press the F4 function key f four on my keyboard once. And you will see when I press it once, my Columns got unlocked, But my rows are still locked. But I want the exact opposite of that. I want my columns to be locked and not my rows. So I'm going to press the F4 one more time. And now my columns are locked and my rows are unlocked. And now I will go ahead and give the condition that I will see D6 is equal to get the double-quotes. And I will save good works. And if this condition is true, then I want my cells to be highlighted with a green sheet. So I will click on format. Select the color green. Click on okay. And then I will click on Okay one more time. And you will observe that everywhere where my vendor name is woodworker, the cells have been highlighted with the green sheet. Okay, now let's go ahead and apply the second rule where if the city name is delhi, then I want the font color to be highlighted as a red. So I'm going to once again select my entire dataset. And then I will click on Conditional Formatting. I will go to New Rule, and then I will click on this particular option that says Use a formula to determine which cells to format. And now I will once again go ahead and set my rule over you. My city is in the C column, so I will just click on this particular cell over. You haven't press F4 twice. And then I will specify my roots. My rule says the city name should be Delhi. I will hit Enter. I will go to format. And this time the rule says, if the city is delhi, then my font color should be read. So I will select my font color as read away. I will make it a little bowl. And then I will click on OK. Click on OK one more time. And you will see that now better where the city is deli, the font color has changed. But at the same time you will also observe that the first rule where it my vendor was woodwork, the color green still persist. Now why did that happen? We'll talk about that later. First, let us go ahead and apply all the rules. Now our next rule is if the unit sold are more than 25, then p1 does sell color to be blue. Let us quickly apply that rule as well. I'll click on Conditional Formatting. I'll go to manage rules this time. And I will click on New Rule. And then I will once again select my option. Use a formula to determine which cells to format. And I will select my rule or V0 by rule says if the unit sold is more than 25, then the cell color to be blue. My unit sold is in this particular column. So I will select it best F4 twice. And then I will give my condition is greater than 25. Hit Enter. And then my rule says formatted with the blue sheet. So I will select the shaded blue area and click on OK. Click on OK one more time. Click on Apply and click on OK. Let's quickly apply the last truth. It might total sales is greater than 2500. Then I want the cell to be orange in color with a yellow font. Once again, let's select the entire data. Glick on conditional formatting. Click on New Rule. And this time the rule is about total sales. My total sales are in the G column. So let's quickly specify our rule. I will say this particular cell, which is G6, press F4 twice. And if the value is greater than 2500, I would click on format. I will say fill the color an orange shade. And I want my font to be yellow shaded. And let's make our font a little bold as well. Click on OK. And click on okay one more time. And with that, you will see that all the cells where this condition is fulfilled, where the value of my total sales is more than 2500. The cell color has changed to orange and the font has changed to yellow. But one thing that you must have observed over your is in certain places, the rules have overlap each other. Now why did that happen? Let's look into an interesting fact or were you let's go to Manage Rules and let's look at our rules over you. And what I will try to do is I will just try to change the sequence of my rules and let's see what happens. So this particular rule, I will shift it to the first place. I will shift this particular rule to second place. Have a ship this rule two, the third place and the first rule, I will move it to the food place. Now let me go ahead and apply the rules and just observe what happens. You will see that all of a sudden the output of our conditional formatting has completely changed. Now let us understand why did this happen in detail. So I will go back to my Manage Rules once again. And you will observe that there are four rules that I have set. Or when you, the first rule said that if, when the name is wood works, then the color should be green. Now let's look at this rule over, you know, the rule says the color of my cell should be green, but I haven't specified what should be the color of my font. So by default, what it does is it picks up the color of the font as black. And it applies the rule to all the cells that are specified in the data range. Then after fulfilling that rule number one, it moves to rule number two. Now my second rule says at the city name is Delhi than the font color should be red. So what it does is it goes and find out all the cells where this particular value is satisfied. It goes ahead and changes the color of the font to read. But what happens in places where rule number one and rule number two overlaps, like in this particular range over yo, where condition one and condition two both are fulfilled. Now, because in my condition one, I haven't specified the color of the font. It will go ahead and pick up the color of the font from my rule number two. But if at all, I would have used this particular option over your vet. It says stop. If true. Let's see what happens. I will click on apply. You will see that in that case, the rule number two did not get applied to those cells where rule number one was already fulfilled. So what happens is if I remove this and click on apply. After fulfilling rule number one for the cell color, it moves to rule number two, to go ahead and change the color of the font. Then after fulfilling rule number two, it moves to rule number three and then to rule number four. Now let's go ahead and look at some of this cells away you, let's look at row number 21, where it says cupboard, and it has gone ahead and colored the cells in blue shade. That has the font color is yellow. Now there is definitely an overlap of rule number three and rule number four or were you why? Because as per my rule number three, it has more than 25 units sold. And as per my rule number four, it also has the total sales value, which is more than 2500. But in rule number three, I haven't specified any color for the font. Color for the font is by default black. And therefore, it moves to rule number four and picks up the color of the font as yellow. If at all, I use this option of stop. If true for my rule number three and click on apply. You will observe that the color of the font remains as default because it does not even move to my rule number four. So this is how you actually go ahead and use a combination of logical functions and apply conditional formatting. And this is how the precedence of the rules are applied to the selected data range. If you change the sequence of your rules, the way in which these rules will be applied to your dataset will default. And also, if you use this particular option of stop, if true, and if the condition is fulfilled, it will not move onto your next rule. The conditional formatting of your next rule will not be applied. So this is how you go ahead and use conditional formatting and logical functions in combination and based upon your needs and requirements, you can change the sequence of the rule and get your required output. I hope you enjoyed this interesting concept. In the next section, we learned some new tricks. I'll see you there.
10. Introduction to Pivot Tables : Hey, welcome back. So in this particular lecture series or in the next five lectures, we are going to get introduced to something that we refer to as the Excel Pivot Tables. Now why do we learn PivotTables are what exactly are pivot tables? So PivotTables are a powerful tool within Excel that can be used to analyze, sought, filter and present data in an understandable way. Now why do we need that? So many times before we go ahead and create a dashboard, we have a lot of cumbersome data in front of us. And we need to first convert that data into a meaningful data and then proceed to go ahead and create a dashboard out of that. So this is where the pivot tables will come handy for us. Now it is called the pivot table because you can rotate its row and column headings around the core data area to give you a different view of the source data. Which means that the data that you have in a pivot table, let's say the data is in the rows. You can move the data from the rows to columns and columns to the rows. And that is the magic of pivot table. As we go through the course, you will understand that. And as a source data changes, you can always update a pivot table, which will give you a real-time view of the running statistics. And before we start and go ahead and construct our first pivot table, let's understand the importance of pivot tables. First of all, PivotTables are user friendly and you can go ahead and create a pivot table in just a matter of few clicks. Pivottables can go ahead and create in some data that we can go ahead and use for dashboard building. Pivot Tables makes the data analysis easier, which means a large, cumbersome data can be converted into a meaningful output. Pivot tables are easy to update and the add dynamic as and when your source data changes, you will observe that your pivot table can be easily updated just in a matter of a few clicks. And pivot tables summarizes your data. Very easily. Pivot Tables can assist in finding certain data patterns. And last but not the least, Pivot Tables can create accurate reports real fast. So let's get started and let's go ahead and create our first pivot table. So over here, what I have is this particular dataset, and I have a lot of records over here. So let me just go ahead and have a look as to how many data records do I have a wheel. So I have close to 97 records. And this data is about the sales per month of various products. And these products fall under different categories. They had been sold under different locations. And there are different units that have been sold. The price of each and every unit is different. The profit per unit is different. And then that is a total revenue, which is the product of units sold and the price. And then finally, we have the total profit, which is the product of unit sold multiplied by profit per unit. Now, I want to go ahead and create a dashboard, all of this particular dataset. But before I can go ahead and make a dashboard out of this, I need to go ahead and convert it into some kind of a meaningful output. Now what we are, what I have done is I have gone ahead and created two different pivot tables. So first, PivotTable gives me a month wise report of the different categories that I sell. And then finally, it gives me the grand total of both my categories. My categories fall into bakery and snacks. And this is a month wise bifurcation of the C. Secondly, if I'm looking at this particular pivot table over you, it gives me a bifurcation of the total seals done by each and every location. Not only on the basis of category, but also on the basis of the products that have been sold, taking into concentration in which particular category the products fall. So let's quickly go ahead and construct these pivot tables real quick. So I am going to use the same data in a blank Excel sheet. And here is my data. Now to go ahead and cast a pivot table real quick. What I'm gonna do is I'm going to click anywhere on this particular dataset. And then I will go to insert and click on the table. Over here. It has already gone ahead and selected my data range. And I will say, go ahead and create a pivot table on a new worksheet and click on, okay. When I go ahead and do that, it has gone ahead and created this particular pivot table layout for me. And over here I have the pivot table fields. Now in my first PivotTable, I need the data month wise for each of the categories. And finally, I need the Grand Total. So to do that, what I'm gonna do is I'm just gonna go ahead and select the seals month and put it in my rows section over yo. Then what I'm gonna do is I'm going to select the categories and put it in the column section. Now what I need in my value section is the total profit. So what I'm gonna do is I'm going to just go ahead and drag this total profit value over you. And you will observe that the moment I did that, I have my pivot table ready. Let's quickly go ahead and construct the second pivot table. Now, the second pivot table over your is having the categories. And the categories I have the subcategories and then it shows me the bifurcation of the seals as per the cities. So let's quickly go ahead and construct that. So I will once again go ahead and click on minority Tojo, click on Insert, have it click on pivot table. And then I will go ahead and click on this particular option that says Existing worksheet. And then I will click over here. I will come down to my sheet over here. And let's say I want to go ahead and construct the pivot table. And I'm gonna go ahead and hit Enter. Click on OK. Now the layout is really all I need to go ahead and do is drag the locations to my column section. Then I need the categories. And under the categories, I also need the name of the product. So I'm gonna go ahead and drag the product. So we, and now finally I will go ahead and run the total profit. So you will observe that immediately it has gone ahead and created my second pivot table as we're reading, it gives me the bifurcation of the categories and subcategories and the total profit earned by each of the cities. Now, when you're working with pivot tables, pivot table gives you a lot of options where you can go ahead and format your data. Now, if at all, I select this particular pivot table over here and go to the Design tab. I have lots of options over you. So I'm not gonna go much into the details of each of them because this is not an exclusive PivotTable course. But I will just show you a few options that you can actually go ahead and play around with. You can come down to this particular option that says Report Layout and change the way your data appears in the pivot table. So I can go ahead and click on the outline format. And you will observe that the categories and the subcategories now appear in separate columns. I can go ahead and use a tabular format, or I can keep on continuing with the compact format. Also, you will observe that towards the right, that is something that we call as a PivotTable styles. Now, Oh yeah, if at all, i go ahead and click on this drop-down. I have a lot of pivot table styles that I can pick up from. Now, why do we need this? Now? Many a times you have a team for your dashboards. And sometimes it's necessary that you go ahead and show your pivot table along with your dashboard. And to match the theme, you probably might need a particular style. And that is where this particular option can come in real handy. So what I can do is I can go ahead and select this particular style over you. And the moment I did that, you will observe that my data is ready. Now there are also other formatting options. Let's go ahead and quickly look at them. Now let's say over here in my value section, instead of the sum, I'm interested in knowing the average. So I can go ahead and click on average and click on OK. And you will observe that it picks up the average value. I can also go ahead and format the numbers in case I want to. So for that, what I can do is I can right-click on my Pivot Table anywhere, go to Number format. And I can come down to this option that says numbers. And let's say I don't want to see the values as decimals. So I can go ahead and change the decimal places to 0. Click on OK. And you will observe that the decimal values have vanished. Also, you will observe that as and when the data changes, the size of my pivot table keeps on changing. So let's see what happens is if at all i go ahead and go to my number format, and then I go ahead and change the decimal places by two. You will see that the size of my pivot table keeps on changing as the data changes. Now to avoid that, what I can do is I can right-click on my pivot table, go to Pivot Table Options. And then I can go ahead and uncheck this option that says Auto Fit column widths on update. And I can go ahead and click on OK and post which if at all, i go ahead and change any values or go ahead and refresh my pivot table. The size of WebPivotTable will not change. Also under the design section, I have a few options like subtotals, grand totals, wherein I can go ahead and hide or unhide. My subtotals are showed them at the bottom or top of the Group. For example, if I go ahead and click on this particular option that's a subtotals at the bottom of the group. You will see that all my subtotals appear at the bottom of my pivot table. If at all, i go ahead and click on top of the group. It appears at the top. Similarly, this particular option called grand total can be used for turning of the grand total for rows and columns, and probably for both of them. For example, over here, if at all I click on off for rows and columns, you will observe that all my grand totals have vanished. If at all I click on, on four rows and columns, they appear. So you can actually go around and play with the pivot table depending upon the requirement of your dashboard. So that was a kind of a quick introduction to the Pivot Table. I hope you enjoyed this quick introduction to pivot tables, and I shall see you in the next lecture.
11. Lesson 11 Formatting Pivot Tables: Hey, welcome back. So in our last lecture, we understood how we go ahead and create pivot tables. Now in this lecture, our focus is going to be on going ahead and applying conditional formatting to our Pivot tables. So as you must have already learned in the lecture of conditional formatting, we're going to use the concept of data bars, and we will try to apply the data bars to our existing PivotTables over you. So now what I'm gonna do is I just want to go ahead and apply the data bars to this particular data over you. So what I'm gonna do is I'm going to click on this. I'll go to conditional formatting, would do data bars. And I will select this particular data borrower, you. Now you will observe that the moment I did that, it has gone ahead and applied a conditional formatting to my dataset over here. But along with that, there is a small widget that has popped up on the right-hand side. Let me just go ahead and click on that widget. And you will observe the directory different options over here. So one says selected cells, and that is by default. The second option over here is all cells showing sum of total profit values. So I'm gonna go ahead and click on that. And you will observe that it has gone ahead and applied that conditional formatting to my entire dataset. However, one thing that you need to notice over here is it has also gone ahead and taken my grand total into consideration. And that is the reason when it is going ahead and comparing these values with my grand total, There is a huge variance. And that's why all these data bars are pretty small in size as compared to the grand total over you. And I don't want that to happen. So how do I go ahead and fix that? So what I can do is I can just go ahead and click OK. And I will select this particular value over here that says all cells showing some of total profit values for sale of month and category. And the moment I did that, you will observe that it has not gone ahead and taken my grand total into consideration. And it has gone ahead and applied the data bars to these particular values away you. Now just in case if I want to go ahead and apply a different set of conditional formatting to this particular dataset. I can go ahead and do that as well. Let me just go ahead and select this particular data. Go to conditional formatting. And then I will select a different kind of a Conditional Formatting over you. And you would also know that the moment I did that it has gone ahead and compare these values amongst itself and have gone ahead and applied a different conditional formatting altogether. So what I have done different Yo-Yo is I have selected the entire dataset first, and then I have gone ahead and applied the Conditional Formatting. Now, let us look at some more scenarios over a year. Now, let me just go ahead and first change this pivot table a little bit. So what I'm gonna do is I will select this pivot table, right-click on it and click on Show Field List. And the way you are, what I will do is I will remove the locations and I will also remove the average to two. And now I will just go ahead and add. The total revenue away. And then I will add that total profits. And let me just go ahead and close this. And now let me just go ahead and click on this minus sign away. And you will observe that now it is only showing my categories and not my subcategories. Now let me go ahead and try applying a Conditional Formatting over here. So let me just first select this particular cell over here. Click on Conditional Formatting, and I'll go to Data Bars and let me just select this particular form I W. Now once again, it gives me that small widget. So I'm going to click on that. And over here, I will select this option that says all cells showing some of total revenue value for category. Okay, so not the subcategory, just a category. So I'm going to do that. And you will also, the moment I did that it has gone ahead and applied my data was to both my categories over here. If I go ahead and collapse the categories and look at my subcategories, no conditional formatting has been applied to the c. Now if at all, I want to go ahead and apply conditional formatting to my subcategories, then I will have to select them, go to the conditional formatting, go to Data Bars. And let me just select a gradient fill lawyers. So let's select the green one. And once again, the widget has appeared. So I will click on this drop-down over here and I will click on All says showing some of total revenue. And you will observe that the moment I did that immediately it has gone ahead and apply this conditional formatting to all my subcategories as we're now. Similarly, let me just go ahead and apply the conditional formatting to my total profit as well. So I will just click over here. Click on Conditional Formatting. Go to Data Bars and let me just delete this particular format. And if I click on it, and I click on All cells showing some of the total profit values for categories. It will apply conditional formatting to both the categories over you, the bakery, and the snacks. But it has not gone ahead and applied any formatting to the subcategory Zoe or the name of the product. So V0. So let me just apply some conditional formatting over here. So I will select this gradient field this time. And once again, I will click over here and I will go ahead and apply the conditional formatting. But now, if I come back to this particular column over here, you will observe that my budget is available on the right-hand side, but it is not visible away you now just in case if I want to go ahead and make any modifications to my conditional formatting, then how do I do that? It's very simple. So I will just go ahead and click on any of the cells within my pivot table, go to conditional formatting. And then I will click on Manage Rules. And you will observe that the moment I did that, all the rules that have been applied for conditional formatting within this pivot table have appeared over you. Let's look at them very closely. So the first one, which is the red bar, is applied to the sum of total revenue within the category. Okay, my categories are bakery and snack. So we're the green one is applied to the product, okay? But on the revenue side, similarly, the pink one is applied to the category on the profit side, and then the yellow one is applied to the total profit on the product side. So you need to understand that initially it might be a little confusing. But as and when you get a hold on conditional formatting, you will understand how these rules function. And in case you want to go ahead and make any changes to any of the rules, all you need to do is highlight the rule, and then you click on Edit Rule. And the moment you do that, you will observe that all those three options that you were looking at initially in the Widget are available or YOU, okay, the selected cells, all cells showing some of total profits and all cells showing some of total profit values for product. So just in case you ever wish to go ahead and change any rules, you can go ahead and do that using the Manage Rules option. So that's how you can go ahead and apply conditional formatting to pivot tables. So I hope you enjoyed this lecture and I shall see you in the next one.
12. Lesson 12 Filters and Slicers: Hey, welcome back. Now in our last lecture, we focused on going ahead and doing conditional formatting to our Pivot Tables. And over here, in this particular lecture, our focus will be on understanding how do we go ahead and filter the data in our pivot table. So what I'm gonna do is add in the same sheet. I will first go ahead and clear all the rules. So it's very simple. I can just click anywhere on my sheet over here, go to conditional formatting and I can click on clear rules. And I will select this option that says clear the rules from entire sheet in case you ever want to go ahead and clear the rules from the selected cells, you can select this post option. But right now our focus on this lecture is on filtering. So I'm going to clear all the rules from the sheet. So now we have these two pivot tables in front of us. Ok? Now, let's say I want to go ahead and filter this data. And what do I mean by filter? So let's say in this particular data, I want to go ahead and look at the data of only a few cities because this particular data is accumulative data of all the cities that I have in my database. Because if I'm looking at my database, it has picked up the data from all the several cities that I have. So how do I go ahead and filter the data so that I look at the data only from specific cities. Now there are a few ways of doing that. I will right-click on the PivotTable and click on this particular option that says Show Field List. And over here I have this particular section which is called as the filter area. Now, I want to go ahead and filter the data on the basis of location. So what I can do is I can just go ahead and drag the location over you. And you will observe that the filtering criteria has just popped up above my pivot table over you. Now effect all I go ahead and click on this particular data. It gives me the option of selecting any particular city, and it will only show me the data for that particular cities. So let's say I want to see the data only Bangalore. And I go ahead and click on ok. It is only showing me the data for the city of Bangalore. Now, in case I want to go ahead and select multiple cities, I can just go ahead and put a check mark on this particular option that says select multiple items. And let's say now I want to see the cumulative data of Bangalore and Delhi. I can go ahead and put a check mark on daily as well and click on ok. So now what has happened is it has gone ahead and filtered my data. And it is showing me the data for two cities, Bangalore and Delhi. Now that's one way of going ahead and filtering your data. Now let's look at another way of filtering your data. Now here is something that we are gonna learn, something called as slices. So let me first go ahead and select this particular pivot table and remove the filter from you. And now let me just go ahead and add slices. Or you know, let's understand what a slices. So what I'm gonna do is I'm gonna select this pivot table over here, and I will click on Insert. And over here I have this particular option called us license. Now let me just go ahead and click on that. And I'm going to click on that. It asks me on what particular criteria do I want to set my slices over you. So let me go ahead and select location. In fact, I can select anything or any criteria on the basis of which I would like to do the filtration. But right now I'm going to select location. And let's see what happens when I click on okay. A small window pop temp o V0. And it shows me all the different cities that I have over you. Now I can go ahead and click on any of the cities. And you will observe that it only shows me the data for that particular CTO. I can go ahead and click on this particular option that says multi-select. And then I can go ahead and select multiple options also. So I can see the detail of Kolkata and Mumbai together. Let me just add Delhi to it, and it will go ahead and show me the data for all the three cities. And in case I want to go ahead and clear my filters, I can go ahead and click on this particular option that says Clear Filter. Once I do that, it will just go ahead and clear all the filters. And you will observe that it is now showing me the data for all the cities cumulatively. Now let's say I want to go ahead and apply this same filter on this particular PivotTable as well. But when I go ahead and make any changes, you will see that the changes only happen in this particular pivot table, but no changes are happening or were you now, let me just show you a small trick wherein I can go ahead and link multiple pivot tables with one set of slices. So what I'm gonna do is first I will go ahead and clear all the filters. I would just right-click on my slices over you. And let me go to this particular option that says report connections. Now, it is showing me that my slices are only connected with PivotTable three. So what is Pivot Table three? Let's first understand that. If I click on this particular pivot table over you and I click on the Analyze tab. It says the name of my Pivot Tables. Pivot Table four. Let me just give it a particular name. So let me just call it revenue PivotTable or let's just call it revenue dividend. Okay? And over Europe, and I click on this particular pivot table. It says the name of my Pivot Table Is Pivot Table three. So let me just call this pivot table S category period. So this is how you can actually go ahead and name your pivot table. Because sometimes while creating dashboards, you might be dealing with multiple pivot tables and having a specific name for your pivot tables can really make your life easy. So over here, I have named this particular PivotTable as category pivot. And this particular pivot table has been named as revenue pivot. And now what I can do is I can just right-click on this license over here. And then over here, I can go ahead and click on this particular option that's is a report connection. And when I click on report connection, you will realize that this particular slicer is currently only linked with category pivot, which is this pivot table. And therefore, when I click on it, any changes were only happened in this pivot table and not in this vivid to. But what I can do now is I can go ahead and put a checkmark over here. And now this particular slicer is connected with both the pivot tables. And let's see what happens now. So I'm gonna click on OK. And now if I go ahead and click on any of the options over here, you will observe that the changes are happening in both my pivot tables. Now just to let you know that there are various styles that you can pick a prompt so that it matches with the theme of your pivot table or with your dashboard. So what I can do is I can just select the slicer over here, go to the Options tab, and over here I can select a design of my choice. So right now, I can go ahead and select this particular design, or maybe this particular design of this one. So select the design of your choice. But the ultimate objective of any slicer or any filter. Go ahead and filter the data based upon certain criterias. So that you are only looking at the output that you are only interested in looking at. So that's a very quick lecture on data filtering using pivot tables. In our next set of lectures, we'll learn a few more things about pivot table. So I hope you enjoyed this one and I shall see you there.
13. Lesson 13 GETPIVOTDATA: Hey, welcome back. So in our last lecture, we got introduced to the concepts of the pivot table. And then we also learned how do we go ahead and incorporate slices in a pivot tables. So in this particular lecture, our focus is going to be on understanding how do we go ahead and retrieve some specific information out of our pivot table. So what do I mean by that? So let's say I have this entire pivot table in front of me. And out of this n-type to the table, I'm only interested in looking at specific data. So let's say I'm interested in only looking at the total revenue that I have gone to my scenes. I'm only interested in looking at the total items that are sold and the total profit that I have on from my big receipts. Now, to understand the concept better, I have gone ahead and added one more slicer away you. And that is the category Slicer life. I just go ahead and right-click on my slicer window and click on this particular option that seems report connections. You will observe that I have gone ahead and establish a connection between this particular slicer. And both might pivot tables over you. Okay, so that is a checkmark on both the options over here. And if I go ahead and click on OK, I can now go ahead and start retrieving the specific information that I'm looking on far from my pivot tables over here. So let's first of all look at the total profit data have earned from the beaker. Now, to get this particular information, it's pretty easy. All I have to do is I can go ahead and click on equal two. And in my Pivot Table, I know that this particular value is available in this particular cell or we, you. So I will just go ahead and click on it. And when I click on it, you will observe that a spawn formula has popped up in that particular cell. It says GETPIVOTDATA, That is a bracket and then there is some information out there. So let's understand what does it say. So over here, what it says is pull up the information from my pivot data from the column which has a heading called sum of total profits. Then you will see that dollar f $1.3 sine over here. And what does that mean? Is that my pivot table actually starts from the cell F3. And then there is a comma and it says category. And that is a comment and it says bakery, which means it's asking Excel to pull up the information from the liver table where the category matches the bakery and column, which has a heading of sum of total profits. So let's go ahead and hit enter. And you will observe that the moment I did that, it has gone ahead and pull up that information. And that information is available in this particular cell over here. But now let's see where we can face a problem. Now for some reason, let's say if I go ahead and use this particular slicer Rubio, and I'm only interested in looking at the data for my snacks. So I will just go ahead and click on it. And what has happened now is because I have gone ahead and selected snacks in my filters. The data for bakery has just vanished. And because that has happened, now, this particular cell is giving a reference. Now, how do I fix that? Now there is a small tweak that I can use an Excel. Let's understand how do we go ahead and fix this issue. So first of all, what I will do is I will just remove the filter. So we'll, I will come down to this particular cell. And when I go to my formula bar away, yo, you will observe that the formula is available or V0. I just need to go ahead and rectify this formula a little bit. So I will say is equal to if error. And then I didn't open brackets. Now what I'm telling Excel is if at all the value is present, pull up the value from the reference cell. However, for any reason, if the value is not available over there, then alternatively return the value as 0. And then I will close the brackets and I will hit enter. Okay, now let's go ahead and change the filters away and let's see what happens. So now if I click on snacks, you will observe that instead of giving me an error, it is returning the value as 0. Now, again, the value 0 is not right because the total profit that I've learned from my big worry is not 0. So what I can do this, I will just make a small modification to my GETPIVOTDATA over there. Once again, I will just go ahead and remove the fructose from you. Come down to this particular cell over here. And instead of a 0, I will say double-quotes. I will use the minus sign and given double good again. And now I will hit Enter. And now if I go ahead and change the filters, you will see that it is not returning any value. So it's much better than looking at a 0, which can sometimes misguide the person who's looking at that particular data. So now let's go ahead and remove all the filters. And let's look at another scenario where you're now the second thing that I need in this particular data where u is the total revenue, and then I need the total items that have been sold. So now I know that my total revenue is available in this particular Pivot Table or wheel, and it is available in this particular cell. So let's use the get pivot function and tried to establish a relationship between this particular cell and this sense so that we get the specific value. So I will say equal to, and I will click on this particular cell. And it has gone ahead and filled up the formula by default. I'm just gonna press enter and I have the value over there. Now let's see what can happen if I go ahead and start using my slices. Like if I go ahead and click on any of the CDS OUT, you will observe that my grand total keeps on changing and accordingly, the value in my total revenue also keeps on changing. Now, I don't want that to happen. What i want is, no matter what kind of filters are used over yo, I want this value to remain constant. So how can I achieve that? The best fixed for that is going ahead and creating a separate table. Let's go ahead and go back to our rod it over you. I'm going to click on any of the cells over here. Click on Insert, go to Pivot Table. And I will go ahead and create this pivot table on a new worksheet. I can click on OK. And now I will go ahead and create a new pivot table over you. So first of all, I will select the seals month. Then I want the total units sold. So I'm going to select this particular option over here that says units sold. And then I will select the total revenue because these are the two values that I'm interested in. And now I have gone ahead and created a separate PivotTable. And now I will go back to my sheet over here. And this time I will delete this particular value. And now I will give a reference. Now before I go ahead and give any kind of a reference, the best practice is to go ahead and name our pivot table. So I'm going to select this particular pivot table, go to the Analyze tab. And I will say this is a reference pivot table, so I will just name it as reference period. Ok? And then I will come back to my sheet over here. I will say equal to and go back to this particular sheet and give this particular value. So now it has gone ahead and picked up the total revenue. I will also go ahead and give the value for the total items sold. So I will say equal to go back to my sheet, select this particular value and hit enter. And now let's see if at all i go ahead and change the filter, what happens? So I'm changing the filters and you will observe that my grand total is changing. However, this particular value or a o, the total revenue and total items soil remains constant. So that's how we go ahead and make use of the get pivot function to pull up any specific data from a pivot table. And we can only go ahead and look at the data that we're interested in looking at. That was the shot somebody about GETPIVOTDATA. I hope you enjoyed this lecture. I shall see you in the next one.
14. Lesson 14 Pivot Charts: Hey, welcome back. So the last thing that we will learn in this particular lecture series on PivotTable is how do we go ahead and add charts to our PivotTable? Now, before we go ahead and do that, let's do a small recap of what we learned in the previous lectures. So I'm gonna select this particular data. We, you and me do some conditional formatting to it. So I will add some data bars to it. I will select this particular data. And then I will go ahead and add another set of data marts to it. Let me just go ahead and resize this a little bit. So my conditional formatting is done. Now let me just go ahead and add some license to it. So I will select this pivot table, go to the Insert tab, click on slicers, and let me just add the location slicer and let me just add the categories over here. And we click on OK. Let me just resize this a little bit so that it fits well. So I will just select this and I will resize this. And I will set distinguishable you. And I will just resize this as let me just go ahead and do some kind of a formatting. So I will select this, and I would select this particular format. And I will select this and apply this particular style over here. Okay, so I have my data ready and now let's quickly go ahead and add some charge to this. So let's say I want a bar graph that shows the CSS of my degree as well as my snacks or the period of last 12 months. So what I can do is I can just select this particular PivotTable, would do insert and a wheel. I see this pretty good option called Pivot Chart. I would click on it. And when I click on pivot chart, we would jot suggests me a particular chart type based upon the type of data that I have. So I will go with whatever it suggests. I will just select the clustered column and I will click on okay, so with that my chapters ready, I can go ahead and do some kind of a manual formatting to it. I can remove this particular grid lines or V0. And then probably Eigen does remove this particular axis. And if at all I want, I can go ahead and change the colors. But rather than doing all this, I prefer going ahead and selecting a ready-made design. So I can just always go ahead and select my chalk. Go to the Design tab and select the design of my choice. So let me just select this one. And the way you and let say, I don't want to go ahead and have the data labels. So what I can do is I can just select the data labels and I can delete it. And now because I have gone ahead and created a vivid shot, I also have all these bridges, those away you. But when you look at the chart, they seem to look a little uglier and I don't want to have them on my chart over you. So what I can do is I can select the chow. I can go to the Analyze tab. And the way I see this pretty good option, that's easy. We'll buttons. So I will click on high HDL, and that will hide all the buttons that I have on my chart. Because whatever I can achieve to these buttons, I can always go ahead and do that to my slices as well. Now let's say I only want to see the data for my bakery. So if I click on B3, you will observe that. It's only showing me the data on a degree away, or let's say I'm only interested in looking at the data from BigQuery for Bangalore city. And if I click on that, you will observe that now it is only showing me the data for Bangalore city and for the category bakery. So innovate. This is a kind of a dynamic chat because as and when you make any changes to your pivot tables, that job will change accordingly. Now let me just go ahead and add one more chart. We'll, so what I will do is I will just resize this chart a little bit and over. Yeah, let me just go ahead and select this particular data. Go to the insert tab, go to Pivot Tables, and click on pivot chart. And let me just go ahead and draw a line chart, this type. And it will automatically suggest to me a charge. So let me just select this when you go to chart the wheel. And I will go ahead and place it somewhere away you. And let me just go ahead and select a ready-made format for this chart as well. So I'm going to select this gel. Click on Design tab, and let me just select this particular format, a wheel. And once again, what I can do is I can remove these buttons that are there. So I will just select this particular chart, click on Analyze, go to feed buttons, and then I will click on Haydn. Now for both the charts, I will also remove these legends and whatever formatting and doing away. Yeah, it is. Yup. Do you you can go ahead and format the chart as per your style and as per your requirement. Let's say I don't want the neighborhoods UBS, I'm going to delete them and I will delete the labels yours when I'm going to keep the neighbors of V0. And let's say I want to add a few features to my chart. So let's say I want to go ahead and add the idly do my chart. So I will say chart title and let's say this is M never knew charge. So I'm going to leave you with this as revenue jump. And we, oh, let me just go ahead and label this particular chart. So let me just give it a chat item and let me just call this in V3 versus snacks job. Just in case I want to go ahead and change the design or select another color pattern. I can always select the chart, go to the design tab. And we, oh, I have this particular option that sees change colors. I can select the color pattern of my choice. So we similarly, I can go ahead and make the changes to this particular chart as well. I can come down to this change colors option and the color pallet of mitosis. We're so let's select this one. Now. Let me just go ahead and check my child. So eo, so if at all I click on any of the filters away, you will see that my chalk will change accordingly. So in a way, it is the only dynamic gifts I only lead one to look at the data from Ice max. I can do that. I can also go ahead and play with the chart elements as an when I want to let say I want the data labels ojos so I can click on this chart elements. The icon D10 will select the data labels. And then I can just change the colors. So in a way I can just go ahead and play around with my chart over you. And it is fairly dynamic because it's the only dependent upon your pivot table over here. So you can actually go around and leave with your charts and give them a kind of a format that you need a passport that team and design of your dashboards. The objective of this particular lecture was to go ahead and give you a small hands-on experience in terms of how you can go ahead and create a quick pivot chart using your pivot tables. So I hope you enjoyed this lecture and I shall see you in the next one.
15. Lesson 15 VLOOKUP and HLOOKUP: Hey, welcome back. Now in order to go ahead and create dynamic dashboards, we need to be familiar with certain formulas. And one such formula is the VLookup or the HLookup formula. Now, v lookup actually stands for vertical lookup. It is a function that makes Excel search for a certain value in a column in order to return a value from a different column in this same room. Now what do I mean by that? So let's understand that through an example of wheel. So over here, I have this small dataset wherein I have the ID number, I have the name of the employees and then I have yet age now over. Yeah. I'm only interested in finding out the name and the age of the employee who belongs to ID 107. And yet is where VLookup will come handy for me. So now what I will do is I will start typing my formula where u. So I will go to this formula bar over you and I will say is equal to VLookup. Okay? And then what I will do is I will click on this function over, you know, over here it says, what lookup value do I want to go ahead and search. So I want to go ahead and look up the value for the ID number. So I will go ahead and give the reference as this particular cell. And then it is asking me where is my table array? So what I can do is I will click on this optional video and I will give the entire dataset as my adding. Now, it is asking me column index number. Now what do I mean by that? So if I'm looking at this particular data over the column which has the ID number is my first column. The column with names is my second column, and the columns with ages is my third column. So the name that I'm interested in looking at appears in my column number two. So I will just give the number two over here. And then what I need to do is I just need to go ahead and give the value 0 where you are now, what does this mean? Is if at all Excel is able to find the value for me, it will return the value. But in case if the cell is not able to find any value in that particular dataset or in that table at a, then it will return the value as 0. And all I need to do now is click on OK. And now it tells me that the employee with ID number 1-0 seven is red. Now similarly, I can go ahead and find the age away. So what I'm gonna do is I'm going to type equal to. And then I will type VLookup. Open the brackets, and now click on this particular option that says Insert function. And now once again, I want to look up on the basis of the lookup value, that is my ID number. My table adding once again is the same. My column index this time is number three because the age is appearing in column number three in my table array. And once again, I will return the value as 0, just in case exit is not able to find any desired value in that table array. And I will click on OK. So now it says that employee number 107 has the name as Fred and has the age as 40. Now because I already have the formula we owe, if at all i go ahead and change this reference. And let's say instead of a 107, I made this a 108. It will go ahead and return me the value of employee number 108, that is Herman and age 45. So that's how VLookup functions. Now let's say if your data is arranged in a horizontal manner, in that case, VLookup function will not work because right now the data is in the horizontal fashion. So what I'm gonna do is I'm going to use the edge lookup function. Now, HLookup actually stands for horizontal lookup. So once again, what I will do is I will go ahead and c equal to HLookup. I will open the brackets. Click on this particular insert function option over here. And once again, I want to go ahead and search on the basis of the ID number. So I'm going to click over here, and I will give this particular ID number. Now, I will hit enter. Now my table at a is available over a year. Now, it's asking me for the row index number. Now I want the name and name appears in row number two in my table at a. So I will give the number as two. And this particular value, once again, I will type a 0 and hit Enter. Once again, it has returned me the value as Fred. Let's go ahead and find the age now. So I will say is equal to VLOOKUP. Open the brackets, Insert function. And I want to go ahead and look up the value on the basis of this particular ID number. We'll then I will select the entire table array. Hit Enter. Then row number is three this time. And the range lookup, I will go ahead and type and hit OK. Once again, if I go ahead and change any value in the ID section, let's say I type in a 102. It says that ID number a 102 belongs to Ronnie and the age is 28. So this is how you can go ahead and use the VLookup and HLookup function and pull up any values within your table array based upon certain criteria. So I hope you enjoyed this quick lecture on VLookup and HLookup, and you must have already grasped the concept as well. And I shall see you in the next.
16. Lesson 16 Match and Index: Hey, welcome back. So our focus in this particular lecture will be on a match an index function. Now match an index is one of the most popular tools in Excel for performing more advanced lookups. So in our last lecture, we got introduced to VLookup and HLookup. However, there are certain limitations which the match and the index function takes care of. And this is because the match and the index function is much more flexible as compared to the edge lookup on VLookup. And you can do horizontal as well as vertical lookups and also two-way lookups using the match and the index function. So if you want to improve your Excel skills and your dashboard building skills, then index and match function is something that should always be on your list. So let's get started and let's understand these two functions with this example over here. So let us first understand the match function. Then we will gradually move to the index function. And then we will understand how do we use them in combination to each other. So the match function is actually designed for one purpose only To find the position of an item in a range. For example, we can go ahead and find the position of product ID p 00 for in this particular ad a of the product IDs over here. So let's first understand that with an example over here. So what I have a wheel is product IDP 0-4. And I'm interested in knowing in which particular row do I have it in this particular adding. So for that, what I'm gonna do is I'm gonna use the match function. And then I will open brackets. So the first thing that the match function is asking me is the lookup value. So what I'm gonna do is I'm going to say this is my lookup value. I will give a comma after that. The next thing it is asking me as the lookup adding. So I would say this is my Adi. And then it is asking me what is the match type. So when I give a comma, it brings up a pop-up saying less than exact match or greater than. So I will just go ahead and select Exact match. And then I will close the bracket and hit enter. So now what it is telling me is in this entire audio video, bees, you know, 0-4 is enough fifth position. Now why is that? Because it has also considered this particular row way because when I selected the Adi, I even selected the first row over here. So over here it is considering this as row number two, and this is row number three, row number four, and this is row number five. So it's returning the value as five. If I go ahead and change this product to, let's say product p 005. It says it is available in row number six. Now similarly, I can go ahead and give a horizontal range and still use the match function and in that way might function is really flexible. So over here what I want to know is in this particular video we'll Where do I find my rate? Okay. Because we I have item name, I have Product ID and I have read. So I'm going to use the match function. I will open brackets and then I will give the lookup value as rate. I will give a comma after that, it's asking me for the lookup at a. So I will give this particular audio video. And now once again I will say match type as exact match, and then I will close the bracket and hit enter. So now it is telling me that rate is available in column number three because this is my column number one, this is column number two, and this one is Mike, column number three. If at all i go ahead and change the rate to itemName. It is now telling me that it is available in column number one because item name is available in that column number one in this particular array, a warrior. That is about the match function, which is actually used only to find the position of an item in arrange. Now let's move our focus towards the index function. Now the index function works exactly opposite to that of the match function. So in a nutshell, the index function actually retrieves the value at a given location in that range. Now what do I mean by that? So let's look at it from an example perspective once again. So what I want to know is what is there in a row number six, if I select the seam adding, in other words, what I'm gonna do is I'm gonna use the index function. I will open the brackets. Then I will get this particular audio wave. And then I will give a comma. So when I give a coma, it's asking me row number. So I want to know what is there in row number six. And since I have already selected this particular audio video, I'm going to give the column number as one and I will hit enter. So now what it tells me is in my row number six, in this particular adding, I have memory. Okay, so let's look at this 123456. So the sixth row has memory under the added that we just selected. Now let's go ahead and use the index function horizontally. So what I'm gonna do is I will just see index. I will open the brackets and then it's asking me the added. So I will say, OK, this is the Adi. And I would say my row number is one because I have only selected one row. We're so by default I'm going to give this value as one. But I want to know what is there in column number one. Okay? And then I will close this and hit n. So what it is telling me is even I selected this particular ad yo-yo in my column number one. What I had was the item name. Okay, so basically the match function actually finds the position of an item and index function exactly opposite. When you give it the position, it will tell you the item name that is located in that particular position. Now using the combination of the two, you can actually run a two-way lookup. Now let's understand what I'm trying to accomplish over u. So I have this product IDB 004. And over here I have a dropdown. So I can select either item name or I can select a read. Now based upon this particular selection, I want to know what value should be populated over here. So if I select p 004 and select read, what I want is I want the price of product ID p 004. But if I select item name over here, then I would want the item name of product ID p 005. So let's try to accomplish that using the combination of the match and index function. So what I'm gonna do is I'm gonna say is to index. And I will open the brackets. Now it is asking me to add eoyo. So I'm gonna select this entire audio volume. And then it is asking me for the row number, one row number, should I look out the data for? So for that, I will go ahead and add the match function of a. And what is my lookup value that I'm looking for? I'm looking out for this particular value. And where is the lookup at a for that? It is available over a year. And then I give a comma, and then I select the match type as 0, and I will close the bracket. Then I give a comma again. So now what I've done is I have defined what is the rho number to look out for? Now it is asking me for the column number. So I'm going to say match. I'm going to open the brackets. What lookup value should i look out for? I should look out for this particular value which is available in this particular cell G6. And then I will give a comma. Then it will ask me where is the lookup array for that? So I'm gonna say the lookup value for that is over here. And then I will go ahead and give a comma. Once again, I will say I need an exact match. I will close this bracket and I will close this bracket as well, and I will hit. So now what it tells me is the product IDB 004 has an item name memory. So if you look at the data over here, for p 00 for the item means memory, if at all i go ahead and change this to read. It will say rate for p 004 is 1320, and it exactly matches with the data that we have at our hand. If I go ahead and change the product ID over here. So let's say I change it to p 004. It says the product ID p 004 has lead one to 40. So p 004 has a rate of 100. It's matching. If I go ahead and change this to itemName, it says 0-4 is monitor. So this is how we can actually use a combination of the match and index function to run a double lookup when it goes one step further. And also takes care of the limitations of the edge Lookup and the VLookup, wherein you could only run one type of lookup, wherein in this particular case, you can actually go ahead and run a two-way lookup and find the specific item that you're looking out for in the big idea of dataset that you would have at your hand. So these type of functions do come really handy to us when we're going ahead and creating professional dashboards to make our life real easy. So I hope you enjoyed this lecture and I shall see you in the next one.
17. Lesson 17 Data Validation: Hey, welcome back. So in our last lecture we got introduced to the match in index function. And in this particular lecture, our focus will be on learning a cool tool in Excel called as a data validation. Now I just want you to pay attention to where you are because I'm going to use the same sheet that I used in my previous lecture. So if you observe, i had a small drop-down over here. And it had all the product IDs that I have listed over u. And similarly I had a drop-down over here, which, which listed the item name and the read. Now how do we go ahead and create these drop-down list? And what is the purpose of going ahead and doing that? Now this is what we referred to as data validation. And the ultimate purpose of data validation is to make sure that the users enter only certain values in a cell. Now for example, we yo if at all I tried to go ahead and punching a value like p 009. Let's see what happens. So I got an error message that states this value doesn't match the data validation restriction defined for this cell. Retry cancel and hell, if I click on retry, it just does not exit any value other than the value that is listed over u. So I have these set of values and I can only go ahead and make a selection out of b's values over u. So this is what we referred to as the data validation, and that is what we are going to learn in this particular lecture over you. So how do we go ahead and create a data validation in first, please? So let's go ahead and create a data validation for this particular data set that we have a wheel. What I'm gonna do is first I will go ahead and select this particular data and peace DW. Now let us say I want to go ahead and create a data validation in this particular cell over you. So what I will do is I will highlight this particular cell. Go to this particular option that says Data. Click on this particular button that says data validation, and I will select data validation over u. Now this data validation window has just popped up. And over here it says allow, and then it has a drop-down over u. When I click on it, it gives me a lot of options like whole numbers, decimals, list, datetime, length, custom. So in fact, I can go ahead and make selection out of any of these. But right now I'm only interested in going ahead and making a list out of this particular product IDs that I have over here. So I'm going to select the list over here. And then I will select the source. So I will select this particular source. So we And you will observe that I have also selected one blank cell ojos. So just in case in this particular cell, if I want to keep my selection as blank, I need a blank cell to be selected in my selection as well. And that's the reason I have gone ahead and selected this one extra blank silhouette. And I will hit Enter and then I will click on OK. The moment I did that, you will observe that my data validation is radio wheel. So if I click on this particular button or we'll all dot product IDs have appeared away and I can go ahead and make a selection from any of these. Now let's go ahead and look at sudden formatting features within data validation. So what I'm gonna do is I'm gonna select this particular cell again, go to data validation and click on Data Validation One more time. Over here, I have this particular option that's is input message. So I will click on that. And a wheel that is a tick mark on just pretty good option, that's his show input message when this particular cell is selected. So let's say I'm going to give the title away as click here. And I will say the input message is, Please input a choice. So whenever somebody goes and highlights this particular cell, you will observe that this particular message will pop up vetting it will say click here and input a choice. So that one can just go ahead and click on this button over here and make a choice. Now let's say if at all I tried to go ahead and input any other value, then the one that is listed out over here. So I have values didn't p 006? Let's say I tried to input a value as b 009, and let's see what happens. So once again, I got this particular error message and it has a stop sign. And it says, this value doesn't match the data validation restrictions defined for this particular cell. And then it gives me the option of retry, cancel and help. So in a way, it will not let me proceed until and unless I select the value that is available in my validation list. So I will just close this. So I want you to understand that how do we go ahead and set these restrictions in first place? And are there any exceptions that we can go ahead and sit? So what I'm gonna do is, uh, once again, I will select this particular cell where I have created the validation list. What do data validation like on data validation one more time. And over here I have this particular button called error alone. You know, right now you will observe that by default the stock option has been activated. And that's the reason whenever I go ahead and try to input any other value other than the one that is listed out over did it gives me this, but he could assign and does not let me proceed. It gives me an error message. Now over here, if I go ahead and click that, uh, two more options. So the one option is wanting to, let's say I go ahead and give it a title as wanting itself. And I would say check value. Okay, and now let's see what happens. Now. I'll try to go ahead and input a value like b 009. And when I do that, it says check the value. Do you really want to continue? Yes, no, cancel and hell, if at all i go ahead and click on Yes. It will still let me proceed further. So I am going to undo this particular setting. Once again, I will select this particular cell voter data validation. And this time instead of wanting, I will select will yet Information. And oh yeah, I will just say information. I would say check value, carefree. And now let's try entering another value. Let's see what happens. So now it says check value carefully. It's only giving me an information. And if I don't like, go ahead and click on ok, it will still proceed. So that's the basic difference between the stop wanting and information. Now why did we learn this cool trick of going ahead and creating a drop-down one to restrict the user from going ahead and entering any other value other than the one that is available in the list. And the second use of it you will understand when going ahead and creating dashboards because as and when the values within your validations changes, your dashboards will start acting dynamically and you will understand the application of it as and when you start creating your own dashboards. So having a quick understanding of the validation tool was the only objective of this particular short lecture. I hope you enjoyed this lecture and I shall see you in the next one.
18. Lesson 18 Combo Charts: Hey, welcome back. So focus in this particular lecture is gonna be on combination chart or something that we're also referred to as combo charts. Combo charts are nothing but a combination of two or more charts. And a combination chart is a visualization that combines the features of bar chart and a line chart. The combination chart displays the data using a number of bar and line charts, each of which represents a particular category. So let's learn it to this particular example that we have over you. So what I have over here on this particular screen is my sales data. In the upper row, which is my row number seven, I have the sales target for each and every month. And then in the next row, that is row number eight, I have the figures of the sales target that has been achieved, or in other words, sales met. And what I have done is I have gone ahead and plotted both these datasets on one single chart. So how did I do that? So let's go ahead and first construct a combination chart so that we understand it better. So I'm going to use the same data that I have over here. And it is also available in the raw data sheet that has been provided with the lecture. So let's quickly jump to our raw data over there. So what I'm gonna do is I'm gonna select this entire data set. And then I will click on Insert. And then I will come to this particular option that says line and area chart. And oh yeah, I'm going to select this particular option over here, which says Line with markers. And I will click on it. And you will observe that the moment I did that to line charts have been created on my screen. But this is not exactly what I want. Well, what you are, what is happening is my sales target is reflecting with blue lines, whereas my CS met target is reflected with the orange line. And I'm interested in going ahead and showing my sales met target using a bar graph. In other words, what I am going to do is in the same graph, I'm going to use a combination of line graphs and bar graphs together. So how do I achieve that? So to do that, what I'm gonna do is I'm gonna select my data for sales met, which is my orange line or video. And then I will go to the design tab and click on this option which says Change Chart Type. And when I click on it, a window pops up which says Change Chart Type. And over here at the bottom, I have my sales target data, which is shown by a blue line. And then I have my sales metadata, which is shown by an orange line. And you can see that both the chart types are lined with mockers. Now what I'm gonna do is I'm going to just go ahead and select the drop-down over here. And instead of a line with markers, I'm going to select a 2D column chart over you. And I will click on it and click on OK. One more time. And you will observe that the moment I did that, my combination chart is ready. Over the bar graphs reflect my CS met numbers, whereas the blue line over here shows my sales target for respective months. Now let me just go ahead and format this chart a little bit. So what I'm gonna do is I'm going to use a customize, ready-made template or a chart style. So I am going to select the chart, go to the design option. And I have been select this particular template or this particular chart style, and I will click on it. And what I'm gonna do is I will just format my bar graphs per little bit. So I'm going to select them. And then I will click on the chart elements and I will click on data labels. So what I've done is I've added data labels to my bar graphs over you. Also I will just right click on it and then click on format data series. And I will just go ahead and reduce the gap width. So currently my gap width is 290. Let me just bring it down to maybe a 100. And then I will close this. And now all I need to do is go ahead and give a chart title. So this is a line versus bar graph. So I can see that the sales target versus a cheap shot. With that, my combination chart is ready. Now before we proceed any further, there's one more scenario that I want you to look at now over yeah. I was looking at sales target and CFS met numbers and both being similar category of data. Showing them in a combination chart can be quite easy. But now let us look at another scenario over u. Then the variance between these two numbers is pretty high. So let's go to a sheet, two over here. And over here, what I have is I have a data for sales revenue, and then just below it, I have the data for sales unit. But you will observe that there is a huge variance between these two numbers. I mean, to appoint numbers are in the range of 12131415 thousand, whereas the lower numbers are in the range of 234 thousand. So what happens when I go ahead and plot a combination chart using this kind of data. So let's go ahead and construct a combination chart real quick. So I am going to select this data. Click on Insert. And then I will select line graphs with markers. And then I'm going to go ahead and convert my sales unit into bar graphs. And let's see what happens. So I'm gonna select this. Then I will click on Change Chart Type and my CSU unit. I will convert it into bar graphs. Let me just format it a little bit. So I'm gonna select this particular design. And then I will just select the bar graph, add data labels to it. And let me just go ahead and give it a title. So let's say this is revenue versus units. Now that is absolutely nothing wrong with this kind of a combination chart. But then if you look at this because of the huge variance between the numbers, it does not really look very catchy to me. It is somewhat kind of disturbing my ISO area because I have to look at this huge variance. And to a human eye, interpretation of this kind of data can be slightly difficult. So what can I do in that case? Not because both these variables have a huge variation in them, I can use something called as the secondary axis. So what I will do is I will go ahead and select my bar graph over you. And once again, I will go to Change Chart Type. And what I'm gonna do is I'm going to go ahead and put a checkmark on this particular option which says secondary axis. And when I click on OK. Let's see what happens. Now. What has happened over a year is for my bar graph, this particular secondary axis as being created. And all the values of my bar graphs are plotted against this particular axis over you. Whereas if you look at my line graph, the line graph is using this particular y-axis. So adding the secondary axis has actually solved the problem for me. Let me just go ahead and format the data bars over here. So I'm gonna right click on it, click on format data series. Then I will just reduce the gap width 200. So with that, my combination chart is ready. So there are two ways that we understood. One, when the variation is not much, we really do not need to go ahead and add a secondary axis to our combination chart. However, if the variation is very high, then in that case, we can go ahead and add a secondary axis. So in other words, a combination of bars and lines in a same visualization can be useful when comparing values in different categories. Since the combination gets a clear view of which category is higher or lower, combo charts can be very useful when you want to compare two or more data series that have different units of measurement. And one way you can use a Combo Chart is also to show actual values in a column together with a line that actually shows the goal or the target value. In a way, you can go ahead and create a target was met chart where the target is constant, whereas your bar graphs will show whether the target was met or exceeded or missed.
19. Lesson 19 Auditing a Worksheet in Excel: Hey, welcome back. So focus in this particular lecture will be on auditing a workbook in Excel. Spreadsheet, audits are designed to ensure a spreadsheet performs the way it was built to. This involves checking all cells are doing what was planned for them, and the data is processed as expected. And then there are no errors in put it into the sheet. So let's understand what do we mean by auditing the sheet? By looking at this example on the screen. So what I have over here is the name of students. And then I have the marks quote by these students in these individual subjects. Based upon these individual marks, we have the final total. And based on the total, now we also have the percentage scored by each of the individual students. Now, over here I have a column that says Passed in all subjects, okay? And then there is the next column that says DID pass overall. And then finally we have the grade. Now, if at all you just go ahead and receive this particular sheet without the information that you see over here. It would be really difficult for you to understand that. How did we come down to this conclusion? What does this yes. Over your means? Or how do you know that the student has actually gone ahead and scored a distinction, or a first grade or second grade. Now we're thankful that we have all the information over a year and we can read that information. But let's say if at all all this information was not there, then understanding this sheet would have been quite a problem. And this is where auditing a workbook gums handy to us. So let's understand that concept in detail over here. So I will just go ahead and make the hidden cells visible once again. Okay, so now let's understand how all these cells have these particular respective values. So as student passes in a subject, if he scores more than 40 in all subjects, okay? So unnecessarily, every student has to score at least 40 marks in each of the subjects to pass that particular subject. Okay? Also student needs to score more than 250 in total to pass all the subjects. Okay? So these are the two criteria's based upon which we will decide whether the student has actually passed overall. And did he pass in all the subjects. Okay, so for example, Tom has code more than 14 all the subjects. So he has passed in all the subjects over a year. And did he pass overall for that one more condition will be checked. That is the total should be more than 250. And as you can see over here, he satisfies both these conditions. Whereas in case of Ronny, he has scored 40 or more in all the subjects. Okay, so he's passed in all the subjects but his overall total is less than 250. So did he pass overall? No. And that's why his grade is fail. Now, if the percentage is greater than 75% and the student gets a distinction between 60.9974, he gets a first grade. If the percentage is less than 60, then he gets a second grade. And if he fails. He gets a fail grade. And oh, yeah, what I have is the total number of students who have passed the test. So now let's go ahead and understand the auditing process. So let's say if at all I want to go ahead and understand how is this total calculated. So I will go to this particular cell over here and I will click on Trace Precedence. And when I click on that, you can see that there is an arrow that is pointing towards these five cells. And if I go to my formula section over here, it says that it is the sum of C6 to G6. Ok? If at all, I click on the percentage and then click on trace precedents. It shows that over yeah, it is dependent upon this particular cell that is, the total. And total in turn is dependent upon these five cells over u. Ok. So this cell is dependent upon this particular cell, and this cell in turn is dependent upon these five cells over u. Okay, I'm gonna go ahead and remove the arrows. Now let's go ahead and look at the grade column over you. And if at all I click on distinction, and I go ahead and click on trace precedents. It shows that my distinction depends upon my percentage. My percentage in turn depends upon my total. And then my total depends upon these five cells. Okay, so that is how you can go ahead and trace the precedence. Or in other words, you can go ahead and check on which particular says, does the value in a particular cell depend upon? As in this case, the distinction depends upon the percentage. Percentage depends upon the total, and total in turn depends upon the marks code in individual subjects. So I will go ahead and remove the arrows over you. Now let's go ahead and look at the total students passed. And now what I will do is I will go ahead and click on trace precedents. You will observe the moment I did that it is pointing towards all these cells over u. Which means that the total students pass depends upon the values that have been set in all these cells. And if at all, like go to the Formulas section over Europe, it clearly says that it is a count of J6 to J3, which is this particular column. And it is counting the total number of yes in this particular column. If at all I click over here and then I click on trace precedents. You can see that once again, passing in all the subjects depends upon the mock sport in all the subjects collectively. Okay, so that's about tracing the precedents. Let's go ahead and remove the arrows once again. Now let's go ahead and understand What do you mean by dependence. So let's say if at all i go ahead and select this particular cell over here, that is the total. Okay? And I click on Trace Dependence. Now it clearly shows me that based upon my total percentage is dependent. Based upon my total, it is also decided whether he passed in all the subjects or not. And based upon my total, I will get my percentages. And then in turn, based upon my percentage, I will also know what grade have ice code. Okay. And all I select this particular cell over here and click on trace dependence. It clearly shows that passing in all the subjects will decide how many students in total health boss. So this particular cell or your is dependent upon this particular value over u. So that is how dependence work. So before you go ahead and delete probably a cell, a column, or a row in an Excel workbook. You can always use the auditing function in Excel so that you can avoid the reference errors because there might be other cells which might be dependent upon the cells that you probably are deleting and end workbook. It's always a good practice to run an audit on your worksheet before you go ahead and make any deletions or modifications within your workbook. Okay, so I'm gonna go ahead and remove all the arrows over here. And I want you to just go ahead and look at one more function over you. So if at all, I want to go ahead and understand how is this particular total calculated. So what I can do is I can go ahead and select this particular cell. And then I can click on Show Formulas. And when I click on show formulas, you will observe that it's not only showing me the formula for the cell that I have selected, it is actually going ahead and showing me all the formulas that have been set within this Excel worksheet. Okay, so wherever there is a formula, you can go ahead and see that formula using the Show formula function. I will go ahead and click on it once again, and the formula gets hidden. Okay, now I want you to look at one more feature while auditing a workbook in Excel. So let's say I'm looking at this percentage over u. Ok, and I want to go ahead and evaluate the formula. I want to understand how did we come to this percentage calculation? So what I can do is I can click on evaluate formula. And when I do that, it shows me that it has come down to this particular value by using the formula H6 divided by 500. If I want to understand what is this S6, then I can click on step in. And it shows me that H6 is nothing but the sum of C6 to G6. Okay, I can go ahead and step out. And then when I go ahead and step out, it shows me the exact calculation that has taken place in order to go ahead and calculate my percentage. So that's 401 divided by 500. Okay. I will go ahead and close this. Now let's go ahead and also try to evaluate the formula somewhere else on our worksheet over here. Let's say I go ahead and select this particular cell over here, and I click on evaluate formula. And when I do that, it shows me that the formula that has been used over your is, if minimum of C6 to G6 is greater than or equal to 40, then return the value as yes, as returned the value as no. If I click on evaluate over your first, it will go ahead and find the minimum value between C6 to G6. So let's go ahead and do that. And it says that the minimum value in this particular selection over year is to T6. So it has gone ahead and evaluated the value 256. And now it will go ahead and compare if 56 is greater than or equal to 40, and it will return the value as true or false. So I will click on evaluate and it returns the value as true because 56 is greater than or equal to 40. And now because it has gone ahead and return the value as two, it will go ahead and return the value as Yes. So I will click on evaluate once again and you can see that the value returned over you is yes. So Auditing in Excel is a helpful tool that allows you to step through each step of your formula. And combined with the Trace Precedents and dependencies and marking when you go in and actually edit a formula where you, you have a lot of tools to try to figure out exactly what's going on with any formula inside exit. So that's about auditing a worksheet in a nutshell. I hope you enjoyed this lecture and I shall see you in the next one.
20. Lesson 20 What if Analysis: Hey, welcome back. So our focus in this particular lecture will be on understanding a cool tool in Excel, which is called as the what-if analysis. Now, by using what-if analysis tool in Excel, you can use several different set of values in one or more formulas to explore all the various results. For example, you can do what if analysis to build two budgets that each assumes a certain level of revenue. All you can specify a result that you want the formula to produce and then determine what set of values will produce that particular result. An excel provides several different tools to help you perform this type of analysis that fits your need. So let's understand this with an example that we have over here. So let's say I'm going ahead and picking up a loan from a bank, okay? And I'm picking up alone for $60 thousand, the rate of interest is 8%, and the ten year for which I am going ahead and picking up this loan is 20 years, so this is 214 months. And what I'm trying to figure out is what is the EMI that I would be liable to pay at this particular rate of interest and for this particular tenure. So for that, I have gone ahead and use this particular formula where, you know, understanding the formula is not that important. But I would go ahead and quickly explain to you the kind of formula that I have gone ahead and used over u. So the formula goes like this, is equal to minus b and t. Ok. And the moment I did that it says, calculates the payment for a loan based on constant payments and constant interest rate. So when I open the bracket first, it is asking me for the read. So my rate of interest is 8%. So what I'm gonna do is I'm going to select this particular value. And then I'm going to divide by 12 because this is an annual rate of interest, then I will give a comma. It is asking me what is my NPER, okay? Or in other words, it is asking me what is the tenure for this particular load. So I am going to select this particular cell over you. And then I will give a comma. And then it is asking me what is a principal value? So the principle value is 60 thousand. And I will go ahead and close the brackets and hit Enter. It tells me to go ahead and pay off this loan in 20 years. I will have to go ahead and pn EMI off $501.86. Now, I want to go ahead and evaluate certain scenarios or wheel. So let's say what happens if I go ahead and take a loan of $100 or maybe I just tried to take the loan at a little bit of a lower rate of interest. So there can be various scenarios that I can come up with. Okay, so that is one cool tool in Excel that can help us to evaluate these various scenarios. And we can look at all the possible outcomes of BI scenarios in one go. So for that, what I can do is I can click on the Data tab away. You go to the what-if analysis function over you. And I will click on the drop-down over here. And then I will click on Scenario Manager. What I will do first is I will go ahead and add this particular scenario as my base scenario. So I will click on add, and I will call this as a base scenario. What I'm going to tell Excel is I'm going to keep on changing the cell values over u. Ok, so I'm going to change the amount, I will change the rate of interest and I will change the tenure. And based upon that, I would like to know what my EMI would be. So I will go ahead and click on ok. So I'm gonna go ahead and keep these same values in my base scenario, and I will click on ok. So this becomes my base scenario. Now let's go ahead and add another scenario. We'll, so I will click on add and I will say changing amount. Okay? And then I will click on okay. And I will go ahead and change the Amman from 60 thousand, maybe $1. And then I will click on OK. OK. And then when I go ahead and click on this particular scenario and then click on show, it quickly goes ahead and calculates the new EMI for me based upon the new amount that I have gone ahead and selected. Let's go ahead and add one more scenario where u. So I will click on Add. And now let's try changing the rate of interest. So I haven't saved changing interests over here. Click on OK. And now instead of 8%, let me just go ahead and try to see what will be my EMI at 6% rate of interest. I will click on, okay. And then I will click on changing interests and click on true. So quickly it does the calculation for me and shows me the new EMI I'm liable to pay at this newly laid-off interest. Okay, so now using the scenario manager, what I can do is I can go ahead and try various permutations and combinations. And based upon that, I can go ahead and evaluate a certain value. Now let's say I wanted to do something Reverse. Now what I'm trying to do over here is let's say I want to be an EMI of $1000. And with rate of interest at 6%, how much amount can I actually borrow so that I keep on paying an interest of $1000 every month. So for that, what I can do is I can go to the What If Analysis tool. But this time I'm going to use another tool called Goal Seek. And what I'm gonna do over here is I'm going to say set cell. And I'm going to select this particular cell over here. And I will give the value as 100 because I want to pay the EMI off one hundred, ten hundred. And now it is asking me what value should it go ahead and change in order to give us the EMI off 100. So I would say, go ahead and change my amount because I want to know how much amount can I borrow at 6% rate of interest. So I can't be an EMI off $1000 every month. I will go ahead and hit enter and hit enter one more time. And you will see that immediately it has gone ahead and done the calculation for me. And it shows that I trade off interest of 6% and photo tenure of 20 years. I can actually borrow one like $39,580.77, so that I will end up paying $1000 every month. Now let us try something is now let's say I want to go ahead and keep the amount as one like Dollars. And I want to go ahead and pay an EMI of $1000. But I only want to go ahead and change the rate of interest. So in that case, once again, I will go to my what-if analysis, go to my Goal Seek. And this time I will say set the cell C 112 value one hundred, ten hundred by changing the rate of interest or were you, which is my cell C8, and I would hit Enter and hit enter one more time. So now it tells me that if at all I want to pay an EMI off $1000 every month and I want to borrow one like dollar as my principal amount, then the rate of interest that I will have to pay is 11%. Once again, if at all I want to go back to my base scenario, I can always go back to my Scenario Manager and click on my base scenario and then click on Show. And that brings us back to the base scenario that we already started with. So innovate the what-if analysis gives me a lot of tools to be able to go ahead and create various scenarios, record these scenarios, and figured out exactly what I need to be playing around with my Goal Seek option. So that's about what-if analysis in a nutshell, I hope you enjoyed this lecture and I shall see you in the next one.
21. Lesson 21 Security and Password: Hey, welcome back. Now we have gone ahead and learn how to go ahead and create a worksheet. We learned all the different formulas. We learned the pivot tables and various tools and techniques in Excel. And using all the skills that we have learned through this entire lecture series, I have gone ahead and created this mall worksheet. Okay, so over here what I have is the name of various ports equipments. I have the name of the accompanies. Then I have the quantities. I have your price per unit and towards the right, I have the total sales and total sales in dollars. And then I have summed everything up away and I have the overall sales. Now, I'm planning to go ahead and send this worksheet to a few people. But I'm really concerned about the security of this particular worksheet. And when I send this worksheet, do a lot of people. I only want them to have a view at this particular worksheet. I don't want them to go ahead and make any accidental changes over here, for example, just go ahead and change the 634 to 600 maybe. And what will happen because of that is the value across the entire worksheet will change. So I don't want these accidental changes or any intentional changes to be done to this particular worksheet. So how can I go ahead and protect my worksheet? Now by default, any cell in Excel is by default locked. Now what do I mean by that? If all the cells in Excel are logged, and then how come I can just go to any cell and type anything over there. The reason is because these cells are locked but they are not protected. Now what do I mean by that? For example, if I go ahead and make a selection over you and right-click and click on format cells. And if I come to this particular tab away of it says protection. It shows that every cell in Excel is locked. Ok, but it does not protected. And because it is not protected, I can go ahead and make any changes in any of these sales. But now I want this particular worksheet to go to people only as a read only worksheet. And I don't want anybody to go ahead and make any changes to this worksheet whatsoever, then how can I go ahead and achieve that? It's very simple. What I'm gonna do is I'm gonna select any cell on this particular worksheet over here. And then I will click on Protect sheet. And when I do that, the small window pops up in front of me. It says protects cheat. Now, you can actually go ahead and add a password over Europe, which is recommended, but I leave that choice to you. You can even go ahead and protect your worksheet without adding a password. But once you go ahead and click on this particular button over here that is OK, you need to understand that it will only allow the users to make any changes to the worksheet where there is a checkmark. For example, it will allow the user to go ahead and select the log says. It will allow them to go ahead and select the unlock cells, but it will not allow them to go ahead and format the cells are format any columns or format any rows. Okay? So anything which does not have a checkmark on it will not be allowed. Let's look at that. So what I'm gonna do is I'm gonna go ahead and click on OK. yeoja. And the moment I did that you will observe that this particular button protects sheet has now turned into unprotected sheet. Okay. Which means right now my worksheet is protected. Now let's try going ahead and making some changes over your, Let's try changing this 450 to 500. Ok. You can see that immediately. It goes ahead and pops up an error message on the screen and it says the set or chart you're trying to change is, is on a protected sheet to make changes and predict the sheet. And you might be requested to enter a password. Right now I don't have any password over here. So what I can do is I can just unprotected this sheet. And now if I tried to make any changes over here, it will go ahead and accept those changes. Okay, let's go ahead and undo the changes now. Now let's look at another scenario. Okay, so what I have done over here is I have added certain formulas over here. So I can easily say that my total sales is nothing but the sum of this particular cell plus this l plus G8 plus i8, et cetera. But I don't want anybody to go ahead and look at this formula. So when I go ahead and click on any of these cells where I have a formula, I want to go ahead and hide these formulas. I don't want anybody to see this, these formulas over here. So what I can do is I can just go ahead and select these cells over here. Okay? And then I can right click on it, click on format cells. And over here, I will go ahead and put a check mark on this particular option that says Hayden. And I will click on OK. And now what I will do is I will just go ahead and protect my sheet. Once again, I will not add any password over here. So I will go ahead and click on OK. And now if I click on any of these cells over here, you can see that the formulas are not visible. If I want to go ahead and make these formulas visible, once again, I can go ahead and unprotected sheet. Select these cells, right click on it. Click on format cells, removed this checkmark from hidden. Click on OK. And then if I go ahead and click on any of the cells over here, I can once again see all the formulas over here. I can even go ahead and hide certain formulas in my worksheet using this particular technique. Now let's try putting a password over you. So what I will do is I will go ahead and select this particular worksheet. And then I will go ahead and click on vertex sheet. Okay? And this time what I will do is I will add a password. The password has 1234. When I hit Enter it, asking me to go ahead and we enter the password. But just remember that if you lose or forget the password, it cannot be recovered. So make sure you use a password that you remember very easily. So I'm gonna go ahead and re-enter the password again. It okay. Okay. And now if I tried to protect the sheet, it's asking me for the password. So I will go ahead and enter the password. Hit OK. And the sheet is unprotected once again. Now I want you to pay attention to one more aspect of protecting the workbook. Now let's say I only want the user to be able to make changes to this particular section, okay? And he should not be able to make changes to any of the formulas that I see over u. Ok? The only section where a user should be able to make changes is this particular section. Then how can I achieve that? It's very simple. What I can do is I can just select these cells over here. Right-click on it. Go to Format Cells. And I will just go ahead and I'm tick this, but he could option that C is locked. Then I would click on OK. And now I will go ahead and protect my sheet. I'll not add any password as of now. I'll just go ahead and click on OK. And now if I try to make any changes over here, you will see that it's not allowing me to go ahead and make any changes. But these cells are unlocked, so I will be able to go and make any changes that I want over here. So let's, let's try making this bold 50 to 500. And it is taking all these changes. Let's try making a change over a year. And you can see I'm able to go ahead and make those changes. Let's undo these changes now. And once again, let's unprotected our worksheet. Okay, so that's about going ahead and protecting your worksheet. So that was about protecting a worksheet. But what if I want to go ahead and protect the structure of my workbook? What do I mean by that? So let's say if at all i go ahead and send this workbook to a few people. So one thing I want to ensure is they should not be able to make any changes to this particular worksheet and that I can take care of through the protect sheet option. But at the same time, I also want them to not make any changes to the structure of this particular workbook. For example, they should not be able to go ahead and rename any sheet over here. For example, this particular sheet over here is called basic pivot. So somebody can just go ahead right click on it, click on Rename and renamed the name of that particular worksheet. Or someone can just go ahead and right-click and just delete a particular worksheet in my workbook. And I don't want these changes to happen. So in that case, what I can do is I can go ahead and protect my workbook. But just remember, protecting the workbook will only protect this structure of the Workbook. Ok, so let's understand how do we do that. So what I can do is I can just go to my review tab. Once again, click on Protect workbook, and now it will ask me for the password. So I'll enter the password as 1234. And then once again I will reenter the password and click on OK. And now if I tried to go ahead and make any changes to the structure of my workbook. Let's say I tried to go ahead and delete this particular worksheet. If I right-click on it, it does not give me that particular option. Okay. The delete option is grayed out. If I tried to rename a particular worksheet, even that is grayed out so I cannot insert, delete, rename, Move or Copy. All I can do is just protect the sheet or I can just view the code. Any other option within the structure of my workbook will be grayed out. So that's about protecting the workbook. If I want to go ahead and unprotectable book, all I need to do is just click on this button once again, reenter the password, and then hit OK. And now if I go ahead and right-click on any of the sheet so I can go ahead and modify this structure of my workbook. That's about going ahead and protecting the structure of your workbook using that protect workbook option. Now last but not the least, let say I want to go ahead and send this entire workbook to a lot of people. But I only want those set of people to go ahead and look at this particular workbook who have the required password to access this particular workbook. So if at all they don't have the access to the password, they will not be able to use the workbook at all. The workbook will not just open. So how can I do that? It's very simple. So what I can do is I can go to the File option over here. Click on the info tab over you. And then that is an option called protect workbook. So I will click on this particular option over you, and then I will click on this. But he could option that says encrypt with password. And now I will go ahead and give it a password and make sure that you also remember the password that you're punching in. Because if you forget the password, it's very difficult to go ahead and retrieve the password. So I will go ahead and enter the password as 1234. It will ask me to re-enter the password. Okay, and now what I will do is I haven't saved this particular file on my desktop. Okay, the name of the file is new workbook advanced copy. So I will save it. And now I will just close it. And now if I try to go ahead and open this particular file from my desktop, let's see what happens. It's asking me for a password. So without the password, it will not let me in and it will not lead me access the workbook at all. If I go ahead and punch in the password, you can see that this particular worksheet just opens up. And in case if you want to go ahead and remove the password from this workbook, then what I can do is I can once again go to the File option, click on Protect workbook, click on encrypted password. I, what I will do is I will just delete the password from you, and then I will click on OK. And now once again, what I can do is I can just go to File and then click on save and then close this file. And now if I tried to open this file, you can see that it opens up without a password. So that was about password and security in Excel. And I hope you learned that what protection can do and cannot do for you when it comes to data security in Excel. So I hope you enjoyed this lecture and I shall see you in the next one.
22. Lesson 22 Recording Macro : Hey, welcome back. Now as you have come so far in this particular journey, you must have realized the power of the software package of Microsoft Excel. But in an advanced section, if you're not aware of macros, your journey is not yet over. Macros can automate common and repetitive keystrokes that are you using Excel to create and edit spreadsheets. And by reducing the number of keystrokes required to perform common commands, macros can speed up your production and reduced the time you have to spend staring at the electronic spreadsheet each day. So let's understand that with an example of a year and let's explore the power of macros. So what I have in front of me is a report created for do not cherry academy. They probably run some kind of a training institute and they conduct a test every week. And every week, they have to go ahead and publish this particular report, very initial the marks scored by each of the students who have enrolled in this particular course. Then finally, they give a summary in the format of highest marks scored. The lowest marks quote, the total students who have passed the test. What is the passing percentage of the week? And then they also show the topper of the vk. But the data that they receive from the system is in a raw format like this, okay? And every week what they have to do is use this particular data and then arrange it in such a way that it looks like a report, as you can see on the screen right now. So this is kind of a repetitive thing that they probably have to do every week. And Yen is where macros can come really handy. Let's understand how now in order to run macros, what I need is a Developer tab. And if I look at the top over your, I don't see my Developer tab anywhere over here. Now in order to get my Developer tab, what I can do is I can just right click on any of the tabs over you. And then I will click on, Customize the Ribbon. And when I click on that, I will click on this particular tab called customize Rubin. Over here. In this dropdown, I will select Popular Commands. And then if I look towards the right-hand side, I can see the Developer tab over you. I will go ahead and put a check mark on it and then click on, okay, the moment I did that, the developer tab over u has appeared. Now what I need to do over here is in order to go ahead and record a macro, I need to go to the Developer tab. And when I come down to the Developer tab over here, I will go to this particular section called the code. And over here I have this particular button which is called as Record Macro. And this is the button that we're going to go ahead and use for recording our first macro. So what I will do now is first, I will go ahead and pick up my raw data from this particular sheet. Okay? And then I will go ahead and paste the data over you. And now what I would do is I would go to this particular button called Record Macro, and I'm gonna go ahead and click on it. When I click on it, you can see that a small window pops up, which is asking me for the name of the macro. So I'm gonna go ahead and call my macro, add a report macro. The next thing that it is asking me Is the shortcut key. So let's go ahead and create a small shortcut key for our macro. So I'm gonna go ahead and use Control and the letter L as a shortcut for this particular macro. Okay? And then I will go ahead and click on okay. When I go ahead and do that, you can observe that my record macro button as now transformed into a Stop Recording button, which means now my macro is recording, okay? And from this point onwards you need to understand that every click on the screen is now being recorded by your macro. Okay? So what I'm gonna do first of all, is I'm going to go ahead and sort this data. So what I'm gonna do is I will just select this entire data YOU I will go to the Data tab. I will click on sought. And then what I will do is I will sorted on the basis of mocks good, from largest to smallest, and then click on OK. And now what I will do is I will highlight this particular header. We'll click on My Home tab, and I will go ahead and give it a nice blue shape. Okay, now I need the highest monks, good. So I'm going to say is equal to max. And then I will select this entire range over here. And then I will hit enter. Okay, so it picked up that the highest marks code in this particular ranges, 97. Then I want the lowest marks code in this particular range. So I'm gonna say is equal to min. And I'm going to open brackets. I will select this range, and then I will close the bracket and hit enter. Now I want the total students bust. So let's say the criteria for parsing is 40 months. At least. What I'm going to see is is equal to COUNTIF. And then I will open brackets. I will give this particular range over a year. And then I will set my criteria. And for that I will open up the double-quotes. I will say greater than or equal to w goods type and and then the number 40. And then I will close the brackets and hit enter. Okay. And for passing percentage, I'm going to say the total number of students passed divide by the total number of students. That is 16. So that gives me the passing percentage. And now what I also need is the Doppler of the vk. So for that, what I'm gonna do is first, I will go ahead and use the match function. It's asking me for my lookup value, so I am going to look for the highest marks. Good. I will go ahead and give a comma. Then it's asking me for the lookup at eoyo. So I will select this at a, give a comma and I'll say exact match. And then I will close the brackets. Ok. And now what I want is the name of the Doppler. So over here what I am going to do is I will go ahead and say index. Open brackets. Then it's asking me in which particular array. So I will say this particular area, we'll, then I will give a comma. It's asking me for the row number. So this row number will be deduced from the match command over u. So I will go ahead and give a comma whale and the column number, I will go ahead and give it as one, and then I will close the brackets and hit Enter. So now it tells me that the topper of the week is Judith. And with that, my report is ready. All I need to do now is just remove the gridlines. So I will select this entire sheet. Click on the View tab, remove the grid lines, okay, and then I will just go ahead and select this data. Go to the Home tab. Then I will give it borders. Ok. I will go ahead and align this in the center where you're OK. And then finally, what I will do is I will give it a nice thick border from outside. Okay, and with that, our recording is o. So what I'm gonna do is I will go to my developer tab and click on stop recording. Okay? And with that, you will observe that the recording is over. And now if I go ahead and click on the macros over your eye can see that new macro is available in the list of my macros and the name of the macro is report. Okay, so I will go ahead and close this. And now let's say if at all, I want to go ahead and create a report for a new week. So I, what I will do is I will once again go to my raw data or where you go ahead and copy this data, then I will come to buy a report worksheet. I will go to week three. I will just go ahead and paste the data we are. And now, in order to go ahead and create the report, all I need to do is just go ahead and run the macro. So there are two ways of running the macro. I can't just go to the macro stamp and then I can select this particular macro and then click on Run. And you will observe that immediately. It has gone ahead and created the report for us for our V3. Okay, now let's try running the macro using this shortcut that we had created. And the shortcut that we had created was control l. So what I'm gonna do is I will go to this particular raw data sheet, OEO. Okay? I will select this data, copy it. And then what I will do is I will come down to my week four and I will paste the data or YOU. Okay? And now this time instead of going ahead and clicking on macros and running the macros, what I'm gonna do is I'm gonna go ahead and press the shortcut key on my keyboard that is Control L. And let's see what happens. When I did that. You can see that immediately. It has gone ahead and created the new report for us. As you can see, the highest marks code is Sean, that is 99, the lowest is 39. Goodbye Tom. Total student passed is 15, and therefore the passing percentage is 93.75. And the topper of the week is Sean. Okay, so that's how you can go ahead and use macros for going ahead and completing your repetitive task by reducing the number of keystrokes on your keyboard and saving you a lot of time. So by now you must have understood that macros is a term of series of commands and functions that are stored in Microsoft Excel. And it can be run whenever you need to perform tasks which are associated with those commands. And in a particular worksheet, you can save a lot of different microbes with a lot of different shortcuts, and then use them as per your need in your worksheet. So with that, we come to the end of the lecture series on Advanced Microsoft Excel. I hope you enjoyed this entire lecture series and I'm really thrilled on your course completion. So I wish you all the very best for all your future endeavors. Happy learning and stabilised.
The Guruskool, Data Analytics & Business Intelligence Leader
