In Make a Copied Chart Link to New Data I showed how to copy one sheet with data and a chart, or a data sheet and another sheet with a chart, so that the new chart links to the new data. The protocol is more complicated than you would expect the first time you get tripped up, but it does make sense.
How do you make a copy of a chart on the same worksheet, and have it link to a new range of data? This is somewhat complicated, too. But here is a simple way to accomplish the task.
The Problem
You have a nice chart, and you want to copy the chart and use it to display other data.
You copy the range that includes the data and the chart(A1:H10), and paste it into another range (A12:H21).
You change the pasted data (A12:C19) but the pasted chart doesn’t update. When you examine the source data of the new chart, you see that it refers to the data used in the old chart.
The Solution
The easiest way to make this work, especially if you need to make many copies of this chart, is to follow this protocol:
- In a new workbook, arrange some typical data in the appropriate layout and make your chart. Save this workbook. This will be your Chart Template Workbook.
- Select the range containing the data and underlying the chart on the Chart Template Workbook, and use Cut (not Copy) to place it on the clipboard.
- Select a cell on the target worksheet, and paste.
- Close the Chart Template Workbook without saving.
- Reopen the Chart Template Workbook, and the chart and data you cut will have been restored.
- Repeat steps 2-5 as needed.
Following this approach, the second chart in the following sheet updates when the second set of data is updated. When we examine the source data range of the second chart, we see that it is linked to this second data range.
Chandoo says
That is a neat trick. Once I had to copy 40 charts. I ended up writing some VBA to do that…
jeff weir says
That’s a cool trick. It’s funny how Excel is inconsistant between selecting the chart/data range and cutting, vs selecting the chart and data and deleting….the data deletes but the chart remains.
Here’s a slight tweak…you don’t need to use a chart template file. Instead, copy the chart sheet to the same workbook, then cut the chart and data range from the new copy and paste back on the original sheet. The bonus is that the amount of charts you are copying/cutting doubles each time.
After you are done, select all the empty chart sheet copies you made and delete them.
jeff weir says
Whoops…I could be clearer about what I say above. When I said ‘copy the chart sheet’ I mean make a copy of the entire sheet by right clicking on the tab and selecting the copy option.
Jay says
Jon,
Thanks for this helpful tips. However, your instructions are not clear for me. Dumb me!
In step3, which is the “target worksheet”? Is it the sheet with first chart or the second one? If I close the chart template workbook without saving, then it remains at Step-1, ie, just with some typical data layout. Could you please clarify?
thanks
Jay
Jon Peltier says
When writing about copy-paste operations I ofter refer to the source (what is copied) and the target (what is pasted or where it’s pasted). So in step 3, you paste on the target worksheet, where you want the chart and data to go. The source worksheet is in the Chart Template Workbook.
Jay says
Jon,
Many thanks for your considerate explanation and clarification.
/// source worksheet is in the Chart Template Workbook. ///
After your reply, It again took me further 20 minutes to figure out what you want me to do. (Dumb me or you can do better!!)
I already have a chart which I want to copy it. Therefore, there is no need for me to create “arrange some typical data” and ” make your chart” as you explain in Step-1. You start by saying that I need to follow these “protocols” if I need to copy the existing sheet. But, you put Step-1 as a sample exercise and not to facilitate existing data. This has got me off-track. There is no need to create a chart template workbook but only use the existing chart book.
Maybe you can further idiot-proof your instructions appropriate for my level!
thanks once again for this excellent tip.
Jay
Jon Peltier says
Jay –
I have a few reasons for using a separate workbook. First, it allows for reusing a sheet template across sessions of Excel. Second, there are differences in the way copied sheets are handled in 2007 from 2003. For example, if I copy sheet1 in one workbook into another workbook which also has sheet1, the copied sheet is named sheet1 (2), and all the charts are copied over, but they reference the data ranges on sheet1, not on sheet1 (2). If I move sheet1 (not keep a copy in its workbook), it is renamed and the chart points to the data in the moved and renamed sheet.
Apparently copying and pasting a range with a chart from one workbook to another works properly in 2007, but I’ve gotten into a particular habit with the copied sheets. Also, copying a range from one sheet to another within the same workbook leaves the new chart linked to the old range, so a separate workbook is needed.