Here is a chart I get when I try to make a line or scatter plot.
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:
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.
Update 3:
Looks like I found an answer.
After I found the axis options I started playing with chart type and changed it to Scattered chart and then I got exact chart I was looking for. The strange thing is that I did start with Scatter and the very beginning, but at least it is working now.
