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)
