Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I select "Scatter" get the empty chart, RMB inside the chart, and click "Select Data". Up pops "Select Data Source", with entry elements "Chart data range", "Legend Entries (Series)", and "Horizontal (Category) Axis Labels". The data I want to plot is B3:B50 and M3:M50.

What do I do?

(Note that Excel 2007 is apparently ENTIRELY different from Excel 2003 in this area.)

share|improve this question
    
bit.ly/QDqf7B –  Excellll Sep 10 '12 at 17:00
    
Have you tried selecting the data you want to chart, then click "Scatter Chart". –  CharlieRB Sep 10 '12 at 17:09
    
@CharlieRB -- That only works if the two columns are adjacent. –  Daniel R Hicks Sep 10 '12 at 17:17
1  
You're right == wrong @. (And my acceptance rate is so low because I ask very few questions, and most that I do ask turn out to not have answers.) –  Daniel R Hicks Sep 10 '12 at 19:17
1  
@DanH - You can select nonadjacent ranges by selecting one range, then holding Ctrl while selecting other ranges. –  Jon Peltier Sep 11 '12 at 11:07

2 Answers 2

@Dan H - You're making it hard for yourself. No incantations, incense, or chicken feet are needed. And you've never irretrievably screwed anything up, if you keep your wits about you.

There are more than one way to skin this cat. Here are the two easiest:

  1. Select the data you want to plot. If it is in multiple ranges, select one range, then hold Ctrl while selecting the other ranges. Go to Insert tab, select the chart type you want.

  2. Insert XY Scatter chart. Edit Data: select series, click Edit. Clear contents of Series Name box, either type name in box or click in box and select cell containing series name. Clear contents of Series X Values box, click in box and select range containing X values. Clear contents of Series Y Values box, click in box and select range containing Y values. Click OK twice.

Note: it is not "blank" cells that cause the X values to revert to 1,2,3,... It is formulas that return "", which is not blank, since it contains a formula that returns a (short) text value. Replace "" in your formulas with NA(), which returns #N/A in the cell. In XY and Line charts, Excel will skip points with #N/A for X or Y value, omitting the point altogether, connecting points on either side with a line (if lines are used in the series), without messing up the axis.

If you need the cells for further calculations, you need two ranges, one that has #N/A for charting, one with whatever won't mess up the calculations (perhaps "").

Someday Microsoft may finally give us the BLANK() function we've requested for so long.

share|improve this answer
up vote 0 down vote accepted

Well, I've sort of worked it out.

  • Select your X column and the do the insert scatter chart thing.
  • Do RMB "Select Data" inside the chart.
  • Click "Edit" in the in the "Legend Entries (Series)" area.
  • Select the little button to the right of "Series X Values (or whichever got filled in -- it tends to randomly fill in X or Y) .
  • Highlight and copy the displayed text (eg, =correlation_09_07_scoring_chang!$B$16:$B$50).
  • Click the little button to the right to go back to the Edit Series window.
  • Click the little button to the right of "Series Y Values".
  • Paste in the the text you previously copied.
  • Very carefully edit the text to change the column letters as required. DO NOT use the left/right arrow keys while editing or you will get hopelessly screwed up.
  • Press the little button to the right to go back to the Edit Series window again.
  • Edit the "Series Name" to something like ="MyData" (if you wish).
  • Press "OK" to go back to "Select Data Source". (A few prayers or secret incantations at this point are merited.)
  • Press "OK" again to finish editing the data source.
share|improve this answer
    
I'll note that this is incredibly buggy, and the chart will revert to plotting the row number on the X axis with even minor additional editing. –  Daniel R Hicks Sep 10 '12 at 18:08
    
Another messy detail: Any blank cells in the data will cause the plot to revert to plotting against the cell row #. –  Daniel R Hicks Sep 10 '12 at 19:18
    
Is the ‘left/right arrow keys’ issue still a problem if you hit F2 first? Have you tried entering =NA() in 'blank' cells, or hiding the relevant rows? - and you should be able to increase your acceptance rate by accepting the answer to this question! –  pnuts Sep 10 '12 at 20:42
    
@pnuts Re the arrow keys, I've never hit F2 -- I can try that, I suppose. Didn't try entering =NA() (hadn't heard of it). Handled the the empty cell problem by shadowing the column with =IF(ISBLANK(B3),-1,B3), placing the problem points off the chart. (Note that I need the blank cells to be blank to exclude them from an =CORREL() calculation.) And can't accept this answer for 24 hours, IIRC. –  Daniel R Hicks Sep 11 '12 at 1:21
1  
@pnuts -- "You can accept your own answer in two days". –  Daniel R Hicks Sep 11 '12 at 1:34

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.