Map Chart Series Color Customization

[alpcangunes]

Dear all,

I've been trying to create this map chart for a while and I came across a problem. I'll try to explain what I've been trying to do step by step:

1. I have a list of cities that are in the same country and a column of data according to these cities,
2. I can insert a map chart without any problem,
3. What I want to do is basically coloring each city with a solid color according to the interval of their respective data,
4. I conditional formatted the data to be highlighted in a certain color (for example if data of city A is between x and y, highlight it in green) and I have 5 different intervals (which also means 5 different colors to be projected on the map chart),
5. I want each city to be colored as the conditional formatting's rules color. But not like a gradient coloring, need them to be solid colors.
6. I also want this to be automatic since the data has about 80 cities.

Is there any way that I can do this? I'm open to any solution.

Thanks for your help!

[#2 · Accepted solution]

@alpcangunes 

To use colored categories (instead of gradients for numeric values), first add a column that contains identifiers for your categories.  In my example below, it is the Century column.  Edit your data series (right-click any city on your map; click "Select Data…"; select your desired - probably the only - series in the Legend Entries (Series) box; press the Edit button).  Change the "Series values" to a range in the category column (and, if appropriate, change also the "Series name").  Click the "Color by secondary category names" radio button, then the OK button.

And of course, click the OK button to close the Select Data Source dialog, saving your changes.

The (gradient and non-gradient) colors for your categories are selectable from the usual chart-coloring tool.

Excel creates only choropleth maps, for which regions of the map area are colored.  For mapping data for cities, it might be more user friendly to use a symbol map.  Microsoft's old MapPoint software was good at this; presumably Bing Maps can do the same.  Other alternatives are Google Maps (although I don't recall if you can map categories to custom icons automatically) or the website Datawrapper.de.

[#3]

Thanks for the advice! This solution was exactly what I was looking for. You're the best!