Line Graph dropping to zero

[jayemoh77]

Hello,
I have a line graph with a date range of June 1, 2017 - December 31, 2018. There are no formulas. Each day a number is entered into a cell. From tomorrow through 12/31/2018 the cells are obviously empty. However, the line graph drops to zero and goes all the way to 12/31/2018. How can I get it to stop dropping to zero?

Thanks, Julie

 

[thisoldman]

From the ribbon Design tab or from the chart right-click context menu, choose "Select Data". in the popup window, lower left corner, click on the button "Hodden and Empty Cells". Next popup, select "Gaps" and then "OK: your way back to the worksheet.

 

[jeffreybrown]

If the above approach is what not what you need, maybe you are looking for a dynamic chart that will grow as your data grows.

See https://www.techrepublic.com/blog/microsoft-office/two-ways-to-build-dynamic-charts-in-excel/

Or https://peltiertech.com/Excel/Charts/DynamicColumnChart1.html

 

[jayemoh77]

none of those solutions worked. I still have a line graph at 0 for dates that have yet to occur. thanks for your suggestions.

 

[jeffreybrown]

As the samples show, if the date has yet to occur, then why graph it. It's zero.

 

[thisoldman]

Do you have a formula in your blank cells? What you are calling blank may not be what Excel considers to be blank.

With hidden and empty cell settings to show as gaps, the following formula will not work to create an unplotted point.
=IF(A9, 1000, "")

This should work to create an unplotted point.
=IF(A9, 1000, NA())

The empty string, "", is not considered to be blank: =ISBLANK(Cell_with_empty_string) returns FALSE.

But Excel will not plot #N/A.

 

[jayemoh77]

The cells are blank; no formula, nothing. The reason i have it charted (even though the day has not occurred) is because the users do not have access to edit the graph every day. They just populated the blank cell with a number.

 

[Jon Peltier]

If the cells are truly blank, and if the Hidden and Empty Cells dialog indicates that Excel should plot blank cells as a blank, then the line will not drop to zero. Are you sure the cells are blank? They don't contain a space character, or even an empty string (""), which might happen if data from another application was pasted there?

Select the "blank" cells, and press Delete. Does the line still lie along the axis?

 

[jayemoh77]

I figured out the problem. In my chart I was using Stacked Line with Markers. I changed it to Line with Markers and it worked. thanks every one!! much appreciation!

 

[Jon Peltier]

Oh, stacked line chart. I think a lot of times, people select it by accident, and then don't understand what they get. And now that you mention it, I remember seeing the behavior you asked about in a stacked line chart. maybe next time I'll remember early enough to answer the question.