6

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)?

Bowi
  • 1,407
  • 1
  • 14
  • 34
  • 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? –  Mar 23 '20 at 16:48
  • @Didier Try it. – Alex M Mar 23 '20 at 20:38
  • @Didier In Excel Ctrl+Space and Shift+Space are used to [select the whole column/row](https://support.office.com/en-us/article/select-cell-contents-in-excel-23f64223-2b6b-453a-8688-248355f10fa9) – phuclv Mar 24 '20 at 08:08
  • Got it. I knew there must be a trick somewhere... –  Mar 24 '20 at 09:16
  • @phuclv How could I need that while typing in a formula? I do not understand it! :( – Bowi Mar 25 '20 at 08:18
  • @Bowi it's used in array formulas, just like how you click or press arrow buttons and it'll enter the current selected cell(s) – phuclv Mar 25 '20 at 08:53
  • @phuclv Yes, but if I enter the entire row or column like this, isn't that always a circular reference and thus uncomputable? – Bowi Mar 25 '20 at 14:46
  • 1
    @Bowi no there's no references here. [Array formula](https://support.office.com/en-us/article/Guidelines-and-examples-of-array-formulas-7D94A64E-3FF3-4686-9372-ECFD5CAA57C7) 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](https://superuser.com/a/1373345/241386) – phuclv Mar 25 '20 at 16:26

3 Answers3

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
phuclv
  • 26,555
  • 15
  • 113
  • 235
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&"!"
phuclv
  • 26,555
  • 15
  • 113
  • 235
Ack
  • 623
  • 4
  • 12
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.)

phuclv
  • 26,555
  • 15
  • 113
  • 235
Jeorje
  • 11
  • 1
  • 1
    It has nothing to do with caps lock. One holds Shift 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 Mar 23 '20 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) –  Mar 23 '20 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 Mar 25 '20 at 08:35