Excel 2007 : Problem with Excel guessing which cells I want in a form

[novicee]

23 Feb 2012

Excel 2007 : Problem with Excel guessing which cells I want in a formula

When I enter a formula Excel guesses which cells I want in the formula. For example, if I want cell J10 to contain the sum of cells K10 and L10 (i.e. J10 = K10+L10) and I enter "=" in J10 then click K10 to have it inserted in the formula, instead Excel inserts the range "K10:M10".

Is there a way to turn off this unwanted "help" from Excel?

Of course I done online searches for a solution but they turned up nothing possibly because I don't know the magic words that describe the problem.

Thanks in advance for any help in answering my question.

- Novice

[daremo]

I am not sure how to turn off the help from Excel, but your problem regarding automatic selection of a range of cells reminded me what might be at play. However this is based on Excel 2003, so may not be applicable in your case.

When you are starting a formula but especially when you use the SUM function (via the toolbar icon), Excel seems to select a range of cells in a column that are "contiguous". If, for example, in a column you have a range a1 to a5 [A1:A5] and another range from a7 to a10 [A7:A10], and you are inputting a formula or SUM at A11, then Excel hi-lights the range [A7-A10] instead of [A1:A10] as the range [A7-A10] is the first contiguous range next to A11 where the formula is being started.

However, if you input <space> in cell A6, so it looks empty at a first glance (because <space> is not an obvious, visible character), and perform the same test, you will see that the range [A1:A10] are hilighted, since the rane [A1:A10] is a contiguous range!

I suspect that in your case the cell M10 has some value (even if it is a space character or something similar and left over from some other operation). Thus, first check if that assumpation is correct, i.e. if cell M10 contains anything, and perhaps try a Delete and/or Clean Contents operation on M10, then try out writing your formula and see if M10 is automatically selected.

I suspect that the issue you have is related to Formula Autocomplete function. When formula Autocomplete is enabled, as soon as you input "=" and type a letter a list of functions are displayed in a drop-down list for you to quickly select a function. However, in your case, you type "=" and then with the mouse you click on a cell, and since you have not continued your input into the cell with a character where Excel can help you out with formula autocomplete by displaying functions in a drop-down list (starting with the character you have input), but instead, you have selected a cell with your mouse, then Excel is probably trying to perform the formula autocomplete ability by hi-lighting a range selection... Perhaps you can Disable formula autocomplete.

You might want to test the above possibility by going to the Menu system, and then Options, choose 'Formulas', and selecting 'Working with formulas' group, and looking at the choices there and reconfiguring them!

In the meantime, if I find something specific regarding Excel's (idiotic) "helpful" hand :-) I'll post it here.

Edited February 28, 2012 by daremo

[Ponch]

For example, if I want cell J10 to contain the sum of cells K10 and L10 (i.e. J10 = K10+L10) and I enter "=" in J10 then click K10 to have it inserted in the formula, instead Excel inserts the range "K10:M10".

This does not happen here on my Excel2007 (even with K10 to M10 filled with numbers), neither with or without the option "Formula AutoComplete" .

[daremo]

Problem is I don't have office 2007 to do tests, as I use Office 2003, so it's difficult to find out what is going on, forcing me to guess.

Now, with the comment from Ponch, which is opposite of what Novie is saying, I'm wondering if Novice has a named/defined the range K10:M10 somewhere on the worksheet? I suspect Ponch tested this out on a clean worksheet, and that forces me suspect that Novice has a named range or something similar defined for K10:M10 on his worksheet. Perhaps selecting the first cell of a "named range" is forcing the formula to h-light/select the full named range???

[novicee]

Quote from Newbie of 29 Feb

"I suspect that in your case the cell M10 has some value (even if it is a space character or something similar and left over from some other operation). Thus, first check if that assumpation is correct, i.e. if cell M10 contains anything, and perhaps try a Delete and/or Clean Contents operation on M10, then try out writing your formula and see if M10 is automatically selected."

Your assumption is correct. The problem disappeared when I cleaned the cells involved by using the "Clear All" option.

Thanks for your help.