Dymanic data on a Map - Easy method using Secret Camera object in Excel

Displaying excel data on map is a complex task as of now.
First of all you need to have Geocoding (latitude and longitude) of the places you want to map then create a KML map. Now , use either Internet or Google Earth to display map... Its really complex!

Here is a simple trick - hidden in excel !
This trick helps you display data automatically and with little tricks you can make it dynamic .
You can also manipulate the data and display it by using a bit of imagination!

You got to use a "Camera Tool" in excel to do this
First let me tell you where this Camera Object is hiding.... It's hidden in excel tools and you need to take it out first !

Step 1: Go to Tools on the main menu and click Customize
Step 2: In Customize ,Go to Tools search for Camera.

Now hold that "Camera" and drag this to your to the tool bar on your excel.
Here is the picture to look at....
We are almost done!

















In Excel 2010, The Navigation to find the Camera tool is here...



















Now search for an map that meets your needs .
You can get enough and more on Google Image search - (Please check for copy rights...)
Paste the map on an excel sheet.
So Now we have a Map pasted as an Image in excel and also the data to be displayed.

Now go the excel, select the range you want to display on the map
Click on the "Camera" - you will find the range - selected.
Now  - leave it on the map! Adjust the size. Do a bit of formating. Now you have the object on the map...

The magic with this is it carries the reference with it! 
Just to check, Select the object - you just left on the map. 
You will find its reference in the formula bar !
So, any change you make for the reference cell, the data in the object changes accordingly!

I added Increase and decrease tabs to make data dynamic.
You can use colors, In-cell Graphs, Dynamically change data...using the formula!
Trick is here for you to explore 
Rest,I leave to your imagination and innovation......explore it!

Take a look at the outcome.

Download the excel  here
Its a DO.. CO..MO.. stuff (Download, Copy ,Modify) You Can also down load @downloads



No comments:

Post a Comment