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 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 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
               '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
   Next c
End Sub

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

Monday, May 2, 2016

How To Get The Best Out of The Concatenate Function

The following is a guest post from Puneet at Excel Champs:

Text functions averagely cover  10%-15% of our Excel usage on a day to day basis. Text functions help us to present a numeric value in a simple way. If you check the below example, a single sentence is describing the performance for the September month. But we can't say there is something wrong in presenting results in tabular form. It is just a matter of requirement. Excel has lots of useful functions in this Text Function family (Upper Function, Lower Function, Proper Function etc....).


A function to combine text, a function to put the text together. Yes, this is the CONCATENATE Function. A Function which exactly do the same for text as SUM Function does with numbers (Giving results in a single number).

How Does It Work?

It takes the text one by one in arguments & convert them into a single string.
Syntax: CONCATENATE(text1, text2, ...text255) 
text1 is the first item to join in concatenation which is also a required argument. 
text2 ... is the additional text which you can add. You can add up to 255 items & up to 8192 characters in a single function(for Excel 2007 & Later). Each entry must be separated by a comma.
If we concatenate two strings like "Hello" & "World", then it would be like =CONCATENATE("Hello ","World"). Now if you notice we had a space between the two strings, which is quite useful while converting them into a single string. The above example is drawn by using absolute input for arguments, you can also use cell reference to add arguments.  =CONCATENATE(A1," ",B1).

How Can It Be Useful?

It can help you at a number of times.
  • Writing a conclusion for a tabular data, which will update with the changes in your data. So that, you don't have to write it again & again.
  • Converting two or more strings into a single string. So that it can make a meaning to you.


Here we have an example where we are capturing the population of the major cities in the world in a string and function will be.


Yes, we have. But you don't have to worry.
  • Using commas to differentiate one string from another is confusing sometimes. If you skip putting a comma between the arguments, it can lead to a different result. =CONCATENATE("Excel""Champs") results in Excel"Champs. You can also put ampersand(&) instead of Comma(,).
  • The space between string is also a point to take care. You can enter space within the string "Hello " or by inserting it separately "Hello"," ".
  • If you use concatenation to join a numeric value, that value will always show in general format. That means if you have a number with 10 decimals, you will get it in the same decimal format in your string(Total growth in this month is 0.0523652). But this problem is not long lasting, you can kill it with TEXT Function & make your number just like you want.

What else to do?

Sometimes using a function like CONCATENATE is time-consuming, if you have to put it one by one different string to create a new one. You can also use a shorthand trick to get results like CONCATENATE, just ampersand(&) to join the text as do in CONCATENATE. It will give you a simple & a fast hand to join text.
Download a sample file here to see examples:
Thanks to Puneet for providing us with this explanation of concatenate. 

Thursday, March 24, 2016

2016 Presidential Candidate Comparison Spreadsheet

I'm not really too big into politics but a few readers have asked me to create a 2016 Presidential Candidate Comparison spreadsheet and here it is! This new spreadsheet serves two purposes - it allows you to see where each candidate stands on forty-eight different issues and also allows you to see how your own views align with what candidate.

1. Candidate Comparison

The first sheet in the spreadsheet is the candidate comparison that shows all five candidate's responded to forty-eight different questions on topics such as immigration, gun control, abortion, and more.

2. Which candidate should you support?

If you're still unsure of which candidate you might vote for in this election, you can use the scoring system in the candidate comparison spreadsheet to help you figure it out. Use the drop down menus to give your opinion on each of the issues - either yes, no, or undecided. Additionally, you need to decide which issues are very important to you and which ones you could care less about. Use the drop down menu to select very important, a little important, you're indifferent, or you don't care. The spreadsheet automatically uses a weighting system to score each candidate's response in relation to your opinion.

. Go to the scores sheet to see which candidate has the highest score and is therefore the most similar to you. The spreadsheet uses a "rank without ties" formula to list the candidates in order of highest to lowest score based on the weighting system.

Only 229 more days until the 2016 election! I'd love to hear what you think of this spreadsheet, if it helps you figure out your politics or not. Any suggestions to improve it?