I’ll paraphrase a reader question I recently received:
Hey, you with your big fancy spreadsheets. I need a way to easily hide the players that have already been drafted so I don’t waste time digging around in a bunch of players that are no longer available. Have any tips?
– Bill
Great question. Let me show you how I do this.
But First…
We have three key Excel concepts or features you should understand. If you’re already familiar with these, just skip down to the step-by-step instructions below.
- Named Ranges
- Data Validation
- Filtering
Named Ranges
Excel allows you to create names for groups or blocks of cells. Once established you can use this name in formulas instead of having to fully spell out the entire range of data again. I find it a lot easier to deal with a name like “TeamNames” than it is to explicitly specify the area of a range like “Settings!$A$1:$A$10”. Named ranges are a lot easier to remember and a lot less likely to result in errors.
Data Validation
Data validation ensures that information entered in a cell or calculated by the Excel fits specified criteria. You could validate that information entered in the cell is a date or is larger than 0, for example.
In the example below we will create a drop down menu that lists each team in your fantasy league and validates that the selected value is spelled correctly and corresponds to a team in your league.
Filtering
Filtering is a function in Excel that allows you to hide entire rows of data that don’t meet certain requirements. In this example we will filter the list of players to hide players that have been given a team (they have been drafted).
Step-By-Step Instructions
The following instructions will take you through the process of adding a column to track which players have been drafted and which team in your league selected each player. We will create a drop down menu that lists all teams in your league and use this to document who was drafted and by what team.
These instructions assume you have some kind of a starting spreadsheet containing projections and rankings already. If you don’t have such a starting point, skip to the end of this post for information on how to get here. (more…)
