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

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

ActiveSheet.Pictures.Insert().Select

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.

Later,

Excel_Geek

Excel 12 - Cool feature I've wanted forever...remove duplicates

I've been frantically reading the blog I posted for all of your to read yesterday (http://blogs.msdn.com/excel/), and while I first mentioned my excitement for really digging into the web services being rolled out, here's a wonderful feature that I've been begging them for forever: automated duplicate removal.

That's right. I've spent countless small chunks of my life adding temporary columns to data tables and entering formulas like...

=IF(B3=B2,"REMOVE","")

Then sorting the entire data table by that new column, and deleting all rows containing "REMOVE" in them.

No more. Check it out at http://blogs.msdn.com/excel/archive/2005/11/04/489259.aspx.

Getting excited for 12 yet?

Microsoft Excel 12 - Check this blog...

More and more of you have been asking for information on Excel 12.

Well, a friend of mine (six-forty.net) tipped me off on this blog: http://blogs.msdn.com/excel/.

This blog features some indepth discussion of the new services to be included in 12. To be short: WOW. I cannot wait to get my hands on this. The web services stuff is awesome, and I am already thinking of the cool stuff I'll be able to build...