Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I have data in three columns, one for the x axis, one for the y axis and one actual data as:

X   Y   Value
0   0   1,005155
0   1   2,051385
0   2   1,15305
...
1   0   1,112285
1   1   1,48512
1   2   0,6085
etc..

How can i plot this data in a 2d graph/image, with increasing color intensity for increasing value?

share|improve this question
    
Well, it is a little difficult to understand what kind of graph output you want to have... Do you want something like that: wikicrimes.org/main.html (it's like a color density graph, hotspot, in Brazil's map)? If so, I think Excel won't do it and you would use a program like Matlab, Scilab or Octave. Excel has "bubbles" graph, that is a little similar... Else, please, create a drawing of it and share it here... –  kokbira Sep 19 '11 at 12:09

1 Answer 1

up vote 3 down vote accepted

Two options I see in Excel, one bland and one more interesting. Bland first:

1. Create a Surface Contour Chart in Excel.

To do this, just select your three columns of data, then insert a Contour chart (listed under Other Charts->Surface in Excel 2007). You'll have to add your series manually, but it can be done with a little effort. Please note that Excel does interpolation between your data points to create the contour map. Most of the time this is a nice feature, but beware that how you define your ranges can have a major effect on the contours Excel interpolates.

Quick Sample:
contour plot example

2. Use Conditional Formatting to Create a Heatmap Within Your Spreadsheet

This requires some data transformation, but the output looks pretty nice, especially for large fine-grained representations. First, you'll need to add a column to the left of your table. This will hold a lookup value that will be used to populate your heat map. In this column you need a key that uniquely identifies each x-y pair in your data. In my example below, I used the formula `=B2&"-"&C2'. Fill this formula down.

Next, you'll need to set up a table on your sheet that mimics an x-y graph. So, x on the horizontal (at the top or bottom, your preference), and y on the vertical with descending values. Once you've done this, you can use a VLOOKUP function to populate the heat map. You'll need something like this: =VLOOKUP(G$11&"-"&$F2,$A$2:$D$46,4,FALSE). Note that in the first argument, the row number for the x labels and the column for the y labels is fixed. This allows you to fill this formula throughout the table for your heat map.

Finally, select the values in the table you just created and apply conditional formatting to them. You can either use one of the pre-defined color scales or create your own to match your needs.

Example:
heat map example

share|improve this answer
    
That's pretty cool. –  music2myear Sep 19 '11 at 16:55
    
This is exactly what i wanted, thank you! –  Theodor Sep 24 '11 at 11:04

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.