# Complex Heat Map using conditional formatting

#### Corried

##### Board Regular
Good day excel world.

I have a concern.
Below link have a list of countries that carries each country unique data.

Now let's take the 1st two example:
#1 Country: Albania Data: -13.60
Below is Albania historical values snapshot:

If you notice all there historical data are measure with a (-) number.

and

#2 Country: Argentina Data: 42.40
Below is Argentina Historical values snapshot:

If you notice all there historical data are measure with a positive number.

My problem is this:
How can I use 3-Color Scale "Heat map", using conditional formatting, to show it correct color code for each country?

Below is the WRONG EXAMPLE of conditional formatting use directly.

What do you suggest I do?

#### AlanY

##### Well-known Member
create an additional column, add the max -ve numbers to all thus create a column with only positive numbers.
and use this column to CF the real data

#### Corried

##### Board Regular
create an additional column, add the max -ve numbers to all thus create a column with only positive numbers.
and use this column to CF the real data

I am sorry but how am I going to do that?

#### Corried

##### Board Regular
create an additional column, add the max -ve numbers to all thus create a column with only positive numbers.
and use this column to CF the real data
AlanY. Hello. Please point me to the right direction. I am new to excel.

What do you mean by "Max-ve" and "CF"?
How am I going to get this done?

#### svendiamond

##### Well-known Member
He probably meant to say get the absolute value. The ABS() function will change them all to positive numbers. Use that for your conditional formatting.

#### Corried

##### Board Regular
He probably meant to say get the absolute value. The ABS() function will change them all to positive numbers. Use that for your conditional formatting.
The problem is, I do know how...?
If you are willing. What are the steps I need to make it work?

#### sandy666

##### Well-known Member
you can re-download heat2 example , I did ABS for column Last

#### Corried

##### Board Regular
Here is an example of a heat map

#### svendiamond

##### Well-known Member
Corried, use the ABS formula like literally type =ABS(A1) which would change cell A1 to a positive number

#### Corried

##### Board Regular
Corried, use the ABS formula like literally type =ABS(A1) which would change cell A1 to a positive number
I got you. The problem is: some countries carries both negative and positive numbers while, a few carries only negative numbers. If I change all to positive. How that is going to help me move forward?