Using Pivot Tables to Summarize NBA Team Salaries

The power of Excel is its ability to summarize data with an easy to use interface. The best example of this is Excel's PivotTable feature.

In order to use a PivotTable you need to have your data stored in a table. A table consists of rows of data, where each row represents one entry of data. And columns that help further define the data. For example, below is a table of data I put together, back when the Lakers were struggling and I was looking forward to free agency (My how times have changed :) ).

The first column has the player name. The second has the team name for that particular player. And the next columns are the salary that player makes in that particular NBA season.

Suppose you wanted to quickly determine how much salary dollars each team is spending in a given year, you could write a few SUMIF formulas, one for each team. However since all the data is already nice and neatly stored, you can use a PivotTable.

To do so, put your cursor anywhere in the data table, then go to the Insert Pivot Table wizard (Alt + D, P). The first screen asks you where the data you want to summarize is (either in a workbook or another data source) and whether you would like a PivotChart included as well. Select Excel as data source and just a PivotTable for now and hit next.

The wizard should have automatically selected the complete data table your cursor was sitting in (if it did not you have rows of missing data that it did not think was part of the table. You should cancel the wizard, clean your data and start over).

Lastly the wizard will ask you where you want your PivotTable to be inserted, select a new worksheet. You should start with a blank PivotTable that looks like this:

The pivot table has 4 parts to it, which are populated by the fields in your data. A field is a column of data from your original table. Filters, which live at the top of the Pivot Table, filter the data that is summarized below. Rows list all members of a field in rows. Columns list all members of the field in columns. And most importantly Values, are numerical summaries of all the filters rows & columns that have limited your data. The summaries of the values can be changed to do different statistical operations (SUM, AVERAGE, COUNT etc...)

To get our salary by team, we are going to move the 'TEAM' field into the Row area of the pivot table. And the '2008/09' field into the Value part of the pivot table. And voila, you have the following table of salaries spent in 2008/09 by team.

Note that since the data included Free Agents and the salary that the teams who cut them still contractually owe them. The 33 Free Agents in the data add up to the highest paid group of players in 2008/09.

As mentioned SUM is only one of the many summaries a PivotTable can compute. To find our how many players were part of each team, simply right click somewhere in the PivotTables Value section, go to Value Field Settings and in the "Summarize value field by" section select COUNT. You should get the view to the right.

Note, that the Top left area of the Pivot Table automatically includes the summarizing operation and the field it is performing on.

You can find the data and sample PivotTable here so you can play with on your own.

As always, feel free to leave me your questions comments and future blog ideas.

-Danny