[MUSIC] Welcome everyone to our next lesson which will be about pivot tables. Pivot tables allow you to extract and summarize large detailed data sets and transform it into useful easy to read information. Pivot tables also automate formulas and functions such as sums or counts to create a summarized table of your data. There are many benefits and uses of pivot tables. Pivot tables can be as simple or as complicated as you need them to be. They are useful to make a quick summary table but can also be customized using self-formatting or importing your own formulas. In work settings, they are often used as reports or dashboards that are extremely useful for internal tracking, as well as presentation material to show to clients. We'll get in to how to create a pivot table once we get into our exercises but the process is easy to learn because the tools in Excel make it a very visual process. Creating a pivot table is as simple as highlighting your data, clicking on the pivot table function in the ribbon, and clicking where you would like the table to be. Lastly, you drag your items from the field list to the appropriate filter, row, or column to make your table. So now we'll start off in question 1A to actually make a pivot table. So first, it's always important to understand your data set. So we'll go to the data set tab, where you see a table of sales information by distributor name, country, product, sales channel and so on. You've seen this sales data in a few of our past Excel lessons. So the first step to making a pivot table is highlighting our whole Data Set. So an easy way to do this, is to click the top left cell, hit the control and shift key, and hold those and then hit the right arrow and the down arrow. So you can see, we easily just highlighted 110 rows of data. The next step is to go into the insert ribbon and click Pivot Table and you'll see that the table that we just highlighted is already in our table range. Below, you can see that pivot tables will default to go to a new worksheet, but since we have a worksheet where we're working, I'm going to click Existing Worksheet and click this button to select our cell. I'll go to the section one create pivot table and click right below and hit enter, and now click OK to create the pivot table. On the right, you can see the pivot table field list. You can choose out of the columns of your data set what you want to be columns, rows, filters, or values, in your pivot table. For example, let's say we wanted to see revenue by country, so I'll click country and you can see that it autopopulated to rows. If we wanted it as columns, we could drag it over to columns, but you can see that doesn't look great, so I'm going to put it back to the row section. And then I'm going to scroll down to revenue, and it will autopopulate to values. If you click the little triangle next to your revenue, you'll see a value field settings option. In here you can choose sum, count, average, or some of the other Excel functions but the pivot table will try to guess what you want. In this case I do want a sum, so I'll click OK, and now we can see that we have the summary of revenue by country. It's always important to remember that pivot tables do not update automatically. So you should always refresh your data to ensure that your table is current. You can do that by going up to the analyze ribbon in your pivot table tools and click Refresh. And you'll see that now we have the most current pivot table. Additionally, you can change the data that you are working with, and you can do that by looking at the PivotTable Tools ribbon again and click on Analyze, up here and Change Data Source. So if we had another tab with another data set, we could change the data source to provide the same pivot table with the different set of data. Now in problem 1b, you can make a pivot table with our sales data. Try moving around different fields to the column, rows and values section to see how it affects the pivot table output. [MUSIC]