chartsmicrosoft excel
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?
The simplest solution is to use "helper" columns to break each category into its own series, which can then be independantly formatted. For instance, in Column D:
Here's an example of what it could look like:
To further simplify things, you can format your data as an Excel Table and that will speed up the creation of your categories by automatically filling the series data. Here's what it could look like:
You can do this in Excel!!! In fact, there are at least two ways to create a chart like this:
The first method is easiest, but requires a free add-in (that every Excel user probably should have anyway), Rob Bovey's XY Chart Labeler.
Otherwise, you can do it this way.
I'd strongly recommend the first method, or using VBA to expedite the second.
Best Answer
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:
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:
