Challenge creating heat map-data carries there own unique values

[Corried]

Hello Excel World.


I'm in the process of creating a 3-Color Scale "Heat map", using conditional formatting for investment purposes.

But I am having some challenges in creating the heat map, because each data carries there own unique values and may differ extremely from each other.

See Link below, of countries listings and the values outline under the header "LAST"

Consumer Confidence - Countries - List

This page displays a table with actual values, consensus figures, forecasts, statistics and historical data charts for - Consumer Confidence. This page provides values for Consumer Confidence reported in several countries. The table has current values for Consumer Confidence, previous releases...

tradingeconomics.com

I also thought with myself. If the heat map should work effectively, each value or each cell reference should have there own heat map values.

But what is the effective way in creating these values or heat map ratio.

See link below of one of the example given.

Albania Consumer Confidence

Consumer Confidence in Albania decreased to -27.61 points in February from -26.82 points in January of 2024. This page provides - Albania Consumer Confidence - actual values, historical data, forecast, chart, statistics, economic calendar and news.

tradingeconomics.com

In conclusion.
What is the best direction to create this?
What are your experiences if any?
How do you want me to proceed?

Thanks in advance

 

[sandy666]

do you want data and you will create map by yourself?

 

[Corried]

"do you want data and you will create map by yourself"?

Yes. I will want data.
And yes. I will like to create map by myself... The problem is. I am looking for some guidance to create the heat map as each categories values are very different.

What do you recommend me to do?

 

[sandy666]

do you want data from all countries at once?
it will be a bit complicated because the same data (except values) for different countries are at different tables, eg. Table1, Table2, Table3
but you can try Power Query (Get&Transform) as you can see on the pictures in my previous post

 

[Corried]

Thank you for your swift reply. I am graceful for someone who have a similar requirements like mines .
For example:
If we look at your image. The header; "Consumer Confidence". We can see Albania have a value of -13.6 and Belgium value is -8.

Example:
1.
Albania’s consumer confidence index decreased to -13.6 in the last quarter of 2019 from -12.3 in the previous three-month period. It was the lowest reading in consumer morale since the first quarter of 2015, as expectations over the next 12 months worsened regarding financial situation (6.1 from 7.8 in Q3); general economic situation (4.0 from 6.5); savings (-44.4 from -43.6) and major purchases (-52.3 from -49.0). On the other hand, expectations about unemployment (1.5 from 5.4) improved. Consumer Confidence in Albania averaged -12.27 points from 2003 until 2019, reaching an all time high of -6.30 points in the fourth quarter of 2011 and a record low of -19 points in the fourth quarter of 2004.

Example
2.
Belgium's consumer confidence indicator dropped 2 points from a month earlier to -8 in December 2019, as consumers have become more pessimistic about macroeconomic developments in Belgium (-17 vs -14 in November). In addition, households are expecting a rise in unemployment over the next 12 months (11 vs 7) and deterioration in their savings (-5 vs -1) while they expect the same for their financial situation (-2). Consumer Confidence in Belgium averaged -7.15 Percentage Points from 1985 until 2019, reaching an all time high of 16 Percentage Points in August of 2000 and a record low of -28 Percentage Points in January of 1985.

Each country "Consumer Confidence" matrix are different. How can I customize the heat-map format to compliment each country data?

 

[sandy666]

it was an example
you need to say which table you want to use
each country has almost the same number of tables on its page. I can show you how to download data, but for example Albania - Table2 and Canada - Table1, so this is a problem.
You can transform data in PowerQuery Editor but I don't know what you want to do.
here is an example

you can change country via DV dropdown (blue small table) then refresh green table (right click and select refresh)
but if you choose eg. Vietnam or Canada you will get error because of different name of the table, incompatible with eg. Albania or Belgium

but you didn't answer for this

do you want data from all countries at once?

 

[Corried]

Thank you again for your response. I am sorry for not been clear. The example photo below is what I want.
I want 3-Color Scale "Heat map", using conditional formatting for investment purposes.
The heat map is to show each country's strength and weakness by using the color coding.

The problem here is this. The color code seems not to represent each country correct value because. Each country chart requirements are unique or different.

How can I fix this?

 

[sandy666]

as I said I can help with the data not with the logic, but what you will do it's up to you
example

maybe use Range to define your tri-color ?

 

[Corried]

Ok. No problem.

do you want data and you will create map by yourself?

View attachment 4435
View attachment 4436

I will take all data then.

 

[Corried]

Do you have any recommendations of who can help me with the heat map?