Sunday, December 29, 2013

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.

2014 nfl playoff bracket

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







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.


Saturday, December 21, 2013

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:
 
 

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:
 
 

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!!!





Sunday, December 8, 2013

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.




2013 College Bowl Pool Spreadsheet


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



Sunday, November 24, 2013

Newborn Feeding Schedule Spreadsheet Template

I am happy to announce I am now a proud father! I'm never been so tired yet so happy in my entire life! While at the hospital we had to keep a Newborn Feeding and Diaper Log. As soon as we got home and the baby was napping I decided to recreate the log as a usable spreadsheet and have made it available to all you other new parents for free. Use the log to keep track of feeding and diaper changing times; whether it was breastfeeding or bottle; if it was just urine or stool plus any other notes and observations. Keeping track of your baby's feeding and changing schedule is a great way to make sure they are growing and thriving as they should be. Take the log to your doctor's office during checkups and you won't have to rack your brain to remember any of these details.
Newborn Feeding Schedule Spreadsheet Template
 



I suggest printing it off and keeping a copy next to the changing table or rocking chair like I have. I just can’t wrap my head around how something so small creates so much poop! Thank goodness I signed up for Amazon Mom and setup a weekly diaper delivery at a discounted rate. Download my newborn checklist for new parents and other spreadsheets related to health, babies, parenting, and more.

Sunday, October 20, 2013

Newborn Checklist for New Parents

Sorry for the lack of posts recently (besides sports spreadsheets) but the reason is because I’ve been getting ready for something big, something exciting, something life-altering: my wife and I are expecting our first child next month! We’re super stoked to be having a son but anxious at the same time. We’ve been working hard at getting everything ready but I can’t help thinking, “what if we’ve forgotten something important?” There have been so many things to do and there are so many things yet to do. How does a new parent keep it all organized? My solution is to use an Excel spreadsheet of course!
 

baby announcement picture shoes

baby announcement picture team rivalry jersey
 


In order to keep myself organized and to help other future parents I’ve put together what I hope is the ultimate newborn checklist in Excel. This master file is composed of four separate sheets (I imagine I'll probably add more in the future too):

things to do before the baby is born checklist


Things to do BEFORE the Baby is Born
The first sheet is a getting ready for baby checklist. It includes a list of things you should do before your baby is born and how soon before the due date they should be completed by.


Baby Registry Checklist
The next sheet in the file is the baby registry checklist. You can use this for a baby shower checklist or for a list of things you need before the baby arrives.




Hospital Bag Checklist
A list of what to pack for the hospital that you can easily print off if you would like.
 


Things to Do After the Baby is Born checklist
Another checklist of things that need to be done after the baby is born.


Behind the scenes so to speak, the newborn checklist spreadsheet uses named ranges, data validation to create lists, conditional formatting to color cells based on text, IF formulas, concatenate formulas, data filtering, and more. You’re free to poke around the spreadsheet if you want to see how it works or you don’t have to deal with any of that if you don’t want to and simply fill in the information or print it off.

My goal is to make this the ultimate resource for new parents. I’ve tried to include everything such as what to pack in hospital bag for mom. Tasks can be assigned to husband or wife. I also included some links to some services that can help you save money, like Amazon Mom. Believe me guys, your wives will be very impressed and happy if you use this list and actually help her prepare (yay brownies points). Download the spreadsheet for free using the link below.




Please let me know in the comments if you enjoy this newborn checklist and if you would like to see more parenting Excel templates like it.


Sunday, September 29, 2013

2013 MLB Playoff Bracket

October is here which means it’s time for the 2013 MLB playoffs to begin! The major league baseball regular season concludes on Sunday, Sept. 29 unless there are any necessary regular-season tie breaker games (Rays versus Rangers). The MLB playoffs are set to begin on Tuesday, October 1st. The Division Series returns to a 2-2-1 format this fall. Game 1 of the World Series is scheduled to be played on Wednesday, Oct. 23, in the ballpark of the American League champions.
 
2013 mlb playoff bracket


My 2013 MLB playoff bracket includes the schedule and TV coverage lineup. As an Ohio guy I'll be rooting for the Reds and Indians, though I am happy for Pirates fans to see their tea finally make it to the playoffs. Who do you have winning your 2013 MLB playoff bracket?

Tuesday, September 24, 2013

Downloadable 2013-2014 NHL Schedule in Excel

The 2013 NHL season is about to begin and to help get you excited for it I’ve made an NHL schedule spreadsheet available to download for free. The spreadsheet contains the logo of every NHL team and is similar format to my NCAA football helmet spreadsheet and NFL helmet schedule (but I didn’t put logos for every game due to the sheer number of games compared to the football seasons).


2013-2014 NHL Schedule.xls download

Monday, September 23, 2013

How to Combine Excel Files

There have been countless times during my day job when I’ve had to combine multiple Excel files into one. I used to do it the old fashioned way of copy and paste but after doing this tedious and time consuming task I decided to write a VBA macro to help me out. I’ve now made the template available for you to use for free. This version of the combine Excel workbooks macro allows you to choose the directory of the Excel files to copy without having to change the code in the Visual Basic Editor. The steps to use the Combine Workbooks tool are as follows:

combine excel files macro
 
  1. Place the Excel workbooks you want to combine into a single folder that contains no other files
  2. Go to View>Macros
  3. Select "Combine Workbooks" then click Run
  4. Select the File where the spreadsheets you want to combine are located
  5. Save the newly created file

Instructions are provided with the spreadsheet

Please note that this code will not work on protected sheets and it will automatically skip any blank worksheets in any of the workbooks saved in the folder. See how it works for yourself by watching my short demo video below:
 

 
Download the Combine Excel Files spreadsheet that contains the VBA code to copy all the worksheets from all the workbooks in a folder into the active workbook using this link:
 

Combine Files.xlsm download

Tuesday, September 10, 2013

Download a Wordpress Website Creation Checklist Spreadsheet

In the past two years I’ve created eight websites for various hobbies and niches I am interested in and that I felt were underserved in the online world. I use Wordpress as my content management system because it allows you to quickly create a website with no HTML or other coding experience. The steps to setup a new website are pretty much the same so I put together a Wordpress website creation checklist to help me remember to do everything. I’ve made this checklist into an Excel template which you can download for free by clicking the link below.
 
wordpress website creation checklist
 
Two skills I encourage everyone to learn that I’ve found invaluable in my career is how to program VBA macros and how to build a simple website. In fact, in one of my most recent projects these skills were used hand-in-hand. I built the majority of the Observation Wheel database with an Excel spreadsheet and a few custom macros. Every row in my sheet contained all the information for a single observation wheel. I then had the macro export the data to Microsoft Word in the HTML format I needed for the site. I then simply copied and pasted that text into Wordpress and published it. I created pages for over eighty dfferent observation wheels almost instantly!
 
There are countless positive benefits for learning how to make a website. I’ve included a link to all the resources I use within the template and I've tried to include as many tips as I could think of. I begin with registering a domain name and take you all the way through setting up an email list. Download my Wordpress website creation checklist below and please feel free to ask me any questions you may have!
 

Monday, August 26, 2013

2013 NCAA College Football Helmet Schedule Spreadsheet

Finally! After hours of work I've finally updated the entire 2013 NCAA College Football Helmet Schedule spreadsheet - just in time too! It was pretty difficult because it has not been updated in quite some time and, as you're probably aware, the landscape of college football has shifted dramatically the past couple of seasons (and will continue to do so for a few years).

2013 NCAA College Football Helmet Schedule Spreadsheet

The 2013 college football helmet schedule in Excel includes every team from all eleven conferences. Every game is listed as either home, away, or neutral site (denotated at the bottom of each sheet). The only thing I haven't completed is linking all the helmets to their teams, so when you click on a helmet it takes you to that team, but I am still working on it. A college football helmet schedule schedule may be available on other websites but, to my knowlegde, this is the only downloadable Excel version and unlike some of the others is 100% FREE!

Download the 2013 NCAA College Football Helmet Schedule Spreadsheet.xlsx

It's hard to believe that College football begins in just a few days! Will the SEC continue their dominance? Will the MAC surpass the Big Ten? Will Johnny Manziel sign any autographs for free this season?

You can also grab the 2013 NFL Helmet Schedule in Excel too.

Sunday, August 25, 2013

Apartment Comparison Spreadsheet Update




I’ve revamped my Apartment Comparison Spreadsheet website and the big news is you can now download the Apartment Search Spreadsheet for free! Signup for the Apartment Search Tips newsletter and you’ll immediately receive the link to download my apartment hunting tool. You can unsubscribe from the email list at anytime but if you’re looking for an apartment I recommend you don’t unsubscribe so you can get my valuable tips, like how to potentially earn $100 for signing a new lease!


apartment comparison spreadsheet update
The Apartment Search Spreadsheet can  also be used as an apartment search checklist as I listed almost every criteria you could think of for what you would like in a new apartment. Enter the data for every apartment you are looking at. Next, you’ll set an importance factor for several categories. What’s more important to you - size of the rooms or distance to work? The spreadsheet uses your inputs to give each apartment a score and you can easily see which apartment has the highest score and is thus the best choice for you.



Please note, the free preview version of the spresdsheet for apartment comparison only allows you to compare two apartments. You'll have to purchase the PRO version to compare more than two apartments, but it also comes with a lot of extra resources you might enjoy. Check it out here. And please do let me know if you find this tool at all useful or if you think it would be great if there were something like this for another item, like to compare houses or cars.

Thursday, July 25, 2013

2013 NFL Helmet Schedule Spreadsheet


It’s time to get your NFL 2013 schedule. The new football season is nearly upon us after a murderous off-season. It’s finally time to get back to playing ball! This NFL 2013 schedule in Excel includes indicators for all special Monday, Wednesday, Thursday, and Saturday games. If you’re looking for a picture for every football helmet in the NFL you can find them all in this spreadsheet. Each helmet is in fact a hyperlink, not to an outside website but to a “Place in This Document.” It uses Cell References so if you click a team’s helmet it will take you to that team’s schedule in the spreadsheet.
 
2013 NFL helmet schedule spreadsheet



The 2013 NFL helmet schedule spreadsheet can be incorporated into fantasy football leagues. And if you love fantasy football, you should check out the Fantasy Football Commissioner from CBS Sports.  Feel free to modify the spreadsheet as you see fit but please share the result with us so everyone can see what cool things you come up with! Download for free here:


I can't wait to see how all the storylines play out this year. Will Peyton Manning finally get a second Super Bowl ring? How will the New England Patriots fare after this crazy offseason? Will this be the year for my Cleveland Browns? I doubt it but you never know what will happen.

Update: Now you can get the 2013 NCAA helmet schedule spreadsheet here.

Wednesday, July 24, 2013

How to Filter Data in Excel

Learning how to filter data in Excel will help you analyse data faster and become better at your job. Filtering in Excel enables you to display only the data that you want to see on your spreadsheet without deleting anything. It’s a really great way to search through large amounts of information and you know I’m all about Excel tips that help improve your speed and efficiency!
add a filter in excel



There are three types of filters in Excel: list of values, by format, or criteria and you can sort your spreadsheet by order, color or text. What’s the difference between sorting and filtering? Sorting will rearrange the order of your list while filtering keeps the order but actually hides data based on your filter criteria.


To add a Filter in Excel, you can first select a single cell within your range of data but I recommend you highlight all your data (please note you cannot add filters to empty cells). Next, go to the Home tab then the Editing section. Under Sort & Filter click Filter (or use the filter shortcut Crtl+Shift+L). Once filtering is turned on you will see little arrows along your top row of data. Select one of the arrows to set your filter options. If you hover your mouse over the drop down arrow you will see a pop-up message displaying what the value the filter is currently set to (example: equals “Test”).
 
how to filter data in excel


To remove the filtering from your spreadsheet, simply click the Filter button again. If you want to reset the filter to the original values click on the Sort & Filter button and then click “Clear."


You can also turn auto-Filter on and off with a VBA macro:



Sub TurnAutoFilterOn()
'check for filter, turn on if none exists
If Not ActiveSheet.AutoFilterMode Then
ActiveSheet.Range("A1").AutoFilter
End If
End Sub


Turn off AutoFilter with VBA:


Sub TurnFilterOff()
Worksheets("Sheet1").AutoFilterMode = False
End Sub



Here’s a great video showing you exactly how to filter data in Excel:




Follow us on Google Plus for the latest updates when I will talk about dynamic filters and Kalman filters in Excel.

Thursday, June 6, 2013

Excel Pranks and Practical Jokes with VBA

I’ve shared a lot of Excel tips about how to increase your productivity at work but today I thought I would post something a little more fun – how to decrease your productivity! I’m talking about ways to use Microsoft Excel spreadsheets to play pranks, practical jokes, April Fool’s Day kind of stuff on your friends, roommates, and coworkers. The intent is not to harm anyone or hurt their professional careers – this is simply about having some plain ole fun.

A great way to wreak havoc in the workplace is to create a macro that automatically runs when an Excel workbook is opened. You can do this by writing a VBA procedure in the Open event of the workbook by using the Visual Basic Editor. Create a new Excel spreadsheet then press Alt+F11 to launch the VBA Editor. Next, right-click the ThisWorkbook object, and then click View Code.
excel pranks
In the Object list above the Code window, select Workbook. This will automatically create an empty procedure for the Open event like this and you can now add your evil code.

excel practical jokes
To make Excel automatically close itself when the workbook is opened use this:

Private Sub Workbook_Open()

     Application.DisplayAlerts = False
     Application.Quit
End Sub


Here’s a trick that will automatically open Microsoft Word and close Excel:

Sub Workbook_Open()
‘make sure the Microsoft Word Object Library is selected by going to tools>references

Application.Visible = False
Dim wdApp as Word.Application
Set wdApp = New Word.Application
wdApp.Visible=True
Set wdApp = Nothing
Application.DisplayAlerts = False
Application.Quit
End Sub


This is one of my personal favorites; have a message box pop-up asking if the user wants to download the virus they requested. Whether they press the yes or no button the next message tells them the virus has begun downloading!

Private Sub Workbook_Open()

MsgBox "The virus you requested is now ready to download, Do you want to start downloading now?", vbYesNo, "Virus Trojan-x45fju"

MsgBox "The Virus is Now Downloading. You have made the biggest mistake of your life! ByE bYe", , "Begin Virus Download"

End Sub

This function flips the workbook and will make everything on the left now appear on the right side:

ActiveSheet.DisplayRightToLeft = True

To change all cell’s color to black:

Cells.Interior.Color = RGB(0,0,0)

Instead of automatically running a macro on opening a workbook (and making it obvious you did something) you could embed a macro that only runs on certain conditions. A funny prank is a macro to change the size of the Excel window every time the user clicks on a cell:

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)

Application.WindowState = xlNormal

Application.Width = Int(Rnd() * 1000) - 100

End Sub


As you can imagine, the possibilities are nearly endless! Now you may think “All someone has to do is change or delete the code to fix the problem.” Well, you can protect your VBA code so only those with the password can modify it. Go to Tools>VBAProject Properties>Protection. Check the box to lock project for viewing and create a password (and remember it). Now you’re an evil genius!




Of course, if you’re not comfortable using VBA (which I recommend you get comfortable and learn it)  you can always use these old fashioned tricks:

  1. Use find and replace on a document, like replacing “you” with “you idiots”.
  2.  If your coworker has an old school mouse simply remove the ball when they're not around and then sit back and watch the fun when they try to figure out why their mouse isn't working anymore.
  3. Take a screen capture of a roommate’s desktop and save it as a jpg or bmp file. Turn on the Active Desktop, and make sure to turn off "show desktop icons". Change the wallpaper to the screen capture image you just saved and watch as they click away on their "icons" that mysteriously stopped working.
Or, you can use a device like the Phantom Keystroker.

Have you ever used something like this on someone? What’s your best Excel prank?