Excel not respecting correct values for the Y-axis?

[Mr Davo]

Hi Everyone,

I am beginning to understand why the World needs an Excel forum like this one...arrgghhh! Such a frustrating program!

I am trying to insert a very simply chart into my spreadsheet, here is a screen capture of my data -

2jfgge8.jpg

Towards this end I 'simply' click on Insert (tab) > Chart > Insert Line Chart...

Instead of getting a chart with all of my data I get a stupid result as shown below -

14akhon.jpg

For reasons which I will 'die wondering' Excel has decided that the Y-axis should be the values 0 to 2, rather than the actual number of Tickets!

WTF is going on here? Any help will be greatly appreciated.

Kind Regards,

Davo

[6StringJazzer]

An image attachment has very little value. Just attach the Excel file. It's easier than taking a screenshot first and then attaching that.

When you create a chart just by selecting the values, Excel will assume that the first column is the x values and second column is y. Your second column doesn't have any numbers in it so there is no data to plot. Excel is a powerful tool but has not yet incorporated the "read my mind" feature

I can think of a couple of ways to resolve this but it will be so much easier to explain if I can use your file. One way is to click on the edge of that purple rectangle and drag it over to the first column. Then you may need to additionally identify the second column as category labels, but it's hard to tell from the picture just how the chart is set up.

[Mr Davo]

Hi 6StringJazzer,

Thanks for your interest in my post. I accept that Excel 'can't read minds' but I sure wish that it would!

I have attached my spreadsheet, if you would be willing to take a look it will be greatly appreciated.

Kind Regards,

Davo

[MrShorty]

You don't say what kind of chart you are trying to create.

possible problem 1: your dates are not real dates, but text strings that look like dates. Text strings are treated as 0 when put on a value axis. If you need Excel to treat these as dates, you will need to convert them to Excel's built in date/time serial number system. http://www.cpearson.com/Excel/datetime.htm#SerialDates

possible problem 2: I usually expect to see dates on the horizontal/x axis and values on the vertical/y axis. Your data has the values on the left with dates on the right. As noted by Jeff, when Excel tries to guess at your desired x and y axis values, it assumes that the x axis values/categories are in the leftmost column. If you want Excel to recognize the dates for the x axis, you will need to put them to the left of the other stuff.

possible problem 3: and this one should not be a big problem, but it seems that Excel likes to see the upper left cell of the chart range blank in order to correctly guess the x axis and y axis data ranges.

I have found that Excel frequently fails to create my charts correctly on the first try. When Excel gets it wrong, I frequently find myself going straight into the "Select data" dialog to get the correct ranges for chart data series. I would suggest that you become familiar with this dialog and use it.

[6StringJazzer]

Here you go. I would use a column chart because these are discrete values, but you seem to be after a line chart. I would also organize your data to put the independent variable in the first column and dependent variable second, but it isn't strictly necessary to get the chart so I didn't do it.

...I frequently find myself going straight into the "Select data" dialog to get the correct ranges for chart data series. I would suggest that you become familiar with this dialog and use it.

That's what I did.

[Mr Davo]

Hi Guys,

Thank you very much for your efforts and explanations. I managed to resolve this by swapping the two columns around, such that the dates were on the left. However I also noted in '6StringJazzers' response attachment that he managed to do the same thing by leaving the columns in their original positions; which gives me cause for a little more research.

Kind Regards,

Davo

[MrShorty]

However I also noted in '6StringJazzers' response attachment that he managed to do the same thing by leaving the columns in their original positions; which gives me cause for a little more research.

After the chart is created with whatever Excel guesses you wanted, you can go into the Chart Design -> Source Data and specify whatever you want for the horizontal category axis data and the y axis data for each data series. Arranging the data with the x data on the left makes it easier for Excel to get this right at chart creation, but you can change that after the chart is created.