2014 NASCAR Fantasy League Template

A reader recently asked me for help with creating a NASCAR Fantasy League spreadsheet and I’m making a template of it available for download. I’ve created Excel templates for many other games and sports, including one for horseshoes, but this is our first 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.

The sheet to track the scores for each race and driver respectively.

Here's how to play: At the beginning of the year (first race is Daytona on February 23rd) 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.

Player ranking sheet.

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.

You can make each player pay a fee to play the game and split the pot to the top three winners. (1st=70%, 2nd=20%, 3rd=10%) Or many people have told me they like to use these types of games to raise money for charities, which is awesome!

To download the 2014 NASCAR Fantasy League manager 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 NASCAR Fantasy League Template.xls download

If many readers play NASCAR fantasy games and find this useful I will improve the spreadsheet by automating the entry process and come up with a way to update the driver scores easier. This is the first 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!

Super Bowl Squares 2014 Spreadsheet

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 Super Bowl Squares template - 2014 edition! The Denver Broncos will play the Seahawks in Super Bowl XLVIII (48) on Sunday, February 2nd 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.

There are actually two versions included in this year’s Super Bowl Squares spreadsheet:

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.

Leaderboard for the Excel squares

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!

2014 Superbowl Squares.xls download

Get the 2015 Super Bowl Squares spreadsheet!

Over 2,000 people downloaded last year’s version. My goal is to surpass that this year and I need your help! I would really appreciate it, if you enjoy this sheet, to share it on your favorite social media site. Let me know in the comments or by email which version you’ll use: paper or electronic!

NEW: 2016 Super Bowl Squares

Interview with an Excel Engineer

Reddit recently featured an interview with an Excel engineer who worked on Excel 2010 and Excel 2013. He answered numerous questions about everything ranging from tips and tricks to how to get a job working for Microsoft on Excel. The post is quite lengthy and there are many unanswered questions to sort through so for your convenience I’ve transcribed some of the most interesting and and informative Excel questions and answers here:

What would you say is the most under-utilized aspect of Excel?

The most underutilized feature IMHO is the Pivottable - the power it gives you is limitless and when people figure out how to use them it blows their mind. Traditional pivottables could only be created on a single table - with the new data model feature in Excel 2013 this limitation is gone and you can now join data from multiple tables/sources.

How does one become a data visualization MVP anyway?

Usually its awarded after years of experience, blogs, books written on the subject, etc.. These guys help us a lot when it comes to planning.

What is your background? How did you get the job?

Software engineer specializes in business intelligence and financial engineering. Got recruited out of school. There is all sorts of backgrounds though from ex flash game designers to mathematics phds to a guy who graduated from vet school. There are four disciplines that work directly on the Excel teams: developers, testers, program managers, and designers. There is also a ton of supporting staff like technical writers, support engineers, etc.. Each discipline had its own requirements. We're always hiring and you can specifically look inside the Microsoft Office Division.

How much do you study actual users and who are they?

A lot. We visit customers small and large shadowing them for days understanding their workflows. We conduct usability studies where we test out new features, designs, etc.. We comb through those crash reports (those popup dialogs that ask you to send info to MSFT) all the time to figure out what problems people are hitting. That said with 250+ million active users we always have to generalize. My first boss once told me, "When developing Excel any decision you make will probably piss off at least a million people, just make sure the rest are happy".

What are some lesser known tips shortcuts or tricks of Excel? What advice would you give to someone wanting to learn more?

• When inside a range of data Ctrl + A selects it all, Ctrl +T creates a table. Learn more keyboard shortcuts here.
• Macro recording to learn VBA is probably the trick that I have gotten the most out of. Or take an online class.
• Read this book: Excel 2010 Bible by John Wallenbach

To read through the entire Q&A session visit this link.

Horizontal to vertical formula across sheets

Typically, to transfer horizontal rows into vertical columns in Excel you would highlight the cells you want to change and copy, then right click on the cell you want to move to, select ‘paste special’, click ‘transpose’ box, and hit ok. But what if you want to accomplish this task with a formula instead so it is done automatically on the fly? In an earlier post I showed you how to do it by using the OFFSET function combined with a named range. Today, I’m going to show you a different Excel formula to transfer rows to columns.

I recently used these horizontal to vertical formulas in my 2013 NCAA college football bowl prediction pool manager, and I am going to refer to that template as an example, so if you haven’t already I recommend you download the sheet. I needed to get the list of the players from the main sheet, which were listed out horizontally, and get them into the leaderboard sheet. The players needed to be listed vertically in the leaderboard in order for my rank without ties formula to rank the players in order of who picked the most games correctly. The basic formula uses INDEX and ROWS functions and looks something like this:

=INDEX(Sheet2!D$22:H$22,ROWS(A$4:A4))

The INDEX function returns the value of the cell at the intersection of the rows and columns specified in the formula while ROWS returns the number of rows in a reference or array. The exact formula I used on the leaderboard sheet in cell A3 is shown below:

=IF((ROW()-2)<=$L$3,INDEX('Master Pool'!J$39:Z$39,ROWS(A$4:A4)),"")

The formula - notice the range

The range expands as the formula is filled down

It takes all the names in row 39 on the Master Pool sheet from column J to Z and puts them into a vertical column in the leaderboard sheet, starting in row 3. The dollar signs are very important. As you fill the formula down the range increases. I also used the IF and ROW functions to account for the number of players (if the row number minus two, because the formula started in row 3, is less than or equal to the total number of players, then transpose the data, otherwise leave blank). As you can see in the example spreadsheet, the data is transformed from a horizontal row on one sheet to a vertical column in another. This is important because the data in the horizontal column can change and the vertical column will update automatically - no manual revisions needed!

Printable 2014 NFL Playoff Bracket

The 2013-2014 NFL Playoffs are set to begin on Saturday, January 4 six days after the end of the crazy 2013 regular season. Two games will be played Saturday, January 4 and two games will be played Sunday, January 5. Second round games will be played Saturday and Sunday, January 11 and 12. Third round games will be played Sunday, January 19. The NFL Super Bowl will be played Sunday, Febuary 2nd.

I've create a printable bracket for the 2014 NFL playoffs with helmets that you can download for free and use in your office pools. As a bonus, I've also included the complete 2013-2014 NFL Postseason Schedule within the bracket spreadsheet, including kickoff times and broadcast stations. Download the file below (hosted on box.com).

Printable 2014 NFL Playoff bracket.xls download

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.

Top Excel Help Posts in 2013 and 2014 Goals

2013 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. I’m going to share my most popular Excel tips from the past year, my most popular Excel templates, and finally share what my goals and vision for next year is.

Top 5 Excel Tips from 2013

 

Listed below are the five most popular Excel tips I published on the Excel Help blog throughout 2013:

 

1. How to combine Excel files with a VBA macro
2. How to filter Data in Excel
3. How to create collapsible rows in Excel
4. How to embed Excel in a website
5. Excel pranks and practical jokes

 

Top 5 Excel Templates from 2013

 

Listed below are the top 5 most popular free Excel templates and spreadsheets I created and made available to download in 2013:

 

1. Printable Super Bowl Squares
2. Website Creation Checklist
3. Newborn Checklist for New Parents
4. Apartment Comparison Spreadsheet LITE version
5. NFL Helmet Schedule

 

Goals for 2014

I recently took a poll of my loyal followers by asking them what they would rather see more of on the blog: spreadsheet templates or tips/tricks/macros to improve spreadsheet creation efficiency? My most recent posts have primarily been spreadsheet templates I’ve made available to download for free, such as my newborn feeding schedule, college bowl prediction pool, etc. Something I’ve gotten away from lately is posting tips and macros to improve your Excel efficiency, like my earlier posts on how to create folders automatically, how to create hatching in Excel, etc.

The majority of my reader’s responses asked for more tips, tricks, and macros to improve Excel efficiency. I will still be creating and posting templates as I have been because I think a lot of people find them to be quite useful and you may pick up a few tips just by looking at them and examining the formulas I use. However, I promise to make a strong effort to post more tips and macros, especially for my email subscribers. In fact, I already have several rough drafts already written and am working on some new video tutorials. What would you like to see more of in 2014? Anything specific?

Have a great holiday season, thanks for your continued support, and see you in 2014!!!

2013 College Bowl Pool Spreadsheet

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.

Upgrades from last year’s college football bowl pool manager spreadsheet include:

• New easy method to make each bowl game worth a different point value
• New leaderboard tab and stats
• New entry sheet to pass out to participants/co-workers
• Manager copies and pastes participants picks into the bowl pool manager file (will be automated with a macro sometime soon)
• Complete NCAA college football bowl schedule with game times and TV station
• The bowl prediction sheets include the football helmet designs for every team, their win-loss record, and the logo for all 35 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!

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 manually add more players. 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

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!


2013 College Football Bowl Pool Manager.xls download (updated 12-9)
2013 College Football Bowl Prediction Entry Form.xls download (updated 12-9)


Please let me know if you have any questions, comments, find any bugs, or have any suggestions for improvement. If you like this spreadsheet then check out our NCAA Helmet Schedule spreadsheet. What team are you rooting for?

2015 College Football Bowl Prediction Pool