Text Range References in Chart SERIES Formula

Posted on 2012-08-12
Last Modified: 2012-08-13
I have a scatter chart for which I want to use text range references (e.g., $AC$30:$AF$58) as the x-value and y-value ranges in the chart's SERIES formula.  Said another way, I have text strings in cells that I want to use as the x and y values for the chart's SERIES formula.  I can't for the life of me figure out the syntax to make this work.

In the attached file I want to use the "X-VALUES:" and "Y-VALUES:" "built-up" range text as the x and y values, respectively, for the chart's SERIES formula.

Anyboby know how this can be done?


Bill Vallance
Question by:bvallanc
    LVL 24

    Accepted Solution


    1) Using Formula > Named ranges add two ranges using the Indirect formula

    Then on the chart data

    2) edit the range and delete the range (but not sheet part of the formula) press F3, select the named range.

    See attached return...

    Author Closing Comment


    Your solution worked perfectly!  Thanks for the quick response.

    Bill Vallance

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Why You Should Analyze Threat Actor TTPs

    After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

    INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
    Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
    The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
    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.

    760 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

    7 Experts available now in Live!

    Get 1:1 Help Now