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.)

  • 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
  • @CharlieRB -- I have "Googled excel 2007 scatter plot" several times. – Daniel R Hicks Sep 10 '12 at 18:13
  • 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

@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.


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.
  • 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
  • @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
  • @pnuts -- Yeah, F2 works to fix the arrow key problem. The trick is to remember to do it, since once you've pressed an arrow key without F2 the data is irretrievably screwed up for some reason, even if you edit back. – Daniel R Hicks Sep 11 '12 at 1:24
  • =NA() mucks up the correlation calc -- it goes from a valid number to "#NA". – Daniel R Hicks Sep 11 '12 at 1:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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