21

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)
Tyler James Young
  • 315

2 Answers2

23

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.

Scransom
  • 113
5

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.

David Andrei Ned
  • 151