2021 Super Bowl Squares Spreadsheet – the only template you’ll ever need

I’ve been creating Super Bowl Squares spreadsheets for ten years now. Every January I’d update the template and add more sheets with more ways to play. It’s gotten to the point where it’s a bit of a mess when opening the workbook and seeing all these different colored tabs with similar yet different names. It was getting annoying dealing with multiple game boards, multiple score managers, etc. and probably very confusing to new users. 

The old version of the Super Bowl Squares template

I’ve had an idea for streamlining and improving the grid game spreadsheet for over two years but just finally had enough time and energy to work on it before the actual Super Bowl. It’s taken many hours of work setting up the macros and testing all of the conditions but it’s finally ready: the new and improved Super Bowl Squares template is here!

When you first open the template you’ll notice the big changes right off the bat. You’re met with a simple setup screen where the user will decide how to play the game. The sheet automatically updates the Super Bowl boxes and the leaderboard to reflect only the version of the game you want to play. 

I used to have to update the sheet every year by inputting the teams and their helmets. This time, all the teams and helmets are in one of the sheets. Simply use the drop down lists in the Squares sheet to select the team from the list (broken up by AFC and NFC) and their helmets will update automatically! 

Watch me demo the new Super Bowl board in the video below:

As you can see, the new sheet allows more ways to play but is simple and easy to setup and is automated as much as possible. 

Get the Super Bowl pool template here.

Even if you’re not into football, you can still use the template to learn how to do these Excel tricks:

• Lookup pictures based on cell values
• Generate random numbers
• Use NameManager
• Create drop down lists
• Use index and match formulas

If you dissect the macros in the spreadsheet you’ll learn how to:

• Hide rows and columns
• Hide or unhide sheets
• Generate random numbers between 0 and 9 with no duplicates
• How to hide command buttons by VBA
• How to change cell fill color

Update 1-19-2021

I've updated the spreadsheet again. There are now 54 ways to play within one slick spreadsheet. I added options to play by quarters, every minute, or every time the score changes. Watch me preview the update below. Also be sure to subscribe to my email list and YouTube channel as I'll be showing off all the tricks and tips I used to make this spreadsheet work.

*Intended for PC/Microsoft Office/Excel. I don't think it will work in Mac Numbers*

Get it here (enter a 0 into the price box then input an email address):

https://gumroad.com/l/superbowlsquares

Let me know what you think. I’d love to hear from you. Is this version as much of an upgrade and easy to use as I think it is? I welcome any and all questions, comments, suggestions, cuss words, and compliments. Let me know using the comments below or via email. Enjoy playing Super Bowl Squares!

How to find the values of diagonal cells in a table in Excel

Today I'm going to show you how to use the OFFSET function combined with Index and Match to find the values of diagonal cells in a table using my Super Bowl squares template as example.

Use the OFFSET function to find the value of a cell to the upper left, lower left, upper right, or bottom right of another cell. Let's say my name is in cell B2, the reference cell. To go up a row use -1, to go down a row use 1. Use -1 to go to a column to the left, +1 to go to a column to the right.

Upper left: =OFFSET(B2,-1,-1)
Upper right: =OFFSET(B2,-1,1)
Lower left: =OFFSET(B2, 1,-1)
Lower right: =OFFSET(B2, 1,1)

Now this is useful if you know the exact location of the reference cell, but what if that reference cell is always moving? That's where INDEX and MATCH come into play. A perfect example is taken from my Super Bowl Squares spreadsheet template. Watch the video below to see how I can get the values of the diagonal cells when the reference cell can change once I hit the randomize numbers button:

What do you think of that? Would you like to see more examples like this, taken straight out of a template? Let me know in the comments below.

Horizontal to vertical formula across sheets

Typically, to transfer horizontal rows into vertical columns in Excel you would highlight the cells you want to change and copy, then right click on the cell you want to move to, select ‘paste special’, click ‘transpose’ box, and hit ok. But what if you want to accomplish this task with a formula instead so it is done automatically on the fly? In an earlier post I showed you how to do it by using the OFFSET function combined with a named range. Today, I’m going to show you a different Excel formula to transfer rows to columns.

I recently used these horizontal to vertical formulas in my 2013 NCAA college football bowl prediction pool manager, and I am going to refer to that template as an example, so if you haven’t already I recommend you download the sheet. I needed to get the list of the players from the main sheet, which were listed out horizontally, and get them into the leaderboard sheet. The players needed to be listed vertically in the leaderboard in order for my rank without ties formula to rank the players in order of who picked the most games correctly. The basic formula uses INDEX and ROWS functions and looks something like this:

=INDEX(Sheet2!D$22:H$22,ROWS(A$4:A4))

The INDEX function returns the value of the cell at the intersection of the rows and columns specified in the formula while ROWS returns the number of rows in a reference or array. The exact formula I used on the leaderboard sheet in cell A3 is shown below:

=IF((ROW()-2)<=$L$3,INDEX('Master Pool'!J$39:Z$39,ROWS(A$4:A4)),"")

The formula - notice the range

The range expands as the formula is filled down

It takes all the names in row 39 on the Master Pool sheet from column J to Z and puts them into a vertical column in the leaderboard sheet, starting in row 3. The dollar signs are very important. As you fill the formula down the range increases. I also used the IF and ROW functions to account for the number of players (if the row number minus two, because the formula started in row 3, is less than or equal to the total number of players, then transpose the data, otherwise leave blank). As you can see in the example spreadsheet, the data is transformed from a horizontal row on one sheet to a vertical column in another. This is important because the data in the horizontal column can change and the vertical column will update automatically - no manual revisions needed!

How to find the most common name in an Excel spreadsheet?

How do you find the most common name or string in a range in an Excel spreadsheet? The following array formula will return the most frequently used entry in a range, where Rng is the range containing the data:
 
=INDEX(Rng,MATCH(MAX(COUNTIF(Rng,Rng)),COUNTIF(Rng,Rng),0))

How do I create a two variable lookup in Excel Spreadsheet?

One way to create a two variable lookup formula in Excel 2007 is to use the INDEX and MATCH functions.

First, the INDEX function returns either the value or the reference to a value from a table or range. The syntax for the INDEX function is:

Index( array, row_number, column_number ) where array is a range of cells or table, row_number is the row number in the array to use to return the value, and column_number is the column number in the array to use to return the value.

The MATCH function searches for a value in an array and returns the relative position of that item. The syntax for the Match function is:

Match( value, array, match_type ) where value is the value to search for in the array, array is a range of cells that contains the value that you are searching for, and match_type is optional.

Putting it all together:

=INDEX(LookUp!$AA$2:$AA$156,MATCH($C2&$D2,LookUp!$X$2:$X$156&LookUp!$Y$2:$Y$156,0))

An important note is you MUST enter it via ctrl+shift+enter, not just enter. You'll know you did it correctly when it creates an extra set of special brackets around your formula ({}).

An array formula can perform multiple calculations and then return either a single result or multiple results. Array formulas act on two or more sets of values known as array arguments. Each array argument must have the same number of rows and columns. You create array formulas in the same way that you create other formulas, except you press CTRL+SHIFT+ENTER to enter the formula.