2019 College Football Bowl Prediction Pool Manager with CFB Playoff Bracket

The college football conference championships were played this past weekend which means the 2019 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 gathering around the TV and rooting for your Alma mater or hometown football team. This year has the added bonus of not just single bowl games but the sixthyear 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 (Go Big Ten! Go MAC!)
• 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.

How to Enter Player's Picks

There are now three ways to add participant’s data:

• Manual entry using the drop down lists
• Copy and paste from the selection sheet to the bowl manager
• Use the import macro to automatically import a player’s data into the pool manager by way of a macro

Below is an older video of how to use the bowl pool manager spreadsheet.

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.

I am working a version where you can add confidence picks by ranking the bowls in order of how confidence you are with your picks. I did one for 2017 but have not updated 2019's for this yet. Let me know in the comments or via email if this is something you would be interested in.

Here are some of my Bowl Game Observations that may interest only me...

• Only bowl eligible team to not make a bowl game? My University of Toledo Rockets.
• Most lopsided records? 6-6 Washington State vs 10-2 Air Force. Next you have 7-5 Washington vs 12-1 Boise State
• There are now 6 independent teams not residing in a conference, 3 of which made bowl games. Can you name them?
• Matchup with most combined wins: 13-0 OSU vs 13-0 Clemson
• The ACC leads all conferences with 10 teams represented, followed by the Big Ten and SEC with 9 apiece.
• There are no teams with losing records this year. There are 13 teams with 6-6 records, though there are no 6-6 vs 6-6 bowl match-ups
• There are 22 teams with 10 wins or more

Download the 2019 - 2020 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?

2019 Gift Guide for Excel Users

The 2019 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. 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!

MY GO TO EXCEL BOOKS

Excel 2016 Bible - The complete guide to Excel 2016, from Mr. Spreadsheet himself! Whether you are just starting out or an Excel novice, the Excel 2016 Bible is your comprehensive, go-to guide for all your Excel 2016 needs. Whether you use Excel at work or at home, you will be guided through the powerful new features and capabilities by expert author and Excel Guru John Walkenbach to take full advantage of what the updated version offers. Learn to incorporate templates, implement formulas, create pivot tables, analyze data, and much more.

Excel 2016 Power Programming with VBA is fully updated to cover all the latest tools and tricks of Excel 2016. Encompassing an analysis of Excel application development and a complete introduction to Visual Basic for Applications (VBA), this comprehensive book presents all of the techniques you need to develop both large and small Excel applications. Over 800 pages of tips, tricks, and best practices shed light on key topics, such as the Excel interface, file formats, enhanced interactivity with other Office applications, and improved collaboration features.

If you’ve ever thought to yourself “there has to be a better way to do this,” while using Microsoft Excel, then know you're probably right. There probably is a better way to complete your tasks you just don't know what it is and you don't have time to read a boring, expensive, thousand page manual on how to use Excel. 76 Excel Tips to Increase Your Productivity and Efficiency is for you. No fluff, just Excel tips and tricks you can put to use right away.

OTHER BOOKS WORTH READING

Will It Fly? How to Test Your Next Business Idea So You Don’t Waste Your Time and Money by Pat Flynn. I’ve been following Pat’s blog and podcast for a number of years, and you might have seen some of his tips at work on my site. If you’re new to the online business world, this book is fantastic. Lots of practical steps to take to prove whether your idea has validity or not. Over 700 reviews and a five star rating, that's impressive!

The Martian by Andy Weir. If you only read one (fiction) book this year, The Martian has to be the one. I absolutely love this book (and it’s even better than the movie). As soon as I finished it the first time, I immediately re-read it – something I’ve never done before. It’s about an astronaut (with a great sense of humor) who gets left behind on a mission to Mars and has to figure out how to survive. If you’re interested in space exploration, problem solving, engineering, chemistry, botany, or disco + 70s TV shows, I highly recommend you read The Martian. Maybe the best book I’ve read in the past five years.

Another quick, shameless self-plug. Where are the most terrifying roller coasters found? Who designs them? Which park builds the craziest rides? Find out by reading my book 50 Groundbreaking Roller Coasters. Another reason for including this book on this list is to show you a real life usage of Excel. How's that? Because this is one of the books I wrote using an Excel spreadsheet!

Nowhere in the world is there a more bizarre theme park than Happy Fun Land. Nike Farmington’s twelve years of thrill-seeking and roller coaster riding has brought him to exotic locales like Perth, Australia, Kaatsheuvel, Netherlands, and Santa Claus, Indiana. He's marathoned a roller coaster for ten consecutive hours and conquered the world’s tallest and fastest. Yet nothing has prepared him for the insanity of Happy Fun Land and it’s mind blowing attractions: a drop ride with no brakes and a death simulator, just to name a few. Will Nike survive his hilarious adventure through the world's craziest theme park? I thought this book was hilarious and I think you will too!

Tools for the Job

Excel Quick Reference Sheets - Laminated quick reference showing step-by-step instructions and shortcuts for how to use Microsoft Office Excel 2016 (Windows Version). Written with Beezix's trademark focus on clarity, accuracy, and the user's perspective, this guide will be a valuable resource to improve your proficiency in using Microsoft Excel 2016. This guide is suitable as a training handout, or simply an easy to use reference guide, for any type of user.

TechSmith Snagit takes the hassle out of creating images and videos. Capture your screen, edit images, and deliver results. Snagit is also the only screen capture tool with built-in advanced image editing and screen recording. So you can easily create high-quality images and videos all in one program. Quickly explain a process, build visual-based documentation and be more engaging by adding images and videos to your communications. It's the tool I use to create all the images for Excel Spreadsheets Help and well worth the price.

Microsoft Surface Tablet. Need to use Excel on the go put don't want to lug around a larger laptop? A Surface tablet is great way to go.

Dimmable Eye-care LED Desk Lamp. A great lamp and exactly what I was looking for in a new clip on lamp for my drafting desk. It has six different light settings so I can find the right lighting for all of my needs. It is well built, works great besides being stylish.Besides using at work can use at home as a reading lamp too.

Keyboard Case for Tablets. How do I get so much done, especially when I’m traveling on the road a lot? I use a combination of a Samsung Galaxy Tablet and my new Keyboard case. They’re small so I can take it almost anywhere and the keyboard allows me to do things like type out this blog post, reply to your email questions, and write Excel macro code.

USB Heated Mouse / Hand Warmer. I'm not sure about you but the office at my day job can get really cold during the winter, especially after weekends or holidays. One solution I've found that helps is a heated mouse to keep you hand warm while not impacting my ability to get things done.

Laptop Privacy Screen Protector. Whenever I visit a customer I always take my privacy screen protector for my laptop. It keeps your personal or confidential information safe from prying eyes as you’ll see the information on your display while people on either side only see a darkened screen. If you’re ever on an airplane or in a coffee shop and feel like your neighbor is constantly looking over your shoulder at your screen then you need to get one of these today!

A good quality laser point. This laser pointer always comes in handy when it’s time for a meeting or presentation. Plus, it doubles as a toy to keep your cats entertained.

Rocketbook Smart Erasable, Reusable Wirebound Notebook with Penstation – the last notebook you’ll ever need!

Krieger Plug Adapters (Most of Europe (type C)) - If you're going to do some international travel I highly recommend taking this adapters with you. They come in a pack of four so you can share when your travel buddy forgets his.

Toys, Tech, Gadgets, and Others

Handheld Gimbal Stabilizer for Smartphone – Outside of this website, in my spare time I like to make and edit videos and montages (mostly of my family). It’s easiest just to use a cellphone but the video is often shaky due to my unsteady hands. So I decided to get a gimbal and I’ve had fun playing around with it.

Anker PowerCore Fusion - This is a portable power charger that plugs directly into the wall so it works as your regular charger, but is also a battery so you always have power on the go.

In my little free time from working and being a dad, I like to play around with my Samsung Gear VR headset. Virtual Reality is really taking off and the technology is getting much better. Though be warned it may cause motion sickness if the app you’re looking at doesn’t perfectly track your head movement.

Solar Powered Christmas Lights. I love putting up Christmas lights and trying to out-do my neighbors, but I have to admit I feel a little guilty about using the additional electricity. Luckily I found these solar powered Christmas lights and they actually work very well! They’re environmentally friendly and they automatically turn on and off each night. They also don’t have to be attached to a power source so I can put them in areas I normally couldn’t string lights.

Dash and Dot – programmable robots. If you’re into programming things like Excel macros and you want to teach your kids the joys of programming then I’ve found the perfect gift for you. These cute robots are designed to help introduce children to the wonderful world of programming. Oh, and they’re fun for adults too! Can’t wait to use this with my son in a few years. For now he just likes watching me drive it around with my phone.

Fitbit Alta. Let’s face it – us engineers nowadays sit in front of a computer a lot. I didn’t realize how much I wasn’t moving until I started wearing a Fitbit. It now helps me to stay motivated by tracking all-day activity like steps, distance, calories burned and active minutes so I can stay healthy for my family. When I’m sick I can’t answer your Excel questions and help you out, so I use Alta to help stay in shape and on top of my game!

Amazon Prime Membership. If you haven’t joined Amazon Prime yet, why not? I do almost all my shopping online and I get free two-day shipping on nearly everything. You can also borrow books, watch movies, and stream music. Get your Prime Discounted Monthly Offering here.

YOUR SUGGESTIONS?

Are you putting any of these items on your holiday wish list? If so, let me know which ones in the comments below. Do have anything you’d like to recommend to me?

How to remove ordinal abbreviations from dates and numbers

Did you know there is a term for the two letters that follow the number of a date? You know, the "st" in 1st; the "nd" in 2nd; the "rd" in 3rd; the "th" in 4th? They're called ordinal abbreviations. These ordinal abbreviations are actually hybrid contractions of a numeral and a word. 1st is "1" + "st" from "first". Similarly, "nd" is used for "second" and "rd" for "third". Sometimes they're also referred to as elevated terminals due to the way they're typically written: 1st, 2nd, 3rd, 4th, etc..

What does this have to do with Excel?

Recently, a reader asked me a question. He was the the date in column B written as 11th, 1st, 2nd, etc., the month written out in column C, and the year in column D. So the question was how to transform these three separate columns: 11th December 219 into this one single column in Excel: 11/12/2019.

Here's how to solve this seemingly complex problem: break down into smaller problems.

First, to combine multiple columns into one, use concatenate. =b2&"/"&c2&"/"&d2.

Next, to change a month written out to a number, use this formula: =MONTH(1&C2)

Third, use SUBSTITUTE to find the ordinal abbreviations and replace them with nothing.
=--SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B3,"st",""),"nd",""),"rd",""),"th","")

Finally, put all the elements together for one long formula that seems complicated but is quite simple when you break it down into parts:

=--SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B2,"st",""),"nd",""),"rd",""),"th","")&"/"&MONTH(1&C2)&"/"&D2

2019 College Football Schedule Spreadsheet in Excel

The 2019 college football season is here! Sorry, I had some trouble with the macro I setup to automatically help in the creation of this spreadsheet so it took longer than expected to finish.

This spreadsheet of the college football schedules includes every team from all conferences plus independents. Every game is listed as either home, away, or neutral site (noted at the bottom of each sheet). 

One of the differences in this year's sheet is I opted to use the team's logos from ESPN rather than their helmets, since I was missing quite a few of them. Let me know if you are OK with this change or not.

For the third year in a row I’ve used macro automation to automatically create this sheet - no more manual copying and pasting for hours with many mistakes. The schedules are imported directly from ESPN (so if there is a mistake blame them!). 

Download the CFB schedule spreadsheet here. To get it for free, enter a 2 into the "name a fair price" box, click "I want this!" then on the next page enter discount code "2019" and it will drop the cost to zero dollars. I had to do it this way due to the size of the file and the limits placed on my Gumroad account.

2019 CFB Schedule Spreadsheet

As always, I welcome any comments or suggestions about how to fix or improve the sheet! Let me know if and how you’re using the sheet.

2019 NFL Helmet Schedule Spreadsheet

Sorry this took a bit longer than I was expecting it to but now available to download is the 2019 NFL helmet schedule spreadsheet. You’ll see a comprehensive breakdown of every NFL pro football team's 2019 season schedule with an image of each team’s helmet design. 

Use this link to download the NFL schedule spreadsheet. 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.

Here's a video overview of last year's sheet to show you what it looks like:

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?

How to hide checkboxes and rows in Excel with VBA

I recently received a question about how to hide or collapse a row if a checkbox in that row wasn’t checked. There are a lot of good lessons in this example. When you’re stuck on a problem, the best thing to do is to break it down into smaller problems and solve each one at a time. Let's do it!

How to Insert Checkboxes in Excel

The first lesson is how to add a checkbox to an Excel sheet. Go to Developer tab then Insert. There are two types of checkboxes: Form Control and ActiveX Control. For this example, we are going to use Form Control checkboxes because you can link these directly to a cell.

How to link a checkbox to a cell

The next lesson is how to assign a checkbox to a cell. We’re going to do this to help with hiding the rows later. When you create the first Form Control checkbox in cell A2, in the formula bar type “=$B$2” to assign B2 to the checkbox. Now, if the checkbox is checked B2 should read TRUE, if not selected it should say FALSE.

**The one downside to this method is if you need 100 checkboxes it could take some time to manually assign each checkbox to a cell. There is probably a way to automate this task, however, for simplicity of this example we’re going to say we only need five checkboxes and assign them all manually.**

Now your example sheet should look like this:

How to hide rows based on a cell’s value in VBA

The hiding of all the checkboxes and rows will be done automatically with a VBA macro. If you’re new to macros see this gettingstarted guide. 

Again, break it down into smaller problems. First, let’s figure out how to hide rows based on a condition, the TRUE or FALSE value in our helper column.

Define the first row that contains data we might want to hide:

BeginRow = 2

Define the last row that might contain data we want to hide.

EndRow = 5

Define the column number of our helper info, the column with the true or false values.

ChkCol = 2

Loop through the range of rows we just defined and if the value of the cell in our helper column is false, then hide that entire row:

    For RowCnt = BeginRow To EndRow

        If Cells(RowCnt, ChkCol).Value = False Then

            Cells(RowCnt, ChkCol).EntireRow.Hidden = True

        End If

    Next RowCnt

How to hide checkboxes automatically with macro

Dim CB As Shape

Dim sh As Worksheet

Set sh = ActiveSheet

We need to loop through all the shapes in the active sheet, see if they are the form control type of Check Box:

For Each CB In sh.Shapes

  If CB.Type = msoFormControl Then

    If CB.FormControlType = xlCheckBox Then

      'MsgBox CB.Name, vbOKOnly

     

      'if check box is checked, keep it visible, otherwise, hide it

      If CB.OLEFormat.Object.Value = 1 Then

        CB.OLEFormat.Object.Visible = True

        Else

        CB.OLEFormat.Object.Visible = False

End If

     

    End If

  End If

Next CB

Full Macro to Hide Rows and Checkboxes

Here’s the final code that hides entire rows based on checkbox is checked or not.

Sub Hide_checkboxes()

Dim CB As Shape

Dim sh As Worksheet

Set sh = ActiveSheet

For Each CB In sh.Shapes

  If CB.Type = msoFormControl Then

    If CB.FormControlType = xlCheckBox Then

      'MsgBox CB.Name, vbOKOnly

     

      'if check box is checked, keep it visible, otherwise, hide it

      If CB.OLEFormat.Object.Value = 1 Then

        CB.OLEFormat.Object.Visible = True

        Else

        CB.OLEFormat.Object.Visible = False

End If

     

    End If

  End If

Next CB

'now hide the rows

    BeginRow = 2

    EndRow = 5

    ChkCol = 2

    For RowCnt = BeginRow To EndRow

        If Cells(RowCnt, ChkCol).Value = False Then

            Cells(RowCnt, ChkCol).EntireRow.Hidden = True

        End If

    Next RowCnt

End Sub

How to unhide rows and checkboxes in Excel

Conversely, I also made a code to reset everything and unhide all checkboxes and unhide all rows:

Sub Unhide_checkboxes()

Dim CB As Shape

Dim sh As Worksheet

Set sh = ActiveSheet

For Each CB In sh.Shapes

  If CB.Type = msoFormControl Then

    If CB.FormControlType = xlCheckBox Then

      'MsgBox CB.Name, vbOKOnly

     

      'if check box is checked, keep it visible, otherwise, hide it

      If CB.OLEFormat.Object.Value = 1 Then

        CB.OLEFormat.Object.Visible = True

        Else

        CB.OLEFormat.Object.Visible = True

End If

     

    End If

  End If

Next CB

'now hide the rows

    BeginRow = 1

    EndRow = 5

    ChkCol = 2

    For RowCnt = BeginRow To EndRow

        If Cells(RowCnt, ChkCol).Value = False Then

            Cells(RowCnt, ChkCol).EntireRow.Hidden = False

        End If

    Next RowCnt

   

End Sub

How to insert a button and link to a macro

Finally, for ease of use we can add two buttons to our sheet to run each one of the macros. Go to Developer tab, insert command button.

Watch the video below to see how the macro to hide checkboxes and rows works.

As you can see, there is much to glean just from this one example.

How to send email from Excel with link back to workbook

One of my most popular articles is where I showed how to create a hyperlink in an Excel spreadsheet that when clicked composes an email message. This method used only hyperlink and concatenate formulas; no VBA macros. It’s simple, easy to use, and not too complicated. But you can only do so much with this method.

Today, I will be using the more complicated method of using VBA macros to show how you can add a button to your spreadsheet that when pressed will automatically compose an email containing a link to the file location of the spreadsheet. I’m constantly sharing spreadsheets with friends of coworkers. I don’t know how many times a day I create a new email message, copy and paste a file location into the message, create the hyperlink to the spreadsheet, etc. This macro will help save you some time (and hopefully impress your coworkers in the process).

Setup Your Spreadsheet to be Able to Send Emails From Excel

If you’re going to send the email to the exact same people every time, you could just include the email addresses inside the vba code itself. But if the people change or someone who doesn’t know VBA needs to update it, this is not the best solution. What I like to do is add a helper sheet, and on this sheet list all the email addresses of who to include in the “TO” line and who to “CC.” This way anyone with no coding knowledge can easily change who the email is for.

VBA Macro to Compose Email with Hyperlink

Now it’s time to write the Excel macro. I’ll walk you through it step by step.

Sub Compose_Email()

I almost always add these to optimize macro speed:

Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual

Dim Wb1 As Workbook
Set Wb1 = ThisWorkbook

Declare variable names for the cells where the email addresses will be saved:

Dim SendTo1 As String
Dim SendTo2 As String
Dim cc1 As String
Dim cc2 As String

The email address are entered on SendList sheet, my “helper” sheet. As I said earlier, you can change email address without having to change VBA code because the macro refers to the cell’s location as opposed to entering the email address itself:

Wb1.Sheets("SendList").Select
SendTo1 = Range("B1").Value
SendTo2 = Range("C1").Value
cc1 = Range("B2").Value
cc2 = Range("C2").Value

'back to the first sheet
ThisWorkbook.Sheets(1).Select

Now get the name shown in the upper right hand corner of Excel to use as the signature:

Dim OwnerName As String
OwnerName = Application.UserName

Get the workbook name. We’ll use this as our email subject line:

Dim WorkbookName As String
WorkbookName = Wb1.Name

Get the location where the spreadsheet is saved:

Dim FileLoc As String
FileLoc = Wb1.FullName

Time to start Microsoft Outlook if it hasn't already been started:

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)

The code will be slightly different for non-html email versus html email. For now, I am only going to show you how to do html email so we can easily link back to the spreadsheet. This is the body of the email. <br> is html code to go to the next line. Chr(34) is for quotation marks. This will make more sense once you run the macro and see the end result.

xMailBody = "Dear Team, <br><br>" & "I've updated the weekly financial report. Please check and sign this:" & "<br><br>" & _
"<a href=" & Chr(34) & FileLoc & Chr(34) & " > " & WorkbookName & " </a> " _
& "<br><br>" & "Thanks," & "<br><br>" & OwnerName

Fill in each section of the newly created email message:

On Error Resume Next

With xOutMail
.To = SendTo1 & "; " & SendTo2
.CC = cc1 & "; " & cc2
.BCC = ""
.Subject = WorkbookName
'.Body = xMailBody
.HTMLBody = xMailBody
.Display 'or use .Send

End With

On Error GoTo 0

Set xOutMail = Nothing
Set xOutApp = Nothing

Reset Macro Optimization Settings:

Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

End Sub

Add a Button to Run the Macro

Finally, now that the code is done you can add a button to your spreadsheet by going to the Developer tab, Insert, Button (Form Control). Link your newly created macro to the button, customize the button text, and that’s all there is to it!

Watch how the macro works in the video below where I also walk you through it step by step:

I’ve made what I’ve shown into a simple template you can download here:

Compose Email with Link Example Spreadsheet.xlsm download here

I hope you find this example will make sharing spreadsheets with your coworkers easier. Let me know what you think about this template in the comments below.