MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Charting and Blank Cells

Posted by Cindy on December 17, 2001 12:47 PM

I would like to create a chart that would chart, for example, A1:B30. A1 through B10 currently contains data. However, the rest of the range is blank. I would like to create the chart for the ENTIRE range but only view the data when that range become populated with data. I would add data every day of the month, but do not want to view blanks in my chart, nor do I want to recreate my chart every day when adding new data. Any ideas???
Have a great holiday if I don't hear from you by then.
Thanks in advance.

Posted by Juan Pablo G. on December 17, 2001 12:54 PM

You have two basic options.

First one, the easy one, hide rows 11:30 and each day "unhide" the next row.

Second one, work with Dynamic Name Ranges, something like this. Go to Insert - Names - Define. Create a name called "Items" and put this in the refers to:

Create another name, "RangeX", and use this formula:
Create another name, called "RangeY" and use this formula

Now, select A1:B20, click the chart wizard, and in Step 2, click on "Series". There, in "Values" put
(Replace Sheet1 with the name of your sheet)
and in Axis put

Now, finish customizing your chart, and it will update "automatically" when you put new data...

Juan Pablo G.

Posted by Mark W. on December 17, 2001 12:58 PM

Enter #N/A into cells B11:B30 (nt)