Wednesday, August 19, 2015

NCAA 2015 College Football Helmet Schedule

It's hard to believe but the 2015 college football season is about to begin in less than two weeks! My Buckeyes are the defending National Champions! I never would have expected that. In fact, I said this after their second game of last season:
Oops. I’m glad I was so wrong. And that’s why I love watching football – it’s unpredictable!


This 2015 college football helmet schedule in Excel includes every team from all ten 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! Download it today using the link below:

Monday, August 3, 2015

When to use macros in Excel?

Automating tasks and process in Excel with macros can be a great way to save time and improve efficiency. But not every task requires a lengthy macro code to be written, tested, debugged, and rolled out to the entire team. There is a good time and a bad time to use macros in your everyday job. You don’t want to waste time writing a program if it is never going to be used, or if there is a simple non-macro solution. Sometimes using macros can be a little overkill, you know, like fishing with dynamite.

How do you know when you should write a macro to solve a problem and when you shouldn’t? Listed below are the cases when it is a GOOD time to implement an Excel macro solution.


  • The most obvious situation to use Excel macros is to replace manual, repetitive tasks. If you find yourself doing something over and over, like copy-paste-copy-paste, you should definitely be automating that task. 
  • If you ever think to yourself “there has to be a better way to do this” then that is a good clue that VBA macros may be the way to go. 
  • Along the same line of thought is reducing the time to manage large numbers of spreadsheets. You can do batch processing with Excel macros, like converting hundreds of Excel files into PDFs, exporting data to Word or PPT, taking screenshots, or combining Excel files.
  •  Macros are useful when there are large numbers of workbook users who need to reuse variants of the same spreadsheets repetitively. Instead of using a template, sometimes it makes more sense to have a macro that builds the foundations of a new workbook on the fly. 
  •  A macro is a good solution if there are tasks that would be practically impossible to do manually. 
  • You should use a macro if there are some tasks which you want to be very sure the user will not miss, such as a series of steps that must be executed in a certain order. Macros can be a form of quality control and are a great way to eliminate human errors. 
  • Automated solutions can enhance the productivity of non-skilled Excel users. If the projects are just taking too long, maybe due to lack of proper training, you can eliminate some of that headache by using macros. 


As you can see, there are countless tasks and processes than can be made more efficient by using macros within your Excel spreadsheets. You can find some of the macros I've written on the spreadsheet downloads page.

Tuesday, July 28, 2015

2015 NFL Helmet Schedule Spreadsheet

The 2015 NFL season is just around the corner! The inaugural Hall of Fame game will take place in Canton, Ohio on Sunday, August 9th. Four weeks of preseason games will follow before the start of the regular season on Thursday, September 10th.  For the complete 2015 NFL schedule, download my free spreadsheet that includes all NFL helmets from every team. It’s a fun way to look at your favorite team’s opponents.

Even though basing a team's supposed “ease of schedule” on the previous year's record is a faulty premise, it's still fun to do and happens all the time. Once again, I’m not holding out much hope for my Cleveland Browns. They still haven’t found their franchise quarterback. I think the NFL season is even harder to predict than college football. Teams at the bottom one year can go right to the top the next. That’s why we love it!

2015 nfl helmet schedule excel


As for the actual spreadsheet itself, it’s very simple at this point, and proves not every Excel file has to have macros or conditional formatting. There are no major formulas or tricks, just images of each of the football helmets. In the future, I am planning on linking the helmets to each team which will make updating the schedule for next year much easier. And I’m always open to suggestions for improvement.

Download the football helmet schedule using the link below.


Yes, it takes quite a long time to assemble all the NFL helmet logos each and every season but it’s a fun way to look at the schedule and all NFL helmets at the same time. Luckily, the NFL doesn't see as much change year to year as college football does with their constantly changing conferences.


In the comments below let me know what you think about your favorite team’s chances this year!

Wednesday, May 27, 2015

How to send emails with custom subject lines in Excel

If you type an email address into Excel and press enter, it automatically becomes a hyperlink. If you click the link, it will create a new email in your default email client with the To: line already filled in. The subject line, however, will be empty.

excel email hyperlink


So how do you automatically fill in the email subject line from Excel? One way to populate the subject line is by using the HYPERLINK formula. Follow this example:

In column A, place all of your email contacts. In column B, place all of your subject lines. Your email subject lines can all be the same or they can each be unique. In column C, we’ll place the following HYPERLINK formula:

 =HYPERLINK(CONCATENATE("mailto:",A1,"?subject="B1)) 


Now, when you click the hyperlink created by this formula it will create a new email addressed to the recipient you listed in column A, and will feature the subject line you listed in column B. If you want to add email hyperlinks with constant subjects this is the way to go.



To CC someone, add this to your formula: “&cc=”, A2 where A2 is the cell location of the recipient’s email address.

To take it a step further, we can also add text in the body of the email by expanding our Excel formula. In column C, place the text you want to appear in email’s body and add the hyperlink formula to column D:

=hyperlink(concatenate("mailto":,a2,"?subject=",B2,"&body=",C2)) 





We can take it even further by including the email recipient’s name and adding our signature. Make it look nice by adding some formatting. To add a line break to your email’s body text, use: %0D%0A. You can add this to the formula or place it in a reference cell. So, to compose a complete email in Excel, including recipient, subject line, and body text with line breaks, use the following formula:



This really is the ultimate formula for sending emails from Excel.


To add an attachment it seems like you should simply be able to add “&attachments=”,C2 but in Outlook2013 I get the error message “Cannot start Microsoft Outlook. The command line argument is not valid. Verify the switch you are using.” Anyone know how to fix this issue? In the meantime, the best bet to add an attachment may be to use a VBA macro, as demonstrated here.

 It is possible to manually manage an email list from Excel. But with all the automated tools out there today, why would you? If you have a good reason to I’d love to hear about it. Maybe it’s because someone does not want to pay for an email subscription/list building service? An email list building and sending tool like Aweber is only around $30/month (and what I use to send out my free email tips). In conclusion, you can manually manage email lists with Excel, but automated services like Aweber make it so much easier.

Monday, April 6, 2015

How to input military time into Excel

I have to admit, the title of this post is a little bit misleading because there is no direct way to input military time into Excel. If you try to input "1300" as 1:00PM, Excel has no way to know that you're entering a time as opposed to a number. In order for Excel to recognize your input as a time and not 1,300 you must enter a colon and enter as "13:00". Basically, formatting the cells for dates and times only affects how the contents of the cell is displayed and not how the information is entered. So if you enter the military time with a colon and format the cell as Time then 1300 will be converted to 1:00PM.

Here's why: Excel stores dates and times as days and fractions of a day, where the number 1 equals January 1st, 1990. Entering 1300 into a cell leads Excel to interpret that as the 1300th day since January 1st, 1900 at 12 midnight.  Enter 1300 into a cell and format it as Time. Notice the value displays this: 7/23/1903  12:00:00 AM. Interesting, no?

how to convert military time in excel

Going back to the original question, how to input military time into Excel, my suggestion is this: entering a colon while inputting the military time is extra work. We always want to make data entry fast and painless as possible. So let's use a formula that will enable us to enter military time as 1300 but then display the regular time.  If we input the value of 1300 in cell A1, enter the following formula into cell B1: 

=time(int(a1/100),mod(a1,100),0)


In the picture above, you can see the how the military time is entered and the formula converts it to standard time. This post was inspired by a question from a reader on our Excel Spreadsheets Help Facebook page.




Monday, March 16, 2015

11 Excel Lessons from the Best March Madness Brackets


I preach this lesson all the time around here, I know, but you can seriously learn so much about Excel simply by examining templates and other professional's spreadsheets. The 2015 March Madness brackets are no exception. This downloadable template for the 2015 NCAA basketball tournament is an outstanding example of how to harness the full power of Excel and it doesn't involve finances, inventory, tracking, engineering, or charts.

2015 march madness bracket excel

B. David Tyler’s NCAA Excel brackets are the best I've seen, and I've been using his brackets since at least 2010. 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. First, download the files here:

http://www.whistleblows.com/2015/03/ncaa-2015-excel-bracket-and-pool.html

Now, open the files and start picking them apart! Listed below are 11 Excel lessons that can be learned from digging into these two templates:


1. Formatting - The bracket sheets are nice and clean and easy to use. Why is that? Because there isn't a lot of flare or unnecessary stuff to distract you. Only a few colors are used, grid lines are turned off, all the font sizes and colors make sense, etc. Less is more.

2. Hidden sheets - When you first open the NCAA bracket you only see two sheets: instructions and the bracket. But if you right click on one of the sheet tabs and click unhide you’ll see there are some hidden sheets, and it’s these sheets that do a lot of the heavy lifting. The beauty of the brackets is the fact you don't have to modify a single formula yourself, everything has been done for you.


3. Protection - When other users are going to be using your spreadsheet you may want to use protection to protect key cells. Fortunately, David did not use a password on the protection, so you can unprotect the sheets to see what all the exact formulas are.

4. Conditional Formatting - Conditional formatting is where you set up rules to change the format of a cell based on a condition. When you get a pick in the bracket incorrect you’ll notice the font turns red with a strikethrough. This is done through conditional formatting. To see how it works, go to the Home tab, click on Conditional Formatting, then Manage Rules. Show formatting rules for: Sheet: Bracket then scroll down and see all the rules.


5. Organization - The 2015 NCAA bracket shows you how to structure a spreadsheet that is potentially going to be used by thousands of strangers - everything is clearly labeled, instructions are included, and there’s information about where to go if you need help.

6. Drop Down Lists - After you make a few selections, you may get second thoughts and decide to go back and change your picks. To do so, you’ll change the winning team by picking from a drop down list. To see how the drop down list works, go to the Data tab, then Data Validation.You’ll see the setting is List and uses a Defined Name. Go to Formulas tab then Name manager to see all the defined names.

7. Error Checking - Are there little green triangles on the cells that are annoying you? To remove them, go to File>Options>Formulas>Error Checking and uncheck the “Enable background error checking” box. There is also a macro in the bracket file used to check for common errors.


8. Macro: HTML Export File - One of the macros included in the pool manager file shows you how you can export an Excel sheet to a HTML file (called exportLeaderBoard). Open the Excel document and press Alt-F11. This will open up the Visual Basic editor, and by clicking on sheet and module names on the left side of the screen you will be able to view all the code.

9. Macro: Import Multiple Excel sheets - One of the best features of the bracket manager file is the ability to automatically import multiple brackets into the manager all at once. This is done via an Excel macro. All the work is done for the user, as the manager of the pool I simply have to place brackets into a folder then press a button. The user’s of your spreadsheets will really appreciate it if you make everything easy for them.

10. Macro: Hyperlinks - Another nifty feature of the basketball brackets is the ability to simply click on a team to advance them. This is accomplished with a very cool hyperlink macro.  The code is run every time a hyperlink is clicked and it checks to see if a game cell is selected, and if so, it advances the team that was selected.

11. Spreadsheets can be fun! - For many, the mention of Microsoft Excel brings up nightmares of pie charts, pivot tables, and data entry. But using Excel can be fun, especially when you’re competing in an office pool. I really like the feature in the manager file that let’s you run scenarios: what if this team wins, and this teams loses, what are my chances of winning?

I know some readers of this blog skip over the templates I post, especially if they're sports related. but there really are many lessons that can be learned by examining them that you can apply to your own spreadsheets to improve them. Special thanks to David Tyler for continuing to update and post his excellent brackets each and every March. What new lessons about Excel have you learned by breaking down a template?

Monday, March 9, 2015

8 Simple Rules to Make Your Spreadsheets Look Better

A little bit of formatting can turn an average looking spreadsheet into a great looking spreadsheet. Here are 8 simple rules to make your spreadsheets look better:

  1. Use no more than two different font types.
  2. Name your spreadsheet. Give your spreadsheet a descriptive name as well as naming all the individual sheets, tables, charts, etc.
  3. Use no more than three to five different fill colors. I typically use white or a light grey for my background colors and a few lighter colors for accents.
  4. Don't use 3D charts. Charts are a great addition to any spreadsheet but make sure they are of the 2D variety rather than the 3D option.
  5. Turn off gridlines. To turn off the gridline, go to the View tab and uncheck the box next to gridlines.
  6. Bold your headers (but don't get too crazy).
  7. Create space. Let your spreadsheet breathe. Don’t be afraid to leave a completely empty column or row in between your data sets.
  8.  Less is more. A general rule when it comes to formatting is less more. It's super easy to get carried away so remember to use some restraint.
There you have it! You can see examples by downloading my free spreadsheet templates. I hope these tips help improve the look of your spreadsheets.