Wednesday, December 30, 2015

Top 5 Excel Help Posts From 2015


2015 is coming to a close so it’s that time of year to sit back and reflect about what transpired this past year and what is to come. Listed below are the top five most popular Excel tips or how-to guides I posted in 2015.

On December 6th, Excel Spreadsheets Help turned six years old! Sometime in the first quarter of 2016 I expect this little blog to surpass the 1.5 million page view mark, no small accomplishment. Sadly, I posted fewer articles in 2015 than any other year, so my goal for 2016 is to at least double the number of tips I wrote this past year.

What did you accomplish in 2015? What are your Excel spreadsheet goals for 2016? How can I help you achieve your goals? What would you like to see more of in 2016? Anything specific?

Have a great holiday season, thanks for your continued support. See you in 2016!

Sunday, December 6, 2015

2015 NCAA Bowl Prediction Pool with Playoff Bracket

The 2015 NCAA college football bowl season is here again which means it’s time to make your picks and predictions about who you think will win each 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 second year of a four team playoff to determine the national champion.

 
2015 college football bowl pool manager and schedule excel

Features and upgrades for the 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
  • Updated leaderboard tab with new stats
  • Separate entry sheet to pass out to participants/co-workers
  • Complete NCAA college football bowl schedule with game times and TV stations
  • New stat sheet to track each conference's record during bowl season
  • The bowl prediction sheets include the football helmet designs for every team (taken from the 2015 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!

Fun Facts: Last year, Fresno State was the only team with a losing record (6-7) while Florida State had the best record at 13-0. This year, there are three teams with identical losing records of 5-7: Minnesota, Nebraska, and San Jose State.

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

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 (not yet ready to use, check back soon)
I will explain how it works in a later post. But if you were wondering about the nuts and bolts, my spreadsheet uses the rank without ties formula: =RANK(num, ref) + COUNTIF(range,num)-1. in column A, for bowl games with really long names (I’m looking at you San Diego County Credit union Poinsettia Bowl) I use the excel shortcut alt+enter to add text to the next line.

To download the college bowl pool spreadsheets 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 College Football Bowl Pool Manager.xlsm download
2015 College Football Bowl Prediction Entry Form.xls download


Here's a quick screen capture video to show you quickly how the spreadsheet works and how to insert new player columns:


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?

Monday, November 23, 2015

2015 Holiday Gift Guide for Excel Users

The 2015 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!

EXCEL BOOKS

For the first 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



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.


OTHER BOOKS WORTH READING

Another shameless self-plug. For the first time ever, I am giving a discount on my paperback book Coasters 101: An Engineer’s Guide to Roller Coaster Design. Ever wonder how roller coasters are made so you scream your head off but return safely to the station every time? For 25% off, enter discount code: UY8WJQMM when purchasing directly from: https://www.createspace.com/3737443


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 (haven’t seen the movie yet). 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.

Journey to Star Wars: The Force Awakens – Lost Stars by Claudia Gray. If you’re a Star Wars fan like I am you’re probably anxiously awaiting The Force Awakens movie on December 18th. This book will wet your appetite before the movie comes out. The story takes place during the original trilogy but offers a new perspective in which the main characters don’t really know anything about The Force, the Jedi, the Dark Side, etc. which I think is a theme that will carry over into The Force Awakens. The story also ends about a year or two after The Battle of Endor in Return of the Jedi and does contain the Battle of Jakku and how at least one of those Star Destroyers ends up smashed on the desert planet. If you’re only going to read one Star Wars related book, this is the one.

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!

TOOLS FOR WORK

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.

Chromecast – I actually have used Chromecast at my day job before. I’ve created screen capture videos and uploaded them to YouTube, then I use the very portable Chromecast to display the videos directly on a TV (haven’t used the Amazon equivalent, so not sure if it works the same).

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.

Excel Quick Reference Sheet - Laminated quick reference showing step-by-step instructions and shortcuts for how to use Microsoft Office Excel 2013.

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.

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.


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?

Tuesday, October 20, 2015

RACI Matrix Template in Excel

The RACI Matrix is a powerful tool to assist in the identification of roles and assigning of cross-functional responsibilities to a project deliverable or activity. The RACI  or RASCI (pronounced ‘race ski’) matrix is a responsibility assignment matrix (RAM) to clarify expectations on the level of their participation. To begin using the RACI template, follow these steps:

raci matrix excel template

1. Across the top row, identify who will be the project’s participants.
2. Down the first column, determine the functions, decisions, tasks and activities that will make up the process or project.
3. Simply place an R, A, C, I or any appropriate combination in each of the applicable roles for each activity. Each activity should have at least one individual accountable while there may be shared responsibilities depending on the activity.

What does RACI (or RASCI) stand for?

  • Responsibility = person or role responsible for ensuring that the item is completed
  • Accountable = person or role responsible for actually doing or completing the item 
  • Consulted = person or role whose subject matter expertise is required in order to complete the item
  • Informed = person or role that needs to be kept informed of the status of item completion 
  • Supported = the roles/groups/departments that provide the resources and hence support that task 



Have you ever used a RACI or RASCI matrix at your job or project?

Wednesday, October 14, 2015

Schedule Meeting Time Template

Having trouble finding the best time to meet with your team? Use this Excel template to find the perfect date or time for your meeting. Here’s how to use the meeting scheduler template: Enter your name in the input field, then use the drop down menu to add a check mark into the time slots you are available. If you’re not available, then leave it blank. When all the required meeting attendees enter their available times, the spreadsheet shows you the first and second best meeting times.

meeting scheduler in excel spreadsheet

One thing this template will demonstrate is how to insert a check mark in Excel. Go to the top tab "Insert" then in click the "Symbol" button on the far right. A dialog box will appear and you need to select "Wingdings" from the drop down list at the top left. Scroll through the symbols until you find the check mark (wingdings: 252). If you copy and paste the check into a new sheet you may see a ü symbol instead. Simply change the font of that cell to wingdings to get the check mark back.

check mark excel

The next thing you’ll see by dissecting this template will show you how to use a check mark in a drop down list. Another function you can examine is how to use conditional formatting to color a cell based on the cell’s value. In this case, we want to color our cell green if it contains a check mark and red if left blank. As you can see, you will use the "ü" symbol in the formula.



There are similar online tools to help you schedule meetings but most require a fee to unlock all the features. Instead you can use and modify this free Excel template. You could add more functionality, like adding a formula to automatically send an email once you’ve picked the meeting time.

Do you think this template will be useful to you?

Wednesday, October 7, 2015

Excel Quick Tips: Count Unique Values

I’ve got an extremely short but valuable Excel tip for you today: how to count the number of unique values in a range. Sometimes you need to count values in a row or column but not if they repeat. To count only the unique numbers or words in Excel, use the following formulas depending on:

If there are no blank cells:

=SUMPRODUCT(1/COUNTIF(Range, Range))

With or without blank cells:

=SUMPRODUCT((Range<>"")/COUNTIF(Range,Range&""))

Replace Range with A1:A7 for example. 


COUNTIF is probably the function you’re most used to that will count based on a given condition. SUMPRODUCT returns the sum of the product in the range.  SUMPRODUCT functions as an array formula, you just don't have to enter it as such. 

To see how this formula works step by step, click on the cell that contains the formula, then go to Formula tab, and click Evaluate Formula and you can cycle through each step in the calculation.




For more, see the index of Excel tips page.

Thursday, September 17, 2015

Quick Excel Help: Date and Time Formats

I received an email from one of my readers asking a question about date and time formats in Excel and I figured I would share my answer here in case anyone else has the same question.

Question: Hi Nick, I am having trouble with Excel time date formats and was hoping you can help. I have a data field with a date time format and I want to separate this out with date in one column, time in another, & hour of the day  in another. Can you please tell me how to do this?

Answer: There are a number of date and time functions in Excel that should be helpful to you. I’m not sure if this is exactly what you’re looking for but try these formulas out:

In column a: =now()
Column b: =month(a2)&”/”&DAY(A2)&”/”&YEAR(A2)
Column c: =HOUR(A2)
Column d: =minute(A2)
Column e: =Second(A2)
Column F: =TIME(C2,D2,E2)

excel date and time formulas


Do you have any other suggestions for this reader’s question?

Tuesday, September 8, 2015

Gantt Chart Template Excel 2010

A Gantt chart template is included in my Master Project Management Template file that I give away as a free gift for joining my email list. I have a new and improved version that is a bit more user friendly and simply better to look at.
gantt chart template excel 2010

A Gantt chart is a bar chart that shows the tasks of a project, when each must take place, and how long each will take. As the project progresses, bars are shaded to show which tasks have been completed. People assigned to each task can also be represented. Gantt Charts are also called a Milestone Chart, Project Bar Chart, or Activity Chart.

How to Use the Gantt Chart Template

Follow the steps listed below to use the template spreadsheet:

1. Enter up to 30 tasks in the cells provided.
2. Enter the Start Date for each task and the number of days to complete it.
3. Enter the Percent Complete for each task. This is required, even if 0%.

Watch the video below to see the Gantt Chart Template Excel 2010 in action.



When to Use Gantt Chart

The list below shows the ideal time to use a Gantt chart:
  • When scheduling and monitoring tasks within a project.
  • When communicating plans or status of a project.
  • When the steps of the project or process, their sequence and their duration are known.
  • When it’s not necessary to show which tasks depend on completion of previous tasks.
This chart is very easy to use and can be quite helpful when managing a large project. Download the template by clicking the link below:


How often do you use Gantt Charts? I’d love hear about your experience so please let me know in the comments below.

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.

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!


NEW: 2016 Super Bowl Squares Here