Excel-ing at Fantasy Baseball: Part 1 – The Basics

In my article last week, I talked about how we as fantasy managers must re-think how we utilize experts’ rankings in our analysis and fantasy work and study. In this, I showed and mentioned how I utilized Excel as a major tool for integrating rankings, pulling rankings from a number of experts together to utilize all of them better. I even had a few people ask if I was going to share the sheet that I showed snippets of in that piece. While I will not be doing that (out of respect for my work, because it’s customized to my needs, and, and most importantly, out of respect for the rankings I pulled in because many of them are behind pay-walls and those analysts should be compensated for their labor), this series will allow you to make your own sheet, that does what you want, and equips you to update and change things yourself to customize your own fantasy rankings and strategy.

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.

Why Use Excel

The power and usefulness of spreadsheets have been shown time and time again in the Fantasy Baseball Industry. This is clear on a simple level with Dynasty Guru’s providing early and specialized access to their rankings on a google sheet, to a more complex use with Derek Rhodes’ handy google sheets to track Best Ball drafts. Both of these examples show how spreadsheets are a powerful tool for data analysis and creation. Indeed, over the last month or two, I’ve built a spreadsheet for Chris Clegg to help automate part of the ranking process for him and one for Mike Kurland to speed up his amazing spring training tracker. Both of these projects stemmed from this general rule when working with data – if you are doing the same thing repeatedly, there is a cleaner way to do it (most of the time).

This utility of spreadsheets is only enhanced by the ease of downloading CSV (comma-separated value) files that are easily opened or copied into a spreadsheet. Sites like Fangraphs allow you to download pretty much every data set, including all of the projection systems or custom sheets you design. Here at Fantrax it’s a breeze to download draft results, the player pool, and players with owners so that you can easily integrate your rosters and teams into the other data on your sheet. With all of this ease of data access, we are doing ourselves a disservice if we fail to utilize spreadsheets to process and analyze data for us.

Types of Spreadsheets

There are a number of spreadsheet software. The most common you’ll see are Excel and Google Sheets. You can also use tools like Numbers and Libre Office’s version of Excel. However, among all these tools Excel is the most powerful and the one I will be talking about primarily, while doing my best to note when something will NOT work on Google Sheets (other tools will have similar facets, but mainly I’d highly recommend getting Excel if you can (there are educational licenses at many academic institutions if you are in/connected with a college, or I would invest in Office if you have the means).

The Basics

In order to get to the point of being able to build sheets like what I had pictured last week, we’re going to have to start small and build up to that. We will get to using tables, using lookup functions (and in honor of Keaton DeRoacher Index-Match), using conditional formulas, conditional formatting, text delineation, indirect functions, . . . and all of those beautiful things. But in order to get there we have to start with some basics rules of Excel – (if you have done much in spreadsheets before you may find this to be very basic, and for that, I apologize, but I want to make sure everyone gets a firm standing on what is happening before we dive into the more advanced things).

• Formulas
  1. Formulas are anything that starts with a “=” – if you have a cell beginning with that, it means a formula is coming. From there you can do all sorts of formulas. But for now, that’s the rule. If it says =, it’s a formula
• Cell references
  1. When we introduce tables later on in the series how you reference things will change slightly at times, but either way, this is necessary, and if you are using anything other than Excel possibly even more so.
  2. =A1 will return the value in A1, =B7 will return the value in B7
  3. Now, remember that if you’re doing the same thing repeatedly, you’re doing something wrong, so you don’t want to write in the same formula multiple times. If you have a formulate in B1 that says “=A1” and you copy and paste that formula down column B, excel will adjust automatically so that the formula in B7 says “=A7” and will return that value. However, what if you want all of column B to return what is in A1 (this would be an odd situation, and more likely you are then multiplying the value in A1 by a value that changes). Then you can “lock” the cells.
  4. Cell locking
     1. If the letter or number reference of a cell has a “$” in front of it, then when you copy that formula, that part of the formula will not change.
     2. So if the formula in B1 in the example above now says “=$A1” then when you copy the formula down, it will do exactly the same thing as before, because it will keep it in column A, but keep going down so B7 would now say =”$A7” . . . however, if you copied this formula over into column C and down then the formula in C7 would still say “=$A7” and return the same value as B7 did because the $ in front of the A means that doesn’t change. If you did not have the $ in front of the A the formula in C7 when copied would be =”B7.”
     3. In the same way if you put it as =”A$1” then when you copy it down B7’s formula will be =”A$1” and if you copy it over C7’s formula will be =”B$1.”
     4. Finally, if you lock both, so that it is =”$A$1” then no matter where you copy and paste that formula – the return will always be the value in A1.

Basic Formulas

Some basic, yet helpful, formulas you will use – when in the formula bar at the top of the page if you select the fx it will give you more info on formulas and let you search through them.

1. = sum() this will add up all of the numbers in the range (group of cells) in the parenthesis
2. =average() this will average the numbers in your range
3. =count() this will tell you how many data points are in the selected range.
4. You can also see a number of these when you highlight the data at the bottom right part of the Excel :

Downloading & Importing

Now that we have a few of the very very very basic aspects of Excel – let’s mess with some data. To start, open a spreadsheet (Excel, Google Sheets, etc).

Let’s begin by getting some trusty rankings into our spreadsheet. Eric Cross dropped his updated Dynasty SP ranks recently and since those are clearly going to be invaluable and useful going forward, let’s go to those here. Then highlight the table (you can drag your mouse/trackpad, highlight the first few cells on the top right and then scroll down and hold shift and click in the bottom rightmost cell, or however you prefer, and then hit copy (either right-click and select copy, or, my go to, cmd+c (ctrl+c on a windows device)). Go to your open spreadsheet and in A1 (the upper left cell) paste it (right-click paste, cmd+v, or ctrl+v). It should go like this:

Now that we have this data set in, let’s do some simple functions. At the bottom of the table in the age column type =average( and then select the data for the average column by either highlighting it or typing in the cells and then close the formula with a )

It should look like this:  “=AVERAGE(E3:E152)” and should return 26.36 – which is the average age of all of the pitchers that Eric ranked.

To finish off week one of Excel 101 let’s import the data from one of your Fantrax fantasy baseball leagues.

To do this pick a league you want to analyze as we go forward with this series, and go to the player page. Sort the player status/team to ALL so you have every player. Then select the “download as CSV option.”

Then, wherever you have your downloads, open that file – if you are on a Mac it may default to opening in Numbers, that’s ok, if you’re on a pc it will probably default to Excel. Also ok. Select all of the data – click on id and then press cmd+a (pretty much any time I say cmd since I’m on a Mac use ctrl on a PC).

Go back to your Excel document, go to sheet 2 (hit the + by sheet 1 if you don’t have a sheet 2 already), and paste that data by clicking in A1 and pasting as before.

Now let’s name our sheets – sheet 1 that has Eric’s Rankings we’ll call “Cross 150 SP,” and then on sheet two name it whatever your league is or what makes you know – this is this league when you come back later. And now save the document and name it Fantasy Baseball or whatever else you want to name it.

It should look like this:

As you might notice in mine it may take a second to load. You just copied in 122,538 data points – that’s ok, it will just depend on your computer, its processing power, what else you have running, etc.

Next week we’ll continue to look at how we can analyze this sort of data. But at this point you have the basic set up for inserting more data, so just copy in whatever data you want on new sheets, be that Fangraph’s projections, other rankings from Fantrax or elsewhere, or any other information that you will want to use going forward, and we will continue to learn more and more about Excel so that you can at least begin to harness the power it holds. In the next few articles, we will examine some of the tools that I utilize a lot in analyzing data and looking that things including:

1. Delineate Columns
2. Combining text via formula
3. Xlookup/Vlookup/index match/hlookup
4. Averageif
5. Sumif
6. If/or/and
7. Conditional formatting
8. Delete duplicates

And one of the most powerful features – tables.

Once we get through many of those and you spend some time working in Excel with them you will be able to build a large variety of tools for your research.

Stay tuned for that and please let me know if you have questions about this content or requests on things to cover in future articles.

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.