Trending searches: espn fantasy football cheat sheet
[Music] welcome to the channel today's video I'll be
covering spreadsheet tips in Excel for Fantasy Football reasons and also summon Google Sheets
as well we'll be going over how to bring a cheat sheet into Excel from a PDF and also create
a draft board that you can use a dropdown to Simply look up names by typing in the first few
letters and Cho use them this way and also some other tips with bringing data from the web and
doing some analysis as well as inserting charts and different stuff like that so the first
step for getting a cheat sheet into Excel will be going to ESPN's website I'll have the
link in the description and we'll be getting a PPR positional cheat sheet click download and
now there will be a copy in our file explorer where we can import it into Excel now back in
Excel we can go to the data tab get data from file and then from PDF and find it in your file
explorer you can click on it and click import and there will be two different tables here it looks
like the bottom one also has the position so now we can just click load and with this loaded it
did not create all the column headers such as the bye and stuff like that so we can manually
do that but before we do any format in for the sheet we'll want to grab all these different
names and create a list that we can use for our dropdown so at the bottom of this list we'll use
a formula and we'll just type equal unique and we'll want to grab all the unique names from one
column and then we'll just drag this over so that takes all the quarterback names and then this
will take the remaining players so now we have these different lists of players and some data
that we don't need so the way we're going to get all of these extra values into one list will be
another formula so now we can go to sheet one or add a new sheet if needed and then we'll just
type equals and start typing vstack can double click and this will just combine arrays into one
vertical column so array one will be this first array and then just a comma then we can grab this
next array another column comma grab this array as well one last comma and then these looks like
just the teams for by weeks so we'll grab that first player and then control shift up to grab
that as well then hit enter and so now this is a Formula that created an array that Stacks but we
can just contrl C and then right click and paste just the values so there's no formulas in here now
they are just the values of what's in that cell so we can delete this formula and then we can also
click on this row right here all the way on the one then right click insert to insert a column and
we'll just want a quick header name so then we can go into data and filter and then we'll filter out
all these zeros so now we can just click on this and then if we press down control and click on
other rows will also work and we can rightclick and click delete rows so now when we unfilter or
select all those zeros will be out of there and now it'll only be names with some exceptions
they'll be certain position names like tight end but that won't matter as when you're looking
up a player you'll be able to type in their name and click from the drop-down list so now we can
start creating the draft board by going into a new sheet and then we'll just type team one so this
will be the team names here and we'll just do a 12 person team 12 person league and then round
one and we will just copy the this down for 16 rounds so we'll just do quick formatting for
each of these and bold them and we can make them a slightly different color so they're a bit more
visible and so now we're going to want to create a drop- down list by just grabbing and dragging
this whole grid and then we'll go into the data tab data validation and this is allowing any
value to be in these cells but now we can click this drop down and just click on list so now the
source of the list we go back to this column that we just created with vstack control shift down to
grab everything and now whenever we either click this dropdown in any of these cells or just start
typing it will show up with their names so let's say you look up Josh Allen it shows up so now you
can just quickly for each team and each drafted player easily look up their name without having to
worry about spelling completely or a lot of things like that and another quick tip if you want to
change the view if we go down here there will be a couple different view options so this will be page
layout this would be if you wanted to print the worksheet and so you can also go into page layout
and we can change this to landscape and then this will be dependent on the size but let's say we
wanted to make these a bit smaller you would need to change and shorten or make the font smaller to
compress everything onto one sheet so then this way you can make a printable sheet or we could
also go to this View and zoom in and this will be the first page but as these go you can see where
the page cuts off so down here will be important if you're worried about that aspect of creating a
page but now we can go back to the cheat sheet and do some formatting so we can click delete since
we have all of these highlighted and now this will be an optional route but we can concatenate
all of these or combine all these cells so it'll be one quick way to highlight them so instead of
grabbing multiple cells and having them mislabeled and then you know as the drafts going you can just
highlight them in different color well we can just combine them all into one cell to make it a little
easier so we can click the top of this column and then right click insert we'll insert a new column
and then we'll want to use a function which will be concat which will concatenate all these
different cells so if we click on this first one we can hit comma then this will be the next cell
but we can actually do an opening quote a space another quote so then this way there'll be a space
in between then this will be the amount of money for daily fantasy or if you do your draft based
on a certain cap but we can actually add a opening quote space dollar sign ending quote and a comma
to add a dollar sign in front of this then we can do open and quote and we can actually put BW
for bye so it'll be space bye and then the actual number so this will autofill down and we can see
that it completely fills in each one four of us and if there's any cell that we don't need we
can just hit delete to delete that formula so now with this done we can actually do
something really quick so we can do rank player dollar sign and buy week so now what this is doing is taking all
of these names so then we can just copy this over and paste it to the next and you'll see
why that's important in a second so this will be rank two but then we can make all of these
two instead of the way they went so then this way we'll be able to easily copy this
formula so now we can just take this formula and paste it right here and we can see that
it auto took but we can do rank two player two dollar sign two and by week two and so the syntax
is incorrect something to do with this column but so we can just delete it and then select right
in there because there's an extra space at the end of here that we were missing so now this
will take everything down except we see we have $2 signs so now we can take off this dollar
sign here when combining and that one's done as well so now we can grab all of these right click
and click hide and then if we go back to here can hide these as well so now we'll just do these
for the other two and you can just change these names to player names and then you can make this
player names as well and since they have have to be unique column names it just changes it to
two I'll quickly get the other two formatted for the sake of the video we'll skip over that
to save some time and now with these formatted we can go down and these are the buy weeks here
so we can get rid of some of these formulas so we can just take the rank and the player is all we
need so that will be by weeks and then this will show what by weeks so this will be five six 7 9
10 and so on with the number above and then the ones below it being the bye and we can also get
rid of these cells down here as we will not need these so now we have this table that
there's a few things we can clean up but it looks like it's ready to go and so a
few quick tips let say while the draft's going on you want to Mark who's taken you can see that
highlighting it the easiest way just to mark them as gone and also if we go to one cell and just
press F4 we can now Mark off whoever got taken without having to click and click you can move
around by either clicking or using your arrow keys and press F4 and also control y also works if
you prefer control y over F4 that will be a quick way to do it and moving into the next part of the
video I'll show you how to get web data into Excel so you can perform analysis or look at different
variables and how they compare to one another so I'm on nfl.com stats and player stats the link
will be in the descript description and we have a table of passing data from 2023 and we can just
copy this URL before we move into Excel and then we can go to data get data from the web and we'll
want to paste that URL that we copied before click okay and now that we've loaded the web page data
we can take a look and see that the HTML code is just how the web page is written display text but
we have this table here which will be the table in HTML or the web page that has all the data we're
looking for so we can just click load and now that the data is loaded can see that we did not
correctly add this First Column or first row as the headers so what we're going to want to do just
click on table design convert to arrange then we can delete this then grab everything we'll do no
borders and no fill and then hit control T and my table has headers and now it just converts it to a
table once again I'm not going to go too in depth with any sort of analysis but let's say we have
touchdown and rating so let's insert a column and then we want to get a touchdown to interception
ratio where we can do touchdowns divided by interceptions so that means and we can actually
just convert these to a number so they'll be full numbers so that means for every 3.43 touchdowns
Lamar Jackson will have one interception and a rating so then if we grabbed well we can do TD to
int ratio and now with touchdown ratios in ratings we can just grab this column and these
two columns and then go to insert charts and this is where all these different
charts will be available for you but we can go into an XY scatter and we can also
add an access title where this will be TD ratio and this will be rating so this will so show the correlation between touchdown to interception ratio and the
rating now to move to the Google Sheets version they don't make it quite as easy as
Excel is power query to import PDF files or a bunch of other types of data we will have to
use an extension there are other ways you can do this such as PDF to CSV files but for this
example we'll be using an extension and the extensions will be right here and go to add-ons
and get add-ons and you will type PDF in this search and we will be getting small PDF so you
can click on it and install it so you'll be able to import the data into Google Sheets so with that
installed we're going to want to go to our Google Drive where we'll want to find the PDF and how
you'll get your PDF into your Google Drive is send it to your Gmail and then save to your Google
Drive then we'll rightclick open with small PDF then you'll have to sign in with your Google
account and you can continue and now now with the PDF in here we'll go to convert and we'll go
to PDF to excel where you'll have to once again select the file from your file explorer and it
is a little confusing um and make they make it a lot tougher so convert selectable text only and
convert and once that's done we can right click on this Arrow or just click on it left click
and save to Google Drive and we'll click save and then we'll find it in our
Google Drive and we will double click and so now it loads all these different
tables but really what we're going to want is table two and table three all we need to
do is click on column a right click and then insert one column left and then do
that two more times and then what we're going to want to do is just copy this data
here by we can just control shift over and down and then contrl C and then we'll right click
and paste so now if we zoom in a bit we can see that all of the names are in here and we can do
a little bit of formatting and it actually looks very nice and very simple to use creating a draft
board would be very similar so I'll just go over some of the basic ideas we would want a list of
the same names so we can you know do uniques to get all the names that we want and then once
we do how we're going to do data validation will be by grabbing a range of cells so let's
say this was the draft board here we'll go to data data validation and we'll go to add R and
the drop- down will be from a range and then the range we'll want to just select the list
then we'll click okay and click done and then all the names will appear here so then that's
how you'll create your draft board with teams and rounds and then create the long list that
you'll select every player from and for the last part of this video I'll show you how to get data
into Google Sheets for analysis we'll be using the same exact website NFL stats and we'll
just be taking the quarterback stats from 20 23 so we'll go into Google Sheets and type equals
import HTML so we'll be importing that table from that web page by putting an opening quote the
URL closing quote and then comma the query type will be a table since that's how the data is
stored on that web page and that's what it's stored in and the index will be one it will
be the first table we can just close that and now it takes all the data with the headers
in there so it'll be available for analysis and it'll be similar where we can add Charts so in
this case we can just take the players and let's just take the amount of attempts and the amount of
touchdowns then you can go to insert and we will insert a chart and so this chart that it picked
for us is a stacked column chart so this might not be the best for this example since they're not
exactly related you could do attempts and then completions for a chart like this but we can also
just change it right here so we can do attempts and touchdowns so we can see the amount of temps
and the am amount of touchdowns throw with another scatter chart or you could do a line chart which
would make no sense in this case so do not use a line chart but there's a bunch of different
charts available for different stats depending on what you want to look into but that's all I have
for you in this video If you enjoyed it please remember to hit the like button and subscribe
it helps out a bunch and thanks for watching [Music]