Thematic map in excel 2007+ help

[largeselection]

Hi,

So ive scoured the Internet and forums looking for some help for this. Basically I have many sales people (1000) across the united states and I want to track their sales results visually on a map of the united states if possible.

My first attempt was to create a very crude map just using regular cells in the grid. Then I put in lookup formulas to sum the sales per state and highlighted my newly created "us map" with the scale value conditional formatting.

That is ok, but is pretty crude.

Anyone have other suggestions of how to handle this more elegantly?

Some things I've seen on the net...

The andypope file - this doesn't show the actual data in the states, just the colors.

Plotting the locations in a scatterplot- so I have the zip codes of all 1000 locations so I got the lats and longs and plotted them. So I've seen suggestions to use a us map as a chart background behind that. The drawback is that only plots the location, but not performance. Is it possible to conditionally format the actual data points based on the same logic/colors of the heat map I described above using the conditional formatting?

Any other ideas? I do not want to use map point or another software package.

 

[diddi]

are you going to tally it state by state, or person by person

 

[largeselection]

It's currently tallied by state since there are too many locs that I didn't know how to represent graphically.

So I only had to create 50 "states" out of the cells rather than trying to create and place 1000 locations.

If possible I'd like to have the same effect of the "conditional format based on a scale" applied to individual scatterplot points so that the worst sales loc would be red, the best would be green, and the middle 50th percentile would be yellow and all the points would fall somewhere in that spectrum.

If this isn't possible then I would have to tally by state to simplify the data.

Thank you for taking interest!

 

[tusharm]

Create a Bubble Chart, where X and Y are latitude and longitude and the US map is the chart background, or more strictly the plotarea background. However, I have never been able to exactly line up lat/longs across the breadth and width of the US. The basic problem is that unlike an Excel chart, the planet is inconveniently not flat.{vbg} But if you can live with this slight misalignment, the bubble chart should do the job.

If you want to use conditional coloring, adapt
Dashboard example - conditional colors of shapes
http://www.tushar-mehta.com/excel/charts/0301-dashboard-conditional shape colors.htm

I also created a PowerPivot related tutorial...
PowerPivot – Part 4 of 4: A geographic visual display of 18 million US Census records
http://www.tushar-mehta.com/publish_train/xl_vba_cases/powerpivot_conditional_shape_color.shtml

Hi,

So ive scoured the Internet and forums looking for some help for this. Basically I have many sales people (1000) across the united states and I want to track their sales results visually on a map of the united states if possible.

My first attempt was to create a very crude map just using regular cells in the grid. Then I put in lookup formulas to sum the sales per state and highlighted my newly created "us map" with the scale value conditional formatting.

That is ok, but is pretty crude.

Anyone have other suggestions of how to handle this more elegantly?

Some things I've seen on the net...

The andypope file - this doesn't show the actual data in the states, just the colors.

Plotting the locations in a scatterplot- so I have the zip codes of all 1000 locations so I got the lats and longs and plotted them. So I've seen suggestions to use a us map as a chart background behind that. The drawback is that only plots the location, but not performance. Is it possible to conditionally format the actual data points based on the same logic/colors of the heat map I described above using the conditional formatting?

Any other ideas? I do not want to use map point or another software package.

 

[diddi]

this is only my impression of what i would do to represent the data, statewise (firstly), and there will undoubtedly be many other methods.

I would create a giant userform which fills the screen and place a wireframe map of the area as the background pic. onto that i would overlay an array of GIF images of each state, each of which is a different gradient of color (eg deep green to very pale green/white) there might be, say 8 shades of color per state. so you would end up with 400 GIFs on your map.

then use .visible to show the particular color you need for each state.

each location could be represented by one of say 4 different colored dots which go onto the topmost layer and are always visible.

done, now you have to start drawing

 

[tusharm]

Unfortunately, the userform object has not been enhanced by Microsoft in years. So, instead of using a userform as the 'drawing canvas' use the worksheet.

That's what I did with the solutions I shared in the previous post.

this is only my impression of what i would do to represent the data, statewise (firstly), and there will undoubtedly be many other methods.

I would create a giant userform which fills the screen and place a wireframe map of the area as the background pic. onto that i would overlay an array of GIF images of each state, each of which is a different gradient of color (eg deep green to very pale green/white) there might be, say 8 shades of color per state. so you would end up with 400 GIFs on your map.

then use .visible to show the particular color you need for each state.

each location could be represented by one of say 4 different colored dots which go onto the topmost layer and are always visible.

done, now you have to start drawing

 

[largeselection]

yes the bubble chart is tricky because of the darn curved earth.

Thank you both for your suggestions. I will tinker around and see if I can get it to work.

Thanks!

 

[tusharm]

Actually, it's not the bubble chart that's tricky. It's any attempt to map coordinates in an Excel chart that's the problem. If you look at a typical map, the latitudes and/or the longitudes are drawn slightly curved. That is a problem in Excel if we want to make them x and y values respectively.

yes the bubble chart is tricky because of the darn curved earth.

Thank you both for your suggestions. I will tinker around and see if I can get it to work.

Thanks!