How to prevent Excel from inserting formulas when typing + or - at the beginning of a cell?

Question

Sometimes I have to mark some cells in a spreadsheet with - to indicate that it was not available or not related, not tested... Sometimes I also use + to denote positiveness or availability.

But after pressing +/- key in an empty cell and press arrow keys or click mouse button to proceed to the next cell like I usually do, it automatically selects the other cell in the formula. So I have to quit editing and type the text again. This is very annoying.

I never start a formula with + or - but always use =. So how can I turn off this behavior? Using macro is also fine.

Solution 1

I'd suggest typing an apostrophe ' before typing the plus or minus. This will escape that functionality and disappear once you move on.

Solution 2

An easier way to disable the automatic formula function is to reformat the sheet (or individual rows, columns, or cells, as suits your need) as Text.

Comments

[phuclv]

How to force excel to stop thinking your cell contains a formula

[phuclv]

Tons of similar questions but all the solutions are putting ' at the beginning which I already used for over a decade and is not what I wanted Stop Excel recognizing a dash, How to tell Excel not to evaluate a formula?

[phuclv]

I already know that for years, but this is not a solution, since it requires 2 keystrokes while + and - can already be at the beginning of the cell without escaping

[Raystafarian]

@LưuVĩnhPhúc you didn't specify what version of excel, but you could try pre-formatting the cells to "text" - though it doesn't work for me

[phuclv]

I've tried that on both excel 2007 and 2010 and it doesn't work

[phuclv]

@fixer1234 a macro is a permanent solution

[hBy2Py]

I know of no setting to disable this behavior with - and +, and no way to intercept keystrokes at the time of formula entry in the way you're seeking. Event macros such as Worksheet_Change only fire when you finish editing a formula, but the problem is that Excel enters formula entry mode instead of value entry mode on the 'application level' when it sees =, + or - as the first character entered. I believe there is no VBA 'hook' for macros to grab hold of to alter the behavior.

[Máté Juhász]

What do you mean by defined in the format? You don't type them but the macro runs immediately when you changed your cell content and you will have them.

[phuclv]

ok I misread your solution