VBA & Macro Problems

How to Find and Replace Source Data in Excel Chart Series

Algirdas Jasaitis Sep 30, 2026 868 views

Question details

The user needs a method to quickly find and replace text references within the SERIES formulas of Excel charts across a workbook.

How to Find and Replace Source Data in Excel Chart Series

Product: Excel
Device & OS: not provided
Scenario: Updating source data references (such as renaming a category like '_02_taste' to '_01_taste') across multiple charts without manually editing every formula.
Observed behavior: Excel does not offer a built-in Find and Replace feature for chart SERIES formulas, preventing users from using the standard Ctrl+H shortcut to update chart data.

Before you start

Before running a VBA macro to modify your charts, ensure the Developer tab is enabled in your ribbon and save a backup copy of your workbook.

Solution 1Recommended

Use a Custom VBA Macro to Replace Series Text

Because Excel lacks a native tool to find and replace text inside chart formulas, you must use a VBA macro to loop through the charts and update the text programmatically.

The standard Ctrl+H functionality only applies to worksheet cells. Chart objects, including their SERIES formulas, are excluded from the standard Find and Replace scope. Implementing a VBA script allows you to automate the process of replacing specific text references across all charts.

Open the VBA Editor

Press the Alt + F11 keys simultaneously on your keyboard to open the Visual Basic for Applications (VBA) Editor.

Insert a New Module

In the top menu, click on Insert and select Module to create a blank script window.

Write the Replacement Script

Enter a macro script that loops through 'ActiveSheet.ChartObjects', extracts each 'SeriesCollection.Formula', and uses the Replace function to swap your old text (e.g., '_02_taste') with the new text (e.g., '_01_taste').

Run the Macro

Click anywhere inside your newly written macro and press F5 to execute it. Your chart source data will instantly update based on the replacements.

Use a Custom VBA Macro to Replace Series Text

Testing First: Always test the macro on a sample workbook or a backup copy to ensure the text replacement behaves exactly as expected.

Free Microsoft Office alternative

Manage Data & Macros Seamlessly with WPS Office

If you frequently work with complex charts and require a reliable, lightweight alternative to Microsoft Excel, WPS Office is an excellent choice. It offers extensive format compatibility and full support for VBA macros, allowing you to run automated data updates effortlessly.

1. Download WPS Office: Visit the official WPS website to download and install the free office suite on your device.
2. Open Your Workbook: Launch WPS Spreadsheets and open the file containing your chart data.
3. Run Custom Macros: Enable macros via the Developer tab and execute your VBA scripts to effortlessly update chart series formulas.

Fully compatible with Microsoft Excel (.xlsx, .xlsm, .csv) formatsRobust VBA environment to run custom macros for chart editingLightweight installation and fast processing speedsFree to download and use with a highly familiar interface

microsoft office alternative - wps office

Frequently Asked Questions

Can I use Ctrl+H to replace source data in Excel charts?

No, Excel's built-in Find and Replace (Ctrl+H) function is designed strictly for cell data and formulas. It cannot search or modify the SERIES formulas used inside chart objects.

How do I view the SERIES formula for a chart?

Click on any data series line or bar within your chart. Look at the Formula Bar located at the top of the Excel window to view the complete SERIES formula containing the data references.

Can I update multiple chart references at once without VBA?

Without VBA, you must manually click each series in every chart and edit the formula in the Formula Bar. If you rely on consistent named ranges, you can update the Name Manager, but direct text replacement inside the chart requires a macro.

Excel & Data Problems