What is a Pivot Table?
Pivot tables Microsoft Excel’s very useful feature. They are used to analyze, summarize and present especially large data sets. Pivot tables can make it easier to organize and manage data to present the data by extracting the information that is useful to us.When we use Pivot Tables we don’t add ,subtract or change information in our data sets, we just simply present it in a more meaningful way.
Lets see an example when can we use a pivot table.I am using kaggle to find my data set, a platform for data science competitions. Kaggle is free ,new users just need to sign up and create a new account. Kaggle
Pick a data set that you like download it and open it in Excel.
So my choice was to download a data set about Global Shark Attacks.If you would like to download this data set just click on this link.Shark attacks Download
So here is the spreadsheet shows the date , location ,area etc. about every shark attacks that has been reported in the world.
There are a few important rules we have to follow before we create a pivot table.
- Data should be organized in columns with headings.Headings must be unique and descriptive otherwise our table will get really messy .
- Headings must be on the top row above your data.
- There should be no empty columns or rows in the data set.The reason is because Excel find the end of the data by looking for empty rows and columns and if you have an empty row it might be just where your data will end even if you have many more rows in the data set.
- Make sure that all of your data in each column have the same format.Especially when your dealing with dates.
Now that your data all tided up and organized your ready to create the Pivot Table.
So we have the data in Excel.To select all cells on the worksheet click on the Select All button.
Click the insert button on the Toolbar than click Pivot Table.A new dialog box will pop up that shows the Table/Range value which is basically the table rows and columns that are selected. You can also have a choice where the new Pivot Table should appear.I choose the default option which is New Worksheet.
This is the New Worksheet that will be presented to you.
If you would like to go back to your original data ,in the left bottom corner you can find them on different sheets.
So let’s say that i would like to have a table that contains every shark attacks between 2000-and 2016 in the world.Also I would like to know how many attacks occurred at what locations, the types of shark involved and if the attack was fatal or not.Because my data is dated back to 1853 I need to select the cells that are contain only 2000-2016 data.So I have 836 rows from the top that I want to select and I would like to select every column.This time I wont click on the select all button, I will click on the toolbar insert than click on pivot table.The little dialog window will pop up again.Where it says select a table or range I will type in the cells I would like to select exactly 836rows.
On the right side of the screen is located the Pivot Table Field Sheet.Just a quick note on Pivot Table Field Sheet.If you click on any cells outside your table this the Field Sheet will disappear.Don`t panic!To get it back just click on your Pivot table and you will get it back again.
In the Field List, place a checkmark next to each field you want to add.
Using the Field Sheet you can organize your data whichever way you want.Unfortunately there has been a Total of 80 shark attacks occurred between 2000-2016 I wont show you all the data.
I am going to make another Pivot Table that will only show the total of shark attacks by countries.You can drag and drop the fields wherever you want to place them in the field sheet.
And now I just totally got carried away…..
So this is it.There are many other ways to use pivot tables and many more techniques and tricks but I wont show them in this blog.