Excel – Does excel have an arrow key shortcut to skip blank cells

keyboard shortcutsmicrosoft excelworksheet-function

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?

Best Answer

No, but set it up this way, and Tab and Shift+Tab will take you to the next and previous highlighted row. First do this:

  1. Change your formula to =IF(MID($C2,5,1)=" ", 1, "") (copying down, of course)
  2. Highlight the column (Ctrl+Space will do that if you're in any cell in that column).

  3. Press F5, then Alt+Special, Formula, de-select Text, then Enter

    • To exclude errors, de-select Errors in step 3 also. (e.g., when LEN($C2)<5, but you could also change the formula to account for that)

Now the Tab key will step you through each cell in that column that has a numeric value in it (1). It also has the added bonus that in step 2 above, you can see the total of the highlighted rows in the status bar at the bottom of the screen (Sum:).

Related Question