1

Here is a chart I get when I try to make a line or scatter plot.

Bad chart with lines only for the day

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:

Better chart with details, but incorrect scale.

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. Good scatter chart with proper scale and distribution

0

Excel is trying to create a chart which gives meaningful insight to the date selected. Excel changes the appearance of the chart based on the appearance of the data.

Just like Excel doesn't show you the raw timestamp, instead showing you the data in the format you selected, it will show the chart based on the format you selected.

To prevent this in the future, decide in advance the format you want to use to present your data. Stick to this format and your charts will never change.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.