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?

Tuesday, November 25, 2014

2014 Holiday Gift Ideas for Excel Users and Computer Geeks

Looking for holiday gift ideas for Excel users? The holiday season is right around the corner and it’s never too soon to start thinking about what presents you're going to get your friends, family, or coworkers. To help you out, I’ve made a list of items I think would be very useful or exciting for not only Excel spreadsheet users but computer nerds and tech geeks like myself. Some of these items I use on a daily basis and others are on my own personal wish list. Enjoy and feel free to suggest items by leaving a comment below!

Microsoft Excel and Other Books


Excel 2013 Bible by John Walkenbach. Excel at Excel with the help of this bestselling spreadsheet guide. This book has everything to help you become a power user of Microsoft Excel. Keep it on a shelve near your desk.


Excel 2013 Power Programming with VBA by John Walkenbach. This book covers all the methods and tools you need to know in order to program with Excel.


What If?: Serious Scientific Answers to Absurd Hypothetical Questions. This book contains hilarious and informative answers to important questions you probably never thought to ask, like: What if everyone on earth aimed a laser pointer at the moon at the same time? What if you could drain all the water from the oceans? What if all the lightning in the world struck the same place? Very fun and interesting read!


Tech, Gadgets, and Toys

Microsoft Surface 2: Hands down the best tablet for Excel and other Microsoft Office products. In fact, it comes with fully functional versions of Excel, Word, Outlook, Powerpoint, and Onenote for free. As far as I know, no other tablet has anything comparable. 32 and 64GB versions are available.


Dash and Dot Wonder Workshop Programmable Robots: If you're into programming things like Excel macros and you want to teach your kids the joys of programming then I've found the perfect gift for you. These cute robots are designed to help introduce children to the wonderful world of programming. Oh, and they're fun for adults too!

Raspberry Pi Computer: The Raspberry Pi is a credit-card sized computer that plugs into your TV and a keyboard. It's a capable little PC which can be used for many of the things that your desktop PC does, like spreadsheets, word-processing, and games, as well as plays high-definition video. However, you need ALL of the peripheral equipment such as mouse, keyboard, monitor (HDMI output), ethernet cable, power supply.


Laser Project Keyboard: We live in the future! This virtual keyboard is a 63-key QWERTY holograph that projects at full size onto any flat surface, lessening the tediousness of mobile texting and emailing, and helping curb embarrassing auto correct fails.


Phantom Keystroker V2: Remember my post on Excel pranks and practical jokes? Here's a new one for you: attach this evil prank device to your victim's computer and it makes random mouse movements and types out odd garbage text and phrases.


Laser Pointer: I use one of these nearly everyday at my job for meetings and presentations. You can also use it as a toy to play with your cat.



Quadcopter: Why did this make the list? Because it looks like fun! Wouldn't it be great to play with on Christmas morning?

What gifts are you getting for your fellow Excel user?

Monday, October 6, 2014

7 Resources for Excel Macro Programmers

Here’s a list of seven resources I use almost everyday when programming macros in Microsoft Excel:

1. Built in Help Features

The Microsoft Visual Basic Help Documentation can be accessed by going to Help > Microsoft Visual Basic Help or pressing F1 while inside the Visual Basic Editor. The best part about it is the search feature. I use this all the time!
Another built in feature that can help you solve your problems is the object browser and is a great tool to use when you’re stuck and don’t know what to do next. While in the VBE go to View>Object Browser (or simply hit F2) and use the search bar.
7 resources for excel macro programmers
 

2. Developer tab

You should display the Developer tab or run in developer mode when you want to write macros, run macros that you previously recorded, or create applications to use with Microsoft Office programs.



To show the developer tab: Click the Microsoft Office Button and then click Excel Options, PowerPoint Options, or Word Options. Click Popular, and then select the Show Developer tab in the Ribbon check box.

 
3. Flowchart Process Diagrams
When you’re programming a complicated macro it’s often a good idea to visualize how it’s going to work before you start typing away.  For these large problems I like to create flowcharts to get an overall idea of how the code will flow. It also helps to think about the steps the macro will need to perform, including all the user inputs. I often write out a flowchart, decision tree, or quickly sketch a process map. My favorite tool for this task is Draw.IO because it’s free and it lets you create flowcharts right in your internet browser and you can save your charts directly into Google Drive, DropBox, etc.
Begin sketching flowcharts here: https://www.draw.io/
Another option is to use something simple like Excel, Word, PowerPoint, or Snagit, like this flowchart:
 
4. VB Script Functions
No, I haven’t memorized every single function that can be used in VB. Instead, I prefer to know where that information is and simply look it up really fast. This page contains all the built-in VBScript functions, like Date, Array, IsEmpty, etc.
5. Pre-written Excel marcos
It’s easier to start with code you know works rather than writing everything from scratch. I almost always start a new macro by taking snippets of code from macros I’ve written earlier. If you’re new to programming and don’t have a pool of macros to pull from you can use some of mine. I’m compiling a list of Excel macros that can be downloaded from my website. Especially useful is how to combine workbooks.
6. Notepad++
Sometimes you don’t necessarily need Excel to write your code, just a good notepad tool to help you get the job done. You could use Notepad, Wordpad, or Microsoft word, but instead I recommend and absolutely love Notepad++. I use it all the time for tasks like: looking through other programmer’s code, record macros, compare text files for differences, view/edit files. It’s much better than using plain ole Notepad. It’s also where I compile all my examples and tutorials before copying them to my website or book pages.
Download for free here: http://notepad-plus-plus.org/
 


7. Eng Tips / Stackoverflow / Mr Excel Forum
When I’m completely stumped by a programming problem I turn to forums where I can get potential solutions from over programmers. The forums listed are all simple to setup an account at and you can start posting questions right away. Often times, other programmers have had the exact same question and you can find the solution by searching the forums. So if you’ve gone throw tons of Google search pages and are still pulling your hair out, got to one of these forums and get the answer you need.
What tools do you use when programming Excel macros?

Monday, September 8, 2014

Getting started with Microsoft Excel Macros

I’ve had many readers contact me explaining how they want to start creating macros for Excel to automate their work but they just don’t know where or how to begin. In order to help out I’ve created a short PowerPoint presentation called “Getting started with Microsoft Excel Macros.” I uploaded the ppt to SlideShare where you can download it for free and it’s embedded below.



This introduction to macros quickly covers topics such as why use macros, programming basics, how to create your first macro, the drawbacks of using the macro recorder, troubleshooting, and more.

I’d love to get your feedback on this. Would you like to see more PowerPoint presentations like this or not? Do you have any questions about getting started with Microsoft Excel macros?

Thursday, August 14, 2014

Quick Excel Tips: Dynamic Lookup

Today, I’m going to show you one of my favorite Excel tips: Dynamic Lookup or Dynamic Searching by using a combination of VLOOKUP and MATCH functions. Bascially, this function combo makes it so that the column that you pull the data from is dynamic based on the header making it more flexible than VLOOKUP by itself because you don’t have to rely on knowing the index column number.

In my example (that you can download below), in cell B2 I have this formula that combines VLOOKUP and MATCH:

=VLOOKUP($A2, $D$2:$G$14, MATCH($B$1,$D$1:$G$1,0),FALSE)

excel dynamic lookup formula


The column header in B matches one of the column headers in D, E, F, G - it doesn’t matter which one, you can change it and the values update automatically, which is the beauty of this formula. This makes it easy to add or remove columns without having to update your formulas. Not only is it more dynamic, the index column need not be on the left. Try it yourself!

Download my Dynamic Lookup formula example spreadsheet here.

Monday, August 4, 2014

Personal Finance Tracking Template

I think I've talked about posting this before (and never did) but after a reader’s request I've finally made my personal finance tracking spreadsheet available to download. Since becoming a new parent it’s even more important than ever for me to track my finances due to all the additional expenses, like doctor visits, buying diapers, more groceries, daycare (holy crap it’s expensive!!) and more. You don’t need to buy any expensive personal finance software, just use a simple Excel spreadsheet and I'll show you how.


The first sheet in the workbook is the Categories page where I list what I want to categorize each of my bills as: gas, electric, mortgage, cable, etc.


Then there is an Income page and an Expenses page with drop down lists driven by what is entered on the Categories page. Finally, a Totals page shows you how much you’re bringing in and how much you’re spending each month. I left a few rows filled in on the template so you can easily see how it works (data is made up and not real).




Pie charts are a great way to visualize your monthly expenses to see what you’re spending the most money on. Sometimes it's quite eye opening, like "wow, we spend a LOT of money eating out! Maybe we should start to make more home cooked meals."
 



I use my spreadsheet to see how much we’re spending on food, gas, entertainment, etc. and you can use it as a free budget planner tool too! You can set a montly goal on the Totals page and see if your spending stays below it.


Personal Finance Tracking Spreadsheet.xls download



This template uses:

  • Data Validation
  • Name Manager
  • Pie charts
  • Line Graphs
  • =Month()
  • =Average()
  • =Max()
  • =Min()
  • =Sumif()
 





As you can see on the example income page, I'm a big believer that in this day and age everyone should try to have more than one source of income, even if your secondary income is much smaller than your primary income stream.
 



This can be accomplished by anyone who has access to the internet, because there are multiple ways to make a little extra in your spare time: online survey taker, freelancer, writer, affiliate marketer, social media manager, graphic designer, Fiverr gigs, blogging, Amazon buyback program, etc. All it takes is a little extra effort.