How to Create a Position Cheat Sheet in Excel
I’m going to try something a bit different here.
Instead of analysis, let’s try an instructional piece about how to add a feature to your draft prep spreadsheet. In the instructions that follow, I’ll show you how to make a cheat sheet based on auction values. When you’re done, you’ll have something like this…
Be Sure to Let Me Know…
Making your own spreadsheet is a bit niche. Discussing lengthy Excel formulas, a bit more niche. So there’s a chance this won’t be well received. But I know you folks are out there and I have a feeling those making their own fantasy spreadsheets are likely to be hanging out here at Rotographs. I’m hoping there’s an interest in this topic. So be sure to let me know if this hits the mark and if it’s something I should consider for future posts.
NOTE: I will be using Microsoft Excel 2013 to demonstrate this process. I believe all the formulas will also work in previous versions of Excel, Google Sheets, and other free Excel alternatives, but there may be subtle differences. I also assume you have some familiarity with spreadsheets and this is not your first rodeo.
The instructions below start with a download from the Auction Calculator, but you could start with dollar values of any sort. You’ll just need to use some creativity to adjust the formulas according to the setup of your own spreadsheet.
| 1. | Visit the Auction Calculator and download a set of dollar values specific to your league’s settings. When you’ve completed the setup, click the “Export Data” link and a “Fangraphs Leaderboard.csv” will download to your computer.
 |
| 2. | Locate and open the “Fangraphs Leaderboard.csv” file in Excel (or your spreadsheet application of choice). Depending on your computer’s settings, you may need to right-click on the file and choose to “Open with>Excel”.
After opening the file, perform a “Save As” and save the file to an Excel file format (e.g. “.xlsx”). Finally, rename the worksheet. Right-click on the lone worksheet tab and choose the “Rename” option. Give it a name like “Rankings”.  |
| 3. | For the sake of simplicity, I’m going to delete some of the information from the spreadsheet that I won’t be using (the PA, mAVG, mR, mSB, mHR, PTS, and aPOS columns).
 Not that these aren’t helpful, but I’ve got limited real estate to work with on this web page. You can definitely keep these columns in your file. |
| 4. | The next issue we have to address is the fact that some players have multi-position eligibility. Take Buster Posey for example. You can see from the image above that he’s eligible at C and 1B.
This doesn’t work well with cheat sheets and it could be argued that drafting a player and putting him anywhere besides his most valuable position (“MVP”) is an inefficient use of resources (dollar values are calculated assuming a player’s MVP is used). To determine each player’s MVP, start a new column. In cell E1 (or your first empty column), type “MVP”.  |
| 5. | Copy the formula below and then paste it into cell E2 (or the second cell in the new column you just started). The formula assumes that cell C2 contains the position for the first player listed.
=IFERROR(IF(FIND("C",C2,1)>0,"C"), IFERROR(IF(FIND("SS",C2,1)>0,"SS"), IFERROR(IF(FIND("2B",C2,1)>0,"2B"), IFERROR(IF(FIND("3B",C2,1)>0,"3B"), IFERROR(IF(FIND("OF",C2,1)>0,"OF"), IFERROR(IF(FIND("1B",C2,1)>0,"1B"),"DH"))))))What is this doing? The formula first searches the text in the POS column (using the FIND function) for “C”. If it doesn’t find “C”, it proceeds through the remaining positions in order of decreasing scarcity. I generally assume the order of scarcity to be C, SS, 2B, 3B, OF, and 1B. If you disagree, just swap the positions in the formula into your order of preference. The “IFERROR” functions are needed because if the “FIND” function is not able to locate “C”, the result of the formula would be an error. These layered “IFERROR” functions are essentially saying, “look for ‘C’, but if you get an error, then look for ‘SS’, and if you get an error there, then look for ‘2B’, etc.”. |
| 6. | After adding the formula to cell E2, copy the formula to the remaining cells in that column.
The easiest way to do this is to click once again on E2 in order to select it. Then double-click on the small square that will appear in the lower right-hand corner of the cell.  |
| 7. | Next we need to determine positional rank. I’d ultimately like to see “OF-1” for Mike Trout, so we need a formula to determine that he is in fact the first ranked outfielder.
Start another column. For me, that’s column F. Type “Rank”.  |
| 8. | Enter the following formula in cell F2:
=COUNTIF(E$2:E2,E2)Then repeat the same process of double-clicking on the box in the corner of cell F2 to copy that formula to the remaining cells in the column. When you’re done, it should look something like this:  This column is a “positional ranking”. Trout is the first outfielder. Harper the second. Goldschmidt the first 1B. Altuve the first 2B. Then Betts is the third outfielder. You get the idea. In the example file I’m working with, here’s what that COUNTIF formula turned in to for Giancarlo Stanton: =COUNTIF(E$2:E9,E9)That “$” in the formula is using an “absolute” cell reference, meaning as the formula is copied to nearby cells, that part of the formula does not change. The “$” being in front of the “2” means it will stay locked on the second row. The rest of the formula is missing the “$” so those cell references are adjusting as the formula is copied. For Stanton, this COUNTIF formula is counting any item between cells E2 and E9 that meets a specific condition… That condition is “E9”. Meaning, “count any player between E2 and E9 that is also an OF”. |
| 9. | Next, we’ll combine the player’s MVP and positional ranking to make a better looking ranking. Going back to Stanton, I’d like his rank to display as “OF-4” in one place.
To start this process type “POS RANK” in cell G1.  Then enter this formula in cell G2: =E2&"-"&F2This should result in “OF-1” for Mike Trout. Using the ampersand is a shortcut to using Excel’s CONCATENATE function, which allows you to attach strings of text together. It’s a heck of a lot easier than typing that long word… This formula is appending the POS (“OF”), a dash (“-“), and the Rank (“1”) for Trout. Complete this step by copying the “POS RANK” formula to the other cells in that column.  |
| 10. | That completes the work on the “Rankings” sheet. Start a new worksheet and name this “Cheat Sheet”.
 |
| 11. | Fill out the new “Cheat Sheet” tab as shown below.
|
| 12. | Enter the following formula in cell B2:
=MATCH(B$1&"-"&$A2,Rankings!$G:$G,0)NOTE: This assumes you labelled your first sheet, “Rankings”. There are many “$” in there and they’re all important. We’ll eventually be copying this formula to the rest of this “Cheat Sheet” tab and need to be careful about the cell references and how they’ll change when that happens. This formula results in “8” in my example spreadsheet.  The MATCH function will look in a designated row or column and tell you which item in that row or column represents the match. This particular formula is looking in column G of our “Rankings” sheet for the string “C-1″ (that’s what the B$1&”-“&$A2 evaulates to (remember the ampersand can string text together). The final element of the MATCH formula is the zero, which means I’ve instructed Excel to find exact matches only. I only want “C-1”. If it were to find “C-11”, I don’t want that to be considered a potential match. Looking at my Rankings sheet, Posey is C-1 and is the eighth item in column G. The formula appears to be working.  |
| 13. | Now add a little more to the formula in cell B2. The pieces to add are in brown text and the pieces you’ve previously added are shaded gray):
=INDEX(Rankings!$A:$G,MATCH(B$1&"-"&$A2,Rankings!$G:$G,0),1)What we’re doing is using the combination of the INDEX and MATCH functions to find player names. This INDEX and MATCH strategy is similar to using VLOOKUP, but it’s even more powerful and flexible. INDEX wants three inputs:
|
| 14. | We now just need to add one more component to that formula. It’s working for C-1. But we eventually will need to build this out to accomodate “OF-60”. And the Auction Calculator isn’t going to spit out “1B-60” or “DH-60”.
To prevent errors, we’ll use IFERROR again: =IFERROR(INDEX(Rankings!$A:$G, MATCH(B$1&"-"&$A2,Rankings!$G:$G,0),1),"")This can be interpreted as, “If there’s an error with the INDEX/MATCH formula, just show blank text”. That’s the “”. Now drag that formula down to fill out the rest of the “C” column. Then drag it to the right to populate the remaining positions.  |
| 15. | Voila!
 |
| 16. | If you want to make it look pretty, click the “Format as Table” button on Excel’s Home tab and choose a color pattern.
 Excel offers many beautiful colors… but not “Rotographs Brown”. I had to make my own.  I only set this up to show the top 20 at each position, but the beauty of the formulas used is that you can easily expand this by dragging the formulas down as far as you want. The only limitation is the number of players exported from the Auction Calculator. |
Wrapping Up, Want More?
Was this helpful? Interesting? I’d love to hear your reaction in the comments. Would you like to see more posts like this in the future?
Hopefully your feedback will be positive and I’ll be back with more. But in the meantime, if you’re looking for more information on building a draft or rankings spreadsheet, here are some other instructions I’ve written previously:
- How to Track Drafted Players in a Spreadsheet – This set of instructions guides you through adding a drop down menu that lists each team in your league. You can quickly use this drop down during the draft to mark off drafted players and assign them to teams in the league. We added something similar to this in the first edition of the Rotographs Rankings Spreadsheet.
- How to Automatically Shade Drafted Players in Excel – This picks up where the first bullet leaves off. Once a player is marked as drafted, you can have Excel shade the player’s name out or you could strike-through the information like we also added to the Rotographs Rankings Spreadsheet.
- How to Calculate Projected Standings for your Draft – Several weeks ago I wrote about Why You Should Aim for Third Place and the idea that this may be the most efficient use of resources during a draft. In the comments, some grew skeptical about how practical this really is, questioning how you can feasibly track where you fall in the standings while the draft is ongoing. This guide should help you set up your spreadsheet so you can see updated standings in just a few mouse clicks. The first bullet above is also a prerequisite for this one to work properly.
Tanner writes for Fangraphs as well as his own site, Smart Fantasy Baseball . He's the co-auther of The Process with Jeff Zimmerman, and has written two e-books, Using SGP to Rank and Value Fantasy Baseball Players and How to Rank and Value Players for Points Leagues, and worked with Mike Podhorzer developing a spreadsheet to accompany Projecting X 2.0. Much of his writings focus on instructional "how to" topics, Excel, and strategy. Follow him on Twitter @smartfantasybb.
Very well done. Nothing here is particularly complicated, but I hadn’t considered putting my sheets together in quite this way before. Thanks for the article.
Thanks, RC! Glad it’s helpful.
Great article, I’ve been creating my own cheat sheets for a two years now and this made it a lot easier with the formula’s. I would be very interested in more articles like this on how to make cheat sheets or using excel to organize stats and data for draft day.
I can’t even verbalize my enthusiasm for this post. Admittedly, I am an Excel nerd and my fellow drafters in baseball (and football) give me the strangest looks with the color coding, lists, and multiple tabs I use during my draft. This is just another tool I will be looking to add to my toolkit.
One question: How would this be changed based on personal feelings/research on positions? Are players easily changed from one order to the next?
Joe, I feel like a fancy color coded spreadsheet is intimidating to the guy sitting next to me. It can’t hurt, right?
You do ask an important question that I wish I mentioned in the article. The ordering on the cheat sheet is COMPLETELY dependent upon the ordering of players on the “Rankings” tab.
So one practical way to change the cheat sheet would be to adjust a player’s dollar value and then sort the “Rankings” tab by the “Dollars” column, largest to smallest.
This also means you would not want to sort the “Rankings” tab alphabetically, or else the cheat sheet will also just mirror that.
If you didn’t want the players sorted by dollar value and you wanted to have a “My Rankings” column, you could just type your own rank order in that column. Then sort the players by that information.
This is excellent. Really well done and such a useful tool. Thanks!
Love. Like emoji-with-hearts-for-eyes love.
Tanner, great stuff! Been using your Smartfantasy Excel docs for the past two years and they help out a ton. Check out his website and grab his e-book it has tons of tips and tricks in it!
Ha ha. Thanks for the plug!
Huh, that concatenate function would have come in handy a couple days ago. Oh well, better late than never.
you can also avoid typing “concatenate” by just sticking cell references together with & such as = A1&B1 you can even delimit your concatenated text in this manner: = A1&”_”&B1
This will save me hours. Thank you kindly sir.
More!!!
definitely helpful even for regular Excel users. I would be interested in more of this. Thank you. especially in a build your own draft tool. where it shows you half way through the draft hey you need a little more SB etc.
Hey Matt, because I’m not sure how many more Excel pieces I’ll get published before draft season, you may want to check out those three bulletted items at the very tail of the article. They can help get you started down the “build your own draft tool” path and even show you your projected standings in all categories.
More like this, yes, please.
Great! Super helpful – thanks for this!
Tanner, I have a spreadsheet setup dealing with an auction draft. You will need to put in your salary cap. But then it automatically calculate how much money was spent on your team, how much money you have left to spend and what is your maximum price you can spend on a given player. It also shows how much was spent on offensive players and how much was spent on pitchers. I keep every draft to see trends year in and year out. If you have interest in getting it from me and me discussing the ins and outs on how to setup or change things, I am up for going back and forth in E-Mails. Let me know
Question for you. I have a column for which team a player was drafted by with a dropdown list. Is there a way to copy a player to a team page based on the condition in this drafted column?
Same way he does the position rank above. Just concat team on there and index>match to that identifier.
Hi Hack,
To elaborate on what Justin has said, to do what you’re describing I almost think you’ll need a second piece of information. Not only do you need to track the team the player was drafted by, but you’d also need the position they’re being assigned to.
So in one column you have “Team Drafted”. In the next column “Position Assigned”. In that column you could enter things like “1B”, “CI”, “OF1”, “OF5”, “BENCH1”, “BENCH4”.
Then you could concatenate them together. “Team 1-1B”, “Team 7-CI”, “Team 9-OF1”, “Team 9-OF5”, “Team10-BENCH4”, etc.
One you have that you could populate a “Team Depth Chart” page much the same way as the instructions above. Team name would be on the top row of table, position on the left.
I realize that’s a lot to track during the heat of the draft. So this may not be practical.
“Double click on this bad boy”
Ha. Have to try to lighten the mood somewhat! Reading that much about Excel can get a wee bit dry.
Semi-related question, would it be possible to add categories to the auction calculator? Specifically Losses and BB issued for pitchers? I realize these may not be super common categories and there’s probably a lot that goes into the calculations.
Hi Nate, I’ll try to figure out the best way to direct this request.
Love this spreadsheet. I use a similar one at my drafts. One additional thing I’ve done is organize by $ value tiers ($40 –> $30 –> $20 tiers, etc.) instead of rank alone. This makes it easier to see the large drop in value from #1 to #3 in catcher vs. the smaller drop-off from the #1 to #3 in 1B… it also makes a great reference for other drops in value between tiers that you may not be aware of just by looking at rankings alone.
was trying to do the same but couldnt find way to execute it.. do you mind showing how you altered formula for that?
Chadkay, do you just manually key in tiers next to player names? Or do you somehow determine tier by a formula?
Is there a way to get the auction values for pitchers straightened out? For example, our league uses IP as a category. However, the auction value calculator will not assign a weighted value for IP, so the 500 it projects has about 300 people projected for less than 30 IP. It leaves off so many SP that look to get several starts this year. For example, Sonny Gray doesn’t make the list, but Eric Jokisch does.
Brad Johnson has written a series of articles on ways to use the Auction Calculator. He discusses some quirks and ways to alter pitcher values. You may want to read through his suggestions and see if you can make it work better for your league.
Auction Calculator Tutorial
Advanced Auction Calculator Use
The Sundry of Ways I Use the Auction Calculator
Mookie Betts above Giancarlo and McCutchen? I like Mookie too, but come on.
Hey Justin, I’m just the messenger! I used the standard Auction Calculator setup to demonstrate the spreadsheet. No comment on the actual dollar values that come out. But keep in mind they’re all based upon Steamer projections. So it may actually be the projection you disagree with, not the valuation. The projections also have a noticeably lower games played projection for Stanton, surely hurting his value.
* Immediately favorites page for future use*
Thanks Tanner. This feeds into my love a S/S and baseball perfectly!
Please do more of these!
Tanner-
On your site I learned how to use excel items like tables and VLOOKUPS.
I’m now the designated Excel expert for a rather large charity. I’m not sure whether to thank you or be mad at you for getting me stuck doing this. I’ll go with the former.
But think of all that job security you now have!
Hey tanner,
the one issue I’m having is that when i paste the positional rankings in step 8, I have multiple players listed at number 1. For example Mookie, Harper, and Trout, are all listed as the #1 OF for me. Any way to fix this?
Hi Shane, what is the formula you have for Mookie Betts in the “RANK” column of the “Rankings” tab? Also, make sure that COUNTIF formula is referring to the column that has the “MVP” in it. So if you did not delete out the columns I deleted out, you won’t want to use “E” in that formula. Use the column letter for where your “MVP” is.
Ah, there’s the problem. Thanks!
This was really helpful. Is there a formula to add for MI or CI?
Hi Roy, there’s not a real practical way to handle that without adding several columns or lengthy formulas to get there. You may want to implement the idea from “abalasco” below.
If you append the dollar value to the end or beginning of the player name, you’d easily be able to look at the 2B and SS columns to see who is the highest values MI left.
So on the “Cheat Sheet” tab, my formula in cell B2 for C-1 becomes:
=IFERROR(INDEX(Rankings!$A:$G, MATCH(B$1&"-"&$A2,Rankings!$G:$G,0),1),"")&" ("&IFERROR(INDEX(Rankings!$A:$G, MATCH(B$1&"-"&$A2,Rankings!$G:$G,0),4),"")&")"There’s a lot going on there. But it’s basically repeating the exact same formula, but also going to the Rankings tab to find the player’s dollar value.
All that really changes is the “1” toward the end of the INDEX formula changes to a “4”. The 1 pulls back player name. The 4 pulls back dollar value (which is in the fourth column of my example sheet).
Excellent article; I would love to see more like this. I modified my Cheat Sheet to show the auction value with the player name (just used concatenate again).
(37.3) Buster Posey
Mind explaining how you added auction value? cant figure it out.. thanks
Great idea, Abalasco. Love it.
Barry, see my comments just above Abalasco’s original comment. I gave a formula to use there.
This is great! I learned a couple new tricks
Bit by bit, Fangraphs is destroying the competitive advantage gained by those of us who built solid spreadsheets, auction calculators, etc. on our own while our competition floundered away with magazines printed before the first day of spring training. That’s a compliment, but one I don’t like making.
I hear you, Rockies. We’ll just need to step our game up in other areas. Keep improving!
Thank you, this is awesome! I’d definitely be in favor of more like this.
Anyone know how to do this in Google Sheets? Once I get to step 12 the formulas need to change, but I’m not great with it :-/
Never mind, I’m an idiot.
SUPER helpful, thank you! Instituted all of the above steps. Opened the tabs for your 3 previous sheets re: assigning drafted players to their teams, shading them out and calculating running standings, but I’m a bit concerned about my ability to implement the 3rd.
Specifically, if I download the Fangraphs Auction Calculator as the basis for all this, then the projections I get for each statistical arrive in a format that is $-based, not counting-stats based. What can I do about that? Thanks again.
Ah, yes. Good point. You somehow need to get the projected stats for each player next to their name. You might find this series helpful.
That assumes you’re starting a spreadsheet from scratch. But with a little creativity you can tweak those to fit into what you’ve already started.
Gold
REALLY liked this post. I love Fangraphs, but feel there is an opportunity for improvement in regards to having more posts like Tanner’s in the future. Yes, that sentence wasn’t the most bestly [sic] constructed, but it’s Friday night?
I’m in for getting a group together to share Excel ideas, and… I think Excel-based posts like Tanner’s could/should be a semi-monthly Fangraphs feature? Right?
… a lot of potential here…
Great Article. These are great tips and such perfect timing. The last step in my spreadsheet was the positional ranking. I had been doing that by hand in the past.
Keep these excel tips coming.
This is information worth buying… which I’ve already done as I’ve purchased the bundle package! Thanks for all you do!
Thank you, Chunk!
Aw man, this was super fun to execute. It didn’t take long at all! I did some tinkering to add SP and RP too. Fun stuff. Thank you.
This was good fun, thanks Tanner. I’ve got two very basic questions, however:
1. I’ve put together a separate sheet for my pitchers, using all the same steps up to Position Rank column. I want to merge that into the Cheat Sheet, but cannot get the values to show by cutting and pasting the formula from the rest of the sheet, even if I change the source from “Rankings” to “Pitching” (name of my pitcher ranking sheet).
2. I want to do a separate sheet that just ranks the players overall by dollar value, including both pitchers and position players, but I’m having a lot of trouble when I merge the data. I think this is a “paste special” issue, and will try that, but do you have any other ideas?
Thanks again, this was a blast, and I’m going to use it for both my league drafts this year.
Vince
vjturotte, does all the rest of the information on your “Pitching” sheet mirror the “Rankings” sheet? Are there any more or less columns? Are the dollar values and/or positions in different columns than they are in the hitter sheet? I ask because you’d have to alter all of the formula references to account for those changes too, in addition to just the sheet name changing.
I don’t have an elegant way of merging pitchers and hitters. Sadly, I have separate tabs for hitters and pitchers in all of my own draft spreadsheets. Copying and Pasting, assuming the dollar value is lined up to be in the same column as on the hitter information, seems like a quick and easy way to do it though.
Vince,
I’ll let you know how I did it when I’m in front of my computer. It was simpler than I thought and I’m an intermediate Excel user. I’ll share later tonight!
Great stuff Tanner. As a total excel nerd this is pure gold! Keep it coming.
My question for you/FG community is around the MVP methodology of player valuation. It is something I subscribe to fully. Using Posey as an example I only use his CA eligibility in my valuation because by drafting him at 1B you lose the positional value and overall max value of Posey. I use z score in my valuation and calculate my own dollar values. My question to the crowd is do people still rank Posey at 1B along with catcher but only use his CA value for overall rankings? I currently do not, but note the multi positional eligibility. My reasoning is that by using the most valuable position (which I have similarly ranked to tanner), you are maximizing player values for ranking purposes.
Hi pdamore,
If folks were to rank Posey among other 1B, then they must adjust his “stats above replacement” or “z scores above replacement” or “SGP above replacement” to account for him being an actual 1B.
It would be flawed thinking to give him a dollar value compared against catchers but then to assign him to 1B. You don’t realize that full value in that situation.
Keep these coming. Great way to learn Excel, and lots of fun, too!
Hi Tanner,
Is there anyway to get conditional formatting from a “rankings” sheet used to populate the cheat sheet to automatically update on the “cheat sheet” sheet itself?
I’d like to track players drafted on the rankings page but have the conditional formatting that tells me they are drafted update the cheat sheet as well. (hope this makes sense).
Thanks for all the excel info.
steve
Hi Steve,
Outside of using a macro, which I make a point to never use, there’s not a way to do it exactly the way you’re describing.
My proposed solution would be to put columns between each position. That column would do an INDEX/MATCH to find out if the player was drafted and pull that answer back.
For example, if you’re using the “Team Drafted By” from one of the other links I shared, you’d do INDEX/MATCH to pull that team name next to the player.
Once that formula is established, then I’d recreate the conditional formatting methodology to just apply formatting on this “Rankings” sheet. You could even hide the “Team Drafted By” column one you have this set up to get the clean appearance of the original cheat sheet.
Hi Tanner,
This worked well. The INDEX/MATCH pulled the team name but also returned a “0” for players not yet drafted/assigned a team. I couldn’t figure out why, so I just changed the conditional formatting to format any value >0 and it worked. Figured I’d share in case anyone else had similar issues.
Thanks again!
steve
Great! Thanks Tanner. Really looking forward to putting the spreadsheets to use this year!
Great stuff,
My 2B column isn’t filling out on the “cheat sheet” tab, but everything else is working fine. Any suggestions or something I missed? Thanks!
HighCheese,
There should be any kind of tricks or anything special you’d have to do for 2B. My guess is it just may be a typo of some sort. Maybe check out the “Rankings” tab and the “MVP” or “POS RANK” columns. Make sure you don’t have any extra spaces in the “2B” formula (e.g. you don’t see “2B -1”).
Then on the “Cheat Sheet” tab, make sure it’s just “2B” and no space in there or anything.
Tanner,
Been reading FG for a long time, never left a comment before, so I guess “long time listener, first time caller”. This article moved me to register and comment because I definitely would like to see more of this sort of article.
I have been using Excel for years for draft prep and have become fairly proficient, first simply to create printable cheat sheets to now using it as a draft tracking program. In the past couple years I have streamlined it enough to actually use during the draft without fear of a hiccup that will throw a wrench into my whole draft, but I am always looking to expand and improve and learn new tricks. There are plenty of Excel related websites out there, but I love seeing how others are using it specifically for baseball related purposes.
Keep it up, the more the merrier. Off to read some of the links you posted.
Thanks!
Wow! Thanks, Chuckie.