Excluding empty/zero values in graph - highlight area in chart

[Scott]

I am doing a simple excel chart to monitor weight loss and BMI.

Every day has a new row with space for foof/exercise data etc.
Each date also has a column for weight - from this BMI is calculated.

However, most users only check their weight once a week, and therefore the
BMI calculation is empty (I set excel toe xclude zeros which is what it
originally showed for the calculation when weight was empty)

When I attempted to creat a chart (date and BMI selected), of course, all
the days appeared including the one's with no BMI data. If I change the
"zero" in the BMI row to "NA", a zero isn't plotted, but the date still
shows up.

What I'd like is for only the days with BMI data (ie weight entered) to plot.
Can we do that?

Finally, on the finished graph, I'd like to highlight certain areas, ie
20-25 (BMI) in blue, and above 25 red background etc. Can we do that?

Thank you.

 

[Jon Peltier]

Set up an autofilter in the sheet, which you can use to hide rows that have
a blank or other value or label in a particular column. By default, a chart
will not include data from hidden cells.

For the colored background, you might try one of these:

http://peltiertech.com/Excel/Charts/ChartIndex.html#Fill

- Jon

 

[Scott]

How do you set up an autofilter. Essentially this sheet has sequential days
with exercise, diet, notes - weight is entered every few days or even only
weekly. From weight BMI is calculated and plotted. Therefore, I only want
the date with weights plotted on the chart. I have it set so that no zeros
appears, but when I look at the graph all the dates are there - every single
one..... Thanks.