Exclude Blank cells while plotting a graph

**kapilrakh** · 04-24-2009, 11:59 AM

Hi there,

I have two columns A and B. I am plotting a graph which takes 100 rows of column A and B as its source data.

The data in both the columns changes with time. This results in fluctuation of no. of rows in which the data is available. So in one time only first 50 rows are there and rest are blank ..whereas sometime later only first 90 rows are there with the data and rest 10 are blank.

I don't want Excel to include these blank cells while plotting a graph.

How do I do that?

Please reply,

Kapil.

**Paul** · 04-24-2009, 01:07 PM

Hi Kapil,

Perhaps this will work for you. I don't have Excel 2003 in front of me, so I hope I'm giving you the correct steps..

Create a chart as you would like to see it based on the values in column A and B (let's say it has 100 rows of data, with headers in A1 and B1). Once that is done, we'll make it dynamic:

Click Insert -> Name -> Define...
Type a new name, such as XAXIS and for the formula, use =OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A:$A)-1,1)

Click Insert -> Name -> Define... again
Type a new name, such as YAXIS and for the formula, use =OFFSET(Sheet1!$B$2,0,0,COUNTA(Sheet1!$B:$B)-1,1)

On your chart, click on one of the data bars or points or pie sections (whichever chart type you used, click on the series somewhere). The formula for the series should show up in the Formula bar, similar to: =SERIES(Sheet1!$B$1,Sheet1!$A$2:$A$100,Sheet1!$B$2:$B$100,1)

Change that formula in the Formula bar to: =SERIES(Sheet1!$B$1,Book1!XAXIS,Book1!YAXIS,1)

(Note: If your defined names are at the sheet level, not the workbook level, change 'Book1' to 'Sheet1' or your sheet name.)

Once you press Enter you should be all set. Remove data rows, add them.. your chart should update.

Hope that helps!

**kapilrakh** · 04-24-2009, 01:53 PM

I didn't quite get the last line

"Remove data rows, add them.. your chart should update."

I followed the steps you mentioned before it.

**Paul** · 04-24-2009, 03:48 PM

What that means is:

If you have 100 rows of data, your chart should reflect that. If you then add more rows of data to columns A/B (or remove rows of data), the chart should automatically update right before your eyes.

So if you deleted 10 rows (A91:B100, for example), the chart should update instantly to reflect the new 90 rows of data.

**purpleaddict** · 03-24-2011, 10:46 AM

Hi,

I have the exact problem now but I got lost with "Click Insert -> Name -> Define..."

I am using Excel 2007 and I don't seem to see "Name" in the ribbon after clicking "Insert"

Appreciate your urgent help on this. Thanks.