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.

Wednesday, February 4, 2015

2015 NASCAR Fantasy League Manager Spreadsheet

This is the second year I’ve made a NASCAR Fantasy League spreadsheet available for download. I’ve created Excel templates for many other games and sports, including one for horseshoes, but this is our second NASCAR spreadsheet! 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.


Here's how to play: At the beginning of the year (first race is Daytona on February 14th) 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 once at the beginning of the year. They keep the same drivers all year long. After each race, the manager then goes to ESPN.com 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. Can currently handle up to one hundred drivers and one hundred participants without needing to modify a single formula.

There are two options to download the 2015 NASCAR Fantasy League manager:

Download Option 1:


Simply click the link below which will take you to the box.com, 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.

Download option 2:
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 file.
https://gum.co/nascarmanager

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 templates to raise money for charities, which is so awesome! In the future. I will improve the spreadsheet by automating the entry process and come up with a way to update the driver scores easier. This is just the second version of the template and I don’t follow NASCAR myself so I’m sure it can be improved in some areas. Please let me know if you have any suggestions!

Sunday, January 18, 2015

2015 Super Bowl Squares

The NFL playoffs are over and the final two teams are set to go at it in the championship game, meaning it’s time to download the 2015 Super Bowl Squares template! The Seattle Seahawks will play the New England Patriots in Super Bowl XLIX (49) on Sunday, February 1st at 6:30pm. To make the big game even more fun start an office pool using our printable Super Bowl square spreadsheet. Fill in the squares, watch the game, laugh at the commercials, and see who wins and maybe make a little money at the same time. If you’re a fan of a team that like mine (the Factory of Sadness) that will never make it to the championship (or playoffs for that matter), then playing Super Bowl Squares will make watching the game more exciting and gives you something to actually root for. Others may be torn between cheering for their favorite team and cheering for their squares.

2015 super bowl squares spreadsheet template


This year’s Super Bowl Squares spreadsheet includes multiple versions - you pick the way you want to play:
  1. Printable version - This print ready version contains a blank grid, simply print it off and write all the player’s names down on the piece of paper. It’s quick and easy!
  2. Electronic version - Manage everything directly in Excel. Press the randomize button to generate the random score numbers. Use this for running your Super Bowl pool. No modifications are necessary, though you can if you so desire.

2015 super bowl squares printable excel


Super Bowl Squares Rules & How to Play
Listed below are the instructions on how to play Super Bowl Squares (which are also included within the spreadsheet, along with an example):
  1. Participants “purchase” individuals squares by writing their name in their desired squares, until all one hundred are filled.
  2. After all squares are taken, the numbers 0 through 9 are written in the empty horizontal and vertical rows in random order (draw the numbers out of a hat).
  3. After the end of each quarter of the game, match the last digit of each team's score to the corresponding square to find the winner. Tip: 0, 3, and 7 are the best numbers to get. 8, not so much.

Below is a short video tutorial I put together of how to use my Super Bowl Squares template in Excel



Download the spreadsheet for free, simply click the link below which will take you to the box.com, where the file is hosted. Next, click on the “Download” link in the upper right hand corner.That’s it!


2015 Super Bowl Squares.xlsm download


Let me know in the comments or by email which version you’ll use: paper or electronic!

Wednesday, January 7, 2015

How to import data from one Excel spreadsheet to another using VBA

It can be very helpful knowing how to import data from one Excel spreadsheet to another using VBA. In fact, one of the new features I added to my College Football Bowl Prediction spreadsheet is the ability to import a user’s picks from a separate entry form into the pool manager’s tracking sheet automatically. I did this by inserting a button in the manager’s file that is linked to a VBA macro. If you're brand new to programming macros in Excel, please see my getting started guide here.

How to import data from one Excel spreadsheet to another using VBA

The import spreadsheet VBA code is below. The comments colored green explain what is going on. This code is pretty quick and dirty - no real error handling has been inserted to deal with unexpected situations, something that should be added.

'this code imports a single player's picks into the pool manager
'player's picks must be saved as .xls file
'==============================================================
Sub Import_Single_Player_Data()
' Get player's workbook...
Dim customerBook As Workbook
Dim filter As String
Dim caption As String
Dim customerFilename As String
Dim customerWorkbook As Workbook
Dim targetWorkbook As Workbook
' make weak assumption that active workbook is the target
Set targetWorkbook = Application.ActiveWorkbook
' get the customer workbook
filter = " *.xls,*.xls"
caption = "Please Select an input file "
customerFilename = Application.GetOpenFilename(filter, , caption)
Set customerWorkbook = Application.Workbooks.Open(customerFilename)
'define the target sheets in order to copy data from customer to target workbook
Dim targetSheet As Worksheet
Set targetSheet = targetWorkbook.Worksheets(2)
Dim sourceSheet As Worksheet
Set sourceSheet = customerWorkbook.Worksheets(1)
'select and copy column L from the new player's spreadsheet
sourceSheet.Columns("L:L").Select
Selection.Copy
'active the pool manager spreadsheet
targetSheet.Activate
'select location where the copied column will be inserted
Columns("L:L").Select
'insert the new column and shift all others to the right
Selection.Insert Shift:=xlToRight
 
' Close customer workbook
customerWorkbook.Close
End Sub

See how this import spreadsheet macro works by downloading the bowl pool manager template. As I’ve stated earlier, using my completed templates is a great way to figure out how to do things that can be applied to other tasks.

By the way, I copied the code into this blog post with formatting by using Notepad++, one of the programming tools I use everyday.