Sunday, December 28, 2008

Bullseye Chart in Excel

The other day I got a $50 Project request from a guy who was analyzing shooting range results -- that's right, guns and stuff. Cool. Anyway...he couldn't figure out a nice, clean way to visually represent the results in scatter plot fashion. He had even started to mess around with various in-cell charting techniques to pull it off.

I decided that Excel's built-in XY scatter plot graph should be able to do the trick, but it took treating each data point as it's own series (so you can use the "shot number" as the data series name and place it in the data point), adding additional data series to "draw" the bullseye and concentric circles, and some other formatting tricks to pull it off. Here's what it looks like:

All in all, not bad, I'd say. Insiders subscribers, your file's on it's way.

Friday, December 26, 2008

Dial / Gauge / Speedometer Chart

Many of you are no doubt aware that this is far from an original concept. Just google "Excel dial chart" or "Excel gauge chart" or "Excel speedometer chart" and you'll see the legions of others before me -- Jon Peltier, Andy Pope, Bill Jelen, to name some major influences on me -- who have done their own versions of this type of chart. Some even have released handy add-ins so that non-power users can easily create these charts for themselves. Nonetheless, I thought it'd be fun to put my own stamp on this type of chart and let my Insiders subscribers play with it. Here's a shot of the chart:

Users will be able to change it's size, scale, start and stop angles, show or hide major and minor gauge marks and add one or two "red zone" regions for the chart, among other features.

Have fun with it.



Sunday, December 14, 2008

Reader's Enhancement to 2008-2009 College Football Bowl Spreadsheet

This is what I love most about the Internet: collaboration with complete strangers.

A couple days ago I got an email from one of my readers, Jonathan Sickinger. Jonathan said that he searched on Google for bowl spreadsheets and found the one I did. (BTW, I saw that I'm the number one hit for a great variety of college bowl pool spreadsheet-related!) Jonathan said that after playing around with the spreadsheet for a few days he decided to make some edits and customizations. Great! He made some cosmetic changes to match an entry form that he sends out to his pool participants. I like the look better than what I had done, personally. Next he added space for up to 40 participants (over my 35).

Then Jonathan said that he had an epiphany: In addition to the individual tracking charts, wouldn't it be great if we also had a chart for each bowl? These charts allow participants to see the pick dispersion for each game, giving them a sense of how important that game is to their overall chances. For example, if everyone in the pool weights the same game as one of their most confident picks then the relative importance of that game drops significantly. Follow? Jonathan also threw in some data on each game to show where the bowl ranks in average points wagered and the distribution of who picked which team. Very cool.

I thought it'd be nice to share with everyone this new and improved version and it's accompanying entry form, so here you go:


Note: the file's a bit heftier now at >2,500 KB vs. the roughly 1,200 KB it was before, but I think the new features are worth the added weight.

Next up? Jonathan and I are thinking about collaborating on a VBA-based enhancement to make it so that once a pool organizer gets everyone's entry forms back, he or she can simply click a button to pull everyone's picks into the master workbook. That'll be sweet, no?



Saturday, December 13, 2008

Pulling Images into Excel from Websites

You may recall a post I did just over a year ago about pulling images into Excel. That project involved pulling images from directories on your computer. I just completed another $50 Project where the client wanted to pull images into Excel from a website. Really the process was the same, except that this client is using Excel 2007, so I had to head Jonah's advice (see comment on that last project's post) and instead of directly pull the images using


I had to first draw an appropriately sized rectangle, move it to the appropriate cell, and "pull the image" by making it the background fill for that rectangle. Maybe someday Microsoft will fix this little idiosyncrasy in Excel 2007, but we'll not hold our breaths.

In the end it's a nice little file. Insiders, your file's on it's way.



Thursday, December 11, 2008

The Lawyer's Guide to Microsoft Excel 2007

I don't know why, but when a lawyer friend of mine sent me this, it struck me as terribly funny.

The Lawyer's Guide to Microsoft® Excel 2007

"Spreadsheet programs are one of the most powerful but underutilized tools in the trial lawyer's toolbox. John Tredennick's concise, clear book shows you how to use spreadsheets powerfully and quickly. I believe that every litigator should be familiar with the contents of this book." -- Joe Kashi, Esq., Author

Maybe it's just this Excel_Geek's frame of mind, but isn't a spreadsheet " of the most powerful but underutilized tools..." in almost any profession's toolbox?

No offense to John Tredennick. I'm a fan of anything and everything Excel, but what's next? One of these specialized titles?

The Rancher's Guide to Microsoft® Excel 2007
The Cab Driver's Guide to Microsoft® Excel 2007
The Hitchhiker's Guide to Microsoft® Excel 2007

Too funny.

Monday, December 08, 2008

2008-2009 College Football Bowl Spreadsheet

Remember that college football bowl pool spreadsheet I did last year (and the year before )?

Well...I guess it's now a holiday season tradition. Here's the same one updated for this season's match-ups. My gift to you.


This year's has been updated so that you can track up to 35 participants, and I've reformatted the individual charts sheet, so that it prints out nicely.

Merry Christmas,