4

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.

4

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

  • 1
    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 – phuclv Jan 20 '15 at 14:58
  • @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 – Raystafarian Jan 20 '15 at 15:00
  • 1
    I've tried that on both excel 2007 and 2010 and it doesn't work – phuclv Jan 22 '15 at 17:18
  • @LưuVĩnhPhúc: It looks like you are choosing to use a scheme that Excel interprets in a different way by default. The easy solution is to use a method that is consistent with how Excel is designed to work. One extra keystroke is a small price to pay. How much more time/work is that than using a multi-key macro? – fixer1234 Apr 18 '15 at 3:07
  • 1
    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. – hBy2Py Apr 18 '15 at 3:37
0

You can use the AutoCorrect feature of Office to replace other arbitrary characters of your choice with + or -.

Note that AutoCorrect list is shared between all Office applications so this approach may have unintended consequences. Choose your characters to replace wisely.

0

As commented already, even with a macro it's not possible to prevent excel from starting a formula.
If you really want one keystroke and leave the cell then you need to change from + and - characters e.g. to "p" and "n" and you get get a macro which change them to your desired output when you finishing editing of cell content. Other workaround could be to type 0 and 1 and change them with conditional formatting.

  • 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. – Máté Juhász Apr 18 '15 at 7:10
  • ok I misread your solution – phuclv Apr 18 '15 at 7:11
0

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.

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.