Excel Spreadsheets Help

Sunday, February 14, 2016

2016 NASCAR Fantasy League Manager Spreadsheet

2016 is the third year in a row that I’ve made the NASCAR Fantasy League spreadsheet available for download. 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.

How to Play NASCAR Fantasy in Excel

At the beginning of the year (first race is Daytona on February 21st) 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 at the beginning of the year (or uses the new entry form to copy and paste entries). Players 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. This Nascar fantasy manager template can currently handle up to one hundred drivers and one hundred participants without needing to modify a single formula!

This year, there are two options to download the 2016 NASCAR Fantasy League manager:

Download Option 1 (no info require):

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.

2016 NASCAR Fantasy League Manager.xls

Single Player Entry Form.xls

Download option 2 (email address required):

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 version of the file, either an update for this year or you will also be notified when next year's sheet is ready to go.

https://gum.co/nascarmanager

Why two links? 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 sport manager templates to raise money for their favorite charities, which is so awesome! In the future. I will improve the spreadsheet by automating the entry process (I know, been saying that for two years now) and come up with a way to update the driver scores easier. This is just the third iteration of the template and I don’t follow NASCAR myself like I do football, so I’m sure it can be improved in some areas. Please let me know if you have any suggestions!

Tuesday, February 9, 2016

Excel Help Milestone: 1.5 Million Page Views!

In my 7th year of blogging, I’ve reached another monumental milestone. In April of 2014, I surpassed one million page views. Today, I am excited to announce Excel Spreadsheets Help has surpassed the 1.5 MILLION page view mark. Yes, I realize there are larger websites out there that may have 1.5 million views in a single day, but those are larger brands with an entire team or company of people. This Excel help blog is run by one person in his free time using Google’s free Blogspot blogging platform, so to me it’s quite the accomplishment. And it’s hard to wrap my mind around 1.5 million of anything. That’s like 15 Ohio Stadiums full of scarlet clad football fans. Wow!

Now, let’s take a look at some fun numbers and statistics from seven years of sharing Excel tips and templates:

Excel Spreadsheets Help Age: 7 years
Total number of posts published: 248 (249 after this one goes live)
Time to get to 1 million page views: 53 months (2009 - 2014)
Time to go from 1 million to 1.5 million views: 21.5 months (2014-2016)
Total time to hit 1.5 million: 74.5 months
Estimated timeframe to hit 2 million page views: November 2017
Average number of posts per month: 3
1.5 million views / 74.5 months = 20,134 average pageviews per month
1.5 million views / 248 posts = 6,048 average pageviews per post

The top five most popular posts since December of 2009 are listed below and interestingly, all top five posts were written in 2010:

Thank you so much for continuing to use my little site as a resource for Excel help. There are hundreds of other Excel tip websites out there, so I’m glad to see people are still coming back to this one. Can't wait until we hit that two million mark!

Sunday, January 31, 2016

5 Lessons Learned from Super Bowl Squares 2016 to Improve Your Excel Skills

Last week I released the latest and greatest version of my Super Bowl squares game template with more ways to play than before. Even if you don’t care for football or sports, numerous lessons can be learned by dissecting this spreadsheet, including:

How to assign a macro to a command button
How to clear cells with a macro
How to generate random numbers in Excel with a macro
How to use a macro to create a list based on cell values
How to create a dynamic drop down list

I thought I would offer a few more details and insights into how each of these features work.

1. How to assign a macro to a command button

To insert a command button into your Excel spreadsheet you’ll want the Developer tab to be available. Go to File > Options> Customize Ribbon and under Main Tabs check Developer. Now at the top of Excel you should see the Developer tab. Click on the Insert button drop drown, then under form controls click Command Button. Place the button wherever you want along with the desired size. To have a macro run on the click of the button, right click the command button and select Assign macro. Pick your macro and you’re good to go!

2. How to Clear a Range with an Excel Macro

To clear a range with an Excel macro, use the code below. When you click the “Clear Numbers” button, it is linked to this macro and will clear the range.

Sub Clear_Random_Numbers()
'clear the random numbers
Dim DelRange As Range
Set DelRange = Range("B5:B14")
DelRange.ClearContents
End Sub

Unlike a formula, if you insert a row or column and your target range moves, the macro will not update automatically like a formula will. Keep this in mind when making changes to a spreadsheet containing a macro. It’s a good idea to have the layout and format fixed as much as possible before writing your macro code.

3. How to generate random numbers in Excel with a macro

To generate random numbers in a range with a macro, modify the code below. This code generates random numbers between 0 and 9. To start from 1, take out the “-1”. To get more random numbers, change 10 to 100 or whatever value you need.

Sub Generate_Random_Numbers()
'define range of cells for NFC random numbers
Dim NFCRange As Range
Set NFCRange = Range("B5:B14")
NFCRange.ClearContents
For Each c In NFCRange
Do
c.Value = (Int((10 * Rnd + 1))) - 1
Loop Until WorksheetFunction.CountIf(NFCRange, c.Value) < 2
Next

4. Macro to Create a List Based on Cell Values

The next macro for the Super Bowl squares sheet is for the Assign Names Randomly sheet. This style of play allows users to enter their name then input how many squares they want. So we need to generate the complete list of names. The key to this program is getting the row numbers to add correctly.

Nick 3
Sarah 2
Henry 1

List would look like this:

Nick
Nick
Nick
Sarah
Sarah
Henry

How do we get the result? Use loops to repeat the code that adds the name to the list.

Sub Assign_Random_Number_For_Names()
'stop screen updating so macro runs faster
Application.ScreenUpdating = False
'clear the range
Dim DelRange As Range
Set DelRange = Range("AA2:AA101")
DelRange.ClearContents

'generate a list of all the names in order to populate the field
Dim row As Integer
Dim NumberSquares As Integer
Dim Squares As Integer
Dim RowNum As Integer
Dim PlayerName As String

RowNum = 2

For row = 2 To 101

NumberSquares = Excel.Cells(row, 24)

For Squares = 1 To NumberSquares
PlayerName = Excel.Cells(row, 23)

Excel.Cells(RowNum, 27) = PlayerName

RowNum = RowNum + 1

Next

Next

5. How to create a dynamic drop down list

In this spreadsheet you may have also noticed I used a dynamic list – a drop down list where the values change based on other cell’s value. There are only one hundred squares in Super Bowl squares, so when each player picks how many squares they want, the total value can only add to one hundred. When making your selection it should only show how many squares remain, basically it needs to subtract from 100 the total value of squares already entered. This is done in column P using an IF and ROW formula. If the total value of squares taken is less than one hundred, then show the row number, if not, show nothing.

I hope by examining this template you’ve learned a thing or two about Excel.

Sunday, January 24, 2016

Super Bowl Squares 2016 Excel Template for Office Pools

Match-ups for the 50th Super Bowl are set which means it's time to download your Super Bowl Squares 2016 Excel template for office pools. Peyton Manning and the Denver Broncos will play the Cam Newton and the Carolina Panthers in Super Bowl 50 on Sunday, February 7th at 6:30pm. To make the big game even more fun start a football office pool using our printable Super Bowl 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, or help. 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 three different versions and you can choose 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. Pick your own squares - Manage the office Super Bowl pool directly in Excel. In this version, each player gets to pick what squares they want. Once all are taken, press the randomize button to generate the random score numbers. No modifications are necessary, though you can if you so desire.

3. Assign squares randomly - In this new version, type each player's name in the manager sheet and use the drop down menu to assign how many squares to give each player. Then click the Assign Names Randomly button and it will automatically populate the grid with all of the names.

Super Bowl Squares Rules & How to Play

Listed below are the basic instructions on how to play Super Bowl Squares (which are also included within the spreadsheet for the three different versions, 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 Super Bowl spreadsheet for free, simply click the link below then click on the “Download” link in the upper right hand corner.That’s it!

Super Bowl Squares 2016 Excel Template for Office Pools.xlsm download

Or download here to subscribe and receive instant notification whenever the file is updated.

Please let me know in the comments or by email which version you'll use to play - printable, pick your squares, or randomly assign names.

UPDATE: 2017 Super Bowl Squares template available here

Tuesday, January 12, 2016

How to change the default arrow size in Excel

As an engineer at my day job, I’m constantly making diagrams in Excel. I have to point at and label different components using lines and arrows in Excel, but it’s rather annoying when I have to change the line weight and arrow color every single time. But this is Excel we’re talking about here, where there is an option for almost everything. So yes, you can actually change the formatting defaults of a shape, text box, or other object and then make your changes the new defaults for anything you add later. For example, if I want all of my arrows in Excel to be colored red, I can change the default to red so I don’t have to change it every single time – a great time saving tip!

To change the default arrow size and color in Excel (or any other shape) follow these steps:

1. Add the arrow (or other shape) that you want to change the formatting defaults for (insert>shapes>arrow)
2. Select the arrow
3. Change the arrow’s color and weight or any other changes that you want
4. Right-click the arrow, and then click Set as Default Line.

Now you can create new arrows with the same formatting and not have to create it or copy and paste it over and over again.

The new default only applies to the open workbook. I have not yet discovered a way to permanently change the arrow size, from session to session or workbook to workbook, other than possibly saving it as a template. Do you have any other suggestions how to do so?

Monday, January 4, 2016

Printable 2016 NFL Playoff Bracket

The 2015-2016 NFL Playoffs are set to begin on Saturday, January 9th a week after the end of the crazy 2015 regular season. Two wildcard games will be played Saturday, January 9 and two games will be played Sunday, January 10. Second round games will be played Saturday and Sunday, January 16 and 17. Third round games will be played Sunday, January 24. The NFL SuperBowl, number 50!, will be played Sunday, February 7th.

I've once again created a printable 2016 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):

2016 NFL Playoff Bracket.xls download

As a Browns fan, it's hard to once again see two other teams in our division make it to the playoffs while we miss out! 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.