0

I have a graph for a formatted table in Excel.

I have a classic issue with empty values that are reflected on graph as zero and the "Connect data points with line" option is disabled. I can't check it.

Hidden and Empty Cells Settings

I googled but never found the answer. Does anybody know why?

UPDATE: SOLUTION: After I added more columns for the graph and removed the heading column from the graph, the options "Gaps" and "Connected data points with line" became enabled.

Alexandr Meyer
  • 23
  • 5
  • If your data for the y-values is in B1:B30 (say) then in cell C1 type the formula `=IF(LEN(B1:B30)>0,B1:B30,na())` and then use column C for your y-values in the chart – DS_London Jan 25 '22 at 14:29
  • Please refrain from asking non-programming questions here. This question is better suited for https://superuser.com/. Stack Overflow is for *programming* questions. – BigBen Jan 25 '22 at 14:34
  • Sorry, I didn't know about superuser.com. Will keep posting here just programming stuff. Thank you for the tip. – Alexandr Meyer Jan 26 '22 at 09:11
  • You should be ok on here, the brief says "or complex formula development" in it... Part of: Questions tagged [excel] Only for questions on programming against Excel objects or files, or complex formula development. You may combine the Excel tag with VBA, VSTO, C#, VB.NET, PowerShell, OLE automation, and other programming related tags and questions if applicable. General help regarding Excel for single worksheet functions is available at Super User (http://superuser.com/). – Solar Mike Jan 26 '22 at 09:36

1 Answers1

2

I got round something similar by duplicating the data series and replacing zero values with #NA, then my chart worked fine.

Not found that answer yet, but it is on here somewhere:

enter link description here

Solar Mike
  • 7,156
  • 4
  • 17
  • 32
  • It's hopefully on https://superuser.com/ since this question is not about programming :-) – BigBen Jan 25 '22 at 14:25
  • @BigBen oh so **real** programmers don't use Excel? – Solar Mike Jan 25 '22 at 14:28
  • No, they do, but this question is not about *programming*. It's about a chart. That is general Excel functionality and not on-topic here. The Excel tag says "only for questions about programming against Excel objects or files..." – BigBen Jan 25 '22 at 14:29
  • @BigBen questions on here don't need to be about 5 or 5000 lines of code, but using functions and tricks to get data or charts to behave as needed. – Solar Mike Jan 25 '22 at 14:30
  • Eh, no they don't. Read the tag again. This is a classic question for SuperUser. – BigBen Jan 25 '22 at 14:31
  • @BigBen then you will be deleting several thousand incorrect questions that are on here about excel issues... – Solar Mike Jan 25 '22 at 14:31
  • No they should be *migrated* to where they belong. – BigBen Jan 25 '22 at 14:31
  • @BigBen Goood luck - will watch your progress with that task. – Solar Mike Jan 25 '22 at 14:32
  • Sorry guys, I didn't know about superuser.com. Will keep posting here just programming questions. Thank you – Alexandr Meyer Jan 26 '22 at 09:12
  • @SolarMike Thank you. Unfortunately, it ignored for #NA for me, but your idea of copying the data lead me to some ideas of creating a formula for calculating automatic average data. Will continue with researching and asking on superuser.com – Alexandr Meyer Jan 26 '22 at 09:14
  • @AlexandrMeyer so did you use na() ? – Solar Mike Jan 26 '22 at 09:20
  • @SolarMike . I guess I am doing something wrong, I added =NA() function, it generated #N/A text and graph keeps showing 0. – Alexandr Meyer Jan 27 '22 at 10:29
  • See this, and give it a vote if it helps: https://stackoverflow.com/a/69077964/4961700 – Solar Mike Jan 27 '22 at 14:00
  • @SolarMike Thank you for your help. I believe in a similar situation it will help and I will vote and mark your comment as a solution. – Alexandr Meyer Feb 01 '22 at 08:14