Open In App

Dynamic Map with Drop-Down in Excel

Improve
Improve
Like Article
Like
Save
Share
Report

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.

Database

 

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.

Inserting-Option

 

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.

Entire-Column

 

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

Revenue-Column

 

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

SUMIF-Formula

 

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.

Revenue-Column-SUMIF-Value

 

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.

 Insert-Column

 

We will name this column as Year column.

 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.

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.

Data-Validation-Range

 

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

Dropdown-Menu

 

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.

Format-Cell

 

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.

Insert-Filled-Map

 

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

Filled-Map

 

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

Revenue-Data-On-Map

 

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.

Format-Data-Series-Window

 

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

Map-Labels

 

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

Map-With-Labels

 

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

Series-Color

 

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

Diverging-Color

 

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

Map

 

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

Add-Data-Labels

 

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

Data-Labels-Added

 

Fig 7: Output



Last Updated : 27 Jan, 2023
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads