For those fantasy football prognosticators in the house, the ones who dive right into the detail and do their own player projections each year – or are thinking about doing it – I have something for you. I created an Excel spreadsheet that should help create, edit and organize your projections.
It is no Draft Buddy, but still a pretty neat little tool in my humble opinion. At a minimum, it includes NFL player stats for quarterbacks, running backs, wide receivers and tight ends for each of the past five years, 2009-2013. Simply for review purposes it has a lot of utility right there.
Lets start with a download and then I will walk you through it.
You do need Microsoft Excel, and I created it in “newer” versions of Excel (not that new, since Excel 2007), which is why it has the .xlsm extension on it. It does include macros, so you need to enable macros on opening. If you have Mac Excel 2008, there is no macro support, so the macros will not work.
When you open it, you’ll see six tabs as follows:
- Projections Input Tab
Projections Input Tab
This tab has two parts really. The bottom section which includes the yellow boxes is where you input your projections. Only change the yellow highlighted boxes to avoid changing a formula. To change teams, select the red highlighted cell in the upper-right and a drop-down should appear where you can switch the team. It should then automatically update the players based on our depth charts.
Below the input section there are two buttons: (1) Clear Input Tab Projections and (2) Copy Projections To Position Tabs. I hope those are self-explanatory, but the first one basically just clears your work space to start from nil values. The second copies your projections to the subsequent tabs, and then automatically sorts the QB, RB, WR and TE tabs by fantasy points from highest to lowest, so you can see how your projections by team work out on a by position basis.
Now, the upper part of this tab. This is part of the beauty of this spreadsheet to help you create reasonable projections. Scroll all the way to the top. Here are the historical stats for the team selected, showing stats by player and totals for each of the past five seasons. There are also some summary calculations and the sum of your projections down below to check if you are projecting in a reasonable range.
The buttons across the top are to show or hide each of the positions, to keep this section clean and focussed. It may not show all players as I had to make it even across all teams, but it should by fairly deep and I believe the team totals are accurate.
This may involve some up and down scrolling but you could perhaps even split the window between top and bottom if you find that helpful.
This tab is really an in-between tab to store your projections from the input tab, and feed the projections to the positions tabs. You really should not make any changes to this tab, but I left it open so you can quickly review your projections by team without having to pull them in one at a time back on the input tab, if desired.
QB, RB, WR, TE Tabs
Similarly, I do not recommend making any changes to this tab. It is filled with formulas that will break the spreadsheet if you mess with them. However, this is the view we ultimately want to see, which is projections by position, and fantasy points calculated, sorted from highest to lowest.
The macros should do this sorting for you automatically each time you click the Copy Projections to Position Tabs button back on the original input tab. When you first open the spreadsheet you will see there are only a few players on these tabs because I only input some test projections for a few teams, the Cardinals, Bills and Panthers.
I hope find this spreadsheet incredibly helpful in your pursuit of creating your own player projections to dominate your fantasy football league this season. Post comments if you have any questions or feedback.