Can you "merge & center" cells and still be able to select the individual cells?

[CivFan]

I have several sets of merged cells in Column A, about 20 each set, and it makes using the keyboard cumbersome while scrolling through my document and inserting rows somewhere in the middle of the merged cell.

For example, I can’t use my normal workflow of just CTRL-Left to get to Column A, then CTRL-SHIFT-Right to highlight a specific row, and then CTRL-SHIFT-+ to insert a row, since it assumes I want to highlight all the rows from that merged set rather than one in the middle.

Of course, using the mouse to select the row works, but breaks the keyboard workflow.

One workaround is to just insert an empty column A, and that works fine honestly, but just wondered if I’m missing this option.

[LeroyG]

Seem that it is no possible with the “normal” workflow.

But you can select a row in the Name Box (Ctrl+Shift+T), by typing its number twice separated with a colon (say 4:4), pressing Enter, then Ctrl++.

If the cursor is in a non merged cell, you can press Shift+Space and Ctrl++ to insert a row.

If do you need to add cells to row 4 only on a few columns (say A:C), go to the Name Box, type A4:C4, then Enter, and then Ctrl++, Enter.

Edit your question if you want to add more information; also can comment an answer (Add Answer is reserved for solutions).

Check the mark () to the left of the answer that solves your question.

[CivFan]

I wasn’t aware of either keyboard shortcut, and they’re both quite useful for this case, Shift+Space in particular IMHO. This is also a more complete and yet more succinct answer, so marking it as the solution, though @Lupp 's answer is also useful.

[Lupp]

No.

Why do you think to need merged cell areas?. Merging always comes with serious disadvantages of which a “broken keyboard worklflow” may even be a minor one. Many experienced users tend to call merging evil.
Therefore my first suggestion would be to redesign the sheet without merged ranges.

If you cannot abandon merging, the appropriate alternative I can offer is to use a keyboard shortcut for the UI function: group=Edit, function=Select row. The default is Shift+SpaceBar. If this doesn’t work for you, use >Tools>Customize.

[“Merging is evil.” again. An inconsistency of the Controller/SelectionProvider(?) wiil even allow this way to put the focus on a cell hidden by merging if it is the leftmost or the rightmost{my error} cell of the row.If you then enter somthing inadvertently -due to your usual workflow without looking at the screen- this may spoil formula results without giving you a reasonable chance to find the cause. Did I already tell you that merging is evil?
No I won’t report this as a bug. Need my time to help user thinking merged ranges are a good idea.]

[LeroyG]

I also think that it’s not a bug.

[Lupp]

I definitely think it is a bug - and in fact I reported it despite my above remark: tdf#135030

[CivFan]

I can’t really argue with “merging is evil”. Nevertheless, it’s a commonly used option in spreadsheets.

[LeroyG]

Seem like two different things: the bug @Lupp reported, and to select a cell within a merged cell.

[mikekaganski]

This is definitely not a bug. Test this:

1. In a new spreadsheet document, put a, b, and c into resp. A1, A2, and A3.
2. Select A1:A3 and use Format|Merge Cells|Merge and Center Cells.

The dialog:

shows that it’s normal that cells hidden by merging may contain own data.

Note that I don’t argue that having that is a good thing. Just that it’s “by design”, being inherited behaviour, and may have its uses, thus can’t be “fixed”.

Note that explicit warning in the help topic:

Merging cells can lead to calculation errors in formulas in the table.

[mikekaganski]

Note also this fundamental difference between spreadsheets and text document tables. Spreadsheets consist of regular rows and columns; every column and every row have fixed number of cells, so spreadsheets are always rectangular. Tables in text documents can contain different number of cells in each row and/or column; in general, they may be not rectangular (both in shape, and in composition).

Even if we would deny creation of content in hidden cells, even through API, then we still need to handle that content somehow when it comes from file data… and it surely may appear there, in normal cell data definition of a cell that happens to be covered by a merge defined elsewhere.

[Lupp]

@mikekaganski: Thanks for the thorough comment, and apologies that I didn’t make clear my position sufficiently. My bad!
I knew that all. My rating of the current behavior as a bug didn’t concern the persistent exitence (including contents and formats and all) of cells being hidden by the increased area of another one due to merging, but the incinsistency in handling it (“cover” short for the cell with the area increased by merging).

1. You cannot select a cell hidden by merging generally, or put the keyboard focus on it.
   Ways you can try it - and the result:
   1.a Click on the guessed part-area of the merged range. - The cover gets the focus
   1.b Enter the address of the hidden cell into the NameBox. - The entered address is changed to the cover.
   1.c. Move the cell cursor starting with an adjacent cell - The cover gets the focus.

[Lupp]

(continued:)
1.d. Define the target cell as a named range, enter the name into the NameBox -
1.d.1. Headers and the DragSquare indicate the hidden cell as selected OR having keyboard focus
1.d.2. - FormulaBar and actual focus work with the cover cell.
Now come statements only valid if the merged range is part of column A.
1.e Select the complete row. - No focus indicator shown, but the hidden cell has the focus.
1.f Click a cell right of the targe, then hit Shift+SpaceBar followed by Ctrl+CursorLeft - The hidden target has the focus.
((In addition concerning inconsistencies: The respective proceeding with a merged range in column AMJ and Ctrl+CursorRight will not have the respective result.)

[mikekaganski]

@Lupp: so what would be the proposal? The only inconsistency that I see is that the part of the merge cannot be selected by explicit range like A7:A7 in Name Box, while A7:B7 works (assuming A3:C10 merged). I suppose that it’s OK that simple A7 doesn’t work (but I wouldn’t mind if it worked to select A7, not A3 - that’s just a matter of UX decision)…

The specific proposal is an essential part of a bug report

[Lupp]

My bad again?
Well, though I’m retired my time is limited - and I even get slower a bit every day never having been very fast anyway? Limitations? Handicaps? In this specific case the implications and different aspects are many, and describing them requires (in my words only?) so many lines that even a very engaged professional may only keep one inconsistency in mind.
If I find the time and succeed with more precise wording and a “specific proposal” I may go back to the mentioned bug report and complete it. Otherwise? Immortality may be an option for bugs.

[CivFan]

Great discussion. It’s almost like this was a good question. Interesting though that there are no upvotes on it. That seems to be a problem with ask.LibreOffice, it’s hard to tell what questions are good questions or just junk, since very rarely anyone upvotes questions.