Using filled (choropleth maps) may be a little tricky. Every time you change the color range values you get a different map. That is why it is crucial to choose a proper method to define the legend ranges. You can find here three most common ways.
Prepare the data in Excel Map
We start with certain values for UK Counties that are entered in the G column of the Data sheet.
Counting ranges of equal interval
In the first method we use the minimum and maximum of your data to count the span and the equal interval.
- Count the maximum using Excel MAX function
- Count the minimum using Excel MIN function and add a small number (like 0,01) because we use less than rule
- Count the interval size
- Use a simple sum with static and dynamic cell addresses to count next intervals.
Then use the Excel Map functionality to attach ranges to cells.
At the end click OK to refresh the map.
Counting ranges of equal size
The second method we define range that have inside the same number of shapes / regions (e.g. green: 10 counties, light green: 10 counties, etc.). This method requires using Excel PERCENTILE function.
- Define a few k-values
- Use one of the PERCENTILE functions with the values as first argument and k-value as the second one
- You can use also a ROUND function to make the ranges look better
Then attach ranges to the cells within Choose color function.
Counting naturally looking ranges using big numbers
The last method is the easiest one. You just look at the minimum and maximum and think of naturally looking that use big rounded numbers (1000, 2000, 3000 etc.). You can try to count them using round functions or just enter them in the Choose color window.
The final map will look like this.
Please note that each of this map looks different and you need to be really careful when choosing the right method.
