Pivot Tables get your data talking
You may have heard that Excel Pivot Tables are too difficult and should be attempted only by the most advanced users. Not true! Pivot Tables are easy to create and you can use them for everything from answering simple questions to performing complex analysis.
What is a Pivot Table?
One way to explain a Pivot Table is to use a map of the world as an example. Take a map of the world and superimpose it on a cube rather than a ball. Now, each time you pivot the cube you get a look at a different piece of the world. Pivot it again and you see yet another view of the world. You are still holding the whole world, but just looking at a piece of it. Think of your data as the world and a Pivot Table as the ability to see just a piece of it without removing it from the rest of the world.
Before you start pivoting
First, you need to make sure your data are in good shape, so your Pivot Table can deliver the answers you want.
Identify the data in columns by assigning a title, such as a name or an account number.
Do not leave any rows or columns blank. Check for this anomaly by clicking any cell in your data and then pressing Ctrl+A. Zoom out and see if you selected the whole table, or if you left out parts. If you omitted any, chances are you have either a blank row or column.
Ensure data types are consistent in each column: all numbers, all text, all logicals (True, False).
Getting started
Click on any one cell in the middle of your data to create your Pivot Table. On the Insert tab, Tables group, click the first button on the left labeled Pivot Table. Verify that the dialog box shows the complete Table/Range. Click OK on the next dialog box. That’s it! The easiest thing that can go wrong is that you clicked in the wrong place. If you don’t see your full range in the Table/Range field, hit Cancel, go back and make sure you did not click outside your data in a blank column or row or that you have not selected (highlighted) multiple cells.
Now what?
Think of the big box below as your canvas, the field list on the right as your paint and the four boxes in the lower right as your brushes. First, you need paint on the canvas. The example shows fourth- and first-quarter data, in that order, by product. Click those boxes in your “paint box.” Now, using your “brushes,” click and drag fourth- quarter above first. Voila! You’ve created a Pivot Table!
You’ll notice filter arrows in the “canvas” pivot report section. Hover over the field names in the “paint” field list. Use these to further narrow the piece of the world you want to see.
