Essential Fantasy Football Spreadsheet Tips

[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]