VBA & Macro Problems

How to Link Excel Scatter Plot Points to Worksheet Cells Using VBA

Maira Mehtab Sep 28, 2026 871 views

Question details

The user wants to establish a two-way interaction between an Excel scatter plot and its source data, where selecting a data point highlights the source cell, and selecting a cell highlights the corresponding chart point.

Product: Microsoft Excel
Device & OS: not provided
Scenario: Creating interactive charts for data analysis where visual plot points need to be dynamically traced back to their exact spreadsheet rows.
Observed behavior: Excel does not provide a built-in standard setting to automatically synchronize the selection state between a scatter plot point and its corresponding worksheet cell.

Before you start

Before implementing a custom script, ensure your Excel workbook is saved as a Macro-Enabled Workbook (.xlsm) and that you have enabled the Developer tab in your ribbon to access the VBA editor.

Solution 1Recommended

Implement Two-Way Interaction Using Custom VBA Code

Use event-driven VBA macros to map chart points to their source cells and apply dynamic highlighting.

Because Excel lacks a native setting for this interaction, you must write a VBA macro that listens for two specific events: 'Worksheet_SelectionChange' and 'Chart_Select'.

The complexity of this code depends heavily on your workbook layout and chart series structure. The code needs to accurately calculate the index of the selected point and map it to the corresponding row in your data range.

Open the VBA Editor

Press ALT + F11 in Excel to open the Visual Basic for Applications (VBA) editor.

Insert a Class Module

Go to Insert > Class Module. Create a custom class to handle application-level chart events, as standard chart objects on a worksheet do not natively expose point selection events.

Write the Selection Logic

Add code to track the Chart_Select event. When a specific ElementID corresponding to a data point is selected, retrieve its Arg1 (Series Index) and Arg2 (Point Index).

Highlight the Source Cell

Map the retrieved Point Index to your source worksheet row and use Range.Select or adjust the cell's Interior.Color to highlight it.

Handle Worksheet Selection

In the worksheet module, use the Worksheet_SelectionChange event to detect when a cell in the data range is clicked, then apply formatting to the corresponding point in the ChartObjects collection.

Requires Specific Layout: VBA scripts for chart interactions are highly specific to how your data is arranged. If you add or remove rows, or change the chart structure, the code will need to be adjusted.

Free Microsoft Office alternative

Try WPS Office for Seamless Spreadsheet and Chart Management

If you frequently work with charts, VBA macros, and large datasets, you might be looking for an efficient environment. WPS Office provides a free, lightweight, and highly compatible alternative for your spreadsheet needs, fully supporting Microsoft Office file formats and macro functionality.

1. Download and Install: Get WPS Office for free from the official website and install it on your device.
2. Open Your Excel File: Launch WPS Spreadsheets and open your existing .xlsx or .xlsm file containing the scatter plot and data.
3. Enable Macros: If your file contains VBA code for chart interactions, enable macros when prompted to ensure your custom features continue to work.

100% compatible with Microsoft Excel formats, including .xlsx, .xls, and .xlsm.Supports running standard VBA macros for customized data tasks and interactive charts.Lightweight application that runs smoothly even on older devices.Free to use with an intuitive, tabbed user interface.

microsoft office alternative - wps office

Frequently Asked Questions

Can I link scatter plot points to cells without using VBA?

No, Excel does not have a built-in feature to automatically sync selection states between a chart point and a worksheet cell. Custom VBA code or a specialized third-party add-in is required to achieve this interaction.

Why doesn't my VBA chart event trigger when clicking a point?

Chart events on embedded worksheet charts are not enabled by default. You must create a custom Class Module using 'WithEvents' to capture application-level chart interactions before clicking a point will register in VBA.

Does this VBA method work for line charts or bar graphs?

Yes, the same underlying VBA principles apply to most chart types. You can extract the Series Index and Point Index (Arg1 and Arg2) from line charts or bar graphs using the Chart_Select event.

How do I find the exact data row corresponding to my clicked chart point?

The point index (Arg2 in the Chart_Select event) typically corresponds to the chronological position of the data point within the series. You can add this index to the starting row number of your chart's source data range to locate the exact worksheet cell.

Excel & Data Problems