# MS Excel question re: changing range on chart from a cell **URL:** \ **Category:** Factual Questions\ **Created:** [March 17, 2004, 10:51pm UTC](https://boards.straightdope.com/t/ms-excel-question-re-changing-range-on-chart-from-a-cell/234928 "2004-03-17T22:51:53Z")\ **Posts on this page:** 10\ **Page:** 1
**Author:** ![kasuo](https://avatars.discourse-cdn.com/v4/letter/k/8c91f0/32.png) [@kasuo](https://boards.straightdope.com/u/kasuo)\ **Post date:** [March 17, 2004, 10:51pm UTC](https://boards.straightdope.com/t/ms-excel-question-re-changing-range-on-chart-from-a-cell/234928/1 "2004-03-17T22:51:53Z")
Is it possible to change the range of a chart from a cell on a sheet? For example, I have the following data: ``` A B ``` 1 2/1 413.1 2 2/2 8.5 3 2/3 19.8 4 2/4 99.5 Say the chart was reading =Sheet1!$A$1:$B$3 but later on I wanted to change the range to =Sheet1!$A$2:$B$4 without going into the Chart Options but instead have it done from cell C1 where C1 would have the data for the range for the chart to read from? ---
**Author:** ![kasuo](https://avatars.discourse-cdn.com/v4/letter/k/8c91f0/32.png) [@kasuo](https://boards.straightdope.com/u/kasuo)\ **Post date:** [March 19, 2004, 5:22pm UTC](https://boards.straightdope.com/t/ms-excel-question-re-changing-range-on-chart-from-a-cell/234928/2 "2004-03-19T17:22:59Z")
Anyone? ---
**Author:** ![TellMeI\_mNotCrazy](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/tellmei_mnotcrazy/32/3419_2.png) [@TellMeI\_mNotCrazy](https://boards.straightdope.com/u/TellMeI_mNotCrazy)\ **Post date:** [March 19, 2004, 5:37pm UTC](https://boards.straightdope.com/t/ms-excel-question-re-changing-range-on-chart-from-a-cell/234928/3 "2004-03-19T17:37:38Z")
It took me a while to figure out exactly what you’re asking. One of my coworkers is our excel whiz. I’ll ask him when he comes back from lunch. I can’t think of a way off hand. ---
**Author:** ![kasuo](https://avatars.discourse-cdn.com/v4/letter/k/8c91f0/32.png) [@kasuo](https://boards.straightdope.com/u/kasuo)\ **Post date:** [March 25, 2004, 2:06am UTC](https://boards.straightdope.com/t/ms-excel-question-re-changing-range-on-chart-from-a-cell/234928/4 "2004-03-25T02:06:36Z")
Anyone can help me with this? ---
**Author:** ![Laughing\_Lagomorph](https://avatars.discourse-cdn.com/v4/letter/l/6bbea6/32.png) [@Laughing\_Lagomorph](https://boards.straightdope.com/u/Laughing_Lagomorph)\ **Post date:** [March 25, 2004, 2:51am UTC](https://boards.straightdope.com/t/ms-excel-question-re-changing-range-on-chart-from-a-cell/234928/5 "2004-03-25T02:51:23Z")
You want to be able to change the range of the data in the chart from a cell on the worksheet? I don’t know of a way offhand that can be done, but I have to confess it never occured to me to try. Some bigger Excel charting whiz than me is going to have to check in. ---
**Author:** ![aerodave](https://avatars.discourse-cdn.com/v4/letter/a/f08c70/32.png) [@aerodave](https://boards.straightdope.com/u/aerodave)\ **Post date:** [March 25, 2004, 4:55am UTC](https://boards.straightdope.com/t/ms-excel-question-re-changing-range-on-chart-from-a-cell/234928/6 "2004-03-25T04:55:59Z")
I haven’t got it totally figured out, but what about referencing some intermediate cells instead of the original data? You may not be able to change the reference of the chart without going into its options. But you might be able to point the chart at a new range of cells that you create. In this range of cells you have references to to original data set. You can change those references as you wish without leaving the spreadsheet. Maybe you want to reference the first 7 rows, or the last 7 rows. To change to either one, you just change the formula in the new cells. There may be some elegant, ingenious ways of doing that, or you could brute force it. But that way, you never mess with your original data. ---
**Author:** ![TellMeI\_mNotCrazy](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/tellmei_mnotcrazy/32/3419_2.png) [@TellMeI\_mNotCrazy](https://boards.straightdope.com/u/TellMeI_mNotCrazy)\ **Post date:** [March 25, 2004, 5:09am UTC](https://boards.straightdope.com/t/ms-excel-question-re-changing-range-on-chart-from-a-cell/234928/7 "2004-03-25T05:09:32Z")
> [@audilover](#): > > I haven’t got it totally figured out, but what about referencing some intermediate cells instead of the original data? > > You may not be able to change the reference of the chart without going into its options. But you might be able to point the chart at a new range of cells that you create. In this range of cells you have references to to original data set. You can change those references as you wish without leaving the spreadsheet. Maybe you want to reference the first 7 rows, or the last 7 rows. To change to either one, you just change the formula in the new cells. > > There may be some elegant, ingenious ways of doing that, or you could brute force it. But that way, you never mess with your original data. I tried that too, didn’t work for me, and… I never did get an answer from that coworker… I’ll ask again tomorrow, **kasuo**. ---
**Author:** ![K364](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/k364/32/5_2.png) [@K364](https://boards.straightdope.com/u/K364)\ **Post date:** [March 25, 2004, 5:42am UTC](https://boards.straightdope.com/t/ms-excel-question-re-changing-range-on-chart-from-a-cell/234928/8 "2004-03-25T05:42:04Z")
Have a look [here](http://support.microsoft.com/default.aspx?scid=kb;en-us;183446) ---
**Author:** ![TellMeI\_mNotCrazy](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/tellmei_mnotcrazy/32/3419_2.png) [@TellMeI\_mNotCrazy](https://boards.straightdope.com/u/TellMeI_mNotCrazy)\ **Post date:** [March 25, 2004, 11:50am UTC](https://boards.straightdope.com/t/ms-excel-question-re-changing-range-on-chart-from-a-cell/234928/9 "2004-03-25T11:50:31Z")
> [@K364](#): > > Have a look [here](http://support.microsoft.com/default.aspx?scid=kb;en-us;183446) Is it me, or does that seem like an awful lot of trouble when you can just right click and change source data? 😉 I mean unless you’re changing the data range 400 times a day… ---
**Author:** ![Peter\_Morris](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/peter_morris/32/359_2.png) [@Peter\_Morris](https://boards.straightdope.com/u/Peter_Morris)\ **Post date:** [March 25, 2004, 3:50pm UTC](https://boards.straightdope.com/t/ms-excel-question-re-changing-range-on-chart-from-a-cell/234928/10 "2004-03-25T15:50:24Z")
Its easy. Suppose you want to use cell D1 to show the start of your desired range, and E1 to show the end. Create the following macro : \*\* Sub ChangeRange() ’ ’ Macro1 Macro ’ Macro created 25/03/2004 by Peter ’ ’ my\_new\_range = Range(“d1”).Value & “:” & Range(“e1”).Value ActiveSheet.ChartObjects(“Chart 1”).Activate ActiveChart.ChartArea.Select ActiveChart.SetSourceData Source:=Sheets(“Sheet1”).Range(my\_new\_range), PlotBy:= \_ xlColumns End Sub \*\* Right click on your chart, and select “assign macro.” Select ChangeRange and press OK. Now, to change the range of the chart, you just edit cells d1 & e1, then click on the chart and it will update. HTH.