In most versions of Excel (I'm using 2010), pressing ctrl+up or ctrl+down will take you to the edge of the current data region or the start of the next data region in a worksheet. Effectively skipping "empty cells", i.e. cells where =isblank()
returns TRUE
.
I frequently set up a column in a worksheet with the formula eg:
=IF(MID($C2,5,1)=" ", "space", "")
to highlight rows where something I'm looking for is true, in this example: when the 5th character in the cell in column C is a space, the cell in the new column will be "space" and if it is not, the cell in the new column will be blank.
Visually, this is a good cue for finding the data rows you're interested in, and you can use a Filter to display only those rows that match.
I would like to be able to use ctrl+up or ctrl+down or some similar keyboard shortcut to skip between the cells with content and skip over the blank cells, but this doesn't work as the blank cells still contain a formula and are not "empty" cells. Is there any way, I could achieve this behaviour?
Short version:
Is there any keyboard shortcut in Excel to skip over cells which show no value, but aren't "empty cells"?
Or, is there any value I can return from a function (e.g =NA()
) that will trick Excel into thinking the cell is empty so the default ctrl+arrow keys shortcut will skip them?