+ Reply to Thread
Results 1 to 10 of 10

Copy chart and change data range

  1. #1
    Forum Contributor
    Join Date
    02-18-2013
    Location
    Berlin, Germany
    MS-Off Ver
    Excel 2016
    Posts
    221

    Copy chart and change data range

    Hello,

    what is the simplest way to copy a chart and change the data range of copied chart? I have several tables for which I want to generate the same bar chart.

  2. #2
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,957

    Re: Copy chart and change data range

    Assuming this is a one-off and you want an easy manual process,.

    If your data is organized so that the data is on separate sheets, but you want to reference the same cells on those sheets for the charts, here is how you can do it.

    Copy the chart and paste it. In a chart where you want to change the data source:
    - Right click on the chart and select Select Data. This will list the series in the chart. (See picture).
    - Click on a series and select edit. This will tell you where the data range for that series is pointing.

    In this case it is pointing to Pareto!$E$4:$E$13. Replace Pareto with the sheet name containing the data. Use the single quote if the sheet name has spaces. Better still, name the sheets a, b, c, etc. then you can simply type a!, b!, c! ,etc. When you change the sheet names back the charts will pick up the new sheet name.

    If you have a lot of charts and a lot of series this can be tedious and you may want an automated solution. In that case, attach a sample workbook.
    Attached Images Attached Images
    One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.

    A picture is worth a thousand words, but a sample spreadsheet is more likely to be worked on.

  3. #3
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    MS-Off Ver
    Office 2016
    Posts
    4,641

    Re: Copy chart and change data range

    Hi there,

    There's a somewhat simpler way to achieve this if you don't need the charts to be displayed simultaneously.

    Create a single chart which takes its data from a specific "Chart Data" range, and use appropriate Lookup functions to populate that range with whichever data you wish to display.

    One slight advantage of this approach is that you have only a single chart to maintain if you subsequently wish to play around with layouts/fonts/sizes etc.

    Regards,

    Greg M

  4. #4
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,957

    Re: Copy chart and change data range

    Greg, what you are describing, I call the "dashboard approach." Depending on how the data are laid out, it can also be accomplished using named dynamic ranges.

  5. #5
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,957

    Re: Copy chart and change data range

    I thought this sounded familiar. I snooped around my archives and found this.
    Please Login or Register  to view this content.
    This code changes all the charts on the active sheet to reference the ranges on that sheet. TSheet is the "Template Sheet" the sheet from which the chart was copied.

    It could be made a bit more general by enclosing the chart object loop in a sheet loop.

  6. #6
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    MS-Off Ver
    Office 2016
    Posts
    4,641

    Re: Copy chart and change data range

    Hi dflak,

    Indeed so!


    Depending on how the data are laid out, it can also be accomplished using named dynamic ranges

    In fact it probably SHOULD be implemented using dynamic ranges if the various data tables contain significantly different amounts of data


    Thanks for your comments, and keep up the good work!

    Best regards,

    Greg M

  7. #7
    Forum Contributor
    Join Date
    02-18-2013
    Location
    Berlin, Germany
    MS-Off Ver
    Excel 2016
    Posts
    221

    Re: Copy chart and change data range

    Hello,

    my problem is, that all the data table are in the same worksheet (see attached sample). Therefore I can't copy the chart and change the name of sheet in the reference.

    My chart start is in column N. Is there a simple way to generate the same chart for all the other tables in the sheet?
    Attached Files Attached Files

  8. #8
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,957

    Re: Copy chart and change data range

    The explanation is rather long and this tiny window isn't the best word processor, so the explanation is in the word document.
    Attached Files Attached Files

  9. #9
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (both in England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2503 (Windows 11 Home 24H2 64-bit)
    Posts
    90,371

    Re: Copy chart and change data range

    Copied from the Word document:

    Since this is a rather long explanation, I decided to put it in a word document rather than try to post it.

    This dashboard is accomplished using named dynamic ranges defined by the offset command and using these names in the charts.

    A more generic explanation is given in the following two wikis:
    http://www.utteraccess.com/wiki/Offs...Dynamic_Ranges
    http://www.utteraccess.com/wiki/Dynamic_Charting

    I assumed that each data set has an identifying number in column A and that the number matches up with the first row of the data set. I also assumed that there is exactly one blank row between data sets. I had to “cheat” and added an “End” data set in Cell A199, so I could find the end of the last data set.

    There is an old Beatles’ song: I Get by With the Help of my Friends. In this case, I get by with the help of an entire helper sheet.

    Columns A:D are used to find the boundaries of the data ranges. I listed the sets 1-12 (plus End) in column A and use this range for data validation in the drop-down list in Cell C1 on the BT sheet.

    Column B uses MATCH to find the start of each data set. Column C assumes that the end of each data set is exactly two rows above the start of the previous set. The difference (Column D) is the number of rows in the data set.

    When you make a section in Cell C1 on the data sheet, it is reflected in cell G1 on the helper sheet. This value is used by VLOOKUP (against columns A:D) to look up the start row (offset – Cell G2) and number or rows in the data set.

    The baseline row is row 4 on sheet BT, so this amount is subtracted from the sheet position of the offset.

    I decided that rather than shift the range on the GT sheet, I would mirror it on the helper sheet and use that range to plot the chart. Columns J:N do this. This table is set up to plot a maximum of 25 rows in a data set, but you can extend the table to plot more.

    I will use data set 10 as an example.

    Cell J2 is the number of rows to offset from the base cell. Column K is the “X-axis” and columns L:N are the three series.

    Cell J2 is the offset for the beginning of the range. In the example, the data we want starts 154 rows below the base row (Cell B4 on the BT sheet). Each row below that corresponds to one row more on the BT sheet. This information is used to look up the data for columns B (Verfahren), I, J and K which are 2016, 2017 and 2016+2017.

    We now have more data than we need to plot. It will be “masked” with named dynamic ranges.
    Plot_2016 =OFFSET(Plot_Verfahren,0,1)
    Plot_2016_2017 =OFFSET(Plot_Verfahren,0,3)
    Plot_2017 =OFFSET(Plot_Verfahren,0,2)
    Plot_Verfahren​=OFFSET('Helper Sheet'!$K$2,0,0,'Helper Sheet'!$G$3,1)

    Plot_Verfahren is the most important one. Once you have a basic range established, you can define other ranges from it.

    The offset formula has 5 parameters:
    Starting Cell (K2)
    Number of rows to go down (0)
    Number of columns to go right (0)
    Number rows to return (whatever is in Cell G3)
    Number of columns to return (1)

    This pares the data down to the first 11 rows in the “table.”

    These names are then used to build the chart (see references).
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help. It's a universal courtesy.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    NB:
    as a Moderator, I never accept friendship requests.
    Forum Rules (updated August 2023): please read them here.

  10. #10
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,957

    Re: Copy chart and change data range

    Thanks Ali. I find that if I copy / paste from a word document and then go back and edit my post, the text disappears.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Expand Defined Name Range Horizontally to Dynamically Change Chart with new data
    By machl22 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 12-20-2017, 12:14 PM
  2. Change the data range for an existing chart
    By Eebigdog in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-07-2016, 01:13 PM
  3. [SOLVED] Change chart source data range
    By jprlimey in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-06-2015, 10:51 AM
  4. [SOLVED] Change Chart Data Range to show YTD data
    By Harribone in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-04-2015, 04:12 PM
  5. Replies: 0
    Last Post: 08-17-2013, 02:55 PM
  6. Excel 2007 : Copy chart, change data source
    By Mimil in forum Excel General
    Replies: 5
    Last Post: 02-03-2011, 08:08 AM
  7. Chart in Cell & Copy/Pasting Charts w/auto range change
    By longfisher in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 04-01-2008, 05:41 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1