Debra D. is currently on a mission at the Contextures Blog to cover 30 Excel Functions in 30 days. Quite the feat, considering since she also cranks out a video tutorial with each one. Anyway, the 10th function she covered was HLOOKUP. And as you may have guessed, poor old HLOOKUP didn’t even get one comment. So I’d like to give you my take on why I use the HLOOKUP function is some of my dashboard models.
Posts Tagged ‘Visualization’
Using HLOOKUP in Dashboard Models
January 17th, 2011Stacked Thermometer Chart
January 13th, 2011Wendi wrote me an email the other day asking:
“Datapig you are best Excel persons in the world … how to make thermometer chart with a stacked column?”
My first international lady fan! Could it be true? Could I be offically too sexy for just the Continental U.S.?
While I ponder that thought, here is Wendi’s answer.…
10 Slick Dashboarding Ideas
December 14th, 2010Last week, I put on a Webinar called ’10 Slick Dashboarding Ideas’.
Since it’s a pretty lazy week for most of you due to the holidays, you may have the time to watch a one hour webinar.
If you’re interested enough (and you’d like to hear my sexy whiskey laden voice) click on the picture below.
Note: I set the video to play for you automatically, and you have all the navigation controls. Enjoy!
» More: 10 Slick Dashboarding Ideas
Excel Hero and Animated Charts
October 22nd, 2010Daniel Ferry is the purveyor of one of the more interesting Excel Blogs out there (ExcelHero.com). His blog has the curious distinction of being the only one with an unexplicable rooster on the banner:
If you’ve never visted, you must. Daniel has a knack for passing on advanced Excel techniques through
» More: Excel Hero and Animated Charts
Creating a Zero Centered Scroll Bar
October 12th, 2010Last January, I published a Pulitzer-worthy post about configuring the Scroll Bar control to work with Percentages. In the comments of that post, m-b
shared a technique he uses to create a zero-centered scrollbar. That is, configuring your Scroll Bar so that the middle value is 0, the lowest value is -100% and the highest value is 100%.
Excel Report Layout Trick
September 8th, 2010Last night, I looked up my book “Excel Reports and Dashboards” on Amazon only to find that my first reviewer gave me 3 out of 5 stars. He gives a bunch of reasons, mostly having to do with how advanced he is and how the book is too basic for his needs. But that’s not the interesting bit. In his review he says:
“My own favorite technique — setting all of a worksheet’s columns to be very narrow and then merging cells to position the information exactly where I want it — isn’t mentioned”
Interestingly enough, I’ve been using that trick for several years now. I’ve always found this trick difficult to explain verbally, let alone in print. But I’ll give it a shot here.
Selecting Hard to Reach Chart Elements
September 6th, 2010Anyone who has worked with Excel charts extensively has inevitably encountered the “hard to reach” scenario. That is, the formatting and configuration of your chart makes certain chart elements seemingly unreachable. Today, I’ll show you a forehead-slapping trick that will both give you relief, and make you feel stupid at the same time.
Selectively Hide AutoFilter Dropdowns
May 20th, 2010During one of my nerdy adventures, I came across the need to selectively hide AutoFilter Dropdowns. That is to say, apply an AutoFilter to a table, but only let the user see and interact with one or two of the dropdowns. Here’s the deal:
The Trouble with Chart Data Tables
April 26th, 2010Chart Data Tables are actually very useful for showing plotted values without inundating your audience with a gaggle of chart labels. I use them all the time.
My problem with them is that their uglier than a bag of lips. For all their usefulness, they sure do emit that “1997″ feel. Luckily, Excel gives you a few options that help dial back the “ugly” on your chart Data Tables.
Sometimes Two Charts are Better than One
April 19th, 2010Last week, I was caught in a vortex of executive reviews, publishing deadlines, and other miscellaneous family stuff. (Note the placement and miscellaneous status given to “family stuff”. This is why I’m going to die alone.)
In any case, during the executive review prep, we had a bit of a debate on how a certain set of data should be represented.
The basic idea is that we have a FY 2010 goal. Each month, we add values to get us to that goal. The question is how to show this on a chart.
