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.

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?

Visit me at Excel Spreadsheets Help and leave a comment. Would love to see you there!

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!

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.

Visit me at Excel Spreadsheets Help and leave a comment. Would love to see you there!

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.

 

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.

 

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.

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

Visit me at Excel Spreadsheets Help and leave a comment. Would love to see you there!

Unique Excel Uses: Video Game

We’ve seen some very unique Excel uses in the past but this just might be the best (or most fun) application: Excel as a video game! Cary Walkin, a Canadian accountant, has transformed a Microsoft Excel spreadsheet into a playable video game. Arena.xlsm is a turn-based fantasy role playing game where the goal is to collect loot to become more powerful all the while warding off increasingly difficult  monsters. In fact, there are currently over 2000 possible enemies with different AI abilities. Sounds complex! Remarkably, it only took Cary four months to completely program this game.

Cary has received a lot or press and attention since creating his Excel game. Recently, he was featured on one of Reddit’s “I Am A” features. A few quotes from Cary:

“I'm an accountant by profession, I use excel every day of my life. I simply worked with what I knew. Also there is an old adage that underneath every RPG is a massive spreadsheet, so now the massive spreadsheet IS the RPG!”

“...a number of calculations are dependent on the background colour of a cell (such as if you are standing on fire).”

“Never stop learning.”

Read the entire interview feature here. Additionally, to see an example of how one would even begin to go about coding this massive gaming macros see Cary’s great VBA tutorial here.

Visit Cary’s site and download Arena XLSM today and try it out for yourself! But be warned, your productivity may decrease greatly after playing a few levels.  Please note you must have macros enabled in order to play the game. Arena.Xlsm will only work in Excel 2007, 2010 and 2013. It will not work in other spreadsheet programs either. Have fun!

-Nick

Of course I’m not playing this at work ;)

Visit me at Excel Spreadsheets Help and leave a comment. Would love to see you there!

How to create collapsible rows in Excel

I was recently creating an Excel spreadsheet template for a friend and I needed to know how to create collapsible rows in Excel. I had a worksheet that listed student’s names and information. My friend wanted a +/- sign at the beginning of each row representing different groups of students that could be clicked to reveal grades for various classes. The Group function in Excel presented the perfect solution to this situation.

One method often used to collapse rows or columns in Excel is by using the Group function.

Go to the Data tab, select the rows or columns you want to group, then select the Group icon (located in the Outline are). This will result in a button being placed to the left of the row number column and allow you to instantly collapse or hide the grouped rows. To ungroup the rows simply hit the Ungroupbutton (also in the Outline area).

You can use keyboard shortcuts to improve your speed and efficiency when applying this method. First, select the Row or Column range, then;

To group:
<Shift> <Alt> <RightArrow>

To ungroup:
<Shift> <Alt> <LeftArrow>

To retain the Groups, but toggle hide/unhide the symbols:
<Ctrl> <8>
(Using the "8" that's under the function keys, *not* from the num keypad.)

Another option to group rows would be to apply Subtotals to your range.  You'll get those outlining symbols and even a subtotal row between each group. The Subtotals button is also in the Outline section of the Data tab.

I’ve created a short how to video to show you exactly how to collapse rows in Excel. Check it out below:

 

So that’s how you expand or collapse a group of cells in Excel. Pretty easy, huh?

-Nick

Easily Grouping Columns and Rows in Excel

Visit me at Excel Spreadsheets Help and leave a comment. Would love to see you there!

Macro to Export Hyperlinks from Excel to Word

In this tutorial I am going to show you how to write a VBA macro to export hyperlinks from Excel to Word. I’m all about automation and efficiency and this is another real world example. I used this macro to quickly create a table of the world’s observation wheels for my latest website.

What you will learn by reading through this tutorial:

• How to create a VBA macro to send data from Excel to Word
• How to export hyperlinks from Excel
• How to find the last row of data in an Excel sheet using a macro
• How to paste hyperlink into Word using a macro
• How to make a webpage from an Excel file

To begin, we have an Excel sheet with names in column A, some of them are hyperlinks to webpages and some of them are not. Hit Alt + F8 and create a new macro, I named mined Tables. It’s time to begin coding.

 

 

The first thing we need to do is create a new instance of Microsoft Word and make it visible:

 

Dim appWD As Word.Application

Set appWD = CreateObject("Word.Application")

appWD.Visible = True

 

Next, let’s find the last row that contains data within our active Excel spreadsheet (my sheet is called “Data”):

 

Sheets("Data").Select

            Dim FinalRow As Integer

 

            FinalRow = Range("A9999").End(xlUp).Row

 

Now we add some error handling - if there is no data then quit the program, otherwise continue on:

If FinalRow = 0 Then

            Exit Sub

 

            Else

 

As a check, I like to have a message box pop-up displaying the total number of rows with data:

 

            MsgBox "Number of rows is " & FinalRow

 

Now it’s time to tell Word to create a new document. We’ll also add our header text by using TypeText. TypeParagraph inserts a new paragraph by going to the next line:

 

            appWD.Documents.Add

            appWD.Selection.TypeParagraph

 

            appWD.Selection.TypeText Text:="[table caption= List of Observation Wheels]"

 

            appWD.Selection.TypeParagraph

 

            appWD.Selection.TypeText Text:="Name,Height(m)"

Next, we’ll create a For...Next loop to cycle through every row and look to see if there is a hyperlink in column A. If there is a hyperlink, we want to copy it, if not then we still want to copy any text in the cell.

 

Dim hyperlink1 As String

Dim i As Integer

 

            For i = 2 To FinalRow

 

            If Range("A" & i).Hyperlinks.Count > 0 Then

 

 

            'if there is a hyperlink

 

            appWD.Selection.TypeParagraph

 

            hyperlink1 = Range("A" & i).Hyperlinks(1).Address

 

            appWD.ActiveDocument.Hyperlinks.Add Anchor:=appWD.Selection.Range,          Address:=hyperlink1, SubAddress:="", ScreenTip:="", TextToDisplay:=Range("A" & i)

 

            appWD.Selection.TypeText Text:="," & Range("B" & i)

 

 

            Else

 

 

            'If no hyperlink

 

            appWD.Selection.TypeParagraph

 

            appWD.Selection.TypeText Text:=Range("A" & i) & "," & Range("B" & i)

 

 

            End If

 

            Next 'i

 

Finally, we can add any text at the bottom of the document and close the if and sub statements.

 

            'end the table

            appWD.Selection.TypeParagraph

            appWD.Selection.TypeText Text:="[/table]"

            End If

            End Sub

 

You may have noticed I began and ended with [table] and [/table]. I installed a Wordpress plugin on my website that enables me to easily insert sortable tables into my webpage without any major html coding involved. So now I can run my macro on my spreadsheet, copy the result it spits out into Word, and paste the text into my website. Here is the end result, a sortable table of all large observation wheels found throughout the world! Pretty cool huh?

Visit me at Excel Spreadsheets Help and leave a comment. Would love to see you there!