How to Make a Clickable Interactive Map in Excel with Macros

Step-by-step

Click a state on a map and its row in the table lights up. The example uses a waste-management report for Australian states, a map picture with a small square shape over each state, and recorded macros: no code-writing needed to start.

1. Add a drop-down for the state

In K1, go to Data > Data Validation, choose List and select the state names (B5:B12).

2. Highlight the chosen state’s row

Select the table (B5:I12) and add Conditional Formatting > New Rule > Use a formula:

=$B5=$K$1

The dollar before B keeps every cell in the row checking column B. Choose a light blue fill and white font. Change K1 and the highlight moves.

3. Get ready to record macros

Save the file as an Excel Macro-Enabled Workbook (*.xlsm), and turn on the Developer tab (File > Options > Customize Ribbon).

4. Record one macro per state

Click any cell other than K1, then Developer > Record Macro. Name it after the state (for example ACT), optionally give it a shortcut such as Ctrl+Shift+A, and click OK. Click K1, type ACT, press Enter, click L1, then Stop Recording. Record a second one for NSW to compare.

5. Copy the code for the other states

Go to Developer > Macros > Edit. Each recorded macro is a few lines between Sub and End Sub: select K1, set its value, select L1. Copy one, paste it below, and change the macro name and the state value (for example WA). Repeat for each state. Alt+F11 switches between the editor and the sheet.

6. Link each map shape to its macro

Right-click the square over each state, choose Assign Macro and pick that state’s macro. Now hovering shows a hand cursor, and clicking a state highlights its row: a simple interactive map for a dashboard.