Using PivotTables for Fantasy Sports Data

The PivotTable can be — in the right hands — one of the most powerful and useful tools available to an Excel Wizard. What does a PivotTable do? It reorganizes raw data into malleable tables. This allows us to look at the same data, but through a different lens.

It’s kind of like the difference between a cup of cold froyo in its original form — with a layer of peanuts, a layer of chocolate- and marshmallow-flavored froyo, and a layer of chocolate syrup — and a cup of froyo stirred and mixed up so that the sauce and nuts and yogurt are more integrated. It’s all the same components, the same flavor, but in one form, you can see them more clearly.

Before we can use a PivotTable, we need a question and a dataset that requires a PivotTable. (Throwing a PivotTable at a random problem will do you nothing.) The typical question I have that requires a PivotTable: Who performed best over the last three seasons?

And the question is more complicated than just “let’s add up data from 2012 through 2014.” I want to weight the years 5-4-3, meaning 2014 — being more recent — is 1.25 time more important than 2013, and 2013 is 1.3 times more important than 2012. This is a common weighting that Tom Tango uses a lot, most notably in his Marcel projections.

So let’s use a PivotTable with my Ottoneu fantasy team’s data so I can best predict my targets for the upcoming year. I’m going to head to my Ottoneu league’s home screen and then browse to the “Sortable Stats” section, but just about everything here can be replicated with the FanGraphs leaderboards and WAR instead of fantasy points.

I’m going to next choose to look at split season from 2012 through 2014:

Then we export the data to a CSV:

Opening that data, we get a bare bones spreadsheet somewhat like this:

Then in the Insert section on the top ribbon, choose PivotTable:

You will then receive a popup window titled “Create PivotTable.” I never use the first section, but will typically leave the second section alone too. Basically, if you want, you can keep the PivotTable in the same sheet/tab, or you can have it create its own new tab. Since PivotTables frequently change shape, I rarely want it anywhere near my raw data because it could accidentally overwrite key stuff.

So hit “OK.”

This will result in something this-ish:

My original question is: “Who performed the best over the last three years?” That means I want my rows to be each individual player. To do this — and avoid problems like having two players with the same name — I will drag the “playerid” field to the “Row Labels” section:

You should now have something unhelpful looking like this:

Now we need to add the column headings (“Season”) and the data we want displayed (“FPTS”). Again, this is just a simple drag and drop operation:

So we end up with something like this:

This is great! But let’s get those names in there now. Drag the “Name” field into the Row Labels sections, adding it just underneath the “playerid” field. This should result in a real messy display where the names appear under the numbers and have completely unnecessary subtotals.

Head to the “PivotTable Tools” and the tab “Design.” Here, we can rearrange the look and feel of the PivotTable. Under Subtotals, choose “Do Not Show Subtotals.” Under “Report Layout,” click “Show in Tabular Form.”

Now our data is in good shape. We’ve isolated the position player fantasy points by season, and now we just need to create a formula that will weight recent performances in favor of older stats.

So now, in order to jam in my own little formula, I will copy the data (CTRL+A then CTRL+C) and paste the values into a new tab or spreadsheet (so CTRL+V to paste it, then CTRL then V to paste just the values). Now that I’ve got raw numbers organized the way I want them, I will delete the top row (which does nothing for me) and then format the data as a table:

First, I’m going to use the filter buttons to get rid of any players who had blank years. Not only do these guys complicate the following math, but they also invariably do not qualify for my question concerning most fantasy points over the preceding three seasons.

After I filter out the “(Blank)” items in the each year column, I should have something like this:

Then I just create a formula that multiplies each year by the appropriate 5-4-3 weight, adds the results, and then divides that sum by the sum of the weights (12). That formula is specifically this:

=([@2014]*5+[@2013]*4+[@2012]*3)/12

NOTE: These previous two steps could go in either order, really.

Then, for fun, I’ll format the final column to look a little nicer, maybe throw in a conditional formatting here or there, and then I’ve got a nifty little tool for for my first year draft. Of course, if I were using this data for a draft in an old league, I would have filtered down to just Free Agents — either in the Sortable Stats page, or within the PivotTable:

So, after all that work, I finally get my sweet, delicious prize:

With a little bit of formula athletics, we can expand this data to look at everyone, even if they have missed a season. This would allow us to allow put rookies and sophomores into the table. But since this is a PivotTable article, I’ll save that for another time.

Happy spreadsheeting!

---

Previous How To’s:

How to Use Tableau for Baseball Data
How to Use Vlookup for Sports Data

Let me know if there’s something else you’d like a How To on!