Excel: Dynamic Chart Ranges

Posted on 2014-08-13
Last Modified: 2014-08-14

I'm seeking your help on creating a dynamic range for my Excel 2010 chart (attached).

In the yellow cell (B2), is an integer user field. I have tried to link this to an offset formula that calculated a charts x & y axis (see green cells).

I would like the charts range to update whenever a user edits B2. I tried creating a named reference for the chart series, but it didn't work.

Can you tell me what I'm doing wrong?
Question by:dabug80
    LVL 50

    Accepted Solution


    you need to place the Offset formula into named formulas (or range names) and then use these in the chart.  You need one range name for the X axis labels and one for the data series.
    May I suggest that you add another cell where the user enters a starting year? Let B3 have that value as an integer.

    Now create two range names with Formulas ribbon > Name Manager > New

    chtXLabels refers to =OFFSET(Sheet1!$D$6,Sheet1!$B$3,0,Sheet1!$B$2,1)
    chtSeries1 refers to =OFFSET(chtXLabels,0,1)

    Now edit the chart series. In the Series Values enter

    Edit the Horizontal Axis Labels values and enter

    You must precede the named range with the sheet name or the file name when you use range names in charts.

    See attached file

    cheers, teylyn
    LVL 27

    Expert Comment

    by:Glenn Ray
    Sounds like you had the right idea about creating a named range, but you just needed to build a new data series with that name, rather than trying to modify an existing data series.

    I created a named range called "ChartRange" using the following formula:

    Then I added a new data series and used this specific reference for the series values
    (Note inclusion of "Sheet1!" in the reference; that is key).

    I've attached a modified version of your file.

    LVL 1

    Author Closing Comment

    Thanks for also providing the dynamic range change

    Featured Post

    Do You Know the 4 Main Threat Actor Types?

    Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

    Join & Write a Comment

    Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
    Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
    Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
    This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now