- Today, we're deep diving into the world of Excel pivot tables. And I think saying pivot tables-- pivot tables? I don't know what it's called. So you're going to have to bear with me during this whole video because I don't know how it is pronounced. This is one of the most powerful tools that you can use in Excel. 

Pivot tables in Excel are used when you have really big data sets with multitudes of rows and columns. So it's a way to easily manipulate that data without ruining the original data somehow. So let's jump into Excel, and I will show you how you can create your pivot table, how to format it, and then how to create nice sliders and graphs so that you can easily use it and easily analyze any type of data that you would ever want to in Excel, basically. 

So the first step about this is something that you do before you even create your pivot table, and that is that you need to check this data so that it's actually the correct format. So you need to make sure that it is in a table format so that each column represents its own data-- that you have a clear headline so that you know what the data is and that the formatting of the actual data is correct. So you need to make sure that this is a date format and not something else. Because with dates, you can do a lot of cool formatting, but it only works if it's actually formatted as a date. 

Now we will actually create the pivot table, and it's really simple actually. So you just mark all of the data and you go to Insert, and then you have pivot table. And click OK-- cool. So here we have our pivot table, and it's empty. But now we will do something about that. 

So what you do is that you have these four boxes. And this is what you will use to format the entire table. And I would pause the filters a bit in the beginning and then just use the rows, the columns, and the values. So what you do is that you basically have all of these column headers from the other sheet. So you the order ID, date, category, product, et cetera-- order ID, date, category, product, and all of these others. 

So they have been imported into the pivot table. And you can now drag and drop these into these four boxes. So you can drag the date to the rows. And you can see now that all of the different months show up here. And then you can also drag-- for example, we had the countries. 

So let's drag the countries to the columns. But there's nothing in this table now. So what you do is that you need to add the values. The values is what will be in all of these boxes where the actual numbers and data will be. And in this case, I think number of units sold could be our values. 

So here you go. So now we have a map of how many units were sold in each of the different countries for each of the different months. So now we can easily see that in January, there were 15 products sold in total. In February, there were 23. And in Sweden, in total this year, there were 17 products sold. So this is a really simple, quick way to understand the data that you have without having to do any manual calculations or anything like that. 

So as you can see, the Rows box is basically what will end up in each row and the Columns is what will end up in each column. The Values is what will end up in each of the boxes where the rows and the columns meet. And then for the Filters, what you can do then is basically that you can filter this data. 

So for example, we had different order IDs. So if we want to look at a specific order-- for example, order number 4-- we will just choose order number 4, click OK, and then we only get the data that is available for order number 4. So then we can see that that order was made in February-- February 18-- in the United Kingdom. So play around a bit with this and see how it works. 

The easiest thing is to only have one of your categories from up here in each of these boxes, because if you start to have more, it will get a bit complicated and it will start looking very weird-- for example, like this. So now the data just doesn't really make so much sense anymore. So start playing around with the columns, the rows, and the values. 

And then the filters is quite an easy add-on. You don't even need to have columns, so you can just have rows and values. That's the most simple version of a pivot table. And what you can actually do is that you can double-click on this data. 

So for example, Biographies-- we can double-click that. And then we can see what data this is made up of. So we can see that there are two different orders that has biographies and the total sales values of those and that the orders were done in Canada and Australia. So that is what happens when you double-click on a cell in this Excel pivot table. 

Another thing that is really important to know is that in this value box, you can change what type of values it is. So now we have the sum of the total sales-- so that is all of the sales added up. But we can also choose, for example, the count of sales. So then we don't have the actual value, but we know how many sales there were. We can also choose, for example, the minimum or maximum-- so we can see the maximum value of the sales. 

So here we can see, for example, smartphones-- we have $4,800. That was then the order that had the maximum sales value. If we then change this to minimum, we can see the minimum sales order. So the minimum was $1,200. So this is a really simple way to extract data very quickly without having to do those calculations. You can just get the sum and the count and the average and the maximum and minimum of all of these values in a very quick and simple way. 

If you choose to have more things in the rows-- so for example, the category and the product belongs together. So you can see here that under books, we have different type of books. So when we do more things in the rows, this is basically a way of using subcategories to sort data in a logical structure. All of the products belong to one category. 

So here, we have it simply structured between the different categories. And under all of these categories, we have the subcategories. And we can use these plus and minus buttons to hide and show data in each of these. And if you ever lose the right-hand side, what you do is you then click in the pivot table, and then you see it again. And if that doesn't happen, you can go to Pivot Table, Analyze, and then this field list toggles if you can see it or not. 

And you have these plus and minus buttons that shows these if you want to be able to hide or show the data in the rows. You can also format the table to make it look a little bit easier to look at. So for example, this can look quite messy. So we have-- in the design part, we can change the color of the pivot table. We can choose to have, for example, banded rows so then it's easier to read all the data. 

We can use the banded columns as well if we want to or use that. You can also make it a bit easier to read to put in some blank rows-- insert blank line after each item. So now we have some space between the categories. You can use it in compact form. You can use it in outline form. You have a bit of different ways to show the data. 

And now to the more fun part-- so now we can actually manipulate this data bit. So for example, in the rows, now we see it from the beginning of the year to the last part of the year. We can sort this data. So we can sort it newest to oldest so we can change the order of it. And this doesn't change the initial data. It just changes the way you see the data. 

We can also use filters. So here, we have the columns. So maybe we don't want to see all of these countries. Maybe we just want to see a couple of them. So maybe let's choose just the United States and India so then we just see the data that is relevant to those two countries. And something else that you can do is that you can actually group data. 

So what we might want to do is that we might want to look at Europe versus the rest of the world. So then we can pick and choose Sweden and the United Kingdom, and then we just right-click and click on Group. So now as you can see, we now have group 1-- that is Sweden and the United Kingdom. And then we can pick and choose all of these others. So I just click on it and then I press Control and choose the other ones as well. 

And then I right-click again and click on Group. So now we have the first group, which is Europe, Sweden, and the United Kingdom. And then we have the group 2, which is the rest of the world. So now we can easily see that the number of units sold in this year was 90 for the rest of the world and 50 for Europe. So we can see that Europe was a big contributor to all of these units sold. 

And if you want to get away from this, you can just mark this. And you right-click and you just ungroup, and the data is back to what it was. Imagine going into this and trying to find all of the Europe data. It would be super tricky. So it's so much easier to do that in a pivot table. 

And if, somehow, the original data was to change-- so for example, we add another sales that happened in another country. So now we have a cookbook that was sold in Japan. Well, how do we get this into the pivot table? Well, we go back to the pivot table. And if the data that was updated was in the actual data that you picked before, you can just click Pivot Table, Analyze, and then refresh. Then the data would go in. 

Since we added a new row that is not included in the data from the beginning, we need to go into Pivot Table and analyze and then change the data source. And when we click on that, we can see that now the row 25 is not included. So if we just change this to go to row 25, we can click OK. And now we see that we have one unit sold in Japan as well. 

So it's also very easy to change your data in retrospective so you can go back and forth if you find some errors. If you see that, oh, these calculations don't add up, then you can go back to your original sheet, change the data, and then just update it in the pivot table. And now for the even more fun part, we get to add some cool sliders and timelines and stuff to make this data even more easy to have a look at-- also for someone who maybe hasn't worked with pivot tables before. 

OK, so we can insert a slicer, which is basically a way to simply filter through data without using these arrow boxes. So we can add one for product. So now we have this box, which we then can expand a bit. And you can see theser are all the different products that we now have in this pivot table in the data that we have. 

And maybe we just want to look at basketballs. Then we click on the basketballs, and then we only get the data that is relevant to the basketballs. Maybe we want the basketballs and the hats. Then we control-click on the hats, and now we have for both basketballs and hats. 

And we can pick and choose so much based on this. We can add another. We choose all of them, and then we can add another slicer if we want to with just the category. So there we can have the category as well, only looking at clothing. And if you see now when we have the clothing here, you can also see that the products that are available are the hats, the jackets, jeans, and t-shirts, which are the clothing. 

So this is a really cool way to simply pick and choose between data. So this is a way for you to build dashboards if you want to. You can also change the formatting of these if you want to change the colors. OK, cool. Let's go back here. Let's try to add a timeline instead. 

So we go to Analyze here, and we insert a timeline. And the timelines are only based on the dates-- shocker. So now we have a timeline where we also can pick and choose the data from. So as you can see here now, we can easily say, OK, now we just want to look at January to March. 

Now we just have that data up here. We only want to look at June. We just have June data up here. And if we have more years, we can compare years. Now we only have from 2023, so it doesn't make sense. But we can look at the quarters Q1 and Q2 only-- and the months and the days as well if we want to. 

We can also make dynamic graphs. So if you go here, you can also add a pivot chart. And the pivot chart that you add is then based on the rows and the columns that you have up at the moment. So here, we can make a clustered column chart. That was a weird thing to say, clustered column chart. 

OK. And we can add that. So now we can see a very nice graph-- let's move this down here. We can see a very nice graph of the dates and what countries the orders are from. Let's try something else instead of the dates. Let's do the category. So now this chart is based on, then, the categories and the countries. You can see the categories, the books, clothing, electronics, and sports, and the countries. 

And you can manipulate this data as you would with any other type of charts that you do in Excel. So for example, you can add the data labels to see how many sales there are. And now if you want to see more of the dates-- because now we only have the dates from January to June-- you can pick and choose the whole year, and then you get the graph with the data for the whole year. If you only want the data for books, you click on the books, and then you can see how many books were sold in each country. 

Do you see the potential in this? This is an amazing way of manipulating data compared to just looking at it in this table. I hope that you, with this video, learned almost everything you need to know as a beginner using pivot tables for the first time. I really hope you liked the video. If you did, then please press that Like button, and I will see you in my next video.