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!