Indzara

Learn to Create a Filled Map with Categories in Excel

A filled map visualization differs from a gradient map in the number of colors that can be inducted into the visual.  While gradient maps are a  great visual representation, in some cases we might need more than 2-3 colors to make a meaningful analysis. This is where a filled map with categories is useful.

For example, what if the colors in the map have to be representative of a range of numeric values?

Consider a sample data of U.S. states and the civilian labor force data. How can we represent this on a map visual where the colors are based on the range of the labor force data grouped as categories? A map that looks like this:

Filled Map with Categories

The sample data is as shown:

Create a Filled Map with Categories sample data

This blog is all you need to create a filled map visualization. Let’s get started!

All the steps for creating a filled map with categories are explained in detail in the YouTube video:

Step 01:

If you’ve been following our blog posts in Data to Decisions, one thing we always encourage you to do before beginning to work with data is to convert them into tables. This ensures better formula readability and scalability when the data expands. Select the data and do CTRL + T for the same.

Filled Map with Categories data to table

Let’s name this as Data.

Step 02:

Begin with creating the categories (also called bins) of our numeric data. Say, five categories as shown below (from cell E4):

Filled Map with Categories create categories

Assign a label to these bins that we’ll use in our visualization as shown here.

Filled Map with Categories assign labels to bins

Step 03:

Let’s connect these bins with our data. Create a “LABEL” column which will assign a label for each of the states based on the bin hierarchy.

With XMATCH function, which looks-up and returns a position of a value in a given range/table data.

Filled Map with Categories XMATCH function

Here, the match type can take the following values:

0 – Exact match (default)

-1 – Exact or next smallest value

1 – Exact or next largest value

2 – Wildcard match

Similarly, the search type can take:

1 – Search from beginning (default)

-1 – Search from last

2 – Binary ascending search

-2 – Binary descending search

Let us look at how this function works in our example:

We look up for the labor force value in the bins for an exact or the next smallest value (1) and do this look up from the start (1).

Note: $ symbol is used to lock the cell references stating that these values are in a fixed location.

This returns the position of the bins as shown:

Filled Map with Categories returns position of  bins

We need the corresponding label value and not the position, for this we’ll use the INDEX function which returns the value from the position.

This gives us the labels for each state:

Filled Map with Categories labels for each state

Step 04:

With the above data, we’ll create our filled map visual. Click anywhere outside the data and (1) Go to Insert, (2) Maps, (3) Create a Filled Map visual:

Filled Map with Categories insert filled map

Right-click on the empty map to Select Data, assign a series name (our column name) and the values (the labels created in the previous step) as shown:

Filled Map with Categories assign series name

A key point to note is to choose the “Color by secondary category names” to get a filled map visual as shown above. (Color by numeric values gives a gradient fill)

Select the horizontal axis labels to be the State names:

Filled Map with Categories select horizontal axis  labels

This creates a filled map visual that looks like this:

Filled Map visual

Adjust the position of the legends to make more space for the actual visual. Add a title, per your need.

Step 05:

A quick look at the map generated tells us that the sequence of the categories is not in any particular order (either ascending or descending). Let’s look at a quick way to resolve this.

The reason our legend has the given order (as shown in the previous image) is based on the order in which the label column contains these values. If we can change the order of the labels, the map’s legends get updated.

To do this, sort the LABEL column from the smallest to largest value as shown:

Filled Map with Categories sort label

With this, we have a visually clear filled map visualization:

visually clear filled map visualization:

There’s an important point to consider here. This method of manual sorting needs to be done whenever there is a change in our source table.

Step 06:

From here on, apply changes to the color of categories per your requirement.
You can also assign labels and much more with the multiple chart formatting options available in Excel. Our filled map with categories is ready!

Filled Map with Categories

Click here to read about how to create a filled map with gradient colors here.

If you have any feedback or suggestions, please post them in the comments below.

Leave a Reply

Your email address will not be published. Required fields are marked *