I am trying to create a scatter plot in Excel 2010 using dynamic named ranges and am having trouble getting it to work. Here's simple example that is failing:

  1. Open Excel, starting a new workbook

  2. Enter some data: Numbers entered in cells A1:B5

  3. In cell D1, enter: $A$1:$B$5. (In my real sheet, this is dynamically computed, but manual entry still has the problem).

  4. On the ribbon, click Formulas, Define Name. Define MyRange1 as a sheet-local name using =INDIRECT(Sheet1!$D$1) as shown below: New Name dialog

  5. Click OK, and then insert a scatter chart.

  6. Open the "Select Data" dialog and enter ='Sheet1'!MyRange1 Select Data dialog

  7. Excel crashes...

Microsoft Excel has encountered a problem and needs to close.

The problem occurs both on Windows XP and Windows 7 with Excel 2010 in both cases, repeatable every time.

I've also tried:

  • Defining separate ranges for x and y data and using the Edit Series dialog. After entering ='Sheet1'!MyXRange in the X value field, Excel stops accepting keyboard and mouse input except for the Escape key which exits the dialog. If I go back to the dialog only then does it crash.

  • Scoping the named range to the workbook instead of the worksheet. This actually does stop the crash, but I get errors in the Select Data dialog depending on whether I type =MyRange1 or ='Sheet1'!MyRange1:

Reference is not valid. A formula in this workbook...

Is this a known issue, or is there somewhere to report it? I don't have Excel 2007 or 2003 here to check if the problem is isolated to 2010. If I can't get this working I'll probably just use VBA instead of dynamic named ranges.

Update: I thought I figured it out (I posted an answer, now deleted). I changed the value in cell D1 = $A$1:$B$5 to D1 = 'Sheet1'!$A$1:$B$5, and the chart is created properly. However, it seems when the chart was created it is not dynamic -- it just used the current values to create the X and Y series, so changing D1 does not make the chart update.

  • Instead of trying to create a dynamic named range. Use a set range as the placeholders for your data in the scattergraph. Update that set range dynamically through the indirect function. – wbeard52 Aug 20 '12 at 19:20

According to the link below and my personal testing of it, you must enter the data series in the form of


If your name is dynamic, then the resulting chart will be too. The formula I used for my dynamic named range was (modify for your situation and use for both X and Y ranges).


That worked to make the chart dynamic as far as adding newly appended values to the chart (required both X and Y values before chart updated).

Source for how to Setup Chart - Microsoft Q&A Site Source for hot to use Dynamic Ranges - OZGrid

  • I also found that inputting the sheet name in front will also do the trick with the offset in the named range 'Sheet1'!RangeNameX – wbeard52 Nov 17 '15 at 20:25
  1. When INDIRECT is used to define ranges, charts often don't use the ranges correctly, and often don't even accept these named ranges. There are better (more robust) ways to define dynamic ranges, using for example INDEX or OFFSET.

  2. Even if the name would be recognized by the chart, it will be converted to a cell address in the Chart Data Range box of the Select Data Source dialog. Names only persist in the ranges for X, Y, and series name for each individual series.

  3. If you prefix the range with the sheet name in cell D1? Charts need fully qualified ranges, so if cell D1 contains Sheet1!$A$1:$B$5 you can use your defined name in the Chart Data Range box of the Select Data Source dialog. Note that, per point 2, Excel will convert this range to its cell address when you click OK.


Here is how I create a dynamic chart.

  1. Create a table from your data.

    enter image description here

  2. Highlight the table
  3. Go to the insert tab and select the type of chart you want.

enter image description here

When you add data to the table, it will be updated to the chart as well.

enter image description here

  • I wasn't able to use the formula reference as you were trying, although I didn't receive an error. I don't believe that is the proper use for that function. – CharlieRB Aug 20 '12 at 19:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.