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