Monday, December 5, 2016

2016 College Football Bowl Prediction Pool Manager with CFB Playoff Bracket

The conference championships were played yesterday which means the 2016 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 third year of a four team playoff to determine the national champion.

2016 college football bowl prediction pool


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!)
  • The bowl prediction sheets include the football helmet designs for every team (taken from the 2016 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!

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

Tweet: I'm using @NTWProductions pool manager in #Excel to predict #cfplayoff & #bowlgames. Get yours here: http://ctt.ec/8EBeO+


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

This year, four teams have made bowl games with losing records:

  1. Mississippi State (5-7)
  2. North Texas (5-7)
  3. Hawaii (6-7)
  4. Central Michigan (6-6 but should be 5-7 because of the fluke win at Oklahoma State)

This could get ugly: Motel 6 Cactus Bowl you have Baylor (6-6) vs. Boise State (10-2) or Birmingham Bowl South Florida (10-2) vs. South Carolina (6-6)

Strangely Intriguing: Popeyes Bahamas Bowl featuring Eastern Michigan (7-5) vs. Old Dominion (9-3). This is EMU’s first bowl berth since 1987 and Old Dominion, playing just their third year at the FBS level, going to its first-ever bowl.

If the MAC’s Western Michigan beats Wisconsin, it will have more Big Ten wins than four teams had in the conference in 2016.


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?

Thursday, November 24, 2016

2016 Holiday Gift Guide for Excel Users

The 2016 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


For just the second time ever, I am giving a discount on my paperback book, 76 Excel Tips to Improve Your Productivity and Efficiency. For 20% off, use discount code: 2F5RYRQC when purchasing directly from CreateSpace here: https://www.createspace.com/4754053. Or you can purchase from Amazon.com here.


Excel: QuickStart Guide - From Beginner to Expert by William Fischer. This Excel book covers everything you need to know about charts, pivot tables, data modeling as well as dashboard design. It is ripe with examples and real-world applications. For those starting in any kind of role in analysis, whether it be as a data scientist, financial analyst, research analyst, or just any type of analyst or manager in general, I would definitely recommend this book.



Excel 2013 Power Programming with VBA by John Walkenbach. This book covers all the methods and tools you need to know in order to program with Excel. Provides tips, tricks, and techniques for expanding Excel's capabilities with VBA that you wont find anywhere else

OTHER BOOKS WORTH READING


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.




Catalyst: A Rogue One Story by James Luceno. If you’re a Star Wars fan like I am you’re probably anxiously awaiting Rogue One: A Star Wars Story movie on December 16th. This book will wet your appetite before the new movie comes out. The story takes place during the Clone Wars and the initial formation of the Empire. Not required reading for the movie, but will give you some additional background information on the characters.



What If?: Serious Scientific Answers to Absurd Hypothetical Questions. This book contains hilarious and informative answers to important questions you probably never thought to ask, like: What if everyone on earth aimed a laser pointer at the moon at the same time? What if you could drain all the water from the oceans? What if all the lightning in the world struck the same place? Very fun and interesting read!




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.


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 The 50 Most Terrifying Roller Coasters Ever Built! 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 Dell 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!



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.



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 Sheet - Laminated quick reference showing step-by-step instructions and shortcuts for how to use Microsoft Office Excel 2013.

Microsoft Surface 2: There are newer, more powerful Surface tablets available but they're pretty expensive, running around $1000. The Surface 2 is now pretty affordable at around $250 and is still one of the best tablets for Excel and other Microsoft Office products. In fact, it comes with fully functional versions of Excel, Word, Outlook, Powerpoint, and Onenote for free. As far as I know, no other tablet has anything comparable. 32 and 64GB versions are available.

TECH, GADGETS, AND TOYS


Google Cardboard – Google Cardboard is described as Virtual Reality (VR) on a budget. Gives you a taste of the capabilities of the Oculus Rift at a fraction of the cost. Though be warned it may cause motion sickness if the app you’re looking at doesn’t perfectly track your head movement. It’s fun to assemble and play with. And cheap too.



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.



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.



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 emails 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. Click here to start your 30-day free Amazon Prime trial membership.



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?

Monday, August 15, 2016

Weighted Olympic Medal Count 2016

In honor of the 2016 Summer Olympic Games currently being held in Rio, Brazil, I decided to create a Microsoft Excel spreadsheet template for the medal count as I did for the 2014 Winter Olympics and 2012 Summer Olympics. There are two primary methods most websites appear to be ranking the 2016 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.

If you rank by gold medals countries like Great Britain and China look really good. On the other hand, Japan has 26 medals, ranking fifth overall, but only 27% are gold medals. Therefore, 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, the Olympic results suddenly become quite interesting.


Weighted Olympic Medal Count 2016


Pictured below is a bar chart showing all medals won for the top 30 countries plus Michael Phelps (as of the time of this posting on 8-15-16). 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.

2016 summer Olympic medal count chart

Looking at the new Olympic medal ranking systems yields some interesting results. The top four countries actually remain in the same order but Japan drops from fifth to seventh. Spain jumps up seven spots while Colombia jumps up five places. The biggest drop is by Czech Republic, falling 11 places because 5 of their 6 medals are bronze. Download the spreadsheet and see for yourself.




Oh, and if he were a country he’d rank 21st in total medals and in my weighted Olympic medal count he jumps as high as 12th overall because Michael Phelps' medal count at these Olympics Games is five gold and one silver.



I’ve shared my Olympic Medal Count spreadsheet and listed out the Olympic medals by country (as of the morning of August 15th - I will try to keep this updated but no promises!). How would you weight each medal against the others? Comment below and share any of your Olympic medal rating systems!

Wednesday, July 20, 2016

2016 College Football Helmet Schedule Spreadsheet

The 2016 college football season is a little over a month away! I still can’t believe my Buckeyes blew it at home against Michigan State and cost themselves a shot at repeating as Big Ten and National Champions. I have no idea what to expect this season. Looking at the schedule in the Excel file, I’m really worried about that away game against Oklahoma early in the season. At least if they lose, they could have time to recover to get back in the playoff hunt.


It’s fun looking at the 2016 college football helmet schedule and try to predict which games you think your favorite team will win or lose. 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).  A college football helmet schedule spreadsheet may be available on other websites but, to my knowledge, this is the only downloadable Excel version and, unlike some of the others, is 100% FREE!


2016 College Football Helmet Schedule Spreadsheet


2016 College Football Playoff Prediction Game

A new feature this year is the edition of the 2016 College Football Playoff Predictor! Week by week, you can pick the four teams who you think will make the playoff at the end of the year. You can see how your prediction changes throughout the year and if it is correct at the end. Crazy that out of 127 teams, only four make the playoff, a measly 3.1 %.  Compare that to the NFL where 12 of 32 makes the playoffs, or 37.5%.


2016 College Football Playoff Prediction Game



Download the football schedule today using the link below:




Suggestions to Improve the College Football Schedule Spreadsheet

I was thinking about other options I could add to make this template more fun, like how much fun the Bowl Prediction Pool is every year. One idea was predicting the top 25 teams compared to the AP Poll every week. What do you think? Do you play any office or fantasy games in regards to college football?

This spreadsheet does take some time to update due to the massive amount of helmets that need to be moved around. In the future I think I plan to make the spreadsheet easier to update and more maintainable. At least maybe figure out how to make the football helmets automatically populate, possibly using drop down lists. Maybe there is also a way to import the schedules from ESPN's website. Any ideas how to make the yearly update go quicker?

Tags: 2016 NCAA Excel Helmet Schedule

Wednesday, July 13, 2016

How to Write a Book Fast by Using Excel - Video Tutorial

Where most authors are writing their books in Microsoft Word, Scrivener, or Pages for Mac, I’ve actually been using Microsoft Excel to write books fast and today I’m going to show you my exact technique. You can see an example of one of my books created in Excel by checking out The 50 Most Terrifying Roller Coasters Ever Built on Amazon.com. As you can see, it has a 4.5 star rating from 14 reviews - none of which are from people I know, all real readers and customer - and has sold more than 2,000 copies. I’ve written four books using this technique and I chose this niche because I have a real passion for roller coasters and amusement parks.

Why write a book in Excel?


List type blog posts and articles are very popular these days, so my idea was to turn one of these type of list posts into a short book. This method is probably not a good idea for every type of book. You wouldn’t want to write a novel or long work of fiction in Excel. But if you're trying to relay stats or facts or have some form of repetition then this technique could help save you a lot of time. Anytime you find yourself doing something over and over or thinking to yourself "there has to be a better way" then, guess what, there probably is a better way and macros could be the answer.

How do you write a book in Excel?

Each row in my spreadsheet template starts a different page in the book. So 50 coasters equals at least 50 pages. I’ve colored coded the spreadsheet: every column in green is my initial input. Orange columns are formulas that use information from two other columns. All columns without color contain data that was entered by a virtual assistant whom I hired specifically for this job from Upwork.

Basically, I came up with the list of 50 coasters I wanted to feature in the book, then I outsourced the data collection process using Upwork. While my virtual assistant was contacting theme parks and researching data, I wrote the code to the macro that would automatically export all the data from Excel into the format in Word that I was looking for. The macro runs through a simple For..Next loop, looping through each row of the spreadsheet and exporting the data into the Word document.



After the information is exported to Word, just add your typical front and back matter, pictures, edit, and you’re done. Uploading to CreateSpace and Kindle Direct Publishing is a breeze. And you can outsource most of these tasks as well, if you have the budget for it. Or do it yourself. Most people don't realize that if you have something written, it could be on sale on Amazon.com in as little as two days. Heck, some guy got a picture of his foot to be a best seller.


Watch My Book Magically Appear


Watch the video tutorial below where I show you how to write a book fast by using Excel and see the book pages magically appear before your very eyes.


Steps to Write a Book Using Excel


Here's the basic steps I use to start writting my book with a spreadsheet:
  1. Setup Excel sheet
  2. Outsource data entry using Upwork
  3. Create macro to automatically export data to Word
  4. Run Macro
  5. Add front and back matter, pictures, etc. to book in Word document
  6. Edit and create front cover
  7. Publish on CreateSpace and KDP
  8. Market and promotion
Feel free to contact me if you'd like to know more or have questions about how the macro works to export the data from Excel into Word.

Wednesday, June 29, 2016

2016 NFL Helmet Schedule Spreadsheet

Available to download now is the 2016 NFL helmet schedule spreadsheet. You’ll see a comprehensive breakdown of every NFL pro football team's 2016 season schedule with an image of each team’s helmet design. The NFL helmet schedule is printable too. You can save the spreadsheet as an PDF file or print it out and pin up in your cubicle at work. 

2016 nfl helmet schedule spreadsheet
The Cleveland Cavaliers lifted the Cleveland curse by bringing a championship home to Believeland. Can the Browns follow it up with a Super Bowl? They've already set the date for the championship parade! There are currently four teams that have never played in a Super Bowl - can you name them all? Cleveland is one.

Stay tuned to Excel Spreadsheets Help as I'll be posting the 2016 NCAA college football helmet schedule soon.

How can I improve this spreadsheet into something you’ll use all the time during pro-football season? Maybe in the future I should also include the option to see different styles of helmets too, like these.Or maybe the Disney-inspired team logos that this guy created. What future features would you like to see?

Wednesday, May 18, 2016

How to Create folders with hyperlink from Excel Macro

I've previously shared how to automatically create folders from an Excel spreadsheet by using a macro. I recently had a reader request an additional feature: to automatically add a hyperlink from the Excel sheet to the newly created folders. I not only added this ability to my code, I also improved the overall code some as well.

The steps to use this macro to create folders is as follows:


  1. User selects range of cells they want to turn into folders
  2. Run macro
  3. Select location where folders will be created
  4. Macro automatically creates folders and hyperlinks selected cell to newly created folder


Here is a screen capture video of the VBA in action:


You can download the exact same spreadsheet template I use in the video here.

Or you can copy and paste the code below into your own Excel file. I use comments to explain what us happening on each line of code.

Sub Create_Folders()
'remember to select the cells you want to turn into folders before running the macro

'Default location where to select folder
Dim OpenAt As String
OpenAt = "My computer:\"

'Dialog box to select folder creation location
Set ShellApp = CreateObject("Shell.Application").BrowseForFolder(0, "Please Choose The Folder For This Project", 0, OpenAt)

'Set the folder to that selected. (On error in case cancelled)
On Error Resume Next
BrowseForFolder = ShellApp.Self.Path

'get the range of cells that were selected before the macro was run
Dim Rng As Range
Dim maxRows, maxCols, r, c As Integer
Set Rng = Selection
maxRows = Rng.Rows.Count
maxCols = Rng.Columns.Count

'---loop through all cells within selected range---
For c = 1 To maxCols
   r = 1
   Do While r <= maxRows
  
       'if the selected cell does not contain nothing, then create a folder
       If Rng(r, c) <> "" Then

           'create hyperlink in Excel file to newly created folder
           Dim cnf
           Set cnf = CreateObject("Scripting.FileSystemObject")

               'If folder already exists in this location, then just create hyperlink in Excel

               If (cnf.FolderExists(BrowseForFolder & "\" & Rng(r, c))) Then
       
               'MsgBox "folder does  already exist"
              ActiveSheet.Hyperlinks.Add Anchor:=Rng(r, c), Address:=BrowseForFolder & "\" & Rng(r, c)
   
               'if folder does not previously exist, then we need to create it and add hyperlink
               Else
               'MsgBox "need to create folder"
               cnf.CreateFolder (BrowseForFolder & "\" & Rng(r, c))
              ActiveSheet.Hyperlinks.Add Anchor:=Rng(r, c), Address:=BrowseForFolder & "\" & Rng(r, c)
               End If

           On Error Resume Next
   
       'if the selected cell contains nothing, then do nothing and go to the next cell
       End If
      
       r = r + 1
       Loop
   Next c
End Sub

Follow my Excel Help playlist for more how to videos in the future.