During the formula edition process, the editing line covers cells in the vicinities, which sometimes bring difficulties to the edition process. This is especially true if you have long formulas in your spreadsheet. For this reason I prefer to edit formulas only in the formula bar.
And what prevents you from following your preferred mode of inserting formulas? Anyway - afaik you can’t achieve that. This would require that cells know in advance, what you are going to add. Will it be a number → allow direct input; will it be text → allow and will it be a formula → don’t allow (and what about adding =this is not a formula?).
My question is related to editing formulas which is a different process compared to inserting formulas. Let me try explain the problem with an example. Suppose you have a long formula such as : =INDEX($changeMatrix.$D$13:$E$751;SMALL(IF($changeMatrix.$D$13:$D$751=$draft.G$18;ROW($changeMatrix.$D$13:$D$751));ROW(15:15))-12;2). Suppose this formula is in cell A1 and you want to edit it. You press F2 in order to edit the formula, then an editing area is created for you to make changes in the formula. In my case this editing area was a rectangle covering from A1 to B10. But, for editing the above formula I needed to refer to one or two cells which are now underneath editing area and I cannot see the cells, let’s say A5 and B5, because editing area is above them. A possible solution for this problem could be disable in-cell editing which will prevent the creation of such editing area using cell range. This resource is available in Excel by the way.
Calc : How to edit formulae in formula bar directly seems relevant. To summarize it, you can define a keyboard shortcut for the Calc Application function “Input Line”, or redefine the [F2] key from “Cell Edit Mode” to this.
Two workarounds (not quite practical):
- Set the cell font size to 2 pt. This will reduce the editing area size.
- Set cell alignment to right. The editing area will spill first to left then down.
Related question: Calc: How to hide the popup of cell content
Maybe this is worth of an enhancement request.
I wouldn’t try to disable inplace editing. If possible at all, it will require some ticklish programming.
If a cell has enabled Wrap text automatically under Format Cells>Alignment the editing process shouldn’t exceed the cell’s area when beginning. (Additionally entered characters may later expand the used area below the original cell borderts.)
If you enable the mentioned property in advance of editing, and disable it at the end, most of the cases you see a problem with will vanish.
If you need this very often, you can assign shortcuts to respective recorded macros.
Here is a macro that you can assign to, say, F2 to allow edit line editing, as suggested by others above. It does also automatically expand to allow one-step editing of CSE (array) formulas, but that can be left out if not desired.
sub UIEditArray
dim document as object
dim dispatcher as object
document = ThisComponent.CurrentController.Frame
dispatcher = createUnoService(“com.sun.star.frame.DispatchHelper”)
dispatcher.executeDispatch(document, “.uno:SelectArrayFormula”, “”, 0, Array())
dispatcher.executeDispatch(document, “.uno:FocusInputLine”, “”, 0, Array())
end sub
However, this will not stop the in-cell edit from appearing and “spilling” out over the surrounding cells. It is useful based on the OQ because often times it suffices to get the edit up to the edit line, and a keystroke may be quicker than a mouse move.