Pages

▼

Sunday, April 14, 2013

Geographic heat map - India (Excel template)

I was looking for a way to present/visualize state level data on an Indian map. I was not able to get that from the Bing maps application for Excel. I am sure there are paid applications that might provide this functionality.  I think this is a very basic need and I decided to prepare a simple template that can help create geographic heat maps for India.
Geographic Heat Map (India)
If you would like to create heat maps like the one above, you can Download the free Geographic Heat Map (India) Excel template (Telangana added in June 2014)

 Please see below, a quick 5 minute demo of this template.



Geographic Heat Map (India) - Excel template

 How to use this template?
  • Enter your (numeric) data in the data column and the map image on the right gets updated. 
  • Please make sure that the state (or union territory) names you enter in column A are spelled the same way as they are
    • They are currently ordered alphabetically. However, you may choose to enter them in any order. 
  • You can print the sheet if needed
  • You can copy the image and paste it in other applications such as Microsoft Word or PowerPoint
Showcasing a state

You can also use this template to showcase the position of one specific state. For the illustration above, I entered a positive number (1) for Madhya Pradesh and deleted the data for all other States/UTs. 

How is the heat map generated?

 The template has a hidden worksheet where the outline of India and States/Union territories were drawn with the cells pointing to corresponding data. Conditional formatting has been used to create the heat map. If you would like to change the colour gradient, read this article.

 If you know of better ways to do this or if you use any application to create such heat maps for India, please share your thoughts.

39 comments:

  1. UnknownJuly 19, 2013 at 5:44 AM

    how can we change colours as green lowest and Red highest?

    ReplyDelete
    Replies
    1. UnknownJuly 19, 2013 at 7:53 AM

      Hello Nisha Sachan,
      It's done using conditional formatting on the hidden 'Data' sheet. If you are familiar with Excel, you can unhide the 'Data' sheet and change conditional formatting. If you are not familiar with condistional formatting, I can either change the colours (Green lowest and Red highest)and e-mail a new template to you. Or I can provide instructions/video to explain how to change colours. Please let me know what you prefer.
      Thanks for using the template. I hope it's useful.

      Delete
    2. UnknownJuly 20, 2013 at 11:02 AM

      Thanks for the revert was able to reverse the gradient,can we be able to mention numbers as well on the states along with the colors.

      Delete
    3. UnknownJuly 30, 2013 at 9:10 PM

      Yes, we can insert text box (or Shape) for each state in the hidden 'Data' sheet and in the formula bar point to the corresponding cells (numbers) in the Geographic_Heat_Map sheet. These text boxes with numbers will automatically appear on the map in the Geographic_Heat_Map sheet. If you would like a copy of the template with the numbers, please e-mail at indzara at gmail.

      Delete
  2. UnknownAugust 9, 2013 at 11:38 PM

    Dear Indzara,

    Greetings, Your map is excellent, how can we change colours as green lowest and Red highest.

    Please help.

    Thanks
    Kamalesh

    ReplyDelete
    Replies
    1. UnknownAugust 16, 2013 at 6:08 AM

      I have written a new article about that. Please visit
      http://indzara.blogspot.com/2013/08/changing-colour-gradient-in-heat-map.html

      I hope it helps.

      Delete
  3. UnknownAugust 18, 2013 at 8:56 AM

    Indzara your map is excellent

    ReplyDelete
  4. UnknownAugust 18, 2013 at 8:56 AM

    Indzara your map is excellent

    ReplyDelete
    Replies
    1. UnknownAugust 18, 2013 at 9:50 AM

      Thank you very much. I am glad it was helpful.

      Delete
  5. UnknownAugust 19, 2013 at 3:57 AM

    Parabéns, Indzara. Seu mapa tá show. Como faço para criar meu próprio mapa aqui no Brasil?

    ReplyDelete
    Replies
    1. UnknownAugust 20, 2013 at 11:51 PM

      According to Google translate, you are saying
      "Congratulations Indzara. Your map okay show. How do I create my own map here in Brazil?"

      Thank you. It was a manual effort for me to draw the outline of the country map and then have Excel cells with formula that points to the numbers in the data. I applied conditional formatting after that. I need to find a better method to create the map so that I can create for other countries too.

      Delete
  6. UnknownAugust 19, 2013 at 1:41 PM

    Congratulations. It's pretty good. How does this the hidden 'Data' sheet? It is macro? Never seen anyone do that. The net has.

    ReplyDelete
    Replies
    1. UnknownAugust 20, 2013 at 11:52 PM

      Thanks. :)

      You can unhide the 'Data' sheet and see how I built it. It has a lot of cells with simple formulas. There is no macro.

      It's not difficult, but it takes a lot of patience and time. When I find a better method, I will post a video on it.

      Delete
  7. sriAugust 27, 2013 at 3:33 AM

    Hi
    your template helped me lot.How to add districts with in State.
    can u guide me plz
    Thanks and regards

    ReplyDelete
    Replies
    1. UnknownAugust 28, 2013 at 7:23 AM

      Hello bhai,

      Thanks. I have not created the district level maps yet. I plan to create them in the future. I will post them. Please subscribe to my blog to be notified.

      Delete
  8. sriAugust 29, 2013 at 1:41 AM

    thanks for your reply will wait for district level maps,but surely it will help lot of people.
    Already we are using your state level heat maps
    I wanted to thank you for your effort Thanks :) :)

    ReplyDelete
    Replies
    1. UnknownSeptember 1, 2013 at 3:35 AM

      Hello Sri,

      Thanks. I am glad that the map is useful. Please e-mail me at indzara at gmail, if you don't mind, and let me know how the map is being used. It will be good to know.

      Delete
  9. heatmapsSeptember 20, 2013 at 8:43 AM

    Interesting...this heat map issue ... Looks like the predictions in the 1981 book by Joel Garreau "Nine Nations of North America" came true. You should read it (If you haven't) it's a great book.

    ReplyDelete
  10. Pierre Jean ShanyNovember 3, 2013 at 9:18 PM

    I woulk to be a friendship this website, i don't see the sign up

    ReplyDelete
    Replies
    1. UnknownNovember 4, 2013 at 6:47 AM

      Thank you very much for your interest. You can subscribe to the blog using your e-mail address. Please see the side bar on the right. You can also subscribe to the YouTube channel. http://www.youtube.com/user/theindzara

      Delete
  11. enfopamNovember 16, 2013 at 8:54 AM

    Hello !
    first of all , I want to tell u Congratulations to your hard work and beutifull
    How to choose a another country, and also i want to know the password , because when i try edit he ask me a password. Thank you

    ReplyDelete
    Replies
    1. UnknownNovember 24, 2013 at 5:20 AM

      Thank you for your kind words.

      To unlock, the word is indzara.

      Currently, I have the map only for India. In the future, I plan to create them for more countries.

      Delete
  12. UnknownJanuary 30, 2014 at 10:07 AM

    This comment has been removed by the author.

    ReplyDelete
  13. UnknownJanuary 30, 2014 at 10:08 AM

    Could you please make a version of this spreadsheet which works in OpenOffice Calc too?

    Thank you.

    ReplyDelete
    Replies
    1. UnknownFebruary 3, 2014 at 5:57 AM

      Thanks for the feedback. I will consider it for one of the future projects.

      Delete
  14. Eswar DeepakFebruary 28, 2014 at 2:17 AM

    I really loved the way you did it. Extremely helpful. Thanks a ton!

    ReplyDelete
  15. heat mapsMarch 8, 2014 at 9:52 AM

    That's a very informative post, there are many things i did not know ! Anyhow I appreciate your willing to share your knowledge about heat map !

    ReplyDelete
  16. SidMarch 18, 2014 at 10:27 PM

    I came across this post while I was searching for a way to draw district level heatmaps in India. Excellent article.

    Additionally you could also use the gvis API from google and the open source R package to create similar diagrams. More interactive and useful if you are faimiliar with R.

    ReplyDelete
  17. justen23April 20, 2014 at 8:14 AM

    Your map is excellent, how can we change colours as green lowest and Red highest.
    pdf digital signature

    ReplyDelete
    Replies
    1. UnknownApril 30, 2014 at 11:33 PM

      Please see the video to change colours.
      https://www.youtube.com/watch?v=Ye0qa--7MjM

      Delete
  18. UnknownMay 23, 2014 at 8:20 AM

    This comment has been removed by the author.

    ReplyDelete
  19. UnknownMay 24, 2014 at 12:58 AM

    thanks for providing us such a wonderful apps of creating indian state map. from last few weeks I was searching and also downloaded some s/w but nt working. however this apps is very simple and very much useful. anyway, I have some ueries about it:
    (1) how can we add title/sub title of map when we like to use only map for copy-paste in another document;
    (2) if I like to use only 4 or 5 different colors with range of data. Then how can we define color for particular ranges which we seen in commonly;

    ReplyDelete
    Replies
    1. UnknownMay 27, 2014 at 7:02 AM

      Hello Vishal,

      You are welcome. I am glad you find it useful.

      1) You can insert text boxes in Excel over the map image, to add title/subtitle. (INSERT ribbon TEXT section)
      2) Please see these videos in my YouTube Channel.
      https://www.youtube.com/watch?v=rLZTpRVJGsk&list=UUujLg-FkXISKqeDWef2b6ZQ and
      https://www.youtube.com/watch?v=Ye0qa--7MjM
      where I explain how to change colours.

      Thanks,

      Delete
  20. UnknownMay 29, 2014 at 10:28 PM

    Hai, your heat map of india is very very excellent, as you know Andhra Pradesh is bifurcated to two states as Andhra Pradesh and Telangana, please provide us the india map which includes Telangana state as well.

    Thanks in advance

    Best Wishes
    Kamalesh

    ReplyDelete
    Replies
    1. UnknownJune 16, 2014 at 9:47 PM

      The template has been updated now with the new state. Please download the file again in this blog post.

      Thanks for your feedback,

      Delete
  21. UnknownJuly 7, 2014 at 11:08 AM

    how to use this map image in other excel files and link it with different data. pls guide me.

    ReplyDelete
    Replies
    1. UnknownJuly 7, 2014 at 8:23 PM

      I am not sure I understand what your specific scenario is. The image of the map can be copied (just select the image and copy in Excel) and pasted in any Office application (PowerPoint, Excel, Word). It will just be an image.
      If you want to use different data to create a new map, you can enter that data (overwrite) in this sheet and the map image will be updated with your data. Hope this helps. If this doesn't answer your question, please explain your specific scenario. Thanks.

      Delete
  22. UnknownOctober 26, 2021 at 11:56 PM

    WHY UTTAR PRADESH COLOUR ARE CONSTANT, ITS NOT CHAGNEING WHILE CHANGING IN DATA

    ReplyDelete
  23. baddenzahmMarch 3, 2022 at 10:02 PM

    Slot Machine Games – Games, Specialization and Facts
    It includes the three 구리 출장안마 most popular slot machines, which are also popular with kids and adults alike. The games are a mixture of classic and 3 거제 출장안마 answers  ·  Top answer: I have 안성 출장안마 a good understanding of the game. They are the best slots and 제주도 출장샵 slots of the 평택 출장샵

    ReplyDelete