Welcome to part 2 of Excel-ing at Fantasy Baseball. If you haven’t read the first installment yet, I’d recommend you start there, so you can see the background (and maybe even go back to the other article I wrote linked here), to see how powerful and useful a tool Excel can be in Fantasy Baseball. It is also really important as we move on in this series that you understand the fundamentals of the previous article. Finally, we began to build a spreadsheet last week that we will continue to build this week, so skip part one at your own risk.
What?! Your fantasy baseball 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.
Review/Recap
Last week 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
To begin today we’re going to once again download some data. Today we’re going to pull in Eric Cross’s most recent update of his top 500 Dynasty Players. If you need a reminder of how to do that, check last week’s article.
So, copy in that data, if you have an extra row between the headers and the top and the first row with Acuña. To do this just right-click on that row, hit delete, and choose move rows up.
Using Tables in Excel
Now we’re going to use one of my favorite tools for cleanness, clarity, and formulas – the table. Sadly if you’re in non-excel spreadsheet software this will not work the same, but, don’t worry – the rest of the material covered will work pretty similarly so stick with it.
To insert a table, simply click somewhere IN the table and click cmd+t (as noted in part 1, if I say cmd and you are on a pc use ctrl). You will get a pop up that asks if your table has headers, ours does, so keep it clicked. The pop-up will also ask you to confirm the cells and it should be the full range (=$A$1:$F$501) and click final. It should look like this:
You may have noticed at the end I also clicked the line between columns b and c and a line with arrows on both sides appeared when I hovered there. That made it so that I can see the full list. Whenever you do this it will make the column wide enough so that you can see the full text of every cell.
In your spreadsheet, you should have a tab that is the name of one of your leagues with the player pool with what team rosters those players or if they are a free agent, on that page. Do the same thing to create a table on that page, clicking on the page and going through the process of creating a table.
Once you’ve done that, add another worksheet at the bottom and move it to the front of the tabs – name that sheet ranks.
Then in A1 type Players, go to your league rank sheet, click the top cell in the player column, and then click cmd+shift+down arrow (adding each one until you hold all three, not one at a time), copy it (cmd+c), go to your ranks sheet, and in A2 hit cmd+v or your preferred method for pasting. That whole process should look like this:
You will notice that the column still has the banded rows of the color of the table. IF you don’t/didn’t want this, under the home row there is a paste dropdown:
When you click the arrow you will see this:
If you click paste values only, then it will not have the formatting of the rows with the coloring. However, this doesn’t really matter because – you guessed it – we will make this a table as well.
First – in b2 type “Cross” and then click, again, cmd+t (or go to insert –> table).
We will get into lookups next week to populate that column – but first – we need to understand how cell references work with tables.
Table Cell References
We talked about cells and locking cells in part 1, but now it is different. To explore this, go to b2 and type =, and click A2.
You’ll notice the formula is not =A2, but =[@Players]. Meaning it targets that column named Players in a table. You’ll also notice – the whole column populated – this will be unimaginably helpful going forward as we do more in tables. This also automatically assumes you want to move down with the column – which, if you recall from part 1 in locking cell references, can be very nice in most circumstances.
Now go to another sheet with a table and in the cell in row 1 immediately after the last column in the table on that page type in whatever you want and then go to row 2 and type = and go to your ranks page and click on A2. The formula should read =Table4[@Players]. Table 4 is our table on the ranks page (yours may not be named table4).
However, it would be helpful if it were named Ranks, so go back to the Ranks Table, highlight the table and then right above it to the right of the formula bar it says Table4 (or maybe 3), and click on that and type Ranking. Or you can go to the table tab at the top (which only appears when you are clicked on a cell in a table), and where it says the table name, type Ranking (or ranks). If you go back to the column on the other sheet you added and look you’ll see the formula now reads =Ranking[@Players]. So now if you look at a cell when the table and column are named you know that the reference is looking at the Ranking sheet in the players column. Understanding how this works will be VERY important when we get to indirect formulas later and in general for cleanly understanding what is happening on your sheet.
You can now delete the column you added and then go to the ranks sheet, click in b2, hit cmd+shift+down and hit delete (backspace on pc), to clear out the column. Also, go to the Eric Cross tab and change that table name to Cross500, following the steps above.
Finally, let’s add some more columns on the Ranks page. In C2 type Age, in D2 type the name of the league you pulled in they will automatically become part of the table. It should look like this but with your league in the last column:
But wait – let’s also add Chris Clegg’s great Top-500 OBP Rankings that were added today. So create a new tab, name it appropriately (Clegg), copy in the ranks from his article, make it a table, name the table Clegg500, and then add a Clegg column NEXT to the Cross column on your ranks sheet. To do this final step right-click on column c and click insert, a new column will appear – name it Clegg.
We’ll stop there for this week.
Next week we’ll dive into the beautiful world of lookups, do some conditional formatting, maybe even some conditional formulas, and figuring out how to import rankings that are less conveniently formatted for this data than others. If you want some “homework” add in the rest of your leagues in their own worksheets and add the column headers for them on the ranking sheet.
For more help, check out Eric Cross’s latest Fantasy Baseball Waiver Wire column.
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.