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.
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.