7

Consider the following Excel spreadsheet:

Screenshot I

B2 should now say "Hello Brutus!", so I try to type 

="Hello " & B1 & "!"

And, of course, as always, I do not feel the necessity to release the Shift key while I type " & B and & "!". So, Excel does this:

Screenshot II

Screenshot III

How can I deactivate this Shift+Space hotkey (that is of no use for me anyway, I cannot imagine how this can be of any use for anyone)?

Improve this question
8
  • Shift-Space acts in reverse to Space, and usually lets you scroll up (instead of down) on any given webpage, unless you're in a text area. There, Shift-Space shouldn't do anything at all, just type a regular space. Do you mean CapsLock-Space?
    – user1019780
    Commented Mar 23, 2020 at 16:48
  • @Didier Try it.
    – Alex M
    Commented Mar 23, 2020 at 20:38
  • @Didier In Excel Ctrl+Space and Shift+Space are used to select the whole column/row
    – phuclv
    Commented Mar 24, 2020 at 8:08
  • Got it. I knew there must be a trick somewhere...
    – user1019780
    Commented Mar 24, 2020 at 9:16
  • 1
    @Bowi no there's no references here. Array formula is a special kind of formula that operates on the whole array or each element of the array. Take a simple example that in D1 you put an array formula of B:B + 2*C:C then D1 = B1 + 2*C1, D2 = B2 + 2*C2 and similar for the remaining cells of column D. The same will happen for rows. It's also used to populate a formula to the whole table
    – phuclv
    Commented Mar 25, 2020 at 16:26

3 Answers 3

Reset to default
4

Unfortunately Excel doesn't support customized keyboard shortcuts:

One workaround is to use AutoHotkey with the below script

; If you want to disable Shift+Space for all applications
; just comment out the next line
#IfWinActive ahk_exe EXCEL.EXE
+Space::Return
Improve this answer
1
  • Wow, haven't thought of that. It's a ... violent approach but I like it =)
    – Bowi
    Commented Mar 25, 2020 at 8:23
2

You can't. But the good news is that you don't need the spaces that are causing you trouble

Type as

="Hello "&B1&"!"
Improve this answer
2
  • 1
    that's less readable than with the spaces around binary operators
    – phuclv
    Commented Mar 24, 2020 at 8:07
  • 2
    Excel doesn't need them, but I need them to feel <s>good</s> less bad when looking at the formula...
    – Bowi
    Commented Mar 25, 2020 at 8:20
1

There is no option for it anywhere. To overcome it in a literal way, you'd need a one line macro that maps the key to being a space. It would look something like:

Application.OnKey "+{SPACE}", "{SPACE}"

but I do not write macros so it could need tweaking. Copied and modified from:

https://docs.microsoft.com/en-us/office/vba/api/excel.application.onkey

POINT TO REMEMBER: once run, it doesn't just end so you'd need a second macro to end it by remapping the Shift-Space ("+{SPACE}") back to being Shift-Space after you are done.

A non-literal way ("living with it with less suckage" or "workaround") would be to hit the CapsLock key before creating such formulas. Then the space will just be a space. Only an issue in case of needing to type non-cap text, then its "which sucks more?" Or in having to remember to do it each time.

(Each approach has something you have to do then undo when done, never forgetting.)

Improve this answer
3
  • 1
    It has nothing to do with caps lock. One holds <kbd>Shift</kbd> while one types " and & characters. I also frequently run into this issue because I forget not to hold it down for the space between " and & because that is only a requirement in Excel.
    – Alex M
    Commented Mar 23, 2020 at 20:37
  • What if you select an All-Cap font? That exists in Word, so I suppose it must exist in Excel too. That way, you wouldn't have to type, say, a capital B, just the letter B, and it would appear as capital B in your formula. BTW, are you sure that you MUST write capital letters? I haven't used Excel in a while, but I seem to remember that formulas (formulae?) don't differentiate upper and lower case when it comes to letters. I may be mistaken, though, plus it's almost midnight here, and it's been a long day... :-) (Off to bed)
    – user1019780
    Commented Mar 23, 2020 at 22:48
  • That VBA call gives me a Runtime Error 1004. :-( @Didier I don't think this would work as I would have to change my "If I want to type capital letters, I press SHIFT" wirings, which I do not want to.
    – Bowi
    Commented Mar 25, 2020 at 8:35

You must log in to answer this question.

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