Forum Discussion
line chart data vs formulas
Hi,
When creating a line graph from a range with (manually) entered data, including dates in the future where cells are still empty, the line stops in ´mid air´ at the first empty cell.
However, creating a similar line graph based on cells where data is created with formulas and the range includes dates in the future as well, the line drops from the last cell with a value towards the x-axis.
Obviously Excel evaluates the values of the formulas as zero hence the drop to zero.
Is it possible to stop Excel from doing this and ending the line in ´mid air´?
Thanks!
Michael
Excel 365/W10
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.
4 Replies
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.
