Excellent use of Excel for Heat Mapping of Data

Best quote from the article: "John Nelson's charts and maps on traffic fatalities are a reminder that a good data [visualization] doesn't always require graphic design chops -- or software."

And how!

Check out the rest of the article to see some really lovely heat maps. If you want that powerful pattern recognition factor built right into the human brain to work, feed it visuals like this.

Thought you'd all enjoy,

Excel_Geek


Updated: I forgot to link to some of my own Heat Chart related stuff.

Inventor of the Barcode Dies

N. Joseph Woodland, inventor of the barcode, has died. So there's that. Reminded me that I actually did this in Excel.

2012-13 College Football Bowl Pool Spreadsheets

Sorry for the wait, folks. Here are this year's college football bowl season pool files. Enjoy!

http://www.excelgeek.com/downloads/collegebowls/2012-13_Bowl_Pool_Summary_revb.xls

http://www.excelgeek.com/downloads/collegebowls/2012-13_Bowl_Pool_Entry_Form.xls

For the uninitiated, the "Summary" file is the one the manager of the pool will use to keep track of everyone's picks and points. The "Entry Form" is the file the manager of the pool sends out to the participants, so they can make picks and assign points. Then the manager can copy-paste them into the "Summary" file.

Also, I routinely get requests for a version of this file with room for more participants. While I haven't updated the "expanded" version to this year's teams and bowls yet, you can yourself. Here's where I posted that version:

click here

UPDATED: I've since fixed the issue some folks (who've commented on this post) were having relative to picks not being displayed in the Individual Charts tab for users #21 and above. I've also added in the feature requested by one commenter for calculating negative confidence points for incorrect picks. Good idea, I thought. I added a user option to select either "Zero" or "Negative" for points for incorrect picks. Check it out.

Single Question Post-Election 2012 Survey

I don't get all political very often, but this morning I've got a burning curiosity. (And I apologize in advance to my international readers for my US-centric focus here.)

You have experienced the past four years. You've awaken this morning to see the same President in office and the balance of power in Congress largely the same. How optimistic or pessimistic are you about the next four years?

Take my single question post-election 2012 survey here: http://excelgeek.com/election2012_survey.htm

Highlight First Blank Cell With Conditional Formatting

Hey, just wanted to share a quick trick about conditional formatting.

If you have a data entry sheet with input in adjacent cells within the same row or column, and you want the next blank input cell to be highlighted with a color, you can apply conditional formatting based on a formula.

The following formulas assume that your input is starting from cell A1 and all the input is in adjacent cells:

• Vertical data entry:   =ROW(A1)=COUNTA(A:A)+1
• Horizontal data entry:   =COLUMN(A1)=COUNTA(1:1)+1

If there are empty cells before the actual input range, you need to modify the formula. Increase the "+1" with every empty cell above (or to the left of) the actual input range. This type of conditional formatting can be applied on the entire column, entire row, or custom range, and there would only be one highlighted cell at a time.

Excel Geek insiders will get the sample file from Eric.

2012 March Madness Bracket Files Are Here!

Looking for 2014 brackets? Check here.

Here we go, everyone!

Despite being in the middle of a bit of traveling, I've managed to get you the much anticipated ExcelGeek.com March Madness pool manager spreadsheet (and associated individual picks spreadsheet). Thank goodness Hampton Inn is a comfortable place to work, right?

The system works the same as last year. There are two files: The first is the "master" spreadsheet, used by the pool organizer to track the results of everyone's picks vs. who actually wins, etc. This is where you determine how many points a correct pick in each of the rounds is worth, as well. Also, the pool manager can make his or her picks in this file, too. The second file is the simplified "individual picks" spreadsheet used by the participants in the pool to make their picks and send the file to the pool organizer to pull their picks into the master.

• "Master" spreadsheet ($2.00)
• "Individual Picks" spreadsheet (FREE)

Once again, the individual picks spreadsheet is free, but the more complex "master" spreadsheet is not. It is ONLY $2.00, though. The files are both locked down and protected. In the case of the master spreadsheet, it requires a "key code" to unlock the magic.

Unlike years past, though, I won't make you email me the "lock code" the file presents to you when you first open it and then email you back a "key code" to unlock it. Instead, I've automated the process.

What you will do is the following:

1. Download the "master" file by clicking on the link above.
2. Open the file. Make sure you've got macros enabled. If you didn't have macros enabled when you first opened the file, enable them, then close and reopen the file, making sure to enable macros. If the file doesn't display a "Lock Code" when you open it, something didn't work right. Try again.
3. Copy the "lock code" the file presents you.
4. Paste that code (be sure to delete any trailing spaces added when you paste it -- it normally adds an extra trailing space for some reason) into the "Lock Code" field below.
5. Click the "Buy Now" button below to pay the $2.00 for the file via PayPal. You do not need to have a PayPal account to pay this way. Any major credit card will work just fine.
6. At the end of the transaction, you'll be redirected to a confirmation page with my pretty little face on it. It will display (among other things) the "key code" you'll need to activate the file.
7. Copy and paste that code into the spreadsheet into the proper place, click submit, accept my terms and conditions, and you're off and running!

Again, if you don't paste in your Lock Code and complete your transaction, you won't receive a "Key Code" that will actually work to unlock your file. Do not forget this part! You may have to pay another $2.00 if you mess it up.

(Actual submission form has been removed, so people don't accidentally send me $2 for LAST YEAR's bracket files.)

Best of luck, everyone!

Excel_Geek

2012 March Madness Bracket is coming...

We're under two days until Selection Sunday! You know what that means, right? That's right. It's almost time for another version of the ExcelGeek.com March Madness Bracket pool manager spreadsheet (and associated participant version).

I've got something new to show you all this year. OOOOooooooo..., right? Well, actually the pool master spreadsheet and associated participant picks sheet both will look and feel and work pretty much like they did last year. The big difference this is that you won't have to email me the "Lock Code" you get when you first open the (locked down and protected) "master" file. Instead, I'll give you a place to enter that code so that it is submitted when you pay the $2.00 via PayPal. Then, when the payment is processed, you'll be taken to a page that automatically generates the associated "Key Code" you'll need to put in the spreadsheet to make it work.

How about that? No more waiting on me. Instant gratification! (And, I don't have to struggle to keep up with emailing people codes.) You didn't know I was also a budding web developer did you? In all seriousness, I have a good friend, Craig, to thank for being patient enough to "teach" me a bit of .php. Thanks, Craiggers!

As soon as all of the picks are announced on Sunday, I'll get the files posted.

Talk to you all again in 48 hours or so.

Excel_Geek

Data Validation Quirk When Using Named Ranges

So here's a goofy item I stumbled upon today. (Others of you, I realize, have known about this for some time.)

Here's the situation:

1. You are using Data Validation to restrict input for a particular cell to items from a list (Data --} Data Validation --} List).
2. You have the "Ignore Blanks" option checked.
3. The list you are referencing is a Named Range.
4. The list you are referencing contains one or more blank cells.

What happens, you ask? Well, it lets users input any value into the cell that's supposed to be data validated without warning. Nice, right?

Three simple options to fix:

1. Uncheck the "Ignore Blanks" option.
2. Don't have any blanks in your Named Range.
3. Don't use a Named Range.

Just thought I'd pass it along...

Later,

Excel_Geek

Bowl Season Wrap-Up

Well, another college bowl season is in the books. For those of you keeping score, Mrs. Excel_Geek beat Excel_Geek for the third straight year (or so). And in a nail-biter, new-comer Excel_Geek Jr. matched his daddy for the number of correct picks, but was narrowly edged out by 8 confidence points. So you know, I picked for the little guy, using the =RANDBETWEEN() function and the =RAND() function -- maybe I'll do a post describing how to do that -- to make his picks completely randomly. That's right. My own, well-thought-out, analyzed, expert picks did only very slightly better than completely random picks and point assignments. Oh well...back to the drawing board. See you all next bowl season!

Later,

Excel_Geek

Oh, almost forgot some traffic stats: the 2011-12 Bowl Season (12/3/11 - 1/9/12) increased ExcelGeek.com website traffic by nearly 3.9x the average daily site traffic for the year leading up to it, whereas the 2010-11 Bowl Season (12/4/10 - 1/10/11) increased traffic by (only) nearly 3.4x. 2011-12's peak 20-day moving average traffic day (12/23/11) was nearly 44% higher than that from 2010-11 (12/24/10). The single day peak from 2011-21 (12/5/11) was 72.8% higher than the single day peak in 2010-11 (12/6/10). Overall...a great year.

Next up...March Madness!