Welcome to part 6 of Excel-ing at Fantasy Baseball. If you haven’t read the first three installments of the series yet, I’d recommend you start there (part 1, part 2, part 3, part 4, and part 5), so you can see the material we’ve covered (and maybe even go back to the other article I wrote linked that showed some of the benefits of Excel), to see how powerful and useful a tool Excel can be in Fantasy Baseball. It is also vital as we move on through this series that you understand the fundamentals of the previous articles. Finally, we have the beginning of your own spreadsheet built over the last few weeks that we will continue to build this week, so skip the first 5 parts at your own risk.
What?! Your league is not using Fantrax? Inconceivable! Check out everything Fantrax has to offer and I’m sure you’ll come around to our way of thinking.
Using Excel in Fantasy Baseball
Review/Recap
In part 1 we covered:
- the basics of formulas
- cell references and how to lock a cell reference
- some basic formulas such as:
- sum
- average
- count
- and how to download and import data into a sheet
In part 2 we covered:
- Tables
- How cell references work
- How to name a table
- The ease of copying formulas within tables
In part 3 we covered:
- Ways to look up data
- Vlookup
- Index-match
- Xlookup
In part 4 we covered:
- Conditional Formatting
- Logic Formulas
- Importing tricky ranks
- Delimitation
- Text To Columns
- Combining text
- Cleaning up text
In part 5 we covered:
- Updating ranks
- Cleaning up duplicates and some of the ways to do that (this one got a little messy)
Today we’re going to build pages for each of your leagues (which you may have already started) and explore the indirect function.
So, to start we’re going to make sure we have all of our leagues pulled in. We did this with one league and maybe you went and did it with all of your leagues already – if so – great!
If you haven’t go and do that – as a reminder this is how we pull in the league:
- Go to the league you want to pull in
- Go to the player pool
- Change the status/team drop down to all
- Click the download arrow to download the file as a csv
- Open the csv (it will auto open into whatever software it defaults to – if it opens in numbers that’s ok – this is just a temporary file)
Now that you have that file you can copy and paste the player-age columns of the table into a new worksheet for each league – repeat this process as many times as you need.
If you have leagues that aren’t on Fantrax (WHYYYY), you should import those as well.
On NFBC leagues go to the Free agent pool (transactions – free agents), change the drop-down from free agent to owner status, and download the table.
There may or may not be an easy way to do this on ESPN or Yahoo – I don’t play on those platforms so I am unable to test run or tell you how.
On each of these sheets you should make sure that the data is formatted as a table and name the table appropriately.
Once you have every league in its own sheet, formatted as a table, and name the table then go back to the ranks sheet and add a column for every league you have. It’s up to you where you put them – but I would recommend at the end for now. Name the column the SAME as the name as the table for each league – exactly the same.
SIDE BAR: Your table is probably getting pretty large now. Tables automatically lock the top row, but let’s also lock the player column so that no matter how far over you scroll you always get that. Highlight the first row – go to view – and select freeze first column:
Now that we have added columns for every league and the column is named the same as the table for that league, we will now pull in the player statuses. You can of course do this with an xlookup or other lookup as we’ve explored, but we’re going to use an indirect to speed it up.
Indirect
This is what the formula will end up looking like in xlookup – adjust accordingly for other lookups:
=XLOOKUP([@Players],INDIRECT(J$1&”[Player]”),INDIRECT(J$1&”[Status]”),””,0,1)
Indirect builds a formula out of the text referenced therein. So – what this is doing, in this case, is building a formula of the Table Reference, using some of the tricks we learned last week for combining text strings in a formula with & and quotes.
If we had built this formula alone without xlookup, it would return the table reference and then the column [Player] and column [Status]. In the xlookup it functions just like the other references do in xlookup.
This formula is why we needed to make sure that the tables are named for the league and the column heads are identical so that the moving reference always knows where to look.
You’ll also notice here that I’m using j$1 (or the column your first team is in), not the reference you get if you click on the header row because that WILL NOT change when you copy it – this approach will. (Remember- the $ before the 1 means the 1 – ie the header row – stays locked as it goes down the column.)
So now this is the formula I will have in every column for every team and can just copy it over as needed when I add a new team
Once you build this formula you can copy it over.
The formulas in these columns will look like this:
And when calculated:
Do this for as many leagues as you have and copy the formulas over.
Now, on your rank sheet, you can sort any league by FA and see the top-ranked player (by aggregate average rank or whatever other column you decide to rank by). This can be super useful if you want to peruse the free agent pool by dynasty rank or to see what top prospect on an analyst list is not currently rostered.
Or – and you’ll also see a way to do this when we finish your league sheets – you can pick another team’s roster to see if you’re looking at a trade with them.
So – let’s go finish those team sheets
Team Sheet
Now go back to your team sheet. You should have the table for the whole league (that you can easily update in the future just like you would the ranking tables following a similar process to above.
BUT – we’re not done. Each of these team sheets is also helpful if they have your roster on it.
We’ll create a new table: name the first column player, the second column position, the third average, and then the rest the names of the different rankers we used (copy and paste the header row text from your ranks sheet).
Now go back to your league on Fantrax and go to your team page and you’ll see an arrow to download the team. When you open that CSV it will be split by pitchers and hitters. You can either copy the whole column or do it in two copy/paste actions so as to not copy the blank rows and the pitcher label (if you do have those just delete them after copying). Copy the names in the player column and if you haven’t yet – make sure the whole new table is formatted as a table with the top row as headers.
Now use your preferred lookup formula to look up the position from the ranks sheet – but wait!
We can use indirect again – but this time for the column name, not the table name.
The first two functions of the lookup can be normal – with the reference being the player column, the second column being the player column on the rank page, and then an indirect to target the column of the same header name on the rank sheet. My formula looks like this with player position in column J
=XLOOKUP([@Player],Ranking[Players],INDIRECT(“Ranking[” & J$1 &”]”),””,0,1)
So that looks up the first player on your roster, the player on the rank page, and then their position. Copy and paste that across and everything should update!
How This Updates
Now, one of my personal favorite rankers dropped his updated Dynasty OBP prospect ranks today so let’s go grab Chris Clegg’s rankings, add them to the overall sheet and to the league sheet. You can find them here.
So create a new sheet next to Clegg’s overall ranks, make it a table, and name the table CleggProspects.
Then go to the ranks sheet and let’s add a column called Clegg Prospects after the Cross Prospects column and lookup the data. Now that’s there go back to your team sheet(s), add that column, and copy the formula over – this is what it should look like (plus any other rankings you’ve added).
Now add a similar table to all of your league sheets that you want. I don’t put tables like this on my best ball leagues or my NFBC leagues because this isn’t really helpful unless you’re trading. Also, my NFBC leagues are redraft.
(On the redraft note: you can add redraft ranks to this or make a similar sheet (even save as and then put in new data) if you are in redraft leagues with trades or if it would help with waiver wire decisions for you pulling in the various redraft ranks out there that are often updated throughout the year.)
Trade Comparisons
One way the team chart with your roster can be helpful is in comparing teams. You can follow the same process you did before to get another team’s roster and copy it below your team’s roster in the table – getting the ranks for that team. Then, if you are considering a trade, you can look at the players and even, when you are ready to make or consider an offer, just type in the names of the players you are trading away and the ones that you would get back on rows by each other to see the ranks that those players have (reminder that this is not and CAN NOT be the end all be all of making trades).
We’ll cap it there for today – next week we’ll add in an age sheet that will continue to update player’s ages and add an age column to your team sheets as well (with conditional formatting) so that you can see how old your team is and how old all of the players are. We’ll also create a shares sheet so that you can have a single clean sheet to see who you roster or who rosters a player across all of your leagues if you decide to go trade for someone.
For more great Fantasy Baseball Check out Mick Ciallela’s Rest of Season Rankings! Mick was the No. 1 Ranker on FantasyPros.com for the 2020 MLB season.
Fantrax was one of the fastest-growing fantasy sites of 2020, and we’re not stopping now. With multi-team trades, designated commissioner/league managers, and drag/drop easy click methods, Fantrax is sure to excite the serious fantasy sports fan – sign up now for a free year at Fantrax.com.