3 ways to count filled map ranges in Excel Map United Kingdom

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.

1. Count the maximum using Excel MAX function
2. Count the minimum using Excel MIN function and add a small number (like 0,01) because we use less than rule
3. Count the interval size
4. 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.

1. Define a few k-values
2. Use one of the PERCENTILE functions with the values as first argument and k-value as the second one
3. 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.

Watch the video tutorial to check how to use Excel Map

← All tutorials