I have a range of 10 cells, and anywhere from 2 to all 10 of the cells will have values. The cells that have values change daily, and I’d like to have a scatter plot that updates and resizes dynamically based on the cells with values. So far, I’ve done this: Two named ranges: Name: Rates =OFFSET(‘Projection’!$H$6,1,0,COUNTA(‘Projection’!$H$6:$H$14)-1,1) Name: Labels =OFFSET(‘Projection’!Rates,0,-7) and the chart series is defined as: =SERIES(“Current rates”,‘Projection’!Labels,‘Projection’!Rates,1) However, even if the cells don’t have values, they are still shown on the chart as zeros. Any ideas how to not show these values on the plot?
An if statement that sets the area to 0 if the cells in question do not have a value.
Right click on chart Select Data Source, lower left hand corner is Hidden and Empty Cells. Select “Show empty cells as: Gaps”.
Slash Wrote: ------------------------------------------------------- > An if statement that sets the area to 0 if the > cells in question do not have a value. instead of 0, try typing the below: NA() It’s an easy way of telling Excel charts not to plot anything at all. 0s would still probably show up as a piece of data. By forcing #n/a by typing NA(), that should do it…