2022 College Football Bowl Prediction Pool

The college football conference championships were played this past weekend which means the 2022 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 ninth year of a four team playoff to determine the national champion. And it was recently announced this will be the second to last year of the four team format as starting in 2024 the college football playoff will expand to twelve teams. Can't wait to see how that plays out!

But let's not get ahead of ourselves and just enjoy that fact that for the first time the playoff doesn't have Alabama or Clemson in it! It's good to see at least one new team make the CFB playoff. Here's the full breakdown of bowl teams per conference:

There are multiple ways to play Bowl pick'em. 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 my 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

Bowl Pick'em Game For Google Sheets

Unlike many of my other spreadsheets, macros are not critical for the Bowl prediction pool. If you have a Google drive account you should be able to upload the pool manager and use it inside Google sheets. Participants can make their picks directly in the sheet using the drop down lists and everyone can see the scoreboard update in real time. All the formulas should work. Insert new columns where it says in order to add more players. The macro to import single player picks won't work but it is not needed. Try it if you want to play that way and let me know if you find any issues in Google sheets.

College Football Bowl Pick'em with Confidence Points

If you watch the video above you can see how this works. Each player can select confidence picks where they rank each game according to how confident they are in their pick. If you're super confident Georgia is going to roll over Ohio State you would rank that game 42 from the drop down list and would receive 42 points if you're correct. If you're not so sure Michigan will beat TCU you might only rank that game 1 or 2 points. 

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

Have fun! As always, I welcome any feedback or ideas for improvement.

How to change relative hyperlink to exact link in Excel

You may have used the HYPERLINK function in Excel before but what you may not have released is your links can be either relative or absolute (or sometimes called exact or specific). What does this even mean? And how do you change from relative to exact?

2022 NFL Helmet Schedule Spreadsheet

 As I explained last year, I finally automated this spreadsheet so I was able to create the 2022 NFL Helmet Schedule in less than a minute! All 32 teams, all 18 weeks of the season (now 17 games plus one bye week), all in one spreadsheet:

A job that used to take hour by moving helmets manually is now so easy to do. Yes, that's why I love the power of macros and automation. See how fast the sheet can be created by watching this clip:

View this post on Instagram

A post shared by Nick (@ntweisen)

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 2022 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!

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!

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.

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?

PS. I will not be rooting for the dumpster fire known as the Cleveland Browns this season so if any fanbases want to recruit, now's your chance to pitch me on your favorite team!

The Best FREE Template to Automatically Create Folders From Excel

One topic I've been discussing for more than ten years, and keep coming back to with improvements, is how to automatically create folders from Excel. I originally shared a simple macro to make folders back in 2011. More recently, I showed how to add hyperlinks back to the newly created folders. But I was never quite happy with these programs. So now I'm back with a better, more powerful, and easy to use template that will instantly create folders for you.

This Excel template to create folders will teach you:

• How to use a macro to automatically create folders from Excel (duh)
• How to change the color of a toggle button (and how to use a toggle button in Excel)
• How to add hyperlinks to folders
• Conditional formatting to change cell color based on combo box selection
• How to copy existing folder names into Excel
• How to duplicate a file structure

As I've done with all my recent templates, I try to make them look as clean as possible when the user first opens the sheet so as not to overwhelm them. I like to use yellow to clearly show the cells that require user input. Command buttons make running your macros very easy.

I envision this template being used in two primary ways: to create a set of new folders from scratch (their names manually typed in), or to quickly duplicate an existing file structure.

First, enter the location in the first yellow box where you want the new folders to be created. Next, type the folder names in the column. Or, if you want to duplicate existing folders, select the folders in explorer, hold down shift and right click, copy as Path. Use find and replace to remove the path. See in this example video:

One unique feature about this template is it allows the user to choose from one of four different options about how they want the folders to be named.

You can download this Folder Generator template for free, just enter a 0 here, an examine the VBA macro code yourself:

Quick VBA tip: To change the color of a toggle button once it is clicked (toggled on or toggled off), use this code:

Private Sub ToggleButton1_Click()

    If ToggleButton1.Value = True Then ToggleButton1.BackColor = vbGreen

    If ToggleButton1.Value = False Then ToggleButton1.BackColor = vbWhite

End Sub

Do you find this template to be useful? If so, please let me know in the comments below!

Free Meeting Scheduling Excel Template

How often do you ask friends, family, or coworkers which day is the best to get together? Whether it’s a meeting, party, trip, or some other occasion I find myself in these situations all the time. There are several apps and online calendars available to help you coordinate which day works for everyone involved. I used to use a site called Doodle all the time.  The problem with these services is they are not always free, they’re constantly changing and you have to keep relearning how to use them, you have to make an online account, and so on. So I did what I always do – I decided to make an Excel template to determine which day works best for meetings and events!

I used a lot of the same concepts and macro code from my Super Bowl Squares spreadsheet. When you first open the spreadsheet you are greeted by a simple, clean setup page. Here you’ll manually input the number of participants, meeting subject and description.

Next, you’ll enter the start and end dates of the days you want the participants to choose from. It’s very important that these two cells ONLY contain dates. To restrict a user to only being able to enter a date in a cell, go to Data > Data Validation. Under Allow select Date. Be sure to enter a custom error message so if a user makes a mistake they understand what needs to be entered.

When the user clicks Generate Schedule the Schedule sheet is unhidden. The schedule can handle up to 100 participants and up to 365 days. However, you probably won’t need all that so the macro will automatically hide all the rows and columns not needed to make it easy for the user to input their information.

A lot of good Excel tips can be gleaned from examining the event timing spreadsheet. Here's a quick summary of what can be learned by dissecting this free Excel template:

• How to add and use Option buttons
• How to use data validation to restrict entry in a cell to a date
• How to use data validation to restrict entry in a cell to an email address
• How to get the day of the week from a date
• How to use command buttons and assign specific macros to them
• How to send emails from Excel with hyperlinks
• How to use a formula to show only weekends in Excel
• How to use conditional formatting to change cell color based on cell value
• How to hide command buttons by macro

Download the Meeting Scheduler Template here.

Watch How to Coordinate Meetings with Excel

If you want to see how this spreadsheet works and some tips like how to limit a cell where a user can only input a date then watch the video below:

Try it out and let me know if you think it’s a legitimate replacement for Doodle, Calendly or whatever meeting scheduling apps you currently use. 

Weighted Olympic Medal Count 2022

In honor of the 2022 Winter Olympic Games currently being held  Beijing, China, I decided to create a Microsoft Excel spreadsheet template for the medal count as I did for the 2020 (2021) Summer Olympic Games, 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 2022 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 countries (as of the time of this posting on 2-14-22). 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.

As of 2/14/22

 

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).

If you’ve been reading my blog for awhile, you’ll probably know I’m a roller coaster enthusiast, so you probably won’t be surprised that the Olympic event that intrigues me the most is the bobsled and luge. As they said on the broadcast of the monobob following the Super Bowl, “this track more than any other is similar to a roller coaster” as it has 4 uphill sections and a helix. It’s over 4,000 feet long, has a total elevation difference of 117 meters (383 feet), and bobsleders were hitting 75 miles per hour while sustaining 4gs up to 7 seconds at a time. That’s intense! A ride on a bobsled would scare me more than any roller coaster that’s secured to the rails, as you could legit crash! What Winter Olympic sport do you like to follow?

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!

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!