:american_football: Now that it is August, the NFL preseason is in motion. Now is the time to be analyzing how your favorite players are doing out there. :american_football: In this post, you’ll learn about Get & Transform, Conditional Formatting, VLOOKUP, absolute and relative references, and more!
We made a sample file that can help you with your Fantasy Football* draft. Click HERE to download it! After playing with it and reading this post, show us how you can make it better. How could you make one that isn’t just a draft tracker for you, but one that could run the draft for your entire league? Share your results as attachments in the comments on this post!
Here’s how our file works:
As you fill out players’ names in the table at the top, the position and bye week data from the table below fill out the table at the top.
The sorting of the players in the dropdown and the lower table is based on their average draft position across multiple experts’ websites and some of our own opinion on certain players. More on how we got this data without just using copy-paste below!
The table at the bottom (starting in cell A20) is our source data on the players, sorted by a ranking scheme we have put together by combining research from some of the best sites out there. However, if you don’t agree with our rankings (some leagues might use different scoring systems, such as Standard versus Point-Per-Reception), feel free to re-arrange players to change the rankings!
A listing of players, ranks, positions, bye weeks, draft status, and team name, all colored based on team color using conditional formatting!
The table at the top (starting in cell A2) shows you your team as you fill it out. Some leagues might have 15 roster spots, while others might have 16. Different leagues also have different required positions – some have a “flex” roster position, some have multiple quarterbacks, and so on. It depends, so feel free to modify it however you see fit!
Each row in the roster spots for “My team” column has a data validation-based dropdown that shows the list of top NFL players.
The upper table in the Excel file shows dropdowns where you an select players from the bottom table. As you fill out the table, our VLOOKUPs grab their positions and bye weeks from below.
Here is where you can go in the “Data” tab in the ribbon to see how we used data validation to make our dropdown list:
In the "Data" tab on the ribbon, you can see the "Data Tools" section has a button for "Data Validation"
You can learn more about how Data Validation works in Excel HERE.
When you pick a player, you’ll see that their position and bye weeks automatically fill out in the table. How does this work?
we are using the function VLOOKUP to look for that player’s name in the table below, then looking over in the adjacent columns to see the values for their position and bye week. We then wrap the VLOOKUP with an IFERROR function – all this does is keep the cells empty for spots where no one has been drafted yet. Otherwise, the “Position” and “Bye Week” columns would just be cluttered with error messages.
A screenshot showing how the VLOOKUPs in the upper table work. Specifically, cell C3.
You can learn more about how VLOOKUP works HERE, and IFERROR HERE.
You may have noticed the color scheme we have for all the players in the full list. We did this with Conditional Formatting. If you haven’t used conditional formatting before, you can read more on how it works for simple built-in formats HERE and how to do more complicated things with formulas like we’ve done HERE.
In the “Home” tab of the ribbon, if you click on “Conditional Formatting”, then “Manage Rules”, you’ll see what we’ve done to get the color scheme for our full list of the players:
The options for conditional formatting are shown: highlight cells rules, top/bottom rules, data bars, color scales, icon sets, and options to create new rules, clear rules, and manage rules
This was a bit tedious to build since we had to do one rule per team, and for each team come up with a color scheme, but here’s how we set it up. Let’s say we need to create a rule to make the Seahawks players have a dark blue cell background and a bright green text color. To do so, we would highlight the entire table since we want the color to not just be in the cell that says “SEA”, but for the entire row that has the cell that says “SEA”.
Click on “Conditional Formatting”, then “New Rule”, then “Use a formula to determine which cells to format”. Once you’ve done this you can enter a formula like we’ve done, =$F20=“SEA”. What this means is for all cells, we are only looking at column F (since we locked to an absolute reference with the $), but we are looking row by row since the ‘20’ doesn’t have a ‘$’ in front of it. This will correctly find all rows that have a team name of “SEA” for us, so now we just need to tell Excel what formatting to use when we find those. You can do this by clicking the “Format” button in this dialog and then setting whatever colors and other formatting you’d like:
Here, we show how the formula is written to set all rows where a player has team of SEA to the correct formatting
When you mark a player as drafted in the bottom table, you’ll notice this greys out that row, taking over the team color that was previously set for that player. We also did this with conditional formatting. Instead of looking at column F to set our color based on team name, we instead used column E as the field to check for “Yes”, meaning the player has already been drafted. Then, in our conditional formatting rules manager, we gave the “greying out” rule precedence over all the individual team formats, like so (the arrows to the right of the “Delete Rule” button let you move rules up and down the stack):
The conditional formatting rules manager is shown with the greyed out players rule given higher precedence than when they have a team of SEA
Absolute and Relative References
You also might notice we have a bunch of “$” characters in our formulas throughout this posting. If you aren’t sure what this means, this is how Excel switches between Absolute and Relative References. You can read more about them HERE.
Get & Transform / Power Query
You might be wondering: where did we get the NFL Player list and their numbers around bye weeks? We used a feature in Excel called Get & Transform. Once you find a website for your research (there are quite a lot of them out there!), you can get data directly from that website into Excel by following the steps shown HERE.
Once you have built a query using Get & Transform to get player information and stats from a website of your choosing, you can then refresh that query whenever you want by clicking “Refresh All” in the “Data & Connections” part of the “Data” tab in the ribbon:
The button for "Refresh All" is in the "Data" tab in the ribbon in a section called "Queries & Connections"
This will help you keep track of your players’ stats as the season goes on and is incredibly useful for any other external data you might want to track using Excel. You can read more about Get & Transform HERE.
Leave a comment with what you thought of this, and please feel free to share how you use Excel to win your draft and your league!
To stay connected to Excel and its community, read Excel blog posts, and send us ideas and suggestions for the next version of Excel through UserVoice. You can also follow Excel on Facebook and Twitter.
* For those unfamiliar with the concept of Fantasy Football, the Wikipedia page HERE is a good place to start.