Excel 2010 graphing

**SPL Tech** · 03-11-2012, 05:28 AM

I am trying to chart out a .csv file that has two values, B and C. However when I try to chart things out in Excel, it references columns B and C against the horizontal line number. I dont need that, I need the graph to reference column B against column C. How do I do that?

Q2: Does Excel 2010 have the ability to graph the contents of .csv files in real time?

52030701.jpg

http://img19.imageshack.us/img19/6595/52030701.jpg

**dilipandey** · 03-11-2012, 07:36 AM

Hi SPL Tech,

Welcome to the forum.

Select one column(data) column to make the chart and then you can select another column under "Data Sources" to have that for axes.
csv file can be charted using VBA where VBA can refresh them on chart source data.

Suggest you to post a sample file (not image) so that forum members can give a quick try. Thanks.

Regards,
DILIPandey

<click on below 'star' if this helps>

**SPL Tech** · 03-12-2012, 01:26 AM

It does not seem to work. I selected one column and charted it out. Then I right clicked the chart and went to "select data". However Excel wont actually let me do anything useful in that window. I can add new data souses, but only to the Y axis. Its either everything is on the Y axis or everything is on the X axis. It does not give me an option to select individual souses for both the X and Y axis. The axis I do not select is always auto populated by Excel using the horizontal row numbers.

Here is a copy of the file: http://www.fileserve.com/file/brtnrrX/DSC 0.csv

**teylyn** · 03-12-2012, 02:36 AM

The file you linked to contains a space in the file name and the link cannot be opened. Please use the forum facilities to upload files, so we don't have to suffer advertisements and waiting times in order to see your file.

What kind of chart are you using? It sounds like you are using a line chart, but from your description you probably want an XY Scatter chart, which will plot the X axis according to the values.

Upload a file to the forum if you want more help.

cheers,

**SPL Tech** · 03-12-2012, 07:14 AM

Here you go: DSC0.zip

And the link works fine, you just have to copy and paste it into your address bar, the URL is suppose to have a space. I was just using a standard 2-D line chart. I tried a XY scatter chart, that doesent fix anything, the graph still looks the same.

Also I am having another issue. My datasets contain a lot of very low value numbers near 0 so they are expressed as a "y10^z" function. Instead of the Y axis saying "0, 20, 40, 60", ect. its saying "0E+0, .5E+1, 1E+2, 1.5E+2, 2E+2", ect. I dont want that, I want it to chart the values using whole numbers.

Basically this .csv file comes from the readings taken from a load cell. A load cell is a device that measures weight. While the load cell is idle the program I use to create the .csv file writes a bunch of super low value numbers near zero. Well Excel is using these numbers as a reference to generate the graph. Thats why its displaying the graph values in the y10^z format. Well these super low value numbers are useless to me, they can drop off the graph as far as I am concerned, I am only concerned with the whole value numbers, that is the readings above 1. So I need to figure out how to get Excel to use whole value numbers as a reference on the graph instead. You will see what I am talking about when you try to chart out my data set.

Thanks

**MrShorty** · 03-12-2012, 09:43 AM

I tried a XY scatter chart, that doesent fix anything, the graph still looks the same.

After converting the chart to XY scatter, you may have to go into the source data dialog to tell it to use column B as the X data and column C as the Y data. Excel may not be smart enough on its own to know that that is why you converted to XY scatter. If it still refuses to let you pick the data to use for the X-values, then something else is going on.

My datasets contain a lot of very low value numbers near 0 so they are expressed as a "y10^z" function. Instead of the Y axis saying "0, 20, 40, 60", ect. its saying "0E+0, .5E+1, 1E+2, 1.5E+2, 2E+2", ect. I dont want that, I want it to chart the values using whole numbers.

I suspect that, when Excel imports the CSV file, it sees those small numbers and chooses by default to format that column as scientific. Then, when you create the chart, Excel, again as a default setting, decides that, because the data in the spreadsheet was formatted as scientific, it will apply the same number format to the chart. When you don't want the default behavior, it is up to you, the user, to go in and tell Excel what you want. In the "format axis" dialog should be a number format option where you can tell Excel to use either a general number format or specify "0" or whatever number format you want to apply to the axis.

**SPL Tech** · 03-14-2012, 05:32 AM

Originally Posted by MrShorty

After converting the chart to XY scatter, you may have to go into the source data dialog to tell it to use column B as the X data and column C as the Y data. Excel may not be smart enough on its own to know that that is why you converted to XY scatter. If it still refuses to let you pick the data to use for the X-values, then something else is going on.

I suspect that, when Excel imports the CSV file, it sees those small numbers and chooses by default to format that column as scientific. Then, when you create the chart, Excel, again as a default setting, decides that, because the data in the spreadsheet was formatted as scientific, it will apply the same number format to the chart. When you don't want the default behavior, it is up to you, the user, to go in and tell Excel what you want. In the "format axis" dialog should be a number format option where you can tell Excel to use either a general number format or specify "0" or whatever number format you want to apply to the axis.

I fixed the y10^z thing, I just filtered out all the numbers below 1 using a number filter. But the other problem still exists. Here, this is the issue: 13629169.jpg

As you can see, it wont let me edit the other column, the edit button is disabled. I can click the "switch column" button, but that doesent fix anything, it just changes the axises and I still cannot change more than one axis.

**MrShorty** · 03-14-2012, 09:59 AM

I have to admit that I don't know my way around the 2007/2010 dialogs very well. What happens if you select a data series and click on the Add or Edit buttons? Does it give you a dialog that allows you to select the x data for that series?