2022 Super Bowl Squares Spreadsheet - one size fits all

I’ve been creating Super Bowl Squares spreadsheets for over ten years now but this is just the second year of using my new and improved one-size fits all generator sheet. When you first open my free grid game template, you’re met with a simple setup screen where the user will decide how to play the game:

• How do you want to assign names to the boxes: manually pick and choose or automatic randomized?
• Do you want to use the same numbers of the Super Bowl score, rotate by quarter or by each half?
• Do you want to earn points per quarter, per minute, or every time the score changes?

No matter what you choose, after you click the “Generate Squares” button the sheet automatically updates the Super Bowl boxes and the leaderboard to reflect only the version of the game you want to play. There 54 different ways to play, all contained within this one template. 

After the Super Bowl, you can keep using this sheet for regular season NFL games. 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! Or make a few modifications and use this template for other sports. The possibilities are endless!

Watch me demo the new Super Bowl board in the video 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.


As you can see, the new sheet allows more ways to play than a fixed style 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 all of these cool Excel tricks:

• Lookup pictures based on cell values
• Generate random numbers
• Use the 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
• How to send an email from Excel
• How to attach an image to an email using VBA

New Features for 2022

A new feature I added for 2022 is the ability for Outlook users to easily share the Super Bowl squares via email. Enter all the player’s emails in Column A on the Send Scoreboard sheet. Just click the button and an email will automatically be composed from Excel complete with an image of the grid. I explained how to insert images into emails from a spreadsheet in a previous article.

Another way to share, copy and paste the board into a Google doc. This sheet won’t work in Google due to the macros but once you’ve got the grid filled in and the score numbers have been randomly generated, you could share it with your colleagues by copying and pasting as an image into a Google sheet. The manager would still keep score in the template but that way everyone can see what numbers they have. Let me know if you use this feature or if it isn't useful for you.

Get the Super Bowl squares spreadsheet here (enter a 0 into the price box then input an email address):

https://gumroad.com/l/superbowlsquares

*Intended for PC/Microsoft Office/Excel. I don't think it will work in Mac Numbers or Google sheets as macros are not supported*

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 Add an Excel Shape to Outlook Mail by VBA

Two spreadsheets I am most proud of are my Super Bowl SquaresGame Generator and my College Football Bowl Prediction Pool Manager (Bowl Pick’em Game). I’ve put a lot of time and features into these free templates. However, I still get many requests to add even more features. One of the most asked questions is “how can I share the leaderboard results when all the players aren’t connected to the same network?” I would normally respond with how I do it: “I take a screenshot of the scoreboard and manually email it to the players.” Then it dawned on me – why not automate this process to make it easy for everyone to use? Why not automatically add a picture of the Excel sheet into an email?

There are two methods I can think of off the top of my head for attaching an image into an email with a macro, and here are the pros and cons of each:

1. The picture is saved on your PC (or needs to be saved by the macro before inserting into the email)  – but either way you have to know the location of the file.
2. Copy and paste an image already in your spreadsheet into an email. Does not require saving the image. But you must know the shape name so the macro can find it.

For today’s tutorial, I am going to show you how to use the #2 method. 

A thread on method #1 can be found here: https://stackoverflow.com/questions/44869790/embed-picture-in-outlook-mail-body-excel-vba

You can read along or scroll down to watch the video below. Again, for this method to insert an image from a spreadsheet into an email, the image must already be created and named manually so the macro knows what image within the sheet to use.

Name the Shape or Image You Want to Copy and Paste From Excel into Email

First, I need to have a linked image in my spreadsheet that will be copied to the email. Highlight the area (the cells) that you want to have an image of, in my example the scoreboard of my Super Bowl Squares sheet. Next, I created a new sheet within my workbook where I will collect the emails of all the players. I right click, paste special, linked picture. Select the image. Under page layout go to Selection Pane. Rename the picture “Preview1” or some other descriptive name. This is what the macro will use to identify which picture to attach to the email.

I also want to allow the user the option to include a hyperlink to the Excel workbook in the email or not. To do this, I create a checkbox in Excel by going to the developer tab, insert, ActiveX controls, Check Box.

 

Attach Image to Email Excel Macro Code

Now it’s time to write the VBA macro that will automatically send an email to all the players with a picture of the latest scoreboard – all at the click of a button!

I’ve previously shared how to send an email from an Excel sheet but this is my first time attaching an image. Below is the full code with my comments explaining what is happening along the way.

Sub SendEmailUpdate()

'Optimize Macro Speed

  Application.ScreenUpdating = False

  Application.EnableEvents = False

  Application.Calculation = xlCalculationManual

 

'define the workbook, location, and name

Dim Wb1 As Workbook

Set Wb1 = ThisWorkbook

 

Dim OwnerName As String

OwnerName = Application.UserName

 

Dim FileLoc As String

FileLoc = Wb1.FullName

 

Dim WorkbookName As String

WorkbookName = Wb1.Name

 

'SendEmailTo will count the number of people who the email will be sent to

Dim SendEmailTo As Integer

SendEmailTo = 0

 

'we will store all the email addresses in one long string then insert them into the TO line of the email later

Dim ToPerson As String

ToPerson = ""

 

'loop through all players in column A of the Send Scoreboard sheet (up to 100 players max)

Dim x As Integer

For x = 2 To 101

 

    ' get the emails to fill in the TO line

    If Not IsEmpty(Wb1.Worksheets("Send Scoreboard").Range("A" & x).Value) Then

    ToPerson = Wb1.Worksheets("Send Scoreboard").Range("A" & x) & "; " & ToPerson

    SendEmailTo = SendEmailTo + 1

    Else

    'MsgBox "email is blank"

    'NoSEnd = NoSEnd + 1

    End If

   

    ' get the emails to fill in the CC line

    'If Not IsEmpty(WB3.Worksheets(1).Range(CCCol & PICRow).Value) Then

    'CCPerson = WB3.Worksheets(1).Range("D" & PICRow) & "; " & CCPerson

    'CCEmail = CCEmail + 1

    'Else

    'End If

 

Next

 

MsgBox "Email will be sent to " & SendEmailTo & " recipients."

 

'get the named Image to attach to the email and copy it

Set oPreview = Wb1.Worksheets("Send Scoreboard").Shapes("Preview1")

oPreview.CopyPicture ' oPreview is now in Clipboard

 

'launch Outlook

    Dim xOutApp As Object

    Dim xOutMail As Object

    Dim xMailBody As String

  

    On Error Resume Next

   

    Set xOutApp = CreateObject("Outlook.Application")

    Set xOutMail = xOutApp.CreateItem(0)

  

'for html email

 

If Wb1.Worksheets("Send Scoreboard").CheckBox1.Value = True Then

    'include the link to the spreadsheet

    xMailBody = "Hello everyone! <br><br>" & "The SuperBowl Squares scoreboard has been updated. You can access the sheet by clicking the link below. <br><br>" & _

    "Link: <br><br>" & "<a href=" & Chr(34) & FileLoc & Chr(34) & " > " & WorkbookName & " </a> " & "<br><br>" & _

    "Thanks for playing," & "<br><br>" & OwnerName

Else

    'false, no link

    xMailBody = "Hello everyone! <br><br>" & "The SuperBowl Squares scoreboard has been updated. Please see the below image: <br><br>" & _

    "Thanks for playing," & "<br><br>" & OwnerName

End If

 

 

    On Error Resume Next

   

    With xOutMail

        .To = ToPerson

        '.CC = CCPerson

        .BCC = ""

        .Subject = WorkbookName

        '.Body = xMailBody

        .HTMLBody = xMailBody

        .Display   'or use .Send

 Quick Note: To use the clipboard to copy and paste the picture into email, you need an Outlook mail editor which can deal with the clipboard. Here I use WordEditor for example. The WordEditor property of the Inspector class returns an instance of the Document class from the Word object model which represents the Body of your email: https://docs.microsoft.com/en-us/previous-versions/office/developer/office-2007/dd492012(v=office.12)?redirectedfrom=MSD

       

        Set oInspector = .GetInspector

        Set oWdDoc = oInspector.WordEditor

   

        Set oWdContent = oWdDoc.Content

        Set oWdRng = oWdDoc.Paragraphs(1).Range

        'oWdRng.InsertBefore "This is a test"

        oWdRng.InsertParagraphAfter

        oWdRng.InsertParagraphAfter

 

        Set oWdRng = oWdDoc.Paragraphs(3).Range

        oWdRng.Paste ' paste from oPreview Clipboard

 

        olFormatHTML = 2

        .BodyFormat = olFormatHTML ' change to HTML

       

    End With

   

    On Error GoTo 0

   

    Set xOutMail = Nothing

    Set xOutApp = Nothing

'---------------------------------------------------------------

ResetSettings:

  'Reset Macro Optimization Settings

    Application.EnableEvents = True

    Application.Calculation = xlCalculationAutomatic

    Application.ScreenUpdating = True

End Sub

2021 College Football Bowl Prediction Pool

The college football conference championships were played this past weekend which means the 2021 NCAA college football bowl season is here again! Therefore, it’s time to make your picks and predictions about who you think will win each bowl game. One of the best times of the holiday season (other than giving and receiving gifts) is being able to talk trash to your relatives about their terrible bowl picks. This year has the added bonus of not just single bowl games but the eighth year of a four team playoff to determine the national champion.

Features for this year's bowl prediction pool over the previous college football bowl pool manager spreadsheets include the following:

• Easy method to make each bowl game worth a different point value, so the national championship game and semi-finals can be worth more points, or however you want to customize it.

• Updated leaderboard tab with new stats

• Separate entry sheet to pass out to participants or co-workers that can be imported automatically by a built-in macro

• Complete NCAA college football bowl schedule with game times and TV stations

• New stat sheet to track each conference's record during bowl season. Graph shows total conference teams and total conference wins

The bowl prediction sheets include the football helmet designs for every team (taken from the 2017 college football helmet schedule spreadsheet), their win-loss record, and the logo for all bowl games. I added the helmets so those players who aren't big college football fans can pick a winner based on their favorite helmet design!

Download the CFP Pool Manager and Single Entry Form here

I'm working on a new version where you could do confidence points that you can test out now and give me feedback.

Download the 2021 CFP Bowl Prediction Pool Manager.xlsm file here

Please let me know if you have any questions, comments, find any bugs, or have any suggestions for improvement. I love that people are using this Bowl Prediction Game to help raise money for charity, that's so awesome to hear! What team are you rooting for?

Gift Guide for Excel Users 2021

 The 2021 holiday season is officially upon us here in the United States which means it’s time for my annual gift giving guide. I used to panic every year whenever my spouse, parents, and siblings asked me what I wanted for Christmas. I needed to give them an idea otherwise I’d end up with an ugly sweater or some random gadget I would never use.

So to help alleviate some of my stress I started compiling my own holiday gift guide. It’s kind of like the big toy catalog you used to get as a kid, only this is for adults. I’ve made a list of items I think would be very useful or exciting for your fellow Excel users, sorted by different categories (and yes, this post does contain Amazon affiliate links). Some of these items I already use on a daily basis and others are things that are on my own personal wish list. It's my biggest and best gift guide yet! Enjoy!

How to Search an Outlook Email Folder by using an Excel VBA Macro

One exciting aspect of using macros in Excel is that they can “talk” to other programs, like PowerPoint. One example I’ve shared is exporting data from Excel into Microsoft Word as the basis for writing a book. Another common use is exchanging information with Microsoft Outlook and writing emails from Excel. Previously, I showed how you can send emails from Excel. Today I want to show you a quick example how you can export email data from a folder in Outlook to Excel.

Let’s pretend you’ve saved emails every month with monthly expenses for your business in a folder called “01 Reports” in your Outlook email. You want to summarize the expenses in an Excel sheet without having to open and copy and paste every email in the folder. A macro in Excel written with VBA is the perfect solution for this scenario. Here’s how to do it.

 

First, setup the template. In cell A2 I am going to allow the user to write in the name of the folder they want to search through for the email reports to export to Excel. Then, we will place the email report date, email sender, and the expense cost into columns B, C, and D respectively. Once the template is setup, we can begin coding.

 

Create a new macro called “Search_Email_Folder.” Open the Visual Basic Editor (VBE). Go to Tools > references. In the object library, scroll down and Check the box of “MICROSOFT OUTLOOK 14.0 OBJECT LIBRARY” to make it available for Excel VBA.

 

Add a header to the top of the code that explains what the macro does. This macro loops through a specified folder in Outlook to export all the expense report data

Sub Search_Email_Folder()

On Error GoTo ErrHandler

  'Optimize Macro Speed

    Application.ScreenUpdating = False

    Application.EnableEvents = False

    Application.Calculation = xlCalculationManual

   

    Dim WS As Worksheet

    Set WS = Worksheets(1)

   

    'Find the last non-blank cell in column B and clear all the old data

    Dim lRow As Long

    lRow = Cells(Rows.Count, 2).End(xlUp).Row

    WS.Range("B2:G" & lRow).ClearContents

   

The Outlook object model provides all of the functionality necessary to manipulate data that is stored in Outlook folders, and it provides the ability to control many aspects of the Outlook user interface (UI). What is MAPI? Use GetNameSpace ("MAPI") to return the Outlook NameSpace object from the Application object. The only data source supported is MAPI, which allows access to all Outlook data stored in the user's mail stores. This is a “late binding” example. the following code sets an object variable to the Outlook Application object, which is the highest-level object in the Outlook object model. All Automation code must first define an Outlook Application object to be able to access any other Outlook objects. Most programming solutions interact with the data stored in Outlook. Outlook stores all of its information as items in folders. Folders are contained in one or more stores. After you set an object variable to the Outlook Application object, you will commonly set a NameSpace object to refer to MAPI, as shown in the following example.

 

        Dim objOutlook As Object

        Set objOutlook = CreateObject("Outlook.Application")

        Dim objNSpace As Object

        Set objNSpace = objOutlook.GetNamespace("MAPI")

        Dim myFolder As Object

        

        '---define the Outlook folder to search through. refers to cell so anyone can change the text without changing the macro code

        Dim EmailFolderToSearch As String

        EmailFolderToSearch = WS.Cells(2, 1) '—place name of folder in cell A2. must update if insert new columns before the first one

       

        'error handling if no folder specified

        If EmailFolderToSearch = "" Then

        MsgBox "No folder specificed."

        Exit Sub

        Else

        'proceed

        End If

       

        'MsgBox EmailFolderToSearch

        ‘the email folder to loop through is actually a sub folder of the Inbox

        Set myFolder = objNSpace.GetDefaultFolder(olFolderInbox).Folders(EmailFolderToSearch)

        Dim rcvDate As Date

        Dim iRows As Integer

        Dim objItem As Object

        Dim EmailSender As String

        Dim SenderEmailAddress As String

        Dim NumofReports As String

        Dim filID As Integer

        Dim DrwPost As Integer

              iRows = 2

             MsgBox "The number of emails found is: " & myFolder.Items.Count & " in " & myFolder.Name & " folder."

              'Loop through every email in outlook drawing folder

        For Each objItem In myFolder.Items

                   If objItem.Class = olMail Then

                Dim objMail As Outlook.MailItem

                Set objMail = objItem

 

                rcvDate = objMail.ReceivedTime

                EmailSender = objMail.SenderName

                SenderEmailAddress = objMail.SenderEmailAddress

               

                If Left(SenderEmailAddress, 3) = "/O=" Then

                    'internal gemail, skip, don't increase the row number

                                  Else

                ‘where to put the data in the Excel sheet:

                    WS.Cells(iRows, 2).Value = rcvDate

                    WS.Cells(iRows, 3).Value = EmailSender

                    WS.Cells(iRows, 4).Value = SenderEmailAddress

                   

                     'find the number of reports, information contained within the body of the email

                     filID = 0

                     DrwPost = 0

                mailBody = objMail.Body

 ‘search the email body for the word REPORTS

                filID = InStr(1, mailBody, "REPORTS", vbTextCompare)

              

                        If filID> 0 Then

                            DrwPost = filID + 6

                            NumofReports = Mid(mailBody, DrwPost, 15)

                            WS.Cells(iRows, 7).Value = NumofReports

                            Else

                            'number of reports not found

                        End If

                                           iRows = iRows + 1

                End If

                       End If

        Next

                  'Release

        Set objMail = Nothing

        Set objOutlook = Nothing

        Set objNSpace = Nothing

        Set myFolder = Nothing

   ErrHandler:

    Debug.Print Err.Description

          'Reset Macro Optimization Settings

        Application.EnableEvents = True

        Application.Calculation = xlCalculationAutomatic

        Application.ScreenUpdating = True

         MsgBox "Macro complete!"

 End Sub

Weighted Olympic Medal Count 2021

In honor of the 2020 Summer Olympic Games currently being held in Tokyo, Japan (in the year 2021 no less), I decided to create a Microsoft Excel spreadsheet template for the medal count as I did for the 2018 Winter Olympics, 2016 Summer Olympic Games, 2014 Winter Olympics and 2012 Summer Olympics. There are two primary methods most websites appear to be ranking the 2020 medal count. Most sites rank countries by the total number of Olympic medals won. Other sites, like the International Olympic Committee (or IOC) rank countries by their gold medal count. And others rank by other factors like per capita or GDP.

Pictured below is a bar chart showing all medals won for the top 22 countries (as of the time of this posting on 7-27-21). The bar chart is created in Excel by highlighting the data then going to Insert>Bar>Stacked Bar chart. Change the colors of the bars by right clicking on them then use the drop down menu to select the data you want to change.. You can update the chart yourself by download the Excel file here.

I’ve devised my own ranking system to give each Olympic medal a weight where the silver is worth half a gold medal and a bronze is worth only a quarter of the gold. Based on this new scoring system, previous Olympic results suddenly became quite interesting. However, for the 2020 Summer Games not too much actually changes (so far, will revisit after more events are completed).

Download the spreadsheet and see for yourself. I’ve shared my Olympic Medal Count spreadsheet and listed out the Olympic medals by country. How would you weight each medal against the others? Comment below and share any of your Olympic medal rating systems!

2021 NFL Helmet Schedule Spreadsheet – finally automated!

One of the many templates I update and release on an annual basis is my NFL Helmet schedule spreadsheet, where you’ll find the complete schedule for every single pro football team’s season with an image of each team’s helmet. Updating the schedule by hand is very tedious. Thankfully, a reader did it for me last year. But I’ve always wanted to automate a solution. To do this, there were two main problems to solve:

1. How to get the NFL schedule into Excel quickly without a lot of manual work

2. How to assign the correct helmets to every game without doing any manual work

I’ve finally got a solution for both problems and can proudly say the sheet is now fully automated. Here’s how it works.

Getting the NFL Schedule Into Excel

Problem number one: how to get the complete NFL schedule into Excel without having to copy and paste 32 team’s individual schedules manually. There’s got to be an online solution, right? First, I went to NFL.com. No good: no easily copy-able full schedule. Next, tried ESPN. Success! Grid format is perfect for copying pasting right into Excel. As long as ESPN (or another site) always posts the schedule in this format we can update all 544 games and their helmets within a minute. If you download my template, unhide the hidden columns. The blue cells are copy and pasted directly from ESPN. I use formulas to change the three letter team abbreviations into the full team names. 

As you can see, the NFL expanded the regular season this year by one game, from 16 to 17 (plus each team gets a bye week hence 18 weeks in the regular season). The preseason is reduced form 4 to 3 games.

Automating Assigning All the Helmets

Problem number two: how to populate the schedule with all the helmets? I was thinking about using linked pictures like I do in my Super Bowl Squares template. But this would have required adding a formula in name manager to all 544 helmets. Instead, I decided to have a macro copy and paste helmets associated with each team automatically into the schedule. This required giving all 32 helmets a unique variable name, which was time consuming, but now that I have it setup I don’t have to change again, even when it’s time to update for next year’s schedule.

On previous versions of the sheet I divided out the two conferences on separate sheets: NFC and AFC. This year, I’ve put all the teams into one sheet. However, there is a new filter option where you can filter by NFC or AFC or even by division: AFC North, AFC South, etc.

Download the 2021 NFL Helmet Schedule Spreadsheet

Download the 2021 NFL Helmet Schedule Spreadsheet here

Watch the video below to see how the filter works. I also so a tip in Excel how to select multiple objects at once with the mouse. And I walk through the populate helmets macro code as well. Lots of good stuff here!

Please note, an email is required to download it. I do this so you will be automatically updated you if changes or additions are made and will update you when the next year’s schedule is ready. I do not use your email for anything else.

This goes to show you a little bit of time and thinking now can save you a LOT of time and trouble later on.

As you can see, the NFL helmet schedule is printable too. You can save the spreadsheet as a PDF file or print it out and pin it up in your cubicle at work. If you do, please email or tweet me a picture of it hanging up - I'd love to see it!

As always, I welcome any comments or suggestions about how to fix or improve the sheet! How can I improve this football spreadsheet into something you’ll use all the time during pro-football season? What future features would you like to see?