Excel 2010 Conditional Formatting Question (Heat Map)

**CasinoMogul** · 06-22-2013, 01:39 AM

Hello,

I've created a heat map of the US in Excel showing % unit sales by city.
Each cell is vlooking it's respective data from a workbook tab.

The cells I am applying conditional formatting to are city sale percent of total US sales (1,000 units sold in Chi. / 100,000 total unit sales in US, i.e. 1%), which works fine.
The problem is that I am also providing the sales figures (1,000 units) in a cell next to the city names, which is far greater than the adjacent percentages.

I am struggling with creating a conditional formatting formula that tells Excel to ignore the unit sales cells (1,000 units) while at the same time applying conditional formatting to cells containing sale percentages (0.4%).
And by conditional formatting I specifically want color ranges based on values, similar to a heat map, rather than just coloring all cells within a value range the color orange, for example.

Visual Example:

I want the conditional formatting to ignore any number above 1 (i.e. the 1,000 & 3,000), while also applying color ranges to the 1% and 3% proportional to the % value, not just a standard format that does not change color based on each cell's respective value.

(Each below number is an Excel cell)
1% 1% 1% 1% 1% 1% 1% 1% 1% 1% 3% 3% 3% 3% 3% 3% 3% 3% 3% 3% 3% 3%
1% 1% 1% 1% 1,000 1% 1% 1% 1% 3% 3% 3% 3% 3% 3% 2,000 3% 3% 3% 3% 3%
1% 1% 1% 1% 1% 1% 1% 1% 1% 1% 3% 3% 3% 3% 3% 3% 3% 3% 3% 3% 3% 3%

Thanks for your help & please let me know if I can be more specific.

Regards.

**tahi.laci** · 06-22-2013, 04:04 AM

If you always put the unit sales in the same cell you can just make the selection of the conditional formatting ignore that cell.

However depending on exactly what you want to do you can use formula to format for example:

(replace a1 with the uppermost and leftmost cell of your selection)

=and(a1>0.02,a1<=0.03)

Choose the formatting, do this for each you want to use and any number which doesn't fit any of the criteria you give will be just left white.

**CasinoMogul** · 06-24-2013, 02:20 PM

Thanks tahi.laci for the reply.

The problem I have is that there are so many cells I don't want to be formatted (100+) that the conditional formatting does not work properly when I attempt to do so.

Also, I don't just want cells I want to format to simply be formatted, I want the heat map color scheme to apply as well.
For example, I don't want all cells under 1 to simply be orange, or bold, rather, I want all cells under 1 to be formatted in a range from blue (cold) to red (hot) based on thier respective percentages while at the same time excluding cells above 1 from entering into the color formatting equation.

I also tried doing a basic condition formatting (Blue - Red) and then individually 'Clear Formatting' from each individual cell (100+) that I didn't want formatted. The problem is that this works until I hit about 40 or so cells, then the conditional formatting for the overall US turns white & essentially breaks.

Thanks for you help on this!

**tahi.laci** · 06-24-2013, 04:02 PM

I've just noticed that color scale gives more than 2-3 values. Unfortunately the color scales don't accept formulas, and you can't use "proper" color scale (e.g. more than a few colors) when you use a formula for conditional formatting. At this point I have to know whether the place of the numbers which are >1 changes or constant. If they are always at the same place it's easiest to leave them out of the original selection, if that's not feasible, I will have to use VBA to do this, which will be rather complicated.

**tahi.laci** · 06-24-2013, 06:20 PM

I managed to do it the hard way. See attachment. And I mean HARD...

The basic idea is that in a separate table I put TURE values if I want to select that cell and FALSE if I don't, then I use two for loops to add extra cells to the selection with

Please Login or Register  to view this content.

when the corresponding value is true. Then I make conditional formatting for selection.

First I made a table of random numbers which are either percentages or big numbers. Every time you change something on the spreadsheet or press calculate sheet these numbers get recalculated, so you can see it still works when something changes. I made it so that small percentages are blue, big ones are red and big numbers are ignored (left white). Unfortunately to do this I had to remove all conditional formatting in the VBA code for that selection, so you can only add additional conditional formatting in code. (Curently everything is "0.00%" and big numbers are "general"). If you want to put the table somewhere else you have to change the reference in W4 (R1C1 style) There are some calculations hidden on the left. If you want to use a bigger table than 12 coloumns and 10 rows you have to autofill the table at: A19:M29.

In case you want to change the colors of the heat map:
View ->Macros ->record macro ->Ok
Home -> Conditional Formatting -> New rule
Change the colors as you want
View ->Macros ->stop recording
Replace the "Colors" macro with the freshly recorded one.

If you're happy with my answer you could change the question to: "Exclude cells based on value from conditional formatting (Color Scales)" so that someone can find it easier in the future.