I have a table of data that is the source for a chart. I import different data into the table often to plot it in the chart. In the settings for vertical axis, I selected "Major Unit: Fixed (at 1.0)", so there is a common scale for display between sets of data. The display of these values looks very bad if all data points have the same Y value. For some reason, it doesn't leave some graph space above and below the line like in other plots where the Y value is not constant. This can be fixed by setting the Maximum or Minimum settings to Fixed, but I need it to stay Auto for my application. Also, I want the Major Unit to stay Fixed. Any other suggestions for fixing this? (Sorry, I can't post pictures yet without rep points.)

Excel 2010, Line Plot with Markers

X   Y   -Plot is fine   
104 306.0   
105 306.0   
106 305.0   
107 306.0   

X   Y   -Plot displays very poorly  
201 491.5   
202 491.5   
203 491.5   
204 491.5   

If you can't set a min/max then my suggestion would be to use a dummy series.

The points could be set using ceiling/floor functions on your data set

for the second one you would have a dummy series something like

x    y
201  491
204  492

Give the series the name "", and format the dummy series so there are no points and no lines. No one will see the data but it will make sure there is data with variable y values.

| improve this answer | |
  • Great! That fixes it. – Mike Aug 28 '15 at 19:38
  • I'd have suggested the same approach. You could use a formula to calculate dynamic dummy values, something like =FLOOR(MIN(y-values),1) and =CEILING(MAX(y-values),1). – Jon Peltier Sep 10 '15 at 13:00

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.