Creating Heat Maps with Text Data

What is it that you want to do with this data.  A Heat Map is a MAP.

What pieces of info is it that you want to chart?

...took the words right out of my mouth.

Shaw,

Although a map does not have to be geographic, it could simply be a 2D table (i.e. something that you can make a 3D chart from.

You need to identify your column headings and row headings and the data that corresponds to each.  For example, with numeric data, you can do this:

Is that what you were envisaging when you said "heat map"?

If so, like I said, you need to somehow assign numeric values to your text data.  This could either be based on its alphabetic position in a list, or on the frequency of its occurrence, or some other meaning (like in the "one", "two", "three" example I gave).  So give us a table like the above, and tell us what makes one piece of text "hot" and another piece of text "cold" and we should be able to help.

Cheers

Rich

What is it that you want to do with this data.  A Heat Map is a MAP, there is no geographic component to your data.

The Definition

According to Wikipedia

“a choropleth map (Greek χωρα + πληθαίν:, ("area/region" + "multiply") is a thematic map in which areas are shaded or patterned in proportion to the measurement of the statistical variable being displayed on the map, such as population density or per-capita income.”

What pieces of info is it that you want to chart?

Choropleth Maps with Excel / Thematic Maps / Heat map

http://www.clearlyandsimply.com/clearly_and_simply/2009/06/choropleth-maps-with-excel.html

A Set of Choropleth Map Templates for Microsoft Excel

Then again, I found this link that may meet your need:

Heat map spreadsheet 1.0 - Download Template

http://www.topshareware.com/Heat-map-spreadsheet-download-96405.htm

This is a Microsoft? Excel? based heat map excel spreadsheet templates, it is an excellent analysis spreadsheet tools for increasing sales revenue, identifying desirable target markets, lowering your products costs, find out rhe project critical spots or adding to your projects management presentations.

Dashboard example – conditional colors of shapes

http://www.tushar-mehta.com/publish_train/xl_vba_cases/0301-dashboard-conditional_shape_colors.htm

Download a sample file Unzip the downloaded file and extract the XLSX and the XLS files to the folder of your choice and open in Excel

Hi

I have not thought about the "alphabetical position relative to the other text". How would I do that? Please see sample of my data: https://www.dropbox.com/s/8y929go31fyboji/Sample_Data_for_Heat_Map.xlsx 

(https://www.dropbox.com/s/8y929go31fyboji/Sample\_Data\_for\_Heat\_Map.xlsx)

I have rated the text, but I do not like that map it produces.

Regards

Shaw

Rich, thanks for making a point I missed, excel works best with numbers. So you have to convert some of the text into numbers that create ranking of importance. The numbers can be hidden if you want a purely graphic interpretation of the data.

Another mistake I made was letting the "user" define the terms.  Your example is a good alternative to a geographic heat map type chart.  Here are a couple more (numeric) chart types Shawsin may want to consider, Surface and Contour charts:

Surface and Contour Charts in Microsoft Excel (TechTrax Article)

Surface Chart:- **Click here to download this file**

http://peltiertech.com/WordPress/contour-and-surface-charts-in-excel-2007/

(examples from TechTrax article)

Surface chart

Contour chart

Hi,

What would the text data be?  Are you looking to colour the text based on it's alphabetical position relative to the other text, or do the text strings have some kind of "value", for example:

"one", "two", "three".

In Excel 2007, you can select a range of cells containing numbers and do Home tab > Conditional Formatting > Color Scales...

but that only works on numeric values....

Tell us some examples of the text, and how you rate them or assign some form of "value" to them and we might be able to suggest a way of doing what you need.

Cheers

Rich