Saturday, August 19, 2017

2017 NFL Helmet Schedule Spreadsheet

Available to download now is the 2017 NFL helmet schedule spreadsheet. You’ll see a comprehensive breakdown of every NFL pro football team's 2017 season schedule with an image of each team’s helmet design. The NFL helmet schedule is printable too. You can save the spreadsheet as an PDF file or print it out and pin up in your cubicle at work.

 



Email required download link (to automatically update you if changes or additions are made and will update you when the next year’s schedule is ready)



No email required (no notifications):


As always, I welcome any comments or suggestions about how to fix or improve the sheet!
How can I improve this spreadsheet into something you’ll use all the time during pro-football season? Maybe in the future I should also include the option to see different styles of helmets too, like these. Or maybe the Disney-inspired team logos that this guy created. What future features would you like to see?

Tuesday, July 25, 2017

2017 College Football Helmet Schedule Spreadsheet

The 2017 college football season is a little over a month away! I still can’t believe my Buckeyes got shut out in the playoff game against Clemson. I really have no idea what to expect this season.


It’s fun looking at the 2017 college football helmet schedule to try to predict which games you think your favorite team will win or lose. This spreadsheet of the college football schedules includes every team from all 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!

college football helmet schedule 2017



I’ve finally done it! I’ve been creating the college football helmet schedule spreadsheet since 2011, and every year I manually update every single helmet. This took forever and unfortunately resulted in many manual entry mistakes: helmets not going with the correct team and so on.


Until now.


With a little help, the creation of this template has finally been automated. The football schedules are pulled directly from ESPN. I've even linked the helmets to the team's website on ESPN. Hopefully this automation will eliminate the manual input errors as well as decreasing creation time. In a future post I hope to show you how the macro works.




I’m happy to say this year’s sheet contains 245 different college football team helmets! It’s surprising how small the file size is when considering how many helmets there are.


Email required download link (to automatically update you if changes or additions are made and will update you when the next year’s schedule is ready)



No email required (no notifications):



As always, I welcome any comments or suggestions about how to fix or improve the sheet!

Here I am running after my son down the ramp at Ohio Stadium!


Tags: 2017 NCAA Excel Helmet Schedule

Saturday, June 10, 2017

How to Hide Gridlines in Excel - Three Ways

Gridlines in Excel are the faint, grey colored lines that appear around cells in a spreadsheet to distinguish them from one another. By default, the gridlines are displayed in worksheets using a color that is set by Excel. You can essentially turn off the gridlines, cover up the gridlines, or change their color in order to hide them.
There are at least three methods you can use to hide the gridlines in Excel.
1. The proper method is to go to View then uncheck the Gridlines box. You can hide gridlines on multiple sheets by selecting all the sheets first, then unchecking the box.



2. Change a cell’s fill color to white or another color. If you apply a fill color to cells on your worksheet, you won't be able to see or print the cell gridlines for those cells.

3. Change the gridline’s color to white. You can change the default Gridline color by going to File > Options > Advanced then scroll down to Display options for this worksheet.

To see how to do each one of these check out the video below.

Borders are not the same as gridlines in Excel

People often confuse borders and gridlines in Excel. Gridlines cannot be customized in the same manner that borders can. If you want to change the width or other attributes of the lines then you need to use a border. Gridlines are always applied to the whole worksheet or workbook, and can't be applied to specific cells or ranges. If you want to apply lines selectively around specific cells or ranges of cells, you should use borders instead of, or in addition to, gridlines.



Let me know if you found this Excel tip helpful!

Sunday, June 4, 2017

Best Excel Spreadsheet Memes - Prepare to Laugh

I've given hundreds of useful Excel tips since I started blogging eight years (!) ago in 2009. But I can't be 100% serious all the time. I've gotta have a little fun from time to time, like when I shared some of the best Excel pranks and practical jokes, not to mention all the sports related templates I produce. And who doesn't love a good joke or meme? So today I thought I'd share the best Excel spreadsheet memes I've collected over the years!

WARNING: Some of these Excel memes might make you literally laugh out loud. You've been warned.

Best Excel Spreadsheet Memes



How'd you know I like Excel? It's not like I write articles or make videos about it...


Yeah, not gonna happen, I'll make my Excel formulas as long as I want to!


Hmmm ok...


I hope not.


Mind = blown.


It's so worth it once you do you learn Excel VBA though.


Gotta have at least one grumpy cat meme.


Ever feel like this?


Me neither. Do people do that? Remove the gird lines by changing the grid lines color to white?



How many have hid non-working formulas before? Admit it if you have!


Haha, not necessarily.




There's always that one co-worker who spends more time formatting then working on the actual information within the Excel sheet.




When writing those super long formulas in Excel....



Drives me crazy.



When can you ever have enough spreadsheets?


I concur Steve!


Which Excel meme is your favorite? Please let me know by commenting below. Or if have a hilarious one that I failed to mention PLEASE send it to me!

Monday, March 13, 2017

2017 March Madness Excel Brackets

It’s the most wonderful time of the year! March Madness 2017 edition is here and the first of the four “play-in” games will begin on Tuesday, March 14th (which we’re not counting off any points for in my office pool). Once again, I’ll be using David Tyler’s NCAA Excel brackets, the same ones I’ve been using since at least 2010, as they're still the best as far as I’m concerned. I'd like to give a special thanks to David for continuing to update and post his excellent brackets each and every March and giving us something to look forward to as we crawl out of the final few weeks of winter (70 degrees a few weeks ago here in Ohio, now it’s snowing!).

I’m a firm believer that a great way to increase your Excel knowledge is to reverse engineer Excel spreadsheet templates, like these office pool March Madness brackets. Previously, I’ve shared 11 things you can learn from the best Excel brackets and they all still apply for this year’s version. 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, that can be downloaded by following the link below. Download the files and start picking them apart!

http://www.whistleblows.com/

 If you’re worried you might not be able to use the March Madness Excel brackets have no fear. They are easy to use and you can watch this short video I made to show you just how easy it is to use David's brackets. Simply click on each team to advance them (don't forget to enable macros).

 

 As far as actually filling in the bracket, here are some interesting stats about March Madness and a few tips that might help you choose your teams.
  2017s-March-Madness-By-The-Numbers-v5
Source: WalletHub


  • A No. 16 seed has never beaten a No. 1. Like ever.
  • A No. 12 seed usually beats a No. 5 seed.
  • Odds of picking a perfect bracket: 1 in 9.2 quintrillion.
  • Odds of picking a perfect bracket using historical data and basketball knowledge: 1 in 128 billion.


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. What new lessons about Excel have you learned by breaking down a template?

Wednesday, March 1, 2017

How to copy a formula down with a macro

Using VBA macros in Excel can make life easier. Here's another example of how. Let’s say I have formulas in row 2 of Sheet2 that I want to copy down a specified number of rows. The number of rows I want to fill the formula down is a value in A1 on Sheet1. Below is the code to show you how to do it along with my comments.

Sub Copy()

'Declare variables. R1 is the range to copy. R2 is the range where we want to put the formulas in the spreadsheet. M is the number of rows to fill to.
Dim r1 As Range, r2 As Range, m As Long
Dim ws1, ws2 As Worksheet
   
'get the row number to copy to from Define Variables sheet
Set ws1 = ThisWorkbook.Sheets("Sheet1")

The number of rows I want to fill the formula down is in A1, plus one for the header
m = ws1.Range("A1") + 1
       
Set ws2 = ThisWorkbook.Sheets("Sheet2")

Declare the range that you want to copy. In my case, the formulas are on Sheet2, in the second row, columns A to Z.
Set r1 = ws2.Range("A2:Z2")

Declare the range you want to fill in. In my example, I want to fill in from A2 through column Z, and down to the row number earlier defined as M.
Set r2 = ws2.Range("A2:Z" & m)
   
Finally, copied the first declared range into the fill declared range.
    r1.Copy r2
   
End Sub

Conversely, if you want to add a command button to your spreadsheet that will automatically clear a range in a sheet, you could use the following VBA code:

Sub Clear()

Dim r1 As Range
Dim ws1, ws2 As Worksheet
Set ws1 = ThisWorkbook.Sheets("Define Variables")
Set ws2 = ThisWorkbook.Sheets("AJ Generator")

Dim m As Integer
m = ws1.Range("L2") + 1
Set r1 = ws2.Range("A3:AJ" & m)

r1.Clear


End Sub

Sunday, February 26, 2017

How to check if sheet name exists with VBA

In Excel, you can't have two sheets with same name in the same workbook. If you create a copy of a worksheet, Excel will automatically rename the sheet, usually adding a (1) onto the name. Try creating a copy of an existing sheet and see for yourself. As you add more sheets you probably want to rename them to keep your spreadsheet organized.

There are times when you are using a VBA macro to automate processes in Excel when you want to rename a sheet. If the new name of the sheet is unique your program will be fine. However, if the macro tries to rename a sheet with a name that already exists as another sheet name, you could have a problem. One way to avoid this to make your macro more robust is to add code to loop through all the sheet names and check to see if your new sheet name already exists, and if it does then add something onto the name to make it unique.


Sub CheckSheetNames()

Dim i As Integer
Dim y As Integer

‘the new sheet name, what I want to rename the active sheet as
Dim myName As String
myName = "Sheet"

y = 0

‘loop through all the sheets in the activeworkbook
For i = 1 To ActiveWorkbook.Worksheets.Count

‘if my new sheet name matches any of the existing sheet names, then increase the y count by 1
If myName = Left(ActiveWorkbook.Worksheets.Item(i).Name, 5) Then

y = y + 1

Else
End If

Next

‘If y is greater than 0 then we know that a sheet already with that name already exists
If y > 0 Then

ActiveWorkbook.ActiveSheet.Name = myName & “(“ & y & “)”

Else
‘if y is 0 then the sheet name does not exist so we are free to rename it
ActiveWorkbook.ActiveSheet.Name = myName
End If
End Sub


Now you know how to automatically rename worksheets and how to loop through sheets using VBA. Adding simple checks like these will make your code better and more robust, so if others are using it that don’t have any programming experience they are more likely to not encounter any errors.

Tuesday, February 21, 2017

How to link a textbox to a cell Excel Macro

If you’re putting together some kind of diagram in Excel, you may want to have the ability to click on a shape or textbox and have it automatically jump to another location, like a cell. I have always believed it is faster to mass produce things then manually delete what you don’t need rather than manually creating textboxes, and this is another example of that. I would run a macro to create 50 linked textboxes and then if I only use 30 I can quickly delete the left over 20.
For example, the macro creates ten Textboxes and then links them to cells A1 through A10 using an automatically created hyperlink. The two most important expressions we will be using are:
To add and position a new Textbox using VBA:
.AddTextbox(Orientation, Left, Top, Width, Height)
To add a hyperlink to a textbox using VBA:
.Add(Anchor, Address, SubAddress, ScreenTip, TextToDisplay)
How to link a shape to a cell Excel Macro

If you’re not familiar with programming VBA macros in Excel, check out my quick start guide here. Listed below is the complete code with my comments to help walk you through it:
Sub CreateTextBoxes()
Dim i, d As Integer
‘d will be used as spacing to separate the textboxes so they are not created on top of one another
d = 10
‘perform the loop 10 times and create 10 textboxes. Could change this to be more or into a user input box
For i = 1 To 10
'create textboxes in a vertical column with equal spacing using d
       ActiveSheet.Shapes.AddTextbox(msoTextOrientationHorizontal, 150, 20 + d, 35, 25).Select
   
'add formula to textboxes so the textbox displays the text in the cell
    Selection.Formula = "=$A$" & i
   
'create hyperlink from textbox to cell, assumes sheet name is Sheet1
       ActiveSheet.Shapes.Range(Array(i)).Select
    ActiveSheet.Hyperlinks.Add Anchor:=Selection.ShapeRange.Item(1), Address:="", SubAddress:="Sheet1!A" & i
   
    d = d + 30
  
Next
End Sub

How to link a shape to a cell Excel Macro Video




If you put some values in cells A1 – A10 and then run this macro, it will create 10 textboxes, and when you click on the textbox it will jump to the location of the appropriate cell.  Download this sample file here.

Thursday, February 9, 2017

2017 NASCAR Fantasy League Manager Spreadsheet

2017 is the fourth 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 Monster Cup Points. Drivers may run in more than one NASCAR division but can only score points in one division.


2017 nascar fantasy league manager in excel


How to Play NASCAR Fantasy in Excel


At the beginning of the year (first race is Daytona on February 18th) 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 email required)


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



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!

Monday, February 6, 2017

How to Combine text from two or more cells in Excel

I'm trying out a new idea I have for sharing Excel tips: creating a short but very targeted PowerPoint presentation on Slide Share about how to do a specific task in Excel. Please let me know if you like this format or not.

Did you know that you can combine or merge text from two or more cells into one cell in a spreadsheet? There are, of course, multiple ways to accomplish this task. One way to do this is to write a formula in a third column which uses the concatenation operator, the ampersand (&).

The CONCATENATE function can also be used to join different pieces of text together. However, in Excel 2016 CONCAT replaces the CONCATENATE function. The CONCAT function combines the text from multiple ranges and/or strings, but it doesn't provide the delimiter or IgnoreEmpty arguments. Also new in Excel 2016 is the TEXTJOIN function combines the text from multiple ranges and/or strings, and includes a delimiter you specify between each text value that will be combined.




Please let me know in the comments below if you want to see more short presentations like this one! I also posted this as a short video on YouTube and on Facebook, would you prefer this as a video instead?

Monday, January 23, 2017

Super Bowl Squares Template 2017

The match-up for the Super Bowl is set which means it's time to download your Super Bowl Squares Template 2017 edition. Tom Brady and the New England Patriots will play Matt Ryan and the Atlanta Falcons in Super Bowl 51 on Sunday, February 5th at 6:30pm. To make the big game more interesting you can 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 raise money for charity). If you’re a fan of a team like mine (the Factory of Sadness) that will never make it to the championship (or winning more than one game), 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.

super bowl squares template 2017

This year’s Super Bowl Squares spreadsheet includes three different versions so 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.

New this year is the option whether to keep the same random numbers chosen for all four quarters or to have four different sets of random numbers for each quarter. Using the rotating quarters method, someone could theoretically still get the number 3 for all four quarters, though that’s not very likely, or four different players could each get a 3 for different quarters.



Below is a short video tutorial I put together of how to use my Super Bowl Squares template in Excel.



Super Bowl Squares Template 2017 Download


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). If you enjoy using this sheet football pool manager I would really appreciate it if you would share it with friends, family, and coworkers.

To be notified when any updates are made to the file or to get a notification when next year's template is available, download using this link(requires an email address):

Download the Super Bowl Squares Template 2017.xlsm file here


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.