Forum Discussion
line chart data vs formulas
I assume that the formula returns "" if there are no data yet.
You could change that to NA()
Excel by default suppresses #NA values in a chart.
Another option would be to define dynamic named ranges for the x-values and each of the series, to include only the dates for which there are data.
Thank you for your reply.
The NA() was new for me. It works fine of course but it fills the sheet with rows of NA´s which looks (imho) rather ugly.
I will try the ranges. I think it will create a other issue (at least for me): I like charts to fill and not to grow.
Presenting each week the chart one sees the changes in data compared to the week before because the layout stays the same (most importantly the X-axis stays the same) E.g. X range is a whole month, every week a new week of data is added.
An alternative would be to create a conditional formatting rule for the source range of the chart.
- Select the source range.
- On the Home tab of the ribbon, click Conditional Formatting > New Rule...
- Select 'Format only cells that contain'.
- Select Errors from the 'Format only cells with' drop-down.
- Click Format...
- Activate the Font tab.
- Select the background color of the sheet as font color (presumably white).
- OK your way out.
- Error values will now show as blanks.
- super solution!!
Thanks!