2017 College Football Bowl Prediction Pool Manager with CFB Playoff Bracket

The college football conference championships were played yesterday which means the 2017 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 fourth 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 by a 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!
• If you click on one of the helmets it should take you to that team's ESPN page so you can learn more about them and see which teams they won or lost to.
• Download the CFP Pool Manager and Single Entry Form here.

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

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

Here's an older video to show you what the sheet looks like 
Below is my newly recorded 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.
Here are some of my Bowl Game Observations that may interest only me...

• Unlike last year, when there were four teams with losing records, there are none this year, though there are a ton of 6-6 teams (yawn).
• There are no undefeated teams in the four playoff spots, though UCF is 12-0.
• Biggest gap of match-up between number of wins is 12 win UCF vs 8 win Auburn.
• There are 5 teams with 12 wins, but only three of them made the playoffs. Can you name the other two?
• There are four teams with 11 wins, can you name the others besides Alabama?

Download the 2017 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?

2017 Holiday Gift Guide for Microsoft Excel Users

The 2017 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. 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

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!

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!

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!

Tools for the Job

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

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.

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.

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.

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.

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.

Toys, Tech, Gadgets, and Others

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?

Excel macro to clear cell contents, textboxes, pictures, and lines within a range

Clearing the cell contents using an Excel macro is very easy (and if you’re unfamiliar see my getting started with macros quick start guide). In fact, it can be done with only one line of code. To clear the contents of range B4 to C30 for example:

ActiveSheet.Range("B4:C30").ClearContents

But this macro will only delete the contents (numbers and words) inside the cells of the active sheet. What if you also want to delete any shapes within the range, including textboxes, pictures, and lines? You can use OLEFormat.Object to identify specific types of shapes and delete the ones you want. This is a better method than simply deleting every single shape within a sheet.

So the code below basically:

1. Looks for all shapes in the active sheet
2. Picks out the type specified, for example it will look for all the shapes that are of type TextBox
3. Checks if the top left corner of the specified shape falls within the defined range of cells
4. If the selected shape is within the range, then delete that shape

Sub DeleteCellContentsInARange()

'clear cell contents in the specified range

ActiveSheet.Range("B4:C30").ClearContents

'Delete TextBoxes

Dim Tbox As Shape

For Each Tbox In ActiveSheet.Shapes

If TypeName(Tbox.OLEFormat.Object) = "TextBox" Then

  If Not Intersect(Range(Tbox.TopLeftCell.Address), Range("B4:C30")) Is Nothing Then

    Tbox.Delete

End If

End If

Next Tbox

'Delete lines and arrows in Excel

Dim Tline As Shape

For Each Tline In ActiveSheet.Shapes

If TypeName(Tline.OLEFormat.Object) = "Line" Then

  If Not Intersect(Range(Tline.TopLeftCell.Address), Range("B4:C30")) Is Nothing Then

    Tline.Delete

End If

End If

Next Tline

'Delete Pictures

Dim TPic As Shape

For Each TPic In ActiveSheet.Shapes

If TypeName(TPic.OLEFormat.Object) = "Picture" Then

  If Not Intersect(Range(TPic.TopLeftCell.Address), Range("B4:C30")) Is Nothing Then

    TPic.Delete

End If

End If

Next TPic

End Sub

You could easily add more to the code and have it loop through every sheet in a workbook instead of just the active sheet. I hope that helps!

Ask Excel Help: How to add or count across multiple sheets in Excel

I recently received a question from a reader on our Excel Help Facebook page asking the following:

=SUM(First:Last!A1) adds up numbers across a series of sheets in the same workbook only if A1 contains a number. How do I add up the total number of times an A1 cell contains a text value x?

First of all, if you’re not familiar you are able to add values across different sheets using SUM function. Say you have 3 worksheet named Sheet1, Sheet 2, and Sheet3. You can use this formula to add up all the values in cell A1 in all three sheets:

=SUM(Sheet1:Sheet3!A1)

This special syntax is referred to as a 3D Reference.

Remember, SUM adds all the numbers in a range. You can type in the formula manually, or, after typing “=SUM(“ hold down the shift key and select the sheets, then select the cells to sum. To sum a range of numbers across sheets rather than an individual cell, the formula would look like this:

=SUM(Sheet1:Sheet3!A1:B16)

If your sheet names are numbers, add apostrophes to the formula:

=SUM(‘1:3’!A1:B16)

So our reader was asking how to count cells with text rather than adding numerical values. COUNT function will count the cells containing numbers, while COUNTA will count the number that are not empty. Same formula as before but replace SUM with COUNTA:

=COUNTA(Sheet1:Sheet3!A1)

This method works for almost any other function, too, such as AVERAGE. However, if you try using SUMIF or COUNTIF you’ll probably get a #VALUE error. Using those functions requires a bit more work using named ranges and INDIRECT, a topic for another day.

If you ever have any Excel questions or need any Excel help please feel free to send them my way! I read each and every message or comment I receive.

How to Increase Macro Speed and Decrease Programming Time

Here are some tips to show you how to increase your macro’s running speed as well as how to save programming time when you’re creating a macro.

First, there are a few options in Excel you can automatically turn on or off to help increase your program’s speed. You may have heard of these before, but for those who haven’t here is a quick recap:

Screen Updating: You can turn screen updating off so that Excel does not update the screen image as your code executes to speed up your macro code. No, you won't be able to see what the macro is doing, but it will run faster. Use this code:

Application.ScreenUpdating = FALSE

Be sure to restore the setting to True at the end of your macro

Excel Calculation: You can actually prevent Excel from recalculating a workbook by using the statement:

Application.Calculation = xlCalculationManual

However, when the calculation mode is xlCalculationManual, Excel doesn't update values in cells, and I’ve also heard this could erase the Clipboard Memory or cause other problems with Excel “losing focus”.  If your macro relies on an updated cell value, you must force a Calculate event, with the .Calculate method, which may be applied to either a specific range (Range("MyRange").Calculate) or to the entire workbook (Calculate).

Display Alerts: Set this property to False to suppress prompts and alert messages while a macro is running; when a message requires a response, Microsoft Excel chooses the default response.

Enable Events: This property can be set to False to prevent the application from raising any of its events. Make sure you turn the events off at the start, and back on at all exit points of the procedure.

Now, we can take these a step farther and put them all in a public subroutine to be called upon as necessary. Here’s how to turn all of the above mentioned items on and off at the start or end of a program:

Public Sub SpeedOn()

With ThisWorkbook.Application

    .ScreenUpdating = False

       .Calculation = xlCalculationManual

    .DisplayAlerts = False

    .EnableEvents = False

End With

End Sub

Public Sub SpeedOff()

With ThisWorkbook.Application

    .ScreenUpdating = True

    .Calculation = xlAutomatic

    .DisplayAlerts = True

    .EnableEvents = True

End With

End Sub

Save these two functions in notepad or somewhere you have quick access to. This way, you do not have to type out this code every time you start a new macro, simply copy and paste the pre-written code.

Another macro speed tip I want to share is, notice how I used the With...End With in the above code? If you are using several statements in a row that apply to the same object, use a WITH statement, rather than fully qualifying the object each time, to help make your VBA macro run faster.

Delete Non-essential Sheets Example

Let’s take it a step further. For example, say you have a macro to delete all sheets in an Excel file except for a few important sheets you’re using that you want to keep. How might you increase the speed of this macro?

Public Sub DeleteAllSheets()

For Each ws In ThisWorkbook.Sheets

    If Not (ws.Name = "Important Sheet 1" Or ws.Name = "Important Sheet 2" Or ws.Name = "Important Sheet 3") Then

        SpeedOn

       ws.Delete

        SpeedOff

    End If

Next ws

End Sub

Notice how I call the SpeedOn and SpeedOff functions we created earlier?

Another tip to increase macro speed: when looping through a collection it is usually faster than the FOR EACH statement rather than using the index.  For example, in the code above I used FOR EACH. It would work but would run slower to use a FOR…NEXT loop:

Dim i as Integer

For i = 1 To Worksheets.Count

        If Not (ws.Item(i).Name = "Important Sheet 1" Or ws.Name = "Important Sheet 2" Or ws.Name = "Important Sheet 3") Then

        SpeedOn

        ws.Item(i).Delete

        SpeedOff

    End If

Next i

I hope these tips help increase the speeds of your macros and decrease your coding time. If you have any other tips you’ve implemented yourself I would love to hear about them!