Pivot Tables in Excel can be very quick and powerful data analysis tools. Pivot Tables allow the user to arrange a contiguous range of data in a variety of columns and perform multiple calculations on an aggregate level. Click here to download the Excel file used as an example in this tutorial.
Using the Pivot Table Wizard: highlight the data range (including column headers) and go to Data > Pivot Table and Pivot Chart Report. This will bring up the window in the screen shot below.
Make sure the boxes next to “Microsoft Office Excel list or database” and “Pivot Table” are checked. Click Next.
A pop-up will ask you, “Where is there data you want to use?” If you highlighted the data as specified in step 1, the Range should already be pre-populated using the data range you highlighted.
Click Next.
Now, you will be asked if you would like to place the Pivot Table in a new worksheet (in the same file) or in an existing worksheet within the file. This is personal preference and can change based upon the task. For the purposes of practice, I recommend selecting “New Worksheet”.
Click Layout.
The Layout screen will give you a Pivot Table “skeleton” where you can drag and drop data fields that were within your highlighted data range into the Pivot Table layout in whatever way you wish to display the data. It will take some practice understanding how the data will display differently when placed in the “Page”, “Row”, “Column”, and “Data” sections of the Pivot Table skeleton.
For this test drag the field “Player Name” and drop it into the ‘Row” part of the layout. Next, drag the fields “Pass TD“, “INT“, “Comp PCT“, “QB RAT” and drop them into the “Data” part of the layout.
The Data part of the layout requires you to specify an operation. What this means is you will need to state whether you want the data to be counted, added, the average, or the maximum or minimum values.
Double click on the “Sum of Comp PCT” button and it will bring up the pop-up in the image above. For this example, click on “Average“. This will return the average QB Rating for each player. If you were to choose “Sum” the result would be the sum of the QB Ratings for each player (which is why we changed the operator to Average, because the sum of QB ratings is a meaningless number). If you were to choose “Count” it would count the number of QB Rating values for player.
Click OK twice and then Finish.
The pivot table result should look similar to the image below. This is a basic Pivot Table that can be modified and enhanced in many different ways.
Next, put your mouse cursor over the gray pivot table cell that says “Data” and hold down the left button. Drag the “Data” field over top of the cell that says “Total” and release the left button. This should take the four pieces of data and arrange them in columns as opposed to rows as seen above. The result should look like the table below.
By clicking on the drop-down arrow next to the field “Player Name” in the image above you can select or deselect pieces of information. This is helpful if you want to display only portions of the data. Select/deselect by checking/un-checking the boxes.
Note: when your cursor is inside a cell within the Pivot Table, a “Pivot Table Field List” will be present on the screen. This allows for dragging additional fields into the pivot table or for removing fields from the pivot table. Use the drag and drop concept.
By using the Pivot Table Wizard and the few skills in this tutorial you can experiment with a variety of layouts and possibilities with Pivot Tables. It is helpful to first gain an understanding of how pivot tables organize information so that you can manipulate the logic the software uses to your liking. Just keep making pivot tables and you will get a feel for how it all works and what you can do with them.
Interested in learning more? …check out the tutorial page for some more Microsoft Excel techniques.
Loading ...
5 Comments
Excellent tutorial. I not only understood it, I was also able to implement it, and thats saying a lot.
Well I tried to put this tutorial up to the Excel 2007 challenge. Obviously there are differences.
To create a pivot table you must go to Insert –> Pivot Table. Beyond that, I cannot seem to find the wizard and it seems Microsoft may have done away with it.
Instead, by default, excel creates a new worksheet and it gives you the the layout where you can drag and drop your fields. All the data shown in your tutorial came out the same. Unfortunately I could not get the Data Columns to give me the gray drop-down menu so I was unable to arrange the data in columns instead of rows.
I need to spend some more time poking around in Microsoft Excel 2007 so I can further figure out the differences.
I have not yet experienced Excel 2007 so I cannot say. I think that the drag and drop concept should remain the same for re-arranging the data from rows to columns – you don’t need the drop-down arrow to do that – they are unrelated exercises. I need to get m hands on ‘07 so I can check it out in all it’s million-row glory.
very good tutorial, thank you
Excellent tutorial, and good choice of sample data to appeal to sports fans. I was fooled into thinking this was a more formal training tutorial until I read some of the self-deprecating bios of your authors. OK, I see where you’re coming from, and I appreciate the tone with which it is delivered. Still excellent, but may I point out some tiny errors?
When I first downloaded the sample data QB.xls, I was a little confused that the first couple of steps had already been done for me. After backing them out and re-creating the starting state, it made much more sense.
Also, I see a very tiny typographical error. In the paragraph following “Click Layout” it says “…drag and drop data fields that WHERE within…” but I believe you meant “…drag and drop data fields that WERE within…” Other than that, you have a highly professional, extremely helpful product. Thanks!!
One Trackback
[...] Excel Pivot Table Tutorial: Wizard Usage and Basic Manipulation [...]