1

I am trying to use the shortcut ctrl + shift + arrow, such as the down arrow, to select an entire column of data.

The issue is that some of the rows in that column have been merged or split. For example, rows 4 and 5 have been merged together for the first few columns, but then re-split for the last few.

If I am using ctrl + shift + down in a column where the rows have been split again, the selection does not extend all the way to the next empty row. It stops at the next merged/split row.

How can I use this shortcut to extend all the way to the next empty row?

2

To understand the behaviour you are seeing, you need to understand what merged cells are really doing, which is to delete everything except the top-left cell of the merged area and then do a presentational trick.

The gaps caused by the deletion of the cells explain all the behaviour you see. For example, the ctrl-shift-arrow shortcut will stop when you hit one of these deleted cells, because it is an empty cell.

So the short answer is:

The short cut is working as it is supposed to. So there is no easy work around

But worse, it is easy to be misled as to what your spreadsheet is calculating and it is important to understand precisely what Merge Cells is really doing - and I don't like it! So I say:

It is better to not use merged cells on anything you are doing calculations on (preferably not even column/row headings on tables either.)

If you must, make sure you know what you are doing. If you must use them, it might be a good idea to

(1) do the cell merges you want, then (2) UN-merge them all while you work on the sheet and then (3) only re-merge them once you are finished and want to make pretty outputs!

Let me explain (I have made this long explanation because cell merging causes far bigger problems that keyboard shortcuts acting oddly and far too few people understand this):

WHAT DOES MERGEING A CELL MEAN

The message box you get when you hit merge tells most of the story: when you merge cells Excel deletes the contents of all the cells except the top-left cell in the range. Behind the scenes, for calculations and key-presses it is behaving entirely normally - it is just that you aren't seeing what it sees.

(The one truly changed behaviour is they way it expand selections so that you can't select part of a merged cell).

EXAMPLE - A 4x4 TABLE

Let us image we have a simple table (4 x 4, arbitrary data) with sums and averages being calculated by rows, by columns and in one go.

Simple 4 x 4 table, not merged

Everything matches up as you would expect.

Now let's say we merge the four central cells:

Centre four cells merged - results change in confusing ways

The figures change - but in ways that are inconsistent and non-intuitive and important to understand. For example you can see that the different rows and columns have different sums and averages despite appearing to be the same. And it now matters whether I take an average over the whole table or by rows first or by columns first, which is not necessarily what you would be expecting. What is going on?

WHAT IS GOING ON?

What is really happening is that Excel (as that dialog box that popped up said) has deleted all except the top left cell, so now there are three empty cells on your sheet:

Comprehensible when you see behind the scenes

When you understand that this is how Excel sees your spreadsheet you can see why Excel behaves as it does. For example, Ctrl-Shift-Arrow stops there because there is an empty cell there.

But as I said above, the bigger issue is that it is easy to loose track of what the sheet is as actually calculating. It is even possible to format a merge so that it doesn't look like a merge but shows data in the wrong cell!

Lets say that you had without thinking about it, re-formatted your table (which has merged cells somewhere on it) to align its cells bottom-right (maybe you re-aligned some other bits of it by mistake and were correcting that). It might end up lookign like this:

Cell value still top-left, but looks like it is bottom-right

Hopefully you can see now what Merge Cells is doing and why it behaves in the way you have found (and see why I call it dangerous, and don't use it if I can possibly avoid it!)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.