Excel Pivot Table Tutorial: Wizard Usage and Basic Manipulation

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.