![]() 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. 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. Once you’ve done this you can enter a formula like we’ve done, =$F20=“SEA”. 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”.Ĭlick on “Conditional Formatting”, then “New Rule”, then “Use a formula to determine which cells to format”. 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. 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. 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 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: 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. You may have noticed the color scheme we have for all the players in the full list. You can learn more about how VLOOKUP works HERE, and IFERROR HERE. Otherwise, the “Position” and “Bye Week” columns would just be cluttered with error messages.Ī screenshot showing how the VLOOKUPs in the upper table work. 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. 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. When you pick a player, you’ll see that their position and bye weeks automatically fill out in the table. You can learn more about how Data Validation works in Excel HERE. In the "Data" tab on the ribbon, you can see the "Data Tools" section has a button for "Data Validation" 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: As you fill out the table, our VLOOKUPs grab their positions and bye weeks from below. The upper table in the Excel file shows dropdowns where you an select players from the bottom table. It depends, so feel free to modify it however you see fit!Įach row in the roster spots for “My team” column has a data validation-based dropdown that shows the list of top NFL players. Different leagues also have different required positions – some have a “flex” roster position, some have multiple quarterbacks, and so on. Some leagues might have 15 roster spots, while others might have 16. The table at the top (starting in cell A2) shows you your team as you fill it out. 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!Ī 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 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. More on how we got this data without just using copy-paste below! 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.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.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! Click HERE to download it! After playing with it and reading this post, show us how you can make it better. We made a sample file that can help you with your Fantasy Football* draft. :american_football: In this post, you’ll learn about Get & Transform, Conditional Formatting, VLOOKUP, absolute and relative references, and more! Now is the time to be analyzing how your favorite players are doing out there. :american_football: Now that it is August, the NFL preseason is in motion.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |