Microsoft Excel Pivot Table Masterclass- Become a Pivot Table SuperHero

1. 1 Introduction : Hello and welcome to the ultimate pivot table master class from no ice to ninja. What exactly are pivot tables? Pivot tables are powerful tool within Excel that can be used to analyze, sort, filter, and present data in an understandable way. It is called a pivot table because you can rotate its row and column headings, the chordata area to give you a different view of the source data. As the source data changes, you can update a pivot table, giving you a real-time view of the running statistics. Now let's take a small glimpse at what you will learn in this particular course. Not what you see on your screen is a large, cumbersome data with thousands of records from a restaurant. It shows the name of the item sold, categories, groups, the city by seats, the sales value, and lots of other data. What if I told you that with the power of PivotTable, just in a few clicks, you can go ahead and convert this data into a report like this, like this, like this, or even like this. And that is the magic of PivotTable. Just in a few clicks, you can go ahead and gun word you are large, cumbersome data into a meaningful report that can be used for analyzing and decision-making. The best part about this course is it comes with a fully loaded resource file. The result file has ample of raw data for your practice and the best part is it is indexed. So all you have to do is just click on the index as you move along the topics and it will open up the topic of your choice. What are the advantages of learning? Pivottables? Pivottables are user-friendly. Pivottables can create instant data for you. Pivottables make data analysis easier. Pivot tables are easy to update. Pivot tables summarizes data easily. They can be used to assist in finding data patterns. And last but not the least, pivot tables create accurate reports at a faster speed. So what will you learn in this particular course? First of all, we will cover the basics of PivotTable. Then we will understand how do you go ahead and prepare your source data. We'll then get familiar with various pivot table layout, designs and styles. Then we will deep dive into data analysis using pivot table. Learned the art of formatting and presenting pivot reports in different styles. They will understand data visualization and pivot charts. They will then spend some time together in automating and release considerations of pivot tables. Last but not the least, I will share with you some really useful tips that will help you in presenting your final report in the most professional manner. So why should one take this course only? Because not only this course comes with 30 plus brilliant video tutorials. It is taught by a highly qualified instructors from the industry. The best part about this course, please, you can learn this course at your personalized learning piece. The tutorials are short and broken down into smaller chunks for easy understanding. There are ample of resources provided with this course for your understanding and practice. The best part about this course is this course comes with a lifetime access. With time, there will be regular updates that will be updated in this particular course. You're learning never stops. One thing I can assure you that by the end of the course, you will master the art of pivot tables. By the end of the course, you will become an Excel PivotTable Ninja. What are we reading for? I'm super excited to see you inside the course. See you there. 2. 2 Creating Your First Pivot Table: Hey, welcome back. Now that we have some good understanding in terms of what a pivot table does. We will go ahead and create our first pivot table in this particular lecture. Now, even before we go ahead and create a pivot table, it's very necessary that we first understand the data that we have at our hand. Now let us look at this data on our screen. Now this is a data of a restaurant within a day by sales have been captured. Now let's look at this data very carefully over you. In the first column we have the sales ID. In the second column we have the sales date, then we have the store ID. So as you can see, my stores are located in various cities based upon the location they have stored ideal for you. Then I have the item code. You can see that I have various items in my list. For example, ALU Friday rule Boone is Julia neural. These are items and every item has a unique item code. Now, every item falls under a particular category. Every category falls under a particular group. For example, ALU fry, it all falls under the category of daily bites. And daily bites falls under the group of snacks. But if you look at something like juicy, it falls under the category of soft drinks, and then it comes under the group of drinks. Then I have the unit sword, I have the price, and then I have the sales value, which is nothing but unit sold multiplied by the price per unit. Next, I have the hours and the minutes at which this particular sale was made. And then I have done a number of the week. For example, some data will be for my first week, then maybe second week, third week and forth tweak and so onwards. Now what do I want to accomplish out of this particular data with you? Now, out of this particular data, I want to go ahead and create my first pivot table like this. Now if you look at this particular pivot table, you will observe that it shows the group at the top, just below the group that is categories. And then I have my city device sales data were you I have drinks and under the drinks I have the various categories. Then I have the snacks, I have the various categories. Ovo, I have the total sales value, and then I have the grand totals. And this is what I want to accomplish out of this particular data that I have at my hand. What I'm gonna do is first of all, I'm going to copy this entire data and put it on a blank Excel sheet. And now that I have my data on a blank Excel sheet, what I'm going to do is I will select any one of the cells on this particular data sheet. Then I will click on Insert. Then I will click on PivotTable. This will give me this particular pop-up on the screen that says Create PivotTable. Then it gives me two options. New worksheet, existing worksheet. Now before that, I want you to observe that when I click on any of the cells within this particular data, it has already selected my data range. I don't have to go ahead and select the data range manually. It has already done it for me. Now what I'm gonna do is I'm going to select a new worksheet because I want to create this pivot table on a new worksheet altogether. I will click on, Okay. When I do that, it has opened up a new worksheet altogether. Or you will see my PivotTable option. And then off to the right of the screen you will absorb my pivot table fields. If you look at these pivot table fields over you, you will observe that these are nothing but the headers that I have in my data sheet. For example, ID sales data store ID item code. These are nothing but the headers that I have over you. And I'm going to make use of these pivot table fields in order to go ahead and create my pivot table. What I want first of all, is I want the group over u. I'm going to pick up the group and put it under my row section. Are you so it has popped up the drinks and snacks group for me. Just under the group, I want my categories. I'm going to pick up the categories and I'm going to place it where you, you will also see that post I have my group. Just below my group, I have my categories. Now I need the name of the cities in my columns over you. What I'm gonna do is I will select the city and I replace it with you. That has bought up all the names of the city, Zoe. Now what I want is the total sale value. I'm going to pick up the total sale value and I'm going to put it under the values box over u. The moment I did that in my pivot table is almost ready. Now you will absorb that by default it has selected the sum of the sales value. In fact, all I want to go ahead and keep this value, maybe like a count or maybe an average. I can't do that. What I just did was I clicked over you and I clicked on this particular button called Value Field Settings. And instead of some, let's say I wanted to count up the sales. And if I click on Count and click on, Okay, it gives me the count of the sales. But right now I'm interested in the sum of the sales. So once again, I will click over here to Value Field Settings and I will change it to sum. Then I will click on, okay. Okay. Now you will observe that this particular pivot table over here has a slightly different look and feel as compared to the pivot table in my original sheet, my audit didn't shoot looks something like this, so it has a different look and field. So how do I go ahead and create the same look and feel in my other pivot table. So I will come back to my newly created pivot table over you. I will select this pivot table. Then I will click on Design. And when I click on design, I have Pivot Table Styles, OEO. If I click on this particular drop-down over you, I have more styles. I can pick up any style of my choice. So I want to select this one. You can select any design of your choice depends completely upon your wheel. I'm going to select this. And with that, I have gone ahead and created my first pivot table. Did you observe how easy it is? Just with a few clicks yet, and then you can go ahead and create a pivot table that will give you more meaningful data that you can actually look at, analyze, and make some sense out of food. That's how you go ahead and create a pivot table really quick. In the next few lectures, we will know some more features about pivot table. And then gradually we will move on to learn some advanced features and reporting structures in pivot table. I hope you enjoyed this lecture and I shall see you in the next one. 3. 3 Field Well Options: Hey, welcome back. Now that we have become familiar with pivot table and I have gone ahead and created our first pivot table. Let us understand how do we go ahead and make any modifications within our pivot table? And what are the options that are available for us in case we want to make any changes to our existing pivot table. Now the answer is simple. If I go ahead and click anywhere on my pivot table, you will observe that on the right-hand side of the screen, this menu pops up, which is pivot table fields. Now at the top, as I mentioned, we have all the headers that we have in our existing database. We have these four sections. The first one is the filters, then we have the columns. The third one is the rows, and the last one is the values. And any changes that we want to make in our pivot table, we will be doing it with the help bulb this particular menu over you. Let's say for example, under the category I wanted to go ahead and add the item name as well. And you will see that immediately that changes reflect within my pivot table. As I mentioned, this pretty good a section comes really very handy in case we want to play around with our pivot table. But then let's understand a few things about it. Now currently, I'm working on a very big screen and therefore, this entire section is quite visible to me. But let's say if you're working on a small screen or maybe a small laptop, then what happens is all the fields and all these sections are not visible very clearly. Therefore, excel gives us a very wonderful option to go around and play with it. If you see this particular gear button, OEO, if I click on it, it gives me an option to go ahead and change the look and feel of my pivot table fields. Let's say if I go ahead and click on this, you will observe that the alignment of all the sections have been changed from horizontal to vertical door, it works exactly the same. It can be convenient for some people. If I go ahead and click on the next option over here, it gives me the field section option. However, the other four sections within my pivot table fields have vanished. If I go ahead and click on this boat option that says area section only, my friends section has completely disappeared. If I go ahead and click on this area sections only, you can see that now it has been aligned vertically. However, my field section is still not visible. You can go ahead and play around with this section based upon your convenience. If we go back to my default settings over here because I'm more convenient working with it. Now I want you to pay attention to this particular option that says defer layout update. And let's see what happens over u. When I go ahead and click on this particular option or you. Instead of going ahead and picking up any items from this particular section, we are the areas section. I can just go ahead and drop it over your anywhere outside. Let's say I don't want the category. I will go ahead and drop it over here. But he will observe that nothing has actually changed within my pivot table. Now in order to go ahead and make those changes visible, I will have to go ahead and click on the Update button. The moment I did that, you will observe that might pivot table has changed. Now let me just go ahead and click on my pivot table once again. Let me just go ahead and set it back to the default settings. So this is how my PivotTable looked earlier. Now let's say if I told by mistake I go ahead and hit this close button over here. Now I go ahead and click on my pivot table. And the pivot table fields are not appearing at odd. How do I go ahead and get them back? Very simply, what I can do is I can right-click anywhere on my pivot table and click on Show Field List option over you. And the pivot table fields are visible. Let's say I go ahead and close them once again. When I click on my PivotTable, the pivot table fields are not appearing. Another option to go ahead and access them is click on your pivot table. Go to the Analyze tab and click on this particular button that says will list your pivot table fields will appear. That's how you can go ahead and play around with the female options in PivotTable. I hope you enjoyed this lecture and I shall see you in the next one. 4. 4 Cleaning the Source Data : Hey, welcome back. So now we created our first pivot table and we also got familiar with the field well, options. Now, before we proceed any further, I want you to make note of certain things. Now, whenever you want to prepare a wonderful dish, you have to ensure that all the ingredients are in. Probably you need the perfect ingredients to make the perfect dish. Now similarly, in order to go ahead and create a perfect pivot table, you need to provide it with a perfect data. Your pivot table needs to consume this data in order to give you a report that is accurate. Now what do I mean by that? There are certain rules that one needs to follow before going ahead and starting to prepare a pivot table using your data. Let's understand them one-by-one. Now, first of all, any data that you provide for your pivot table has to have headers. Okay? So if you look at this particular data over you, it has headers in the first row of the dataset. It has the ID, it has the sales date stored ID, units, items, categories, etc. It fulfills my first criteria. It has the headers in the false, true. Now the second criteria for providing any data for a pivot table is that your data should be consistent. Now what do I mean by that? Now let's look at the sales date column over you. If you observe that entire data in my sales date column is in a date format. And you will observe that even if I scroll down, it is maintaining that consistent format. Now my store ID is a numerical value. And if I scroll down, you will observe that all stored ID item codes and category codes are numerical values. Similarly, my item name, category, group, city, alphabetical values, that T2 is consistent. Not taught. Very important thing before starting a pivot table is that your existing data should not have any grand totals are subtotals because that is the job of the pivot table. Pivot table will do it for you. If your data already has a grand total or a subtotal, ensure that you remove it before you start preparing the pivot table. And last but not the least, you have to ensure that your dataset does not have any blank rows or columns. Now I'll tell you the reason why. Let's say if I go ahead and select this first cell in my dataset, I press Control, Shift and down-arrow. You will observe that even though that is data after the 13th row, my dataset is not picking up any row postdoc. And that is because the row number 15 is completely blank, it has stopped or there. Similarly, if I press Control Shift and right arrow key, you will observe that my data has only moved to the E column because the F column is completely blank. If I still try to go ahead and manually collect this data and try to create a pivot table out of it. It will give me a lot of errors with lot of blank rows and columns available in my dataset. It's my first job to ensure that I go ahead and clean all these blank rows and columns in my dataset before I go ahead and proceed in constructing my pivot table. So how do I go ahead and do that? Let's understand it step-by-step. So now what I'm going to do is I'm gonna go ahead and select this entire first row in my dataset. I'm gonna best Control Shift and the End key on my keyboard. When I do that, my entire dataset has been selected. Now what I'm gonna do is I'm gonna go ahead and click on the Home tab or you. Then I will select this option that says Format as Table. When I click on that, you will absorb that an entire menu has popped up with different layouts of the table. You can select the layout of your choice. So I'm gonna select this one. When I do that, it has automatically selected my dataset. I will go ahead and put a checkmark on this particular option that says my table has headers because my dataset already has headers. I'm going to go ahead and click on. Okay. When I do that, you will observe that my entire dataset is now being converted into a beautiful data table. Now, we can start with the cleaning process. If I go ahead and look at the headers over my head or has had their respective names like ID, sales date stored ID item code. But it says something like column one, which means that this is a blank column. And if I go ahead and click on this drop-down over you, you will observe that it has nothing but blanks. What I can do is I can just go ahead and right-click and delete this blank column over you. As I move further, you will observe that it says column to. Once again, When I click on this drop-down, it is nothing but blanks. What I can do is I can just once again right-click on it and click on Delete. Now, I need to go ahead and clean the blank Rosario. Let's see if there are more than one blank row in my dataset. I have one over you're at row number 15. If I scroll down, there's one at row number 46, then at row number 81, and so on and so forth. So there may be multiple blank rows within my dataset. And it's practically impossible for me to go ahead and keep searching total blank row one-by-one, and then delete it because my dataset can be in thousands and that could be more than a hundreds of blank rows within my dataset. How do I go ahead and fix it in just one group? What I can do is I can select any one of the headers in my dataset. Then what I'm gonna do is I will select this option that says sort from smallest to largest. The moment I did that my data is now sorted on the basis of the id from smallest to largest digit. And now if I scroll to the bottom of this data, you will absorb that all my blank rows have come down to the bottom of my dataset. If I 100150025,003, all these three rows are nothing but blanks. What I can do is I can just select them, right-click on it and delete it. And then I will go back to the top of my dataset. Now, if I go ahead and look at this particular data and I tried to select it by pressing Control Shift and down. You will observe that my entire dataset God selected. And that is because it does not have any blank rows, are blank columns. Now, that is the kind of clean data that you need before you go ahead and provide this data to your pivot table. Because the pivot table will only consume the data in the right fashion if it is properly cleaned and provided to it in the right format. So to summarize it all, just ensure that your dataset, it has good headers and chart that data is consistent. Then ensure that your dataset does not have any grand totals are subtotals. And last but not the least in short, that there are no blank rows, are blank columns within your dataset. That's about cleaning the dataset for your PivotTable. I hope you enjoyed this lecture and I shall see you in the next one. 5. 5 Using Data Tables: Hey, welcome back. Now in this particular lecture, we're going to look at two different styles of making a pivot table. Once we look at both the styles, then I will tell you why I prefer the one or the other. Now somebody who's new to pivot table, we will always use the traditional way of making a pivot table that we learned in the previous lectures. And it's quite easy. What we do usually is we just selected dataset and convert it into a pivot table and a pivot table is ready. However, there are certain disadvantages to that. Let's understand what are the disadvantages. Let's say I had this particular data in front of me. If I scroll down, I can see that overall I have it on 99 records over a 100. True is my last row in this dataset. Now I'm going to use the traditional way of going ahead and creating a pivot table out of this, what I will do is I will click anywhere on this particular dataset, click on Insert, go to Pivot Table. And the moment I did that, you will observe that it has by default selected my entire data range. But in the 100 throat, they didn't have the 1990s record is the last row in my dataset. Now what I'm gonna do is I'm going to go ahead and create the pivot table on this existing worksheet itself. I go ahead and click on existing worksheet and then I define the location. So let's say I want my pivot table YOU. Then I click on, Okay, now my pivot table menu has popped up. Now all I need to do is some drag and drop and my pivot table will be ready. Let's quickly go ahead and create that. So what I'm gonna do is I'm going to add the group, so I will add the category. Uh, we'll, we'll go ahead and add the name of the cities. And then I will go ahead and add the units. Now with that, my pivot table is ready. What is the disadvantage of you? Let's look at it. What I'm gonna do is I'm going to scroll to the bottom of this data table over you. And I'm going to go ahead and add one more record over u. Let's say I'm going to add a 100 triggered. And I will just go ahead and copy this same data. But now instead of 11 units, I'm gonna go ahead and make it 1000 units. What I've done is under the drinks group, under the fruits category, the item name pomegranate juice has been added with 1000 units. Now ideally, this data should reflect in my pivot table over u. Let's see if I rephrase this pivot table, what happens? I click and I go ahead and refresh the pivot table. Absolutely no change under the fruit do section. There are no changes at all. Now, why is that? Now? That is because by default, Excel has defined the range and taken the a 100th row as the last row in my dataset. In any data added after that has not been incorporated in this pivot table. What do I need to do now? I need to go ahead and click on Analyze. Then I will go ahead and click on Change Data Source and click on this option for you. Now instead of a 100, I will go ahead and add a 101, true also in my dataset. Manually doing it, I will click on OK. The moment I clicked on okay, you will observe that now the new changes have been incorporated under my fruit do section under Chen night. I don't thousand units have been added. If I expand this photo and add the item name, you will observe that pomegranate juice with 1000 units have been added to my data. But all this was manual. And when you're working with Excel, most of your data is dynamic. You keep on adding and subtracting data every single day. It's not gonna be manually possible for you to every now and then, define the data source. You want something to be automated and you want Excel to pick it up all by itself. How do you go ahead and do that? Let's look at our second method of doing it. I'm going to close this. Now what I'm gonna do is I'm going to copy the same data on another blank Excel sheet. Okay? Now, before I go ahead and make a pivot table, I'm gonna make a small change. A small change in my methodology. What I'm gonna do is I'm going to select this data were you. Then I will click on the Home tab, and then I will click on this particular button OVO Format as Table. Once again, it pops up a menu with different layouts. So I'm gonna select the layout of my choice. Then I will go ahead and click on, Okay. Now before I click on Okay, you will observe that by default it has gone ahead and selected the range. Make sure you click on this particular option. Put a checkmark on my table has headers. And then click on, Okay. Now the moment I did that, my entire dataset is converted into a beautiful data table. 100th record. I need to go ahead and delete this for now. Now I have the 99th record over you as the last record in my dataset. Now let's go ahead and create a pivot table out of this, I'm going to select any particular cell in this data table. I will click on Insert and then I will click on Pivot Table. You will observe that this time instead of the range it is showing table. Because this is the name of my table. To confirm, you can observe this particular section over you. Let in it shows the name of the table and the name of the table is table two and that is what it has picked up. What I'm gonna do is every once again select existing worksheet. I will give the location or you. Then I will click on the pivot table menu has popped up. So let's go ahead and create the same pivot table. What I'm gonna do is I'm going to add the groups. I will add the category, I will add the units, and then I will add the name of the cities. Okay? Now my pivot table is ready. Now, let us see what happens if I go ahead and make any additions or subtractions in my existing DataTable. What I'm gonna do is I'm going to copy this particular data once again. Now once again, instead of 11 units, I'm gonna make it at 1000 units. One thing that you need to absorb over u is the moment I added one more record. You will see that it has become a part of my table. The colors have changed and it is now a part of my table itself. Now if I go ahead and right-click and replace this data, you will observe that the picked up the new row automatically incorporated it in the dataset. And now my pivot table is showing the new data. If I just double-click on this and then click on the item name, you will observe that 1000 units of pomegranate juice have been added to my dataset. This is basically the advantage of going ahead and converting your data into a data table first and then converting it into a pivot table. Because as I mentioned earlier, you work on Excel sheets, day-in, day-out, you make a lot of changes to them. You need to ensure that when you make any kind of reports and if you need to refresh those reports, it has to be done with minimum number of cliques, with minimum manual intervention. These are the two ways of going ahead and creating a pivot table. And as I mentioned, the second way is more better as compared to the first one due to the advantages that it provides. I hope you enjoyed this lecture and I shall see you in the next one. 6. 6 Merge Data Tables: Hey, welcome back. Now let us look at a completely different scenario together. Now we have two datasets in front of us. Alphas dataset is a huge dataset very nerdy, showing me the sales day-by-day. That as on the right-hand side, the great dataset is the dataset, which is my pricing list. For example, if you look at the first dataset over u, it has the unit circle, okay? But every item it has the unit sort, but it does not have the pricing. The pricing for these items is available in this particular dataset. Now let us imagine that I wanted to go ahead and create a sales report for all these items, city, vice, and I will go ahead and moisture together in one single pivot table. Now the complex situation that I have at my hand is my data source is coming from two different datasets. Now, how do I go ahead and create a single pivot table out of this data, basically two ways of doing it. Now the first way of doing it is using the methodology called Power Query. But Power Query is a different system or a different course altogether. The second way of doing it is using a friendly tool, VLookup. What we're gonna do is in this particular lecture, we are going to match these two datasets together using the VLOOKUP function and then create a single pivot table out of it that will give me the sales data for all the items. City Vice. Let's get started. What I want is I want the pricing and the profit margin in this particular dataset. So what I'm gonna do is I will go ahead and insert a few columns over you. What I will do is I will add the pricing and profit margin. Now you need to observe that in order to go ahead and create a VLookup, we need to have a common key. And in these two datasets, the common key is the item code. For example, the ALU fried rule has item code of 11012 in this dataset. At the same time it has the same item code in this dataset as well. I'm going to make use of that. And based upon that, I'm gonna go ahead and create my VLOOKUP. So what I'm going to say is, is equal to VLOOKUP open bracket. And I want to run VLookup on the basis of the item code. I'm going to select the item code, give a comma and then I'm going to select this data age over you. Then I will give a comma. My data is available in the third column, so I'm gonna say three comma 0. I'm going to close this and hit enter. Now with that, my data has appeared, my pricing has appeared. All I need to do is I need to just go ahead and select it, right-click on it. And then I will click on Format Cells. And then I will convert it into currency. I'm going to select dollars as my currency and I'm gonna change the decimals, two zeros that I have the pricing over u. Now, I also want the profit margin. For profit margin, what I'm gonna do is once again, I will run the vlookup open brackets based upon the item code, give a comma, then I will select my data. My data is available in the fourth column because if you look at this particular dataset, profit margin is in the fourth column. I'm going to say the number four give a comma is 0. And then I'm going to close the brackets and hit Enter. Once again, I will select this entire data range. Right-click on it. Click on Format Cells, can see this time I'm gonna keep it to two decimal places and then I will click on, okay. With that, my most data table is ready. I can go ahead and make use of this data table to go ahead and create my pivot table. The only field missing OVO is the sales value. What I'm gonna do is I will go ahead and add one more column over you. I will say this is total sales. Total sales value is nothing but the units multiplied by the price. These are nothing but my total sales value. Now I will go ahead and start constructing my pivot table. So what I'm gonna do is I will select any cell within my pivot table. Click on Insert pivot table. You will observe that this time it has selected the range as table number 14. And if you absorb over you, the name of the table is table 14. Okay? I'm going to create this particular pivot table on a new worksheet. So I'm gonna click on, Okay, and now I will go ahead and add the item name in my rules section. I will go ahead and add the cities in my column. Then I will go ahead and add the sales figures. Are you okay with that? I have gone ahead and created my pivot table. And if you scroll to the bottom, you will also observe that it has gone ahead and also calculated my grand total based upon the cities. If I want, I can go ahead and then change the design and give it a design of my choice over u. That's how easy it is. Just in case if you come across a situation where your data comes from, two different data sources, identify which is the common key between these two data sources. Based upon the common key, one can either use Power Query, which I mentioned is a different lecture series altogether. You can make use of the VLookup function, create a merch table, and then create a pivot table out top dot. I hope you enjoyed this particular lecture and I shall see you in the next one. 7. 7 Pivot Table Layouts: Hey, welcome back. Now we will move to our lecture number six, that is pivot table layout options. You can use the resource file that has been provided to you. Go to the index and click on this particular lecture number six. When you click on it, you will observe that it opens up a new page where it shows me three different layouts of a pivot table. The first layout is called as the compact layout. Then just below it, you will see that there is an outline layout. And then below that you see a tabular layout. These are the three different layouts that one can have within a pivot table. What we will do is, we will go ahead and first create a pivot table. Create these three different layouts, and then we will understand the difference between them. Let's do that step-by-step. What I'm gonna do is I'm going to go ahead and use the same raw data that I used for my pivot table. And first of all, let us construct a default pivot table. What I'm gonna do is I will come back to my index and click on this particular option that is raw data. I will just pick this data up, copy it, and put it on a blank Excel sheet. Now with my data at my hand, let us go ahead and create our pivot table. So I will click on Insert Pivot Table, and then it has already selected my range. I will save create a pivot table on a new worksheet. Then I will click on, Okay, I will go ahead and create my default pivot table that I have created in all my previous lectures. So I will click on Group, I will click on Category, and then I will click on sale value. And then I will select the city's. My pivot table is created. Now, this particular pivot table, or the default view of this particular pivot table is called as the compact view. It is the default view. So even if you don't do any kind of modifications, whenever you go ahead and create a pivot table, by default, it will go ahead and create a pivot table in a compact view. What I'm gonna do is I B, just go ahead and rename this as compact. Now what I will do is I will go ahead and create a copy of this pivot table because it really does not make any sense creating a same pivot table over and over again. Then we will go ahead and create the outline and a tabular view of it. And then we will compare all the tree. What I'm gonna do is I'm just going to click on this particular tab. We'll press Control. And I'm going to just drag it over you. This one I'm going to rename as Outline View. Now how do I go ahead and change the outline view? I will go ahead and click anywhere on my pivot table. Then I will click on the Design tab OU. When I click on the Design tab, I see this particular button that says report layout. I will click on it and I will click on Show outline form. The moment I did that, you will see that the look and feel of my pivot table has changed. What I will do is I will go ahead and create a tabular form also. And then we will look at the difference between all the three. Once again, whatever do is I will just press Control, click with my mouse and just drag wheel. This one I'm going to rename as tabular. And how do I go ahead and change it to tabular view? Simple. I'll click OK. Then I will go to the Design tab, go to my report layout and click on show in tabular format. Okay, now let's go ahead and compare all the three. If I look at my compact view over you, you will observe that OEO I have by group, just below the group, I have my category in the same column itself. However, if I go to my outline view, you will observe that my category has moved to column B. Okay? My column a only has my group and my category is available in column B. It has moved by one column. Similarly, you will absorb that in my tabular format. Also the category has moved to the second column. However, that is a small difference that you will observe in the outline view and the tabular view. And the differences. You will also have that all the totals are at the top. In my outline view. Beta is in my tabular view. You will observe that all the totals are at the bottom. Can I go ahead and make any modifications to it? Let's look at that. If I click on my outline view OU, and if I click on the Design tab, go to my subtotals option. If I click on this option that says Show all subtotals at bottom of the group. Let's see what happens. You will observe that all by subtotals and moved to the bottom of the group OVO. Okay. I have this subtotal for my drinks, then I have the subtotal for my snacks, and then last, I have the grand total. I can once again go ahead and move it back to the top so I can just click on Show All subtotals at top of the group, and it will move back to the top. But if I go ahead and try making such changes in tabular form, let's see what happens. If I click on this particular data. We'll click on Design mu2 subtotals. And let's say I click on this pretty good option that says Show all subtotals at the top. You will absorb that nothing happens because in tabular view you will always get all the subtotals only at the bottom, at the bottom of the group. But as the grand totals will be at the end, you do not have any options of going ahead and making the modification of moving your stop total up or down in the tabular format, it is fixed. Okay. Now if I go to my outline view, click on my design option. Once again, come to the report layout. And if I click on this particular option that says repeat all item labels, let's see what happens. You will observe that under the groups column, the name of the group that is drinks has been repeated in each and every row. If I have one, this kind of a view, I can go ahead and create it. It is an option that is available if I want to remove it once again, I can click on report layout and then I will click on do not repeat item labels and it's gone. Similarly in my tabular view, I can click on report layout and click on this option report all item labels. And you will observe that the name of the group is once again repeated throughout the column. I can go ahead and remove it as well. One more thing I want you to look at is in my compact view, the categories as slightly indented towards the right-hand side. I have my group. Oh, and then if I look at my categories, it is indented slightly towards the right. Can I go ahead and move this indentation a little photo? Yes, of course. What I can do is I can just click anywhere on my pivot table, right-click on it. Click on Pivot Table Options. And OU, I have this particular tab that says layout and format. I have this particular option where it says When in compact form, indent row labels by one character, it is a default setting. Let's say if I go ahead and change it to five characters, let's see what happens. You will observe that the indentation has now moved by five characters. This is another option that is available only in the compact truth. However, this particular option is not available in outline and tabular format because in tabular and outline format, it takes up a completely different column altogether. These are the three different types of layouts that are available in a pivot table. This budget good to know information depending upon your choice and preference, you can select any layout that you want and add it to your dashboard. I hope you enjoyed this lecture and I shall see you in the next one. 8. 8 Subtotals and GrandTotals: Hey, welcome back. So in one of my previous lectures, I have already mentioned that one should never incorporate totals. Subtotals are grand totals in your dataset, especially if you're going to use that dataset for creating a pivot table. The reason for that is pivot table actually takes care of all your subtotals and grand totals. And if you already have set subtotals and grand totals, it may lead to some confusion. There is a possibility that the PivotTable might provide erroneous reports based upon the ground rule that we will not add any totals and subtotals. We will now understand how it works in pivot tables and what are the different options that are available to us when it comes to subtotals and grand totals. So first of all, what I'm gonna do is I'm going to select this particular data over here. And I'm gonna go ahead and create a pivot table out of it. I'm going to create the same pivot table that I've created in all my previous lectures. So I'm gonna go ahead and add the group over here. And then I'm gonna go ahead and add the category a wheel. Now I will go ahead and add this series value. We'll now the moment I did that, you will observe that my subtotals and grand totals have automatically been calculated. If you look at brings over u, this is the subtotal for my brings. And if you look at the group snacks OVO, it is the subtotal for my snacks over you. And if you look at the bottom of that particular dataset, my grand total is also available. Now, if at all, I go ahead and select this pivot table and go to the design optional video, and come down to this subtotals menu with you. I have various options available. The first option says, Do not show subtotals. If I click on that, you will observe that fall my respective groups, the subtotals have vanished. Only my grand total is available. If I go ahead and click on this particular option that says, Show all subtotals at bottom of the group. You will absorb that my subtotals have moved to the bottom of the group. This is my subtotal for drinks. This is my subtotal for snacks. And then I have the grand today. Once again, if I wanted to move it to its default position, I can go ahead and click on Show All subtotals at the top. And my subtotals have appeared. Now one more thing that I want you to observe over u. Let's say if I go ahead and add the item names in this particular list, you will absorb that my data has collapsed. So what I'm gonna do is I will just click on this minus sign over here, and this data will get consolidated. Now if I go ahead and click on this particular option that says subtotals, and then I click on do not show subtotals. You will observe that for my groups the subtotals have vanished. However, it is still showing me the subtotals for my respective categories. But if at all, I go ahead and click on this plus sign OEO and collapse it. You will observe that the subtotals is no mode available. These subtotals will only be visible if at all I click on subtotals and click on this option that says Show all subtotals at the top. That's about subtotals. Now I'm gonna go ahead and remove the item names from my list. Now, I'm just going to focus on grand totals. My grand total is available at the bottom of this particular dataset. Now, let me just go ahead and make a small modification to my pivot table. I'm going to pick this group over here and put it under the column section. You will absorb. Now, I have the grand totals for my rose. At the same time, I have the grand total for my columns as well, because now my group, that is drinks and snacks are falling under two different columns over you. Buy drinks are under the B column, and snacks are under the C column over u. And therefore, this particular grand total is the grand total for this particular column. And this particular grand total is the grand total for this particular column. At the same time, I have the grand total for the respective roles as well. Now once again, I can play with this menu as well. If I click on the Design tab and go to the grand total option over you. And I click on this first option that says off four rows and columns. You will absorb that my grand totals have now vanished and there are no longer available on my pivot table either for my roles are for my columns. If I click on my pivot table, go to the Design tab, and now I click on, on four rows and columns. You will observe that my grand totals had appeared back again. If I go ahead and click on the third option that says on four rows only, my grand totals are now available only for the rows, whereas it is no more available for my columns. Last but not the least. If I go ahead and click on this particular option that is on four columns only, you will observe that my grand total will be only seen for my columns and it's no more available for my rose. That's how you can go ahead and play around with totals, subtotals, and grand totals in your pivot table. And as I mentioned earlier, make sure that your dataset does not have any existing totals are grand totals are already tried to delete them and tried to create those reports in your pivot tables only. That's it for now. I hope you enjoyed this lecture and I shall see you in the next one. 9. 9 Grouping and Ungrouping: Hey, welcome back. Now we come to the eight topic in our lecture series and that is about grouping and ungrouping. Basically, what grouping and ungrouping does is it gives you a complete different look and feel of your data. And it also helps you to look at the data from a complete different perspective altogether. Now, I can talk about it, but I think if we learn it, that would give us a better understanding of what it is all about. Let's post construct our pivot table and then let's understand what is grouping and ungrouping all about. I'm going to use the same old data that I have at my hand. I've just started constructing a pivot table out of it. I'm going to click anywhere on this particular dataset. Click on Insert, and then I will click on PivotTable Every go ahead and create a new pivot table. But this time I'm gonna go ahead and create a different pivot table. Let's go ahead and construct it. I will go ahead and add the groups over you. And then I will add the categories. This time instead of the cities, I'm gonna go ahead and add our Zoe. Then I will go ahead and add this sales value. Now the pivot table that I have in front of me actually shows the hour wise tales of the different categories. This is the sales between eight to nine o'clock. This is between nine to ten, this is between ten to 11 and so on and so forth. And this data is available from eight o'clock in the morning to eight o'clock in the evening. I can assume that might astron actually works in a 12-hour shift. Now let's say I have three different ships in my restaurant. The people who are working in my restaurant come to work in the morning shift, in the afternoon shift, and in the evening shift. But if I look at my data, that is no such bifurcation available in the dataset. Let's look at our pivot table. And let's assume that the staff that works from eight o'clock to 12 o'clock in the noon is considered to be my morning shift. Anybody who works between one o'clock to four o'clock in the evening is my afternoon shift. And then all the people who worked between 1700 hours to eight o'clock in the evening? Odd in my evening shift. So basically what I'm trying to do is I'm trying to group them in three different categories. Morning shift, afternoon shift, an evening shift. But since there is no such provision in my dataset over you, I will go ahead and make use of one of the features of pivot table in order to achieve that. And that is where grouping comes handy. What I'm gonna do is I'm gonna go ahead and select these Porsche shifts over you. Right-click on it. And then I will click on Group. The moment I did that, you will absorb that it has gone ahead and created a group called group one. Okay, I'm gonna go ahead and rename this group to money. If I go ahead and click on this small button, OEO, you will observe that all the shifts between eight to 12 had been grouped together in my morning shift. And I can see the collective subtotals and grand totals separately. Now similarly, let's go ahead and create our often moon-shaped. I'm going to hit, I'm going ahead and selecting these four shapes together. Right-click on it, and then I will click on Group. Okay, So with that, my second group has been created, so I'm going to call this afternoon shift. Then. My last shift is between five o'clock to eight o'clock in the evening. So let's go ahead and collected together. And I would right-click on it and then click on Group. My third group also has been created and I'm going to call it an evening shift. If I go ahead and merge these together, you can observe that now I see the stop distributed in my morning shift, afternoon shift, an evening shift. And I can look at the data separately altogether. Now similar grouping I can also do for my rose as well. Let's say my canned drinks and my soft drinks. I have to group them together and put them under a section called as beverages. I can just right-click on it. And then I will call this particular group as beverages. This particular section over your fruit juices and milkshakes, I will call it healthier drinks. I will select it. Click on it, and then click on Group. And now I will rename them as healthy drinks. Now, once again, I can just merge them together and even collapse the data and look at the zeta separately. But now let us say while creating my groups, if I make a mistake, then what do I do? Let's say, for example, my manager comes to me and says that all the people who are working between eight to 11 out to be considered in the morning shift. But anybody who's working after 12 o'clock has to be pushed to the optimum chipped audit has to be considered under the afternoon shift, then what do I do? It's very simple. All I need to do is select this particular group, right-click on it, and then click on Ungroup. Similarly, I will select my afternoon shift, right-click on it, and then click on Ungroup. Now what I'll do is I will select all the shifts between eight to 11. Right-click on them and I will group them together as morning. All the shifts between 12 to 40 the evening, I will consider it under my afternoon shift. I could even go ahead and group something together. I can even ungroup it and then even change my groups based upon my preferred. That's about grouping and ungrouping. I hope you enjoyed this lecture and I shall see you in the next one. 10. 10 Aggregation: Hey, welcome back. Now what is the purpose of going ahead and creating a pivot table? Of course, we want to go ahead and build some reports out of it. Or maybe analyze the data, get some meaningful insight out of it, and maybe use it for decision-making. The ultimate motive of going ahead and creating any pivot table is actually aggregation. When I say aggregation, what do I mean? It means that I take up a lot of cumbersome data together and convert it into a meaningful output. Such that each of these cells will give me some insight in terms of what this particular data is strength to tell me. It would involve mathematical calculations. So the basic objective of going ahead and creating the pivot table is one, creating a report that is a concise and a compact report. And then play with the mathematical functions within the report to get some meaningful output out of that. That is the purpose of this particular lecture. In this particular lecture, we will understand the various mathematical functions that we can use within a pivot table. Let's start off and let's create our first pivot table. What I have for you is the same data that we have been using for all our previous lectures. The sales data of the restaurant. What I'm going ahead and doing is I'm clicking anywhere on this pretty good pivot table and I click on insert pivot table. Then I click on, Okay. Now let's quickly go ahead and build our pivot table. What I'm going ahead and doing is I'm picking up the group and then I'm picking up the categories. Now what I will do is I will go ahead and pick up the sale value. We'll now you will observe that the moment I did that it has picked up the sum of the sales value. What it has done is it has summed up all the sales value for the respective categories. And it has gone ahead and given me the subtotals and at the bottom it has also given me the grand totals. Now let's go ahead and pick up something is from the Fuge list. What I'm doing is I'm going ahead and picking the units. Are you okay? I'm placing it over, you know, you will absorb that when I picked up the sales value by default it picked up the sum. Whereas in the case of units, when I dragged it to my pivot table, it has picked up at count, not a sum. Now why did that happen? So let's go back and analyze that data once again. If I just scroll down on this particular data where you, you will observe that in my row number 70, I have made a mistake. I'm at this particular section called unit by mistakenly instead of a numerical value, I have added an alphabetical or alphanumeric value. That is what is creating a problem. Because if I add an alphanumeric auto alphabetical value, excel will not be able to go ahead and do any calculations for me, because calculations can be only performed on numerical values. Let's go ahead and change this to the number ten now. And then I will come back to my sheet over here. And now let's try do the phrase this pivot table. When I did finish, nothing really happens. It still remains count. That is because in a pivot table, once you go ahead and drag a field in this particular area, you will not be able to make any modifications dead after. I mean, you will not be able to change the sum to account for the account to an average. For that, what you'll have to do is you'd have to just remove it from your pivot table once. And now if I go ahead and drag it once again, you will observe that by default it has picked up some, because all the values in my units column are now numerical. So it has gone ahead and done a mathematical calculation and given me the sum. Okay, now let's go ahead and add the units one more time under my values area. This time. Instead of some, let's say I want to go ahead and look at the count itself. I don't want to look at the sum. So how do I go ahead and achieve that? It's very simple. All I need to do is come down to this particular drop-down over u. When I click on it, I get this particular option called Value Field Settings. Now what I'm gonna do is instead of sum of units, I'm gonna say I want gout of units. In this particular menu. Instead of sum, I will select account, and then I will click on, Okay. You will observe that the moment I did that, instead of the sum, now I have the count of the units. Now let's say I go ahead and add it one more time. This time, I'm actually looking at the average. So how will I go ahead and do that? Let's see another way of doing it. What I can do is I can select this particular call him, right-click on it. And then I will click on value field settings. And this time instead of sum, what I'm gonna say is average of units. In my dropdown OVO. Instead of sum, I will select average and then click on, okay. With that, I have the average as well. I can also go ahead and format it. So let's say if I go ahead and right-click on it, and then click on Format Cells. I can go ahead and click on number. And I will change this to two decimal places, and I will click on OK. And you'll observe that now for my unit, I have the average. If I go ahead and right-click on it and click on value field settings, you will observe that I have lots of other mathematical functions that are available over you. Like Max, main, standard deviation, etc. Based upon my need and my requirement, I can use any kind of aggregation function and then use it to build my report. The ultimate motive of going ahead and creating this particular lecture was to show you all the different mathematical options that are available with you. How you can go ahead and use them in building the pivot table report of your choice. I hope you enjoyed this particular lecture and I shall see you in the next one. 11. 11 Cumulative Sum: Hey, welcome back. Now we come to the tenth lecture in our lecture series, and that is cumulative. So if I go ahead and click on my index over you're on the 10th topic that is cumulative sum. Let's see what opens up. It opens up a completely new pivot table in front of me. And if I look at this particular pivot table, it looks a little similar to me because this is the same pivot table with the same kind of data that I have been using for all my previous lectures. But the only difference that you will see in this particular pivot table over here is just next to my group and my categories. I see the sum of units. However, along with the sum of units, I also see the cumulative total. Now, what exactly is a cumulative total? Cumulative total is nothing but that running total. What do I mean by that? Let us understand it. Now if I look at this particular data, you will observe that the total sum of my canned drinks is 327. And under the cumulative total Also, it shows 327. But then if I look at my fruit juices, it is 583. If I go ahead and calculate 327 plus 583, you will observe that the sum comes to 910 and that is what it is showing up over you. It's 910. If I go ahead and add another 300 to it, you will also have that my sum is 1210. That is the value that it is showing up over, you know, cumulative total is not required in all the reports. But there are times when we need to go ahead and incorporate it in our pivot tables to make reports something like a Pareto chart. It can come really handy to make certain types of reports in the future. It's a good pinto information. Let's understand how do we go ahead and add a cumulative total to our existing pivot tables? What I'm going to do is I'm going to use the same raw data. First of all, what I'm gonna do is I will go ahead and create a pivot table out of it. Let's quickly create that. Click on Insert PivotTable. Okay. I will add my groups, my categories, and this time I will add the units. Are you? Instead of sum, let's play with it. Count, OU. Let's start with count. Because when we are creating a cumulative total, it really does not matter. What I'm doing is I'm gonna make use of the counter you in my column section, what I'm gonna do is I'm gonna make use of the weeks. This is my count V convict, okay, So this is the count for V1, V2, V3, week four, etc. Now let's go ahead and add the cumulative total to our pivot table. For that, what I'm gonna do is I'm going to play a small trick. I'm going to go ahead and add the unit one more time over you. Let's convert it to count. You will observe that the same data is not replicated twice. The same data that is dead in column B is now in column C. The one which is in D is available in E, and so on and so forth. The first thing that I'm gonna do is I'm gonna go ahead and rename it to cumulative total. Now, let's go ahead and create our running total. So to do that, what I'm gonna do is I will just select this particular cell over you. Right-click on it. And then I will come down to my value field settings. This time, I will click on this particular tab that says Show Value As. And under this particular drop-down, I will scroll down. And I will select this particular option. Are you running total in? And then I will go ahead and click on OK. The moment I did that, you will observe that it is now showing my cumulative total. Can drink. Ovo shows only one unit. Now. One plus two is equal to 33 plus one is equal to 44 plus four is equal to eight. So it's showing me the running total respectively. Now let's look at one more aspect of running total. Currently my data is showing up in a collapsed banner. Let's say I go ahead and consolidate it. You will also have that when it consolidate it, it gives me an error over you because this is the running total. It's not going to replicate the value from this particular set. Since it does not see any other running values over there, it is giving me an error. Similarly, if I go ahead and consolidate it, it will give me an error. Once again, how do I fix this? Let's look at that. Let me just go ahead and expand it. And now what I'm gonna do is I will right-click on my pivot table over here, and then click on this particular option that says Pivot Table Options. And then under the layout and fallback tab, I have this particular option that says for EDR values show I'm gonna put a checkmark on it. And I'm going to leave this as blank. And then I will click on, Okay. Now if I go ahead and close this, you will observe that even when I go ahead and consolidate the entire group, it does not give me an editor. Instead, it just shows a blank value. Okay, so that's about fixing the editor over there. Now let's say if at all, instead of getting my cumulative total vertically, what if I want to have my cumulative total horizontally? Let's say I wanted to running total week ways. How can I do that? Simple? What I'm gonna do is I'm going to once again select this particular cell, right-click on it, click on Value Field Settings. Once again, go to Show Values As I'm going to keep it as running total in. But this time I will scroll down. And I will say, I wonder running total on the basis of weeks. I'm going to go ahead and select Veolia and then I will click on, Okay. Now it's showing me my running total in a horizontal fashion. Let's take that out. One is equal to 11 plus 81 is equal to 8282 plus 133 is equal to 215215 plus 103 is equal to 318. It's showing me this cumulative running total in a horizontal fashion. But then if I look at this particular data, it looks a little confusing to me because whenever you add cumulative total to your PivotTable, it kind of adds noise to your pivot table. What if I only wanted to look at the cumulative total in my pivot table and nothing else. What I can do is I can click on the pivot table. We'll right-click and click on Show Field List. And this time what I'm gonna do is I'm going to go ahead and remove that count of units from my pivot table. And when I did that, now, all that is left in my pivot table are running totals are the cumulative sum. As I mentioned earlier, cumulative sum sometimes comes handy and we need to incorporate it in a pivot table to build certain kinds of specific reports. And as I said, it's a good to know information how you can go ahead and add running totals to your pivot tables. I hope you enjoyed this lecture and I shall see you in the next one. 12. 12 Percentages: Hey, welcome back. Now the concept of percentages is not new to us. We all must have laundered in a high school or in college days. But when it comes to business reports, percentage is play a very critical role. Now percentages not only give us a lot of business insights when we look at certain dashboards or reports. But they also form a foundation of more complex report and thereby help in the process of decision-making. In this particular lecture, our main focus will be on understanding how do we incorporate it percentages in our pivot table. And get more insight from our existing pivot tables that are various ways in which percentages can be used. So let's understand them one-by-one. For ease of purpose, what I've done is I've already gone ahead and created three different types of pivot table. As we go through the lecture, you'll know why I have gone ahead and created them. Let's look at them one-by-one and let's understand percentages more in detail. So what I've done is I have gone ahead and created the same pivot table. And just to let you know that I've gone ahead and made use of this same raw data that I have been using for all my previous lectures. The pivot table is already created. The only difference is I have gone ahead and disabled this snacks from my pivot table. Otherwise my groups is the same, that is drinks. And then I have all the different categories. And this particular pivot table shows the city wise sales for the different categories for me. Now let's incorporate percentages in it. Now. First of all, let's go ahead and find out what is the percentage contribution of each of these category to the entire group within a particular city. I want to understand what is the contribution of this 2857 out of this 15,457, and so on and so forth. So how do I go ahead and do that? Let's approach this step-by-step. What I'm gonna do is in my value section over u, I'm going to go ahead and add the sales value one more time. This particular part, I'm gonna go ahead and call it as percentage of goodwill. Okay? Then what I'm gonna do is I'm going to select this particular salary. You right-click on it. And then I will select this particular option that says Show Value As. And then I'm going to select this particular option. We'll percentage of the column total. The moment I did that, you will observe that now it is showing me the percentages of the column total. Now the column total is nothing but this particular value, this value, this value, and so on and so forth. So 2857 is nothing but 18.48% of this, 15,457. Similarly, this 5,331 is 34.23% of this particular value. If I go ahead and submit all, you will see that it comes down to a 100%. It is giving me the percentage of each and every category to the sum of c of my entire group. This is one way of looking at percentages. Now let's say I want to go ahead and look at the positive digits in a different way. Let's go ahead and remove it. Now what I'm interested is knowing what is the contribution of each of the city, the respective categories. For example, what I want to know is what is the contribution of Bangladesh to canned drinks sales in comparison to the total sales of all the five different cities. Innovate. I'm looking out for percentages row-wise. You got my point. How do I go ahead and do that? Once again, what I would do is I will go ahead and add the sale value one more time. This time what I'm going to do is I will right-click on it. Go to this particular option that says Show Value As this time instead of the column total, I'm going to say show percentages as row total. And we'll just close this. Now you will absorb the 2857 is nothing but 15.35 per cent of my 18,611. Okay. Similarly, 5,331 is nothing but 20% of my 25,908, which is nothing but the total sales of fruit juices for all the five cities. In our first methodology, we found the percentages column wise. And now in the second method. We found the percentages in a row-wise fashion. Now let us look at the third methodology. Now what I'm interested is knowing what is the contribution of each of these particular values over u du my entire grand total, that is 83,506. Okay, to do that, what I'm gonna do is I will call it percentage of grand total. Then what I would do is I will just right-click on this particular Salvia. Then click on this particular option that says Show Value As. And this time I will select the option as percentage of grand total. Now, all these values are compared with the denominator. That is my grand total. That is 83,506. Innovate do 1857 is nothing but 3.42 percentage of 83,506. In other words, 3,212 is nothing but 3.85% of 83,506. Okay, So we saw three methodologies of finding percentages, one column wise, then row-wise, and then as compared to the grand total. Now let's look at another method. Now. I've gone ahead and made use of this same pivot table. The only difference being now I have made my snacks visible. Now, what I'm interested in knowing is what is the percentage of each of these values as compared to the subtotal of each of the groups. In a way, I will do know what is 2857 out of this particular value. One thing that you need to observe OEO is now I don't have only one group, two groups over u. So I have to make sure that all the categories that fall within respective groups big the denominator respectively. For example, all these values need to be divided by this particular value only and not this. What I'm gonna do is once again, I will just right-click and click on Show Field List. I will add the sales value over you. Then what I'm going to do is I will just type away a percentage of bed. And because for canned drinks, fruit juices, milkshakes, soft drinks, the parent is drinks. Whereas for daily bites, depth fusion, bytes, etc. The parent is snacks. Okay? Now what I'm gonna do is I will just right-click on it. Go to show value as this time, I'm going to go ahead and select this option percentage of PEDOT row total. And you will now absorb that. The moment I did that, it has gone ahead and calculated my post MDG. And if I look at the cumulative value of it, it comes down to a 100%. You can look at the bottom of the screen over here, it comes down to a 100%. At the same time, it has also gone ahead and done the calculation for me, telling me that drinks actually contribute to 23.68% of my grand total for Bangalore. Snacks contribute 76.32% to my Bangalore grand total, and so on and so forth. Just to make this a little convenient, what you can also do is you can just select this particular pivot table, go to Design option, but to report layout. And then you can look at this data in a tabular format. It makes it look much better. Now this is the fourth way of going ahead and using percentages. Now if I go ahead and right-click OVO and click on this particular show value as option. It also has other options like percentage of parent column total, percentage of pattern total, which you can go ahead and use as in the new need. The logic remains the same that we saw in the first three examples. Now let's look at the fourth example that we have over u. Now in this particular pivot table, what I have done is I have gone ahead and taken weeks in my row section, this data is nothing but V quizes sales data for my respective cities. Okay? Now, what I'm interested in knowing is what is the percentage increase or decrease in this particular data as compared to my first week. What I'm interested in knowing is if in first week I made a sale of 561, in the second week I made a sale of 17,766. What is the percentage increase or decrease that has taken, please? Okay. I'm looking at the percentage increase or decrease playing the wheel. Now let's go ahead and plot it on our pivot table. Once again, I will go ahead and pick up the sales value and put a duplicate value over here. Then I'm going to type percentage. First week. This time, what I'm gonna do is I will right-click on it. Go to this particular option that says Show Value As. And I will click on this particular option that says percentage off. Then it is asking me what is the base field? So I'm going to say VK and base item. I'm gonna say week one. And then I will click on, Okay. Now you will absorb. It has gone ahead and plotted my percentages. And how do I look at this particular data? Let me explain that to you. Now, phi 61 was the sales for the first week. My denominator will also remain 561. Innovate. My percentage is by 61 divided by 561, that is a 100%. In the next week, my sales increase to 17,766, which means my sales actually saw a jump of 3,166.84%. Okay. Then in the next week it went up to 26,730, which is almost a 4,764% jump has compared to the first week. And the data is actually showing a similar trend for each of these cities as compared to the data for the first tweak. Now let's look at another example of it. Now what I'm interested in knowing is, what is the percentage increase or decrease as compared to the previous week. Okay, let's go ahead and change this first week, two previous week. Now over you, I will go ahead and right-click on it. Go to show value as the once again, I would select percentage of this time instead of the base item being one. What I'm gonna do is I'm going to select this option that says previous. And then every click on, Okay. Now you will absorb that over your, it says 100% because before this there is no data available. Whatever data is available, it will count it as 100%. The next week, sales went up 17,766. In a way we saw at 3,166% jump. In the next week, our total sales were 26,730, which means as compared to the previous week, which is the second week, the jump is only a 150 per cent. Then in the next week, we saw the seeds coming to 20,229, which means as compared to the previous week, that is the third week, the seals are now only 75.68%. Then it is showing a similar kind of a trend for all the different weeks. Okay? So we solve various different ways in which percentages can be used within pivot table in order to get a good insight in terms of the data that will look into, okay? As I mentioned earlier, percentages can really form a foundation of creating more complex and insightful dashboards in the future. Okay, So that's about percentages and pivot table. I hope you enjoyed this lecture and I shall see you in the next one. 13. 13 Top X and Bottom X: Hey, welcome back. One more prime objective of going ahead and creating a pivot chart is to understand our outliers. When I say outliers, what I mean is in any of the data that we have, we wanted to know who are our top performers. And at the same time, we want to also know who are our bottom performers. Now the reason we want to know both of them is because if, for example, in this case, if there are certain items which are selling more than I probably want to go ahead and increase the production of them. I find certain bottom outliers, which means that there are certain items which are not making a lot of sale, then probably I need to go ahead and slow down the production of these items are probably replace them with other items that cell. So that is the purpose of understanding an outlier. Similarly in IT organization environment. Let's say if you're going ahead and creating a pivot table for a team, you always wanted to know what your bottom performance and you also want to know what your top performers so that you can take the best practices from your top performers and help your bottom performers to improve. Now what we are going to learn in this particular lecture series is how do we go ahead and find out the top performing assets and bottom performing assets in our dataset. Basically, this lecture will not involve a lot of calculation technique, but really involve mode of a filtering technique. Let's learn it step-by-step. So I have this pivot table already created from my restaurant data. It's the same data used for all the previous lectures as well. Now what I'm interested in knowing in this particular data over u is in each of the categories, which are my top three selling items. What I'm gonna do is I will just go ahead and click on any of the items over you. Come down to this drop-down OEO, click on value filters and undervalue filters. I have this pretty good option that says top ten. I'm gonna go ahead and click on that. When I click on that by default, I get this option top ten items by sum of sale value. I'm gonna change this number ten to number three. When I do that, you will observe that now pivot table is giving me the top three selling items in each of my category. Now similarly in this particular list, I want to know what are my bottom selling three items. I'm going to select any of the items OEO, click on the drop-down, go to value filters, Click on top ten. And this time instead of top, I'm going to select bottom. I'm going to change the number ten to three. Then I will click on, Okay. Now the moment I did that, you will observe that this particular pivot table is now showing me the bottom selling items in each of the categories. Now the thing to notice over you is you will see that there is some data overlap. For example, in my cam bring section, you will absorb that a molecule shows in my top list as well. At the same time it reflects in my bottom as well, and so does Hawkeye. Now the reason for that being if I click OU and remove all the filters, you will observe that there are only four items in this particular category. That is via looking for top three and bottom tree, does definitely gonna be some overlap. Let's go ahead and clear the filters from yet as well. Now, instead of top three and bottom three, let's say I'm interested in knowing what are my top 5% selling items. What are my bottom 5% selling items? In that case, once again, what I'll do is I'll click on any of the items over you. Click on this drop-down, go to Value Filters. Once again, click on top ten. I'm going to change the top, tend to top five. And instead of items, what I'm going to say is percent. What I'm interested in doing is top 5% items in each of the categories. I go ahead and click on, Okay, and now it is showing me the top 5% selling items in each of my categories over u. Similarly, if I go ahead and click over here and tried to figure out what are my bottom 5%. What I'm going to do is I will click on this particular option. We'll select Bottom, change the number ten to five from items. I changed it to percent, and then I will click on, Okay. Now this particular list shows me my top 5% selling items. That at this particular list shows me my bottom 5% items. Let's go ahead and clear the filters one more time. Now when I click on this drop-down over you and went to this particular option value filters. You must have observed that I get a lot of other options also you like equal, does not equal, greater than, less than, less than or equal to. These are also conditional filters that I can go ahead and use whenever I want to display a certain data based upon a certain criteria. What do we do is we will take one example of that. And then you will probably understand the concept as a whole. Let's say out of this particular data, OU, I'm interested in knowing only those items that make a sale of more than $5 thousand. Okay? So what I'm gonna do is in my value filters, I'm going to select greater than or equal to. What I'm gonna do is I will type 5 thousand and then click on, Okay. Now you will observe that it is only showing me the list of those items under each of the category where the sales is more than $5 thousand. And similarly, what I'm interested in knowing is only those items where the seals is less than 5 thousand. Okay. What I'm going to do is I will just go ahead and click on less than or equal to. This time. I'm gonna say 5 thousand. Okay? And now this particular list only shows me those items where the sales is less than $5 thousand. The whole objective of this particular lecture was to show you these different period during criteria's based upon which you can go ahead and look at the kind of data that you are actually interested in looking at based upon certain conditions and criterias. So that's it for now. I hope you enjoyed this lecture and I shall see you in the next one. 14. 14 Calculated Field: Hey, welcome back. Now, there might be situations in the future when you would go ahead and create a pivot table, then you want to go ahead and perform certain calculations on it. Now, let's say if you want to perform a particular calculation, but that particular calculation is not available in your dataset. So in that case, what would you do? Not pivot table gives us an option wherein we can go ahead and create customized calculated fields that in weekend, go ahead and use mathematical formulas and see the results in our pivot table itself. Now let's look at these two examples that we have on our screen over you. What I've done is I have gone ahead and use the raw data that has been provided in your resource sheet, Oreo. And I have gone ahead and create it to pivot tables out of it. Now my first pivot table is the common pivot table that we have been using for all our previous lectures. I have the groups, I have the categories, and then I have gone ahead and added two values over here. I have added the units, and I have also gone ahead and added the sale value. We'll now let's say I'm interested in knowing the average sale value per unit in each of the categories. But if I look at my dataset, nothing such is available. It's easy for me to go ahead and find the average price, but unit often item because the price is mentioned over you. But if I have to look at a category as a whole, then there is no such provision given in my dataset. Are you? In that case, what I would have to do is I will have to do these calculations manually. As I said, we can go ahead and create customized calculated fields within pivot table that will give us those results that we want. What we will do is we will first go ahead and add a calculated field to our first pivot table. For that, what I'm gonna do is I will select the pivot table, go to the analyze step. I will come to this option that says fried items and Sets. And if I click on the drop-down over here, I have this option called Calculated Field. I'm interested in knowing the average unit. If I have to go ahead and calculate it, the calculation is simple. The formula for that would be my sale value divided by my units. What I'm gonna do is I will just scroll down over you. I will select sale value were you then I will divide it with the unit. So you then I will go ahead and click on OK. The moment I did that, it has gone ahead and perform the calculations for me. What it has done is it has gone ahead and taken this value from the sale value section and then divided it with the units and given me the average. Okay, in case I wanted to go ahead and format this, I can go ahead and do that. I can just go ahead, right-click, click on Format Cells, come to Numbers. And let's say I want to keep it to two decimal places. And I will close this. Now. I have my calculations done. Now let's look at this dataset over you. And let's add a filter over here. I'm gonna click on Data and then I will hit filter. If I click on this drop-down, I will observe that my data is spread across eight different weeks. Let's say I'm interested in knowing what is my average sale per week. I want this in my first pivot table over you. What I'm gonna do is I will once again select my pivot table, go to the Analyze tab, come to the Field Items, click on Calculated Field. And this time I'm gonna say I want average week. The value will be equal to I'm going to select the sale value. We'll then I will divide it by eight because I have eight weeks. And then I will hit OK. The moment I did that, it has gone ahead and perform the calculations for me and it is giving me the average sale per week per category. Once again, I can right-click, click on Format Cells, go to numbers and change it to two decimal places. My calculation is done. Now let's look at our second pivot table over here. The only difference is interface to an under second one is I have gone ahead and added the date or you the moment I added the sale date. Pivot table by default has consolidated the month and it is showing me the data for two months, May and June. Because if I look at my raw data, my data is spread across two different months, May and June only. Okay? So if I'm looking at this particular pivot table, let's say I'm interested in knowing what is my average C bar D in each of the months. Now I understand that May has 31 days in June has 30. But for convenience, what I'm gonna do is I'm going to select 30 days as my denominator. Let's go ahead and add a calculated field value. I'm interested in knowing average sales per day. So I'm selecting this particular pivot table, go to the Analyze tab. Once again, I will select calculated fields. Then I will say average C, D. Okay? And the calculation will be equal to the total sale value divided by 30, okay, because I'm taking 34 convenience. And then I click on OK. The moment I did that, it has done the calculation for me and it is showing me the average sale per day for both the months. Right-click Format Cells, change it to two decimal places and then hit Okay. Similarly over here, right-click Format Cells two decimal places. The objective of this particular lecture was to show you that if at all a particular calculation is not available in your dataset, there are two ways of doing it. One is either you can go ahead and do those calculations in the dataset itself and create a different field altogether. But I would not recommend that because this is more convenient method. Okay, very new. Go ahead and you create a calculated field within the pivot table. Just give the formula and the pivot table will go ahead and give you the required results. Now one more thing that I wanted to tell you very specifically is the calculated field is not just limited to average. You can go ahead and use any mathematical formula we're there. You can go ahead and use addition, subtraction, multiplication, and division, whatever you want. And you can even go ahead and create complicated calculations over there. A good show you the results based upon the input that you produced. That was the whole objective. Okay, So this is all about the calculated fields and PivotTable. I hope you enjoyed this lecture and I shall see you in the next one. 15. 15 Formatting Values: Hey, welcome back. So now we come to the 14th lecture in electricity's and that is about for my team the values. So let's go ahead and click on the index, and it opens up this particular sheet, but a pivot table is already been created. Now, one of the primary requisite before you go ahead and present your pivot table to anybody is it has to look professional. And when I say professional, it means that the data should be presented in a manner which first of all, looks professional. And then looking at the data. It should not confuse the viewer. Then it should also help him in making some kind of a meaningful output out of it, which may further help him in some kind of a decision-making process. So as I mentioned, cleaning the pivot table before you present it to somebody is something that is very important. Let's understand how do we go ahead and format the values within a pivot table before we go ahead and use it for any kind of presentation, are for building any kind of dashboards. Let's go ahead and look at the pivot table that we have at our handle you. What I've done, I have gone ahead and use the same raw data that I have. And it's the same raw data that has been used in the previous lectures as well. I've gone ahead and created this pivot table over you. If you look at the rows, I have the groups, I have the categories, and then I have the sum of the units, some of the sales. And then in my column section, I also have the cities. Now if you look at this particular pivot table, you will observe that there is a lot of noise oil. It does not really look very uniform to me. Because if I look at this particular section over your sum of units, it is showing in single decimal places. If I look at my values, it has three decimal places. Then if I look at my sum of units over you, that is nor dissimilar tall. And then I also see that there are some errors in my report over you. Okay. Which probably I need to fix it before I go ahead and present this report to anyone. Now, let's understand how do we go ahead and fix these errors? First of all, let's go ahead and clean the decimal places. Ou, I have this single decimal places and this is nothing but sum of units. I don't think I really need any decimal places over you because these are whole numbers. So what I can do is I can just select this particular column OEO, right-click on it. And then I will go to this option that says Format Cells under that tab number come down to the category and then once again select number of you in the decimal places. I'm gonna change this to 0 and I will go ahead and click, Okay. The moment I did that, you will observe that the sum of units now everywhere have 0 decimal places. It looks beautiful. Now I need to go ahead and fix some of the sales value. Okay, they didn't. I have three decimal places. What I'm gonna do is I will go ahead and select it. Right-click on it. Go to Number Format. This time average, change it to two decimal places. Then I will click on, Okay. You will observe that every event now my data has only two decimal places. Wherever it is saying sales value, it's only two decimal places. Now you will observe that the third thing that I need to work on fixed is this particular error that I see over u. Now there are two ways of fixing these errors. The one way that I can do that is I can right-click on it. I can go to Pivot Table Options. And the way I have this pretty good option that says pod data values, I can go and put a checkmark on it and leave it as blank and then click on, Okay. When you look at this pivot table now all the errors have vanished. But along with that, my sum of total units and some of sales value as also vanished. I don't want that to happen. Let me just go ahead and bring the editor back again. I'm going to uncheck this. Click on. Okay. Now what I need to do is I need to look at this pivot table very carefully and find out where exactly is this enter originating. The editor is actually in the snack section in Delhi. And if I look at the category, it's under the daily bytes section. The editor or you is under Delhi. And under the daily bytes section, what I'm gonna do is we go back to my dataset Oreo. I will click on this category and I will just take everything and click on daily bites. Click on Okay. Then under city I will just uncheck everything and then click on Delhi. When I do that, I will observe that I have this arrow. If I go ahead and click on it, let's see what the error is. Now what has happened, probably what we do is we wanted to input the value as ten. But while putting the value over there, we put the value as one divided by 01, divided by 0 is definitely going to give me an error because it leads to infinity. So let me just go ahead and fix this error. You, okay? The moment I did not, you will also observe that the NRA, the sales value also vanished. Now let me just come back to my sheet or you, let's see if the error is gone. I will right-click on this data and then click on Refresh. And you will auto. Now that the data is clean. Now, few more tips that I would like to give you in terms of presenting your pivot table is you can just select this entire sheet. You can go to the View tab and you can hide the grid lines. Your pivot table will actually appear on a nice white sheet. It looks kind of neat. Then what you can also do is you can select your pivot table, go to the Design tab, then you can select a design of your choice. There are a lot of ready-made layouts, so you can select any particular layout that suits your needs. I will select this one, just click on it, and it's done. Now if you look at my data, it looks much more neat and much more professional. So now I can go ahead and present this data to anybody who's interested in looking at this particular data. And also I can use this for building future dashboards. I hope you enjoyed this quick lecture on how you go ahead and fall back the values. I'll see you in the next lecture. 16. 16 Understanding AutoUpdate: Hey, welcome back. One of the biggest pain that one has when handling pivot table is that, let's say if you have done some kind of, uh, formatting to your pivot table. But then when you go ahead and refresh the data, your formatting is lost. And that can be really frustrating. What do I mean by that? Let's look at it from an example perspective so that you understand it better. Now let's say I'm looking at this same data that I created in my previous lecture. Looking at this particular data, what I see is my milkshake sales are pretty low. So I'm interested in knowing what is the reason behind that. What I'm doing right now is I'm just going ahead and highlighting, so I don't forget later on that I have to go ahead and revisit this particular data. Okay? Then just after milkshake, I see that even my canned drink sales, I'm dumb. I'm interested in looking at them as well. Later on, I will go ahead and color code them. Once again, let's say I give them a lighter yellow sheet. Now one more thing that I'm interested is I want to go ahead and give all these columns a standard size. What I do is I select this entire worksheet and then I just adjust this pretty good or sheet in such a way that all my columns now out of a standard size. But now let's see what happens if I go ahead and refresh this data. I will right-click on it and then I will click on refresh. You will also that the moment I refreshed it, my column sizes went back to the default settings. However, the color-code did not change. Milkshakes and canned drinks still have their color-coding intact. That's good. Now let's say if I go ahead and add the item names OU, you will upload that canned drinks and milkshakes still have their formatting. The color-coding is not lost, which is good. But now let's go ahead and remove the item name and look at a scenario. Let's say I keep on adding more data to my pivot table. And by next month, the milkshake and the candling sales go up. And at that point of time, if I rephrase this data, then I don't want this color-coding to be there. I don't want to manually go ahead and remove the color-coding. I went pivot tables to go ahead and take care of that. How do I go ahead and fix these two problems? Okay? So first of all, let's handle the column width problem. Once again, I will go ahead and select my worksheet or let's say I go ahead and change the size of my pivot table so that all the columns are of standard size. Now what I want this when I go ahead and refresh the data, I don't want the column sizes to change. What I'm going to do is I will right-click on this particular pivot table, go to Pivot Table Options. And I have this option called auto fit column widths on update. I'm gonna go ahead and uncheck it and then click on. Okay. Now let's see what happens if I replace this particular data. The column bird does not change. You can ensure that your column width remains as it is by going ahead and unchecking that particular option. Now let us look at the second scenario. Wet. And I mentioned that if my milkshake and cantering sales go up, I don't want this color coding to be there. Every time I go ahead and refresh the data, how do I go ahead and take care of that? I will once again right-click on my pivot table, go to Pivot Table option. And I have this second option over here. It says Preserve cell formatting on update. I will just go ahead and uncheck it and then click on, Okay. You will absorb that the color-coding is now lost. Though this was a very short lecture. I wanted you to know this good to know information. Because in that case you can go ahead and represent the data in the format and the layout that you prefer. So that was a quick tip on auto update. I hope you enjoyed this lecture and I shall see you in the next one. 17. 17 Pivot Table Styles and Designs: Hey, welcome back. Now let's go ahead and move to our next topic in the lecture series, and that is using pivot designs. Now, we have already been familiar with going ahead and changing the design of our Pivot Tables in some of the previous lectures, we know a glimpse of what it does and how it works. But in this particular lecture, we will go ahead and take a deeper dive in understanding how do we go ahead and play around with the Pivot Table Styles to go ahead and change the look and feel of our PivotTables. What I'm gonna do is I'll go back to my raw data. I will go ahead and create a pivot table out of it. Let's quickly go ahead and do that. I'll click on Insert, PivotTable, new worksheet. Select the group, the category. I'll put the sales value. And let's say I'm going to go ahead and add the weak soil. Okay? With that, my pivot table is ready. In my previous lectures, I've also shown you how you go ahead and change the look and feel of it using the Design tab. So let's quickly go ahead and change the design of our pivot table over here. Let's select the pivot table, go to the Design tab, and let's go ahead and select any design of our choice over u. Let's say I go ahead and select this particular design of you. Now, just selecting the design is not the only option that we have. We can actually go ahead and play around with this design. How let us look into the other options that we have. Okay, so let's say I go to this particular manual. We'll pivot table style options. And OEO, I have this particular option called row headers. Let's see if I go ahead and uncheck it, what happens? You will observe that the moment I did that, all my row headers have taken the color of the remaining pivot table. They're not reflecting with a different shade altogether. But if I put a checkmark on it, you will observe that the row headers now have a differentiate. Similarly, if I go ahead and uncheck the column headers OU, you will observe that the color change has happened. If I put the column headers back, the column headers have a different shade or together. Now let's go ahead and look at the other two options that we have at our hand, banded rows and Banded Columns. Let's see what happens when I click on banded rows. Now something has happened, but it's not really very visible for you. To understand that. Let's select another styloid. Let's select another style, maybe something like this. Now let's go ahead and click on banded rows. And now you will observe that the moment I did that, my pivot table actually shows binded rules. If I uncheck it and put a checkmark on Banded Columns. Now my columns are banded. Okay, so that's how you can go ahead and change the pivot table style as well. Now let us look at another example. Now let's say you want a particular style, but when you click on this particular drop-down, you are not able to find the style that you're looking out for. In that case, what can you do? Now, pivot table gives us an option wherein we can go ahead and design our own style. And how do we do that? Let's understand that. What I'm gonna do is I will go ahead and select anybody good style which is closest to the type of design that I wanted. Let's say this particular design is very close to the type of design that I want. What I'm going to do is I will right-click on it and then I will click on Duplicate. Then I will call this as my style. When I do that, you will observe that this particular mice style is now appearing in my pivot table style list over you. So I'm gonna select it. Now what I can do is I can start modifying this particular style. So what I'll do is I'll right-click on this and then I will click on modify. Okay? Now let's look at the header row. And let's say I click on format, I click on Fill. My header row has a dark green shade over u. Let's say I wanted to go ahead and change this shade to maybe blue. Go ahead and click on, Okay. Click on Okay one more time. And you will observe that the shade as now changed to blue. Once again, let's go ahead and click on modify. Now let's go to the Grand Totals OVO. Let's say I clicked, when I click on format, I want my grand totals to appear in this particular shade. Then I click on the borders. I also want some borders for my grand total. So I'm going to select this particular border. Then I want this particular portfolio. Let's say I want to make it a little board as well. Then I will click on, Okay. Click on Okay, one more time. Let's change the font color as well. So once again, we'll right-click on it. Go to modify, go to the grand total row, click on format. And then I will go ahead and select the font color as black. And then click on, Okay. And you will absorb that my font and my entire layout has also changed. If I click on this pivot table over you and click on design, you will also observe that whatever changes I make, OU also reflects into my style layout that I have gone ahead and created in my Pivot Table Style menu. This is true for all the other pivot table styles as well. For example, if I go ahead and select this particular style, and now if I go ahead and make any changes over you, you will observe these changes reflect in the Pivot Table Style menu as well. Let's go back to the style that we created. And let's say this is the style that I want. The objective of this particular lecture was to show you that within the Design tab, you have multiple options that you can play around with and give you a pivot table, the look and feel of your choice. I hope you enjoyed this quick lecture and I shall see you in the next one. 18. 18 Conditional Formatting: Hey, welcome back. In this particular lecture, we will learn conditional formatting in a pivot table. Now the basic objective of conditional formatting is to add some business intelligence to our pivot table. Now in one of the previous lectures that we've learned that on pivot tables, we've learned how to go ahead and identify the top outliers on the bottom outliers within our pivot table. But the objective of conditional formatting is to go ahead and give that information to us visually. What I mean by that is just looking at the PivotTable, I should get some kind of an intelligence from it, stating that this particular data is visually trying to tell me something, Something like, okay, this is my top performer acid or this is my bottom performing acid. This is something that I need to take care of. This is something that I need to analyze photo. Okay? How do I go ahead and achieve that? Definitely by using certain colors within my pivot table. Conditional formatting helps us achieve that. So let's get started. Now. I've gone ahead and created a pivot table out of the raw data that I have at my hand. Once again, I'll show you what I have added to the pivot table. It has the groups, it has the categories. And in my column section, I have two weeks. Now, I'm interested in looking at the top ten sales value within this particular pivot table. So how will I go ahead and achieve that? Let's say I select this particular cell in my pivot table. I go to conditional formatting, and then I select this particular option that says top or bottom rules. Then I click on this particular option that says top ten items. Now by default it has gone ahead and selected a color combination that is light red fill with dark red text. Now ideally in any pivot table, if you're trying to show your top performing assets, you would use a green shade. So what I'll do is I'll click on this drop-down over here. And I will select this option that says green fill with dark green text. Okay? Since I'm interested in looking at the top ten assets within my PivotTable. I will go ahead and click on, Okay. Now you will also have a cell, I select it, it has turned green. Along with that, that is a small rigid that has appeared on its right-hand side. If I click on this particular rigid OVO, I get three options. One is the selected cell, which is by default. Then the second option is all cells showing some of sales value values. Let's see what happens when I click on that. Now you will observe that the moment I did that it has gone ahead and picked up the top ten values within my pivot table and given it the required format. But this particular data is slightly misleading. Now the reason being it has also gone ahead and done some formatting for my subtotals and grand totals. And if I look at my subtotals and grand totals, it does not require a rocket science to know that these values will always be slightly more as compared to the individual values within my categories. This data is misleading. How can I go ahead and change that? I'll click on this drop-down over you. And I will select this option. All says showing some of sales value for category and weak. When I did that. Now it is showing me the data perfectly. It is going ahead and showing me the top ten values within my pivot table. Now similarly, let's say if I wanted to look at the bottom values OU, what I can do is I can select any of the cells over you. Click on the Conditional Formatting, come to this option that says top and bottom rules. And this time I will select this particular option, bottom ten items. Then I will keep the default formatting that is light red, fill with dark red text. And I will click on, Okay, I will go to the drop-down over you and I will select this turtle option, all cells showing some of sales value. Now it is showing me the bottom ten values within my pivot table. But now let's say I only want to look at the top three values within my subtotals only. Then how do I go ahead and achieve that? What I can do is I can click on any of the subtotal values over you. Go to conditional formatting. Once again, I will click on top, bottom rules and select top ten items. This time I will change this pen to three because I'm interested in only looking at the top three subtotals. And then I will go ahead and select this second option, yellow filled with documented text. Then click on, Okay. Once again, I will click on the drop-down, and then I will click on this particular total turnover you. Now it is showing me the top three values within my subtotals. Great. Now, if I click on this conditional formatting go, I can see a lot of options over you, like highlight cell rules, then top bottom rules, data bars, color scales, etc. Now conditional formatting is in itself a lecture series. So we will not go much into the detail of it, but we will just try to understand a few things yet and there. And then you can go ahead and explore this feature further for more understanding. Now, if at all, Let's say I go ahead and did this particular conditional formatting, but now I want to go ahead and change it. Can I do that? Yes, of course. What I can do is I can click on this particular conditional formatting. And then I click on this particular button that says Manage Rules. And whatever conditional formatting we have set for our PivotTable OVO. The rules are available over u. For example, the top ten rule that shows the green color, the bottom ten that shows the red and top three that shows the yellow. Let's say I want to go ahead and clear that particular rule. Then how can I go ahead and do that? I'll select that particular rule. Then click on delete rule, click on Apply, and then click on. Okay. With that, this particular rule is removed from my pivot table. One more feature that now we will try to explore is data bars. Let's look at what data bus does for us. Now let's say I'm looking at my grand total over your first snacks only. I'm interested in going ahead and using data bars over you. What I'll do is I will select that particular cell range, go to conditional formatting, go to Data Bars, and I will select the data bar of my choice. Let's say I select this blue one. And the moment I did that, you will observe that data bars have been applied to these respective cells. The length of these data bars is directly proportional to the value that is available in this particular cell over yield. Higher the value the longer the length of the data bar. Now similarly, if I wanted to go ahead and clear this rule, what I can do is I can select this, go to conditional formatting, go to Manage Rules. I will select this rule. Then I will go ahead and delete it. Click on Apply, and then click on, Okay. Now last but not the least, will look at what color scales does for us. Now in the same particular dataset, we go ahead and select Conditional Formatting, go to Color Scales, and let's select this first color scale over u. Now you will observe that based upon the value within the cell, different colors have been applied to the respective sales innovate. A color grid has been applied to the entire set of cells, wherein the highest value will be shown by a green shade. Just next to it. The value will be shown by a lighter green shade. Then the next value will be Amber than yellow than red, and then dot grid and so on and so forth innovate. It picks up the values from the cells and applies the color scales respectively. This was a short brief about how you go ahead and apply conditional formatting to your pivot tables. Because as I mentioned in the beginning of the chapter itself, The objective of conditional formatting is to provide some business intelligence. That visually, for example, the red tells us that it's a bottom outlier. The green tells us it's a top outlier. You can go ahead and use conditional formatting based upon your needs and your requirement within the pivot table. And use it to represent the data in the format that you desire. That's it about conditional formatting as of now. I hope you enjoyed this particular lecture and I shall see you in the next one. 19. 19 Build In Data Filters: Hey, welcome back. In this particular lecture, we will be focusing more on variables. Now the objective of using filters in a pivot table is to slice and dice the data. And now what do I mean by that? Now if you look at this particular pivot table over here, I have added the categories, I've added the item names. Now if we go ahead and look at this particular pivot table, you will observe that it actually has a lot of data. I may not be interested in looking at all the data in one goal. I may be interested in only looking at this certain data at any given point of time. That is where filters gum handy to me. Now, filters are basically of two types. The first one is the type of filters that you see in this two-by-two grid over you. Then there are second type of filters, which are the inbuilt filters within the pivot table. So we'll look at them one-by-one. So let's look at the photos that we have in our two-by-two grid over u. Let's say in this particular pivot table, I go ahead and add the groups in my filter section over you. You will observe that this particular filter has now appeared in my pivot table. Now let's say if I go ahead and click on this drop-down over here, I see two options, drinks and snacks. Okay, So these are basically my two groups. Now let's say I'm interested in only looking at the data for my drinks and I don't want to look at the data for my snacks. Then how do I go ahead and do that? I will go ahead and put a checkmark on this particular option that says select multiple items. And then I will go ahead and uncheck my snacks from you. And then click on. Okay. With that, you will see that now it is only showing me the data for drinks and not showing me any data for snacks. If at all. I want to go ahead and see the data for snacks and not drinks. Then I can go ahead and put a checkmark on snacks and then click on, Okay. Now it's not showing any data for drinks. Only the data for my snacks is overlapping. Now I can go ahead and add another layer to my photos. Okay? Now let's say I go ahead and add the category over u. Now in my category, what I wanted to look at is the data only for my milkshakes and my fruit juices. And I go ahead and click on OK. Oops, it does not show me any output. What could be possible reason for that? The reason for that is if I look at this particular second filter that I have applied, it's produces and milkshakes that I have selected. But milkshakes and produces fall under my drinks category. But in this particular top section over here, in this particular filter, I have only selected snacks. I haven't selected the drinks at all. And that's why I'm getting this particular error. So what I can do is I can just click over here and then I will select all. Then I will go ahead and click on, Okay. Now it is going ahead and showing me the data only for my milkshakes and fruit juices over you. That's how I can also go ahead and add another layer of filters. Do my filtering technique. Now let's go ahead and set everything back to default. What I'm gonna do is I will select a window and then select all. Then I will go ahead and remove these filters. Then I will just add my category over you. Okay, so my PivotTable is back to the default settings. Now let's say I go ahead and add few more filters. Let's say I go ahead and add the group over here. Then I add the city of Rio. And then let's say I also add the data for vk. Now let's say I'm interested in only looking at the data for drinks for Bangalore city for the first four weeks. Okay? Even that is possible. What I've just done is I have added three layers of period during and now based upon the criteria is that I have provided, it is showing me the respective data. Now, I want you to observe a few things or were you? I have gone ahead and added these filters one above the other. Now I can actually play around with them a little bit. So currently my filters are in a vertical fashion. What I can also do is I can apply these filters in a horizontal way. What I can do is I can right-click over here, click on Pivot Table Options. The way I see this pretty good option that says display fields in the report filter area. If I click on this drop-down window, I see a second option of you that says OA, then down. Let's see what happens when I click on that. And then I click on, Okay. You will observe that now all my filters out in a horizontal fashion. Let's play around with a little bit more. So if I right-click on it, go to Pivot Table Options. Once again, I bring it back to the default settings. But this time in my second option over your button it says report filter fields per column. I change this value from 0 to two, and I click on, Okay. Now what has happened is it has gone ahead and place the filters in such a way that two filters will be there in each of the column. The moment another filter is added, it moves up to another column altogether. And that is because I have set the threshold to only two filters per column. Let's go ahead and set it back to the default settings again. Now, let's go ahead and set our data back to the default settings. So I can go ahead and remove all these filters. And my data is back to the default pivot table that I had in the beginning of the lecture. So that was about the filters that are available in the two-by-two grid. Now, Excel also provides an option within three days that are available within the pivot table itself. And or your, you have two types of parameters, labeled filters and value filters. Now let's look at what the value filters do for us. Okay? So what I'm gonna do is I will just change my data a little bit. So what I'm gonna do is I will just remove the column section over you. I will add the weeks over you. Then I will add the sale value. And if I go ahead and look at the photos OUT, probably I'll have to reset the filters. I want the data for all the four weeks. And one more change that I would like to make is let's remove the sale value from you and just add the units are you? And now let's start playing with our inbuilt filters that we have over u. The first filter that we will look into is the value filters. And as the name suggests, value filters deals with numerical values. If you look at the menu for you, you have lots of options like equity does not equal, greater than, less than, which signifies that we will be only playing with numerical values using this particular photo. What I'm interested is within my item names, I'm only interested in looking at those items where my grand total or the total sum of units is greater than 3 thousand. Okay, So this value has to be greater than 3 thousand. For that, what I'm gonna do is I'm going to select any of the items over here. Let's select the first one. Then I will click on the drop-down over here, and I will click on value filters. And I will click on greater than or equal to. Now, it shows me very clearly that this filter is being applied on the item names and not on the category. Just pay attention to this. And the criteria is sum of units is greater than or equal to 3 thousand. Let us make it 2500 for now, okay? And I will click on Okay, so I have set the criteria wherein I only want those item names where the grand total or the sum of the units is greater than 2500. I click on Okay. And you will observe that now it will only list those items where the grand total or the sum of units is greater than 2500. If I click over your, let's say I click on the category or you, then I click on the filters. And I wanted to go ahead and clear the filters. But it is not giving me any option to go ahead and clear the filters. Now, why is that? Because the filters that I have applied is applied on the item name and not on the category. When I wanted to work on clear the filters, I will have to select item name, then go to my filters and then clear the filters from you. My afraid is now cleared. Similarly, let's say if I want to apply a filter on my category, then I will have to select the category and then apply the filter. Let's say I'm interested in looking at only those categories where the sum of units is greater than 10 thousand. Okay, let's make it 15 thousand. Let's click on the drop-down over here. We'll do value filters and then click on greater than or equal to. And you will upload that this time the filter is applied on my category and not on my item name. And I will give the value where you would as 15 thousand. Then click on, Okay. Now you will observe that it is only listing those categories where the grand total or sum of units is greater than 15 thousand. If I select an item and then try to clear the filters, It's not giving me that option because the filters are now applied on the category size will have to select the category. In order to go ahead and clear my filters. I can go ahead and clear the filters from mu. That's about value filters. Now let's take a look at the other types of filters which are labeled filters. As the name suggests, labeled filters are used for characters, not for numerical values. So basically, I have a lot of options over you're like equal, begin with, ends with contains, etc. Now let's play around with labeled filters a little bit. If I go ahead and look at my menu over you, I see a lot of items with the name chicken in it. For example, a chicken popcorn, chicken fried chicken chemo raw chicken puff. Let's say I'm only interested in looking at those items where it contains the word ticket in it. So my label filters will come handy to me. I will first select the item name and then I will apply the filters. So I will click on Label Filters now. And I will select contains. And I will give the word Chicken over you. And I will click on, Okay. You will observe that now it lists all the items where the word chicken has been used. If I tried to select the category and clear the filters, it will not give me that option. Since the filter has been applied to the item name, I'll have to select an item name and then clear the filters. Let's try one more thing. Let's look at only those items where the word juice has been used. I will click on the drop-down over here, what we labeled filters. And then I will click on Contains. I will type the word juice. Click on Okay. And it is listing all the items where the word Jews has been used. As I mentioned in the beginning of this lecture, the objective of this particular lecture was to give you an idea in terms of how you go ahead and slice and dice the data to get the required output and only look at the data that you are interested in looking at. Because your pivot table contains a lot of data and you may be interested in only looking at a certain chunk of it. And that is where filters will come handy. We learned three different types of filters. One, which is available in the two-by-two grid over here. Then we learned about the labeled filters and value failures. That's it about filters as of now, I hope you enjoyed this lecture and I shall see you in the next one. 20. 20 Slicers: Hey, welcome back. In one of our previous lectures, we got introduced to the concept of peer TO we saw three different types of filters. The internal filter, the value of filter, and the label filters. Now in this particular lecture, we will load something mode about Fritos. And we will learn probably one of the most coolest technique of going ahead and printing your pivot table. And I'm very sure that once you learn this particular method, this will be the default method that you would use for filtering any of your pivot tables in future. Let's straight jump into it. Now, I have this particular pivot table already created. Let's quickly look at the fields over u. I'll right-click, click on Show Field List. And you will observe that I have the categories, I have the item names, I have the same value in my columns, I have the cities, and I have used one filter over your groups. By Have I used it? We will understand that as we move further in this particular lecture. Now let's say I want to go ahead and look at some very specific data within my pivot table. And we'll, as I said, we are going to learn a completely new good technique in terms of going ahead and filtering this data. For that, what I'm gonna do is I will click anywhere on my pivot table, come to this tab called Analyze. Once you click on that, you have this particular option called inserts slice hill. When you click on Insert Slicer, you will observe that a menu pops up and it has the names of all the fields that are also visible in my field list over u. Now let's go ahead and put a checkmark on some of them. Okay, so let's say I select the group, I select the city, then I select a week. Then I go ahead and click on, Okay. Now the moment I did that you will observe that something really popped up on my screen. I have these boxes that show the individual fields. Ou, I have this group OVO. Then I have the city. Let's place them next to each other. And then I have the vk. These particular boxes will now walk as my filters. Now let's look at this. Let's say I only want to look at the data for drinks. If I go ahead and click on drinks, you will observe that now my pivot table is only showing me the data for drinks. And the reason I kept this particular photo for you is if you observe it. When I clicked on drinks automatically, the group Twitter also changed two drinks. If I click on snacks, you will observe that it is showing me the data for snacks. Similarly, I can apply multiple levels of filters as well. Let us say under snacks section I only want to look at the data for Bangalore. If I click on Bangladesh, it's not only showing me the data for Bangalore, let's say under snacks and under the city Bangalore, I only want to look at the data for the second week. I do that. I'm able to look at the data specifically for Bangalore second week and that do only for snacks. Now let's go ahead and clear the filters. And in order to clear the filters, what one can do is one can just go ahead and click on this cross section over u, which is along with a filter sign. When you do that, the filters will just reset themselves. Now, let's say I want to go ahead and select multiple filters. Then what? Let's say I want to look at the data for Bangalore, Chennai, and delete together. Now that is also an option provided by Excel 2016 and beyond. Now, you see this particular tick marks over you, a box with a lot of tick marks. When you click on that, you actually activate mighty selection criteria. Now with that box checked, I can go ahead and uncheck Mumbai and Pune because I'm only interested in looking at the data for Bangalore, Chennai, and Delhi. Let's, I also want to add the data for Mumbai to it. I can go ahead and do that. Similarly for my weeks. Let's say I wanted to look at the data for the first four weeks, then I can just uncheck the next four weeks. And it will only show me the data for the first four weeks. Once again, to go ahead and clear the filters, we can just go ahead and click away. And all the filters will be clear. Now let's look at some more features of the filters. Now let's say I want to go ahead and reset my filters and place them vulnerable the other. Now that again is an option. What I can do is I can just change the size of my photos, please them anyway. But I want them to be. I can resize them and fit them as n where I want them to be. Now that's one way of doing it. Let's say I want to go ahead and change the height and width of my filters, then what I can do is I can select a particular filter that I want to go ahead and modify. Click on the Options tab OVO. Then I can just reduce and increase the height and size of my filters. Similarly, just like the height, I can also go ahead and increase and decrease the verdict of my filters over u. Now, let's look at this particular fear TO YOU. It has eight weeks. Now, I want to go ahead and Lisa is this size of this particular filter, and I want to keep it of this particular size. But then I will have to just scroll up and down to look at the weeks over u. Now there is a fixed product. Now what I can do is I can select my slicer over you. And once again, under the Options tab, I have this particular section called as the column. What I can do is I will just go ahead and increase the column due to. Now it is going ahead and putting two columns within the same height and width of my slices. I can also go ahead and increase the numbers photo as well. Okay, so that's one more option available for me. Okay, Now let's look at some more features of the slices. Let's say I select my slices over, you go to the Options tab. I see this particular section that's called the slices styles. Now if I go ahead and click on it, it gives me various options. Basically it is giving me different styles in which I can go ahead and modify my slices. Let's say if I go ahead and select this particular style, it will go ahead and change the color of my slices. Similarly, let's say for my cities, I go ahead and select this shape. And then for my groups, I go ahead and select this one. Okay, So that is again a choice that is available to us. We can go ahead and select the style of our slices based upon our needs. Now if I told her particular style that you want is not available, then you can go ahead and create a new slices style as well, just like what we did for Pivot Table Styles. Now one more thing that I want you to know about slices is that as in when you apply the slices, sometimes the size of your pivot tables become bigger or smaller depending upon the filters that you have applied. And because of that, the size of your slices also keep on changing. And they tried to fit themselves depending upon the size of your pivot table. And in case you don't want that to happen, what you can do is you can select your slices. Right-click on it, go to Size and Properties. And under the Properties section you have this particular option that says don't move or size with cells. And when you click on that, you will observe that video applied the filters. Your slices don't move from the place. The size, and the placement of your slicers is then fixed. Let's go ahead and clear all the filters once again. Now since we already have the group filter wheel, what we can do is we can go to our pivot table over here and remove the filters from you. One mode advantage of having slices is that it reduces the number of filters that you apply it within your pivot table. The best benefit of having slices is that they'll visually available, okay, so one can actually see them on the screen along with your pivot table. So anybody who's even new to pivot table are probably does not understand how a pivot table works. Can actually go ahead and click on these options. Because they are visually available and play around with the pivot table and look at the data that he is interested in looking at. That's it about slices. And now since you know this particular feature, I'm pretty sure that this is gonna be your default view of going ahead and filtering your pivot tables in future. I hope you enjoyed this lecture and I shall see you in the next one. 21. 21 Date Filters and Timelines: Hey, welcome back. In a previous two lectures, we learned a lot about filtering. We learned about the internal filters, and then we also learned about the slices. In this particular lecture, our focus will be on understanding how do we go ahead and filter our data based upon certain dates. Let's go ahead and make certain modifications within our pivot table over here. What I'm gonna do is I'm going to select this pivot table. I'm gonna remove the categories. I'm going to remove the item names. And over what I'm gonna do is I'm going to go ahead and select the data. We'll go ahead and select the dates you will observe that Excel actually goes ahead and consolidates everything together month wise. I have this data for two months, May and June. Therefore, it has gone ahead and created two different groups within my pivot table for me and June separately. Now I don't want this data in a month wise format. I wonder data in a date wise format only. What I'm gonna do is I will just pick up this month and I will remove it from you. Now, my data is available in a DOIs format. What I'm gonna do is I want to remove the cities from you. And I will go ahead and put the weeks in my column label. Let me just activate all the weeks. Okay. I will remove the sum of sale value and I will put the sum of units over u. Now my data is actually showing date wise sum of units. And I have the weeks within my columns. Okay? Now let us say out of this particular data, I'm only interested in looking at the data from tenth of me to 25th of me. How will I go ahead and do that? If I go ahead and click on the filters over here, you will observe that I have only two filters, no date filter and value filter. Because now since I don't have any characters over there, my label filters have vanished. Now if I go ahead and look at the menu that pops up on the right-hand side, you will observe that there are a lot of sensitive criteria is over you, like next week, this week, last week, next month. Now this isn't relation to our system date. And a system date is the date which is showing up on the right-hand side, bottom of your screen, That's your system date. And any value over you that pivot table will now calculate will be in relation to the system date. We are not interested in looking at this particular data over u. What we are interested in looking at is this particular section. What we're gonna do is we're going to use this particular filter between. Then what I'm gonna do is I will go ahead and click on this menu or you. And I will go ahead and select the month of me. I wonder data from tenth of me. Instead of in 2022, I will make it 2013 because my data is available for May 2013 and June 2013. Okay. Once again, I will go to the month of May. I will select the data's 25th, and then I will go ahead and change the date to 2013 and click on Okay. Now the moment I did that you will observe that the filters have been applied. And now it is only showing me the data from 10th of May, 25th of me. Now, probably 10th of May and 25th of May falls within my second, third, fourth week. And therefore, all the remaining weeks have completely vanished from my pivot table. Let's go ahead and clear the filters. And to clear the filters, once again, what I can do is I can go ahead and click on this particular option that says Clear Filter from CLD and my filters will be clear. Now let's look at another alternative method which is similar to the slices that we used in our previous lecture. Now let's say I wanted to apply slices for my dates. How do I go ahead and do that? Now there is a way of doing it. Let's say I select any particular date within my pivot table over here. Then I go to the Analyze tab. When I click on Analyze tab over here, I have this pretty good option that says insert timeline. Because when it comes to date, we will not apply a slicer. We will use timelines. When I click on timeline, you will observe that it does not show me any other field within my field list, but only the sale date. Because sale date is the only field which is in a date format. I will go ahead and put a checkmark on it and then click on, Okay. Now the moment I did that you will observe that it has gone ahead and created a kind of a slicer in front of me, but this is actually a date slicer and actually it is called a timeline. If I scroll over here, you will observe that it is showing me the data from January to December. However, if I go ahead and click on any of the other months earlier, it is not going to show me any data out here because my data is only available for the month of May and June. If I click on the month of me, it will show me the data for the month of me. Similarly, if I click for the month of June, it will only show me the data for the month of June. Now, just like in slices, we have multi selection criteria. Or we can go ahead and drag this and select multiple months. And it will show me the data for me and June collectively. Now, if I go ahead and look at this particular drop-down over you, it gives me a lot of options like yours, Cortes, month, date, etc. Not all these features will be used only if your data is spanning across a long duration, maybe like 2345 years at a stretch. But since our data is only limited to two months, we've been only go ahead and use this particular filter criteria we'll days. When I go ahead and select that you will observe that now my dates are spanning from first of me to the 30th of June because my data is only available for these two months. Now what I can do is I can use the date twice filtering over you and look at the data for a particular duration. Let's say I'm only interested in looking at the data for 25th of June. I can look at this particular data. Let's say I'm interested in looking at data from 25th of June, 30th of June. I can select multiple criterias and look at this data collectively. In case I wanted to go ahead and clear the filters, I can go ahead and click on this Clear Filter button over here. And all my filters will be cleared. The objective of this particular lecture was to let you know that there is another filtering criteria which is broadly data-based. And you can actually go ahead and use the inbuilt date filtering criteria within your pivot table. At the same time, you can add a wonderful feature like timeline within your pivot table to go ahead and analyze your data. That's it about Date filters for now. I hope you enjoyed this lecture and I shall see you in the next one. 22. 22 Slicer Connections: Hey, welcome back. In a previous few lectures, we understood different filtering techniques. One of the techniques that we used was slices. Now in this particular lecture, I want you to show one more feature about slicers, which I wanted to cover separately. Now let's say I have this particular pivot table in front of me. And I have selected the groups, the categories, sum of values. And then in the column section I have the cities. Now let's say I go ahead and create another copy of this particular attribute table somewhere over you. Let me just go ahead and make some modifications in this particular pivot table. So what I'm gonna do is I will select this particular pivot table. I will remove the groups from you. I will add the item names. Instead of cities. I will just go ahead and add the week. So we'll, and now let me just go ahead and add a few slices to my data. But in order to do that, what I will do is I will create some space for my slices, so I will add a few rows over u. Now I will select this particular pivot table over. You. Go to my Analyze tab and I will click on Insert Slicer. Okay? I will select a slicer for, let's say category City. And then I will select a slicer for my VQ. Now I will go ahead and place these slices somewhere. Will you? Let me just go ahead and give them a nice design. What I'll do is I will select them, press control and select all of them. Come down to styles over here, and then let's select this particular style of view. Now let's say I start filtering my data using these slices. So let's say I select the CD4 bind load. When I do that, it's showing me the data for Bangalore. When I select first week, it's showing me the data only for first week. I can go ahead and clear the filters. And if I go ahead and select, anybody could have category a wheel. It's only showing me the data for that particular category. However, you will observe that whatever changes I'm implementing within my slices, those changes are only reflecting within this pretty good at Pivot Table. This particular pivot table, it is completely unchanged. And that is because right now we haven't built a relationship between these slices, this particular pivot table. What we have to do is we will have to go ahead and build the relationship between the slices and the PivotTables. Before we get into building relationship, I want you to understand something very important. Let's go ahead and select this particular PivotTable and I click on my Analyze tab. I want you to look at this particular section. A way of it. It says Pivot name. The name of my pivot table is pivot table to. But if I select this pretty good at Pivot Table a, we, the name of my PivotTable is pivot table three, innovate. These two pivot tables are two unique pivot tables. And benefit if you want to go ahead and build a relationship between two pivot tables and use single set of slices to slice and dice the data. Then we need to give them unique names. Not as of now the names are unique, but let's give them a good name so that we can remember that particular name. But this pretty good pivot table, I will go ahead and select any one of the cells over here. And I will name this pivot table as sit device pivot TV. And in this particular PivotTable, I will select anybody who will sell and I will call this B plays. Now if I go ahead and select this particular pivot table, it shows the name of the pivot table. Let's sit device. And if I select this particular pivot table, it shows the name IS week wise. Now, we will get into the relationship building activity. Let's say I select this particular slicer over you. I right-click on it, and then I click on this particular option that says report connection. In a day, what we are doing is we are going to build a connection between the slicer and the pivot table. When I look at this report connection window where you, you will observe that this particular pivot table is only connected with my city Vice pivot table, and it is not connected with my VQ wise pivot table. Now in order to go ahead and build a relationship or a connection, what I'm gonna do is I will go ahead and put a checkmark wheel and then click on, Okay. Similarly, I will do the same activity for other slices as well. I will go to the port connection and I will connect them with my weak base pivot table. Now if I go ahead and make any changes, Let's see what happens. If I go ahead and select canned drinks. You will observe that in both the pivot tables, it's only showing me the data for canned drinks. If I click on daily bites, it's only showing the data for daily bytes. If I go ahead and slice and dice this data-based upon cities, you will see the changes reflect in both the pivot tables. And similarly for my weeks, if I go ahead and select any particular week, the data will reflect those changes automatically. I just wanted to cover this particular section. Are you, I wanted to show you how you can go ahead and build a connection between two or more pivot tables using single set of slices. And then use these slices for looking at a particular set of data between all these pivot tables. I hope you enjoyed this quick lecture. I shall see you in the next one. 23. 23 Basic Sort: Hey, welcome back. So I'll focus in this particular lecture will be on understanding the sorting technique in a pivot table. Now if I look at this particular pivot table over here and look at my group, I have two groups over your drinks and snacks. Because I've used the same pivot table that I've been using for my previous lectures. And as you can see that I've selected groups and then the categories within my row section over you. So let's say if I'm looking at this group over you, my groups are actually sorted in an alphabetical order. Drinks comes first and then snacks in an alphabetical order. Now let's say for some reason I wanted to go ahead and reverse this particular sequence. I want it to be arranged in reverse alphabetical order. Then in that case, what can I do? Simple, what I'll do is I will select one of the groups over you. Right-click on it. Go to sort. And right now it is sorted from a to Z. I will go ahead and change it to sort from x2000. The moment I did that, you will see that my groups are sorted. But even though my groups are sorted, that is absolutely no change within my categories. And why is that? The reason being any kind of sorting that you apply or any kind of filtering that you apply is always applied at the field level. It was applied at the group level. So it will not reflect at the category level. If I want to go ahead and sort my categories, then I will have to select one of the entries within my categories. Right-click on it. And then I will click on Sort. And then I will click from Z to a. And you will see that the order is now reversed. But didn't buy snacks as well as it didn't my brings group as well. Okay, so the categories are reversed. Now let's say for some reason I want this spicy delight to be reflecting as the last entry we didn't my snacks section. But when I do that, I don't want to disturb the other settings over you. I want this entire sequence to be the same order, but I only want my spicy delight to move a little down and be the last entry within my snacks section. So how can I do that? Simply, what I'm gonna do is I will select spicy delight. When I come to the edge of that particular cell, you will see that the down arrow appears on my screen. I'm just gonna go ahead and click on the spicy delight optional video. And then I will just gradually drag it to the bottom. Now you will upload that spicy delight is the last entry within this particular categories. Similarly, let's say if I want to move the milkshake from second position to the third position. Once again, I will come to the edge of that particular cell and a download or will appear, I mean, go ahead and click. Okay, and then I will just gradually drag it over you. My milkshake has now moved to the third position. Let's say if I wanted to go ahead and bring it back to the default settings, once again, select any of the categories over you, right-click on it, go to sort. And then I will click on this particular option that says more sort options. Now right now manual has been selected because we manually moved spicy delight from the top position to the bottom position. I wanted to go back to the ortho sort settings. So I will go ahead and click on ascending to descending. And then every click on, Okay. And you will observe that it does come back to its default sorted settings. Now, sorting cannot only be applied to those, but it can also be applied to columns. Let's say if I want to go ahead and sort my cities, OEO, which are right now arranged in an alphabetical order. I wanted to reverse this order. What I can do is I can right-click on it, click on Sort, and then I will click on Sort from x2000. And you will observe that the order is now reversed. Now let's say I go ahead and make a small change over you. I removed the cities and I added week. So were you right now my weeks I didn't n ascending order. And I wanted to go ahead and change the sequence over here. So I will right-click on it, click on Sort, and then click from Sort Largest to smallest. And when I did that, you will see that the sorting order has been reversed. That's about basic sorting within pivot table. In the next few lectures, we will also get familiar with some more sorting techniques. I hope you enjoyed this lecture and I shall see you in the next one. 24. 24 Custom Sort: Hey, welcome back. So in one of the previous lectures, we understood basic sorting in pivot tables. Now we will look into something called as the custom sorting. Now what do I mean by custom sorting? Let's look at it in detail. Let's say my manager comes to me and says that going forward, whenever we present any data which is involving drinks and snacks, I want the data should be reflecting this particular sequence, okay? What I want is I want us snacks to be first and then followed by drinks. And then within my drink section, I want the canned drinks, and then I want milkshakes, then I want the fruit juices and then solve drinks. Okay? Similarly, when showing the categories within my snacks, I wanted to see quick bites post, then followed by regular bytes, spicy delights and so on and so forth. He has given me a sequence, and he wants the data to be shown in this sequence only at any given point of time. Whenever I go ahead and present the report. How can I go ahead and achieve that? For that, we will have to go ahead and do a small tweak or are you okay? Now what we will do is we will go to this particular option or your file. Then we will come down to Options. And then we will click on this pretty good at tab called Advanced. Then we will scroll down at the bottom of this particular tab over u. Then we will click on this pretty good option that says edit custom list. When I go ahead and click that, you will observe that it already has certain list mentioned within this particular box called the customer list. Now these are basically Sunday, Monday, Tuesday, Wednesday, Jan, fib, March, etc. Because of this particular customer list, whenever I go ahead and select anybody good at Excel automatically does the sorting for me in a specific order. What I'm gonna do is I'm going to add these lists over to my Custom List section over you. What I'm gonna do is I would click on this up and over. You have been selected the first list over here, and then hit Enter. Then I will click on import. And with that, you will observe that my custom list as been selected and being added to the existing list over you. Let me just select another list. I'm gonna select this one. And then I will click on import. So even that is added. Now, once again, I will go ahead and click on the up arrow, select my third list, and then click on Import, and then click on, Okay. Okay, and then I will click on Okay one more time. But you will also have that when I did that, nothing really changed within my pivot table. Let's go ahead and refresh our pivot table and let's see what happens. I'm going to right-click on my pivot table and then I will click on Refresh. The moment I did that you will observe that now the data has been sorted in the required format. Snacks comes first followed by drinks. And then if you look at the individual categories, it has sorted the data exactly in the manner mentioned in my Custom List over u. Now let us say even though I have added a custom list, but I want to go ahead and sort the data in the default manner. And if I go ahead and try to do that, you will see that nothing really happens. How can I do that? What I can do is I can play a small trick over you. I can just right-click anywhere on my pivot table and come down to Pivot Table Options. Then I will click on this particular tab that says Totals and Filters. And I have this particular option called sorting. And there's a checkmark on this particular option that says Use Custom List when sorting. What I'm gonna do is I will just go ahead and uncheck this and then click on OK. The moment I did that, you will observe that my settings have come back to default. If I go ahead and put a checkmark over there once again, you will see that my custom list is now applied once again to my sorted list over u. Ok. Now let's say I want to go ahead and delete this particular setting. Then once again, I will have to go to File, click on options, then click on advanced, come down to custom list. Then I can select any of the custom list from you. And then I can delete it. Once again, I can select it and delete it. Select this one and delete this one. Then click on, Okay. Click on Okay one more time. And now I will go ahead and finish my data. And it comes back to the default settings. That's how you can go ahead and apply custom sorting, do your pivot tables. Now before we go ahead and end this lecture, I want to give you a heads up on two factors. One, whenever we go ahead and create a custom list, we will have to manually create a custom list and add it to our custom lists section over there. Second, Let's say if I go ahead and create a custom list based upon the Custom List, I go ahead and create a report and then I mailed this report to someone else. Now if I told the same custom list is not available on the other system, then the data will only show with default sort because the Custom List is not added to that particular computer. In case you want the data to be reflected in the same sorted manner on different systems, then the same custom list has to be added to the different systems individually. These are the two drawbacks whenever we go ahead and use custom list. Other than that, it is one of the very handy tool that you can use for your reports. I hope you enjoyed this lecture and I shall see you in the next one. 25. 25 Value Sort: Hey, welcome back. In a previous two lectures, we saw basic sorting and you also saw custom sorting. Now in this particular lecture, we will focus more on value sorting. Now you have already understood that we can go ahead and sort the data based upon values. So what we did in the column section was we right-click on the data. We sorted the data from largest to smallest. The data got sorted. Now let me just go ahead and bring this data back to the default settings. Now let's say I want to go ahead and sort this data based upon the grand totals within the individual category. What I will do is I will right-click over you, then click on Sort. Then I will click on smallest to largest. And the moment I did that, you will observe that my grand totals are now sorted from the smallest value at the top and the largest value at the bottom. Now let's say, I'm interested in knowing over you that amongst the soft drinks, which was the week when I made the highest see alveolar. Go ahead and do that. What I'm gonna do is I will select any of the values within the soft drink category. I will right-click on it. And then I will click on Sort. Then select this option called more sort options. This time what I'm gonna do is I will select this value from largest to smallest. And then I will select sort direction from left to right. The moment I did, daddy will see that my data is now sorted in a random fashion. Week seven comes first, then week three, and week four then decade. And that is because I've sorted the data on the basis of the soft drink category I wanted to know which was the week when I made the highest sale. And if you look at this particular data is arranged in the descending fashion. Now let's go ahead and do a small trick of you. Now my milkshake is at the top right now, because I've arranged my grand totals from smallest to largest. And right now my milkshake seeds are the lowest, and that's why this data is at the top. Now let's go ahead and look at our data sheet over you. In my data sheet, what I will do is for milkshakes, for this particular value, which is only 22. Let's go ahead and add a very high value. We'll, I will make this 2222 thousand. Then let's see what happens over you. I will come back to my data view. Then I will refresh this data. The moment I did that you will absorb that my milkshake has now moved down to the bottom spot. It has gone ahead and sorted the data automatically. And milkshake right now has the highest grand total. So this data as moved down. Let's go ahead and change this particular setting back to the original one. Okay? Now I will come back to my data to phrase this data. Then once again, you will observe that my milkshake had moved to the top spot with the lowest sales. Now let's go ahead and look at something else. Now lets say, no matter how many times I go ahead and refresh this data, or no matter what the value of any of the individual categories is. I don't want this particular sequence to change. I always want my mood shakes can drink soft drinks and fruit juices to be in this particular order only. No matter how many times I go ahead and rephrase the data, no matter whatever the sales values are. Okay. Let's say I go ahead and once again change this value from 2222 thousand. When I rephrase this data, I don't want this sequence to change. So how can I achieve that? Simple? What I'm gonna do is I will right-click you, go to sort. And then I will take on more sort options. When this particular menu pops up, I'm going to click on this particular button that says More Options. And when I click on that, it gives me a small menu for you that in it says auto sort, sort automatically every time the report is updated, I'm going to uncheck this. And then I will click on, okay. Click on Okay one more time. Now you will observe that if I go ahead and replace this data, even though my milkshake has the highest sales within the category section, it has not moved its position. So it has all the other categories as well. The position remains the same. Now once again, if I right-click on this data, but to sort to mode options, go to more options one more time and put a checkmark over you. Click on Okay. And then I will select any one of the following sort options. I'm going to select ascending from a to Z. Now you will observe that if I go ahead and refresh the zeta and sorted again, you will observe that my milkshakes now move to the bottom spot because of the highest number of sales. Let me just go ahead and put it back to the default settings. Once again, I will sort it on the basis of the grand totals. Now my milkshake again comes back to the top spot. These are the various ways in which you can go ahead and use the value sorting techniques within pivot table. I hope you enjoyed this particular lecture. I shall see you in the next one. 26. 26 Double Click: Hey, welcome back. Now what do you see on your screen is a PivotTable that I have gone ahead and created out of the same raw data that I've been using. Along with that, you will also observe that I have gone ahead and added a few slices for my category, city and weeks. Now this particular data is actually showing me the sum of units for all the eight weeks. And now if I go ahead and look at this data very carefully, observe the trend of my sales. I will observe that in the third week, all of a sudden my sales numbers have shortstop. And if I look at other weeks, okay, So let's say that the first week my sales was only 450 units. The next week it was only 15 thousand units. But if I look at the C-H for my third week, the series are around 62 thousand units for snacks and pretty 11000 units for drinks. Which means my sales have gone up almost four times in a span of one week. This is the data that I should be having a look at. What really happened in this particular week that all of a sudden my data short up. Now pivot table gives us a very wonderful option, wherein I can just go ahead and have a look at a particular data mode in detail. So let's say I'm interested in only looking at the data for my third vehicle view and I want to know why my sales went up. All I need to do over you is I will just go to the Grand Totals OVO, my total week. I will just double-click on this data. When I go ahead and double-click on this data, you will observe that it opened up a new sheet altogether. But in my data is already filtered for the third week. Now if I look at my units, so video, let's look at the top 20 or top 25 seats for this particular month. What I'm gonna do is I will click on the drop-down over you. Then I will go ahead and click on Number Filters. And then I will click on Top Ten. I will say show me my top 20 seats for this particular data. When I click on that, you will observe that all of a sudden the numbers have shot up on the 15th of the month, the 15th of me. And then if I go ahead and look at my remarks column OVO, it shows that there was some kind of a soccer final that happened on that particular day because of which probably my seats went up. So there is a possibility that it was a soccer final of welcome. And people just wanted to come and sit down in this particular restaurant and enjoy the match. Probably this restaurant is very close to a stadium or a playground. And probably there was some kind of a soccer final over dead. And because of which a lot of crowd came in on that particular day and all of a sudden MAY sales shortstop. If I go ahead and let's say I want to go ahead and look at some more data. I go ahead and click on top ten. And this time I'm looking at, let's say tall 150 seats for this particular period. And if I scroll down, I observed that most of my sales have actually happened on the 15th of made self. And this sale actually shorter because of the soccer finding. In this way, what I can do is I can just go ahead and double-click on any of the data within my pivot table and look at that data in a filtered manner. Let's say for some reason I really wanted to go ahead and look at the data for my eight week. And I only want to look at the data for my drinks. What I can do is I can select this particular cell and double-click on it. And it will open up the data only for drinks for my eight week. Okay? So this is one way in which you can go ahead and look at the detailed dataset for a pretty good or value within your pivot table. Now let's look at two scenarios. Let's say in the first scenario, I want to go ahead and prepare this report and send it to somebody within my organization. I'm okay if this person goes ahead and double-click on the data and looks at the detailed data with you. I will just go ahead and create this report and mail it to the other person. And the other person can just go ahead and double-click on the data. And he can have a look at whatever data he wants to look at. But let's say if I wanted to go ahead and send this report to an external source outside my organization. Then what can I do? I only want that person to look at the pivot table. But if we double-click on the data, I don't want him to look at the detailed data because maybe the data is very confidential for any security reasons. I don't want to share the detailed data with that particular pursue. And probably only interested in showing that person a snapshot of the pivot table. So in that case, what I can do is I can click anywhere on my pivot table, right-click on it. Go to Pivot Table Options. Then I will click on this particular tab called data. And I have an option called enabled show details. I will go ahead and uncheck it, and then click on, Okay. Now if I go ahead and double-click on any of the data, we'll it will not show me the detailed information pertaining to that particular set. And I will get this particular message. We can change this part of the pivot table and it will not allow me to go ahead and look at any detailed information for my pivot table. All I can look at is the snapshot OU. But now let's assume that the person outside the organization is very familiar with Excel. And he knows very well that if he has to look at the detailed information behind this pivot table, all he has to do is just right-click on the PivotTable, go to Pivot Table option, click on Data, put a checkmark, click on Okay, and he will be able to look at whatever information he wants to look at. Only for this reason, excel also provides us some other levels of security that we can add to our pivot table. Now this becomes a part of my pre-release prerequisite, which I've gone ahead and covered in detail in my last topic. For this particular lecture, I only wanted you to know that there is an option that you can go ahead and look at the underlying data on your pivot table by double-clicking on it. I hope you enjoyed this particular lecture. And I said, See you in the next one. 27. 27 GETPIVOTDATA: Hey, welcome back. So coming so far in the lecture series, you must have realized by now that pivot table is one of the coolest technique in Microsoft Excel. It helps us to go ahead and create a small synopsis of a large cumbersome data, then helps us in analyzing and the decision-making process. We learned a lot of filtering, sorting, and slicing dicing methods within pivot table. That also helps us in looking at very specific data within our Pivot Table. In our last lecture, we also understood digested by double-clicking the data. We can also go ahead and look at the underlying data within our pivot table. But now let us look at a different scenario altogether. Let's say I have this pretty good pivot table in which I have added the category name, I have added the item names. I have the sum of units and then I have the weeks. Now out of all this data that I have on my screen, Let's say I'm only interested in looking at the data for orange juice, mango milkshake, and mixed batch puckered up. And how do I want to look at this particular data? Let's understand that. So what I'm gonna do is I will go to my index over you and I will open up this particular topic, understanding GETPIVOTDATA. When I open up this particular sheet, you will observe that a similar kind of a pivot table is already created on my screen. On this particular data. I have also gone ahead and created the names of my items in which I'm actually interested in. All you, I have the vk. Let's see what happens when I go ahead and click on the weeks over here and try to change the week. If I go ahead and select the second week, it gives me the data for my orange juice, mango milkshake, and mixed record off or second week. Let's confirm that autoinducers 170. So if I go ahead and look at my second week data, I have 170 sales for orange juice, for mango milkshake. Let's confirm the numbers. Topo mango milkshake. Let's come down and yes, it's 1112 and it matches over you. Similarly for my mixed batch per capita, it's 2310 and the numbers are matched. If I go ahead and change this week, first week, you will observe that the numbers will change dynamically. And that's exactly what I'm going to go ahead and try to achieve in the other raw data that I have. I will come back to my sheet over u. What I'm trying to accomplish over you is it should automatically give me the figures for the respective items when I go ahead and select the Vico value, okay, so what I'm gonna do is I will go ahead and let's say I want the data for third week. Then in that case, I will go ahead and select this particular salary you and I will say is equal to two. Then I will try to find out that is odd induced in my data. It's ADH induces OEO, and this is my third week's data. So I'm gonna select this particular cell and I will hit Enter. You will absorb that automatically. It has gone ahead and picked up the data for me. Now, if I go ahead and look at the data for my mango milkshake, then I can say is equal to. And then I will just select where is my mango make shapes. So it's probably some better wheel. And this is my total week's data, so I'm gonna select it and hit enter. Similarly for my mixed switch, but good, I will just say equal to both to my pivot table and this is my third week's data and I will hit Enter. Now let's look at what really happened. When I click on this particular salary. You will observe that it has picked up a big formula. It says GETPIVOTDATA and then opens up bracket and gives a lot of information. Now let's try to understand what is this information that it is trying to give us. Now what it is telling me is get the pivot data. Okay? Out of the sum of units. The pivot table is around the sum of units that it starts from the cell A3. So my pivot table actually starts from cell E3. Then it tells me from the item name, pick up orange juice out of the category pickup fruit juices. And then it says, which particular week's data or do I want. It tells me that I want the data for my week, three a wheel. But now let's look. What happens if I go ahead and start playing with the filter, Zoe? Let's say I go ahead and change this week to week four. Nothing is really happening, is just showing me the same data. Let's say what happens if I go ahead and start using my slices. So let's say if I click on drinks, you will observe that when I click on drinks, it is substantially meta-data for my odd induce an mango milkshake for the third week. Still not showing me the data for the fourth week. You will observe that it is still showing me the data only for the dod week. And also it is giving me an error for my mixed switch backorder. That is because once I select a slice of all my drinks or snacks have vanished from my pivot TB. And it is not going to show me any data or audio. But if I click on snacks over here, once again, it picks up the data from a mixed micelle, Dakota. But for my odd induced and milkshake, it starts giving me an error. I need to go ahead and fix these problems one, according to week, it should pick up the data. And secondly, it should not give me these errors when I ran the filters. Let's go ahead and look at it. I'm going to clear the filter, Zoe. Then what I'm going to do is I right-click on this particular formula over you. That this particular formula is based upon certain criterias. Item name is orange juice category is fruit juices, etc. What I'm gonna do first of all is I'm going to make it independent. So for that, what I'm gonna do is I will remove the categories from you. Okay? What I'm only interested is in the orange juice. So I'm gonna keep the item name as our induced, and I'm going to remove the category and I will also remove the gum always. Then I wanted to pick up my VQ from this particular cell, the wheel. I'm going to come down to this particular value, 30AM. I'm going to delete it. And I'm gonna give a cell reference to this particular self-aware. And in order to go ahead and freeze this value, I'm going to go ahead and press F4. And then I will go ahead and hit enter. Now if I go ahead and change the week two, week three, you will object that it is picking up the value for my orange juice. But if I go ahead and scroll down, it is giving me the same value for all the other items as well. Now why did that happen? Because in my formula I have selected item name as orange juice. I have to go ahead and make this thing dynamic as well. What I'm gonna do is I will come back to my formula. We'll I'm also going to go ahead and remove this orange juice from you. Instead of autoinducer, what I'm going to say is pick up the value for item name from this particular Silvio. And I will hit Enter. Now if I go ahead and drag this, you will all do that. It is giving me the value for odd induce. It is also giving me the value for milkshake, but it is not giving me the value for mixed match backorder. Why is that? Now if I go ahead and click on this particular cell over here, you will observe that I have by mistakenly given a space, I have to just remove this space. Okay? Because whatever values you give as a reference have to match with the values within your pivot table. If there was a space in mixed switch because it will not match it with the value that it sees within your pivot table. So both the values really have to match exactly character ways. Now once I go ahead and remove that space over u and hit Enter, you will see that it is going ahead and picking up the value dynamically. Okay? If I go ahead and change any values over here, you will see that automatically it is going ahead and picking up the value for that respective week. Let's confirm that we're looking at the data for I eight-week OVO. Let's look at the data for orange juice. Now for orange juice, my series was 267 and it is a perfect match for my angle milkshake. It was 380, and it matches that data. And similarly for my mixed veggie Dakota, it was 402. It is matching that data as well. Okay. Let me just go ahead and give it a nice Bordeaux. And if I keep on changing the week, it keeps on giving me this data dynamically. Now let's go ahead and handle our second problem. Now let's say if I go ahead and select drink, so we'll, when I select drinks, it gives me an error for my snacks. And when I select a snack, it gives me an arrow from my drinks. I need to go ahead and fix this. How can I do that? What I'm gonna do is I'm removing the spirit of you. I'll come to this formula wheel and I'm going to use the IF error formula in Excel. I'm going to say if eta, then I'm going to open brackets. And then I'm gonna say if there is no later than written this particular value, it does the calculation done within these brackets written this value, returned the value as I'm going to give a comma. Then I will type double-quotes. No data found, double-quotes. And then I will close the bracket and hit Enter. Now, I will go ahead and drag this formula one more time. Now, if I go ahead and run my photos, Let's see what happens. If I run the drink spirit though. It says no data found for mixed switch pagoda. If I'd under filters for snacks, it says no data found for my drink section. However, it returns the value for that respective item for the particular week. My data is still dynamic. That's how you can go ahead and use the get pivot data feature within your pivot table to go ahead and pull up any required data within your pivot table that you're interested in looking at. I hope you enjoyed this lecture. I shall see you in the next one. 28. 28 Pivot Charts: Hey, welcome back. Now as in when we're moving ahead in this lecture series, you probably must have understood the importance of pivot table and how it plays a critical role in terms of understanding the large convert some data better. Now, as an accountant, I might be very satisfied with the kind of output that pivot table gives me. But then people who look at the pivot table reports are coming from different backgrounds, okay. They have different mindsets. Innovate. What I wanted to tell you is I have different types of audience who will look at my pivot table and not everybody will be happy to look at a summary report which only shows certain numbers yet and there some people are visual. And they would probably love to look at the visual report of the entire data because visual data post to fall is more appealing. And then it gives you a better business insight when you look at stuff in a visual battle. So let's go to our index once again, and let's go ahead and open this particular chapter using charts and pivot table. And when you look at this particular page that opens up, you will observe that I have gone ahead and made use of different types of charts within pivot table to go ahead and represent my data. For example, the same value for four weeks is shown by a bar graph. Whereas the drinks and the category sales for my sum of units is being represented by a line arrow video. I have gone ahead and made use of the pie chart to look at my data in a different manner altogether. Okay, Now this is what we will learn in this particular lecture. We will learn to go ahead and represent our pivot table data in a pictorial or in a graphical manner. And this will help us to represent our data to different types of audience who are interested in looking at what types of reports, the one which represents numbers, and the other one that chose graphs depending upon their interest. Let's start constructing graphs within our pivot table. I will come back to my original data over you and you will observe that I have gone ahead and created this pivot table in which I have added the groups and the categories, and then I have used the cities within my columns. I've also gone ahead and added a smallest slicer away based upon the groups. Now let's try going ahead and adding up pivot chart over here. What I can do is I will select this particular pivot table. Then I will click on Insert. And then I will select this option that says recommended charts. When I click on this, it will give me a recommended chart based upon the type of data that I have in my PivotTable. I can go ahead and make use of any type of chart depending upon my need and choice. However, what I'm gonna do is for learning sake, I'm going to select the same chart that Excel is trying to recommend to me. And then I will go ahead and click on, Okay. Now the moment I did that, you will observe that a small chart has popped up on my screen. Are you? Okay? It is going ahead and showing me the data for my different categories. And this is actually a city wise data because if you look at the right-hand side of my screen, you will absorb that addition, showing me a color-coding wherein blue shows for Bangla or orange juice for Chen, I agree for Delhi. And accordingly, I can look at this particular data. We'll now once my chart is created, I can go ahead and play around with this chart and give it a look and feel that I prefer. What I'm gonna do is first of all, I'm going to remove these filters that are available over you. Okay? I can go ahead and filter the data based upon the filters that are available within my chart. But then what happens is it makes my chart looks a little cluttered. These filters can also be replaced by this slices within my pivot table. And that actually makes my chart and pivot tables look more appealing. What I'm gonna do is I'm going to select the chart over you. Then every click on the Analyze tab, we'll come to this particular option that says field buttons. And I'm gonna say a high load. Okay? With that, all the filters within my chart had vanished. Okay? Now I want these legends to be somewhere at the bottom of the chart. So what I'm gonna do is I will click on this plus button OVO, and that bumps up a menu called Chart Elements. Then what I'm gonna do is I will click on legends. And I would say put my legend at the bottom of the screen. With that, my CTs have moved to the bottom of the screen. Okay. I don't want the grid lines away on. So what I can do is I can select the chart elements once again, and I would say clear grid lines. Then within my chalk, I also have an option that in I can select a particular design, a default design, which is probably recommended by Microsoft Excel. I can select the design of my choice. That's a different topic altogether. But as of now, let's go ahead and select the design that is available to us. So what I'm gonna do is I will select my chart, go to the Design tab, and then you will see a lot of options under the chart styles menu. When I click on it, it gives me a lot of different options. As I move my cursor over them, it will give me a preview of how my data will look with that particular format. Let's go ahead and select one particular format. The way I'm going to select style it and you can select the style of your choice. Okay? With that, my data is ready. And even after going ahead and applying the style of area, we can always go ahead and make certain modification based upon our requirement. Okay, cool. Now let's say if I want to go ahead and add some data labels to my chart, I can even do that. I can select the chart, click on the plus sign, and add data labels over you. As of now, I don't want them, so I'm gonna go ahead and remove them. Okay, so that's how I can go ahead and create a particular chart. We'll now let's say I wanted to go ahead and add one more chart to my pivot table over you. Okay, so what I can do is I can just copy this pivot table and I will paste another copy of it or were you? Then I will go ahead and make some modifications to this. Let us just go ahead and click on it, right-click and then click on Show Field List. Let's say I go ahead and remove the group from here. I will only keep the categories. I also removed the cities from you. Now, I only have the sum of units for all the different categories over here. Now let's say I wanted to go ahead and create a pie chart out of this particular data. What I can do is I can just click on this particular PivotTables. Click on Insert, go to Recommended Charts. Okay, so right now it's recommending a bar graph for me. What I'm gonna do is I will select a pie chart. Are you? I'm gonna select the 3D pie chart and then click on, Okay. I will place the pie chart over here. Okay. Let's say we'll go ahead and place it over your resize it a bit. Then I will go ahead and remove the field buttons. And then let's say I go ahead and click a design and select this particular design over here. I would click on the chart elements. I will put the legend at the bottom. Now what I'll do is I will give both my charts or chart title, okay, So this one, let us say I will call it a category device sales. This one I will call it So devices. Okay, so I will click on the plus sign over here, and I will click on Chart Type. I will call it safety by seats. Now, extract necessary that in order to go ahead and create a new chart, I will always have to wear it and create a pivot table. Because depending upon the type of data that you have within your pivot table, you can go ahead and create multiple different types of charts. However, if you want a specific type of chart, then probably you will have to go ahead and modify your pivot table to suit the type of data that, that particular chart where you demand. Now let's say for this, but you get the same data. I also want to go ahead and create a line chart. What I can do is I can select this data. We'll once again click on Insert, go to Recommended Charts. And this time I'm going to select line chart. Are you okay? Then click on, Okay. And then what I will do is I will just go ahead and place my chart over here somewhere. And I will just drag it a little bit. I can modify my chart, the VI want. I will once again hide all the fields buttons from here, it to my Design tab. Then I will select a ready-made layout OVO. Then I can just hide the grid lines. Let's say. In this particular chart, I want to go ahead and look at my data labels, so I will keep the data labels active. Then once again, this particular chart, I will say this is a category line chart. I can give it whatever name I want. Now let us say if I go ahead and start playing around with my data, we'll let say if I click on drinks, you will absorb that my charts are now picking up their data based upon the filters that I have applied, and then changing the data accordingly. Let me just go ahead and add one more slicer to this. Let's just go ahead and clear this one. And then let's select the pivot table. Go to Analyze tab, click on Insert Slicer. And let's say I added citywide slicer also do this. Let's place it some better wheel. Okay, let's just modify this a little bit. Then. I will select this particular style over here. Now let's say I'm only looking at the data for Bangalore. Let's see what happens. You will observe that my data changes accordingly. Now, my data is also available in a numerical format because my pivot tables are always there. But along with that, my charts will also show the data in a visual format. Now you will observe one more thing YOU, let's say go ahead and change the filters. Are I start applying the filters. You will see that my charts keep on moving left and right, okay, depending upon the size of my pivot table. Now I don't want that to happen. Similarly, you will also observe that my slices also keep on moving and at this point of time they have even overlap each other. Now I don't want distinct to happen when I'm actually going ahead and presenting the data because it looks a little unprofessional. Let us go ahead and do certain modifications then. First of all, I'll go ahead and fix the issue for my slices and then I'll fix the issue for my chart says, Well, okay, let's go ahead and clear all the filters from here. I'm gonna select my slices over you. Right-click on them, click on Size and Properties. And then I will click on this particular option that says don't move or size with cells. And then I will close this. Okay? Similarly, I will go ahead and right-click on my pivot table, go to Pivot Table Options. Then I will go ahead and uncheck this option or Tophat column width on Update and then click on, Okay. I'll do the same for this pivot table as well. To Pivot Table option. And then uncheck this option and then click on, Okay. Now for my chart, I will select my charts. Then right-click on them. Click on Size and Properties. And then I will click on, don't move our size with cells. And then I will close this. Now if I go ahead and run my filters, Let's see what happens. You will observe that once I made those changes, normal slices nor pivot tables, normal charge is moved from the opposition. That's one precaution that you will always have to take before you go ahead and send the report. You go ahead and present this report to your audience. So that's about going ahead and adding visual effects and charged to your pivot tables. I hope you enjoyed this lecture and I shall see you in the next one. 29. 29 Auto Refresh: Hey, welcome back. We have come very far in this journey and we have learned a lot of features about pivot tables. By now, you must have become very comfortable going ahead and creating and playing around with your pivot tables. But throughout this journey, you must have realized that there is one small drawback that pivot table has. That is, it does not auto refresh at any given point of time when I go ahead and add any data to my dataset or delete any data from my dataset. Then I have to come back to my pivot table, right-click on it and then hit the refresh button. And let's say I weren't pivot tables to do it all by themselves. Does Excel provide any such kind of provision? Then I would say yes, unknown. Now why am I saying yes? Because let's say if I go ahead and right-click on my pivot table over u, then I go to this particular option that says Pivot Table Options. Then I click on the Data tab over you. I get this particular option called ads refresh data when opening the file. Now if I go ahead and put a checkmark on it and then click on, Okay, what happens? Now? What happens if I go ahead and put a checkmark over there is that every time this particular file is closed, and then I'd reopen this file. Whenever this file reopens, all the pivot tables within this particular file will get automatically refreshed. Innovate. It's an advantage for us. But while working on this particular file real-time, it will not do any kind of or 30 fresh. Okay? So if I make any changes within my dataset, then the only option to me will be going ahead and closing this file, then reopening it again so that all my pivot tables will be auto refreshed when the file reopens. Innovate. It can be advantage if you are going to go ahead and close the file and reopen it again. But practically it is not possible to always go ahead and close and reopen the file when you're actually working on it real tight. You want something that will work real-time on your pivot table. Excel does not provide any such detect option to us, but that is a welcome around that we can use. And that is what we will learn in this particular lecture. I'm going to go ahead and make use of the macro feature of Microsoft Excel. Now, in this particular lecture, I only want you to pay attention to whatever I'm teaching you because macros is a very vast concept in Excel. What we will be using is not even the tip of the iceberg when it comes to Macros. Just try to get an understanding and just replicate what I'm doing in this particular lecture. Just in case you want to auto refresh feature to be activated within your pivot table. Okay? If you want to go ahead and learn macros in more detail, then I would recommend you to go ahead and enroll in a course which is specifically around macros. Let's start building our macros. Now basically what a macros. Macros is something within we go ahead and record a process and then play that process over and over again in order to get a specific output. In order to go ahead and create a macro, we need this particular Developer tab in our Excel workbook. This Developer tab should be available in our ribbon at the top over you. Now just in case you don't find it on your webinar, what you can do is you can click on the File tab, go to Options, and then click on Customize Ribbon. Under Popular commands and main tabs over you. You will observe that a developer tab is available. You can just drop down the way and you can select the Main tab over here. And you will see that I have up checkmark on it. In case on your system, it does not have a checkmark on it. Go ahead and put a checkmark on it, and then click on, Okay. Once you do that, the Developer tab will appear on your screen. Now with the help of the Developer tab, what I'm gonna do is I'm gonna go ahead and click on Record Macro. I will start recording by microbe. Once I go ahead and click on Record Macro, It's asking me what should be the name of my macro. I will say the name of my macros should be Refresh. Then haven't go ahead and click on. Okay. Now the moment I clicked on okay, My Record Macro button has now converted into stop recording, which means the macro recording process has already started. What I'm going to do is I will just right-click on my pivot table over here. Then click on Refresh and then I will stop recording. Now what I'm gonna do is under the same Developer tab, I will click on this particular button over here that says Visual Basic. Visual Basic. I am interested in looking at modules over you. I regard and click on the plus sign. And I will double-click on this particular option that says module one. When that particular module opens up, you will see it has gone ahead and created some kind of a text over you. As I said, don't try to get into the details of it. Just select this particular texts that you see over you. Activate Xite pivot tables, brackets, pivot table, pivot cache dot refresh, Right-click, and then just copy it. And then I will close this. And now I'm interested in going ahead and applying this formula. Do my beverage sheet over you. Because my pivot table is available on the sheet called us pivot. I will go to my Visual Basic and I will select this particular sheet over here. Under the general drop-down over you, I will click on worksheet. And then under selection change, I will go ahead and select Activate. Then it says Private Sub worksheet activate. I will just go ahead and press Control V in order to wet and paste that formula. Then I will just go ahead and close this. Then I will come back to my Excel sheet once again. Now my macro has been applied successfully. Now I need to go ahead and test it. So this particular pivot table is created from the raw data that I have in this particular sheet. The raw data category. While this particular pivot table is created from this particular sheet that in it says raw data hours. What I will do is I will go ahead and select this particular pivot table first. Click on raw data category. Let's say for the first entry over you, let me just expand this. My first entry is for category daily bites, group snacks. And it says 13 units. Let's go ahead and change this to 13 thousand units. And now let's see what changes have happened within my pivot table. Now ideally, if the macro has been successfully applied, then even without hitting the Refresh button, my snacks and my daily bite category should actually show value more than 13 thousand. Okay, let's go to our pivot table. Now under my snacks and my daily bytes section, you can see that the value is more than 13 thousand. Even without hitting the Refresh button. Let's go ahead and make one more change. Let's say for mayonnaise instead of 25, I'm gonna say 25,000 million is falls under snacks group and under the dips category. So now these values also should be more than 25 thousand. Come back to my pivot table, you will see that the values have changed. Now let's go ahead and try making certain changes to this particular pivot table, a wheel. And as I mentioned, this particular pivot table has the raw data over you. It's created from this particular sheet in my audit or were you I'm gonna go ahead and change this 1313 thousand. Okay. Now, this 13 thousand has been made at $900. Okay. Probably at nine o'clock in the morning. My pivot table over there is an early data. Okay. But when I come back to my data where you look at the data for nine o'clock. It does not show any value which is greater than 13 thousand. Because this particular data is an ALU data. Just now I went ahead and added 13 thousand units in my nine o'clock shift. Ideally, at nine o'clock, the value somewhere it should be more than 13 thousand. But I don't see any such value where you plot is the issue. The issue is that when I went ahead and created by macro, I went ahead and hit the refresh button only on this particular puberty, but I did not make any changes in this particular pivot table while I was going ahead and recording my macro. That is the reason I make any changes. Do my dataset for this particular pivot table, it will go ahead and do the auto refresh, but it will not make any changes in this particular dataset until and unless I go ahead and right-click and then click on Refresh. Okay, how do I fix this? Now, for that, once again, what I will do is I will go to my Developer tab. I will click on Macros and then go to Record Macro. This time, I'm going to call this particular macro as Refresh on. Okay, then I will click on, Okay. Now what I'm gonna do is I will click on my Analyze tab away you. Then I will come to this particular button that says refresh. Click on the Dropdown, the window. And then I will click on refresh all. Then I will go to my Developer tab, and then I will click on stop recording. Once again, I will come back to my Visual Basic. We'll have a double-click on module. We are right now. Another code has been created that says active verb book, refresh on. I'm going to copy this code. I want to go ahead and apply this code on my entire workbook via entire workbook because I make any changes in any dataset. No matter how many pivot tables I have in my workbook, I want all my pivot tables to be refreshed in one goal. Therefore, I will go ahead and apply this code on my entire workbook. This time instead of a sheet, I'm going to select this workbook. I'm going to double-click on it. And agenda, I will select Workbook. What I'm gonna do is I'm going to select Sheet activate. Now in this second section over your wedding, it says Private Sub workbook sheet activate. I will just go ahead and paste this code. And then I will just close it. And then I will come back to my Excel sheet once again. Now let's try making any changes to any of the pivot tables over u. Let's say I come back to my raw data category and I change these values from 13 thousand to 13 and this 25 thousand to 25 respectively. And I come back to my pivot table and you will see that this particular pivot table is refreshed. Now if I go to my raw data where you fall, my audio data, and let's say this 13 thousand, I go ahead and make it 13 again. You will see that even this pivot table is updated. Now for the same pivot table, I go ahead and make the changes from twenty-five twenty-five thousand and thirteen hundred dollars. So this is around one o'clock in the afternoon, then it should ideally show value more than 25 thousand. In my 1300 hours shift. I will go back to my pivot table. And you will observe that for one o'clock in the afternoon, it is showing me a value more than 25 thousand. No matter whatever changes I'm making within my worksheet, my pivot tables are getting auto refreshed. So this was basically a small workaround using macros in order to go ahead and achieve the auto refresh option within our pivot table. And as I said, this is just a small tip of what macros can do for you. If you're really interested in learning macros photo, then go ahead and enroll for a full-fledged course, we only on microbes. Now before I go ahead and end this lecture, I want to give you a small tip. Now let's say I have gone ahead and use a macro within my Excel sheet. Now I'm trying to go ahead and save this excel sheet. If I go ahead and click on File, and then I click on Save As, let's say, I want to save this file within my document. So you will observe that by default, Excel has selected an option like this. Excel Macro Enabled template. Now, whenever you go ahead and make use of any macros, Excel will prompt you to save this file as an Excel macro enabled template. So that next time when you open up this particular file, you can go ahead and make use of the same macros while working on that particular file. But let's say if I tried to go ahead and save this file as an Excel file only. Let's see what happens. When I go ahead and click on save. Excel will actually give me a warning. It will say the following features cannot be saved in macro free workbook, which is a VB project. To save the file with these functions, click No, and then choose a Macro Enabled file type in the file type list. Okay, so Excel will actually prompt me that because I have made use of a macro within my Excel sheet, I should save this file as a Macro Enabled worksheet only so that I can use the macro features in future. I will click on No. And then I will once again go to macro enabled template over here. And then I will go ahead and save my fight. This was a good to know information in order to avoid any revoke in future. This was about auto refresh. I hope you enjoyed this particular lecture and I shall see you in the next one. 30. 30 Points to Remember: Hey, welcome back. So now we come to the last lecture in our PivotTable lecture series. We will talk about three considerations that one should take before going ahead and sending any pivot table on PivotTable related report to any of the recipients. Now in one of the lectures we spoke about this pretty good option wherein anybody can just double-click on the data and look at the underlying data source. And we said that we will talk about more security features in future. I'm going to delete this data that just popped up. I will come back to my sheet over u. Now let's say I'm going to ahead and sending the sheet to two different people. Now the first one is somebody within my organization. And I would not mind this person going ahead and double-clicking on any of the cells and looking at the underlying report. In the second case, I want to send it to a third-party vendor, but I only want that person to look at the pivot report. He should not be able to look at the underlying data source. How do I achieve that? Let's go ahead and this time, this methodology that in I can go ahead and hide the data source from one-party. I can go ahead and let other party have a look at my underlying data source. So what I'm gonna do is I'm gonna copy this entire dataset on to another blank Excel sheet. Then what I will do is I will go ahead and save this file. I will just right-click on it, click on Save As, and save it to a particular folder. And I'm going to call this particular file as my database file. Then I will click on Save. Now what I will do is I will go ahead and close this particular file and open up another blank Excel sheet. Let's close it. Now. Let's open up a blank Excel sheet. Now, I will start constructing my pivot table. Now what I will do is I will click on Insert and then every click on Pivot Table View, I would say on my existing worksheet, start constructing my pivot table from this cell onwards. Then it's asking me select a table or data range. In this particular worksheet, I don't have any data because this is a new worksheet altogether. So what I'm gonna do is I'm going to click on use an external data source, optional. We'll then I will click on Choose connection. Then it will ask me that is my file located. So I will click on Browse per mole. Then I will go ahead and select my file a wheel. My file is in my E drive in this pretty good at folder. And the name of my file is database planes. I will double-click on it. Then it's asking me in my database file, where is my data located? My data is on the sheet, one of my database file. If there were multiple sheets, it would have popped up those sheets in this particular menu, it's IV. But since my database file is only available on Sheet one over there, so it's giving me that option. So I'm going to select Sheet one over u. Then I will click on, Okay, and then click on Okay one more time. Now it has gone ahead and build a connection with this particular sheet and my database file. Now let's go ahead and create our pivot table over you. My PivotTable had groups, then it had the category, then it had the cities, and then it had this EAS unit. So I will just put this here as unit. Are you okay with that? My file is ready. My PivotTable file is ready. Now if I double-click on any of the cells over, yeah, I can see the underlying data. Okay. I'm sending this particular file to my internal resource or somebody within my organization. What I will do is I will save this file and call this file as internal. Let's save this file as in total final. Now what I will do is I will save the same file for my external sources. Well, but when sending this five to my external source, I don't want him to go ahead and look at the underlying database. What I will do now is I will right-click on my pivot table over you. Go to Pivot Table Options. Click on the Data tab over you. Then I have this particular option over here that says Save source data with file. I'm going to uncheck this particular optional you because I don't want to save my data source. I will click on Okay. Then I will click on file, and I will save this file as an external file. I will just call this file as an external file. Then I will save this file. Now, I've been also greater than close this file. Now what I will do is I will go to my pivot folder away. And first I will go ahead and open my internal file review. And I will click on Enable content. Now this is the file that I've been saying to somebody within my organization. You will observe that if I double-click on any of the cells, it will pop up the underlying source data for b. Let's close this file. Now let's go ahead and open our external file. Once again, I will go ahead and click on Enable content. And now if I go ahead and double-click on any of those cells over here, Let's see what happens. It gives me an alert saying that the pivot table report was saved without underlying data. Use the replace data command to update the report. No matter whichever cell I click on, it will not show me the underlying database. Because this particular file I will be sending to an external vendor or somebody who's not working within my organization. I want him to only look at the data that I'm sending him without revealing any underlying data source to him. That's about going ahead and saving your file in two different formats, using one for your internal resources and sending the other one do your outside windows. Now before I go ahead and end this lecture, there are few more tips that I would like to give you before you go ahead and send a report to anyone, whether it be within your organization or outside your organization, make sure that you keep certain things in mind. Now, first of all, it's very important that you present your data in a format which looks professional. And at the same time it looks eye-catching as well. Whenever sending a report, try and use one color combination. You have used a blue pivot table. And along with that, I have also used blue colored slices. Don't try to make a report very colorful. Using the standard color combination will make your report loop professional. Then one more thing that one can do is select the worksheet, click on the View tab and hide the grid lines because report looks really neat on a plain white background. Now the next thing that one has to remember is that all your columns should be off standard width. Now I'll tell you the reason why. Let's say if I go ahead and uncertain filters OU, you will upload that first of all, your columns will keep on changing the size. And along with that, what will happen is even your charts will keep on moving on the screen. Let's see if I go ahead and select multiple options over you. You will see that first of all, you are pivot tables are moving along with that, even your charts are moving. Then if I also keep on changing those slices, even the position of my slices keeps on moving up and down. What I wanted to do first of all, is I want to have my columns of standard width. What I'm gonna do is I will select my columns over you. Right-click on it. Then I will click on column width. Let's say I keep my column with two, then I will click on, Okay. I don't want my column width to change every time I refresh the data. What I will do is I will select my pivot table, right-click on it, go to Pivot Table Options. And then I will uncheck this option that says autofill column width on Update. And then I will click on, Okay. After this, no matter how many times I go ahead and refresh my pivot table, my column size will not change. Similarly, I want to ensure that the position of my slices don't change as of now, you can see that they're already overlapping. Okay. So what I will do is I will place them properly on my sheet somewhere. Then I will press control and select my slices, right-click on it, and then click on Size and Properties. Then under properties on the right-hand side, I will select this option that says Don't move or size, width sales. And then I will close this. Similarly for this particular PivotTable, I will right-click on it, go to Pivot Table Options, and I will uncheck this option and then click on okay. And also I don't want my charge to keep moving left and right depending upon the size of my pivot table. So I'm going to select my charts. Right-click on them, click on Size and Properties. And then 1 second I will select, Don't move our size width says, Okay. Now if I go ahead and make any modifications within my pivot table, it doesn't really matter. The position of my slices. Pivot tables and charts will not change. Let's go ahead and clear all the filters. I will once again repeat whatever we discussed in this particular lecture. Before you go ahead and send a report, make sure that whether you want the recipient to look at the underlying database or not. And based upon that, decide whether you will save the data with the resource file. Audio will save it without the resource file. Then always have uniform color combination across your pivot charts or your pivot tables. Always set a standard column width for all your pivot tables. Ensure that you lock the oppositions and ensure that you uncheck all the auto fit options. Similarly, when sending charts, ensure that all the charts that you show on a particular page have a common design. If required, always add a chart, title, legend, and data labels if necessary. And last but not the least, if it suits your preference, always remove the gridlines and tried to present the data in a plain white sheet because it looks pretty neat. So with that, we come to the end of the lecture series on PivotTable. I know we covered a lot of ground. And if you are familiar with pivot table in the past, I hope you added few more tips to your critique and probably must have taken your pivot table knowledge to a whole new level of expertise. Now I would only recommend that you go ahead and practice more because knowing Pivot Table in and outlays a very strong foundation in terms of your data reporting skills. Because as it when you learn mode at once technique in data analysis like visualization or dashboard making, you would always require this foundation knowledge to go ahead and build your expertise. I would recommend go to these lecture series once, twice thrice, and keep practicing with the resource file that has been provided with this data sheet. And during this journey, if you face any problems, always know that I'm there to help you use the question and answer section within the platform to go ahead and ask me any questions or doubts that you have in your mind, and I will ensure that I reply back to you at my earliest convenience. So keep practicing and keep growing Happy Learning, stay safe. And may God bless you.