Fantasy Football Spreadsheet Tool – The Ultimate Dashboard To Manage Your Team

Update: I’ve recently started a new site specifically for sports spreadsheets. The tool below is out of date, but you can find an updated version along with many other tools and fantasy football spreadsheets over at www.spreadsheet-sports.com.

Recently I’ve gotten involved in a fantasy football league for the first time. For a sports fanatic and numbers geek like me I think I was a little bit late to the game. Admittedly, I’m doing pretty poorly so far, but I think I might have some good weeks ahead of me. I’ve learned a few lessons along the way on how fantasy football really works.

1. Luck is a huge part of the game. You never know when some relatively unknown player is going to erupt for 150 receiving yards and 2 TD’s (like Austin Collie has twice in the first 3 weeks for the Colts) or that one of the preseason top 10 running backs would have less than 100 yards combined and 0 touchdowns after 3 weeks (Shonn Greene of the Jets).

2. Even though luck plays a big part you can dramatically increase your chances of being on the right side of luck if you have the right data available. Fantasy football is all about numbers and match-ups, and there are hundreds of decisions that need to be made throughout the season on who to start or who to potentially trade. Your chances of being lucky are much better when you make those decisions based on data rather than just a hunch every single time.

I’ve also realized that there really isn’t a way to have the information you need all in one place. It’s time-consuming to go back and forth between ESPN, Yahoo Fantasy Sports, team websites, etc. to gather the data you need to make decisions. That’s why I’ve created this fantasy football spreadsheet to act as a hub for all of my fantasy football decision making.

How To Use The Fantasy Football Data Hub

First things first, I want to make this tool available and useful to everyone.  I’ve built it using Google Spreadsheets, and the nice thing is that all of the data updates automatically week to week.  Here’s what you’ll need to do to use the fantasy football spreadsheet yourself:

1. Make sure you have a Google account (if you don’t have one you can sign up for free here)

2. Open the Fantasy Football Data Hub

3. Click File > Make a Copy so that you save your own version of the spreadsheet into your account. You won’t be able to edit the master sheet, but once you make a copy you’ll be able to put your own players and use all of the player comparison tools.

4. **Important** Read the “Week to Week” section below. It’s the only manual effort that you’ll have to make to get the data to display correctly.  If you don’t follow these instructions the data will be invalid after a week.

What Can You Do With The Fantasy Football Data Hub?

I’ve included several different fantasy football tools in the spreadsheet that should help you make decisions as the season goes on. Each tool is located on a different tab of the spreadsheet.  There are a few tabs that only  contain raw data and won’t be used, but they’re important to keep intact because the data feeds into all of the other tabs.  Here’s a list of the tools contained in the Fantasy Football Data Hub.

What To Watch For

This tool gives you a data driven perspective on which offenses or defenses you should look out for in the coming week.  It takes each matchup into account and looks for the biggest disparities in rank.  The first column (Defenses That Should Do Well) is based on scoring in past weeks.  Each team is ranked based on the total amount of points their defense has given up.  Then the matchup is taken into account by looking at the points per game of the opposing offense and displaying the defenses with the biggest difference in ranking against the opposing offense they’re playing.  So if the #28 defense is playing the #2 scoring offense they’re going to show up on the “Defenses to Avoid” list.

Top 20 Players This Week

This data is an aggregation of all of the top fantasy sports sites (ESPN, Yahoo, etc) and the projected points their analysts assign to each player.  This data comes from the brilliant work of the Fantasy Football Nerd at www.fantasyfootballnerd.com.  They should get all the credit for compiling these rankings, and I’ve simply displayed them here along with all of the other fantasy football tools in the spreadsheet.  These will be updated each week by Wednesday or Thursday so you might see errors or old data if you look at it on Monday or Tuesday.

Player Comparison

This tool allows you to quickly compare season stats on up to 13 players at once.  You’ll get projected stats for each week as well as cumulative season stats on fantasy points, touchdowns, passing, rushing, receiving yards etc.  This might be useful if you’re exploring potential trades and you want to see how a few different players have performed up to this point in the year.

Should I Start/Should I Trade?

Both of these tools allow you to input players into the boxes that you’re trying decide between.  The Should I Start? tool simply looks at the upcoming week and the data that FantasyFootballNerd.com aggregates.  It will return an answer and will show you how many points better that player will be for the week.  The Should I Trade? tool looks at total season data and the average amount of fantasy points each player has scored per week and let’s you know if you’re getting the better deal on the trade.  This tool doesn’t take into account your current lineup so the results might be skewed if you’re trading a running back for a wide receiver (since RB’s generally score more points than WR’s based on the scoring systems in fantasy football).  However, it will give you a quick idea of whether a trade is fair or not.

My Teams

On this tab you can input your League and Team ID’s of ESPN and Yahoo fantasy leagues so that you can import your own team data into the spreadsheet.  Currently the integration with ESPN works well, but the Yahoo import is not quite complete.  However, you can still manually input your own player names in the players column and the projected points will then appear.

To find your League ID and Team ID you’ll need to check the URL of the webpage you use to manage your team.  For ESPN leagues it should look like this:

The first box is your League ID and the second is the Team ID.  Those are the numbers you’ll need to plug into the spreadsheet.

For the Yahoo leagues (integration coming soon) it should look like this:

The red box is the League ID and the green is the Team ID.

This tool will give you a quick indication if any of the players that you have sitting on your bench should start (based on a higher projection that another player at the same position).

Breaking News

The breaking news tab is an RSS feed of all of the latest news, injury updates, and insider information that might affect the week’s fantasy football matchups.  This is compiled from Footballguys.com, and they do an amazing job of getting the latest information displayed immediately.  Here you can find their fantasy football RSS feed.

Week to Week

This tab is actually the most important part of the spreadsheet because it’s the one sheet that you have to manually change each week to make the data display correctly. Currently, the next week of the NFL season is at the top of this sheet.  In order for all of the data to work correctly you must make sure the next week is always at the top of this sheet.  Here’s what you’ll need to do.

Scroll down the spreadsheet to the next week (or whatever week is upcoming in the NFL season) and cut the entire chunk of rows and paste it into the top of the spreadsheet to replace the data from the old week.  Don’t replace the very top row with the headers, but take the row starting with “Week x” and all the way down through all of the games for that week.  So you cut the new data and paste into the top of the sheet and you’re good to go.

Make sure to leave all of the other tabs alone if you want the spreadsheet to work as it’s designed.  However, if you want to build your own formulas feel free to tweak the data.

I’d love to get your feedback on how you’ve used the Fantasy Football Data Hub, and any feature requests you might have.  I plan to continually develop the tool to use in my own fantasy football leagues so look for additional tools and features over the next couple of months.

Be Sociable, Share!

3 thoughts on “Fantasy Football Spreadsheet Tool – The Ultimate Dashboard To Manage Your Team

  1. George Ciarrocchi

    love the ff spread sheet. Do you have anything for draft day itself. I leave a paper trail when I go to my draft. It would be nice to have a spread sheet/ cheet sheet that I cn track my team and others.
    Thanks
    george

  2. Mike Byrne

    I love the idea of your spread sheet and I’ve always amazed at the different things people are able to make and come up with. That being said I am having some issues though.
    (1) I have pasted (and entered to try something different)my League ID and my Team ID and it doesn’t seem to update (I played with changing the Team ID first, and it did change to what I’m assuming is the team number in your league). Any reccomendations on how to have it autofill?
    (2) I’m confused on the week to week. Where is the information pulled from? Is it pulled from the “Schedule” tab and then edited to fit into the correct columns?

    Note: If you have discontinued support for your spreadsheet I totally understand.

    Thanks in advance!
    mike

    • Mike – thanks for the compliments. Unfortunately, I’ve neglected this spreadsheet a little bit since last year, but I’m working on getting it up and running again. Unfortunately, ESPN, Yahoo, etc have changed some of the underlying data sources that this sheet pulls from.

      For #1 what kind of league are you in? ESPN? I’m having trouble getting the Yahoo league to work at all since I can no longer find a public URL.

      For #2 Yes…you’re correct in that I copy and paste the current week’s data from the Schedule tab every new week. If you copy the spreadsheet and use your own copy you’ll need to do the same. Simply take the entire block of ‘Schedule’ data for the given week and paste it into the Week to Week tab. Leave the last 2 columns alone because those simply pull the team names back in so that it can use those in one of the other sheets.

      Hopefully that makes sense…feel free to email me any other questions: bpovlins@gmail.com

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes:

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>