NCAA Basketball Tournament Bracket Spreadsheet File

Friday, March 13, 2009. 11:00 pm. Posted by Josh.

It's almost time for the 2009 NCAA Basketball Championship, aka March Madness! If you're one of the many people who like to take your luck at filling out a tournament bracket or maybe even organize a pool, I have just the spreadsheet for you.

My family has been doing a tournament pool for years (mostly just for bragging rights). In the past we would all fill out a paper bracket and someone would manually check them and highlight them as the games ended. With my family living farther apart now, we decided we needed an electronic format for our brackets. Yes, there are various websites out there devoted to this, but they often require account registration, have ads, etc. My goal was to make it simple so I created a spread sheet which allows you to fill out your bracket using simple drop-down boxes. Then I collect everyone's spreadsheets, combine them in to one (a little complex), and the spreadsheet automatically keeps score as I fill in who wins the games.

At the end of this post are links for downloading the spreadsheet in Excel or OpenOffice.org format.

Instructions On Using The File

1. Download the File

Obviously, the first step is for the person running the pool (that's you) to download the file and open it up. There is are also instructions in the file for your reference.

A Note On File Protections:

This file and the sheets within it, are protected to prevent users from accidentally messing up the file.

To add/rename/copy/move a sheet, you will first need to unprotect the document. In OpenOffice.org, Tools->Protect Document and uncheck Document. In MS Excel, Tools->Protection->Unprotect Workbook.

To edit the protected regions of many of these sheets, unprotect the sheet. In OpenOffice.org, Tools->Protect Document and uncheck Sheet. In MS Excel, Tools->Protection->Unprotect Sheet.

It is recommended that you re-enable protections before giving this file to the people in your pool to prevent problems. You can even add passwords to the protection to keep it more secure.

2. Setup Scoring

To begin, you need to define your scoring methodology. Switch to the Scoring tab and unprotect the sheet.

Enter the number of points for a correct answer in each round of the tournament. For example, if you choose 1 point for Second round, you will get 1 point for each team that you successfully predict making it to the second round. If you choose 8 for Champions, you will receive 8 points if you predict the correct winner of the tournament.

You may also setup bonus points to be awarded based on team seeds. If a lower seeded team beats a higher seeded team the bonus points assigned to the seed difference will be awarded. No bonus points are awarded if a higher seeded team wins.

In the bonus points table, assign a point value to each seed difference. For example, if you assign 5 bonus points to seed difference 7, you will score 5 bonus points (in addition to the regular points) if you correctly pick a winner that was an underdog by 7 seeds (i.e a 12 seed beats a 5 seed).

Re-protect the sheet.

3. Fill In The Master Bracket

The Master Bracket tab holds the field and all the winners as the tournament progresses. Switch to the Master Bracket tab and unprotect the sheet.
Fill in the field of teams in the tournament by typing the name of each team on the corresponding line of the bracket.

When done, re-protect the sheet.

4. Distribute The File

If you are doing this for a group of people, make sure all the sheets and the document are protected. Then e-mail the file to them with instructions on entering their picks.

Tell the other people to each use their own file, and to fill out the YourBracket sheet just like the instructions below. Have them save it and email it back to you.

5. Enter Your Picks

Go to the Your Bracket sheet (no need to unprotect). For each line of the bracket, click the cell and use the drop down box to select the winner.

Enter your Name in the Name field at the top of the sheet and your Tie Breaker value (whatever your pool is using for a tie break).

6. Consolidate The Sheets (slightly tricky)

Your file will be the master file. You will now consolidate all the brackets into your master file. For each file (including yours), open it, unprotect the document, switch to the YourBracket sheet, unprotect the sheet, then rename the sheet to the person's name. Copy the sheet to the master document.

If you are using MS Excel, you will have to change the links to point to the master document. Go to each person's sheet in the master document. Unprotect the sheet if it is not already. Go to Edit->Links. Select the link and click the Change Source button. Then browse to and select the master document file. This will make sure all the sheets in the master document file are linked to the master bracket. The link on that window will disappear since there are now no externally linked files. Reprotect everything if you wish.

7. Create A Score Sheet

If you want, you can create a sheet with each person's name and the score from their bracket sheet. That way you can easily see the standings.

8. Watch The Games!

9. Fill In The Winners

When a game is over, mark the winner on the MasterBracket page. Every person's individual bracket should update. A wrong answer gets crossed out and a correct answer gets highlighted.

I hope you find these files useful. I will also upload a file with the field of teams already filled in each year, so bookmark this page and come back!

**** UPDATES ****

Mar 21, 2010 - v2.1

!!BUG FIX!!

It appears there was a bug in the MS Excel version of the file in the bonus point calculation. The bug is now fixed and the file has been updated to v2.1. Go here for more details about this bug and the fix.

Mar 12, 2010 - v2.0

I have updated the template so that you now have the option of scoring bonus points for picking upsets. See the instructions for more details.

*****************

I hereby donate these files to public domain so feel free to use it in any way you wish.

Template Files:

NCAA Bracket Template v2.1.ods - OpenOffice.org Calc Spreadsheet (38KB)
NCAA Bracket Template v2.1.xls - MS Excel Spreadsheet (138KB)

Files With 2010 Field Already Filled In:

NCAA Bracket Template 2010.ods - OpenOffice.org Calc Spreadsheet (38KB)
NCAA Bracket Template 2010.xls - MS Excel Spreadsheet (138KB)

Files With 2009 Field Already Filled In:

NCAA Bracket 2009.ods - OpenOffice.org Calc Spreadsheet (36KB)
NCAA Bracket 2009.xls - MS Excel Spreadsheet (101KB)

Posted in: Downloads , Sports
This article has been viewed 4882 times.
Comments: 24

Comments

Post Comment

Sunday, March 15, 2009. 8:13 pm. Posted by BracketMan.

Well done! An excellent file I can use over and over again! Thanks for posting this!

Sunday, March 15, 2009. 9:43 pm. Posted by Josh.

BracketMan - Glad you find it useful. Check back every year if you want to download the file with the teams already filled in!

Monday, March 16, 2009. 10:02 am. Posted by Terry Dell.

Nice job on the bracket!

We also have a cool, online bracket. We are running a contest with a $100 million grand prize for anyone who picks all the teams correctly this year! http://www.pickmanager.com

Good luck and enjoy the tournament!

**************************
Josh's Edit: I don't normally edit the comments left by my readers, but in this case, I wanted to make it clear that I am in no way affiliated with the website posted in this comment by user Terry Dell.
**************************

Tuesday, March 17, 2009. 3:39 pm. Posted by dogleg69.

Great work. Thank you for putting this together. Much appreciated by my family too.

Wednesday, March 18, 2009. 7:51 pm. Posted by Novice bracketplayer.

I am filling out a bracket online, and it will not let me save without entering a valid tie breaker. Can you help me in any way, or would it break some sort of rule???

Thank you!

Wednesday, March 18, 2009. 9:42 pm. Posted by Josh.

bracketplayer - That does not sound like my spreadsheet that you are using. My spreadsheet can not be filled out online and does not actually require you to enter a tie breaker for saving. You must be using something else.

My guess is that you are using the website in the comment above posted by Terry Dell. I am in no way affiliated with that website. If you are using it and have questions with it, please contact their support team.

Thursday, March 19, 2009. 2:18 pm. Posted by jman.

thank you a TON sir!!! this saved me so much time, well done!!!

karma in the bank for you :)

Saturday, March 21, 2009. 12:21 pm. Posted by Fred in California.

Thanks so much. My 10 year old son loves the tourney and he used this to send his picks to his cousins around the country. GREAT WORK!

Monday, March 23, 2009. 11:34 pm. Posted by PlayHardPlayFairHaveFun.

I know my family is really enjoying your application. The only sad note is that my granddaughters (age 2 and 4) are about to pass me up on the scoring. ;}

Again Thanks.

Tuesday, March 24, 2009. 6:59 pm. Posted by kristoffer.

Agree with the many other posts that this is a great spreadsheet. We came up with a twist on scoring this year, where if you predict an "upset" (lower rank team beats a higher rank team) then you get 1.5 points whereas predicting a win by the higher ranked team is worth 1.0 point. This was to encourage more bets on the underdogs. I don't see how a way to do that currently in your spreadsheet, but you have all the data needed so that would be an additional option you could throw in if others do something similar.

Wednesday, March 25, 2009. 9:28 am. Posted by Josh.

I thought about the upset bonus points this year, but didn't have time to get it into the spreadsheet. Hopefully, I can get an option for that in there by next year's tourney.

Josh

Tuesday, March 31, 2009. 9:13 pm. Posted by Rich00987.

Just wanted to say that your bracket spreadsheet in excel is awesome . It was exactly what i was looking for and took me forever to find... Now that i have yours my days of pen and paper bracket pools are over. I am a bartender and run a pool every year and your spreadsheet probably saves me about 3 hours of work all together. Wonderful job... Could not be happier-
- "not so mad in march bartender rich"

Tuesday, March 31, 2009. 9:15 pm. Posted by rich00987.

opps , commented on an economic blog....sorry.

Wednesday, April 1, 2009. 9:44 am. Posted by Josh.

No problem Rich. I moved your comments over to the appropriate article.

Thanks!
Josh

Wednesday, March 10, 2010. 12:43 am. Posted by Jon.

Great model...

I'm hosting a bracket this year and have a random scoring system. I have set points for each round win that i can set up in the scoring tab you've provided...But I am adding an underdog bonus.

Essentially, say a bracket owner predicted correctly an 11 seed beating a 6 seed in first round. I take the difference of the seeds and add 1 to the game pick (11-6=5 + 1 round win = 6 points). If a favorite is selected there is no seed difference.

Any idea how i can alter the scoring to reflect this?

Wednesday, March 10, 2010. 10:35 pm. Posted by Josh.

Jon

Funny you should mention this because this is a feature I wanted to use this year as well. In fact, I have the changes nearly done to add this feature. Check back in the next couple days for an updated sheet which adds this capability.

Sunday, March 14, 2010. 2:18 pm. Posted by Jon.

Awesome! thanks for updating the new scoring spreadsheet for upsets...

Just so I understand...on the scoring spreadsheet you have it set up below the round scores to put in values for the seed difference.

So essentially if there are 0 seed differences, (i.e. final four 1 seed plays a 1 seed, there is no bonus) then there are no bonus points. So basically i just need to re-number the right column to match the numbers on the left. (i.e. 1 seed difference equals 1 extra point, 3 seed difference equals 3 bonus points, et al...) I assume you've created this so if a 3 seed beats a 14 seed then a 6 seed that they don't receive bonus points.

Amazing Josh...thanks a ton.

Sunday, March 14, 2010. 10:23 pm. Posted by Josh.

Jon - You have it all correct.

Tuesday, March 16, 2010. 11:52 am. Posted by Sean.

Great Workbook this is exactly what I was looking for.

As far as creating a scoring sheet to keep track of the standings of everyone, I am a little stumped on the best way to achieve this. Would love to hear some suggestions. Thanks in advance.

Tuesday, March 16, 2010. 6:46 pm. Posted by Jon.

Josh,

I did some trial and error for the brackets to make sure the underdog scoring bonus works...

The following cells do not take into account the underdogs bonuses...
C-16,20,28,32,49,53,57
O-4,12,24,32,37,65

I'm going to manage those cells manually as I don't know how to fix it but wanted you to know for your brackets and anyone else who plans on utilizing Josh's awesome document he's provided for us.

If you do fix it Josh, let us know how it affects all the brackets that have been turned in from competitors. Basically, all the bracket owners already have filled in their own brackets...if you fix the master sheet and just point everyone's bracket to the fixed master...is that going to fix the glitch?

Let me know your thoughts.

Thanks again for all your hard work on this.

Tuesday, March 16, 2010. 6:55 pm. Posted by Jon.

Actually there are a lot of unknown errors. I advise that you do manual scoring....

N39....Winthrop over Louisville does count the bonus points....but if Cal or louisville were to beat Duke...it doesn't count the underdog bonus...

There are a lot of unknowns i think...

Still a great sheet...just if you do the scoring you need to be careful to score everyone's bracket correctly!

Friday, March 19, 2010. 10:09 pm. Posted by Josh.

Sean -

Sorry I couldn't help earlier, I was out of town all week. Anyway, creating standings sheet is relatively simple. After consolidating all the brackets into one file, create a new sheet within the file called Standings. Starting in cell A1 and going down column A, put the names of the people in the pool. Then for each corresponding cell in the B column, type an = sign and go to the sheet for that player and click on their score cell. Do that for each player and that's all there is to it.

Friday, March 19, 2010. 10:15 pm. Posted by Josh.

Jon -

Sorry I didn't respond sooner, I've been out of town all week. I'm not sure what the problem is that you're seeing with the bonus points. I just tested the cells you mentioned and the scenario with Cal/Lousiville beating Duke and the bonus points DID take effect.

Here's how I tested:
On the scoring sheet, I assigned 1 points for each correct answer in every round. For the bonus points, I assigned 1 bonus point for every seed difference in the table.

I went to the YourBracket tab and selected Duke to beat Ark-PB, Louisville to beat Cali, and Louisville to beat Duke. Then I filled in the same on the master bracket.

My score came out to be 5

Duke beating Ark-PB = 1 regular pt 0 bonus pts
Louisville beating Cali = 1 regular, 1 bonus
Louisville beating Duke = 1 regular, 1 bonus

Total 5 pts. It seems to be working correctly.

Are you using OpenOffice or Excel?

Sunday, March 21, 2010. 2:00 pm. Posted by Josh.

!!!BUG FIX!!!

Earlier this month, I released version 2.0 of my NCAA basketball tournament bracket spreadsheet. The v2.0 update contained the functionality to grant bonus points for correctly choosing an upset win.

Unfortunately, as Jon pointed out, there was a bug related to the bonus point calculations in the Excel version of the file. I created the sheet in OpenOffice and then exported to Excel format. Apparently one of the formulas I was using worked a little differently in OpenOffice than in Excel. I have since corrected the file and updated it to v2.1.

If you downloaded the v2.0 (Mar 12 - Mar 20, 2010) EXCEL file (OpenOffice file is fine), are currently using that file to run your 2010 pool, and are using the bonus points feature, there is a fix available to correct the issue.

Please go to this post for details on the bug and the fix.

Sorry!!

Josh

Blog Post

NCAA Basketball Tournament Bracket Spreadsheet File

Comments

Favorite Links

Categories

Archive