In honor of the 2020 Summer Olympic Games currently being held in Tokyo, Japan (in the year 2021 no less), I decided to create a Microsoft Excel spreadsheet template for the medal count as I did for the 2018 Winter Olympics, 2016 Summer Olympic Games, 2014 Winter Olympics and 2012 Summer Olympics. There are two primary methods most websites appear to be ranking the 2020 medal count. Most sites rank countries by the total number of Olympic medals won. Other sites, like the International Olympic Committee (or IOC) rank countries by their gold medal count. And others rank by other factors like per capita or GDP.
Pictured below is a bar chart showing all medals won for the top 22 countries (as of the time of this posting on 7-27-21). The bar chart is created in Excel by highlighting the data then going to Insert>Bar>Stacked Bar chart. Change the colors of the bars by right clicking on them then use the drop down menu to select the data you want to change.. You can update the chart yourself by download the Excel file here.
I’ve devised my own ranking system to give each Olympic medal a weight where the silver is worth half a gold medal and a bronze is worth only a quarter of the gold. Based on this new scoring system, previous Olympic results suddenly became quite interesting. However, for the 2020 Summer Games not too much actually changes (so far, will revisit after more events are completed).
Download the spreadsheet and see for yourself. I’ve shared my Olympic Medal Count spreadsheet and listed out the Olympic medals by country. How would you weight each medal against the others? Comment below and share any of your Olympic medal rating systems!
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.
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?
