Simple Example
Let’s say you have a list of names, and next to them are a series of codes. The problem we’re going to solve is to get a list on a separate (or the same) sheet listing who has what codes associated with them.
We’ll create a simple spreadsheet like this:
Note that the columns MUST have headings for a pivot table to be created. In the Menu, click on Data and pull down to PivotTable Report…
When the first window comes up, hit next.
It will now show you the range for the data it’s going to use for the pivot table. It usually guesses right, but if not, simply drag across your data until it is correct. Hit Next again.
In the next window, you’re going to click on the button that says Layout:
The Layout portion is really weird. The trick to getting what you want is to simply experiment. You’re faced with a window that has a rectangular area that says Data, the bar across the top of it says Column, and the down the side it says Row. There’s also a box floating out in space that says Page. Ignore Page for now, we’ll used that in a more advanced lesson. Remember our problem statement now – we want to know the names of everyone who has a certain code next to their name.
On the right side you’ll see all of the column headings in your table range, in our case it’s just two: Name and Code. Drag Name over on top of Row, and drag Code on top of where it says Column. Finally drag Code again, this time dropping it into the center Data field. Then hit OK.
Click Finish when you get back to the previous window, and your pivot table will show up in a new tab.
You can manipulate the Pivot table to sort all of one code together, or you can click on the pivot table, and a little menu will jump on screen that lets you go back into the Pivot Table Wizard to play around with the format. That’s the same place where we originally set up the fields for the pivot table.
One thing to remember is that Pivot Tables don’t naturally update when you change the data that is driving them. To refresh the table, click on it and hit the red exclamation point in the fly in window.
And that’s your first Pivot Table – congratulations!
