Here is a chart I get when I try to make a line or scatter plot.
![enter image description here](https://cdn.statically.io/img/i.sstatic.net/LMW2l.png)
Notice how all the data points have been compressed such that I only see a vertical line for each day.
This what my data looks like:
time value
3/4/19 18:31:05 -77.41666667
3/4/19 18:31:06 -77.13888889
3/4/19 18:31:27 -77.41666667
3/4/19 18:31:28 -77.13888889
3/4/19 18:38:37 -77.41666667
3/4/19 19:07:21 -77.66666667
3/4/19 19:07:26 -77.66666667
3/4/19 19:07:52 -77.66666667
3/4/19 19:09:27 -77.66666667
3/4/19 19:10:08 -77.41666667
3/4/19 19:10:17 -77.66666667
3/4/19 19:22:52 -77.66666667
3/4/19 19:24:09 -77.41666667
3/4/19 19:32:03 -77.66666667
3/4/19 19:32:04 -77.66666667
3/4/19 19:32:22 -77.66666667
3/4/19 19:32:27 -77.66666667
With some playing around I found that just changing the format of the time column to just show the time without the date gave me the more desirable chart below:
![enter image description here](https://cdn.statically.io/img/i.sstatic.net/IEEjo.png)
The data is exactly the same, but the chart changed from the cell formatting alone. I have never before seen Excel arbitrarily adjust behavior based on formatting vs actual data.
Can anybody tell me what is happening and how can I prevent it in the future?
Update:
I found other issues with the cart.
First, notice how there are vertical lines in the second chart. Those represent missing points in the data. The points above and below the line are at least an hour apart so the line between them should be diagonal not vertical. It seems somehow the scale is dynamic, not static like I would like. How can I make the scale static and why is this change happening now. New version or some update, 2016?
Second, normally under Axis options there is Minimum, Maximum, major and minor unit and so on. When I changed the format to time only I seem to have lost these options and now I only have Interval between tick marks and interval between labels.
Update 2:
I searched for Axis scaling which I thought was the issue. I found an article here: https://www.brainbell.com/tutorials/Excel-2007/date-or-time-scaling-in-charts.html
It describes the axis type setting, which I have and when set to text displays the good chart even with full date formatting. However, I still cannot get a correct scale. In the article the first chart has a consistent scale in the case of missing data. My equivalent chart would have 2 vertical lines for the 2 years or missing data removed in chart 2. I cannot seem to get the chart 1 equivalent.