Microsoft Excel - Excel 2010 Graphs "the data range is too complex to be displayed"

Asked By Lisa G on 03-Jan-13 11:56 AM

Excel 2010

I have an excel sheet with monthly data across the columns. They are titled: Jan, 2013 ---- Feb, 2013 --- Mar, 2013 --- thru Dec, 2013. I then have 3 additional columns titled 2013 YTD --- 2012 TTLS --- 2011 TTLS.

I have a graph to display the data. I need the graph to show the monthly numbers, the 2012 TTLS and 2011 TTLS (for reference/comparison). I do not want the 2013 YTD on the graph. In my spreadsheet for 2012 data, it worked with displaying the 2011 numbers, without the 2012 YTD (it is set up the same way, monthly data first, then YTD, then 2011 TTLS). Now, as I am creating the 2013 graph, I get the response "The data range is too complex to be displayed. If a new range is selected, it will replace all of the series in the Series panel."

How can I have 2012 & 2011 data displayed on the graph?

Thank you!

Harry Boughen replied to Lisa G on 03-Jan-13 02:57 PM

Hi Lisa,
You don't say what sort of chart you are working with. I am assuming it is a bar chart with the titles along the x-axis. I am also assuming that you are modifying an existing (copy) sheet that will show one column for the 2011 TTLS.
Click on the visible column and the chart ranges will appear in the formula bar. Click in here and change the ranges in the second part of the range pairs for the two axes.
=SERIES(,(Sheet1!$A$1:$L$1,Sheet1!$N$1:$N$1),(Sheet1!$A$2:$L$2,Sheet1!$N$2:$N$2),1)
would become
=SERIES(,(Sheet1!$A$1:$L$1,Sheet1!$N$1:$O$1),(Sheet1!$A$2:$L$2,Sheet1!$N$2:$O$2),1)
HTH
Harry

Lisa G replied to Harry Boughen on 04-Jan-13 01:59 PM

Thank you, Harry,

Yes, it is a bar graph (I also tried a line graph, with the same results). The dates are across the top, with percentages on the left side. I don’t know if this is a copied graph or if I just deleted the copied graph and replaced it with a new one. I have tried many different ways to make this work, so I’m not sure what all I tried. When I go into the graph and “select data” I am editing the Horizontal (Category) Axis Labels. When the box opens, I am clicking on the labels I want, which are appearing on the top of the columns of the data I have in the spreadsheet. When I click on the titles I want, this is what is showing in the Axis Labels box:

=’SHEET NAME’!$C$5:$N$5,’SHEET NAME’!$P$5:$Q$5

My columns are set up, with the data labels in C5 thru Q5. As I said earlier, my titles are: Jan, 2013 : Feb, 2013 : Mar, 2013 : etc, thru Dec, 2013 (C5 thru N5). Then I have “YTD 2013” (in O5, which I do not want in the graph), then I have CY 2012 (in P5) and CY 2011 (in Q5). So, the titles are in C5:N5, P5:Q5.

In regards to your formula, I am not sure where you are referring to in the second parentheses when you are entering cell #s A2:L2, N2:O2: “Sheet1!$N$2:$N$2,Sheet1!$N$2:$N$2”

Does this make sense?

The data I am trying to graph is on line numbers 6, 10, 14, 18, 22, 26, 30, 34 & 38, in the columns described, above. The data is for 9 locations. So, the data is in C6:N6,P6:Q6 -- C10:N10,P10:Q10 –- C14:N14,P14:Q14 etc.

Thank you for your help,

Lisa

Harry Boughen replied to Lisa G on 04-Jan-13 10:16 PM

graph_bar.zip

Hello Lisa,
This file contains a couple of versions of what I think you are talking about and there does not seem to be any problem. It is an earlier Excel version file but I don't think that there should be any difference for later versions for something so basic.

Let me know if that is not what you want and if you can't fix yours as a result, if you can extract the relevant data and chart to a zip file and post it. that would be good.

Let me know how you go.

Harry

Lisa G replied to Harry Boughen on 07-Jan-13 10:56 AM

Harry,

Thank you very much! It worked!

I am still wondering why Excel 2010 had a problem with the date range. What version were you using to build those graphs?

Lisa

Harry Boughen replied to Lisa G on 07-Jan-13 02:46 PM

Hi Lisa,
I was using Excel97. I haven't had a chance to try it on 2010 yet but I would be very surprised if it was any different in 2010. As soon as I access the new version I will try it but I suspect that it was something to do with how you were trying to go about it in terms of selecting/updating the ranges on the chart. I will try to reproduce the problem.
Regards
Harry

Harry Boughen replied to Lisa G on 07-Jan-13 07:03 PM

Hello again Lisa,
I have now had a crack at 2010 and tried all sorts of variations but I have not been able to get your error.This article by Jon Peltier might help you in deciphering what was going wrong.
http://peltiertech.com/WordPress/chart-source-data-highlighting/
Regards
Harry

Harry Boughen replied to Lisa G on 07-Jan-13 10:55 PM

Hello again again Lisa,
I did manage to get the same message as you when I went into the select data window. Whether this is the cause of your problem or not remains to be seen. How I managed to achieve this was to give the sheet a name as long as the syatem would allow (31 characters) and selecting every cell in the range individually (C, D, E... rather than C:N etc).
I gather that there is some sort of a limit on the number of characters (1024) allowed in the range specification.
However this is a highly artificial scenario and I feel sure that your problem had a more mundane explanation.
Regards
Harry

Lisa G replied to Harry Boughen on 08-Jan-13 12:54 PM

Thank you, Harry, for following up on this.

Would it be helpful for you if I put together a sheet with a graph for you to play with? I will have to sanitize it as I work with extremely confidential information. I will be in a meeting all afternoon, so it may be a day or two before I can prepare it.

Please let me know if you would like for me to do that.

Lisa

Harry Boughen replied to Lisa G on 08-Jan-13 02:54 PM

Hi Lisa,
It would be interesting to know what the exact problem was so if you can make one that shows the fault then I would be keen to try to explain what the problem was. Some precise navigation details to get the the fault condition would be good as well.
Regards
Harry