• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

24 hour heat map

AitchK

Member
I'd like advice from you guys. I want to create a heat map for a 24 hour period. X-axis = days of week, updateable to months of year Y-axis = minutes 1-1440 (1440 minutes = 24 hours).
Requirements:
1.needs to fit on a single screen
2. conditionally formatted red or green. If device was in use for a minute in corresponding Y-axis hour, it is to be red, if it was idle it is to be green.
3. refreshable, using sql query as basis of data.
4. Y-axis labels divided into 24 tick marks for 24 hours (even though accuracy within the chart is to the minute)
5. X-axis labels divided into days of week, and changeable to months of year ie. chart able to handle both.

The first hurdle I'm up against is the fact that the smallest row height that I've been able to set in Excel 2013 is 1. 1440 rows with a height of 1 is still to high to fit on one screen (don't want to use scroll bar). If I go any smaller the rows just disappear altogether.

The second worry I have is that I be able to make the X-axis easily shift from days of week, to months of year, and that the x-axis shows days of week or months of year.

Can you advise if you have ideas for making this happen or know of examples on-line I can review?

I know about conditional formatting and have no problem importing the data. But, usually I just pivot the stuff.
Actual scenario is device utilization. I want to produce a heat map for each device in our lab, which does the work of backing up data. For planning purposes it would be good to know when the devices are not in use, so we can schedule new backup jobs in those idle windows. I'd like a heat map for each of these devices, to show historically when they tend to be in use and when they're idle.
 

AitchK

Member
Hi Faseeh, Thanks for replying. I looked at examples. Wiki says "A sparkline is a very small line chart, typically drawn without axes or coordinates." I do need to have x-y axis visible so that a red/green section of the chart can be correlated with time of day in y-axis, and day or month on x-axis. Also, the chart must be informational, so I do need the chart to be big enough for that correlation to be clear. Also, I should do a quick check to see if our sharepoint site, which is currently hosting the excel reports, can also display sparklines...So, if sparkline charts are versatile enough to handle these requirements, then yes.

If no, any other suggestions for making this chart in Excel 2013?

EDIT. have just tried to add a sparkline and getting error "the reference for the location or data range is not valid". I have removed $ signs as some forums suggest and not able to add....so looks like a show stopper for Sparklines.
 

Faseeh

Excel Ninja
Hi Aitchk,

If you just click the word sparkline in my last post it will open a new page for you :) , i will also upload a sample shortly!
 

AitchK

Member
Hi, If it helps, I've posted a sample file. Devicename is LTO5DRV2 which was in use from Oct 7, 2013 at 11:59PM through Oct 9, 2013 at 3:05AM. Heat map for Oct 7 would show a vertical chunk of green as most of the day that drive was not in use (til a minute before midnight), then it would show a vertical chunk of red for Oct 8, on which it was busy all day, and then a small chuck of vertical red for the 3 hours it was busy on Oct 9 with the remainder of the vertical axis green. It picked up another job after that, for which I didn't include data.
Most utilization for most drives start in the evening and ends around midnight, so normally would see clustering of green around that time....
 

Attachments

Faseeh

Excel Ninja
Hi, Usually a heat map has two dimensional data, on one we can place dates or time, on second axis we can place day+time but it is all 8 & 9s.
 

AitchK

Member
True, I'll have to join on a calendar so that I have placeholders for those days/times where nothing is happening on that drive. But the data gives you a general idea. Yes, y-axis will be the device itself on any given day, the x-axis will be time of day.
 

Faseeh

Excel Ninja
I believe that we are slowley moving towards a simple bar char. Let me explain, a heat map would have been desireable iif we have devices on one axis and times on other, with just one device it needs a simple bar chart.

Its 1,30 am so will continue tomorow. Gud Night....
 

AitchK

Member
the problem with a bar chart is that it rises or falls with utilization. I do not need merely illustration of how much but when, that's why I need to plot what *part* of day it is getting utilized and am looking into heat map.
 

Faseeh

Excel Ninja
Hi Aitchk,

I just read ur post, was unable to respond and feedack due to a field visit. Let me workout and i revert tomorow with some solution, hopefully. Thanks
 

AitchK

Member
Faseeh, I think this is good. I will have narrower ranges for x-axis and will need more rows. I can expand to as many rows as I want, correct?
To practice I grabbed the chart you made A1->G9 and copied into a new Excel sheet, highlighted it, and went to Insert a Sparkline. Keep getting that error..."location reference is not valid or the data range is not valid"
I see a lot of people battling this error, but their solution, removing $ signs from cell reference, doesn't work for me. Any ideas?
Otherwise, thanks for your generous help in getting me toward a heat map that'll work on one screen....less than 1440 row but capable of showing times of day when utilization is heaviest.
I appreciate your help and I will use this.
 

Hui

Excel Ninja
Staff member
In Faseeh's last example
delete the 4 charts in the J2:O9 area
In J2: =A3
Copy across and down to O9

With J2:O9 selected
Conditional Formatting
Color Scales
Select or setup your own color scale

With J2:O9 selected
Ctrl 1
Number, Custom
Type the format ;;;
Set Borders of the cells to White

or see attached
 

Attachments

Hui

Excel Ninja
Staff member
Except that it isn't a Sparkline
Its a Heat Map using Conditional Formatting
 
Top