|What can you say about this |
What I will say is with Excel, if you get the data right in the first place, you will save hours of aggravation. Charts were designed to accept a row or column of X values, and a row or column of Y values. Having alternating X and Y values in the same row is making it difficult. Putting the X and Y into distinct rows and columns adjacent to each other makes it work better, and often as you discovered, look better.
If you need the data table to look one way and the chart requires data the other way, use two (or more) different data ranges. Worksheets are cheap, hard drives are enormous and dirt cheap, your time is expensive and your frustration doubly so. Put the original data into a sheet somewhere, maybe even out of sight of the user. Set up a visual data range using Copy and Paste Special - Link, so it's arranged the way you like it, and linked to the original. Set up a chart source data range, again using Paste Special - Link, so it's arranged the way the chart likes it. Five extra minutes of work to set up the different data ranges, five hours less work to set up for printing or for making a chart.
You can eliminate routine tasks more easily by understanding what the program expects than wishing it expects something else. When you understand it, it even makes sense, and you will be more likely to set things up efficiently in the future.
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions