Is there a way to skip down to the next change in value in Excel?

Question

For my current daily use of Excel, I have a sheet where hundreds of consecutive rows have the same value for a particular column. I’d like a way to quickly skip down to the next different value.

ctrl + ↓ goes to edge (i.e. the end or the next break in data), but I want to only skip identical cells.

I am looking for a keyboard command, not a macro or an extension-dependent solution.

The solution must work for a column of formula-populated cells. The formula in question:

=VLOOKUP($N19377,'Dates and Codes'!$B:$D,2,FALSE)

Solution 1

You can use the Go To feature to get to the next different cell.

1. Highlight the column you are interested in
2. Press Ctrl+Shift+\

The first different cell within that column will be selected.

Step 2 is the shortcut for the following steps:

1. Press F5 to bring up the Go To menu
2. Click Special..., click Column Differences, and then click OK.

For more information see the Excel Help Documentation: Find and select cells that meet specific conditions.

Solution 2

Actually there is a shortcut called CTRL+\ for selecting the cells that don’t match the formula or static value in the active cell (which accomplishes the same as the method in the answer)

However, that is not the answer for what the OP asked, as it does not return a different Value for formula-determined cells, but the next different Formula.

"The solution must work for a column of formula-populated cells."

This is particularly annoying if you are using a single formula to determine different values, as I am currently doing. If the same formula gives different values, the method described in the answer does not recognise differences. The easiest way to make it work would be to Paste as Value in a separate column, and then run Ctrl-| (which is the same as Ctrl-Shift-\)

Note: there is no such combination as Ctrl-Shift-| , that is like saying Shift-! exists when it is in fact Shift-1

Sorry to seem overly critical but the question is a very pertinent one and the answer does not do it justice.

Comments

[meatspace]

I don't think there is a native solution for this. Are you open to a macro or third-party solution that is invoked via keyboard command?

[Tyler James Young]

@meatspace That explains why I can’t find one on any shortcut lists, but I could have sworn I’d heard of a native solution at some point and it seemed pretty reasonable that one would exist... I think my next move would be to write an AHK script for myself.

[meatspace]

That was going to be my suggestion.

[Admin]

Ctrl+Shift+| works for me - even with =vlookup. Not sure why it won't work for you.

[Tyler James Young]

I’m not sure either. I keep getting the error “No cells were found.” It works everywhere else, though, so I’ll accept your answer. Thanks!

[Tyler James Young]

This is an interesting route to take, but doesn’t appear to work for columns of formula-populated cells.

[Kaydax]

Strange, I tested this out on a column with formula-populated cells before posting an answer. The entire column, or at least the section of data you want to compare for differences must be selected before bringing up the Go To menu. Maybe you can update your question with the formula you're using - I can take a closer look.

[Tyler James Young]

Putting this into practice, I hold ctrl + shift, press ↓ to highlight the rest of the column and then press | to jump to the next change. This can all happen pretty fast, so it’s a good shortcut. Thanks!

[8bittree]

Welcome to Superuser! The question is open (if it was closed, you would not have been able write this answer). If you're referring to the fact that there is an accepted answer, that's determined by the user that asked the question. This is supposed to simply indicate that that answer worked for them. For the broken link, there should be an "edit" or "improve this answer" link just under the left side of the answer, you can use that to suggest an edit that fixes the link, if you're aware of a replacement.

[David Andrei Ned]

If I ask more or less the same question because the above answer did not work for me, will it be marked as a duplicate question and referred back to this 'answer'?

[8bittree]

There would certainly be a good possibility of it being closed as a duplicate. You should definitely include a link back to this question and an explanation of why it doesn't satisfy you. However, I would recommend first seeking advice on Meta Super User or in chat, so you can bring it to the attention of some of the folks that would be doing the dupe voting, and see what they suggest.

[Scransom]

This solution appears to move to the next unique value, not the next different value. I.e. in a list: cat, dog, dog, cat, fish; ctrl+shift+\ would move from cat to dog to fish, but moving among different values should move from cat to dog to cat to fish.