How do you make a graph in Excel by varying the value of a certain cell over a certain domain?

Posted on 2011-10-10
Last Modified: 2013-11-05
In my Excel spreadsheet, I have one cell where you input an arbitrary value, multiple large tables of calculations, and then one cell that contains the final result, which ultimately depends on what is in the input cell.

I would like to make a graph that shows how the Result cell varies depending on the Input cell.

For example, I would like Excel to plot "Result vs. Input" over a domain of Input from 0 to 1000 in increments of 1.

I know I could do this manually by manually calculating a table with the values of Input and the values of Result for every integer between 0 and 1000, but that seems like a tedious process that Excel should be able to automate.

How could I achieve my objective?
Question by:sffc
    LVL 3

    Expert Comment

    Hi sffc,
    if i am understanding the problem correctly then you need to run the input values from 0 to 1000 and based upon the calculations each value will generate a specific output in a cell for each input.

    This cannot be done through formula but can be easily done through macro.

    I am uploading a Demo file for your reference.

    you can use the same range for your chart also. Demo.xls
    LVL 41

    Accepted Solution

    @sffc - you can use a data table to simulate what you desire, without the need for VBA code.  See links to examples on how to do that... (Excel 2003)

    Reading the links can make one feel that the process is quite complicated, but its actually quite simple.  Create your calculation depending on the one input variable.  Then create all possible input variables (in this example 0-1000) along column A.  Put the result (just reference the results of the calculation) on column B, one row higher than the list of input variables.  Finally, select the 0-1000 inputs and one column higher, for a 2-column selection.  Then, select (Excel 2007+ use What-if/Data Table, Excel 2003 use DATA->TABLE).  Then, because we've created a columnar list, we need to input the location of the input variable in our 1-variable datatable in the COLUMN input, leaving the ROW input alone.  Then hit OK.  Your data table should then be populated...

    Attached, please find 0-1000 inputs, a "massive calculation" result and single input cell.  After having created the data table, the results are populated for each of the input variables in Column A.

    What's neat about this, is you can change your "massive calculation" which could be HUGE - contain many operations, etc., and your data table will display the results (as fast as it takes to calculate!)

    See attached example. dataTable-Example.xls

    LVL 1

    Author Comment


    Wow, this is just what I was looking for!  I never even realized that Excel had this feature!

    This is perfect, because I was hoping that I wouldn't have to use VBA code because I prefer not to run macros when there is a formula-based or built-in feature that also achieves my objective.

    Thank you very much for your time and assistance! :-)


    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    Suggested Solutions

    Introduction This Article is a follow-up to my Mappit! Addin Article (, it was inspired by an email posting I made to EUSPRIG (, I will briefly cover: 1) An overvie…
    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…
    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.

    745 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

    16 Experts available now in Live!

    Get 1:1 Help Now