Does excel have an arrow key shortcut to skip blank cells?
Solution 1
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:
- Change your formula to
=IF(MID($C2,5,1)=" ", 1, "")
(copying down, of course) - Highlight the column (Ctrl+Space will do that if you're in any cell in that column).
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)
- To exclude errors, de-select Errors in step 3 also. (e.g., when
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:).
Solution 2
In short: No.
The Ctrl+arrow keys will recognise only truly empty cells. There is no keyboard shortcut out of the box that can evaluate cell contents to jump to the end of a cell range with similar content, like formula results, error values or similar.
Solution 3
You could replace all your blank cells with something like zzzzz. Be sure to choose the option Match Entire cell contents if your data has blanks. Then select your range to copy, paste it were you need it. Then change all the zzzzz to a blank on the new source.
Solution 4
You could do Ctrl + F to Find
that True or False data value you want. Click on Find All
to get a List displayed in the dialog box which shows you all the locations where your formula is true of false.
Do you need more than just jumping around to the different areas?
Solution 5
Combining the other two answers, you could create a macro to fit your custom needs. The macro could be bound to whatever keys you like. You could create a series of macros for search up, down, left, and right and bind them to different keys.
Make the macro hold the current cell contents and then move one step in your chosen direction and compare the contents. If it is same, keep moving, if it is different, move the selection.
You will need some Visual Basic knowledge to make this relatively simple macro, and I don't know the syntax off hand, but it is possible.
Related videos on Youtube
Luke
Updated on September 18, 2022Comments
-
Luke almost 2 years
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()
returnsTRUE
.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? -
GlennFromIowa over 9 yearsI don't get exactly what you're saying, but changing to blank is a good idea! If you changed the formula to
=IF(MID($C2,5,1)=" ", "space", "zzzzz")
then used Copy, Paste Values on the column, then did Replace zzzzz with nothing (blank), thenCtrl
+Down
andCtrl
+Up
would work to skip the blanks! (I know there's conditions for when you can Replace in Values instead of Formulas, but Copy, Paste Values was easier than researching that.) -
Luke over 9 yearsExcellent! If I don't need dynamic results I can even de-select numbers in step 3 and hit delete, and then ctrl+up/down works perfectly!
-
G-Man Says 'Reinstate Monica' over 4 years“I can’t open this bottle of pancake syrup; the cap is stuck.” / “Well, just break the bottle with a hammer; then you’ll be able to get to the syrup.” Your answer is destructive (it changes the worksheet) and it doesn’t work!
-
Rajesh Sinha over 4 yearsIt's complicated but a nice idea +10 ☺
-
Rajesh Sinha over 4 yearsIt's one of the built in KB shortcuts.. quite useful ! +1 ☺