CODEWITHSUNDEEP
excelgraphscatter
Fabio Masino
Fabio Masino

Reputation: 67

Excel, how to create a dynamic scatter chart that ignores blank cell

as the title says, I want to create a dynamic scatter chart that ignores blank cells. For example:

COLUMN A
1000
2000
501

700

1000

The result I want is that:

enter image description here

http://animenod.altervista.org/Immagine.png

The column A contains a lot of values (even blank) and they are inserted through formula, and I wish that when I add a data, the chart will update without any jump or zeros, as the image shows.

Thanks in advance.

Fabio

Upvotes: 0

Views: 1898

Answers (1)

Wiktor Stribiżew
Wiktor Stribiżew

Reputation: 626845

I guess you need to select Select Data... > Hidden and Empty Cells > Connect Data Points With Line, and make sure Show data in hidden rows and columns is not checked. To get rid of the blank row indices, hide the rows with blank values.

If you cannot filter the data column manually, you can do it automatically with a macro:

1) Open a VBA Editor (ALT+F11) 2) Double-click the sheet name on the left 3) Paste this code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 Then
 If IsError(Target) Then
  Target.EntireRow.Hidden = True
 Else:
  If Len(Target.Value) < 1 Then Target.EntireRow.Hidden = True
 End If
End If
End Sub

Whenever you add new data, the cell will get analyzed, and the row will get hidden if the cell has no text in it.

enter image description here

Upvotes: 1

Related Questions