Sunday, February 04, 2007

Okay...Who's Ready for a New NCAA Basketball March Madness Bracket?

I can't tell you how many requests I get, from close friends and family, as well as from readers around the United States and internationally, for an Excel-based NCAA men's basketball March Madness tournament bracket. Well, here's a pretty neat one I whipped up just in time for the tournament.

The workbook consists of three sheets:

1) The "Setup & Actual Results Bracket" worksheet is where you input the teams who've made the tournament, record the results of the games as they are played, and also where you can set how many points each correct pick is worth by round. The image below is a glimpse of this worksheet (well, at least the upper left quarter of it). Don't worry, this is laid out so that you can nicely print the entire bracket on one 8.5" x 11" page.

(click on the image to open it up larger in a new tab/window.)

2) The "My Picks & Points Bracket" worksheet is where you would predict the winners of each game. Using data validation, I've managed to automatically make each cell into which you make a pick only allow the teams playing in that game. Then, using conditional formatting, I've made it so that correct picks (once you've input the actual results in the other worksheet) show up as green, and incorrect picks show up as grey, AND eliminated teams that you've picked to win subsequent games will also show up grey. Finally, this sheet contains an up-to-date total of the points you've won, as well as the total possible points still remaining based upon your picks.

(click on the image to open it up larger in a new tab/window.)

3) The "My Points Chart" worksheet contains an in-cell tornado chart (I admit it: I've got a thing for these crazy charts!) displaying round-by-round performance.

(click on the image to open it up larger in a new tab/window.)

Now, the way I've set up this file, it's pretty much self-contained for a single person to track their picks and points, but I can certainly envision using this as the basis for a complete system in which one "master file" is created to track the results of the actual games as the are played and to set the number of points awarded for correct picks in each round. Then any number of "player files" are created and emailed to each person to make their picks and email back to the person managing the system. Then the "master file" could simply reference the results in each "player file", displaying a summary or a leaderboard, if you will. Could be pretty cool.

To my growing Excel_Geek Insiders subscribers, enjoy this's on it's way. To those of you who haven't subscribed yet, did you know that for only $39.95 per year, you get copies of all Excel_Geek $50 Projects I do for the year, as well as copies of these special little projects I just decide to do? It's true. Sign up today!

Enjoy the tournament!


UPDATE: I released this file for download. See comments here. Download the file here.

No comments: