0

I want x axis to be column A which is date & time. I want columns B, C, and D values each to be different color on y axis.

Here are my steps...

  1. open excel
  2. Data > From Text
  3. Select columns A-D
  4. Insert > Scatter chart ...only shows column D

HERE IS GRAPH THAT ONLY SHOWS COLUMN D...

enter image description here

HERE ARE SELECTED COLUMNS A-D........ enter image description here

Improve this question
5
  • Hi @DougNull - have you tried changing the source data (right click on the chart) - and adding / moving the data around that way?
    – Mr R
    Commented Apr 8, 2021 at 0:21
  • "Select Data Source". I will try
    – Doug Null
    Commented Apr 8, 2021 at 0:57
  • Excel "auto" graphing from selection is sometimes tripped up by specific formatting in the headers or lack of data (blanks) like in your example. What happens if you only select A and B then plot? That is sometimes easier... select only x column and one y series column, plot and then right click the chart "select source data" and manually add the other series. Another option is to select everything, plot, go to the same dialogue and select ~"switch rows/columns".
    – ExcelEverything
    Commented Apr 8, 2021 at 6:51
  • Also make sure the data is actually imported and formatted as numbers and not text. Text to data import is sometimes tripped up by region settings for the decimal seperator (. vs ,). To test make duplicate columns with formula =A2+0 in G2. Then copy and paste G2 across G2 downto K1000.
    – ExcelEverything
    Commented Apr 8, 2021 at 7:01
  • Go to Chart Design- Select Data and check the data as: i.sstatic.net/UROq9.png
    – Lee
    Commented Apr 8, 2021 at 9:10

1 Answer 1

Reset to default
0

If those "blank" cells aren't really blank (contain space or other non-printing characters), Excel will treat them as text and parse the data differently than you would expect.

Select the plotted series and look at the series formula to see how Excel parsed it. I'll bet it looks something like this:

=SERIES(Sheet1!$D$1:$D$220,Sheet1!$A$221:$C$500,Sheet1!$D$221:$D$500,1)

Where the first three columns are used for X values, which doesn't work in an XY chart (though formatted as date-time, the first X value in your chart is zero), and the top at least 218 cells in column D are used as the series name (the chart title is "SELL 433" which are the values in D1 and D218 the first number found in column D, and the red outline that highlights series names surrounds all of the visible part of column D in the first screenshot).

You would expect instead three series like this:

=SERIES(Sheet1!$B$1,Sheet1!$A$2:$A$500,Sheet1!$B$2:$B$500,1) =SERIES(Sheet1!$C$1,Sheet1!$A$2:$A$500,Sheet1!$C$2:$C$500,2) =SERIES(Sheet1!$D$1,Sheet1!$A$2:$A$500,Sheet1!$D$2:$D$500,3)

To fix this, press Ctrl+G (GoTo), Special Cells, Constants, Text, then press Delete. Now rebuild the chart.

Go To Special

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .