Software

Quickly highlight matching values in Excel with keyboard shortcut

When you just need to see matching values and you don't want to filter or sort, try this Excel keyboard trick.

There are numerous ways to find matching data in an Excel sheet. Filters are probably the most versatile. Sorting is probably the easiest way, but even that's unnecessary if all you want to do is review matching values. In other words, if you don't need the values rearranged or filtered, there's a quicker way. Excel offers a keyboard shortcut trick that's quick to implement and easy to remember.

Let's work through an example using the sheet shown below. Specifically, let's highlight all the beverage records as follows:

  1. Highlight any cell in column G that contains the text value, Beverage.
  2. Press [Ctrl]+[Space] to select column G.
  3. Press [Ctrl]+[Shift]+\.

The combo in step 3 selects all the cells (in the column) that do not match the value in the active cell. It's a bit counter intuitive, but it works great!

About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

5 comments
DBlayney
DBlayney

I use a Swiss-German keyboard at work and the Ctrl Shift \ combo does not work for me. This is probably becasue on this keyboard it is necessary to use AltGr to get the \ character. I wonder if there is an equivalent? I would probably have done this with conditional formatting but it is celarly not as quick as the given solution, when it works.

RU7
RU7

If you use CTRL+A instead of CTRL+Space, CTRL+Space+\ selects all cells that do not match the relative cell in the active row.

RU7
RU7

But I don't know where the criteria comes from. CTRL+{ CTRL+Shift+[ (i.e., CTRL+{) CTRL+] CTRL+Shift+] (i.e., CTRL+}) CTRL+/

zimmerwoman
zimmerwoman

This key sequence is indeed useful for the purpose you described, but the description is not quite accurate. This will work for cells that EQUAL "beverage" not that CONTAIN "beverage" unless you specify that the cell contains ONLY "beverage". Frequently, my tasks require me to identify cells containing a particular string of text, but it could be buried in a larger string. Nice trick though. I'll use it when it fits..