I know, I know. It’s mid-March, college basketball is taking over your time, and you’re in the final push of draft preparation for your baseball leagues. The last thing you want to do right now is start tinkering with your draft spreadsheet and screw something up.
Then here I come with another monster Excel post…
NOT THIS TIME! I swear. This is an easy one and you might find it helpful.
In this post I’ll show you how to use Excel’s conditional formatting to gray out players that have been chosen during your draft.
Excel’s Conditional Formatting
Conditional Formatting is a way to adjust the format of individual (or many) cells based upon the information within that cell. If you were using this in a business spreadsheet you might want really high sales months to show up in bolded font or below average sales people to show up shaded in red.
For fantasy baseball you might want all players projected to hit 30 HR to have their HR projection in bold red font. Who knows.
The point is you can tell Excel “what to format” and “how to format it”.
I’m using Excel 2013 in the screenshots below. I think this will work on Excel 2007 or 2010 without any issues. I’m also assuming you’re starting with a spreadsheet you created following either this standings gain points series or this points league series.
|1.||The first thing you need to do is decide how you will track drafted players. You could use the method I’ve described here. Or if you’re in an auction you could track who has been drafted simply by putting the dollar value each player was sold at in the $ACTUAL column of your spreadsheet.
It doesn’t matter what method you choose, but for this example I’m going to assume that whatever column you use to track will be blank if the player has not been drafted and there will be some value in the column if the player has been chosen.
Make note of the column number of your choice. For example, I’ll be using column “AB” based on the image below.
|2.||Click on the first player ID in column A of your “Hitter Ranks” tab. Then hit CTRL + SHIFT + END (all at the same time) to select the remaining rows and columns in the table (Excel should highlight all players and columns).
Then on the “Home” tab of the ribbon, click the “Conditional Formatting” drop down and choose “New Rule…”.
|3.||In the “New Formatting Rule” dialog that pops up, select the last option of “Use a formula to determine which cells to format”.|
|4.||In the “Format values where this formula is true:” box, enter the following formula
The reason I’m using cell AB6 in this formula is because my $ACTUAL column is where I’ll be tracking who’s been drafted (and that’s column AB). The reason row 6 is referenced is because I added some information above my table to help define where players a slotting for determining replacement level (whether they’re a starter, falling to MI or CI, UTIL, etc.). My first player doesn’t appear in the sheet until the sixth row.
A few things to keep in mind about this formula. The equals sign at the beginning is very important. Excel will let you get away without typing in the equals sign but then the formula will not work properly.
In plain English, the
The dollar sign before cell “AB6” is to tell Excel, “Only look in column AB for the blank cells”. If we left the dollar sign out Excel would start shading seemingly random areas. If you get to the end of this set of instructions and you have weird shading showing up, check this again.
|5.||Now that we’ve given the instructions on what to shade, let’s decide how to shade these drafted players. Click the “Format…” button.|
|6.||You can format a variety of things for each cell (although some options gray out when you have a large block of cells selected like we do). The “Fill” tab will allow you to shade cells a certain color.
I’m going to shade drafted players gray. After you’ve chosen your color click “OK” to accept your color choice and then “OK” to accept the conditional formatting rule (we’re done setting up the rule now).
|7.||If you have not entered any auction values or that any players have been selected, nothing will be shaded yet. To test that everything is working, add a dollar value to the “$ACTUAL” column or enter a team name in the “LGTEAM” column (from this example).|
|8.||If you get to the end and you find out the formatting is not working properly, you can edit the rule by going to the “Manage Rules” drop down option under the “Conditional Formatting” menu on the ribbon.
Then select your rule and click the “Edit Rule…” button.
Think of What Else is Possible
Conditional Formatting in Excel is something a lot of people don’t know about and even those that do know it exists can find it intimidating. It can be confusing to work with, but if you follow the principles outlined above, you’ll be able to do some really neat things.
I’ve only given you one small practical way to use this. But there are A LOT of other neat things you could do with this.
Want to shade all outfielders a certain color? Or highlight all players with multiple position eligibility? How about tell the difference between a starter and a reliever? Left-handed hitters versus righties? Shade all injured players red so you don’t draft them?
Please ask me any questions you have about this in the comments area at the end of this post.
Want to Know How to Do Something in Excel?
If you have a suggestion or question on how to do something fantasy baseball related in Excel, shoot me an e-mail at smartfantasybaseball at gmail dot com.
Want More Info Like This?
The best way to stay in touch with the site and to get more information like this is to follow me on Twitter or to register as an SFBB Insider.