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.
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.)
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:
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.