How to Build Your Own Fantasy Football Rankings: Part 3
Welcome back for Part 3 of my “How to Build Your Own Fantasy Rankings” series. Part 1 showed you how to build out a nicely formatted table using Andy, Mike, and Jason’s industry-leading Fantasy Football rankings, along with adding your own rankings into the mix. Part 2 then showed you how you can build a “source data sheet” and add formulas to your table allowing you to simply copy and paste the Ballers’ freshest rankings, so you don’t have to keep rebuilding your tables. Throughout those two parts, we used step-by-step guidance and references to specific cells and sources.
Part 3 is going to be a little bit different. In this portion of the series, I’m focusing on importing new rankings from other sources. Unfortunately, each and every website presents their rankings a little bit differently. Some allow exporting an excel/.csv form that will play nicely with our existing data, while others are more complicated. While every site wants to share their rankings, some sites, like that major four-letter site, have decided to make it difficult for you to “use” their rankings in your own form. Which they may not provide a nice downloadable file, most of the time you can at least highlight the rankings and copy/paste them into your Excel sheet. However, to use rankings from a site like that, you will need some advanced tools at your disposal to make them work within your datasheet.
Under the assumption that you’ve gotten the hang of the core table and basic functionality of Excel presented in Parts 1 and 2, Part 3 will be the guide to transforming this new data into the same formatting as your existing tables. The goal of this article is to serve as a reference to some of my most commonly used functions and formulas. I will walk through how to use them, but because each reference site is different, I will not walk-through the exact implementation. You will need to identify which tools work where, and then try them.
Why Would I Use “Other” Rankings?
With the Fantasy Footballers providing easy access to best-in-class rankings, why would you want to bring in outside rankings? The reason is, these amazing Baller rankings will not show up on your league’s draft platform. A few years ago, I wrote an article titled “Tips and Tricks to Mock Draft Like a Champ” and more recently, fellow writer Aaron Larson dropped his “Make the Most of Your Own Mock Drafts” article. In both articles, we highlight the importance of mock drafting using your league’s format and draft platform. Whether we want to admit it or not, being on the clock can be stressful. As that timer ticks down, it’s easy to be persuaded by the site’s default rankings showing their “best player available”. But their “best player available” may not be YOUR “best player available.” For that reason, it’s extremely important to import the rankings from your leagues draft hosting platform to compare them to both your rankings and the Ballers. Those rankings will have an impact on you and your league mates’ draft strategies. Identifying the differences, sometimes big differences gives you a better sense of when to reach for your guy or when you can wait.
Move/Copy A Tab
Just like the Fantasy Footballers, some sites will make their rankings exportable. The often-referenced Fantasy Pros is just one example. However, when you do this, you will end up with a whole new “workbook”. (Excel calls their files “workbooks”) While we could copy and paste the rankings onto a new sheet in our existing workbook, Excel also makes it easy for us to simply move the whole tab to a different workbook while maintaining the tab’s name.
When you export the file from the source website and open it, you will likely have just one tab at the bottom, which is titled with the sites name some description of the data, such as “2020 Overall”. (Note – you will need to have your rankings workbook already open in another window for this to work). To move the exported tab to your workbook, right-click on the tab and select “Move or Copy…”. A menu will open, with the first option being “To book:”. Select your rankings file’s name from the drop-down. Next, you will have the option to decide where this new tab will appear in your file in the “Before sheet” area. You can select where it will appear, or choose “(move to end)” to place it at the very end of your tabs. When you do this, the entire tab will move to your file and the exported file will close. If you wanted to keep it open, you can check the “Create a copy” checkbox, which will move the tab to your workbook and leave the exported workbook open.
Find/Replace
One of my biggest pet-peeves about the numerous fantasy football websites and ranking services is the inconsistent use of a player’s suffix (Jr., Sr., II, III), periods in “initial” names (DJ, or D.J.), and in-name apostrophes (Le’Veon, LeVeon). While it is most respectful to use players’ proper names, use of them is inconsistent across sites. Since your rankings are intended for your use only, I recommend removing all of these from the names, for simplicity and consistency.
Within almost any program, pressing “control + F” will bring up the “find” function, which also holds the “replace” function. In our case, simply “find” your chosen character/s, then leave the replace portion blank, eliminating the character. While not formal to the player’s name, it will make your Excel life easier.
Text to Columns
Bringing in some sites rankings will result in a column with the player’s name, team abbreviation, position, and/or bye week, all in one place. Our rankings were built using the first name last name format, so having this additional information in our datasheet would prevent our formulas from working, as they are looking for a player’s first and last name in one column.
To break the name and other data into separate columns, Excel provides us with a tool called “Text to Columns”. This function will look for a specific character that is separating text such as a space, comma, parenthesis, and use that as the column break. Using this tool will take a single column of text and separate it into separate columns.
There are a few steps to the Text to Columns Function:
Step 1: Choose Delimited or Fixed Width – In most cases, you will use Delimited, which will create columns based on specific characters. Fixed width would be useful for data that has the same spacing.
Step 2: Select your “delimiters” (IE – the characters that are separating your text). Excel shows you a preview of what your data will look like based on the selections. In some cases, websites will use “hidden” characters. If you find that your data is not appearing correctly, backout, and figure out what the hidden character is. I’ve found that many sites will use a character that looks like a space, but is not. You can copy/space the “space” and place it in the “other” box.
Step 3: Choose your format, such as text, data, or even select columns to skip. Most of the time, you can just use General and move along.
Step 4: Finished product. Here’s where the above-mentioned find/replace feature is extremely helpful and suggested to be used before using Text to Columns. If you left in a suffix, such as Gardner Minshew II, you’ll have six columns where every other name has five. If you are only using the first and last name columns, this is not a big deal, but if you’re eventually going to pull the other data to a sheet, you’ll need everything in the correct columns.
Concatenate
Now that we have the player’s first name and last name broke out from the other information, you’re probably wondering how we can combine them back together without manually retyping every name. Of course, Excel has a formula for that!
The concatenate formula will take data from multiple columns and put them together in one column. In the example below, we want to bring the first name (column B) and last name (column C) together in one column (column G). Your formula builder will guide you through this process. While Excel is a very smart program, it also only does what you tell it to do. If you want a space between the first and last name, you need to put the space in the formula! Your formula will look like this: =concatenate(B2,” “,C2)
vlookup
I mentioned the “vlookup” formula in Part 2 but did not provide a sample or image. As a refresher, this formula will “lookup” the first occurrence of specific criteria and return the corresponding data in the same row from another column. This formula is used for pulling in specific numbers or text related to a specific player, such as their team or position.
As an example, let’s use our original QB ranks tab. What if we didn’t have the team abbreviations in there from the beginning, but now wanted it from another data tab? The formula would look like this: =VLOOKUP(A4,FantasyPros_2020_Draft_QB_Ranki!D:E,2,FALSE)
To better understand, it’s broken down like this in the formula builder:
Lookup_Value: The cell containing the specific data we want to look up, in this case, Lamar Jackson in A4.
Table_array: This is the data table we are looking for the reference in. This example is looking on the tab named “FantasyPros_2020_Draft_QB_Ranki”, and our data is found in columns D and E. The first column of the data table should be the column containing the player’s name and can extend as far to the right as you would like to capture their data points.
Col_index_num: The third part is a number representing how many columns to the right of the reference column our target data is listed. In this example, the team abbreviation is in the second column, so we use 2, for the second column. If it was 6 columns to the right, we would use a 6.
Range_lookup: Lastly is a true/false statement. If you type true, Excel will find the closest match to your source data. If you type false, it will only return data if there is an exact match. For your Excel rankings, you will almost always use false because we want to look up an exact match, such as player name.
Sumifs
While this may not be useful for a traditional rankings table, perhaps you have a data sheet with player projections. (If you haven’t already, go purchase the Fantasy Footballers Ultimate Draft Kit, where you can download each of Andy, Mike, and Jason’s projections!) I covered the sumif function in Part 2 to pull in specific player numbers, but what if you wanted to find the total projected receiving production for an entire team, while also seeing it broken out by position? The sumifs function would be the way to go to find that data. Where sumif looked for just one criterion, sumifs is going to be checking for multiple criteria, two or more “if statements”, such as team and position, and then finding the sum of the stat column of choice.
Below I have a data set with players, teams, positions, and receiving projections for receptions, yards, and touchdowns. In this example, to show how this formula is working, I’ve built three comparison charts and selected three teams who deploy their pass catchers differently; Kansas City, with strong passing volume to the RB and TE, Arizona with minimal focus on the TE, and Carolina, with a strong focus on the RB.
In my first formula in cell I3, I want to find all the receptions projected for players on KC, at the RB position. The sumifs formula would look like this: =SUMIFS(D:D,B:B,H2,C:C,H3)
Using the Formula Builder, it breaks down like this:
Sum_range = the column you want to sum; in this sample, it is column D, receptions.
Criteria_range1 = the range of cells with the first condition; in this sample, it is column B, Team.
Criteria1 = The first specific criteria we are looking for; in this sample, it is cell H2, KC.
Criteria_range2 = the second range of cells with the second condition; in this sample, it is column C, Position
Criteria2 = The second specific criteria we are looking for; in this sample, it is cell H3, RB.
I’ve also used a simple “sum” formula above each header, to show team totals, using the formula “=sum(I3:I5)”, to show me the total projected receptions for KC, then used the same formula above each team’s stat categories. If you’re going to be copy and pasting this formula into the other cells, make sure to use the “$” trick from Part 1 of this series! On top of that, you can now type any team abbreviation into the cell (H2, H9, or H16) to bring in that team’s data. Welcome to your first foray into a “dashboard”!
Going Even Deeper
In the fourth and final part of this series, I’ll be share another method on automating your ranking downloads and building them into your ranking page, along with a few other tips and tricks that I find useful in building out simple dashboards and make data visualization even easier!