Does anybody have a clue how to create a heat map as per this page?
http://how.best-free-information.com...-map-in-excel/
I specifically need to represent 3 things on the same chart: time of the day/day of the week/usage. But once I have the system i can apply it to anything really.
I also need something that looks a bit stylish not just colored cells using conditional formatting.
I guess I need a plug-in, but even looking in google I can't find anything good.
Any advice?
Thanks!
rs232
Last edited by rs232; 01-05-2010 at 07:48 AM.
That heat map is a surface chart.
You need a matrix of data with, for example
Please Login or Register to view this content.
I see.... thanks I completely missed the surface chart :-)
So, let me understand your answer: let's say I have 6 month worth of data and I'd like to create a heat map that looks like:
I can get the values I need out of my SQL query filling in a single value per "cell". Is it all I need?Please Login or Register to view this content.
Thanks again!
Yes the data layout would be almost the same as the surface map layout except the first row would be the Day labels and the first column the Timeslots.
One thing to keep in mind is that the x and y axes of these type of charts are categorical. So as long as you have equi spacing values you will be alright.
If you have 24 time slots and 5 days the plot will be skinny!
Great thanks!
I though about it being skinny but I think I'm going to play with the chart setting to "expand" the space taken by the weekdays.
I'll give ti a go and let you know.
Thanks!!
There are no setting to expand the axis.
Hum... I need to work out a solution here, possibly just reduce the data points to business hours (8:00 to 17:00) would help.
Any ways heatmap is done and looks beautiful :-)
Just another thing, can I manually change the colors?
Blue - yellow -red would be my personal preference for low - mid -high as per the link I posted above...
To change the colours use the Legend.
Select a legend entry and CTRL+1 to display Format dialog.
The scale tab will allow you to change the banding.
Thanks very helpful!
just another question:
is there any way to define the number of divisions (colours)?
By default I get 4 (0-10, 10-20, 20-30, 30-40) as the max value I have is 38ish.
I've seen charts around with 10-12 colours....
If I add some dummy value e.g. 200 here and there it still use 4 colours but it divides by 0-50 ,50-100, 100-150, 150-200
As I said previously the bands are controls by the Major Unit value of the Z axis.
If you format a legend entry you should see the Scale tab on the format dialog.
I'm sure it's me but I'm not able to find the Z axis parameter...
I did find the way to scale the chart though (as I have only 5 week days)!
It's the paramenter "Depth (% of base)" under 3-D rotation/3-D rotation
Now the chart doesn't look too slim.
Last edited by rs232; 01-05-2010 at 09:25 AM.
I can't tell much from a picture.
See attached, which includes a screen shot of the format dialog.
I admit I feel a bit stupid asking this question, but how do you get to the format legend window?
If I right click on the legend and select "format legend..." I get the windows as per the image in attachment
Ah, you need to let us know you are using xl2007 to start with
Select the chart
Chart Tools > Layout > Current Selection
from the dropdown list pick Vertical (Value) Axis
Format Selection
lol even with your instruction on screen it took me 2 minutes to find the right menu. It's so differen from excel 2003!
Any ways: success!
This post deserves a double [SOLVED]he he.
Thanks again!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks