Map Chart Series Color Customization

Copper Contributor

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 Replies
best response confirmed by alpcangunes (Copper Contributor)


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.

2023-03-13 data series.png

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


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