# How to make a pivot table

00:00:00,030 --> 00:00:19,795
Excel's Pivot Table option is a powerful way to summarize complicated data tables like this one. It's hard to overstate how important pivot tables will be when you're working with large data sets in future. Making them can be confusing at first but this is an extremely valuable skill to master.

00:00:19,795 --> 00:00:30,482
One common way that statisticians use pivot tables is to make a frequency table for one variable to see how it changes through time and that's what we're going to do here.

00:00:30,482 --> 00:00:39,937
This is what the basic data that you've downloaded at the beginning of this exercise looks like: column C is store type and Column K is time.

00:00:39,937 --> 00:00:56,641
We want to find out how many observations there were of each type of store at two times: December 2014 and June 2015. Clearly you can't see that by looking at this table because there are more than 2,000 observations in it.

00:00:56,641 --> 00:01:24,974
It's always better to make a pivot table on a blank worksheet. So click on the little plus button at the bottom of the worksheet that you can see and you see it opens up a blank page. Click on 'Insert' and the first button on the toolbar is the pivot table button. It brings up a box. We'll use this box to specify what data we're going to summarize. You can do this by selecting the cells directly.  

00:01:24,974 --> 00:01:34,745
So click in the table range space and then click on the tab at the bottom to go back to theoriginal worksheet called 'Data'.

00:01:34,745 --> 00:01:57,173
Select cell A1, scroll to the bottom of the data, hold down the shift key and select the bottom right populated cell it should be L2176. Click on OK to exit the box. Click back to the pivot table tab. There's now a box on the right-hand side.

00:01:57,173 --> 00:02:26,838
Again don't worry if this looks confusing at first. The top part of it has the names of all the variables in the data, and in the bottom part there are a smaller boxes. The top right one is where you choose the column variables from your list and a bottom left one is where you choose the row variables, and in the bottom right box it's where you choose which variables are inside the table.

00:02:26,838 --> 00:02:59,611
So we're looking at store type and time period and we want to count the frequency. We select store type and drag it into the row variable box. Do the same for time - this needs to go into the column variable box, and a counter store type is in the values box.Excel does all the hard work to summarize the data.There might be more data than you want and there is in this case but remember you can always use filters to hide rows and columns that you don't need.

00:02:59,611 --> 00:03:40,266
So click on the down arrow in column Labels, unselect the box for March 2015 and we have the table we set out to find. It shows the number of stores of each type in December 2014 and June 2015. A lot of people find pivot tables a head-scratcher at first so don't worry. The best way to learn them is to experiment.Think about what you want to summarize and see if you can do it. Remember always make a pivot table in a new tab. You can label your tab: do this by double clicking on the tab name and giving it a title.