Using Excel to Track a Fantasy Football Draft

For the past several years I’ve taken part in a family fantasy football league. Each year when draft time comes around, I start my process of figuring out how I’m not only going to draft my team but also keep track of the full draft results. As a member of the league, I want to draft the best possible team. As league commissioner, I need to know the full draft results because I’m responsible for entering our draft into Yahoo, where we run the league. Most importantly, as a part of the gang that comes together each year for the draft, I want to enjoy the experience and not just focus on playing draft tracker to get all the data. As a user of screen reading technology, I want an experience that works well with the technology I use.

Our league uses an auction style draft. For those unfamiliar with fantasy football, this basically means that each league member is given a budget of $200 to invest in building a team of 16 players. We draw numbers to determine what’s known as a nominating order where each of us then nominates a player and then auction-style bidding takes place until someone wins the auction to have that player on their team. This process continues until every team in the league has 16 players.

Our draft generally takes about four hours. This means that over 240 minutes, we are drafting a total of 160 players. That translates to an average of one player every 1.5 minutes. That is definitely just an average because in the early rounds, we can spend five or more minutes per player. In the late rounds, when few people have any auction money to spend, it isn’t uncommon to spend 15 seconds per player.

Fantasy football is a booming business and you can buy paper draft kits from many locations. We use a paper draft board with stickers for player names and then manual writing and tracking of money spent and calculation of remaining money on draft day. Stickers are color-coded by position such that running backs might be red and wide receivers blue. Thus it is easy for everyone in the room to quickly determine when someone’s team is getting a bit position heavy as mine did by my drafting perhaps a few too many running backs early on.

In addition to tracking what players are assigned to what team, I wanted to track how much auction money each league member had spent, how many players they had drafted, how many players they still needed, and what we call their remaining max bid. That last talks about the maximum they can spend on any given player and still field a full team. At some point, most turn into “one-dollar guys” because of the number of players they still need and the money they have left. This is when the auction moves quickly. In short, I wanted to duplicate what we track on paper.

I also wanted my process to allow me to communicate with everyone else about what was going on during the draft. And again, more importantly, I didn’t want my process to interrupt from the experience, which is equally about the conversations, banter and time together with family and friends.

As I mentioned early on, I’ve been doing this league for several years so figuring out what strategy I was going to use to track the draft wasn’t a new exploration for me. My choice came down to RotoWire’s 2017 Fantasy Football Draft Kit or using Excel. RotoWire’s app is quite good and does allow for tracking all the items I’ve mentioned. For me the biggest limitation is just in the speed at which you can enter data rapidly in a fast-paced environment and then review it when using VoiceOver on an iPhone or iPad.

Excel is basically a blank slate so for me picking Excel meant I’d have to find a good source of player data that would easily import into Excel. It also meant I’d have to work out all the details on formulas and such to track what I wanted. That said, Excel has powerful keyboard access and pivot tables make tracking some of what I needed quite easy.

This year I opted for Excel and decided to invest a bit of time ahead of the draft to make my process as efficient as possible. Here’s a bit more background on what I did.

Player Data

You can find dozens of NFL fantasy rankings and cheat sheets online. Few of these work well for importing into Excel because they are generally formatted for printing. The best site I’ve found for obtaining player data that easily loads into Excel is the NFL’s fantasy player data. You can select from the various positions and then get a table of players for that position.

Loading the data itself into Excel is where I have come to appreciate the versatility of the copy and paste functionality the JAWS screen reader has for dealing with web content. JAWS has two modes for copying and pasting text from the web. A setting allows you to choose between using what JAWS calls the Virtual PC cursor view of the page and what JAWS calls on-screen highlighting.

For those unfamiliar with screen reading technology, the Virtual PC view presents web content in a more linear fashion and adds more details about page structure such as headings, lists and tables. It is excellent for reading web pages but not so much for copying and pasting where you want to retain formatting.

Using JAWS, copying the player data involved just a few key presses. I navigated to one of the positions I was interested in, such as quarterbacks, and pressed the letter t to navigate to the table of data. JAWS, like other screen readers, allows users to press hotkeys to navigate to various parts of a web page, such as t for tables, h for headings and so on.

Next, I pressed F8, a JAWS hotkey to select all the text and formatting of the item with focus on a web page. Note before doing this you must press up arrow once, since by default JAWS positions the user on the data in the first cell of the table when using the letter t to jump to a table.

After selecting the text, I simply pressed control+c, the default Windows shortcut for copy, used Alt+Tab until I was back to Excel, moved to the location where I wanted the player data pasted and pressed control+v to paste the result.

Building My Spreadsheet

In building my spreadsheet, I had to think about several factors. As one example, was I going to put the data for each position on a different sheet in an Excel file or use one sheet for all data. I opted for a single sheet and pasted all the different position data into that sheet. That allows for rapid searching of player names when tracking draft picks. I made one key improvement though, that takes advantage of a basic keyboard navigation feature in Excel.

As my first column, I inserted headings for the different position types. For example, in Cell A2, I inserted the term QB. The NFL data includes at least 40 players for the quarterback position, so I knew my next position of player data would start appearing in row 42. Row 1 was overall column headers, rows 2-41 quarterback data and row 42 is where wide receiver data started. So, in cell A42, I put a header of Wide Receiver. I continued this pattern for all player data.

The result was that column 1 of my spreadsheet had just a few pieces of data and I could then use the key control+up or control+down arrow to quickly jump from player group to player group. When using Excel from the keyboard, control and the four arrow keys will jump to the next cells with data in that direction, skipping over blank cells.

After copying and pasting all the player data, I made just a couple more modifications to my basic spreadsheet. I’m a big fan of the Tel Me feature in Office applications, so pressed alt+q and entered the term insert and used Tell Me to add two new columns to my spreadsheet. I added these immediately after the player name. One column was for tracking the team that selected the player and one for the dollar amount.

I also wanted to ensure I didn’t make typing errors during the auction because some of the calculations I wanted to do would then not work correctly. I used Excel’s data validation functionality to ensure I only entered correct team names.

Tracking the Draft with a Pivot Table

Pivot tables are a very powerful feature in Excel for examining data in various ways. At the most basic level, a pivot table allows you to assign data to categories such as rows, columns, values or a filter in the resulting table. Excel then takes care of all the calculations, such as quickly adding the totals for all the items in a certain column. Part of the power of this feature then comes from the ability to quickly adjust what data is assigned to what category and view your results.

For my basic pivot table I wanted to track how many players were assigned to each team, the money spent by each owner, how much money was remaining and the maximum that could be spent on any given player. I started by inserting a basic pivot table by choosing the Insert Ribbon with alt+n and then tabbing once to the pivot table option. I could have pressed alt+n, v to do the same directly.

At this point Excel asks you for the data you want to use for the pivot table. The default for this is the range of the sheet you are on when you ask to add a pivot table. Again I had copied my data from the NFL’s web site so wasn’t confident it would have every possible player so I had to ensure to increase the range of the data I was going to use to account for the fact that I might manually enter players during the draft. This simply involved typing an updated range reference.

I next selected the defaults offered by Excel to add the pivot table to a new sheet and the OK button. I then had my new pivot table ready for me to choose how I wanted to view the data.

Choosing the Data for a Pivot Table

After you insert a pivot table, Excel moves you to the new sheet and positions you on cell A3 of the new sheet. To choose what data is assigned to the different categories, press F6 to move to the field list. By default you are on a search box where you could search for different names of data. The number of fields in my table was small so I simply tabbed once to the list of fields available and used the up and down arrows to move to different fields. To use a field, from the keyboard press Shift+F10 on the field name, and choose where you want it used in the pivot table.

My pivot table was very basic. I added the field team owner, referring to the fantasy football teams in our league, to my table rows. I then added Player to values. In this case I wasn’t interested in the specific players, just the count. Last I added price, again to values. In this case I was interested in Excel adding up the money spent by each team.

After finishing with my selections, I pressed F6 until I was back to the spreadsheet area for my pivot table. I now had a table with three columns. The columns were by default Team Owner, Count of Player and Count of Price. Count of was text added by Excel.

Again Player and Price were columns I told Excel to add to the values section of my pivot table. When you do this, Excel chooses defaults for how the data should be summarized. There are a range of options but in this case the two I was interested in were count and sum. For the players, Excel’s default choice of count was just fine. However, for price, I needed to make a change.

To change how Excel summarizes data in a pivot table, move to the data and press Shift+F10 on one of the cells. The resulting menu will have an option listed as Summarize By. From that choose the option you want. In my case, I chose sum, meaning I wanted Excel to add the actual values. For comparison, count simply adds whether data exists or not, independent of the value of that data. Had I left the Price to be summarized by count, when the draft was done, each row would have shown a value of 16. By changing the summarization to sum, Excel added up the actual dollar amounts used.

Final Steps

Before I was ready for the draft, I made some final functional and cosmetic changes to my pivot table. I added some basic formulas to take the money spent and subtract that from $200, our total allowed, to be able to keep track of how much money each person had left in a fourth column. I added a fifth column, to show the maximum amount that could be spent on any given player. Last I adjusted the names of the columns where Excel had inserted default text of count and such, just to give myself more user-friendly names.

While this was enough for most of what I wanted for the draft, I added a second pivot table. In this case I added player to both values and columns. This made a very large table but allowed me to use control left and right arrow to jump through all the players taken by each team during the draft.

Draft Day

Overall my spreadsheet worked well on draft day. For the majority of the draft I had no trouble keeping up with the pace of the draft. In the last hour, Excel crashed a couple times on me, requiring a machine reboot and some behind-the-scenes updating of player selections. The pivot table tracking of money spent worked better than expected and over the course of the draft became the source of truth for who had what left to spend. As for my fantasy team, we’ll have to wait for the NFL season to play itself out to see how I did.