How to drill down on Excel Map France?

If the map has two administrative levels (like Excel Map France, where there are Regions and Departments) you can easily drill down and up between these levels using Hide shapes functionality. The example will be based on the population data for France.

Prepare the data in the G column of the Data sheet

Let’s start with putting data into G column called Value. In many maps we have here two administrative levels, so we need to prepare the data for both of them.

Build a drop-down list

Now the most complicated part, especially for the Excel beginners. We need to insert a drop-down list to be able to select the level we want to work on. To do this, we need to unlock the Developer tab on the Ribbon: right click on the Excel ribbon > Customize the ribbon > Main tabs > check the Developer tab.

Then choose Insert > Form controls > Combo box > draw a drop-down.

Create a table with your choices and mark result cell yellow. Then right click the drop-down > Control > Connect Input range and Cell link to your cells.

 

Prepare a formula to make shapes visible

Now it’s time to go to the Data sheet and find the U column. This is a parameter column where you can control the shape visibility. If the value equals 1, the shape is visible, if 0 it’s hidden.

We need a formula that will show 1 for all the regions when level 1 is selected (regions) in the drop down and in this case we need the formula to show 0 for departments. In the opposite case, when we choose level 2 (departments), we want regions to equal 0 and departments to equal 1.

You can use the following formula:

For regions: =IF(Map!$P$1=1;1;0)

For departments: =IF(Map!$P$1<>1;1;0)

Now we need to assign the updating macro to the button. Right click the drop-down button > Assign macro > choose macro called: Update2 and hit OK.

Drill down the map

Now if you choose a level from a drop-down on the map sheet, you can switch between the departments and regions.

Adjust the legend

The old color classes for lower administrative level will not work in this case, but you can also calculate them and switch using technique described in the how to classify your data.

Watch the video tutorial to learn how to use the Excel Map

← All tutorials