Pivot Tables in Excel

Pivot tables are one of the most useful tool of Excel when you need to go through big Data.

Lets take the data from our Farm Orchard and the data we collected over from 2010 to 2014.

Our data includes the amount of apples per tree and per year, the name of the harvester and the health and number of liter of water the tree received.

Here the table.

harvest apple pivot table



We want to know which Harvest had the most apple over the years.

So to this we need to make a pivot table that will agglomerate the various data in this table.

First we select the area we want the table to used the data from and then we press the PIVOT TABLE button in the INSERT RIBBON.

pivot table

The following window will open.

pivot table

Just make sure the range is correct and press OK.

Usually it is best to create a pivot table in a separate sheet. Its cleaner.

Automatically, a new sheet is created looking like this.

pivot table sheet

On the right you see the various headers of your table. Now what we want to do is to MIX correctly these fields and make a new table out of it.

TIP: YOU NEED TO ASK YOURSELF THE CORRECT QUESTION. WHAT DO YOU WANT TO DISPLAY.

In our case we said, we want to know what each harvester did every year.

This means we need to have the harvester names as the FILTER.

The Trees or Apples will be the ROWS and the values of each year will be the VALUES.

By Sliding every field into the correct area you get:

pivot table fields

 



and the following table is created.

table pivot

Now you can see in the A row that Harvester is a field you can select. Do this:

fields pivot table

By selecting JOHN, you will see which tree John harvested over the years and how many apples.

pivot table

Now you can do this for all the harvesters but you could have this in one table only.

By changing the filter like this. The harvester is the rows and the apples are the filter. Or you could just not put the apples.

filter pivot table

Following table will appear.

pivot table

So you can see how easy it is to find some complex data in a big table.

Here you could also compare the number of liters of water and the health of the tree and see if there is a correlation.

 

So that's it for the pivot table in Excel. 

These were only some basics. Now you will learn by doing or if you want to become a Pivot Table Expert and go into the details and secrets of pivot tables, you should visit to the site SpreadSheetTo that has 12  chapters, has an exercise file, a video - and it's updated for Excel 2016