Excel is surely a cavalry of features. And if we count the ways in which each feature can be useful just makes the whole think almost countless. However, for every feature one must know what it is, why and where we might need it and finally if we need it then how to use it in the given situation. So let’s have a panoramic view of Excel’s Pole Star -Pivot Tables.
- summarize the information so that it can be digested
- present the information so that it can be easily understood
And this is exactly what pivot tables do to data spanning hundreds or more rows and columns. Very cleverly it divides the whole data in relevant pieces on-demand and summarizes and presents the data accordingly. If pivot table is still too alien for you then let following examples will help you settle:
- Suppose you are given something in your hand in a pitch dark room. What will you do? You will examine the object by moving your hands around it rotating it clockwise, anti-clockwise and in all sorts of direction until you start guessing it. This is what you do with pivot tables. You rotate, move, scale, summarize the data the way you want to make it according to your needs.
- Ever played or watched children playing with Lego building bricks or blocks? You assemble the small, scattered bits and pieces and put them in shape by joining the right block at the right place. And if something doesn’t fit child rotates it and try to press it again and the child keep on rotating it until it fits.
- Sometime when you try to open door lock inserting the key and if it does not work you pull the key rotate it and push it in the key hole again and lock clank opens.
Looking at the literal meaning of the word pivot, I came to know that the word itself is so dynamic and versatile that it has so many uses and meanings but one thing was common; rotation. It can be used both as verb or noun. For example:
- a shaft or pin on which something turns
- A person or thing on which something depends or turns; the central or crucial factor.
- To cause to rotate, revolve, or turn.
Pivoting Back – How Excel works!
Before we move ahead to pivot table lets step back a little bit to understand how Excel stores its data.
Excel’s biggest reason why it excels at what it does is basically its ability to store data in very distinct manner. Excel stores information in a cell. A cubical that is an individual entity in itself.
Everyone of us have home. And as everyone live at a different place having address makes things easier in our lives. But we do not do everything at home. We have separate place for office, to party and to rest.
Similarly in Excel we can have large amount of information of different nature therefore, we cannot fit all of the information in just one cell (cube). So we need more cells. But think for a moment that if you don’t know where you put your information then it would be very difficult for you (just like you forget address back to your home).
To solve this issue we have rows and columns. Rows are horizontal arrangement or classification of cells whereas columns are vertical. Similar type of data is grouped or arranged in rows and columns whereas each cell has its own address as well.Where particular cell and row intersects, an address is marked. For example A1, F19 etc.
Now as as each cell enjoys certain level of independence it helps us to rearrange the information in any way we want. Remember the game of match sticks where you had to make an additional box by moving only two sticks? In the same way pivot table rearranges the data in cells to make it meaningful. Following slides on pivot table makes it even more clear that how pivot table slice and dice and splice again!
So here is my attempt make it as simple yet detailed so that anyone who is just starting on Pivot Tables in Excel can understand the science behind this excellent Excel feature.
The next episode will conclude this two part series in which we will learn how to make Pivot table from simple data.