Tuesday, July 27, 2021

Weighted Olympic Medal Count 2021

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.


weighted Olympic Medal count 2021 in excel


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!

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?


Wednesday, June 30, 2021

Podcast Analytics Tracking Spreadsheet

When the COVID-19 pandemic struck last year, many people were stuck at home with nothing to do so they started podcasting. As many of you may know, I love traveling to theme parks around the country. We had discussed starting a podcast for a site I also created content for, Coaster101.com, for years but it wasn’t until the midst of the global pandemic when we finally turned it into reality. Listen to me ramble about visiting amusement parks and riding roller coasters here: https://www.coaster101.com/podcast/

Once we got the hang of recording and it become a permanent thing, we started to analyze our analytics to try to decide what was working and what wasn’t. By understanding the data, you can make decision to help you grow your podcast. Like everything else I do, I decided to make a spreadsheet to track specific stats I had in mind. I’ve turned it into a template you can use, available to download for free here.

podcast tracking spreadsheet


 As I always say, even if you don't have a direct use for this spreadsheet you can still learn something about Excel by examining this template.

For starters, it uses Ranking formulas in Excel to show the most popular and least popular episodes. As I do with almost all of my spreadsheets, I color code the columns so I can easily know which require manual data input by me, which are drop down lists, and which use formulas to be left alone. I use the TODAY() formula to help determine how many days old a podcast episode is (because one just released will obviously have fewer downloads than other episodes). You’ll see there are SUMIF and VLOOKUP formulas as well. Feel free to take a look:

Podcast Downloads Tracking Spreadsheet here

Monday, June 21, 2021

How to Add Conditional Formatting with a Macro

Conditional Formatting is a useful tool in Excel that allows you to do things like highlight duplicate cells, or color every other row in with color, and so on. If you have a large range or table with many conditional formatting rules, sometimes things can get a little messy. If you’re inserting, adding, or deleting rows and columns often, your conditional formatting rules might go from a short, highly understandable list, to a complete cluster:


One way to be able to reset your conditional formatting rules is with a macro. We’re going to use a macro to automatically delete the conditional formatting and then add it back.

First, to clear and delete all the conditional formatting from a sheet with a macro, use this code, changing the A:AQ with whatever range you’re using:

    '--------delete the conditional formatting--------

With ActiveSheet.Range("A:AQ")

    .FormatConditions.Delete

End With

Now it’s time to add conditional formatting with a macro. In this example, I have a status column C where I enter values, and based on these values the format of my range will change.

Use LastRow to find the last row of data, making it a dynamic range (meaning the size of the range changes based on how much data is inside the range).

'define the last row

LastRow = Cells(Rows.Count, 1).End(xlUp).Row

First, define the range where you want to apply the formatting.

Next, define the formula or rule. Here, I want if the value in Column C is the letter N to change the font color to red. I use double quotations to have a quotation. Notice there is only one $ sign. If I put $C$11 then the formula would not trickle down through the rest of the range.

Finally, define the condition, font color to red the color index is 3. See the font color index here: https://www.automateexcel.com/excel-formatting/color-reference-for-color-index/

This is the first rule I am adding so notice the (1) inside the parenthesis.

'-------add the conditional formatting-------

'if new, change font to red

  With ActiveSheet.Range("D11:AP" & LastRow)

     .FormatConditions.Add Type:=xlExpression, Formula1:="=$C11=""N"""

     .FormatConditions(1).Font.ColorIndex = 3

End With

 

Now I want to add another conditional formatting rule programmatically. This time, change the font color to green if there is an H in the column C. Green uses a 4 in the color index. This is the 2nd rule so notice the (2).

 

'if h, change font to green

  With ActiveSheet.Range("D11:AP" & LastRow)

     .FormatConditions.Add Type:=xlExpression, Formula1:="=$C11=""h"""

     .FormatConditions(2).Font.ColorIndex = 4

End With

 

Finally, if there is an X in column C, I want to use strikethrough to cross out the words.

 

'if x, then cross-out

   With ActiveSheet.Range("D11:AP" & LastRow)

     .FormatConditions.Add Type:=xlExpression, Formula1:="=$C11=""x"""

     .FormatConditions(3).Font.Strikethrough = True

End With

 Here's what the conditional formatting rules look like after running the macro:

And that’s how you add conditional formatting with a macro. Let me know in the comments below if you have any questions.

Wednesday, June 16, 2021

How to Make a Drop Down List From a Macro

Drop down lists in Excel are super helpful when you want to limit a user’s input to a set of predetermined choices. Normally, drop down lists can be made by clicking on a cell, going to the data tab, then by using the Data Validation function. Change Allow to List. 

But what if you’re importing data from another Excel file and want to automatically create a drop down list? It’s possible to do using VBA. To create a drop down list from a macro in cell A1, try this code in the VBA editor:

 

Sub ListCreator()

Range("A1").Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _ Formula1:="OK,Maybe,No"

End Sub

 

Here, all the entries in the list will be created from the macro. What if one of the entries needs to change? Only someone with VBA experience will probably be able to figure out how to change it. So I like to create the list somewhere within the spreadsheet where anyone can see it and it’s obvious what it’s used for, then use the Name Manager to create a Named Range to easily refer to the list. This way, if you decide to add entries later the lists will automatically be updated as long as they used the Named Range.

 


In this example, I select my list within the Excel sheet (column G), and give it the name “Status”.

To used a Named Range when you create a drop down list via VBA, simply refer to the Named Range:

 

Range("A2").Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Formula1:="=Status"

 

To set a default value, simply set the value of the cell after changing the data validation:

 

Range("A2").Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Formula1:="=Status"

 

Range("A2").Value = “OK”

 

It might be easier to understand by watching this short video:

 

That’s all there is to it! Now you know how to add a drop down list from a macro in Excel. How often do you use drop down lists? I’d love to know, so let me know in the comments below.

Monday, March 15, 2021

Excel Tips from the Best 2021 March Madness Brackets

After a year hiatus it’s finally here, the college basketball March Madness brackets are back! Last year, I made a NFL Draft Game spreadsheet for the first time to try to compensate for the loss of the basketball brackets but it just wasn’t the same (but I did still update it for 2021). This year, the 2021 NCAA men's basketball tournament will be unlike any March Madness that has come before. All games will be played in Indiana, with most in Indianapolis. The schedule has also been change. The First Four would typically be played on Tuesday and Wednesday night with the first round being played on Thursday and Friday. 

Here's the 2021 March Madness schedule:

  • First Four — 4 p.m. start on Thursday, March 18
  • First round — 12 p.m. start on Friday, March 19, and Saturday, March 20
  • Second round — 12 p.m. start on Sunday, March 21, and Monday, March 22
  • Sweet 16 — 2 p.m. start on Saturday, March 27, and 1 p.m. start on Sunday, March 28
  • Elite Eight — 7 p.m. start on Monday, March 29, and 6 p.m. start on Tuesday, March 30
  • Final Four — 5 p.m. start on Saturday, April 3
  • NCAA championship game — 9 p.m. Monday, April 5

Once again, I will be using the best March Madness brackets in Excel, created by David Tyler (and I will continue to use his until he decides to no longer update them). They’re very polished and easy to use. There are only 68 teams in the field but the spreadsheet is already setup to handle up to 128 teams, if they expand in the future. There are two sheets: the bracket and the pool manager. Instructions are included but its very intuitive. 

march madness 2021 bracket spreadsheet template


The First 5 Things I Do When Examining Someone Else's Spreadsheet

As I’ve said countless times before, you can learn a lot by looking at templates made by others. Here are 5 things I do when examining a new spreadsheet:

1. Unhide hidden sheets, columns, and rows: When you make a template others are going to be using, you want to make it look nice and clean and hide anything that could cause confusion to a first time user, which leads to hiding rows, columns, or even entire sheets in a workbook. So, the first thing I do when examining someone else’s template is look for the hidden data. Right click on the sheets tab and click “unhide”. I unhide all the hidden sheets if there are any to see what data is present. Look for any hidden columns or rows as well by seeing if any letters or numbers are skipped.



2. Understand the NamedRanges: Go To Formulas > Name Manager and examine what the named ranges are, what sheets and cells they refer to. Hopefully they're all named well, like in David's brackets.



3. Look at conditional formatting rules: On the Home tab, go to Conditional Formatting, click Manage Rules, then Show formatting rules for This Worksheet to view them all.



4. Look through the formulas: On the Formulas tab, click “show Formulas” to show if they were manually typed in or if there is a formula calculating the values



5. Look through the macros: Hopefully, the person writing the code left lots of good comments so it’s easier to follow along with what each piece of code does



Watch me quickly walk through David's 2021 March Madness brackets going through the five points listed above:


Tuesday, March 9, 2021

2021 NFL Draft Game Spreadsheet Template

It’s been one year since the COVID-19 pandemic began. Last year, since March Madness and other sports at the time were cancelled, I started thinking about what other things I could do to fill in the void of not having any March Madness brackets to fill out. The answer came in a suggestion from a reader to create an NFL Draft game spreadsheet. I’ve updated the template for this year.


Inside this template I've listed the top 100 draft prospects according to ESPN. Each draft game player (and the template is currently setup to handle ten players) are randomly assigned ten future NFL players by using a randomize macro. The earlier your players get drafter the better, as the draft position counts for points and the lowest number of points wins!

The random number macro is pretty simple:

'define range of cells for random numbers

Dim Player1 As Range

Set Player1 = Range("AN2:AN101")

Player1.ClearContents

For Each a In Player1

Do

a.Value = (Int((100 * Rnd + 1)))

Loop Until WorksheetFunction.CountIf(Player1, a.Value) < 2

Next


Based on some good user feedback I added the option where each player can now try to predict which team will select their players for additional bonus points. Well, negative bonus points that is, as it subtracts points from your total score (remember, lowest score wins).

See how the Draft Game spreadsheet works in the video below:

Even if you have no interest in the NFL, football, or drafts, you can still learn a bit about Excel by examining the random number generator macro, or the vlookup and sumif formulas used. The scoreboard uses a “rank without ties” formula:

 =(IF(D3<>"",(RANK(D3,$D$3:$D$52)+COUNTIF(D$3:D3,D3)-1),""))

For now, the spreadsheet is setup to handle ten players. To add more, the formulas and macro will need to be modified. If enough people are interested in using this sheet, I will work on making it scalable so it can automatically adjust to the exact number of players.

2021 NFL Draft Game Spreadsheet Template.xlsm 

Let me know if you like this game or if you have any suggestions or questions.