2017 March Madness Excel Brackets

It’s the most wonderful time of the year! March Madness 2017 edition is here and the first of the four “play-in” games will begin on Tuesday, March 14th (which we’re not counting off any points for in my office pool). Once again, I’ll be using David Tyler’s NCAA Excel brackets, the same ones I’ve been using since at least 2010, as they're still the best as far as I’m concerned. I'd like to give a special thanks to David for continuing to update and post his excellent brackets each and every March and giving us something to look forward to as we crawl out of the final few weeks of winter (70 degrees a few weeks ago here in Ohio, now it’s snowing!).

I’m a firm believer that a great way to increase your Excel knowledge is to reverse engineer Excel spreadsheet templates, like these office pool March Madness brackets. Previously, I’ve shared 11 things you can learn from the best Excel brackets and they all still apply for this year’s version. David doesn't lock or hide anything behind password protection so you can examine all the formulas to see how they work and make any modifications you desire. There are two files: the bracket manager and the individual bracket file, that can be downloaded by following the link below. Download the files and start picking them apart!

http://www.whistleblows.com/

 If you’re worried you might not be able to use the March Madness Excel brackets have no fear. They are easy to use and you can watch this short video I made to show you just how easy it is to use David's brackets. Simply click on each team to advance them (don't forget to enable macros).

 

 As far as actually filling in the bracket, here are some interesting stats about March Madness and a few tips that might help you choose your teams.
 

Source: WalletHub

• A No. 16 seed has never beaten a No. 1. Like ever.
• A No. 12 seed usually beats a No. 5 seed.
• Odds of picking a perfect bracket: 1 in 9.2 quintrillion.
• Odds of picking a perfect bracket using historical data and basketball knowledge: 1 in 128 billion.

I know some readers of this blog skip over the templates I post, especially if they're sports related. but there really are many lessons that can be learned by examining them that you can apply to your own spreadsheets to improve them. What new lessons about Excel have you learned by breaking down a template?

How to copy a formula down with a macro

Using VBA macros in Excel can make life easier. Here's another example of how. Let’s say I have formulas in row 2 of Sheet2 that I want to copy down a specified number of rows. The number of rows I want to fill the formula down is a value in A1 on Sheet1. Below is the code to show you how to do it along with my comments.

Sub Copy()

'Declare variables. R1 is the range to copy. R2 is the range where we want to put the formulas in the spreadsheet. M is the number of rows to fill to.

Dim r1 As Range, r2 As Range, m As Long

Dim ws1, ws2 As Worksheet

   

'get the row number to copy to from Define Variables sheet

Set ws1 = ThisWorkbook.Sheets("Sheet1")

The number of rows I want to fill the formula down is in A1, plus one for the header

m = ws1.Range("A1") + 1

       

Set ws2 = ThisWorkbook.Sheets("Sheet2")

Declare the range that you want to copy. In my case, the formulas are on Sheet2, in the second row, columns A to Z.

Set r1 = ws2.Range("A2:Z2")

Declare the range you want to fill in. In my example, I want to fill in from A2 through column Z, and down to the row number earlier defined as M.

Set r2 = ws2.Range("A2:Z" & m)

   

Finally, copied the first declared range into the fill declared range.

    r1.Copy r2

   

End Sub

Conversely, if you want to add a command button to your spreadsheet that will automatically clear a range in a sheet, you could use the following VBA code:

Sub Clear()

Dim r1 As Range

Dim ws1, ws2 As Worksheet

Set ws1 = ThisWorkbook.Sheets("Define Variables")

Set ws2 = ThisWorkbook.Sheets("AJ Generator")

Dim m As Integer

m = ws1.Range("L2") + 1

Set r1 = ws2.Range("A3:AJ" & m)

r1.Clear

End Sub