Cheat Sheets, the number one tool used during every single fantasy football draft. Many people take the easy road, buying a magazine or comparing rankings on various websites. After hours of research they’ll compile a list of player rankings based on their personal opinion of each player’s value, sound familiar? Why not kick it up a notch, create a cheat sheet in excel customized to your league point settings. You’ll have serious leverage headed into your draft, you’ll understand why once it’s in front of you.
I’m here to help you make a customizable cheat sheet. A cheat sheet you can use for every draft, every year, based on the players projected stats combined with their strength of schedule, average draft position, bye-week and age. It doesn’t take long and helps you really break down each player and draft logically. Having strength of schedule, ADP and bye-week listed alongside the players on the cheat sheet allows you to make informed decisions before and during the draft. Otherwise, you would have no point of reference, at least not consolidated into one sheet.
Without further ado, let’s walk through the process of creating a fantasy football draft cheat sheet in Microsoft Excel. It’s important you understand the process so you can tweak it later on down the road. But don’t worry, we’ve provided a downloadable fantasy football cheat sheet that’s ready to use.
Your cheat sheet is going to need seven tabs, create the tabs using the names below, to the letter!
1. League Settings: this is where you’ll input your league point settings
2. QB: this is where you’ll input the quarterback projected stats
3. RB: this is where you’ll input the running back projected stats
4. WR: this is where you’ll input the wide receiver projected stats
5. TE: this is where you’ll input the tight end projected stats
6. SOS: this is where you’ll input strength of schedule
7. ADP: this is where you’ll input ADP and age
Tab 1
League Settings, we’re going to keep it simple. List points for rushing, receiving and passing. In one column you’ll enter the points scored and in the second column you’ll enter the value which earns that point. In order for the functions to work you need to have the data in the following columns.
• Column A = Points
• Column C = value which rewards points, ergo: yards, touchdown etc
• Row 5 = passing yard
• Row 6 = passing touchdowns
• Row 7 = passing interceptions
• Row 11 = rushing yards
• Row 12 = rushing touchdowns
• Row 16 = receiving PPR
• Row 17 = receiving yards
• Row 18 = receiving touchdowns
Tab 2 through 5
List the projected points for each position. You can obtain “starter data” from our fantasy football draft projections page. Read the key points at the end of this article, which explains how to copy data from websites and import it into Excel. In order for the functions to work you need to have the data in the columns listed below. If you import from the columns will resemble the format below, which is needed to ensure the formulas I give you work. The formulas are listed below, simply paste it in column X row 2, and then drag it down so it will be applied to each row.
QB Tab
• Column A = player name
• Column B = team (must be the same on every tab, example: NO, ARZ, GB)
• Column C = bye
• Column D = position
• Column E = Completions
• Column F = attempts
• Column G = yards
• Column H = touchdowns
• Column I = interceptions
• Column J = rushing attempts
• Column K = rushing yards
• Column L = touchdowns
• Column M = age (Formula) =INDEX(adp!A$1:H$300,MATCH(A2,adp!B$1:B$300,0),6)
• Column N = ADP (Formula) =INDEX(adp!A$1:H$300,MATCH(A2,adp!B$1:B$300,0),8)
• Column O = SOS (Formula) =INDEX(sos!A$1:E$300,MATCH(B2,sos!A$1:A$300,0),2)
• Column P = Fantasy Football Points (FFP) (Formula) =SUM(G2/'League Settings'!$C$5*'League Settings'!$A$5+H2/'League Settings'!$C$6*'League Settings'!$A$6+I2/'League Settings'!$C$7*'League Settings'!$A$7+K2/'League Settings'!$C$11*'League Settings'!$A$11+L2/'League Settings'!$C$12*'League Settings'!$A$12)
CLICK ME - Draft Guide!
WR Tab
• Column A = player name
• Column B = team (must be the same on every tab, example: NO, ARZ, GB)
• Column C = bye
• Column D = position
• Column E = receptions
• Column F = yards
• Column G = touchdowns
• Column H = rushing attempts
• Column I = rushing yards
• Column J = touchdowns
• Column K = AGE (Formula) =INDEX(adp!A$1:H$300,MATCH(A2,adp!B$1:B$300,0),6)
• Column L = ADP (Formula) =INDEX(adp!A$1:H$300,MATCH(A2,adp!B$1:B$300,0),8)
• Column M = SOS (Formula) =INDEX(sos!A$1:E$300,MATCH(B2,sos!A$1:A$300,0),4)
• Column N = FFP / Fantasy Football Points (Formula) =SUM(E2/'League Settings'!$C$16*'League Settings'!$A$16+F2/'League Settings'!$C$17*'League Settings'!$A$17+G2/'League Settings'!$C$18*'League Settings'!$A$18+I2/'League Settings'!$C$11*'League Settings'!$A$11+J2/'League Settings'!$C$12*'League Settings'!$A$12)
RB Tab
• Column A = player name
• Column B = team (must be the same on every tab, example: NO, ARZ, GB)
• Column C = bye
• Column D = position
• Column E = attempts
• Column F = yards
• Column G = touchdowns
• Column H = receptions
• Column I = receiving yards
• Column J = receiving touchdowns
• Column K = AGE (Formula) =INDEX(adp!A$1:H$300,MATCH(A2,adp!B$1:B$300,0),6)
• Column L = ADP (Formula) =INDEX(adp!A$1:H$300,MATCH(A2,adp!B$1:B$300,0),8)
• Column M = SOS (Formula) =INDEX(sos!A$1:E$300,MATCH(B2,sos!A$1:A$300,0),3)
• Column N = Fantasy Football Points (FFP) (Formula) =SUM(F2/'League Settings'!$C$11*'League Settings'!$A$11+G2/'League Settings'!$C$12*'League Settings'!$A$12+H2/'League Settings'!$C$16*'League Settings'!$A$16+I2/'League Settings'!$C$17*'League Settings'!$A$17+J2/'League Settings'!$C$18*'League Settings'!$A$18)
TE Tab
• Column A = player name
• Column B = team (must be the same on every tab, example: NO, ARZ, GB)
• Column C = bye
• Column D = position
• Column E = receptions
• Column F = yards
• Column G = touchdowns
• Column H = AGE (Formula) =INDEX(adp!A$1:H$300,MATCH(A2,adp!B$1:B$300,0),6)
• Column I = ADP (Formula) =INDEX(adp!A$1:H$300,MATCH(A2,adp!B$1:B$300,0),8)
• Column J = SOS (Formula) =INDEX(sos!A$1:E$300,MATCH(B2,sos!A$1:A$300,0),5)
• Column K = Fantasy Football Points (FFP) (Formula) =SUM(E2/'League Settings'!$C$16*'League Settings'!$A$16+F2/'League Settings'!$C$17*'League Settings'!$A$17+G2/'League Settings'!$C$18*'League Settings'!$A$18)
Don’t worry about the ADP, age, SOS showing a value of “N/A”, this is because you haven’t completed tabs 6 and 7.
Tab 6
This is going to contain projected strength of schedule for each team and position. Next to each team input a numerical value to represent the strength of schedule. Read the key points at the end of this article, which explains how to copy data from websites and import it into Excel. In order for the functions to work you need to have the data in the following columns.
• Column A = team name (must be the same on every tab, example: NO, ARZ, GB)
• Column B = QB SOS rank
• Column C = RB SOS rank
• Column D = WR SOS rank
• Column E = TE SOS rank
Tab 7
This is where you’ll input ADP and age. A great site that has these metrics is http://www.fftoolbox.com/football/adp.cfm. Read the key points at the end of this article, which explains how to copy data from websites and import it into Excel. In order for the functions to work you need to have the data in the following columns.
• Column A = not used
• Column B = player name
• Column C = not used
• Column D = team name (must be the same on every tab, example: NO, ARZ, GB)
• Column E = bye week
• Column F = age
• Column G = not used
• Column H = ADP
Honestly, once I worked out the Excel formulas this entire process only took 30 minutes. You can shortcut the entire process by downloading our pre-made customizable cheat sheet.
Key Points
1. You can copy data from any website by copying it and pasting it into a text file, then save the text file as somename.txt. Now open Excel and choose file -->open --> somename.txt which will open Excel’s import wizard. Follow the prompts to import the data in the proper format.
2. All player names and team names will need to be sanitized because some of them will have a leading or trailing blank space, which will need to be remove for the formulas to work. This is done using the =trim function in excel. Watch this youtube video if you don’t know how to do this.
Basically, after you import the data you’ll insert a temporary column next to the column you want to sanitize. Let’s say you’re removing blank spaces (sanitizing) from the players name in column b2. In A2 you’ll type =trim(b2). This will remove the blank spaces in B2 and place the sanitized version in A2. Now simply drag A2 down and it will sanitize the rest of the player names. You’ll have to copy the sanitized names and paste them over the original names. When you paste be sure to choose “paste special” and then choose “values”, otherwise you’ll simply paste the trim function again. Now you can delete the temporary column A where you executed the trim function.
Attribution
1. https://fantasyknuckleheads.com - strength of schedule
2. http://fftoday.com - the draft projections - (not where we get stats for our premium membership, very different)
3. http://fftoolbox.com - ADP and Age etc
