Mastering Pivot Tables in Excel: From Basics to Advanced Analysis

1. Welcome to the course!: Do you also work with large datasets in Excel and struggle to gain important insights in your data. Then it's time using Pivot Tables. Pivot Tables help you analyze your data from different angles, spot key trends, and create clear summaries in just a few clicks. Hi, my name is Fabio Basa and I'm a trainer for data analysis with over a decade of experience working with Pivot Tables. In this video course, you will learn the key skills like creating professional Pivot Tables, building interactive dashboards, using Power Pivot, and automating with VBA. This course is for beginners to intermediate Axl uses, and it's enough for this course to be familiar with basic Excel functions. By the end, you will confidently analyze complex datasets and present professional reports. For the class project, you will analyze a sales data set to create a dynamic sales Dashboard with Pivot Tables, charts, and Slicers. Let's unlock the power of Pivot Tables and see you on the first lesson. 2. Module 1: Introduction and First Steps: In this first module, you will learn what pivot tables are and why they are useful for your work with Excel. You will learn common mistakes and how you can avoid them. You will set up your very first Pivot Table for data analysis, and I will also show you how you can customize Excel for better work with Pivot Tables. So let's get started. 3. Using Pivot Tables in Business Scenarios: In this first lecture, we're going to discuss what pivot tables are and why you should use pivot tables for your daily work with Excel. We can see here already the official website of Microsoft overview of pivot tables and pivot charts. As we can see pivot tables are a tool in Excel, build in which helps us to summarize, analyze and explore our data in an interactive and very user friendly way. So pivot tables are a tool, as I said, like built in in Excel, which enables us to summarize our data in a very quick way and extracting information out of our data and present it in a structured way. So then we can analyze not only our numerical data, but also summarize our data by categories and subcategories. So to sum up what pivotables are, we can say that pivotables are a tool, which is built in in Excel, which helps us to quering large amounts of data in a very user friendly way. Can subtotal our data and aggregate our data, summarize it by categories and subcategories. We have different interactive ways how we can analyze our data and we can also create visualization out of our data, which is meant with pivot charts. This is also something we're going to have a look at in this course. Now we also have a look at our database which we're going to use in this course. This is the spreadsheet, which you also find in the course material. Here we have different columns. In total, we have 1,000 rows as you can see, I just press the Control button and arrow down on my keyboard and so I can find out how many rows I have in this database. We have different columns. For instance, we have the idea of our data, we have the date, but also the year. We also here our data on country and city level, then we have the volume and the price, so we can calculate also the sales out of it and we have also information about the business area, products, and customers. Let's discuss why we need pivot tables. Let's think about different questions. We want to extract information out of our data, one question could be, what is the average volume in the year 2025? Of course, we can just filter in our database here year and 2025 and then have a look at the volume, can also mark it with control, then the shift key, and also arrow down. Then we can see that the mean, so the average is 964.26. But we could also just calculate it with the so called average function in Excel. As you can see, I have here my cells selected already, and then we can also see this figure. But it's just way easier to use a pivot table for that. This we can see here. In the first place, of course, you don't have to understand how we can create such Pip tables or how we can read them, but just have a look at it. Then we can already see that we have the same figure and here we also have such an interactive filtering option. On the right side, we also have such a menu bar. As you can see, we have the same figure as well in the formula function, as well as in the pivot table. What is the advantage of such a pivot table? This is not so clear in the first example. Let's go on with the second example. There we have the question, what is the highest volume per business area in China? And so there it's not so easy anymore with formula function in Excel and also filtering because in the first place, we have to filter here on the country China. So we have to remove our filter, first of all, for the year, so clear filter from year, and then we have to put on a filter on country level for China, as you can see. And then we also have to summarize our data on business area level and then calculate the highest volume. Calculate the max. Of course, we can also use here the Max formula function, but it's not so easy anymore because we have to also split it here on business area level. Let's have a look at the result with a pivot table. As you can see, it's here also presented in a very structured way. We also have the filter here already on China, and we also have it here split it on business area level. You can see here, we also have each cell the maximum of our volume. Here, it's definitely way easier to work with such a pivot table. The last example is also the question, what is the standard deviation of the prices on customer level in the countries US and UK. Also here, we need to filter, first of all, for UK and US. Then also we would have to calculate the standard deviation for the prices, which we can see here and also here, it's not so easy anymore to summarize it with formula functions, then we have to use several formula functions. But using Pivot tables, it's just way easier. We have it here already summarized on customer level, as you can see, for UK and US and also here in such a two dimensional table overview structure, and we also can see here that's calculated for the standard deviation already for the prices. You can see, it's very easy to work with pivot tables, but we have not learned yet how to create such pivot tables, but we can see that it's very easy to read the information out of those tables and that's why we're going to learn everything you need to know about pivot tables here in this course. 4. Common Mistakes When Creating Pivot Tables: Before we even start now creating our first pivot table, it's very important that we discuss here in this video how our data should look like, so how our data should be structured because every pivot table is related to a dataset, and this is why it's so important because the preparation here also leads to a successful pivot table. This is why I want to present you here typical mistakes, but also here an overview of ten quick tips for preparing our dataset. Let's start with the first quick tip, which is just that we try to format our dataset as a table. Why is it that important? Well, if we have a look at our dataset here, then it's not formatted yet as a table. This is something which we can see here also on the menu bar because if a table is really formatted in Excel as a dynamic or intelligent table, then we can also see the new menu bar. So we can format this table as a table by clicking on home and also form it as table, and then we can choose one of those table styles, for instance, here Light Blue. And then we can see here in the Dialog that we can create a table out of our data, and then we can see in the menu bar that this is formatted now as intelligent table in Excel. We can also make this with the shortcut Control and and by pressing Control and then also our data is formatted now as an intelligent table. Well, this has many advantages. For instance, we can also add slicers to our data, for instance, on country or year, then we can also here select in interactive way our data, and then we can see only the raw data for a certain country or year. Then this is also very important because if it is really formatted as a table, then our Pivot tables later, which we will create are really related to the entire dataset. This is really considered as an object and not by a statical list. The next quick tip is here that we also use column headers. This is our data structure, how it should look like. And here we can see that we have column headers, ID, date here, country, city volume price, business area product and customer. But we also have a bad example here. In this example, for instance, we also can see here this row, and for some users, it's not clear if this is here the column header or here in row number two, if this is a column header. So please make sure that you also use column headers. It's also important that you prepare data types, if possible. For instance, here in this column B, we can see the date, but this is not formatted in the correct structure, which we can also see here by clicking on home number, and then we can see currently it's formatted as a number and not as a date. This we can, of course, change here just to a short date to make sure that the data type is also correct because the correct data type also needs to be reflected in our pivot table which we'll create. Also very important is that we remove empty rows, but also empty columns. For instance, here, column D and column G, they are both empty. So please make sure to also remove those empty columns just with a right click and then delete. Or if I undo it here with Control set, I can also just press Control and minus in order to remove those columns. Also for our rows, which are empty, here another shortcut. If you want to select a column, then you can just press Control in space and also control again and control in space to select the next column, which is empty to remove it. And here another time, saying you can just press Control and minus to remove those empty columns. And by selecting empty rows or certain rows, you can use the shortcut Shift in space and also pressing control again and shift in space to also select those empty rows. And here we have control and minus you can remove them as well. So here also important is that we do not use any aggregations because a pivot table needs to be related to our entire dataset, which should not contain any sums or aggregations. For instance, here we see sums. For instance, this sum, if we go in there in this cell with F two, pressing F two. Then we can see here the sum formula, which is related here to our column volume. And here we can see also the result of the sum or here the result of the price. And if we say now, this is here, the input for our pivot table, then it also contains those sums which we do not want to have in it. So no aggregations, also no subtotals. So I also would just here remove those subtotals or any calculations in it, also here, adding some sums as we can see here, I would remove them. Also important is that we split multiple tables into different worksheets or also imagine if we have here this table, and in the same worksheet, we also have another table like that. Then it's better to just remove it here and then just insert it in another worksheet. Then it's better structured, and then we can also create a pivot table based on this new worksheet. And also important is that we save all files in one folder. This is also an important quick tip because in the end of the course, we're also going to speak about Power Pivot. In Power Pivot, we can also use different data sources, not only Excel, but also CSV files or databases, and here it's just easier if you collect all of them in one entire folder. The last tip here is just that we remove entries outside a table, which also makes it easier. For instance, if we have here some entries by using here a formula like the rand n formula. Then we can see here some values and sometimes mistakes can be caused because this is related to a range here, which can also include outside of the table different values, please make sure to insert them in another working sheet or just remove them. But you can also make sure to not have this mistake by just formatting here the database as an intelligent table, which we did initially here in this video by pressing Control and T. Those are very important quick tips. Make sure with your database that you make this right and also do not make those mistakes. 5. Creating Your First Pivot Table: So let's finally create our very first pivot table now together. In the last lecture, we've already learned that it's very important that we format our dataset as a dynamic table or also known as an intelligent table. This is very, very important because it also helps us in the way how to create a pivot table. But let's first of all, look at it how we would create such a pivot table without a dynamic table, so that we have a dataset like this only as a statical list, so not formatted as a table, what we can see here. We can do that by clicking on Insert in the menu bar, and then here we can find tables, and then we can click on Pivot table. Here we can also say from a table or a range. Since this is not formatted yet as a dynamic table, of course, then Excel understands it here as a range. So let's click here on creating a Pivot table from a range, and then we can see here our range. This working sheet is having here the name database, and then we can also see here the range from a two to J 1001. And then we can say here, this is our range, and then we can also choose where we want to place our Pivot table. And we can say, either it's going to be placed at a new working sheet or also on an existing working sheet. I would rather prefer to say this needs to be placed on new working sheet so we can select this, and other thing we can still ignore. So let's click on Okay. And as we can see, we have here our Pivotable and this was successful. One issue which can occur here is when we jump here at the end of our data set with control and arrow down, then we can see here if we would have here new entries, such as 1001, 1002 or 1003. Then, of course, we have different values here. For instance, we can say different revenue numbers, multiply it with 1,000 or 10,000, so those can be yeah, such new revenues. And if we look here at our pivot table and go here and pivot table analyze, then we can see here change data source. And until now, this only has here the range from A one to J 1001, as we can see here. So the new entries, they are not considered. And this is why it's so important to say here that we do not want to create the pivot table based on a static range. We rather would prefer to create a dynamic table here by pressing Control and T or saying here, form it as a table. And then we can also choose here the table style, for instance, here in blue. And then this is formatted as a table what we can see here. Yeah, then it's just easier by clicking here on insert tables, and then we can say here, grading pivot table based on this table. And then we can also see here, not the range is selected anymore. But it's selected here the entire table. So this is named as table one, but we can also give it a name here by table design and going into table name and can say here, database or database new, we can press Enter and say here, insert and table, pivot table. And then we can see here this is the new name, and it's considered here as an entire object. We can also say here on a new working sheet or existing working sheet, then we can place it also here next to this pivot table, which we've created before, and then we can go here on Okay and have created here the next pivot table. Also interesting is here that not only those tables can have names as we can see here in table name, but also such pivot tables can have a name going here in pivot table, pivotable analyze, and then we can also create a name here. We can see, say here, pivot table new then we see here also that the name is changed. So there's another opportunity. So there's another way how we can create such a pivot table, not only by clicking on Insert, but also it's possible by clicking here in Table Design, and then we can also say here summarize with Pivot table, and then you can also get into this dialog which you can see here. Also interesting is clicking here on insert tables, and then you can also create here next to pivot tables, also a recommended pivot table. And this already contains such an analysis. So we can create here, such a recommended pivot table. And as you can see here, this is here something which Microsoft identifies here so that it's interesting, for instance, to analyze the volume on customer level, and then we can also click on Okay here. And as we can see here, I can hear price control plus to insert here you column. Then we can see here that we have a new pivot table. And this pivot table here shows the volume on customer level. But maybe we also want to do our own analysis and how this works, how we can create such a report and also make it on our way. So for our data, which we want to analyze on our way, this will be the thing we're going to look at in the next video. 6. Performing Your First Data Analysis: We have already created our first pivot tables, and in this video, we're going to start with our first analysis. So extracting information out of our data with using a pivot table. But before we go to start here with the first analysis, I would like to illustrate again why it's so important to use such a dynamic table. In the last video, we've seen that we could also create a pivot table based on a range instead of such an intelligent table. Then we've seen if we start here adding some new figures, some new lines here, then we have the issue that the pivot table is still related to the old range, for instance, here only until row number 1,000. But what the intelligent table does here is once we add some new entries, such as those here, only as an example, then we can see also the formatting that it's here already considered in our table. The great thing here about such an intelligent table is here that it's really seen as one object, which we can see here if we select everything with Control A. This is also how the pivot tables work. So as we can see here by clicking on pivotable, analyze, we can see here, change data source, so it's really connected here to our initial database, and once we also add some rows for the future, then they are always included and we do not have here the risk of not considering new values. Is why I would rather suggest here when it comes to this pivot table, which is here based on a range that we just delete this pivot table. It's easy. We can just press Control D or just selecting the entire columns with Control and space, and then here shift and arrow to the left. And with Control minus, we can just delete it. Then we can work and start here with this new pivot table and start creating our first analysis. For instance, we want to see here on country level how the volume looks like. So what's the structure to create such a pivot table? First of all, we have values. Those are always numerical data, such as the price, for instance, but also the volume or later, also the revenue. And then we can create our analysis with those numerical values. We have numbers here, but we also want to describe our numbers with categories, such as the column country, city, year, but also customer or product. Those are categorical data. And this we can typically pull here into the rows or also put here in the columns. So that means such a pivot table is structured as a two dimensional table. And we can see this the best if we just grade such an analysis by clicking here with the left mouse click and just pulling here the volume into the values. But we can also undo our pivot, and there's another option. We can also just click on here. And with this, it's also important to create the first pivot table. We can also add here the country, for instance, in the rows, then it looks like that. Then we have it here on country level, but we could also place the country here with the left mouse click here, pull it into the columns. We can also create it here on columns level that in the columns we have here, our countries here. Oh, then we can also say that the products are interesting for us. This is why we can also put the products here in the rows, and then we have here such an analysis, which is very interesting already. Or we can also just use the business area does not contain so many different attributes, and then we can see here such an analysis and we can instruct interesting information out of it, like in which country, in which business sector, we have the highest volume, for instance. Also very important is to format our data. I would suggest to just select the entire worksheet just by clicking here or pressing Control A, and then you can just click here on this button, commerce style, or you could also use the shortcut Control, Shift and one, and then you can see that we also have this thousandth separator and also we can here decrease our decimals that we have just fewer decimal places. Now it's just easier to identify which country we have the highest volume. What we can also do is just to swap it. So putting here the country in the rows and the business area in the columns, then our table looks different as you can see here. Yeah, in the end, I also want to talk about the filter options. You can also place here the year in the filters, and then you can also filter here in a dynamic way here by saying select multiple items that, for instance, we only want to see the year 2026 and pressing here, and then you can see here and numbers change. I can press Control set undo it or control why to repeat it again. And as you can see, whenever we change the filter, also the numbers are changing. So in this lecture, we have learned how to create such an analysis. We have seen here that it's structured like that that we far all the columns, which we can see also here in our initial dataset, and then we can just place them in the filters and the columns, rows and values. 7. Customizing the Ribbon in Excel: Now back here in the Snactsit we're going to have a look at how we can customize our Quake access tool bar. So I can just press here Control and F one to show all the ribbons which we have here in the menu bar. And here we can also click on this. I can hear, and then we can here navigate two more commands. And then we have the option to click on Customize Ribbon because there we have all our tabs. So the standard tabs or ribbons are home insert page layout, formulas, data review, view developer, and so on. We can also say here that we want to customize our own one. So therefore, we can just say here tab, and then we can also be, and then we can say tables. So we can pass here and then we can move from popular commands to all commands, and we can just press here P because then we see all the actions in Excel existing with P, and then we can also go to all of them with Pivohart. Then we can, of course, select them and we can just say Add, and then we can just add them here in our groups or also with a left mouse click. We can just drag and drop them here in our new group, pivot chart and pivot table here, for instance, or the simple creation of such a pivot chart. And then we can also make groups here in our tab, can rename it here and also can say here for pivot table creation. We can name it like that. And there are plenty more which we can add, like also pivot table filter connections or pivotable name. We can also add here. Also interesting are maybe also actions for tables, then we can also press T here, table options, for instance, or also filtering and many, many more, we can create here different groups. This works very easily. We can just press here new group, and then we can see that we can also add actions here into this group. And then we can also press Okay here. And then we have created our new tab. And by the way, we can also go here on Import Export. Such a customization here of a new tab, we can also export, and later we can also import existing customization files. So let's press Okay here. And as we can see here, we have our new tab here, pivotables and all the pivotables actions which are maybe interesting for us. 8. Quick Access Toolbar Configuration: So in the last lecture, we've learned everything we need to know here about customized ribbons here in our menu bar, and we've also here created our own tab, our own ribbon named pivot tables here with different actions for pivot tables. In this lecture, we're also going to discuss how we can customize our Quick Access tool bar. And here we can just click on this customize Quick Access Toolbar. And here you can also see which actions are activated already or here, added Quick Access Toolbar, for instance, here, saving our file and also undo or repeating certain actions. And here we can also add new actions which are interesting for pivot tables. So we can just go here on more commands. And as you can see here in this new dialog here in Quick Access Toolbar, we can also head here to the all commands, and here we can see all the commands existing in Excel. So here we can just press P. I'm going here to pivot tables, for instance, pivot charts, and then we can also edit here or also grading a pivot table, pivot table creation, filtering name style options, and so on. And also here, it's possible that we can import existing customization files or also export them. We can just go on ok. And as you can see, we have here two new icons, and here it's possible to create a pivot table based on data input. Either it's a range or it's also possible with an intelligent table. Then it's also possible that you go here in the menu bar on certain ribbons and also search for actions such as tables, for instance, or here Pivot table. And you can always do such a right click. Then we can also say that we want to edit here to our quick access tool bar. Or also interesting, for instance, here, I'm going to data. Often we have to also filter our tables or pivot tables. We can also say here with a right click. Want to add it here to the Quick Access tool bar or also here filtering, sorting, for instance, sorting here, scanning or decanding is also interesting to edit here in many, many other actions which you can see. It's very simple to add them here to the Quick Access tool bar. And also, you can say here if it is shown here on the top or also at the bottom. So we can say this Quick Access tool bar should be shown here below the ribbon, but we can also say here, it should be moved here on the top, just by saying here, this should be shown above the ribbon. And then it's just a bit easier to navigate here and also to use those pivotable relevant actions, just to add them here to the Quick Access tool bar, and you can easily click on them. And then yeah, the work with pivot ables is just getting a bit easier. 9. Working with Dynamic Tables: So welcome back here to this lecture where we're going to speak about dynamic tables. Not only with Pivot tables, we have many, many options to analyze our data in a dynamic way, but also with dynamic tables, we have those options. So let's have a look at our table, which we can see here, and we spoke about it already that we can format a data range as an intelligent table with the shortcut Control T, or either also it's possible here with clicking on Format as Table. Then as a result, we also generate here this new ribbon in our menu bar table design. And here it's possible also to use quick styles to also format it here, the layout. For instance, we can say here, we want to choose the Blue table style, light, quick style, or we would also like to highlight it here in a light orange. So there are many, many colors, as you can see. So let's choose just one of it. Like for instance, this one, and then we want to have a look at more options which we have here. So interesting is also here to activate the header row. So this is very important that we can also see the headers. In our case, it's here in the first row, and as you've already learned, you can also select rows by pressing Shift in space and another time just to select the entire row. And then with a right click, you can also say here insert, or you can also use the shortcut press control and plus, and then we can here add more rows. Why do I do that? I want to show you something which is very, very great. It's here filtering objon. So using such called, which you can see here table design, data slicers. So let's click here on slicer and what happens here is that a new dialogue pops up, and here we can select all our columns which you can see here in the headers. And here we can also click on here, for instance, country, but also city, I would only choose categorical data, but also business area product and customer interesting. So let's click on Okay. And also when it comes to those slicers, we can also format them with different layouts, for instance, here in orange, but I just leave it here in this blue style. Then we can position it here on the left. And also interesting is, if you just press the alt key, then it's also here along our grid lines, what we can see here. I can position a tier, for instance, and how does it work now? It's very interesting. So when it comes to our entire dataset here table, and we click on year 2025, we can see that everything is filtered only on this year. So that means I do not have to click on the column C here, year and selected here. I can just do it in a more user friendly way. This also with our other data slices, which we can see here, so we also have here a new ribbon in our menu bar slicer. And here we can also say with columns that we want to choose two columns, for instance. I can also press here again, that it's also positioned here along our grid lines, and then we can also position it like that, that it's a bit smaller. We do the same here with city, also that we have three columns, for instance, and position it like that. And also here the business area can also say here two columns that it also fits here. And also product is here and customer we can also hear position on the right. Interesting is also just to press the control key and then position here on the left. And as you can see, then you can also hear duplicate those existing slicers, but I can also remove it here. And it's also interesting to look at the total row. So I can here click on Total Row, and as you can see, then I also generated total. I would not recommend that because then also mistakes can occur. I would rather suggest you here to insert more rows and then work with the so called subtotal formula in Excel, so we can just type here subtotal. And it works like that, we can aggregate our numerical values now with different aggregation functions such as, for instance, I can press the tab key this is number nine. And then I can say here, what is the reference? I can just select everything with Control, Shift and arrow down. And then I can yeah have a look at my result here. As format it here, I can highlight it here FAD, just with B, clicking here in Home and font or also Control and B is the shortcut for it. I can also add here 1,000 separator, and it's also possible with the shortcut Control, Shift and one, and then remove here our decimal places or let's say fewer. And then we can also copy it. And here we can also say with Control V, we want to show it for the prices as well, for instance. Then we can see here the total of our volume. And interesting is now to analyze it here for several years, 2025, 2026, 2027, but also only for Germany, for instance, if I want to select more countries, then I can just press here the control key, then it's for Germany and the US and Mexico as well. Um, I can also select here, the cities. Interesting is also all the cities, which do not belong here, for instance, to Germany, are not visible here anymore, for instance, cities of Gina. Then also business areas, I can filter and whenever we filter, we only see here the results and also our subtotal. Please make sure to not use here, um, simple sum formula because the sum formula does not work here with filtering, we'll always show them the total, but we want to see based on our filtering the result. So this is very interesting. What else can we do here with intelligent tables, getting back? We can also say here, we want to highlight here with a FAD fund the first column, for instance, or also the last column in a different style. It's also interesting. We can work with banded columns or not. Then whenever we refresh our data, it gets refreshed. So it can also be connected to a data source. And then interesting is also here the name, which we said here already, so we can always hear um, rename our table, and we can also resize it. So this is the trend range, and then you can also say, it should be extended, for instance. Then also important, sometimes we do not want to work with intelligent tables anymore. Then we can also say convert to range, then it can be converted to a normal range. And in this dialogue we would have to press Okay. Then you can also remove duplicates. It's also interesting sometimes. And another positive thing about the tables is when we look at the columns, what we can see here, normally, we have letters in Excel, like here. And when we scroll down here, then we can see that in our columns, through the intelligent table, we can also see here now the name of the columns. And this is also a big advantage here with our work with those tables. I would say not only Pivot tables are interesting, what we can see here in this sheet. Sometimes it's also just interesting to work with our entire table. Then we can also see the values here, every single row, but also we can aggregate our data in the way we want to do it. And also interesting here, going back to table design, it's not only impossible to create a pivot table based on insert and then tables and pivot table, but it's also possible here with table design and then saying summarize with pivotable, then you can also create a pivot able based on this table here. 10. Module 2: Building and Customizing Pivot Tables: In the second module, I will show you how to build Pivot Tables step by step. You will learn how to use fields, how to sort and filter data, and you will get to know the G Pivot data formula function. Let's get started. 11. Creating Easy Pivot Tables: The last lecture, we have learned more about intelligent tables here and dynamic analysis possibilities. And what we can see here are the data Slicers, those we can also use for Pivot Tables and later. But getting back here, we can also have a further look at our data Slicers. We can always also remove the current filtering of our data just pressing here this icon here, and then the filtering gear on the years got removed as you can see. Then we can also select several attributes, several countries, as you can see, and it works and also pressing control, then you can select multiple countries. But if you press this one here, multi select or just press all the Ns, then you can also with this click say that others should be removed. For instance, I click here with left mouse click to Germany, and then with this mouse click, it gets removed. Now we want to remove all the filtering here to see our total result of 983,374. In the other worksheet, we can see here that we've created already in Pivot Table analysis. Clicking with a double click or with a right click and saying rename, we can also give it a new name. For instance, Pivot, or we can also say Pivot one, and then on the left, we have our database, and on the right, we have ours Pivot Table. So let's come to the next Pivot Table or next data analysis table. I'm clicking here on Table Design, and here we can also say summarize with Pivot Table. Then also here our entire database is selected as initial table object, and then we can choose where we want to place. Our new Pivot Table should be placed here in a new working sheet which we renamed as well, and then we can say here, this should be named as Pivot two. Then we can also add here a new column, just selecting it, also pressing Control and space, it's possible and control and plus in order to add a new column. And here we also can see here our Pivot Table fields again and can start our analysis. For instance, we also want to make an analysis how our revenue looks like or our sales. So that's going back in our database, and what we can see here is that we only have the volume and the price. So what we can do here is that we select here column H because there we want to place a new column just with the right click and saying insert or also pressing Control and plus. And then we can name it just revenue. We can implement the logic here with a formula just saying the volume should be multiplied here with the price, as you can see here, and this works also differently than a normal range in Excel, a normal Excel list. So we do not have here any cell ranges so that we say N 19 gets multiplied with 019, for instance, know those intelligent tables work like that that the entire column is also considered, and this is a big advantage because it automatically includes all the values. If we press control and arrow down, then we can see everything is considered here. And then we need to refresh also our Pivot table because as we can see here, the revenue is not part of our pivotable fields. So therefore, we go to pivotable analysis back, and then we can just press refresh. And as you can see here, now we also got the revenue. We can just pull here with drag and drop with the left mouse click into values or also just click on here revenue. And then also here, it gets consolidated, and this is our total revenue. Then we can also say here that we yeah just select our entire Pivot Table in order to format it. By the way, it's also here possible with going to Pivot Table Analysis, and then we can also say here at Actions, we want to select here the entire Pivot Table. Then it gets selected as well. We can go back to home and can also say here. We want to work with comma styles, so format our Pivot Table with 1,000 separator. But also, as I mentioned, no several times with control shift and one, it's possible as well. Then we can also say here, decrease our decimal places, and then we have formatted already here, our number, and then we want to also create an analysis here on a categorical level. For instance, we want to see here. Um, the country. And then we also want to see here, for instance, the years. So over the years, how it develops. And here we can say, this is not a number. This is just a general data type. Yeah, this is a very interesting data analysis already. So now we can see here the revenue development of all the countries. For instance, in China, we can see that it decrease over the years or 2025-2026, and then it increases again. Also in Germany, we can see that or also in other countries. And on the right, we can also see here the Grand Total. And also here is a grand total. So not only for the years, but also here for the countries, we can see Grand Total. Interesting is also here to put also the cities in the country. So then, of course, we can only see here the cities which are really related to a certain country. For instance, here cities in China or in Germany, here in Berlin, Bremen, Dtmond and so on. And in between, we can also see here a Grand Total. Then it works also like Dent. We can click here on the country itself, and then we can also show here more cities. And also speaking about the filters, here, it's also possible to put here, for instance, the business area and the filters, and as you can see here, then you can filter it in a dynamic way, also to say you only want to see the business area one and business area three, and then also the values will change in our analysis. 12. Understanding Field Options: There is one thing about our pivot tables we didn't speak about yet. Those are the pivotable fields, and here it's like that, working with pivot tables that we always have to click here in the pivot table itself, that also here this menu bar appears. This is very important, also when it comes to our data tables. Then as you can see here, this new ribbon regarding table designs and the work with dynamic tables itself also only appears if we really click into the table. Also with pivotable works like that. So we always have to make sure that we click in the pivot table. And then we can also see here the pivotable fields. For this example, we're also going to remove here the city again. And what we can make here also is that we can place here the pivotable fields in the middle, for instance. We can make it bigger, but the standard is always that it's placed here on the right. So I will put it back here on the right. And there are many things which are interesting. You need to know about the pivotable fields and this menu, how it works. Um, for instance, you can also say here task pane objons. For instance, you can also use here the search bar. For instance, if you want to see the product, then you can just type in your product and then you can also find the result here, the column which is named like that. When it comes to pivot ives we always speak about fields, not columns, and then you can also edit here. And um just by clicking on product, then it's not so clear where we want to position it. Here, the standard is that it's going to be put in the rows, but if we want to place it in the columns, for instance, then you should drag and drop it here in the columns. Yeah, I will remove it here again. This is very interesting, especially when we have different fields, so many, many fields, so many columns. So think about here our database, data table, which is very, very huge has several columns. Then, of course, it's also recommended to use the searching here to find the pivotable fields. But also later, if we're going to speak about Power Pivot, then we also combine different data tables to one entire data model, and then we do not only see different data tables, but also the columns which are in the data tables, and then also, it makes sense to search the results. Yeah, then we already spoke about filters, colums, rows, and values. So we've learned about Borg here at the top, we can see the filtering. Then we can also position there many, many fields, for instance, also the product or also the price. But normally more makes sense to put there the categorical values we have, here, some attributes which are categorical, but not a number. So with numerical values, it does not make sense so much. We could do it with the price, but then we work with a different logic. We want to say that only those which are bigger than 0.5, for instance, we will consider but not selecting every single number here. Then we put it back here, and then we can see the columns. So in the columns currently we have put here the years, and in the rows we have positioned the country and the value itself is the revenue. Then you can also see here the settings, and then we can also say how it is structured, the field selection and the area section stacked we can say we want to have it differently, for instance, here side by side. So this is definitely a personal thing. I like it more in the standard, but others also say this is easier to work with that we position it like that. Others also say field selection only, and it's more interesting that we only work with that. I wouldn't recommend that because then we cannot see anymore our four areas. You can also say areas section only two by two then looks like that, and we do not see our fields or we can also say one by four. Then we also do not see those fields. So I would recommend either here to do it in a stacked way or side by side, which also can be interesting. What you can also make is you're assorting, then those fields are sorted from a to set, but you can also sort it in a data source order. So this is also interesting, so when it comes to pivotable fields. 13. Pivot Table Options Overview: B now here in this lecture, we're going to speak about pivotable options. Here we can just click on Pivot Table Analyze, and then we can go here on Pivot Table. And as you can see, we also can give pivotable names. This is also interesting later if we manage several Pivot tables, maybe in one worksheet, maybe in different worksheets, but also if we work with data Slicers, then we can also manage if the Slicers will have an impact on one particular pivot table or on a difference several pivot tables. We can also change the name here. For instance, we can say Pivot Table two. The first one is here in our worksheet one, and then we can also go here in Pivot Table options. Then we can see here that a new dialog pops up and also you can make a right click here on Pivot Tables, then you come here in the same dialogue. You can also give here the name as you can see. Then it's also possible here to say that in the case of empty values, we want to show a specific value. For instance, we want to write here just empty. For instance, if we say with a right click here, we want to show the field list. If we say here, we also pull the city in the rows. Then we can see here for empty entries, we just have written here empty, but we could also say here with R click Pivot Tables that we want to just show a numerical zero, for instance, and then we can see here zero, and you could also just remove it here, and the standard for it is also just here. Numerical zero value. Then we go back here in the Pivot Table options because we could also say here, we want to have an auto fit here for our column width on every single update. I would not suggest that because if we, for instance, yeah, track and drop here the city into the columns, then as you can see, also the column width adjusted here now it's 17. But here, for instance, only nine. And before it was different. So with a right click here on Pivot Table objects, you can just remove it here by saying whenever we update here our columns, then it always maintains here the same structure will be in the same column width, and this is just better for our work because this just makes the work also a bit easier with our Pivot tables. Back here and Pivot Table options, we can also go here in total and filters because here we can also manage if we want to see the grand totals for our rows and columns. For instance, we can remove it as well, and then we can not see anymore here the grand total for years, but also we do not have any grand totals anymore for the countries. This we can also undo by going back on Pivot Table options and clicking here on Show Grant totals for rows and columns. Then there's also the display, and the options we can find here are similar to the pivotable fields. So for instance, we can also say here, we want to show or expand and collapse our buttons, yes or no. And also, we could say here by going into display, our feels should be sorted from A to set or in a data source order, and this you can also see here that it's not sorted like before anymore. And going back into pivotable options and printing, we have also some options for printing our Pivot Table. For instance, print expound collapse buttons when dspyed on Pivot Table or also here to check for repeat row labels on each print page. In this case, it's not important because our Pivot Table is not that big. But imagine if we press Control P here, then we can bring our Pivot table on the paper. And for instance, if we have huge Pivot tables, then it's also interesting here to manage that if we want to see the entries on many pages. Those are the most important options for managing our Pivot Tables. And in the next lecture, we're going to learn more about pivot tables. 14. Generating Data with GETPIVOTDATA: Sometimes we need the values which are in our Pivot tables and need to link to them out of our cells in Excel. So for instance, we can click here in SL C 15 and we want to have here the sum of the revenue in china for the year 2026. Then we can just say here. We want to link in D seven. And we would expect actually that we would see here Deven written in our cell now in our formula. What we can see here is the formula, G pivot data. And this G pivot data can be a bit complicated if we work with it, especially also if we generate here a new worksheet just by clicking here on Plus, or you could also use the shortcut Shift and F 11. And then, for instance, we want to say here sum of revenue in 2026 in China, and then we want to link to it, and then we can go to the pivot table here, number two, link here in this cell. And then we can see here also that we get here this G PIV data formula generated. This can be a bit complex, bit complicated to understand. Also here with our cell range here, B five, B five is here actually meant that this is the starting point of our Pivot table, as you can see here, and then we can see here. We take the year, the attribute is 2026 and the country is China. So this is how the formula works. We could also say here 2027, and then of course, we get a new number or we could also replace here the country, China and could say we want to have Germany. But if we really only want to have the value, then we could also say, we do not want to generate here this formula get pivot data. In order to do that, we can just click in our pivot table going into pivotable Analyze and saying here by pivotable objens that we want to remove it here, generate G pivot data. Then what happens here if we click in our Pivot table, it works just normally like we would also expected in every other cell in Excel. So we could also just say, we want to generate here or get the revenue of 2026 for China, 136,229, and then it also works, as you can see. I would really suggest you to not work, if possible, with this Get pivot data formula because it's just easier without. If we just want to get some numbers out of a pivot table in a very quick way. 15. Sorting Data in Pivot Tables: Sorting our values and pivot tables works quite easily. This is something we're going to have a look at in this video, and in order to sort our values, we can either go here on data on this ribbon in our menu bar and go here to sort and filter where we can sort our data, or we can also just simply work with a right click on our data and sort. What you can also see here at the top is the Quick Access bar. And here at the Quick Access tool bar, we've also added here the sorting, either a scanning or D scanning. So with that, we can also sort our values. For instance, we want to sort here our columns to say here in 2027, we want to see our revenues sorted, and then we can just say a to set, and then we have here the smallest revenue and sorted to the highest revenue value. We could also say here, sorting largest to the smallest, and then we exactly the opposite that we have here the sum of the revenue, the highest, and then we have it here sorted to our lowest revenue value. Yeah, then we could also just simply work that with sort and filter with this actions group, or we go here in this dialogue where we can also choose here either smallest to largest or largest to smallest here in our sort options. Furthermore, we could also say here sort direction from top to bottom or left to right. We say here top to bottom. And it's also simply work with Right click and SRD, where we can also go here in this dialogue to sort here our values. For instance, we could also say here in our grand totals that it has to be sorted here, SOD, and then smallest to largest. And also here we could say SRT for instance, largest to smallest. I would definitely recommend is always to click on a single cell here in the pivot table and then work with the right click. So either with the right click or here in the menu bar data and the actions group sort and filter, instead of selecting multiple values here and then try to sort our data, then you can also see it's not possible anymore to sort the data. So always make sure to click in a single cell of a pivot table, and then it also must work. 16. Filtering Data in Pivot Tables: After we've learned everything about sorting, now we want to continue with filtering our data in a pivot table. Everything we will learn here, we can also adapt here, by the way, with our database, with an intelligent table. So for instance, here, we can also filter our data either numerical data or categorical data. For instance, I can just say here with the volume, I click here with number filters that I want to see here every value which is greater than 500, then you can see here those lines and those rows where we have volume which is higher than 500. Then we can undo this, and we've also learned that we can use here the data slicers also to filter our data table in a dynamic and interactive way. This we can also adapt with our pivot table, but we will have a look at it later. So as we've seen, we can here not only work with a number filter, we can also say we want to see single values. I would rather suggest to do that with categorical data. So, for instance, here, business area, then you can also say just BA one and BA two, but you can also do it here with the slicer. So then it's just way easier. When it comes to numerical data, we have here numbers filters, and we can say equals does not equal greater than less than. You could also say a volume less than 1,000, and then we only see those results, or you could also say here in between. So 1000-2 thousand, here, for instance, we want to see those values, and then we can see only those. Yeah, what you can also do is here to show the top ten top ten items regarding the volume. Then you have those numbers here. Then it's also interesting to have a look at the text filters. So for categorical data, equals and does not equal and begins and ends with. So we could say we want to see all the cities which begin with as. So if we go down, then we can see all the cities like Seoul, Shanghai, Seattle, Shenzhen, and many moore. You can also do is also say here with the tax filter ends with, for instance, also ends with a, then you have Los Angeles, Las Vegas, Leeds, and so on. But you could also implement here logic with contains. For instance, interesting here with product, numbers, for instance, only those which start with zero and then the number or does not contain, and you can also go here into custom autoflter where you can also implement different rules here for filtering. But that's now also adapt it here for our pivot table. Here you could also say that you just change it here with the fields. So for instance, we say here, we remove the country, and we also add here the city, and then you can also click here city and go into label filter, where you can also say begins with S, and then you can also see here in the pivot table all the cities which start with S with this letter. Then you could also say here ends with S, then you see also here the three cities which ends with the letter S. Also contains here is interesting. Can say here contains A and then can also use a placeholder. So you can see here the question mark represents any single character, and here this placeholder represents any series of characters. If you use here, this logic, then you can see all the cities which contain here and A. We can also remove here the city and add here the customer because we could also say here with label filters that we want to see all of them, which are equal to C, and then we want to see the number one and then using, again, placeholder to say what is behind the one is not important for us, and then we can only see them. Or we could also say does not equal C one, and then we just see the other remaining customers. Then we can also remove the customer once again and also add the city again because we could also filter numerically for our data because we could also say here between, let's say, cities with revenue 1000-2 thousand, and then we can only see here peaking. And then we could also say we want to see the top ten items, so the top ten cities, and those by the sum of revenue. And then let's have a look at here, the grand total. So it's really depending on the grand total. We could also say, here we want to sort it, smallest to the largest, and then we see here the cities with the highest revenue in this case. So as you can see, we have many options, how we can filter our data, and here you can also try to do it on your own and filter the data in a new way. 17. Module 3: Exercises and Hands-On Practice: Now it's time to practice. In this section, I will give you an exercise to build pivot tables on your own. Later, you can also check the results with my solution, which I will present you as well in this chapter. Wish you much luck and get started now. 18. Exercise: Build and Analyze Pivot Tables: In the end of this section, I would like to give you an exercise here to the topic sorting and filtering. The first task is here to create the pivot table for the revenue on customer level per year, and then you should sort the largest to the smallest for the year 2026. Then the second exercise is also here for the topic sorting after you should sort all the customers from set to A. So now a text sorting, not a numerical sorting. Task number three should be here, creating a pivot table for the revenue on city level, and here you should filter all the cities for which the label starts with B. And the last exercise should be here as well about filtering where you should show all items for which the sum of the revenue is 10000-50 thousand here on city level. Now you can start trying here this exercise, and in the next video, I'm going to present here the solution for the exercise. 19. Solution: Pivot Table Exercise: Welcome back here to this video where we're going to have a look at how you could have solved here in this exercise for sorting and filtering. First of all, of course, we need to create the Pivot Table, clicking here in database, and there we can just say table design, summarize with Pivot Table or also possible inserting here, a new Pivot Table based on a data base here, this data table, and a new worksheet, which we can also move here in the middle and also give the name Pivot. So now we can start here with the first exercise, which is just that we create a Pivot Table, which we have done already. For the revenue on customer level per year. So now we can adjust position here, also our revenue and the values, and then also the customers, which we have here in our rows and also the year we can position here in the columns. Then we can also format it here. Either you can press your Control Shift one, or you can also use the comma style here, and then also with working with fewer or lower decimal places, and then you can also say here and this is not a number. This should be a text so general. And then we want to sort it, largest to the smallest, especially for the year 2026. And then you can just make a right click here saying sort, largest to the smallest, and then here as you can see, we have this sorting. Then you should also sort it for all customers from set to A. So we can also duplicate here this Pivot Table and just insert it here, control and C and control and V. And now we want to sort it here from set to A, and this is also the next exercise. Then let's speak about exercise number three, where we have to create a Pivot Table for the revenue on city level, and also we want to filter all the cities for which the label begins with B. So let's start creating Pivot Table for the revenue on city level. So let's say here, we can also here insert new roles with Control plus. This is exercise one. And, of course, we can also make it a bit bigger here, as you can see, and then we can say this is exercise two, and then we can continue now with exercise three. And here we can also just copy our existing Pivot Table to build on, and we would like to grade a Pivot Table now for the revenue on city level. This is why we remove here just to customer and add here the city, then it looks like that. And then we want to filter all cities for which a label begins with B, which is easy because we can just say here, label filter, and then we can say begins with B. And also here, this represent any series of characters, and then we have all the cities. So here, Baton Rouge, Berlin, Birmingham, but also Bremen, Bristol, and Busan. Come to the last exercise, which is just exercise four, and in exercise four, it's about showing all the items for which the sum of the revenue is 10-50 thousand on city level. This means we're going to remove here our filter from city, and then we can just say we want to implement a value filter. In between, we can say here for the sum of the revenue, 10,000, and here 50,000. And then it looks like that that we have here all our cities. We could also remove here just a year and also say here, we want to sort it from largest to smallest. Then it looks like that from 17,000 Luan to Guana Hua 48,564. So those are all four exercises, how you could have sold them. 20. Module 4: Advanced Calculations and Aggregations: In Module four, we will expand our knowledge about data analysis with pivotables. So I will show you how you can aggregate data and group data with pivotables, how to use multiple pivotables and also how to refresh and use troll throughs. So let's get started. 21. Aggregation Methods Explained: Welcome back here in this lecture, where are we going to speak about aggregation methods in our pivot tables. So what we can see here in this multidimensional table is that we have here simply the sum of our volume. For instance, we can also just position the revenue instead of the volume here, and the standard is always that we get here is sum of the revenue. We can also say that we want to drag and drop the revenue again here by clicking on value field settings. You can also say that you don't want to see the sum, so the consolidation of the entire revenue, but seeing here the number of values by clicking on count, we can also custom our name by saying this is the count of revenue, so count of values, which we have then this constellation here for China, but also here for business area one, for instance. Then we can see here in Germany, we have 38 revenues here in business area two. And we can also track and drop the revenue here again, and let's see what else we have. What kind of aggregation methods. For instance, also interesting would be here the average, so that we have here our arithmetic mean calculated. And as you can see, then it's exactly here the result out of those columns, so we take sum of the revenue and divide it here by the count of the values which we have then here. For our revenues in China for business area one. And then if we calculate it here, our division, then we have here the result of 6,079. Then we can also say with Control on A and control on C that we want to select the entire pivot table and copy it here, and with Control V, we can also just insert it here below. And then we can also say we want to further look at more aggregation methods. For instance, also interesting is just determining here the highest value of our revenues, which would be those values, but we can also say here by using the revenue again, clicking here on MI that we want to see the lowest revenue point, which we can see here then. And we can also put the revenue again in our analysis and clicking here on, for instance, the standard deviation. What is the standard deviation well, we have already calculated the arithmetic mean, and then we can also say that every single value, we want to deduct from the arithmetic mean, and then we also calculate the square out of it and calculate the mean of it, and then we also generate here the variance or the standard deviation. If you would calculate here the root out of the variance, then we have the standard deviation and we can also square the standard deviation in order to get the variance again. So with the standard deviation and the variance, we can also describe then how the variance looks like of our data points or how the deviation is from the single data points to our average value. And we can also say here by putting the revenue again in our analysis that we want to calculate, for instance, here the count numbers, and then we can only count our numbers existing in our table. And as you can see, we anyways only have numbers here. So it's the exact same then here calculating the count of the revenue. As you can see, there are different aggregation methods, and also important is here always to say with custom name that we want to adapt the name, then you can also just put a name in it, and then you can confirm it with okay, and then you have graded here in those analysis. 22. Working with Percentages: Welcome back to this lecture, we'll be going to speak about percentage calculations. So let's click here in our Pivot table. We can also use the right click to show our field list, and then we can see that we already have here our revenue with a consolidated sum of this key figure. And when we click here on value field settings, then we can also say, besides summarise values by our aggregation methods, which we got to know in the last lecture already, we can also click here on the right to show values there. As you can see, there are many options how we can calculate percentage figures. For instance, we can simply say, we want to calculate a percent of the grand total. If we click on Okay, then we can see already our result. We have here our total, which is always 100%, and then we have here our single percentage values. We can also select the entire pivot table by pressing Control A and Control C in order to copy it here, and then with Control and V, we can also insert it here below. And what we can do here also is that we just compare it here, show fields and we say here, we want to remove here the grand total. So no calculation, and then we can simply yeah see how those percentage values are calculated. So we can just select everything here. We can also press Control Shift on one or another option would be just here on Home button and then pressing here, comma style. We can say without any um, decimal places. And then, as you can see here, the total is 100%, so 5,044,351. And then, of course, we calculate here this number with this total. So this is exactly the value. We can also press Control, Shift and five on keyboard, and then you can see here and this is the exact same value. But you could also say, for instance, that this is not a total, the grand total, but a total for our division base should be here in our columns, either or in our rows. But what you can also see here is once we select everything here, all the percentage values, then our total, if you look below here, our sum is exactly the 100%. So also if we see those here in one row, then the 14.96% is exactly here the grand total percentage sum, which we can see here, below, which we can see here on the right. And also below, if we select those percentage single values, then we can also see here the 18.43%. What I also have mentioned is that we can also make it like that that we can with a right click, show the field list again, and say, next to the sum of the revenue in terms of percentage of grand total, we can also use here the column total as division base. So all the columns here have 100%, and the grand total, as you can see, and all the values here in total also lead to 100%. Then also if we select here an entire row, then you can see, this is the exact same number with 14.96% also, which is visible here, the first slide. But then in the next lines, it's also matching as you can see. Yeah, and then let's have a look at the last option. Right click here, show field list. We can also say next to the percentage of column total, we can see the row total can also use it here, and then as you can see here, the row total is also 100%. Let's look at more options, which you can do here with percentage calculations. We can also jump in again. By the name, you can also hear rename always the name here of this field, which is placed then in our values in our pivot table. You could also say here parent row total, parent column total, for instance, but also with different values and percentage we can calculate also interesting would be here in the running total. The running total, as we can see if we compare it here to the absolute values here, for instance, we could also say here with our forum painter, we want to have it in the same formatting style. Then you can also see here once we compare it here from 188,000, here, it gets accumulated here until we ended at 929, which is also here our column sum. As you can see, also with running totals, it's interesting to calculate, and then we have different options of how we can calculate with those aggregation and percentage calculation methods. 23. Performing Calculations in Pivot Tables: Back here in this lecture, we began to speak about calculated fields which we can use here in our pivot tables. First of all, we would like to remove some of our pivot tables which we have here, so we can just simply select them and press Control and minus. Also like here, we can just press Control minus after we have selected the cells. Yeah, another thing I would like to highlight, I would like to point out to you is that you could also just simply use the right click here in order to use aggregation methods. You could also say here summarize values by, and then it's the exact same then clicking here on the sum of revenue value field list settings, and then you can also say here what we've learned already that you want to summarize the value field by different aggregation methods. Either it's a sum or account calculation or average maximin, and so on. This you can simply also use just by a right click here, as you can see. It's also possible here, the same with the percentage calculations. And also, for instance, if we want to calculate a difference, for instance, then you could also see a difference from the country China, and then you can see the exact difference which got calculated here by those values. Then it's also interesting to use here in our pivot table analyze the fields items and sets because here we can also say we want to calculate a field. For instance, we would like to have our cross revenue, and then you could simply just say that you want to use the revenue. Either you type in revenue here or it's also just possible by using a double click. And then you can also multiply it here with 1.19, and then you can calculate here the cross revenue, as you can see also here, we could simply calculate the difference out of both values. Then it's also possible when we have a look at all the aggregation methods which we have here, then it's also possible here to use aggregation methods which we don't have here in this overview. For instance, we have here the average. So the average is calculated by the sum of all values and then divided by the number of values in total. But the median, for instance, has a complete different approach. So, if we compare here the average, for instance, of those values, then we can see here the average is here, 184,387. But if we use the median here, then it's this value, which is standing in the exact middle of our sorted dataset, and this would mean 173,853. And you could also simply use this approach here by clicking on Piper Table Analyze calculated fields. And here you could also work with all the formulas which you have like median, and then you could also say, here you want to calculate the median. So as you can see, you have many options, how you can use those calculated fields. 24. Grouping Data in Pivot Tables: So let's have a look in this quick video how we can also group our data points. So this is already here an analysis, which we have in our Pivot table here, and then it's also possible here besides the country that we also drag and drop here, our cities. Then we have here always a group, for instance, here in this entire group for China with all the cities, as you can see, and then we do not only see the total for the country China, but also the single revenue values here, also in business area level for our cities, which you can see. Same for Germany, Mexico, South Korea, but also UK and the US, as you can see. But what if we also want to create a group here for the states, for instance, the states here in Germany. So we could also say we positioned the country here in the filter, and then we would like to filter only on Germany, for instance. Then it's possible to select values such as Kato and Freiburg because those two belong to the same state here in Germany. You could also just say with a right click, you want to group the data, but also possible here with Pip table analyze. You could also work with a group selection. Anyways, I just use a right click here in order to group the data. And as you can see, everything is grouped and also afterwards, you could still say that single data points like those should be in one group. Then you can create a group out of it. So Berlin and Bremen is matching already. So this is done because it's not only the city but also the name of the state. Then for Dortmund, it is NRW Noren van. Then we have here Karzon Freiburg, which is the state, but then Wert timberg. Then you also have Hanover. Hanover here is Nia Sachen, then you have Leipzig, which is the group for Sachen, and then you have also Bavaria here for Munich. And as you can see, here it is working. We have created here our own groups, as you can see, but you could still remove here the filter on Germany and still position here in the country once again here in our rows, as you can see. And if we go to Germany, then you can see the overall group here with Germany, we can see the states and also the city. Also, then it's possible always to work with those groups. We can also remove here the country again in the filters and also put the filter once again here on Germany that overview is a bit easier to understand. Then you could also just say, here we want to group it again, and then you can just say, see here the total for our sums in each group. Also it's just possible to ungroup it again just by using the shortcut Shift Alt and left or also just pressing here this action field ungroup, and then we could also ungroup here our data again after we have selected it. 25. Duplicating Pivot Tables Efficiently: Let's speak about how we can also clone pivot tables because sometimes it makes sense that we don't create a pivot table on a new way. So here going to insert and then tables, pivot tables and select the entire database, data range here or going here and table design and summarize it here with the Pivot table. Now sometimes it's just also an advantage to say, we have an existing Pivot table like here, and then we just duplicate it here. It is possible just with a left mouse click here selecting the entire Pivot table, but you could also just press the shortcut Control and A, then everything is selected. And then with Control and C, we can copy it, and then we can click in a new elf or our target range, and then we can also just press Control and V, and then it's copied as you can see. What I did here is that I used the shortcut Control A, but with working with control and A, filters are ignored, as you can see, so you could also just make it bigger here by pressing Shift and arrow up. But you also instead of, you could also say pivot table analyze, and then also here by the actions, you could say select, here our entire pivot table, and also here it's possible to use the shortcut Control and C and control and V. And then as you can see, it's also copied here in this new area. You could also, instead of, you could also just select all the rows as you can see here from row three to 15. Then also yeah using Control and C and Control and V, if you have here a target row, where it should be positioned as you can see, but by the way, you can also instead of Control and C, you could also simply just work with copy or also on the home button. It's also possible here to work with copy. Or also, if you want to do it, you could also cut and then insert it here with Control and V or paste. Then you could also move a pivot table as you can see. This does not only work with rows, which we can select here. By the way, you can also use the shortcut Shift and space, and then here with the shift key, you could also work with that. No, you could also make it in the same way with columns here with the shortcut control and space, selecting all the columns or just with the left mouse click here on the columns, control and C or here copy. And then also you could insert it here on the right. This is basically here all the options, how you could clone pivot tables. 26. Actions: Clear, Select, and Move Items: In this video, we're going to speak about actions in terms of clear, select and move our pivot reports. So you can just go here easily on Pivot table analyze. And as you can see here on actions, you have the option here to select a pivot table. Also, it's possible, of course, we have to shortcut Control A, as I've mentioned before already. But if you really want to select the entire pivot table, then it's also here recommended to click on entire Pivot Table, and as you can see, everything is selected. After a selection, you could also say, here you want to work with labels and values or values and labels. As you can see, then you have selected only the labels or only here the values, labels and values, or you could also say here you want to enable the current selections here. Then it's also possible here to click on clear because sometimes it's interesting to clear all filters, especially when it comes to many filters. So for instance, we can position here many filters in our report, also the product, for instance, we can position here. And then let's say we have fear a selection like that, which is filtering our data, so we could also just simply remove some of our values here, and then we have fear such a selection, as you can see, and then it's possible here to click on actions, and then we could say we want to clear all filters. Also possible is also here to click on clear A. And then what happens is that our pivot table like this with all the numbers which you can see here is getting into a raw pivot table again. So for instance, if we click on our database here, then we can also say here table design and summarize with Pivot table in a new working sheet. So also here you can select where you want to position the new pivot table, and as you can see, this is how an empty pivot table looks like. Also, if you have a current aggregation here or a pivotable report, then you could also say here, clear and clear all, and then you could clear the entire pivotable, and then it looks like that again, but you can also undo it here with Control and set. And as you can see, this is pretty similar like here, this empty pivotable report, as you can see here. Going back here in pivot table, analyzing this ribbon, we can also click on Move pivot table here in our actions. So sometimes we also want to position it in a new sheet. Of course, we can select here our columns, and then we could just copy it or better. We could also just cut it with Control and X, and then we could go here in a new sheet and just position it here with Control and V or just paste. Then it's also here possible to delete pivot tables just by selecting everything. Also here, you can work with actions, select here the entire pivot table, and then you can simply just press the delete button, or you could also say, here, I'm selecting our columns and then pressing Control and minus, then it's also here removed. Then it's also possible here with pivot table analyze that we go on our actions and click here on Move pivot table. Once we click here a new worksheet, then it's possible here to position this pivot table in a new sheet or also possible here with actions and move pivot table, saying here this should be positioned in an existing working sheet. Then we just say here our range or here our cell as a target like before, press Okay and then it's also here repositioned. 27. Refreshing and Updating Data Sources: Refresh our data in a simple way and also how we can change our initial data source. So this is a pivot report which I've created already, and let's say here a number will change here in Business area one. Let's say here in China, then we can go back here in our database, and we can just remove the filters here with the data slicers. I can select China and Business area one. Let's say here we have here revenue and manipulated that it's not 1832, but it's 10 billion, for instance, as you can see here. And then once we go back here in our pivot table, it's not refreshed automatically. This is why we need to make a right click here and say refresh. Then you can see it is refresh and we can see here in your number, or also it's possible here with going here in pivotable analyze and also here you can just simply refresh the data. It's also possible that you can refresh all. So all sources here in the workbook and also it's possible here with the shortcut control D and F five. So let's also discuss now how we can simply change the data source. Our advantage here with our pivot report and also with the table, which we've prepared here as national databases that it's formatted as a dynamic table. We've discussed this so much here in the beginning of the course, and this makes our work with pivot tables just easier by clicking here on data source change. Then you can see here that our table is selected, another range. He just pointing out the difference again, a range would be here also with cells, as you can see here from A 132, for instance, until F 457. Yeah, and then it's just easier if we say this is the entire database by pressing Control and A, and then the entire database here is selected. And the good thing is always if we refresh something here in our dynamic table, then we can also have here a change in our pivot table very quick. Yeah, those are the options how you can refresh data and also change the data source. 28. Drill-Through Techniques: Let's speak about a very important shortcut which you can always use for your data analysis. We talk about drill throughs. But before we even going to start speaking about drill throughs, let's rename here our worksheet and just name it pivot. And then we can also just delete the other worksheet which we don't need anymore. So with a right click, you can say here for a single cell that you want to show details how this sum is calculated or out of which single values it's existing. Because when it comes to our database, we've seen also that we can use those slicers. For instance, here, a slicer for the year for 2025 for the US and here for business area four, for instance, and three, then we can see here a total volume and a total revenue those numbers. So let's say here, we're also going to calculate here a subtotal here for a sum, and then we can say this is the entire range, and we can see here a volume of around 6,020 7,000 here for the revenue. And we do not only see here those sums, but we can also see here the single values, as you can see here. And this we can also use here with our pivot table. So let's say we have here similar figures, but we want to see here the single values. So you can just make a right click and say here show details, click on it. And as you can see, we generate the entire report with the single values. So what we also could do instead of clicking here with a right click on it, show details, we also simply can just make a double click. For instance, here for Business area four, in the UK, just make a double click, and then we also generate such a trill through view. And the advantage of it is that we really generate a new table, and it's also generated as a table already so that you can build on it and also create a Pivot table again based on this table. This is just a sub table then of our initial database table here. So this is also an advantage that you can make such deep dive analysis. 29. Managing Field Lists Effectively: Let's talk about more options with Pivot tables, also about field list. So when clicking here in our Pivot table, we do not only see here our Pivot table fields. We also have here our ribbon pivot table analyze, and also here we have our show action field. Here, we can also simply say we want to show our fields or hide it, which is also possible, of course, just by saying here with a right click Show field. We can also make an analysis with our revenue, which we can position here in the values. A can here format our data with Control Shift and one and also say we want to have fewer decimal places. And then it's also possible to illustrate it here on business area level. And interesting is here also when it comes to pivotable analyze show that you can say here next to showing the field list, that you can also say here, you want to show the plus or the minus buttons, show it or hide it. What do you mean with it? Well, we can just position the business area in the columns. And then we can also position the country here once again in our rows, as well as with the city, which we can place here as well. Then here it's possible to say plus and minus buttons. So with plus, of course, then we can work with such levels that we can say, this is the top level, China revenue in all the business areas. And with this plus, I can show it also here in city level. This is possible, of course, and also when it comes to pivot charts, which we're going to learn about layer, then we can also work here with this plus and minus buttons. Then also field headers, you can also simply just show or hide. This is also important here when it comes to our work with Pivot tables that we also work here with this show button. 30. Module 5: Layout, Design and Styling: Make your Pivot Tables also look great. Module Pifer will show you how to apply styles, how to adjust layouts, and how to format your data for printing. Good designs, make your data really stand out, so let's get started. 31. Recommended Pivot Table Layouts: Welcome back to this video, where are we going to talk here about recommended pivot tables. This is already a topic when it comes to the creation here of a pivot table because we have learned we have many options how to create the Pivot table. First of all, we can have here such a database, which is a dynamic table, and then we can just easily click here on Table Design and summarize it here with a Pivot table, as you can see. But you could also say here, insert and then tables. And next to Pivot table are also here the recommended pivotables as you can see here. This click button you can also find here when you have graded such a pivot table already here, pivot table analyze, also here recommended pivot tables. Interesting is here that Excel is also recommending us pivot ables, which could make sense, for instance, here to illustrate the revenue on customer level or also the sum of our volume on customer level, and this is something I want to have a look at now. Let's press here, and then we can see here that such a pivot table is graded, or let's say before we already had such a pivot table, which we created on our own before. So now it's just changed, which you can also do here with recommended pivot tables. But you could also say here, you want to insert a new one, pivot table, recommended, here, and then, for instance, I also want to have a look at the volume by customer level, and then I can also place it here in your sheet. Yeah, then I can also format it here once again, just pressing here, common style or also using a shortcut Control Shift and one, and just reducing here the decimal places. Yeah, as you can see, this is now recommendation here of Excel. So we could sort it here with the right click here, largest to smallest. And then it's interesting here to analyze the volume really on customer level and to find out with which customer, we have the highest volume or also in this example here, the highest revenue which customer also sort it here, largest to smallest, and then you can also analyze it here. Sometimes it also can just make sense here to work with that feature. Yeah, recommended pivot tables. Of course, you have to select the initial table. Only then it's working, and then Excel can just recommend a pivot table, which summarize our complex data, and it's very simple. So we just have to click the button to get a customized set of pivot ables. 32. Style Options for Pivot Tables: In this section, we are going to start to speak about all the layout options which we have with our pivot tables. Let's go here into the ribbon and design in our menu bar, and then we have here this group pivot table style options. And as you can see here, we have many jack booxesF instance, column headers, where we can say if we want to have column headers here in our pivot table or not. Also interesting is here the banded rows, especially interesting once we have bigger pivot tables. So let's show also the field list, and then we can say here that we want to extend it, also make it bigger our analysis, maybe here also on city level. And then also the overview can be greater if we also have this style here so that every second row is banded here, but it's also going to work here with columns, for instance, as you can see here, banded columns. Also interesting is that it has an influence on all the pivot table styles which you can see here, either with banded rows or with banded columns, and how it works here with all those pivot styles here, with those layouts is something which we want to discuss in the next lecture. 33. Pivot Table Styles Final: So let's discuss also the pivotable styles. As I've mentioned also in the previous video, we have here the option that we can click on Pivot Table styles here before we want to remove here the banded columns. And as you can see here, we have many, many templates for our layouts, which we can use here. There is a category, light, but also medium and dark. You can also just clear existing Pivot table styles, as you can see, but I wouldn't recommend, especially if you go here and view in this menu bar, then you could also say here and show grid lines that you don't want to show the grid lines. And then it's not so visible here that this is Pivot Table, also related to the environment here out of the Pivot Table. So I definitely would recommend here to choose a color. Um, for instance, we can just have a look at here the light, column styles like white pivot style or also using some colors like here, light blue. This is the standard one, just a light blue here, but this is also existing here in different colors, such as a light gray, for instance, or here in orange or also in yellow in this light gray or here also in this light green, as you can see. Then we also have those medium ones where it's also a combination here with plaque backgrounds, especially here for the totals and also for the columns. But also if we go down here for the gran total overall, and this you could also combine here with different colors so that especially here, the top and the bottom is black with a white font, and here the middle is either a light gray, a light blue, green or also yellow. Then also, of course, it's existing in a dark theme that you use, for instance, your dark blue Pivot style, and also you have numbers. This is, for instance, your gray Pivot style, dark 15, and then you can choose some of them. It's also a personal stuff what you like the best and so it's up to you which styles you want to use for your analysis. Then you can also click here on new Pivot Table style. Here you can also configure it on your own that you just say how you want to have it here with the subtotals and everything, and then you can here format it like you want. Those are pretty much the options, what you have if you work here with the Pivot Table styles. 34. Layout Configuration for Better Readability: Let's continue now with our layout. The last election, we've already used different pivot table styles, as you can see here for our grand totals here in a black background with a white font and also here our value area here with a light blue. But we could also manage here the layout, how our Pivot Table looks like. And this is very easy just by clicking here on design. Then we can use here all the functions which we have for the layout, such as the subtotals, grand totals, report layout, and also the blank rows. Let's commence here with the cran totals. Here we can just show or hide our grand totals as you can see. For instance, we can just say here off for rows as well as for columns, and then it's completely off as you can see, we only see here our values and no grand totals anymore. But we could also say both should be on or either we say this is on for rows only, or we say this is only on for columns. But in my case, I just want to show both. This is why I'm going to press here show on for rows and columns. So next we're going to continue with the subtotals. Then we could also say here that we want to show subtotals or do not show subtotals. So let's work here with our field list just by adding here the city as well on the rows. And then it's interesting to say here that we want to see our subtotals by clicking here on show all the subtotals at the bottom, how it is currently, as you can see, but we could also say show all the subtotals here at the top. And then also interesting would be here to say that we don't want to show our subtotals, that we just want to see here the values for the cities, but for the countries not showing any subtotals. Yeah, I'm going to say here, I want to illustrate it here on the top. And this is also interesting here in combination with the plank rows because here you can also say, I want to remove here the plankline after each item because here we usually have such a plankline and here by clicking on Insert Blank line after each item, you could also add it again. Then let's come to the last one, which is a report layout. Here you can simply say that we want to show it here in this form or also here in a tabular form. Then it looks like that or also in a compact form, as you can see here that we have here the highest level, which is just the country. I make a double click here on the column. And then below we see here all the cities, so we can see the revenue on city level. Yeah, interesting is also the outline form, which is the standard that we have here split into two columns here, the first level is country and the second level is here a city. But we could also say, as illustrated already here, that we want to show it in such a compact form, then it looks like that. When it comes to here to the outline form, then we can also use here those two functions, repeat all item labels. Then we can see here that the country label is always repeated here, but we could also say, we do not want to repeat all the item labels like here. As you can see, you have many, many options, how you format your Pivot Tables, a lot of options regarding the layout. 35. Printing Pivot Tables Professionally: Sometimes it's also interesting to not only have our results from a Pivot Table here in our worksheet, but also having it physically on a paper. This is why we want to discuss here how we can print our Pivot Table. And here we have many options. So the first option, how to print it here is, of course, clicking on file, but you could also just use the searching here also with the shortcut Al and Q and just type print. And then there's also the print button, but you could also just use the shortcut Control and P. As you can see here with Control and P, I can print here the entire Pivot Table. And also interesting is here going into the options, Pivot Table analyze and here Pivot Table options. Here you can also say here by printing that you either want to print expand collapse buttons when displayed on a Pivot Table or not or also say that you want to repeat row labels on each printed page and I also can here set the print titles. I want to set the print titles, and also interesting would be here to find out how we could only select, for instance, here, the result from the Pivot Table relevant for China. Here we can also press Control and P. And as you can see here, we still see the entire Pivot Table. This is because here in the settings, it's still selected, the active worksheet. We could also say here just simply the selection of it, and then as you can see here, this is the current selection for a country. Also would work here for the next country like for Mexico, controlling P and as you can see. Also here only Mexico is selected. And then here by pressing print, you can bring it to the paper. Here you can also select your printer. Then you can also configurate the format, whether it's a four or a three. You could also scale it here with scaling without scaling and also the margins, you can configure it here. So this is how it works, very simple with the short cut control and P, and then you can bring your results also on a paper. 36. Module 6: Conditional Formatting: In Module six, we will cover conditional formatting. Highlight your key data with color, so I will show you how to implement rules and advanced options to visualize key trends in your data. Let's get started. 37. Basics of Conditional Formatting in Pivot Tables: So let's now speak about conditional formatting in Pivot Tables. Well, first of all, we have to ask a question what is conditional formatting. Conditional formatting, we can also use just for ranges in Excel, and it's just a method which we can apply to change the appearance of cells in range based on our specific conditions. Often those conditions are rule based based on specific numerical values or also categories, and we can change the appearance and can change it on an own way with different colors or also data bars and so on. And this is something we can find here in the ribbon home, conditional formatting, as you can see here. And as I've mentioned, it also just simply works for data ranges. So with Control N, I can just create here a new workbook here in Excel, and then I can just say conditional formatting. So not even working with a Pivot Table, and then you can just use, for instance, the color scales. Here, it's green, yellow, red color scale, and then you can see here how it's filled with all the colors. As you can see here, all the values which are zero are here highlighted as a red color and then near the highest value in a green, then it's just easier to identify outliers or also to see which revenues are the highest. This also simply works for Pivot Tables with the same procedure, clicking here on conditional formatting, color scales, and also here we can just apply such a green, yellow, red color scale. We can just apply it, this color gradient to a range of our cells, which you can see here, and then the color also indicates where each cell value falls within that range. And then, of course, you could also just select those cells as I've done it here. And then we can also use the forum painter and apply it as well here for the next country. But we can also make a double click. Then it's also possible here for the next ones here, just with one click, very simple. This does not only work for an entire block. We can also just see it like column wise or row wise, what you can also implement next to the colors which we've used here with the color scales. It's also data bars and icon sets. But before we go to speak about that, I just want to point out more also what kind of color scales we have. This is one of the easiest color scales, just a green, yellow or red color scale. But please make sure that it also matches to our key figure which we have here in the values. So for instance, in our case, we have revenue, so it means the higher the value more in the green area it should be or the more with a green color it should be colored. But the opposite would be, for instance, if we would have here, also the costs in our Pivot board, then we rather should choose here such a red, yellow, green color scale. Yeah, then it's also possible to work here with a green, white, red color scale or also here with a red, white, green color scale. And then also interesting here, not using the green color but using the blue color, for instance, and also interesting and also by the way, what I like the most is not the color scale. It's the data bars. Because of the data bar appearance, we can represent the value here also in a cell by using such a data bars. So it just works like that. The higher the value the longer the bar is. And then we can see just very easy and very quick that, for instance, here, the revenue when it comes to this column here in our business area four is here, the highest in this city. And yeah, you can also simply change it here. You can also click here in the Format Objens that you can also say that all sales are showing the sum of the revenue or here for city and business area, you can also format rules, but this is something we're going to have a look on later. Yeah, you can also say here with the data bars that you want to use different colors, for instance, or also solid fill. Then also interesting, what we're going to have a look at with the next block here for the country Korea is when it comes to the conditional formatting, also here the icon sets because you can also say it should be directional or also with certain shapes or also here interesting indicators. I would rather use those here for deviations, so this is just here um visible on one view, which figures are deviating too much or have a huge difference or others which are okay. You can highlight pretty well here with the icon sets, but when it comes to here, so I can also apply it here, for instance, with those with those shapes. But when it comes to here, like absolute values such as revenue or also earnings, then I would rather suggest to use here the data bars because it's pretty easy and simple to understand and very understandable, and you can see it on one view only where you have the highest revenue. 38. Rule-Based Formatting: What we can also do is implementing our own rules for conditional formatting. How does it work? Well, we can click here on conditional formatting. And then, for instance, we can click here on highlight cell rules. We can just simply say we want to highlight our values which are greater than a specific value. For instance, I can just enter 15,000, and then you can just click on a green filling, for instance, all cells which are greater than this value are here format with such a green filling. Click on Okay, but it also simply works with saying that all the values which are less than a specific value, for instance, here, 15,000 or also we could say here 10,000 and we can also customize it here by clicking on custom format. Then we can also give a filling. For instance, here, such a dark red style, then it's filled like that. But you could also say here in custom format with the font that the color should not be black, it should be white, for instance. Um, also confirm it like that, and then you can say it's also fill like this. Then also possible is here between. So what is left now for sure, is here between 10,000 and then here 15,000, the missing values. And those we can just format here in yellow, but also we could say custom format and also say here, the font should be here gray or black. And then with the filling, we can say here such a light orange or yellow, and then it's here formatted like that. Just confirm it here with okay. And then we can also go here in manage rules. And as you can see, we have implemented three rules here, but also the other rules are still in there, so the CN sets, the data bars, the created color scales, all of them are actually conditional formatting rules, and those we can see here in the formatting rules manager. Then we can also say we want to delete certain rules just here by clicking on Delete, and then we can delete it. But I don't want to confirm it here. And also, you could say we want to clear existing rules like this in the selected cells. Then we don't have to delete all of those three separately. No, we can also just go here on clear rule from selected cells or we could also say in the entire worksheet, then everything is selected. But I can also undo it here just with Control Set, as you can see. As you can see, we can highlight cell rules here with this, and also we can clear them and also manage them. But there still many options what we can do here with it, and this is something we're going to look in the next video. 39. Advanced Conditional Formatting Rules: So let's continue now with more advanced rules, which we can use here for our conditional formatting work with Pivot reports. So first of all, we would like to create a new Pivot report, Pivot Table. We can just simply click on our database here and also go here on either Insert and Pivot Table or also possible here with table design and summarize with Pivot Table. What you could also just do is here, create a new worksheet by simply clicking here plus or also using your shortcut Shift and F 11, and then we also have a new sheet here. We can also name it here Pivot two. Or also what would have been possible is just a right click here saying we want to move or copy it, create a copy, and move it here to the end or in between. This also would be possible because then we also would have here a copy of this existing Pivot table. It's also possible here by simply just selecting it here with the columns and pressing Control and C and inserting here with those columns like that. Or also what you have learned already is that you could also just select the entire Pivot Table here by the actions with select and then saying entire Pivot Table. Yeah, then we can also copy it here with Control and C and then insert it here with Control and V, and then we can select all the columns, saying conditional formatting, clear rules, and clear all the rules from our selected cells as you can see. We can just say here that we want to remove the country level, just remove it here, and then we have all our cities which you can see here. And for instance, I want to select here this entire column or all the values without the grand total for sure. I want to see the top ten values. And this is possible here by clicking on Home, conditional formatting and then saying here, top and bottom rules, I can either format here the top ten items or also the top 10% of our cities here when it comes to the revenue. Say here top ten items, and then I can also say here, I do not want to have the top ten, but maybe the top eight, but I'm going to say here in the top ten. And for sure, when it comes to the revenue, I don't want to highlight it in a red color, but in a green color, I can also say here custom format and also choose my own green color like this one, which I like here the most, and then I can just enter it with okay. Also interesting is just to sort it with a right click saying sort here, larger to the smallest as you can see, and then we see it on Brown view that those cities generate here the most revenue in business sector one. Interesting is also just to select here this existing style also with this formatting, going here on format painter, and then also move it here to the right where you can also see that it was successful, that you can see here all the cities in the other business sector also here formatted like that. So let's continue. We could also apply it for sure for the other columns just by saying here with our painter format painter that we want to apply it here as well, by a double click, you can also have it selected always and also putting here on all the columns which you can see here. Then, of course, it also works like that that we can say here the bottom rules. So we could also say the bottom ten items should be highlighted here in a red fill, also the top ten, for instance. And also interesting, besides the top ten or bottom ten items, you could also use it here for the top or bottom 10%, not the items, but percent value. Interesting would also be here to highlight the ones which are above or below the average. We could also simply just clear the rules not here in the entire sheet but then selected cells here, and then you could also say here above average should be highlighted here in yellow fill like that, which are above and those which are below the average. So the arithmetic mean here for the revenue in those cities for Business sector five should be highlighted differently for instance, yeah. Yeah, and what else can we do? We could also say here, highlight rules based on the text. So for instance, we highlight here in column B, all the cities, which commence here with the letter D, for instance, the, and then we highlighted here, maybe like that that we have here such a gray filling or let's say here this light blue. And then it gets highlighted like that, as you can see. And whenever you implement such a rule, you can also always go back here and manage rules and also edit it afterwards here edit rule. And in this dialogue, you can also navigate here in formatting and also change the color still. I'm doing it like that, for instance, or also the fund you can manage and everything. Then also you can say a date occurring. This is also interesting because here you could say, of course, we must have a date, of course, as a field in our Pivot report, but you could also say the day today or also tomorrow or the entire past week or the next following week, and so should be highlighted. This is also interesting. Yeah, and also here duplicate values you can highlight as well. In the end, we also want to speak about the manager for our rules. Here we can create our rules also in this dialogue, but we can also edit it afterwards and also clear existing rules, delete them, and also have all of them here in the overview. And here I only see the existing rules here for column B. This is why we also can just simply select the entire PivDRport, manage rules, and then we can see all of them existing. You can also duplicate rules, by the way, but you could also remove them, for instance, here with this tax filter, I want to remove it, and then here as you can see, it's removed. 40. Module 7: Pivot Charts and Data Visualizations: Turn your data into real charts. In this section, I will show you how to visualize your data with pivot charts. You will learn the basic chart types such as spar, line or Pie charts. Let's go. 41. Pivot Charts vs Standard Excel Charts: Now we're going to start using visualizations for our Pivot reports. This is very simple. We just can go here on Pivot Table Analyze, and then we can use here the Pivot Jarts. As you can see, a new dialogue pops up, and here we have many, many charts which we can use here for our data. For instance, we can use column jarts, line jars, but also Pi and bar Jart and many more. So first of all, I would like to point out here the difference between Excel charts and also pivot charts. So what do I mean with it? First of all, I can just select here my Pivot Table, and then I can also, yeah, paste it here with some values here, as you can see. And also, based on that, I can create a visualization here in Excel. I can also just simply remove here my grand totals. I don't need them anymore, and then I can also just select the data like here. Excel charts, I can simply create here by clicking on Insert and then going here to charts. For instance, I can choose such a two d column jar, but also a three D column jar or also many other visualization types which you can see here. This is the first option which I have in order to create such an Excel jar. Second option is that we just press Control Q as you can see here. So with this, we can just quickly jump into the quick analysis. There we cannot only create here different formattings but also totals tables, but we can also create charts. For instance, here, clustered column jars, such as this one here. And now we want to compare it also here to our Pivot Charts. We can just simply click on Pivot Table Analyze, and then we can click on here, Pivot Charts. And as you can see, I can also select such a clustered column Jart. So what is now the difference between those two charts, which you can see here? The chart at the top is such a pivot chart based on a Pivot Table. And we can also highlight it here by such a chart title, where we can say, this is a pivot, a chart. And this chart below is a normal Excel chart. This is why I just call it, yeah, Excel chart, for instance. And as you can see, this is very similar. If we have a look at here the charts, how they are built, and also here our axis, the Y and X axis. They are very similar. For instance, we have here the legend on the right, while we have the legend here in the Excel jar at the bottom. But we could also change it here because we have here or legend as you can see, and we can also say, it should be placed here on the right. Now it looks pretty similar, as you can see. One difference which catch our eye here is maybe that we have here those buttons, and with those buttons, we can also filter. We also have an interactive filtering option here in our chart, so those Pivot jars built in. So we can also say we want to remove China, and as a result, also the values are updated now. Or also we can just simply remove a business area, for instance, removing business area one, and as you can see, it's also updated here automatically in our jar. Is the sum of the revenue, but we could also change here this figure. Then it's also here possible to remove those fields just by clicking with a right click here in the diagram in the chart. And also, we can here simply say, we want to hide all field buttons on the chart, and then it really looks like such an Excel jar, as you can see. But I undo it here with Control set, and one important difference here between Pivot Charts and Excel charts is just that this pivot chart is always linked to our initial Pivot Table, while this Excel Jart, as you can see here is still related here to the cell range. The difference between the cell range and this Pivot table is, of course, that the Pivot Table is dynamic, so we can also place it here, for instance, this range. Then we have more space for our Pivot Table and we can also show the field list. Then of course, we can make different stuff. We can also here say, we want to add a year in addition here, then it gets bigger and also our pivot chart, of course, is updated here, but you could also say you show the field list again and pull the years now in the rows and also the chart would be updated then. This is definitely an advantage here when it comes to the Pivot Charts. But one advantage which we still have with the Excel chart is that we have much more diachram types, which you can see here, right click, change char type, and then you can also see here that, for instance, we can also use a TreeMap, sunburst, but also a histogram and a box blot, for instance, while when we go to the Pivot chart, right click and also say here, change char type, then you can see here some of those diagram um, are not available for a Pivot Table, so we cannot create this chart type with a data inside, based on the Pivot Table. So, Excel recommends us here if we want to continue working with such a Pivot Table that we rather select a different chart type. Or we could also just simply copy the data outside the Pivot Table. This is always an option which you have, like I did here, just selecting it, and then are pasting here the data outside the Pivot Table, and then you can also use those chart types which are not existing for Pivot Charts. To conclude here the learnings from this video, I would definitely say that it has a big advantage here to work with those pivot charts because it's always based on such a Pivot table, and also the chart is updated automatically. Furthermore, it also will help us by creating dashboards with interactive filter Slicers, but this is also something which we will see later. 42. Column and Bar Charts: So next, let's move on with column and bar charts. We can simply create those Pivot Charts as we have seen in the last lecture already with clicking here on Pivot Table Analyze, and then you can go here in the tools Pivot chart. Then a new dialogue pops up like this, and then you have all the charts here in the overview, which you can use here for getting insights into our data. But also possible would be here to work with insert, and then you could also simply click here on Pivot chart, or also shortcut is using a right click and then saying here, add the Pivot chart here to the Quick Access Toolbar. And then we also have our Pivot chart here at the top and the Quick Access Toolbar, and we can also simply just click on here. And then we have different column charts as you can see. Instance, we have here the clustered column Jart. So we have different groups, as you can see here countries like China, Germany, Mexico, South Korea, UK, and the US. So this would be a chart where we have clustered on our X axis. But we could also visualize it here with a stacked column jar and it looks like that. Or also possible would be here such a 100% stack column jar. So let's simply click on here. I'm using a stacked column jar. Then it looks like that and we can also place it here on the right. By the way, there's another shortcut you can simply use. Also by position it easier this diachram also along the grid lines, it's just simply pressing here the ld key, pressing the old key, and then you can move it here to the right. And as you can see, also, it's positioned here quite well along the grid lines. You can also track it down here, position it like that or more to the right here, for instance, like that by pressing the old key and then it's here perfectly along the grid lines. This is such a column jar, and then we could also hear duplicate it just simply by pressing Control C and Control W. Or another option would be also just clicking on the jar and pressing the Control button and just tracking it here. And then as you see, it's also here has been copied. Then we can also with a right click jump into your chart type, change it here very easy easily, and then you could also instead of column charts, also use bar charts. For instance, here is such a clustered bar chart like that, then it's here position it like that here that we don't have it in such a vertical way, but in horizontal way, as you can see, we have many, many options, also how to format here, our jarts, but this is something we're going to have a look at it later. Make another right click here in order to change the chart type again because we could also say here, we want to have a stacked bar chart, then it looks like that. But we could also say here, select chart type. Here we want to have a 100% stacked bar chart, and we could also work here with the data labels, which we can add here with a right click, and then you can see here all the data labels like that, also like that, and like that and also here for Business area one. Then you can simply see here also the percentages, the shares of each business area, and also here the absolute revenue figures, but also possible would be here, change the chart type here to a three D cluster bar jard stacked and also with 100% views, possible as well. Yeah, this is also here for the column charts, of course. So if we want to show categories here with a numerical value, such as with the revenue, then of course, it has many advantages to work either with a column chart or with a bar chart. 43. Line and Area Charts: Ine charts and area charts are quite suitable for displaying data over time. What you can see here is the revenue on country level, and I would like to visualize it in such a line chart or also pivot area chart. In order to do that, we also have to addhe in our field list, the date. We can just pull it here in the rows, as you can see. Also we have a certain hierarchy, so that means I can also say, I want to display it here on a quarter level, but also here on year level is the higher level, but also we're able to go down here even on monthly level, and this is also something which will change in here, as you can see in our rows in terms of the Pivot Table fields. Now we can create such a Pivot chart out of it just by clicking here on Pivot Table Analyze, then Pivot Charts you can see, and then I can simply move here to the line chart. I have different line charts. I have the simple one. I have the stacked line here. Since I have a legend, I'm consisting of different countries, as you can see. But I can also click here online with Marks, for instance, but also here stack line with markers or also 100% stack line with markers. Also interesting maybe here the three D line chart. Let's click on here, the line with markers chart. I click on Okay. And as you can see here, where here the data over time because here we can see the quarters and also the years. We can also move it to the right again by pressing also Alt then it's also here along the grid lines where we can position it. And interesting is also that if we go on a deeper level, then it's also visible here in our chart. Also interesting would be just click on plus or here on minus, then we can also reduce it or also expand here the entire field, as you can see. So let's even go on the lowest level, which would be here on a monthly level, but we still can see here each quarter, and we can also see here the year level. And then we can see here also our data label here with colors and also the legend. So also interesting is that it's here still interactive with our Pivot chart, so we can also remove certain countries and here the green line for the US revenue got removed, as you can see. Then we can also duplicate it again by pressing Control C or just pressing the Control button and move it here to the right. Then also pressing all again, then it's here along the grid lines. And with the right click, I can also here go into change char type, also move here to a Nia jar. And also here, we have either a Nariajar, the simple one or also a stacked area chart. Of course, there are many more like a 100% stacked area chart. This is very comparable also to the 100% stacked. Column or bar chart, and also we have here the three D variants. So let's choose here this simple stacked area chart. And as you can see, also, I can see here the data over time 2025-2027 in all the quarters I have here for the revenue development, here for the countries. And especially when it comes to the data over time, I would recommend you to use here either a line chart or such a area chart. 44. Pie Charts and TreeMap Tips: In order to visualize the proportions of a category, pie charts and also TreeMap are a good option. So let's first of all, start here with a circle chart or also called Pi chart visualization. We can simply go here on Pivot Table Analyze and go here to PVA chart. And then we can move on here Pie charts. And here we have many options we have here the two D or also the three D Pie chart, but also Pie charts which are consisting out of a different Pie chart, as you can see, or just a doughnut. Let's start here with such a pie chart. And here, I would like to visualize the proportions here of the business sectors of this company regarding the revenue. Also here, we can just add data labels, as you can see here with a right click, and then I can say, I would like to show the value, but also the percentage. And so this is possible, and then we can see it here as a result. Of course, we can also change it here, change the chart type to a ring chart, for instance, and it looks like that. But here, it's very important. I would like to point out here that it's not possible, as I've mentioned also before to use certain charts like a TreeMap chart, for instance. But such a TreeMap chart is actually a very good option in order to visualize the proportions of such a category, especially when we have more. So here we can also simply select our Pivot Table and also paste it here. And then we can also make a right click for our field list, or let's say here. We want to remove the format shape field and then we can also remove here the business area and we can also add here, for instance, the city. Then we can also sort our data with a right click and say largest to the smallest. And yeah, when we go here to the Pivot Table Analyze, I'm going here, Pivot Charts, then everything here in one chart is not so clear anymore. So now it's very hard to see all the proportions of those cities. This is why I would rather recommend you use TreeMap, therefore, I would just copy it. And then we can also paste it here just as a data range. And as you've learned already, you can press Control Q in order to create a new Excel chart, or you could also go here on insert and chart, and then you can also click here on TreeMap. And as you can see, then it's just a better option for many, many categories, for many attributes to use rather such a TreeMap. Then we can remove the title. We can also remove the legend because we anyways have here our data label and as you can see, I can make it here a bit bigger. It's visible very well, and also we can make a right click here and say we would like to format our data labels, and besides the category name, we can also just simply add here our values. So I would highly recommend if you have more attributes when it comes to category, to use such a TreeMap. But the only big disadvantage here of this TreeMap when it comes to Pivot Charts is, of course, that it's not linked anymore to the initial Pivot Table, especially when we have changes here in our database, for instance, then of course, we would also have the changes here in the Pivot Table, but it still requires that we need to make a copy here in our data range in order to be able to create such a TreeMap. 45. Radar Chart Techniques: Another chart which is available here for our Pivot Tables is the so called radar chart. So how does it work? Well, we can go here on Pivot Table Analyze, Pivot chart, and then we can select here the radar chart. This is possible here, and as you can see, it makes sense here with different categories. For instance, here with our business sector. So we have in total five business sectors, as you can see, I can position here the visualization on the bottom and also make it a bit bigger. I can also press the key again. This is here our chart, as you can see here with different categories. So those are the business areas, and also we can see our countries. So with different colors, we can see here the country, China, but also Germany, Mexico, and also we can see here the difference between those data labels. For instance, it would be also interesting to change it here by showing the field list. We could also say, here we want to instead of the business areas, we would like to show the year, for instance, then it looks like that. Or we could also try it here with the date, pull it here, and also we have here the option that we can press the plus here, as you can see. And then also we have the analysis here even on quarter level, so we can see here the year, but also the quarters, so 2025 quarter one, quarter two in the same year, and so on. And then we can see here our data labels. We can compare here the colors, and then you can see how much revenue we achieve here over the time. Let's go back here in our chart type because we can also change it here to visualization here, which is still such a radar chart, but also with those markers, as you can see. And also possible when we go back here is also here a filled Rutter chart. But here, it's quite important also how we make it here with the data labels so that especially those who are the biggest like South Korea in our example, that they are more in the background that we can also see the other countries. 46. Choosing the Right Chart Type: In this short video, I'm going to show you all the pivot charts which are existing with Pivot tables. So we've learned already that there are different types of pivot charts, and we can subdivide it here into four groups, the categorical jars, but also the proportional jarts over time charts, and also expanded charts. When it comes to categorical charts, we could either create such a column jar or a bar jar. And there we also have a variety of different column jarts. When it comes to the column charts, we can either use a clustered column jar, but also a stack column jar or a 100% filled column jar. This is possible with the two D view or the two D diagram, as well as with the three D diagram. Same also for the bar charts, also clustered, stacked and the 100% filled one available. When it comes to proportional charts, then there's the pie chart. We also have a variety here. We could either use the pie chart or the ring chart. You could also grade a pie chart, which consists of another pie, so pie of pie chart, but also bar of pie chart is possible, as well. Then also we have overtime charts, such as the line chart, and also here, we can use different legends, so different data labels with different colors, for instance, in order to visualize, here the countries so the development from one year to another, and also comparing here different countries or different business areas with different lines. But also it's possible here filled with an area chart. And also here we have a certain variety here. So we could either use simple line chart, but also the stacked variant or also using markers. Also this is possible with the two D diachram as well as with the three D diagram, and also 100% filled is also possible. Yeah, the last chart I've also added here is the so called radar or radar chart. And here is also existing simple variant, but also the filled one or the three D variant of it. As you can see, those are different types of charts, and there's not the one chart, the one pivot chart, which is the best or has the most advantages. It's more like it depends on your data and what you want to visualize. 47. Design and Chart Configuration: We have already created such a simple pivot chart here, and now we're going to discuss what kind of options and layout and design configurate options we have. So this is possible here by clicking on the diagram, similar to the pivot chart options. It's also important here that we always click in the Pivot chart itself in order to be able to do forumating or change the settings of our Pivot Table. This is the same for the diagram also for the Pivot chart. So therefore, we click here on the chart, and then we can click here on design. Interesting is also here when it comes to the ribbons that not only the design and the format ribbon is available then, but also here the pivot chart analyzed ribbon is also for the Pivot Table itself is also available. Yeah, this is here, O Pivot chart, we can click on the design. And as you can see, we have many chart styles which we can adapt. For instance, we can also show the data labels here also vertically, but it's also possible here to use this chart here, style number three or number four, also with a gray background or also here Style five, for instance. There are many more, as you can see, also with a dark mode with a black background also. And as a result, also the data labels are in white, of course. Then also, we can change colors. So it's also interesting here to use different palettes. For instance, here, colorful palette number two, or also here number three or number four, as you can see. So there are many color palettes, as you can see, but also monochromatic ones and also here always here with one specific color, as you can see, here with different bright and dark colors. There are different themes, and also we have here the option to use quick layouts, as you can see. So here, for instance, this is something I would suggest you here. If you have more numbers in a jart, then you can also show a table below our columns here. And then here we can also see every single value. Then also interesting is here that you can add chart elements. So for instance, here, axis titles, but also chart title overall or formatting the data labels. This is not only possible here by clicking on Add hart element, as you can see here, but also it's possible here. Clicking on chart elements. And here we can also, yeah, just format our diagram here data labels or chart title, we can remove it as well. Also we can add the axis here, and we can also give it a name. For instance, we can just say here that this is the revenue, and also by pressing plus again, we can also show arrow bars. We could also use a legend, but we don't need it because we also have this table below already. Also, here, when it comes to the trend line, it's also not relevant here, more in line chart, for instance. And what we have here also, when we click on here, is also that we have many starts which we can still use with a dark background, for instance. Then this is still too small for us. We would like to see more also the columns. And therefore, it's here possible also to move our chart. Just click on here, move chart, then it's the same like if you would press here or use a right click by saying here move chart, and then you can also say you would like to move it in a different worksheet or a different object. I say here in a new worksheet, which will be created now, and then the advantages here that is just bigger, and then we can see it in a better way. Also is possible here is that we go back here and assign that we can switch column and rows, as you can see. Then we don't have here the countries anymore in our table, but we have here it on business sector level, but we could switch rows and the columns again, then we have it initially. Then also we could say select data. This is not so relevant when it comes to Pivot Charts because it's always still related to our initial Pivot Table, which you can see here. So therefore, I would say this function is rather interesting here also with the right click here, select data for normal Excel charts which are not referring to Pivot Tables. Then of course, what you've already got to known here with a Right click is that you can change the chart type, but it's also possible here by clicking on Jar type, and also here you can easily switch on a stacked column jar next to the clustered column jar, but also 100% stacked column jar, there are many jars, but also you could switch completely here from a column jar to a bar chart, a pie chart, and so on. Those are the most important features when it comes to design formatting. 48. Final Touches: Formatting and Polish: So now let's speak about formatting our Pivot Charts. I've created here such a Pivot Table already, as you can see, and then I can click here on Pivot chart. And as you can see, then I've created such a clustered column Jart and I can press Okay. And then we do not only have here this ribbon design, but we also have here the ribbon format. And here we have many options with what we can do here with our graphic. As you've learned already, we can just use a right click and say here that we want to move our chart. Um, you can, by the way, also say here by design, clicking on Move jar that we want to move it here in a new sheet, what we create here. Then we can click here on format, and as you can see, we can also insert certain shapes. Also with insert, of course, it's possible here to insert illustrations like shapes, for instance, but we could also use here our ribbon format in order to do that. Could also here just say we want to insert such a simple rectangle, and then we can position the tier for instance. And then we can also say this should have no filling, here the outline. For instance, such a red frame or the frame should be in such a dark blue color. And then it looks like that. Um, we could also say it should be a bit thicker by clicking again on outline weight and then increase it also here to three point. Then you could also just press the Control key and press it here, drag it here to the right. This is also possible here. Then we've duplicated here this frame, and the same we can also do here for business area four and five. And then, as you can see, we have added here all those frames. Of course, you could also do it here with many other shapes which you can see here. You could also just change the shape. Once you've selected such a shape, then you can also change it here. But also possible is here to work with the ribbon shape format. Then you can also change the styles here. For instance, this style or also just the outline only. Then you can also change the color or also the effect. So you also have a shadow here, for instance, which you can also add here as a style. And also the word art styles and the arrange you can manage. This is pretty similar also when clicking here on the graphic here format, then you also can see exactly this that you can also change here the shape style, for instance, here with such an orange frame as outline, but also many other colors are possible here green as well. But you could also say here, the theme color should be changed for instance here have such a light gray, and then you can see we also adapted here in the background. It's also possible here to change the color with this setting here in regards to the frame, but also here the shadow you could manage, for instance, here, offset bottom or also many others or possible soft edges or a bevel. Then also interesting is here the quick styles when it comes to the word art. Arts. Then we can also manage how it looks like maybe here. This one, this is here black tax color, and then you can see that everything changes here in this graphic, but you could also manage how it looks like with the fund. I definitely would suggest if you choose a dark background, then of course, the font should be bright. So with brighter colors. Then also interesting, we could just copy here our chart and with Control V, we can also insert it here and pressing the Control key and dragging it here in the right. Then we have duplicated it, and then it's also interesting here to say which one should be in the background and which one should be here in the front. Then you can see here this right graphic is here now in the background, but you could also just say, bring it forward and then here it's in the front. Especially once you have more, then you could also use this function here that you want to bring it here to the front or just one to the front. Also saying here one to the back or in the very back. Then we can also remove it here again because it's also possible here to form material size of it. Of course, we can just drag it here bigger. Either we're going to do it like that, that we pull it here up or here to the left or also to the right, or we could also make it here like that. And then also possible, as you've learned already is that we press the all key, and it's also here moved along the grid line, also here possible once we change the size of the graphic. You could also make it like that that you just give in something. For instance, ten should be the height and the width should be 15, for instance, and then you can also change here the size. But here are also the size properties. When clicking here in this button, then we also have our right menu bar, and there we can also change the height and the width here again. Also the scaling, you could adjust here, how it is scaled, for instance, 100%, which is the standard. Then also the properties are here. Then you can also say, should be moved with our cells or not moved. I just said here, I should not be moved. So this is when changing here our columns or our rows, then you can see that we have no change with our graphic. But if we say it should be moved with our rows and our columns, then it looks like that, and also the size of our graphic changes, of course. Then going back here in the format, then we can also see that there is the current selection. So not only with a right click, we can say we want to select certain data labels or columns. It's also possible here by saying we want to have the serious Mexico in order to say that we want to change the filling, for instance, with such a light green, but you could also besides the selection of certain data labels, you could also say you want to select only the plot area. And formed it or only the legend, as you can see, sometimes it's just easier if you have many things in your graphic to use this function here in the ribbon format. But of course, it's also just possible with a right click. Then also interesting here with the plus. You know that already. You can say you want to add an axis, access titles, but also jar title, data labels, but also data table you can add. So that below, there's also data tables the same when clicking on design and quick layout here, which is number five from the layout that we can also add such a table below. Then also interesting are just the grid lines. We could say we want to have the primary major horizontal grid lines, but also the major vertical ones are interesting or the minor horizontal and vertical ones as well. Then also interesting is just clicking here with the right click and saying here if we go down that we want to format our chart area. Then we have here our menu bar like that. Then you can also change here the fillings, saying this should not be filled and we have no fillings and see the grid line from the Excel spreadsheet or from the worksheet behind or also cradian filling is possible. You could also work here with different types, directions or also cranian stops. So if you want to say, this should be a certain color, and then we want to have here cradian stops from dark blue to bright blue, for instance, or to white, and also the transparency, you could manage here and the brightness as well. So there are a lot of options. But also a picture, you could add pattern fill as well. And you could also say here, you want to manage the shadow here with the color, transparency, size, blur angle, but also the distance, and then also the clo you could adapt, but also soft edges, and you also have settings here for a three D format. The end, I also want to point out that it's also possible here to change the size of the graphic again here with the width, but also rotation is possible, and this is something we already had to look at. Very important to say if the charge should also move when changing here the column or row width. 49. Module 8: Slicers, Timelines & Dashboard Integration: After we've learned how to visualize our data with pivot charts, now it's time to build an entire Dashboard using filters, Slicers, and timelines. Let's build powerful reports. 50. Slicer Functionality and Best Practices: Welcome back to this video where we're going to have a look at how we can create a Dashboard here with our Pivot Tables and charts. Well, what we have seen already is that we can use those interactive data Slicers for our dynamic tables, and then we can easily just filter our data for a certain business area, for instance, but also for a certain country, as you can see here, only for Germany, only from Mexico. And this also, of course, works with our Pivot Tables. So I have created here in such a Pivot Table already. Then I can click here on Pivot Table Analyze, and then I can insert my data Slicers. Then I can say I want to have a data slicer for the year, but also for the country. Maybe interesting would be also here for the business area or also for the product, also for customers for sure, but I just want to confirm it here with okay. Yeah, then we can just position those here on the right, as you can see, we have here in the background, our Pivot Table and then our four data Slicers. We can just select all of them, pressing the control key and just moving them here to the right. Then we can also use the key that is here positioned along the grid lines. Then I can also press the key again to position the country here, for instance, and then also the business area below. And then what is only missing is here, the data slicer for product. And here it's also interesting that you can also manage how many columns you want to see. So I can say I want to have two columns. Then I can also change the size here, make it a bit smaller. And then also the same here with the business area that I want to see, for instance, even three columns and then position it like that. And then also here with the product that say, we want to have here, only two columns. Then of course, it works that we can filter here only certain business areas, only business area one, for instance, or three, but you could also press the Control key in order to select multiple ones. You can also press this button here, multi select or also just use the shortcut all the NAS then it's also working like that that if you click here on a selection like Business area three, then you can also remove them. If I remove also Business area one, then everything is selected again. And then of course, I can also just add here another Pivot chart just by clicking here on Pivot Charts. Let's choose this one a stack column chart and confirm it here with Okay. Also here, I can press the Al key and move it here to the bottom. And then let's have a look at here how it works with the data Slicers. Of course, it does not only have an influence here on our Pivot Table, but also here on our Pivot chart. And once we filter, for instance, business area three and four, then we can see here that something changes in the Pivot Table as well as in our Pivot chart. What else is possible? We can also say we want to duplicate here this Pivot Table, select everything or also press Control A. And then we can also insert it here with Control and B, then we have another Pivot Table. And with a right click, we can also show here our field list can also remove here the country because we want to show here the business area, for instance, and instead of the year, we can say we want to see here the customers. So it would also make sense here to swap it, here the customers in the rows, and then the business area here in the columns, then it looks like that. Then also here, we can say we want to implement a visualization or insert here, such a new chart. For instance, here, such a stacked bar chart, then it looks like that. Then the interesting question, of course, is how it works now here with our data Slicers. So for instance, if we select here Business area two, then we can see that this has filter impact on our first Pivot table on the first Pivot jar, but also on the second Pivot Table and second Pivot jar. This is something we can control as well. We can also just say here, clicking on our data slicer, and then you can also go here on report connections. And then we can also remove here the jack box in order to say that our data slicer should not have a filter impact here on our second Pivot Table. And let's see, let's check it again if it's going to work, filtering Business era three. And then as you can see, the filtering is still remains the same, is still the same here when it comes to our second Pivot table and second pivot chart. And this is something you can manage for every single data slicer. Also here, I can say should not have a filter impact here in a second Pivot Table. Also here, I can remove it, and for the product, I can remove it as well. Yeah, and as you can see, then you have many options. You can also build here such a Dashboard consisting of many Pivot Tables, but also data Slicers in combination with data visualized in Pivot Charts. 51. Using Timelines in Pivot Tables: We can use data Slicers for our dashboards with Pivot arts and Pivot Tables, but we can also use timelines. So let's have a look at it. We click here in our Pivot Table, go here to Pivot Table Analyze, then we can also click here on insert timeline. A timeline is a great thing in order to filter dates interactively. So timelines make it faster and easier to select time periods in order to filter our pivoarts but also our Pivot reports, so the pivot tables, but also cube functions. Let's have a look at it. We have to click on Insert timeline, and of course, the only data field, which we can select is the date. This is also based on the data type so that our values in the column date are really formatted as a date. Let's click on Okay. And as you can see, we generate here such a new object here, our timeline. Then also interesting, we get a new ribbon called timeline. Also here, we can add a timeline caption. Let's say date. Also interesting, we can report connections with the data Slicers, we can also say this only should have a filter impact on our Pivot Table one and also the pivot charts based on the Pivot Table one. This is now our timeline. Also here, we can change the colors. For instance, we can also make it yellow, orange is possible here, but also blue and green, I would like to format here in this light cream and I can also do so here with my data Slicers that they should have the same color here in green, then it looks like that. Then let's have a look at how the data timeline slicer works. Also here, it's possible to press the Control key and then just drag it here to the right. Then we have two data Slicers, two timelines. Also here you can say bring forward or bring backwards. It's also possible. Then also interesting is here that we can change the size, so we can configure it here the height, but also here the width. And also can show here header, also the selection label, but also the scroll bar here and the time level. So let's say here we want to work with this timeline, and as you can see, we can choose a level. So either it's going to be here on a yearly level, then we make it here on year. And then you can say here, this should be only for year 2025 or 2026. Then you can also say here you want to see it on a quarter level. Um, here, quarter one to quarter four, in 2025, well, let's say here from quarter three to quarter two in 2026. And then, of course, it has an influence here on our graphic, as well as on our pivot chart. Then it's also possible, of course, on a monthly level and even on daily level, as you can see, for a deep dive can be also interesting. Then we can also clear our filter to just click on here this filtering icon. 52. Selection Pane for Enhanced Control: If we work here with dashboards, so with Pivot Charts, pivot tables, as well as with data Slicers and timelines, then it's also possible here to work with the selection pane. What do I mean with it? Well, we can just click on our timeline here, go to the ribbon timeline, and then you can click here on Selection Pane. This just gives us a new menu bar on the right with a list of all our existing objects here in this worksheet. And here we have the option to select our objects and also can say which of those should be visible in our Dashboard and which of those not. So when it comes here to our chart, then we can see here that this chart has a name or also this Pivot table, but also the Slicers. So we could, for instance, say that we don't want to show here our chart 12. As you can see here, then I can also hide it here. The same also with chart 13, then you can also see here. If I say hide, then it's not visible anymore. But it's still there, it's only hidden. Therefore, you can also just press here, hide all and all of them just disappear, as you can see, except our two initial pivot tables, and then you can also say you want to show all again. This just makes it easier by creating such a Dashboard to select certain data Slicers and say which of them should be visible in our Dashboard and which of those not. Therefore, I would also suggest you here to click on our objects such as our Pivot chart, and then you can also say, here, this should be with a new name. So we could also just change the name. We can say here, this is a column Jart for our countries. And then it's named like that, and you can also see it here and then it changes also the name. And here, for instance, we can see the customer for business area, so for A. So now we also gave it a name. Can also just say V, underscore, and also here with a V and underscore. Then it's also just visible that this is a pivot chart, and for the Slicers, you could also just say, here, you want to add just an as as here, then we have the business area where you can also say a underscore and also for the country, we can say as underlying country and for the product as well. This also just makes it a bit easier that we can also see it in here in our selection pane and also for the timeline. When we go here to the name of our timeline, it just has a name here, date, so we can also just say here, mt underscore. Yeah, and then you can work here with the selection pane in order to say which of those objects will be visible and which not. 53. Creating a Pivot Dashboard from Scratch: So now let's create professional and interactive Dashboard from scratch. As you can see, we have here such a Pivot Table, and now we can start with grading our Dashboard, just going here on Pivot Table analyze where we can also just start with our sliders. So let's ask the question which slides we need. So it's interesting for us here to filter by country, but also maybe here on business area level as well as for customer and also product. I don't select here the year or the date because this is something we can do so with our timeline. So then we can just go here on insert timeline and also insert a timeline based on our date. Then we can also choose a color. I would definitely say that it does look better if we just use one color for everything. So for instance, we can just say we want to create our Dashboard here in blue color or gray. I can also just say yellow then it looks like that. So let's adapt it here for all the data Slicers, as you can see here, also the slicers here in yellow, and also our timeline should be formatted here in yellow as well. Then we can also do so here with our Pivot Table on design. We can also say this should be here in yellow. And then all of them, we can just group. Either we group it or we just select all of them advance here by pressing the Control key and then move it here on the right. We can position it and we can say here, our Dashboard should look like that at the top are all of our interactive data Slicers and here below are our visualizations and on the left. We just want to see here our pavilybts. Then we can say here with the sizers that we want to see two columns. Then you can also press the l key again here for our grid lines. Then it looks like that here for our country. Same then here for our business area, here, we can even say three columns, then it looks like that. We can also make it with the same height, then it's just formatted equally. Yeah, then let's go to our product. Then we have more attributes and values to select. So we make it here a bit bigger, but with the same height. Then we can also scroll down, as you can see, and the same here with the customer, we can also say three customer columns or let's say four columns, then it looks like that. And then the last one is just here our timeline. Then we can also choose the level. I would just say here, it should be on yearly level. Then we can also make it a bit tighter here, and then we already have here our top. And below, we just want to see our visualizations based on our PVIT report, which we can see here. We can add the pivot chart. For instance, we just start here with a simple stacked column chart. So here we can see it on country level as well as on a yearly split here for the legend. And here on design, we can also say here with change colors that everything should be here with such a yellow tone, and then you could also say you want to see the table below. Then we can also remove here the chart title, and we can also say with a right click, we want to hide all field buttons on our chart. Then we can also move it here on the right, and also you can press once again here the old key, then it's also positioned along the grid lines. Then you can also impress the Control key and drag it here to the right. And before we're going to do that, we can also say here with a right click that the outline should be maybe here in gray, and also we could say here with view and show that we want to remove the grid lines in our Excel worksheet. Pressing the control key, dragging adhere to the right. We can say here with design, we want to change the chart type. For instance, we just want to saying we want to see here such a ring chart, then it looks like that, and also here we could add the data labels. Also here, we could add them and also here. By the way, it's also possible here to say form data labels that we do not only want to see here the values, but also here the value from the cell or the series name we want to show as well. Then we could also see here which category we wish allies here, category name, the same here then, category name, and here also for the category, the name. Yeah, and then also here, of course, we could add the legend, and then we can also see it here for the countries. And then we could also position it here, and it already looks like an interesting Dashboard. We can make it here a bit bigger. We could also here duplicate our Pivot Table, this report, Control A, Control C, and then paste it here with Control V. Then we can also show our field is here with a right click, and then we simply can say here that we want to see here the development over time, using here another Pivot chart, maybe here is such a line chart, also looks interesting. Then we can also say here with design, it should be the same color here in yellow. Or we could either also show here on the customer level. So for the customers like that, but also here interesting over time. But also you could just sort it just saying here from largest to smallest, then it looks like that. This is something you could also position here below at the bottom. Also saying here, we want to choose such an outline for the frame, and also we can remove the chart title and also the illustrated field list buttons on our chart. Then we can make it like that. Also looks quite well. Then we still have some space here, so we could do it like that. And then we can also insert our last chart based on this pivot table where we can also choose a pivot chart, and then also using here, such a bar chart maybe clustered like that. And also here we could say here with the sign that we want to use here in the monochromatic pallet number four, also just position it here on the left. And here, also the same that we want to use such a frame. So we could make the size like that. Also here, outline here with this color, also remove here our field buttons because we anyways have our interactive data Slicers. And now we have created here, such a Dashboard. And the positive thing about this Dashboard here is that it's interactive. So we could also send it here to our management, and then we could only see here the values for Germany, for instance, or here South Korea or multiple countries like that in certain business areas for certain products, and then you can get the most important information out of our data. We can extract it here from this interactive Dashboard. And this is just an easy example how such a Dashboard could look like. But of course, you could also use different chart types, but also different pivot tables. So this is, of course, only based on two more or less contains similar key figures, but you could also change it up and create your own Dashboard like that. 54. Module 9: Power Pivot and Data Models: Until now, we've built Pivot tables only based on one flat table in Excel. But it's also possible to build pivot reports based on multiple tables by using a data model. In this module, I will show you how to use the Power Pivot add in in Excel and how you can use those BI skills for your Pivot reports. Let's get started. 55. Using Lookup Functions with Pivot Tables: Welcome back here to the section where we're going to speak about Power Pivot. Power Pivot is a built in technology in Excel for Business Intelligence, where we can also use Pivot Tables. So our intention now here is to build an entire data model consisting out of several data tables, and then we can, in the end also create our own dashboards here in Excel with Pivot reports or Pivot Tables, but also Pivot Charts and interactive data Slicers and timelines. You can see here, I have here a new data table, and first of all, I would like to point out why it's important to work here with Power Pivot A and ad in Excel. So this is my data table. We have 1,524 rows, as you can see here, and this is only here for revenue. We have a different table here, which is only for the city information, here for country, for customer, but also product, business area, and data. We have own tables. And this is also how it looks like often here in practical projects because often you don't have the data all collected in one entire data table, but here split it also in different several data tables, as you can see here. Let's say we would have a dataset which looks like that, that we have it here in the first sheet like that. And the next one, just to make it here simplier in the beginning would look like that, that we have here in next ten values, so like that. And even though it's the same structure, it is like that if we would here create a Pivot Table based on the left table. We can also format it as a dynamic table, which we have learned already in the course with Control T. Then we would be able. I do it here as well for the right one. Then we would be able here to create a Pivot Table here based on table, which I name here, Table A. And then also we have her Table B. And if we work here with several different data tables, then of course, also the Pivot Tables are always connected to a different table. So this would be here our table A. We can also rename it here and call it here Table A and this of course is then Table B. Then, of course, if we summarize our results from Table A in a Pivot Table here in this existing working sheet, let's say here. Then we have here our table A, and we also have then our Pivot Table A, which you can see here. And the same, of course, then also here with this table B, where we can also just say here, table design and summarize results here with Pivot table. Then I can position it here, which is not Table A then, but Table B. Then our problem, of course, is that this Pivot Table A is always referring here to our initial data table A. But Pivot Table B is getting all the information and data here of here, data table B. And this is a huge problem, of course, because sometimes we also need here in this Pivot Table B, then also information from the other table. So what you can do, of course, is to merge them. So just putting those rows here, then of course, it's possible to have then only one Pivot Table, which is going then on all the information out of the data table. So, this problem we don't have here anymore because then here it's all combined in one data table, but we have a different problem because we have here the product ID, but not here any information about the product itself, so the product name, or also to which business area it belongs, which we can only see here. Business area name would be those. And also we don't have any information about the customers, only the customer IDs. So those are the customer IDs, which you can see, or also we don't have any information about country and city. Those are also information we just get from other tables. And of course, we could work here with lookup functions. This is something we want to have a look at in the next video. 56. Adding Tables to Power Pivot: The last lecture, we have already learned that it makes sense to use Power Pivot as a technology in Excel for building data models and also for building dashboards with Pivot Tables. Then we also have seen here the problem which we face here with this data table that we have the revenue, but on the other hand, we don't have any information about product customers, city, and country because those information are here in the other data tables. So what we can do here is that we could work with lookup functions. So the first lookup function, which could be interesting would be here the V lookup. This is the formula, V lookup. So now I also can call it here Vlookup in order to get the product here. So let's have a look at the V lookup function. We can just type here VN L, and then you can tap also the tap key on your keyboard, and then it's auto fulfilled this function. The V look up function looks for a value on the leftmost column of a table and returns a value in the same row for a column which we specify. By default, table must be sorted in an Scanding order. So first of all, we have our lookup value. When it comes to the product, it would be here in the product ID. Then semicolon, we say here, we want to use the table array, which would be the array here of our product table. And then also here, we give then our column index number. So here, the product ID will be found, and then it's a second index because then we want to have our product. Then we can also say if we want to have an approximate match or an exact match, therefore, I'm going to say false exact match. And then also, I have to press a four because I want to have a fixed range, of course, when it comes to the cell here. And as you can see, it works, and I can also just copy it down with Control C and then paste it with Control V, and it was successful, what you can see here. So the only problem which the Vloga function has is that it's only working to the right direction. Do I mean with it? Well, we are searching here for the product ID, and then the return should be here the product name. So we could also name it here product name. But what if the product ID would not be in the left position, but would be here on the right position? Then the VlogA function could only work here to the right, but the product name would be here on the left. And as you can see, then also the VlogA function is not working anymore. This is why I would highly recommend you more to use the X lookup function. X lookup function searches range or an array for a match and returns the corresponding item from a second range or array. And by default, an exact match is used. So that means we're also going to search here our product ID. Then we jump back here in our product table, and then it works like that. We do not select here the entire product table, but we just select here the lookup array. So this should be search here in column C, and the return array should be here column B because we want to have back here just a name. And then this is already our function. We can also press F four once again because also here, this should be fixed. As you can see, now it's working and I can also copy it down. I definitely would recommend here the X lookup function because it has way more advantages than the V look up. By the way, there is also the so called H lookup function. This function also looks for a value, but in the top row of a table or array of values returns the value in the same column from a row which we specify. Just the structure then of course, is different. V stands for vertical, and H stands for horizontal. This is also lookup function, but the H and the V lookup function, you don't need anymore nowadays because in Excel there is existing the so called X lookup function, which is just smarter to use. Oh, now with the X Lou function, of course, we got here our product. So what about the cities? Those, of course, we can also get. But let's say here that the information here from the city is not here in this worksheet in this book here, lookup functions dot XLSX. But it would be here in a new worksheet, so I can just press Control N, and then I can insert it here. So this would be here my new city table. And then with F 12, of course, I can save it here. Let's say I want to save it here, and then I can also name it here city Table, and then I can just say, I want to save it here as Excel Workbook. And then, of course, we have here a new workbook. By the way, it's also recommended to go here and view because then you can also here arrange all the workbooks which are open, and then I can also say I want to have it like tiled. Then it's just easier to work with because on the right, we have here our big revenue Table, and we also have here the column city ID. And then on the left, we do have our city table where we can get the information about the city name. So how does it work now? Also here, we can say city X Lou and then we can also use the X Lou function. We can select first the city ID. Then the Lou array, which is here, just the City ID. So this is Lou array and then also here our return array, which is that, then as you can see, it works, it does work, and we can just make a double click here on the top right and then as you can see, it's fulfilled and everything was successful. But let's have a look here. I can also select everything here, here, all the columns with Control and space and Control and plus, I can insert a new column because I want to use here the so called formula text function because here I can also just illustrate once more how the Lou function looks like. As you can see here, we are referring to an external sheet. This is not an advantage of those lookup functions because first of all, it's just here the workbook name ct table dot Excel, which is mentioned. And now let's say we would like to close this left workbook. We can just press here the X. Or you could also use the shortcut Control W or also possible to use the shortcut d and afore, and then I want to save it also. And now let's have a look what happens here with our formula. As you can see, I just get here the entire path, and now it gets more complicated because the entire path is here. But of course, if we decide to open our spreadsheet again, which you can see here, then we don't have this path anymore, but it could be a problem here with projects here that's just getting too complicated. And also when it comes here to our data, then we have here all our queries and connections, but also links, and now there's a link to an external Excel workbook, and then also mistakes can occur. Also another problem is that we only have gotten here our information about the product name and the city name, and it already took some time. But we also have to get the country again, but also the customer, so we have to work with a lot of lookup functions. There's definitely a better approach how to work here with multi tables. So we can just use the technology, so called Power Pivot to build a data model and also build in Pivot tables referring to multiple data tables. And this is something we want to have a look at in the next video. 57. Introduction to Power Pivot: So let's speak about Power Pivot. Power Pivot is a data modeling technology which enables us to create data models and establish relationships and also create calculations. We can use it here in our project for creating Pivot Tables, which will refer them to multiple data tables which we have initially, and then also it's possible to connect them as mentioned here with relationships, and this is something we want to have a look at here. Do I get Power Pivot? Well, it's already built in. So it's an add in or add on in Excel, and we just have to activate it, and then also a menu bar a new ribbon occurs, and then we can work with all the features Power Pivot provides. So let's have a look at. We can just make a simple right click here, customize the ribbon. And as you can see here, I can just go on addIs then after I can click here on com Add ins, as you can see, then we just can go here, go and then we can say, we want to activate here the Power Pivot Excel feature. Then we can confirm it here with Okay, and then we also have to show it here, customize the ribbon. It's activated now in our Excel environment, but it's not here listed as a new ribbon in our menu bar, therefore, we have to click on the checkbox and then make a double click here in the new ribbon, we can see here that the new Power Pivot feature pops up. Now we have different action fields which we can use, for instance, here with our data model. So clicking here on the data model will lead to a new window. So it's out of our Excel environment, which we have used before. And here, we can just import all the data tables which we can see below here in this new environment. We can also maximize it here, click in there or just using the shortcut window key and then arrow up. And then this is our new working environment. And our approach looks like that we now import all the data tables into our data model environment of Power Pivot, and then we can in the end also grade our own reports with Pivot Tables. Also interesting would be here to use the shortcut, right click and saying here, we want to add this click button or this feature to the Quick Access tool bar, which you can see here, and then it's just easier to change in this environment, what you can see here. So let's start importing our data tables in Power Pivot. 58. Adding Tables to Power Pivot: So now let's start with adding all our data tables here in our Power Pivot environment. This works pretty easily. We can just go here on Power Pivot, and as you can see here, I can either go here on Data Model, and then in this new Power Pivot environment, select my data table, or I can also just say here, I want to add this data table to my working environment. Then as you can see, it's important that we create also a table here out of our range, and then we can also say here, our table has headers. And as you can see, now it's imported already here in our working environment here with Power Pivot. The only thing I don't like so much is here is that the name of our worksheet in Power Pivot is different than the worksheet here. The name of the worksheet in Excel, as you can see. So this, of course, just must be revenue. This is also always the same name here with our table because it used to be named as Table one. This is why we also can just give it a name revenue. Therefore, I would rather say that we click here on Format as Table or press Control and T instead of clicking here on add to data model because then afterwards, we can always give a name before we really add it here in Power Pivot. Then we can say this is our city Table, and here we can then go back to Power Pivot and also add this data table here in our working environment. Now we have added also the City Table. Then we can also go on with saying that we want to also forum it here our data range when it comes to the country as a dynamic table and also rename it here as our country table. Also, this one is something we want to add here. Also here, you could say add to Quick Access Toolbar and then just click on here, and then it's also been added. This is now the country. Then continuing here with the customer, also Control T, then it's a dynamic table. And then we name it here as customer, and also here, we can edit again. What is still missing then is our product table, which we can find here. Also here, we can say we want to create a table. Of course, I would rather say that I want to position the ID column here on the left. This is why I just select the entire column here with control in space, press then control in X, or here, say I want to cut it, and then here I can just press Control plus in order to insert it once again. Then I can press Control and T to say this is a dynamic table as well, and also here, I can say this is my product table, which I want to add here in Power Pivot. Then let's go back here also in the tab business area. Also here we can press Control T and say here, this is our business area, and this table also should be added to Power Pivot. And last but not least, the very final one is here the date, also here, Control T in order to format it here as a table to say here, this is our dat table, which should be also part of our data tables here in Power Pivot. Now we have imported all the data tables in the Power Pivot working environment, and we can also maximize our Window or just pressing window on there up. And then we have here all our data tables, which you can see. So now it was successful to import all the data tables in Power Pivot, and now we're going to continue with the data model. 59. Building a Data Model for Pivot Use: So now after we have imported all the data tables in the Power Pivot, it's the time to build up our data model. This is something we can do here in the diachram view because here we can see all our data tables which we have imported already, and it works like that. We have here our revenue table, which is the so called fact table, and then we have around here all our dimension tables. And then we just try here to connect all the tables with each other. So we would like to build relationships. And then we can just position it here around our effect table. It's also the so called star model in BI, and it works like that that we position here the fact table in the middle. And then outside, we have all our dimension tables. For instance, we can say here, we would like to create a connection or relationship between the country table, which is the dimension table and the fact table revenue. So we can just say here, those two columns should be connected country ID from the revenue table and the country ID from the country dimension table. As you can see here, also, it's very important that we have to write directions because here the information should be flow from the dimension table to the fact table. This is very important that it's not the opposite direction. This we can also enter either here with this diachram view that we really drag from the fact table to the dimension table or also by design. We can also click here on Create relationship where we could also say here that, for instance, the customer IDs should be connected, causomD from the fact table revenue and custom ID from the dimension table customer. And also here, we have created another relationship, what you can see here. Then also date, of course, so the date ID with the date ID of our date table, but also the business area here so we can position it like that, rather, the business area ID here from the product table should be connected with the business area ID column of the business area table, and then also, of course, the product ID with the product ID between the revenue and the product table. Yeah, if we do so, then we get here another information. Then the product ID column here in our table product also contains blank values. So this is a very great mistake which occurs here that you learn how to solve it because going back here in our product table, we can see there is really such an empty row, and you cannot remove it here with a right click. Therefore, we have to go back here in our initial data table, and then we can just select it here with shift in space and also with control minus, we can just simply remove it, and then we can go back here in our Power Pivot data model and just refresh it in order to see that this row got removed, as you can see. But now we really see that it got removed, so we can't click here in this row anymore. Going back in the diachram view, we can now build a relationship between the revenue fact table and the dimensional table product. So the last connection we want to make here, last relationship is with the City ID, between the fact table revenue and dimension table city here with the connection between both City ID columns. And as you can see here, we have created our entire data model. And what we can do now with this data model will be a thing which we have look at in the next video. 60. Working with Pivot Tables Across Multiple Data Sources: So let's finally start grading our Pivot Table across multiple data tables based on our Power Pivot data model which we have created here. And as you can see, all the tables are connected, and then it's possible here simply to click on the home ribbon in the Power Pivot window here in this working environment, and then we can click on Pivot Table. So we can summarize all our data now on a Pivot Table in a report or either in a pivot chart. And this just simply helps us to summarize and visualize and explore our Power Pivot data. So it's based on this Power Pivot data model which we have created. So either we can create here a Pivot Table or a Pivot chart or also we can, for instance, create both. But we just want to say we want to create a simple Pivot table in a new worksheet, then we can press Okay. And as you can see, a new Pivot Table was created here. And then we can also drag our column fields between areas here below in the filters and the columns rows, but also in the values. And of course, I want to highlight and also point out, once again, here the difference between a pivot table based on Power Pivot, which we can see here on the left. So this and also here a normal pivot table based on our dynamic table. We could also say here, summarize with Pivot table, existing working sheet sheet one, and then just position it here, for instance, press. Then this, of course, is Pivot Table based on one table. By the way, we can also move this worksheet here on the left because those are Pivot reports. Then we can also highlight it here. Tap color. This is our effect table and all the orange ones here, I can just press Shift. Here are the orange ones, those are the dimensional tables, and here in green, we have here our sheet with the Pivot Table analysis. As you can see here on the left, this is our Pivot Table based on Power Pivot and on the right, this is based on one dynamic table. Well, what is now the difference? We can just click here in the Pivot Table based on one table, so on our revenue table, which we can see here. Then of course, everything is still referring to this initial table. As a result, we can see only the fields which are also here part of our table like revenue ID, date, ID, all the other IDs, the revenue itself, which we can see here as fields and we can position it here in our fields, track and drop them. But our left Pivot Table is based on our entire data model which we can see here. So all the information flowing here from the dimension tables to the fact table. We have relationships, and as a result, also we have different tables with different columns to select. For instance, I could just take here some fields like the revenue and trackeer also in columns, rows and values or filters, and also I could work now across multiple tables. And this is the difference here between Pivot Tables based on Power Pivot and based on one table. 61. Pivot Table Analysis: Final Techniques: Welcome back to this lecture, where are we going to start creating our Pivot Table now based on Power Pivot. I've already illustrated and point out here the difference between Pivot Table based on Power Pivot and one flat table. So let's call it here just difference. And then we can also here paste our existing Pivot Table, of course, based on the Power Pivot data model here in this worksheet. Then we can also rename it here. We can also use a different tab color can say here Pivot. And then how does it work now? We can just go here in our fact table revenue. We can track the revenue and the values. And then we can also format it here. So with Control Shift and one or also here, pressing commerce dolls, and then we also show fewer decimal places. And then we also want to have a categorical split. So getting the country, for instance, trying it here in rows, and then you can see it here on country level. Right click, can sort it largest to the smallest here. But you could also say here, you want to show all the product areas, try it here, or instead of also the business area names, what you can see here or position it in the columns, and then you have a very interesting report. Of course, you could also here use a pivot chart again, for instance, a stack column chart, place it here. So below our Pivot Table, and then also it's possible, of course, since it's based on Pivot Table technology, you could also just insert some slicers, and the slices, as you can see, are referring now to all our data tables, so business area country and revenue because those are now in this Pivot report. Is why I would recommend you also to just put the remaining in the filters because then if we go back then in the Slicers, then we can also use the slicer based on our filter. So therefore, also the customer, for instance, just here in the filters, also the date, here the date. And also the product which we can position here. Let's say the product name, and then we have just more options when it comes to our Slicers, clicking back in our Pivot Table Insert Slicer. Then we can also say we want to have a slicer for the business area name, but also either for the city, for the country, for the customer, but also either for the product name, and then we can confirm it here with. So we generate here total five data Slicers, which we can position also here on the right, and also here, we can position them next to each other. Or we could also say, we want to work here with different columns, here, two columns, here, two columns, but also here, I want to work with two columns, here, two columns, and also here. Then it's working, of course, we can select here the business areas and also it has an impact here, the filtering on our PivDRport as well as on our Pivot Charts you can see, and also the countries for sure, but also the customers, then we can find out with which customer we make which revenue. As you can see, that's a great option. If you have multiple data tables here with revenue, CD, country, customer product, business area, and date to build an entire data model based on Power Pivot and then using Power Pivot in order to create your interactive dashboards. 62. Module 10: Automation and AI: Save time by your data analysis with Pivot Tables by using automation. In this last chapter, I will teach you how to use VBA, Visual Basic for application as programming language in Excel, and also how to use AI for Pivot Tables using JGBT. Let's go. 63. Automating with VBA for Pivot Tables: In this lecture, now we're going to speak about VBA for pivotables. First of all, what is VBA? VBA stands for visual basic for applications, and it's a programming language which we can use here in Excel for writing codes. And so we can write macros. And with those macros, we can also automize here our steps in Excel, also work with Pivot Tables. So how do we just start here with VBA? Well, first of all, we have to activate the developer ribbon. If you don't see this ribbon in your menu bar, then you just have to do a right click here on customized the ribbon. And then just make sure here that you have this check here at developer Ribbon, and then you can just confirm it here with o. And then you can easily jump here into Visual Basic. This is the so called Visual Basic editor or also called VB E. And here we can see the structure of our workbook. So we have our workbook and also existing worksheets. So this is here in your window. And as you can see here, we have currently three worksheets, database, Pivot, and VBA, which you can also see here. And then it's possible with a right click here to insert the so called module. Here in this module, we can just write our codes. For instance, we can automize our Pivot Table that they get updated automatically, so we could write here some codes. In this first lecture, we have learned now what VBA is and how we can activate here our ribbon in the menuar. Then also interesting is here that we have several action fields here. This is the BE. You can, by the way, also started here with all N F 11 to press it here. In the next video, we're going to learn also how we can record our first macro. 64. Recording Macros for Pivot Tables: Work with Pivot Tables, then we have three options how we can use VBA here for Pivot Table. The very first option, of course, is that we write VBA code. So you've already learned that you can start a visual basic editor here with the developer ribbon and clicking here on Visual Basic, and then here inserting a new module, and here you can write the code. But the second option is also here to avoid writing actual code and getting it generated. This is also possible here just in order to record a macro. So macros contain such VBA code, and we can also record them easily, and then we don't even have to have any knowledge about VBA codes and the macro recorder, so called, will generate a VBA code in the background. The last possibility here, the last option is just to use ChatGPT in order to generate here some code for us, VBA a code. So those are the three options which we can use in order to automize here our processes or work with Pivot Tables. Yeah, first of all, let's generate here a new Pivot Table, and while we do that, we also use the macro recorder. You can also click here on Macro, and then you can see here all the macros which are recorded already. You can also run a macro with here or also running a macro clicking here on record macro, but it's also possible here at the bottom here, clicking here at recording a macro or also possible, it's here with clicking here on view. Then you can also see here macros and also here you can record a macro. I want to do that now. I click here on record macro, and then I can say Pivot Table creation. I can call it like that. And also, you can use a shortcut key, which you can give in, like, for instance, Control and you. And with this shortcut, for instance, we can later run also the macro which we have recorded already. But I just remove it because I don't need it here. Also you can add a description. With this macro, it's possible to create a new Pivot table, you can say. And then it runs now, what we can see also here at the bottom. So a macro is here currently recording. Then we can move here in our database. And by the way, this is always here recorded, what you can see here in the new module. So this macro is called now Pivot Table creation, as you can see, and then we can see all the steps. So it always starts with sub, then the name of our macro, which is pivotable creation. Then it also always ends with ends up, and here in between, we can see everything which we are doing here in Excel. So we can also see our comments. So comments always start here with this tick, and then we can see here that we currently just selected a sheet, which is just a worksheet database so called. And then we can, of course, just say here table design that we want to summarize our results in Pivot Table based on our table range. And then we can place it here in an existing working sheet here, so called VBA, just position it here and confirm it with o. And let's see here all the results which we have in our macro recorder. By the way, you can also open the window, you're maximize with pressing Windows button, and arrow up, and then you can see all the code which is generated. So first of all, we have selected here our database sheet. Then also the specific range because we have clicked here in our dynamic table. And after what we did is also that we generated a Pivot Table, which we can see here. So some of this code we don't really need. And yeah, then we can see that a Pivot table was created here. So we have a lot of parameters here also. So this code, we also could have written just a bit easier or leaner, and then we can see also that it was placed here in our active working sheet here with this function and also gave it a name here, Pivot Table one. Yeah, and then it just ends here, and this is pretty much here or macro. It's still running. Therefore, we can also click here and view and just stop it here, Stop recording or also saying here, stop recording. This is our very first macro. So it's just interesting to see for us that it works to generate such a macro. And I would suggest to you in the beginning also to work with this macro recorder because it's just easier that you also get used to the code which is written. Then you can also have a look in it and also start understanding here what is written. And also, you can use different use cases. For instance, we can also grade our Pivot Table here. Then we can make an analysis here in business a level, for instance, and then we can also show here our revenue and the values, also format it here like that with fewer decimal places. And then, for instance, we can also run a macro here, clicking either on the bottom here in this action field or also view and macro here record macro or also here, which you have learned, record macro. We can also say here, update Pivot. Then you can also press Okay here. Then it's recording. You can see here that a new code is generated here, sub update Pivot, also with a comment here. Now let's see what happens if we click in the Pivot Table and here with a right click, say, refresh, then we can see here the code also, and we have learned now here that first of all, the Micro recorder recorded that we clicked into the cell of the Pivot Table like Range D eight, which was selected. And after it's a very simple code because we can just see that the Pivot Table was selected here, and with the refresh function, the data was refreshed or updated. We can just end our macro, so we can stop recording by clicking here. And then, of course, it's also possible to insert here certain form controls, like this one, for instance, we can just place it here, and then we can also assign a macro, which is existing like update pivot, and then it's always possible if we click here that our Pivot Table is updated. Um, let's check here because we could also just say in a certain business area, so business area one, we want to add a number, which is very high. So let's say here, we want to add this number like 10 billion euro. And then we can just press the button one. And as you can see, it's updated and this new figure is also appearing here in our Pivot Table report. You can also give it a name at a text, and we can just write something here. Like, for instance, we could just write update Pivot Table, and then it's just very easy for a user to press here, and then Pivot Table is updated. So of course, I just want to remove the figure once again, and then we click here again on Update Pivot, and as you can see, it's updated automatically. So now in this lecture, we have learned how the macro recorder works, as you can see here, so pretty easy. And now we also want to start writing our own code in the next video. 65. Writing Custom VBA Code: We already have learned that there are many options how you can create a new macro, which, for instance, will update our Pivot Table. So first of all, we could just use our macro recorder in order to record our macro and automatically generate our VBA code behind. But we could also write our VBA code on our own. This is something we want to try here in this video. So, of course, we want to move here in our VBE, in our visual basic editor, you could also just simply press all the 11, and then we have here our environment. And as you can see, I can hear, say, with right click Inst module that I want to write a new code. Yeah, then as we've learned already here, every macro starts here with SAP, and then we can just say here update Pivot Table, and then we can press Enter and already generate here this ASAP. Then we can also work with objects. What kind of objects do we have? First of all, this is already an object, this workbook. Then also our worksheets here can also be considered as a workbook. And then also here our Pivot Table, for sure, is an object or also if you decide for a Pivot chart, and this is also an object. Then we can also give them a data type. So every object, we can also write it here with them. Then we can say dim PT as Pivot Table. So this is an object which is already existing here in VBA, and then also our worksheet, which we can just call WS. So we say dim WS as worksheet. And yeah, then we select our current worksheet. Therefore, we can write here set Ws, equal to this workbook here our current worksheets, and then we select our worksheet in our example, which is just our VBA sheet. Therefore, we can type in here VBA. And then we can work with our Pivot Table. Therefore, we can write set PT, equal to Ws dot Pivot Tables, and then we can say which Pivot tables we want to have. And it's just this Pivot table. We can also give it a name here as you can see, it's just called Pivot Table one. So therefore, we can just insert it here Pivot Table one. And with this Pivot Table, we can just use a function, which is called the refresh table function. And this is our macro already. And we can also run our sub our user form also portable here with F five. And also, we can just compare it here because this was our old macro, which was, of course, then generated here with our macro recorder. So therefore, we can say, macro recorder, and then we can pretty much do the same. Of course, we can also make it a bit bigger here. Can also press the old key, then it's here position at long our grid lines. Then we can also go here back to our developer environment can also here, say with insert, then we want to paste also such a button, such a form control here again, the same like here. Then we can also just assign our macro, which have written here with our own VBI code, and then we can also give it a name here. Name it here, update Pivot Table as well. But here we can also say this is VBA code which we have generated. And let's try it here. So it seems like that it's working. Of course, we only see it if we change the numbers. So for instance, we can use such a high number like 10 billion, and then we can press here. And as you can see, it's working, we can also undo it here again, and then going back in our environment, and then you can see our PIV table is always updated. And the positive thing about it is that we just have less code if we write it on our own. So there's a lot of stuff the McroRcorder is also recording and putting here in our VBA code. But if we write our VBA code on our own, then we can just structure it a bit easier. 66. Using ChatGPT to Generate VBA Code: In this lecture, we're going to sign up and Open AI for JAD GPT in order to generate VBA code with JAD GPT, which we can use then in our Excel environment for Pivot Tables. So I'm already here on Google, and I can just search here for Open AI. Then I can click here on the official website of Open AI, as you can see here. Then you can just go here on log in at the top right. As you can see, we can just click here on signup and here we can just simply enter our email address, which I can do here. Then we can click here and continue and we can also add the password. Then it's important that we also verify our email, which was sent now here to our email address. After we have confirmed our email, then we can also just put in here our name or organization name and also birthday. Yeah, the final step, we also have to verify it here with our phone. So just entering here our phone number. As we can see, it was successful, and now we can either select between JathPT or Open AI models. So we're going to click here on ChatGPT, which is our language processing model to work with. And there we can also see some tips for getting started. So ChatGPT here is an interactive chat which can answer questions, help us to learn and write code. But we should not share any sensitive information, of course, and also don't check our effect. So let's go. As we can see here, we can interact now with Jet GPT. The model, which is used here is the 3.5 version, and this also will work here with later versions. So let's just try it here. We can say hello, and then we can also get an answer here, as you can see from Jet GPT, and then we can ask something like what are Pivot Tables. And then we can see the answer here. A Pivot Table is a data processing tool used in spreadsheet programs like Microsoft Excel, and then we can also see here more information about it. But we could also ask what is VBA? And also here we get the answer. VBA stands for a visual basic for applications. It's a programming language developed and designed by Microsoft and here more information about it. So in this lecture, we have signed up now for JADEPT and in the following video, we can work with it. 67. ChatGPT for Signing Up and Generating Pivot Table Scripts: So that's getting started now in this video with JCPT in Excel by using JAPT for writing VBA codes, which we can use here for our work with Pivot Tables. As you can see, I have here two macros already, and one macro here was created by our macro recorder and the second, a macro, which is basically doing the exact same updating a Pivot Table was created here by our own VBA code. And next, we also want to use JATCPT to do that. So we can just tell JDGPTPlease create VBA code for updating Pivot Table in Excel. So let's enter. And as you can see, we generate here our code for refreshing a Pivot Table. Then also interesting is that we get more explanations for it, which is quite helpful. And here we can see. Yeah. This is a VBA code, which was created now by Jet GPT for updating Pivot Table in Excel, and it's based here on this code, and this code, we can either select and copy here like that with the left mouse click and then right click and copy or just Control C, or also possible would just click on here, copy code. Then you can see here that we have to open our Visual Basic for Applications editor so the VBE, and then we can insert a new model, right click on Insert, choosing our module, and then we can copy and paste our VBA code. But it's also recommended to adjust it, of course. Let's copy our code. It's copied now, and then we can go back here in our Excel environment and we can go here in the ribbon developer and can navigate here in our visual basic editor. But we could also just simply press all in F 11. You can see, we're here back in our Visual Basic editor, and here it's possible now to make a right click here, insert module, and then we can just paste here our code which was written by JetGPT. So let's understand the code which was written here, and also we could just simply compare it to the code which was graded here from the macro recorder and also here from our code which we have graded. You can see, this is our macro. This is also the name of it, and also here, two variables are created also with the data type. Those are both objects, worksheet and Pivot Table, and then also here our worksheet object is set. And also interesting is here that we also have such a comment and also here Pivot Table object, and then we want to update here our Pivot Table. First of all, the worksheet object is selected this workbook dot Sheets. Then we just given here the name of the sheet, which is just simply called VBA. Also here is information. Please change Set one to our actual sheet name. So in our case, it's done already, and then we can see here the Pivot Table object, which was selected as well, and in our case, it even is named like that Pivot Table one. But we can check again going here in our Pivot Table, Pivot Table analyze and here Pivot Table, where you can also see here Pivot Table one, and it's correct. And then we simply just want to update our Pivot Table. So with the refresh table function, PT is our Pivot Table object. Refresh Table is the recording function. And as you can see, there is even built in such a message box in order to give out that the Pivot Table was successfully updated. And also here with VB information with the click button. Now let's just check it here. Also, we can try to manipulate our data again, just giving here a huge number, like, let's say, 10 billion, and then we can go back here in our VBA sheet, and also we can assign here a macro to a new insert object like such a form control object, such a button, and also we can place it here. We can also make it bigger. And here we can also just assign our macro which was written. So let's check again what the name was. And the name was just simply update Pivot Table. We can confirm it here with ok. And here, I just want to show it again, update Pivot Table. This is really our macro, which was generated here with ChatGPT because this was also the one with the message box. And then we can make it a little bit bigger here with the Alt key, and we can also name it here, something like update Pivot Table, but here generated by JET GPT. And let's check it now. So also here, we can just press the button. And as we can see here, it's updated automatically here now. And also we have here this message box where it's also written Pivot Table updated successfully. So of course, it's working successfully here, also with this code from ChatGPT. And as you can see, now we have three options. Either you can use the Macro recorder to also generate your VBA code. You can also do it on your own. Then you also learn more about coding and also can write your own code structure on your way, and you can also write a code, mostly just a bit leaner than the macro recorder is doing it. So just fewer code lines or the last option. It's also very smart to just use here ChatGPT to do that, and then you also generate her such a new code from Jet GPT, which you can use in Excel. 68. Congratulation on your successful Completion!: Thank you for completing the Pivot Table mastery course. Now you've learned how to build Pivot Tables on your own format and automate Pivot tables from beginner to expert level. You can now create dashboards, use Slicers and timelines, and even write simple VBA scripts. But don't stop here. Check out my other courses to keep growing your skills. Stay curious and keep exploring Excel. Well done.