Thursday, July 15, 2021

2021 NFL Helmet Schedule Spreadsheet – finally automated!

One of the many templates I update and release on an annual basis is my NFL Helmet schedule spreadsheet, where you’ll find the complete schedule for every single pro football team’s season with an image of each team’s helmet. Updating the schedule by hand is very tedious. Thankfully, a reader did it for me last year. But I’ve always wanted to automate a solution. To do this, there were two main problems to solve:

1. How to get the NFL schedule into Excel quickly without a lot of manual work

2. How to assign the correct helmets to every game without doing any manual work

I’ve finally got a solution for both problems and can proudly say the sheet is now fully automated. Here’s how it works.

Getting the NFL Schedule Into Excel


Problem number one: how to get the complete NFL schedule into Excel without having to copy and paste 32 team’s individual schedules manually. There’s got to be an online solution, right? First, I went to NFL.com. No good: no easily copy-able full schedule. Next, tried ESPN. Success! Grid format is perfect for copying pasting right into Excel. As long as ESPN (or another site) always posts the schedule in this format we can update all 544 games and their helmets within a minute. If you download my template, unhide the hidden columns. The blue cells are copy and pasted directly from ESPN. I use formulas to change the three letter team abbreviations into the full team names. 

As you can see, the NFL expanded the regular season this year by one game, from 16 to 17 (plus each team gets a bye week hence 18 weeks in the regular season). The preseason is reduced form 4 to 3 games.


Automating Assigning All the Helmets

Problem number two: how to populate the schedule with all the helmets? I was thinking about using linked pictures like I do in my Super Bowl Squares template. But this would have required adding a formula in name manager to all 544 helmets. Instead, I decided to have a macro copy and paste helmets associated with each team automatically into the schedule. This required giving all 32 helmets a unique variable name, which was time consuming, but now that I have it setup I don’t have to change again, even when it’s time to update for next year’s schedule.

On previous versions of the sheet I divided out the two conferences on separate sheets: NFC and AFC. This year, I’ve put all the teams into one sheet. However, there is a new filter option where you can filter by NFC or AFC or even by division: AFC North, AFC South, etc.

2021-2022 nfl schedule in excel spreadsheet


Download the 2021 NFL Helmet Schedule Spreadsheet


Watch the video below to see how the filter works. I also so a tip in Excel how to select multiple objects at once with the mouse. And I walk through the populate helmets macro code as well. Lots of good stuff here!


Please note, an email is required to download it. I do this so you will be automatically updated you if changes or additions are made and will update you when the next year’s schedule is ready. I do not use your email for anything else.

This goes to show you a little bit of time and thinking now can save you a LOT of time and trouble later on.

As you can see, the NFL helmet schedule is printable too. You can save the spreadsheet as a PDF file or print it out and pin it up in your cubicle at work. If you do, please email or tweet me a picture of it hanging up - I'd love to see it!

As always, I welcome any comments or suggestions about how to fix or improve the sheet! How can I improve this football spreadsheet into something you’ll use all the time during pro-football season? What future features would you like to see?


8 comments:

  1. I Love It.

    I sent an email. Why is there 18 weeks instead of 17? Will you also make one for the Preseason schedule?

    ReplyDelete
    Replies
    1. Thanks! The NFL expanded the season this year. There are 17 games instead of 16. Preseason is reduced from 4 games to 3.

      Delete
  2. This comment has been removed by a blog administrator.

    ReplyDelete
  3. Hey Nick! I was looking for the CFB 2021 Helmet Schedule. Will you be creating one this year? :)

    ReplyDelete
  4. This comment has been removed by a blog administrator.

    ReplyDelete
  5. Excellent work every year!

    ReplyDelete

I'd love to hear from you!
-Nick