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.

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.

https://app.box.com/s/2uuylw7vm91ymtjxqda97urr973erlqe

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!

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.

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.

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

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.

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.

Excel Spreadsheets Help Turns Five Years Old

This post is a little bit late, but if you hadn't noticed Excel Spreadsheets Help recently hit another milestone - our 5 Year Blog-iversay! Yes, it's hard to believe but this blog was launched over five years ago, on December 6th, 2009. In fact, check out our very first post here. In those five years we've had several highlights, one of which was reaching over one million pageviews! While the number of total posts has declined every year, I think the quality of the posts has improved (do you agree?). Here's to another five years!

The Top Five Posts from the Last Five Years:

1. How do I Insert the Degree Symbol
2. How do you flip or reverse a column
3. Project Management Downloads
4. How to make horizontal rows into vertical columns
5. How to create collapsible rows in Excel

Year in Review 2014 and 2015 Goals

In 2014, I created some new spreadsheet templates and upgraded some old ones. Many readers were asking for more tips though, and I did my best to deliver with posts such as how to make Excel drop down arrows visible, and how to align charts. I created a "how to" Index to track all my Excel tips. In 2015, I want to add even more tips and continue to improve my templates. What would you like to see more of on this blog?

What did you accomplish in 2014? What are your Excel spreadsheet goals for 2015? How can I help you achieve your goals?

Printable 2015 NFL Playoff Bracket

The 2014-2015 NFL Playoffs are set to begin on Saturday, January 3rd a week after the end of the crazy 2014 regular season. Two wildcard games will be played Saturday, January 3 and two games will be played Sunday, January 4. Second round games will be played Saturday and Sunday, January 10 and 11. Third round games will be played Sunday, January 17. The NFL SuperBowl will be played Sunday, February 1st. 

I've once again created a printable 2015 NFL playoff bracket with team helmets that you can download for free to use in your office pools. The spreadsheet also includes the complete 2015 NFL playoff schedule including game times, dates, and TV stations. Download the file by clicking the link below (file is hosted on Box.com): 

2015 NFL Playoff Bracket.xls download

As a Browns fan, it hards to see the three other teams in our division make it to the playoffs while we miss out again! And we still don't have the answer at quarterback. Sigh... So who are you cheering for?

If you enjoy this spreadsheet, please use the share buttons to send to your family, friends, and coworkers. Also, be sure to check back after the playoffs are over and I’ll be posting my Superbowl squares spreadsheet.

2014 NCAA Bowl Prediction Pool with Playoff Bracket

The 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 addition of a four team playoff to determine the national champion.

Features and upgrades over the previous college football bowl pool manager spreadsheet include:

• Easy method to make each bowl game worth a different point value
• Updated leaderboard tab and stats
• Separate entry sheet to pass out to participants/co-worker
• Complete NCAA college football bowl schedule with game times and TV station
• The bowl prediction sheets include the football helmet designs for every team (taken from the 2014 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 Fact: Fresno State is the only team with a losing record (6-7) while Florida State has the best record at 13-0.

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 pionsettia bowl) I use the excel shortcut alt+enter to add text to the next line (shortcut post link)

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!

2014 College Football Bowl Pool Manager.xlsm download
2014 College Football Bowl Prediction Entry Form.xls download

Please let me know if you have any questions, comments, find any bugs, or have any suggestions for improvement.  What team are you rooting for?