Tuesday, December 30, 2014

Excel Spreadsheets Help Turns Five Years Old

This post is a little bit late, but if you hadn't noticed Excel Spreadsheets Help recently hit another milestone - our 5 Year Blog-iversay! Yes, it's hard to believe but this blog was launched over five years ago, on December 6th, 2009. In fact, check out our very first post here. In those five years we've had several highlights, one of which was reaching over one million pageviews! While the number of total posts has declined every year, I think the quality of the posts has improved (do you agree?). Here's to another five years!

The Top Five Posts from the Last Five Years:

  1. How do I Insert the Degree Symbol
  2. How do you flip or reverse a column
  3. Project Management Downloads
  4. How to make horizontal rows into vertical columns
  5. How to create collapsible rows in Excel

Year in Review 2014 and 2015 Goals

In 2014, I created some new spreadsheet templates and upgraded some old ones. Many readers were asking for more tips though, and I did my best to deliver with posts such as how to make Excel drop down arrows visible, and how to align charts. I created a "how to" Index to track all my Excel tips. In 2015, I want to add even more tips and continue to improve my templates. What would you like to see more of on this blog?

What did you accomplish in 2014? What are your Excel spreadsheet goals for 2015? How can I help you achieve your goals?

Monday, December 29, 2014

Printable 2015 NFL Playoff Bracket

The 2014-2015 NFL Playoffs are set to begin on Saturday, January 3rd a week after the end of the crazy 2014 regular season. Two wildcard games will be played Saturday, January 3 and two games will be played Sunday, January 4. Second round games will be played Saturday and Sunday, January 10 and 11. Third round games will be played Sunday, January 17. The NFL SuperBowl will be played Sunday, February 1st. 

printable 2015 nfl playoffs bracket spreadsheet

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

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

Monday, December 8, 2014

2014 NCAA Bowl Prediction Pool with Playoff Bracket

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. This year has the added bonus of not just single bowl games but the addition of a four team playoff to determine the national champion.

2014 ncaa bowl prediction pool spreadsheet

Features and upgrades over the previous college football bowl pool manager spreadsheet include:

  • Easy method to make each bowl game worth a different point value
  • Updated leaderboard tab and stats
  • Separate entry sheet to pass out to participants/co-worker
  • Complete NCAA college football bowl schedule with game times and TV station
  • The bowl prediction sheets include the football helmet designs for every team (taken from the 2014 helmet schedule spreadsheet), their win-loss record, and the logo for all 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!

Fun Fact: Fresno State is the only team with a losing record (6-7) while Florida State has the best record at 13-0.

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

There are now three ways to add participant’s data:
  1. Manual entry using the drop down lists
  2. Copy and paste from the selection sheet to the bowl manager
  3. Use the import macro to automatically import a player’s data into the pool manager by way of a macro (not yet ready to use, check back soon)

I will explain how it works in a later post. 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. in column A, for bowl games with really long names (i’m looking at you san diego county credit union pionsettia bowl) I use the excel shortcut alt+enter to add text to the next line (shortcut post link)

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!

2014 College Football Bowl Pool Manager.xlsm download
2014 College Football Bowl Prediction Entry Form.xls download

Please let me know if you have any questions, comments, find any bugs, or have any suggestions for improvement.  What team are you rooting for?

Monday, December 1, 2014

How to keep Excel drop down list arrows visible

A useful feature in Excel is the drop down list but currently there is no way for a user opening a spreadsheet to be able to tell what cells contain drop down lists without clicking on them. A simple solution that I've used in the past, such as on my personal finance tracking spreadsheet, is to fill all the cells containing drop down lists with the same color.

If you choose this option, you should make a key or insert a text box with a note that the user will see as soon as the spreadsheet is opened.

Another option that's pretty cool is to make a "fake" arrow that is always visible. This video tutorial from Excel Campus shows you exactly how to do so: 

Bill "Mr Excel" Jelen also has a few hacks to make it appear the drop down arrows are always visible that he shares on his YouTube channel (and his was one of the ones featured in my list of 11 best Excel video tutorial channels):

Sorry that the title of this post is a little deceiving as there really is no "good' way to keep Excel drop down list arrows visible but there are some alternative methods. Which of these "hacks" do you like the best? Or do you have your own solution? Or is it never a problem to you?