Results 1 to 1 of 1

Thread: Dynamic Chart That Expands As More Data Is Added

  1. #1
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10

    Dynamic Chart That Expands As More Data Is Added

    I recently got a request from a friend to help him create a dynamic chart that expands as data is added to the source. As I put it together, an onlooker said that it doesn't look as easy as you made it look. And I thought that was right. Even some of the experienced Excel users tend to consider this a not-so-easy task. On the contrary, I think it's one of the most easiest tricks to master.

    This is how you do it.

    Let's take a simple column chart. For extra effect, I've changed one of the 3 series in to a line chart also within the same chart group

    Dynamic Chart.png

    The source data should expand all the way from B3:E50 excluding the header row, depending on how many rows of data we have. So for example, if there are 10 rows of data, the source of the chart should only be from B3:E12. If there are 15, the source should automatically expand to B3:E17.

    OK, now that we know what is expected, let's find out how this can be achieved.

    Excel has something called a Dynamic/Defined Named Range

    We will assume that the date column will be our key to identify how many rows there are in the source data. And we will give the name DateList to the source column.

    Here's how you do it. Go to the Name Manager and create the defined name mentioned above as

    =OFFSET(Chart!$B$3,,,COUNT(Chart!$B$3:$B$50),)

    Now, create defined named ranges for the data columns with Workbook scope. To know more about scope of named range, read the Name Manager link posted above.

    Column1=OFFSET(DateList,,1,,)
    Column2=OFFSET(DateList,,2,,)
    Column3=OFFSET(DateList,,3,,)

    I've used Column1, Column2, Column3 here, but it is always better to give a more meaningful defined name than what I've used above.

    I will assume that you already know how to create a chart. Now, create the desired chart with some dummy data. It should look like the picture above. Now select one of the series within the chart, and look at the formula bar. You should see something like this.

    =SERIES(Chart!$D$2,Chart!$B$2:$B$5,Chart!$C$2:$C$5 ,1)

    Replace this with

    =SERIES(Chart!$D$2,'Dynamic Chart.xlsm'!DateList,'Dynamic Chart.xlsm'!Column2,1) where Dynamic Chart.xlsm is your workbook name.

    Note the use of the apostrophe ('). This is necessary when using workbooks that have a space in their file name. Also, you can play around with the order of the series, by modifying the last argument (in this case, it is 1)

    Repeat this for all the 3 series. You should now get what I've done in the attached file. Enjoy
    Attached Files Attached Files
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

Similar Threads

  1. Excel Dynamic Waterfall Chart
    By Excel Fox in forum Excel and VBA Tips and Tricks
    Replies: 2
    Last Post: 10-08-2015, 05:33 PM
  2. Data Validation With Dynamic List Of Sheet Names
    By TomyLee in forum Excel Help
    Replies: 2
    Last Post: 08-19-2013, 02:40 AM
  3. Update All Tables With Newly Added Data
    By redja71 in forum Access Help
    Replies: 5
    Last Post: 07-16-2013, 07:06 AM
  4. Dynamic Chart Query
    By leopaulc in forum Excel Help
    Replies: 6
    Last Post: 11-26-2012, 04:50 PM
  5. 2007 PPT Chart Data is not Reflecting In Chart
    By littleiitin in forum Powerpoint Help
    Replies: 2
    Last Post: 04-28-2012, 01:42 PM

Tags for this Thread

Posting Permissions

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