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. 

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:

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.