Heat Charts are so easy in Excel 2007

Before you roll your eyes and say, "Welcome to life after 2003, Excel_Geek," let me just say, "I know, I know." The driver for this project was that I was analyzing my website traffic using my Google Analytics account. I noticed a few things.

One was that "heat chart" or "heat charts" seems to be a pretty popular search term for folks to find me. I've obviously done a couple of heat chart projects, but back when I was still using 2003, so in order to create them with more than four colors (the limit to Conditional Formatting in 2003), I had to do write some VBA code to accomplish it.

Another thing I noticed what the rhythmic up and down of my site traffic. There were a few major spikes -- March Madness season, college football bowl game season, and one recently when @MSExcel mentioned me in their Twitter feed. What I wanted to further analyze, though, was if there was any pattern to traffic patterns based on Day of Week and recency of blog posts.

I combined these two findings and decided to do a heat chart incorporating both Day of Week and recency to blog posts. Here's what the chart itself looks like:



Along the way I used some other favorite techniques along the way: indexing the data (so I'm not sharing my actual web stats...hey, that's my business), a trick to convert a list of dates into a list of days of the week, VLOOKUP using TRUE instead of FALSE (whatever for? right?), and using SUMPRODUCT to create COUNTIFS and SUMIFS functions that will be backward compatible to Excel 2003 and earlier. I highlighted the creation of the heat chart and these other techniques in the source file, which I'll be sharing with my Insiders Subscribers.

Later,

Excel_Geek

2007-2008 College Football Bowl Spreadsheet

Hey, anyone remember that college football bowl pool spreadsheet I did last year?

Well...Merry Christmas! Here's the same one updated for this season's match-ups. My gift to you.

0708_College_Bowl_Pool_worksheet.xls

Speaking of Christmas gifts, be sure to check out the Excel_Geek line of merchandise I've got up on Cafe Press. This guy's got one...man does he look cool.



Later,

Excel_Geek

Heat Charts, Another Creative In-Cell Charting Technique

I recently completed a $50 Project for a client who wanted to create visual "heat charts" from data in a matrix. What's a "heat chart" you ask? Well, basically, it's a color-coded representation of the relative data values contained in a data set. What?

Ok, so say you've got four columns of data in four rows: The columns represent the four seasons of the year, and the rows represent four different cities around the world. The data in the matrix might be the average midday temperature in each of those cities over each of the four seasons. A heat chart would represent this data showing the hottest (highest) value as bright red, and the coldest (lowest) value as deep blue, and everything in between would fall on that spectrum from red to orange to yellow to green to blue. Now, obviously, a heat chart can be used to represent any data, not just temperatures...

Here's a picture so you can see what the heck I mean:


(Click on the image to open it up larger in a new window.)

How's it work? Well, basically I just use some formulas to convert the values in the matrix into an "index" from 0 to 100, where 0 is the lowest value in the chart (or another user-specified minimum value) and 100 is the highest value in the chart (or another user-specified maximum value), and all other values in the chart fall somewhere, proportionately, in between.

Using the values in this "index" version of the chart, (using VLOOKUP) I reference each the RED, GREEN, and BLUE values for the RGB color representing that index value from a predefined COLOR SPECTRUM table.

Then, with a macro in the ThisWorkbook object, I automatically set the background color (using the RGB property in VBA -- i.e. "cel.Interior.Color = RGB(RED_VALUE, GREEN_VALUE, BLUE_VALUE") every time the sheet is changed.

This together with some general cell formatting, data validation, and some other macros to help automate adding and subtracting rows or columns, and there you have it.

Pretty cool if you ask me.

Later,

Excel_Geek

The Excel_Geek's Super College Bowl Game Pool Sheet

Yesterday a buddy of mine requested a project. He's a college football (pro football, any football, any sports, really) nut, and so he's involved in setting up a bowl game pool. What he wanted was a simple spreadsheet to keep track of everyone's picks, as well as how many points they allocated to those picks. Then as the games are played and winners are known, the sheet could be updated to see how the points standings are going.

He said he looked all over the internet, and there are tons of them out there, but everyone wants too much for them.

Being in the Christmas spirit, I decided to bang one out for him at a $50 discount. That's right...free.

The sheet is fairly straightforward. I've used sheet protection and data validation to ensure users don't "break" the sheet, and I'm using a bunch of conditional formatting to show which games are over, which picks were good ones, etc.

I also included a chart summarizing the current standings at any given point.

The real bonus (to me)is another sheet in the workbook which contains an in-cell tornado chart of each participant's ongoing results. The visual depiction helps you easily understand which picks gave you the most points versus those which did the most damage. Here's an image of those charts:



Merry Christmas, Jason, and merry Christmas to all of you Insiders subscribers who are getting this one, too!

More uses for In-cell Charting - Gantt Charts & Graphic Equalizer Display

I've been playing around for the past few days with other creative ways to utilize the in-cell charting technique I've posted about. Here are two of my current favorites:

1) You can use in-cell charts to create Gantt charts. Let me just say that creating Gantt charts in Excel is hardly breaking any new ground. John Peltier has shown us advanced techniques for doing this using Excel's built in charting feature, and Mr. Excel has shown us how to build Gantt charts using simple conditional formatting. I'm passing this along as just another way to accomplish a similar visual appearance. Below is a small image of what I've done (click on it to open a new window with a larger image):



To create the bars in the chart I use the following formula:

=REPT(" ",D5-G$4)&REPT("█",E5-D5+1)

You can see to accomplish what we need, we first repeat a space (" ") a number of times equal to the start date of the task minus the start of the timeline. Then we repeat ASCII code 219 (hold < ALT > while typing 2 then 1 then 9) a number of times equal to the end date of the task minus the start date of the task + 1. I've also done this one to use conditional formatting to color-code the bars based upon their STATUS. Pretty straightforward, I think.

2) You can use in-cell charting to create graphic displays, such as a graphic equalizer display. Below is a small image of what I've done (click on it to open a new window with a larger image):



In order to accomplish this look, I have used in-cell charting, only this time I've aligned the "text" (the bar symbols we're using) vertically. Also, since I wanted to have the first 5 bars be green, the next 3 be gold, and the last 2 be red, I've actually split the "bars" into three stacked cells, and adjusted the height of those rows accordingly.

In the lowest segment of each "bar" here's the formula I've used:

=IF(D$8>5,REPT("█",5),REPT("█",D$8))

In the middle segment, here's the formula:

=IF(D$8<6,"",IF(D$8-5>3,REPT("█",3),REPT("█",D$8-5)))

In the topmost segment, here's the formula:

=IF(D$8<9,"",IF(D$8-8>2,REPT("█",2),REPT("█",D$8-8)))

Add to this a little standard formatting with a black background, grey border with diagonal borders drawn on the corners for a 3D effect, and you've got yourself a nice looking graphic equalizer display. The possibilities from here are endless. You could feed this display the bar values dynamically from some other application using VBA, etc.

Additionally, this sort of chart could be used just as well as a regular bar chart in which you want to color-code segments.

I'll be packing up these two sample files and sending them off to my Excel_Geek Insiders subscribers, so you can play around with them and get your own great ideas. Please post a comment on this post if you come up with more cool ideas for in-cell charting.

Later,

Excel_Geek

More In-Cell Charting - Handy Little Add-In Called SparkMaker

I guess you could say the Excel_Geek's on a bit of an in-cell charting kick.

A couple weeks back, though, after reading one of my recent posts on in-cell charting, I reader shot me some information about a handy little Excel add-in called SparkMaker from a German company called Bissantz & Company GmbH.

Now, I think I've said it before, but I'll say it again: one of the single most useful applications for in-cell charting is dashboard reporting -- you know, cramming a lot of trend-type data onto a single page view to give users a quick glance at their overall operations. One thing I hadn't figured out how to do using the in-cell charting techniques I've described previously is how to get an entire trend series of data represented in a chart in a single cell. I mean, each of the "bars" you create in that way "lives" in its own cell. How can I get them all into one cell?

Meet SparkMaker. By using its own set of true type fonts and special functions, SparkMaker can do this in a heartbeat. Check out the image below (click it to open it larger in a new window):



I have an entire series of data that is important to me for trend analysis, though on my dashboard report, I really only need to see the current value, but it would be nice to also get an idea as to what the trend has been. Using SparkMaker, I create an in-cell line chart of the whole series of values. So I can see, "Nearly $49 million in revenues...nice...and it looks like we're on the upswing." All in two cells rather than having to derive it from a series of ten data points or using a big, clunky traditional Excel line chart.

Sparkmaker also does pie charts, which I really like for the same reason. How hard is it to get a traditional Excel pie chart small enough to fit nicely into a dashboard report? Hard. But with SparkMaker, it's a simple formula:

=SparkPie(C5)

Change the font size and color as you like, even dynamically, using conditional formatting, like this (click it to open it larger in a new window):



As I'm typing this I'm thinking of other ways I'd use these pie charts...

Now, at 199.95 (USD) for the "Pro" edition and 59.95 (USD) for the "Basic", SparkMaker is not free, but they do offer a try-it-before-you-buy-it free trial, and I recommend giving it a spin.

Later,

Excel_Geek

In-cell Charting - Tornado Chart Example

I'm sure that most of you, being Excel geeks like me, have heard about the technique to create so-called "in-cell" charts in Excel. The guys over at Juicy Analytics recently had a couple of posts on this subject that garnered a great deal of attention from fellow bloggers, with some people going as far as saying that this technique is superior to the built in feature to be included in Excel 12. I'll hold judgment there until I've personally had more time with Excel 12, but for right now, this in-cell charting technique is certainly a quick, easy, light-weight (to steal a term from the JA guys) means to do some fairly powerful data visualizations.

I thought I'd post a demonstration of how using this technique, one can create in-cell "tornado charts" -- a type of chart commonly used to visually depict various data points for two different groups. John Peltier's site shows a nice example of using Excel charts to create tornado charts, which I in no way want to diminish, but here's a simple example of using in-cell charting to do it, too.

A relatively common place to see tornado charts is for census data for men versus women, so I grabbed some quick data from the Statistical Abstract of the United States of America (one of my favorite sources of data) to get me started. Below is a small image of what I created:


(Click the image to view a larger version of the chart.)

See how much more visually appealing data can be in this style of chart?

The formula used in the top, "MALE" column looks like this:

=FIXED(E6,0,FALSE)&" "&REPT("█",ROUNDUP(E6/10000,0))

The special, rectangular character is ASCII code 219. It seems simple and I'm actually a bit embarrassed about it, but it was only recently that I learned that one can use ASCII codes directly in formulas (and in most applications, actually) by simply holding down the ALT key and typing the numeric code. Huh. █ I just did it again. Simple. Anyway, this code provides a nice solid bar.

The cell E6 contains the value I'm visually representing with the bar. Note that I use the FIXED function to display the whole numbers with the commas -- followed by a couple of spaces, then I repeat the ASCII code 219 to make the bar in the chart. Note that I divide the value by 10,000 so that the bar is not too long, then I roundup that value to the nearest whole number. What rounding up in this manner prevents is a situation where, say, the data is 219, and I'm dividing it by 10,000, which if simply rounded to the nearest whole number would result in zero, and therefore show no bar at all, despite there being data greater than zero. Rounding up provides at least one character in the bar to represent relatively small data points.

In the bars immediately following, I divide by only 1,000. Later on, I divide by 2,000, and on the last one, I divide by only 3. Each group is obviously showing a different scale, but it looks nice this way, and still maintains the visual comparison between the MALE and FEMALE data points.

By right-aligning the cells in the MALE column, they line up along the center line. I then repeat a very similar formula in the FEMALE column:

=REPT("█",ROUNDUP(F6/10000,0))&" "&FIXED(F6,0,FALSE)

Note that in this formula the bar comes before the value it represents (since it's on the right), and I align these cells to the left. BLUE vs. PINK color-coding for MALE vs. FEMALE is typical (at least in the United States), so I thought that was a nice touch.

This example is a fairly large chart, but one of the really nice applications for in-cell charting is dashboard reporting, where many times you're trying to squeeze eight or ten or sixteen individual charts into a one page document. In-cell charts can fit that bill very nicely.

I'm very curious to learn if any of you have done cool things with this in-cell charting technique, so please pass along any thoughts or examples -- via commenting on this post, emailing me, or by using my Meebo Me chat feature (Like how it now scrolls up and down with you?). I'll be sending out this example file to my Excel_Geek Insiders subscribers for their enjoyment, as well.

Until later,

Excel_Geek