Dynamic Map with Drop-Down in Excel

The map charts are generally used to compare the data values and show different categories across the geographical region. In excel we use map char to visualize the KPI (Key Performance Indicator) and represent the distribution of KPI across multiple geographical regions for any specific category. In addition to this, we can use the excel inbuilt function to create the dynamic interactive map char using the dropdown option and adding the KPI in the dropdown menu.

Step By Step Implementation

In this example, we will create a dynamic map that shows the yearly revenue for different states of India. We will be using random revenue data for our example.

Step 1: Create a Dataset

In this step, we will create a database for the state-wise revenue of India. For this, we will be using the following data which we will be using to compare with the help of a dropdown list and represent it on the map of India.

 

Step 2: Create Range Criteria

In this step, we will add one more column – ‘Revenue’ to our database. And, we will apply SUMIF Function with ‘Year’ so that, if the Year value changes the revenue column gets updated. For this Right-Click On Column-B > Insert.

 

Once we click on Insert, excel will open a window asking where and what we want to insert. Select the Entire Column option in the popup window.

 

It will create a new column, we will name Revenue.

 

We will use a specific cell(Here, we are using H1) for referencing the ‘Years’ columns. 

 

Note: You need to add the SUMIF formula according to your rows and columns.

Once we have added the SUMIF formula, we will drag it down to the end of our dataset. It will fill all the revenue values for the Year 2018 because we have used 2018(H1 column) as our reference. Now, as we change the value in H1 columns all the values get updated according to the value of column H1.

 

Step 3: Adding Year Column

In this step, we will need to add one more column. i.e., the Year column to our dataset. For this Right-Click(Here, on Cell H1) And Insert A Column.

 

We will name this column as Year column.

 

Step 4: Adding Dropdown List

In this step, we will add a dropdown list (Here, in the I1 column). For this Select I1 Column > Data > Data Validation.

 

Once, we click on Data Validation, excel will open a popup window asking about the range of the dropdown lists. We need to give List in Allow option and specify the Range in the Source option.

 

Once we click OK, Excel will create a dropdown menu(Here, for column I).

 

Before moving further to enhance our dropdown menu we will format it. For this Select Column(Here, Column H1 & I1) > Home > Cell Formatting > Choose Your Own Format Style.

 

Step 5: Insert Map

In this step, we will insert the map of India. But first, make sure that we are connected to the internet to insert Filled Map. For this, Select Dataset(Here, State and Revenue) > Insert > Maps > Filled Map.

 

Once, we click on Filled Map, excel will automatically insert a map of India by analyzing our dataset(name of states).

 

If we hover over our map, it will show the revenue data.

 

Step 6: Formatting Map

In this step, we will format the map to enhance its representation. For this Double-Click Inside Map, and excel will open a Format Data Series window.

 

In the Format Data Series window, Select Show all in Map labels. 

 

Once, we click on Show all in Map labels, excel will show the names of all the states over our map.

 

In the same, Format Data Series window, we will change Series Color to Diverging(3-color).

 

This will highlight the map with 3 different colors according to the lowest, medium, and highest value of the Revenue column.

 

We have also added the map title. For this Double-Click On the Title and add Statewise Revenue.

 

Now, we will add data labels to our map. For this Right-Click On Map Area and click on Add Data Labels.

 

This will add data labels to our map according to data from the Revenue column.

 

Fig 7: Output

ankur035

Follow

Improve

Article Tags :

• Excel
• Microsoft Office
• News
• Excel-How To
• ExcelGuide
• Technical Scripter 2022

+2 More