How to copy charts based on named formulas to other workbook?

posted Sep 23, 2014, 9:17 AM by Krisztina Szabó   [ updated Sep 24, 2014, 3:57 AM ]
Freddy: Mr. Hilltop, will you hop up on your feet and stand beside this table. Nice hopping. Mr. Hilltop [...]

by The FrankensTeam

As an answer to a frequently asked question, we would like to share a tutorial about a simple method to copy a chart based on named formulas to your own file and use it according to your needs.
Our charts in E90E50charts gallery are usually built on named formulas. This way we do not use sheet cells to store the calculated background data, so the sheet is clear: the reader only needs to change the base data and labels in the downloadable example file and the chart is automatically updated. Unfortunately the example file is not suitable for those who would like to use the chart in an existing workbook. In this case the simple copy and paste the chart method does not work since the named formulas will not be created in the other file. Because of the complexity manual creation and re-linking the names is usually irksome and time consuming.

So how should you copy the chart? The trick is to copy the whole sheet and replace the link under the Data menu. Here is how to do it step-by-step using the matrix bubble chart as example.

1. Open the downloaded example file.

In the Name Manager (Ctrl+F3) you will see there are some named formulas of which rng is the dynamic named range of the data table.

2. Open your file where you would like to use the chart.

In this tutorial the file we use is My_workbook.xlsx. (It is important you have to use an existing file, alternatively you have to save the file before going to step 4.)

3. Move the sheet with chart to your workbook.

In the example file right-click on the sheet tab and choose Move or Copy

In To book select your workbook and check the Create a copy box.

Now the sheet is in your file, and the named formulas are created accordingly - you can check it in the Name Manager.

The problem is that the chart series are linked to the original file, as you can see in the chart series formula:

4. Change the data connection link source to your file

Under Data menu / Connections group / Edit Links click on Change Source and choose your own file from the Change Source dialogue box. (in the example it is My_workbook.xlsx)

You will see as if nothing happened on the Edit links pane, but now the chart series references are correct, showing the name of your file:

After you Save, Close and re-open your file, the link to the original file will disappear from the Edit links pane too.
Now you can use the chart in your workbook without any problem, and you can change the reference of the data table.

Some examples where the trick could be useful:

Leave a comment