Complex Heat Map using conditional formatting

Corried

Board Regular
Joined
Dec 19, 2019
Messages
217
Office Version
  1. 2019
Platform
  1. Windows
  2. Web
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:
Albania.PNG

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:
Argentina.PNG

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.
heat (1).PNG


What do you suggest I do?
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
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
 
Upvote 0
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
Hummm. Thanks for your swift reply.

I am sorry but how am I going to do that?
 
Upvote 0
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?

Thanks in advance
 
Upvote 0
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.
 
Upvote 0
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.
Thank you for your reply. Please. I am embarrass already. I have understood what you are saying.
The problem is, I do know how...?
If you are willing. What are the steps I need to make it work?

Thanks in advance
 
Upvote 0
Corried, use the ABS formula like literally type =ABS(A1) which would change cell A1 to a positive number
 
Upvote 0
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?

Thanks in advance
 
Upvote 0

Forum statistics

Threads
1,212,927
Messages
6,110,729
Members
448,294
Latest member
jmjmjmjmjmjm

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top