8

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.

2

4 Answers 4

6

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

6
  • 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, 2015 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 Jan 20, 2015 at 15:00
  • 2
    I've tried that on both excel 2007 and 2010 and it doesn't work
    – phuclv
    Jan 22, 2015 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, 2015 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, 2015 at 3:37
3

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.

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.

2
  • 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. Apr 18, 2015 at 7:10
  • ok I misread your solution
    – phuclv
    Apr 18, 2015 at 7:11

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .