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?

Wednesday, March 16, 2016

March Madness 2016 Excel Brackets

It’s the most wonderful time of the year! March Madness 2016 edition is here and the first of the four “play-in” games have already begun (which we’re not counting off any points for in my office pool). Once again, I’ll be using David Tyler’s NCAA Excel brackets, the same ones I’ve been using since at least 2010, as they're still the best as far as I’m concerned. David doesn't lock or hide anything behind password protection so you can examine all the formulas to see how they work and make any modifications you desire. There are two files: the bracket manager and the individual bracket file, that can be downloaded by following the link below.

A great way to increase your Excel knowledge is to reverse engineer templates, like these office pool March Madness brackets. Last year, I shared 11 things you can learn from the best Excel brackets and they all still apply for this year’s version.

I'd like to give a special thanks to David Tyler for continuing to update and post his excellent brackets each and every March and giving us something to look forward to as we crawl out of the final few weeks of winter.

I also created a short screen capture video to show you just how easy it is to use David's brackets. Simply click on each team to advance them (don't forget to enable macros).

Pictured below is my bracket. You should probably just go ahead and treat it like the Bible, Koran, and Torah all rolled into one. It’s as good as truth.

Good luck in your office pools!


*just a special note to my email subscribers: I will hopefully have an option soon where you can opt out of notifications about sports templates, that is if you only want to read about my general Excel tips and tricks.

Sunday, February 14, 2016

2016 NASCAR Fantasy League Manager Spreadsheet

2016 is the third year in a row that I’ve made the NASCAR Fantasy League spreadsheet available for download. This NASCAR fantasy game is based on Total Driver Points according to NASCAR’S scoring system. In order for your drivers to score they must be running for Sprint Cup Points. Drivers may run in more than one NASCAR division but can only score points in one division.

How to Play NASCAR Fantasy in Excel

At the beginning of the year (first race is Daytona on February 21st) participants choose ten race car drivers. These will be their 10 drivers for the entire year - no changing after the entry deadline. All players also submit the three drivers that they think will place 1st, 2nd, and 3rd in the NASCAR standings at the end of the year. They don’t need to have these drivers in their picks, just who they think is going to finish on top. This is used as a tie-breaker.

The fantasy league manager manually enters each player’s picks into the spreadsheet at the beginning of the year (or uses the new entry form to copy and paste entries). Players keep the same drivers all year long. After each race, the manager then goes to and enters the amount of points that each driver earned during the race into that driver’s column. The spreadsheet then automatically adds and ranks each player according to their driver’s scores. I used many of the same formulas found in the leaderboard of my NCAA Bowl Prediction Pool sheet. This Nascar fantasy manager template can currently handle up to one hundred drivers and one hundred participants without needing to modify a single formula!

This year, there are two options to download the 2016 NASCAR Fantasy League manager:
Download Option 1 (no info require):

Simply click the link below which will take you to the, where the file is hosted. Next, click on the “Download” link in the upper right hand corner. That’s it! No email address is required.

Single Player Entry Form.xls

Download option 2 (email address required):

File is hosted on Gumroad. Click "I Want this" then submit your email address to download the spreadsheet. Your email address will ONLY be used to alert you if I upload a new version of the file, either an update for this year or you will also be notified when next year's sheet is ready to go.

Why two links? Sometimes users find small bugs or offer suggestions for improvement and so I update the spreadsheet. I recommend downloading through download link 2 so you will be automatically notified when an update is made, whether for this year’s spreadsheet or next. But if you don’t feel comfortable submitting your email address I understand, which is why download option 1 is available.

I’ve received comments from many readers informing me they like to use these types of sport manager templates to raise money for their favorite charities, which is so awesome! In the future. I will improve the spreadsheet by automating the entry process (I know, been saying that for two years now) and come up with a way to update the driver scores easier. This is just the third iteration of the template and I don’t follow NASCAR myself like I do football, so I’m sure it can be improved in some areas. Please let me know if you have any suggestions!

Tuesday, February 9, 2016

Excel Help Milestone: 1.5 Million Page Views!

In my 7th year of blogging, I’ve reached another monumental milestone. In April of 2014, I surpassed one million page views. Today, I am excited to announce Excel Spreadsheets Help has surpassed the 1.5 MILLION page view mark. Yes, I realize there are larger websites out there that may have 1.5 million views in a single day, but those are larger brands with an entire team or company of people. This Excel help blog is run by one person in his free time using Google’s free Blogspot blogging platform, so to me it’s quite the accomplishment. And it’s hard to wrap my mind around 1.5 million of anything. That’s like 15 Ohio Stadiums full of scarlet clad football fans. Wow!

Now, let’s take a look at some fun numbers and statistics from seven years of sharing Excel tips and templates:
  • Excel Spreadsheets Help Age: 7 years
  • Total number of posts published: 248 (249 after this one goes live)
  • Time to get to 1 million page views: 53 months (2009 - 2014)
  • Time to go from 1 million to 1.5 million views: 21.5 months (2014-2016)
  • Total time to hit 1.5 million: 74.5 months
  • Estimated timeframe to hit 2 million page views: November 2017
  • Average number of posts per month: 3
  • 1.5 million views / 74.5 months = 20,134 average pageviews per month
  • 1.5 million views / 248 posts = 6,048 average pageviews per post

The top five most popular posts since December of 2009 are listed below and interestingly, all top five posts were written in 2010: