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.

Super Bowl Squares Template 2019 - SuperBowl LIII Grid Game

The match-up for the Super Bowl is set which means it's time to download your Super Bowl Squares Template 2019 edition. Surprise, not surprise: Tom Brady and the New England Patriots (man, I don't even have to update this year to year) will play the Los Angeles Rams in Super Bowl 53 on Sunday, February 3rdat 6:30pm. To make the big game more interesting you can start a football office pool using our printable Super Bowl spreadsheet. 

Fill in the squares, watch the game, laugh at the commercials, and see who wins and maybe make a little money at the same time (or help raise money for charity). If you’re a fan of a team like mine (the Factory of Sadness Hope) that will never make it to the championship (or even get a taste of the playoffs), then playing Super Bowl Squares will make watching the game more exciting and gives you something to actually root for. Others may be torn between cheering for their favorite team and cheering for their squares.

This year’s Super Bowl Squares spreadsheet includes three different versions so you can choose the way you want to play:

1. Printable version - This print ready version contains a blank grid, simply print it off and write all the player’s names down on the piece of paper. It’s quick and easy.
2. Pick your own squares - Manage the office Super Bowl pool directly in Excel. In this version, each player gets to pick what squares they want. Once all are taken, press the randomize button to generate the random score numbers. No modifications are necessary, though you can if you so desire.
3. Assign squares randomly - In this latest version, type each player's name in the manager sheet and use the drop down menu to assign how many squares to give each player. Then click the Assign Names Randomly button and it will automatically populate the grid with all of the names.

Super Bowl Squares Rules & How to Play

Listed below are the basic instructions on how to play Super Bowl Squares (which are also included within the spreadsheet for the three different versions, along with an example):

1. Participants “purchase” individuals squares by writing their name in their desired squares, until all one hundred are filled.
2. After all squares are taken, the numbers 0 through 9 are written in the empty horizontal and vertical rows in random order (draw the numbers out of a hat).
3. After the end of each quarter of the game, match the last digit of each team's score to the corresponding square to find the winner.

Tip: 0, 3, and 7 are the best numbers to get. 8, not so much.

Added again this year is the option whether to keep the same random numbers chosen for all four quarters or to have four different sets of random numbers for each quarter. Using the rotating quarters method, someone could theoretically still get the number 3 for all four quarters, though that’s not very likely, or four different players could each get a 3 for different quarters.

Below is a short video tutorial I put together of how to use my Super Bowl Squares template in Excel.

Super Bowl Squares Template 2019 Download

The beauty of this football bowl manager is you will not have to change or modify any formulas yourself (unless you want to of course). Instructions are included within the Excel file and shows you exactly how to add more players (either manually or by using the button that is linked to a macro). If you enjoy using this sheet football pool manager I would really appreciate it if you would share it with friends, family, and coworkers. 

To be notified when any updates are made to the file or to get a notification when next year's template is available, download using this link(requires an email address):

Download the Super Bowl Squares Template 2019.xlsm file here

Please let me know in the comments or by email which version you'll use to play - printable, pick your squares, or randomly assign names. Or if you have any requests for modifications shout me out!