Introduction to Pivot Table
Pivot table is wihtout a doubt one of the most loved features in excel. It makes data analysing as easy as childs play. And pivot table does it with one click.
Some of the things can you do using pivot table:
- You can instantly arrange your data into a table with rows and columns of your choice.
- All columns are automatically filtered and you can add any filter to your pivot table at a whim. Also your table will have zebra lines by default for easier reading.
- You can group your data for summary view or ungroup it for detailed view.
- You can drill down your data with just a double click on related data cell.
- You can add calculated columns and easily enrich your data.
- You can apply fancy conditional formatting and make your reports look very pleasing
You can convert your pivot table to a “pivot chart” with one click and have a nice chart with filters and stuff. This chart will get effected by any change in your pivot table dynamically
Now lets create an example pivot table and play with it…
I’ve arranged a sales transactions list for 2012. List consist of 8 columns and 1000 rows. All dates, names, etc. are ramdomly generated. You may find column names a little odd but I did it on purpose to give it a more database like look. Here is how our data looks:
To insert a pivot table, all you need to do is to select your list of values (including headers) and click insert pivot table button from insert ribbon. If your list is formatted as a table, than clicking on any single cell in table is sufficient for pivot table to include all your values.
If your list is in table form, you will see table name instead of cell range in pivot table prompt.
After inserting pivot table into your sheet, you will be greeted by an empty worksheet and a field list. On top portion of the field list, you can find names of your columns from your source data. You will drag them to the boxes in lower portion of field list to customize your pivot table.
Lower portion consists of 4 boxes. From left upper corner to right these are; filter, column, row and value areas.
Now you can do alot of combinations depending on your needs. And you can do it easily by dragging and dropping headers into boxes.
Lets see some examples of what kinds of analysis we can do on our list using pivot table.
Here is some example analysis that can be done from this data using pivot table:
Which customer representitive did how much $ worth of sales to each customer.
Which customer bought how many of which item.
How many of which item sold by which customer representitive (with transaction date filter).
To each customer, which customer representitive sold how much $ worth of which item.
Examples can be increased with different combinations of rows and columns, also with calculated fields and/or conditional formatting. I’m going to cover all these topics in future posts. For now, you can download example pivot table and try things yourself.