Ask Your Question

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

asked 2020-07-21 23:34:37 +0100

CivFan gravatar image

updated 2020-07-22 01:19:43 +0100

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.

edit retag flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted

answered 2020-07-22 00:21:15 +0100

LeroyG gravatar image

updated 2020-07-22 01:01:10 +0100

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 (Correct answer mark) to the left of the answer that solves your question.

edit flag offensive delete link more


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.

CivFan gravatar imageCivFan ( 2020-07-22 01:18:53 +0100 )edit

answered 2020-07-22 00:10:58 +0100

Lupp gravatar image

updated 2020-07-22 01:05:06 +0100


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.]

edit flag offensive delete link more


I also think that it's not a bug.

LeroyG gravatar imageLeroyG ( 2020-07-22 01:03:18 +0100 )edit

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

Lupp gravatar imageLupp ( 2020-07-22 01:07:56 +0100 )edit

@Lupp; add "e" to "somthing", and delete this comment.

LeroyG gravatar imageLeroyG ( 2020-07-22 01:08:25 +0100 )edit

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

CivFan gravatar imageCivFan ( 2020-07-22 01:15:22 +0100 )edit

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

LeroyG gravatar imageLeroyG ( 2020-07-22 01:16:36 +0100 )edit

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:

image description

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.

Mike Kaganski gravatar imageMike Kaganski ( 2020-07-22 10:19:28 +0100 )edit

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.

Mike Kaganski gravatar imageMike Kaganski ( 2020-07-22 10:54:54 +0100 )edit

@Mike Kaganski: 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 ...(more)

Lupp gravatar imageLupp ( 2020-07-22 12:00:07 +0100 )edit

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

Lupp gravatar imageLupp ( 2020-07-22 12:12:31 +0100 )edit

@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 ;)

Mike Kaganski gravatar imageMike Kaganski ( 2020-07-22 12:17:07 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2020-07-21 23:34:37 +0100

Seen: 87 times

Last updated: Jul 22 '20