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

My Excel 2016 Wishlist

Today, I started thinking about my Excel 2016 wishlist. Microsoft Excel has been updated every three years beginning in 2007. Knowing this, we can only assume the next edition of Excel will come out in three years from now in 2016. The latest update, Excel 2013, includes new tools like FlashFill, PowerView, Timeline Slicer, and over 50 new functions. Don’t get me wrong, Excel is a very powerful tool but it’s not completely perfect yet.

Thus, it’s never too early to start thinking about the next edition of Excel and what improvements can be made. There are a number of features that I think would make creating spreadsheets even easier. A couple of simple improvements could help save users even more time. Here are some of the things I’d like to see in Excel 2016:

• A FIXTODAY() function that holds the date of the day the formula was first created or entered into the spreadsheet.
• I don’t know if this will ever be possible outside of Google Docs but allowing multiple users to edit the same document at the same time would be wonderful.This would eliminate all those extra copies of spreadsheets floating around.  This option is already available!
• I don’t know why you can’t do this already but it would be great to be able to unhide multiple worksheets at the same time. Currently, you can only unhide one sheet at a time which can be a major headache and quite inconvenient.
• Along this same line, I would like the ability to unprotect multiple sheets at once. Something like a global password may be the solution.
• An easy way to be able to see exact revision history.
• More options when protecting a sheet, like being able to use CustomViews.
• More text manipulation functions and options.

That’s what I think. What about you? What would you like to see in future versions of Excel? What’s on your Excel 2016 wishlist? Please let me know in the comments below. Who knows, maybe Microsoft will even be taking notes!

2013 NIT Bracket Spreadsheet

 

If you’re an avid NCAA men’s basketball fan and March Madness isn’t enough for you than you can also download the 2013 NIT Tournament Bracket. The National Invitation Tournament is comprised of many of the teams that some argue should have been in the NCAA tournament field but got left out. You would think all of these teams would be really bad when in reality some automatic bids to the NCAA tourney go to really bad teams (twenty loss Liberty, for example) thus teams like Kentucky, Iowa, Ohio University, are forced out of the field of 68 and into the NIT. This is still a good thing for these teams because they get the extra practices with come with the postseason while the other teams are sitting at home until next year.

This bracket is pretty straight forward. I’ve included the dates, times, and TV coverage for each game. However, there is no Pool Manager for the NIT spreadsheets (have you ever heard of anyone running an NIT pool anyways?).

Download the 2013 NIT Bracket

Downloadable 2013 NCAA Tournament Bracket

The downloadable 2013 NCAA Tournament Brackets are here! It's finally time to download and print your 2013 March Madness Brackets. I’ve made my own tournament brackets for almost every other major sporting event (see them all in the downloads page) but I’ve never attempted to make my own March Madness brackets. Why? Because for the last several years I've used David Tyler's spreadsheets, which I consider to be the best NCAA Excel brackets out there and are nearly flawless in my opinion. They're free to download too!

David has created two separate files: the actual tournament brackets where you predict the winners of each game and a separate bracket manager for office pools. You must use his brackets in order to use the pool manager (and make sure macros are enabled).

Download the 2013 NCAA Tournament Bracket.
Download the 2013 March Madness Pool Manager

Instructions are included with the spreadsheets. To learn more about how the brackets are created read our interviews with David from 2010 and 2011. Also, be sure to visit David’s website, When the Whistle Blows.

My Buckeyes won the Big Ten Tournament and are one of the hottest teams in the nation right now. Who do you have winning the big dance? Let us know in the comments below!

If you would like to you can join my basketball pool on Yahoo's Tourney Pick'em.

 

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 Ungroup button (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

Printable Big Ten Tournament Bracket 2013

I’ve never done this before but being a big fan of the Big Ten I’ve decided to create a printable Big Ten Tournament Bracket this year. The men’s basketball tournament begins on Thursday, March 14th in the heart of Big 10 country at the United Center in Chicago, Illinois. The cool thing about this editable Excel bracket is it includes the complete Big Ten basketball tournament schedule including all of the game times and television channels. Currently, I only have seedings listed. I will list the actual teams once they are known and then update the spreadsheet on Sunday.

 

Download the all in one bracket and Big Ten Tourney schedule here.

I can’t wait until the Big Ten Tournament starts! There are so many questions to answer: Will Michigan have an early exit? Is Indiana fading? What Minnesota team will show up? Can Iowa or Illinois spring an upset? How far can Ohio State go? With the way each of the top five teams have dropped random games they should have won this year I’d argue there is no clear favorite to win the Big Ten Basketball Championship. What do you think? I’d love for you to share your thoughts on the Big 10 tournament!

Check back soon for the 2013 March Madness brackets and download our new Excel Spreadsheets Help Android app!

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?