1

My question is similar to this one: Dynamic chart range using INDIRECT: That function is not valid (despite range highlighted), however for pie charts it doesn't seem to work.

When I specified the series through named ranges, and pressed Enter, Excel expanded the named range to the specific range.

Here is a spreadsheet that you can try: test_pie_chart03.xlsx

The SERIES I typed is this one:

=SERIES(,'Raw Data'!Slices01[Description],'Raw Data'!Slices01[Value],1)

As soon as I pressed enter Excel changed it to:

=SERIES(,'Raw Data'!$A$14:$A$33,'Raw Data'!$B$14:$B$33,1)

Is there another way to specify a dynamic range for a pie chart?

Thanks

Community
  • 1
  • 1
boggy
  • 3,674
  • 3
  • 33
  • 56

2 Answers2

1

When using range names for the chart source, you cannot use this syntax that refers a multi-column range with a filter on a column. You will need to create two range names, one for Value and one for Description and plug these into the chart formula. Once you confirm the formula (or enter the range name via the "Select Data" dialog), Excel will replace the sheet name with the workbook name.

enter image description here

On a separate note: with that many data points, a pie chart is not a good choice for data visualisation. Consider a horizontal bar chart instead.

teylyn
  • 34,374
  • 4
  • 53
  • 73
  • That explains why the instructions for the other post didn't work for me. I thought all the named ranges are created equal. Anyway, based on your answer I managed to define Description & Value ranges based on the other ranges, i.e. Slice01[Description] & Slice01[Value]. As soon as I added a row to Slice01, the pie chart was updated. I also agree with your note, this is just experimental. Thanks! – boggy Apr 24 '14 at 17:32
  • Hi, sorry, it appears that my joy was premature :-( I followed your instructions, but what happens is that after you close excel and re-open the spreadsheet, the SERIES call has expanded ranges, i.e. the references to test_pie_chart03.xlsx!Description & test_pie_chart03.xlsx!Value were replaced with =SERIES(,'Raw Data'!$A$14:$A$34,'Raw Data'!$B$14:$B$34,1). I am using Excel 2010 14.0.7116.5000 (32 bit) – boggy Apr 24 '14 at 17:44
  • How do you define the range names? Absolute reference or formulas? – teylyn Apr 24 '14 at 22:43
  • Initially I was excited that I can define Description based on =Slices01[Description] and Value based on =Slices01[Value]. That seemed to work until I re-opened the spreadsheet. Then I thought of defining the named ranges based on absolute references and I defined Description based on ='Raw Data'!$A$14:$A$34 and Value based on ='Raw Data'!$B$14:$B$34. Unfortunately, after I saved, closed and re-opened the spreadsheet the SERIES function call included absolute references and no named ranges. – boggy Apr 24 '14 at 22:51
-1

Slices01 references cells A14:B33 Slices references the cell A36.

Replacing the named ranges with the referenced cells is normal in Excel. You can use them to select a range but should always replace with the referenced areas, as it did in your excel file.

mcadio
  • 737
  • 7
  • 27
  • Did you look at the test_pie_chart03.xlsx spreadsheet? There is no pmt named range in that one. – boggy Apr 24 '14 at 00:42
  • My bad on that. I was looking at a different spreadsheet, and saw yours once I closed out the other. – mcadio Apr 24 '14 at 00:51
  • I want to specify a dynamic range, i.e. if the number of rows in the range increases the chart would adjust automatically... – boggy Apr 24 '14 at 00:53
  • 1
    @user3561890, with all due respect, but that is nonsense. – teylyn Apr 24 '14 at 04:20