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?
Great question. Let me show you how I do this.
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
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 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 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).
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.
This Looks Awesome, But I Don’t Already Have A Rankings/Projections Spreadsheet
Don’t worry. If you don’t already have your own spreadsheet, you can register as an SFBB Insider (it’s free) and get a free 60-page guide showing how to create your own Excel-based rankings. Or if you want to get your hands dirty and create your own projections, check out the “SFBB ‘Projecting X’ Bundle” that comes with Mike Podhorzer’s book “Projecting X” (the best tutorial I’ve found for developing your own projections) and an Excel template for following the “Projecting X” method that comes preloaded with 800 players, links directly to Fangraphs pages for each player, and that will rank players for you automatically.
What Questions Can I Answer For You?
Have a question about how to do something in Excel? Or how to use one of the spreadsheets discussed on the site? Or do you just wonder if it’s possible to do something? E-mail me at firstname.lastname@example.org with your question.