Cannot create graphs in Excel - 'there's an error in the formula you entered'

[surfing96]

I have recently noticed that when try and create a line or scatter graph, as I select 'add series' I initially see a plot with a data point at 1,1 x,y and then as I select the data series cells an error message pops up stating 'there's an error in the formula you entered'.

Attached is a document with screen shots showing the sequence of events for a dummy table.

The actual table is similar to the one below where X series is in blue and the 3 Y series data in yellow .

well - you would have been able to see the image if this platform was able to insert the 13kb png image i selected when i clicked on upload. No idea why it didnt insert here?
i have attached it instead

[6StringJazzer]

Pictures have very limited value. Please attach your Excel file.

[MrShorty]

=[1]+range is not a valid reference in a chart. When editing the ranges in the select data dialog, you need to delete the [1] before pointing to the range you want.

[surfing96]

For avoidance of doubt I do delete the '=[1]+range' before selecting the actual data with my mouse.

Also please do not suggest I highlight the whole table and then select recommended graph,

I have attached an excel 2019 workbook with a simple 'dummy' table for a line/scatter graph and an actual data set where the X series is highlighted in bule and 3 Y series highlighted in yellow.
please let me know how you get on.
thanks

[MrShorty]

Also please do not suggest I highlight the whole table and then select recommended graph,

I never do this myself, so I wouldn't suggest it for you.

test 1 tests:

1) Select E8:H10 -> Insert scatter chart -> switch row columns -> done.
2) Clear E7 (because for some reason Excel since forever has wanted/demanded an empty cell in the upper left corner of a chart data range when creating the chart). Select E7:H10 -> Insert scatter chart -> switch row/column -> done. Enter "time" in E7 to restore the text label to that cell.
3) Clear E7. Select E7:H10 -> Insert line chart -> switch row/column -> done. Enter "time" in E7.

I'm not sure what difficulties you are having with data arranged like in test 1, but the main issue that I think people run into is not being aware of the need for an empty cell in the upper left corner of the data range while creating the chart.

[MrShorty]

test 2 tests:

1) Select multi area range E8:E10,J8:J10,N8:N10,R8:R10 (hold ctrl while selecting each area) -> Insert scatter chart -> switch row/column -> done.
2) Clear E7 (same reason as before) -> Select multi area range E7:E10,J7:J10,N7:N10,R7:R10 -> insert scatter chart -> switch row/column -> done. Enter "hours" in E7.
3) Clear E7. Select multi area range E7:E10,J7:J10,N7:N10,R7:R10 -> insert line chart -> switch row/column -> done. Enter "hours" in E7.

I didn't use the Select Data Dialog for any of this, because I have enough experience with how Excel parses data ranges at chart creation that I could select the data range before creating the chart in a way that Excel should correctly parse the data ranges. Is that enough, or do you still want to explore how to use the Select Data dialog to do the same thing?

[surfing96]

hello
your instructions worked. However unless I am mistaken, when I used Select Data in office 2013 I cannot recall experiencing this type of error message. I have another PC running Office 2016 and seen the same behaviour.

I respect to either Test tables, what if at some point in the future I want to add another series? I did try this with Test 2 table i.e. added series 4 and used select data. Again I saw the same error message - pls see attached image

is there a solution for this scenario.
I never had this hassle with office 2010 and 2013 - MS must have changed the something between 2013 and 2016- usually for the worse.

[MrShorty]

I don't have a newer version to test on, but here's what I can do in my older version:

Recreating the error:
1) Select E8:G10 and insert a scatter chart.
2) With the chart selected, open the Select Data dialog and add a data series. I get the same dialog you show in your picture. If I click into the "Y values" field, I get an "insert" cursor. When I then click into the spreadsheet to select the Y values range, it gives me the same thing you show in your picture ={1}+range which is invalid, and I get the same error message.

Fixing after getting the error message: After dismissing the error message, I can place my cursor in the midst of the "{1}+" string of characters and use backspace/delete to remove these.

Avoiding the error message: When I click into the Y values editing field, be sure to select the entire field and press delete to clear the ={1} from this field before clicking into the spreadsheet to select the range.

I'm not sure what would have changed between 2013 and 2016 or why you have not had encountered this issue before. As I noted before, the main idea is to get rid of the {1} before or after selecting the range you want to use. If there is a difference between versions in this behavior, then it is probably buried deep inside of Excel and how Excel decides when to "append" to an input field and when to "replace/overwrite" what is in an input field.

I don't know if that helps at all, but that is what I have seen/am seeing in this scenario.

[surfing96]

Ahh - i think i have discovered why i am seeing these issues.

What I have been doing is highlighting the '={1}' term and then selecting the Y data thinking (obviously mistakenly)that the selected cells that appear in the field 'Series Y values:' will overwrite the ={1} term.

Whilst this may work in word or other document where one can copy a word from one section of the text and then by highlighting the word for replacement, one can paste directly on top.

I don't know if I simply forgot to delete the ={1} or coming from office 2013 I thought by highlighting this term and pasting over it would delete it- or simply a 'school boy error' !!

oh well - you live and learn. I wasted hrs on this.

But thanks for shedding light on this matter.

[6StringJazzer]

If your question has been answered please mark your thread as "Solved" so that members will know by looking at the thread title that your problem is solved. Go to the menu immediately above your first post to the thread and click on Thread Tools. From the dropdown menu select "Mark this thread as solved..."

[surfing96]

Unfortunately I can't see the thread tools
I am accessing this thread on my phone i wanted to attach a screen shot to illustrate this matter but can't find the means to do so.