Find specific cells in Excel

Finding the right cells by one condition or several is one of the most common tasks in Excel. Cells with a certain word, with numbers inside text, duplicates, blanks, bold text: each of these is solved differently. This page is a map of all the options, and it belongs to the Find section of the site.

Find cells in Excel (built-in tools and functions)

Excel offers several built-in ways to locate cells by their contents: Find and Replace, filters, Conditional Formatting, Go To Special, and logical and text functions. Which one fits depends on what exactly you mean by “find”.

What does “find” mean in Excel?

Everyone puts their own meaning into the word “find”:

• For some, it means marking the right cells somehow. Conditional Formatting usually works best here.
• Others filter the data to see only the cells that meet the criteria. That’s also a kind of search.
• Some want to extract data from cells and move it elsewhere for further work.
• And some need a marker next to each cell: TRUE/FALSE or a label returned by a formula with the IF function.

Now let’s look at what a cell is. An Excel cell is a compound object with three parts: the value, the formula and the formatting. Accordingly, there are three fundamentally different ways to search for cells:

• by value;
• by formula;
• by format.

Below I show how to find cells in each of these ways, with links to detailed examples.

Finding cells by value

Most often you need to find cells by their value. The value can be considered on its own, or as part of a range, where values are related to each other. The simplest and most popular task of the second kind is finding cells that duplicate each other.

Find unique or duplicate cells

Regardless of the data type, a cell either duplicates others or contains a unique value that occurs only once. There are two root algorithms for finding duplicates:

• Within a single range: mark all repeated values in a column.
• Between two ranges: compare two lists row by row, cell by cell, or find the values of one column anywhere in the other.

The built-in way to mark duplicates is Home → Conditional Formatting → Highlight Cells Rules → Duplicate Values. With a formula, the COUNTIF function does the job. This formula returns TRUE if the value in A2 occurs in the column more than once:

=COUNTIF($A$2:$A$100,A2)>1

And this one returns TRUE only for the first occurrence of each value, so you can keep it and filter out the rest:

=COUNTIF($A$2:A2,A2)=1

There is also a less obvious case: implicit duplicates, phrases with the same words in a different order, like “buy sofa” and “sofa buy”. No built-in tool sees them. All the algorithms, including this one, are covered in the article on how to find duplicates in Excel.

Finding cells that contain text

The type of the value affects how we search. If the cells contain text, the criteria are text-specific too:

• Find cells that contain a certain word, not just a substring;
• find cells that contain all words from a list, or at least one of them;
• find cells that start or end with certain text.

Searching by one text value is easy with the built-in Find and Replace (Ctrl+F). For two conditions at once, the Advanced Filter helps. But for several conditions at once, you need formulas. For example, if the list of words is in D1:D4, this formula returns TRUE when A1 contains at least one of them:

=SUMPRODUCT(--ISNUMBER(SEARCH($D$1:$D$4,A1)))>0

The SEARCH function looks for each word of the list in the cell, and SUMPRODUCT counts how many were found. To require all words of the list, compare the count with the number of words:

=SUMPRODUCT(--ISNUMBER(SEARCH($D$1:$D$4,A1)))=ROWS($D$1:$D$4)

It works, but every new condition means a new formula, and every formula has to be typed, filled down and removed afterwards. In the articles on each task, I give both the formulas and the matching !SEMTools procedures, which do the same in a couple of clicks.

Finding cells with numbers

Cells with numbers are more interesting than cells with text. Numbers can be treated both as text and as actual numbers, and cells can be hybrid, containing both text and numbers (“Price: 500”). Some cells only look like numbers but are stored as text.

All text criteria apply to numbers too, but you also get numeric criteria. They are available in Data → Filter → Number Filters:

• equals / does not equal;
• greater than / less than (or equal to);
• between;
• above / below average;
• top or bottom N values (top 10 by default).

Dates and times are also numbers in Excel, just formatted differently, so the same criteria work for them. The filter adds date-specific options on top: by year, month, “last week”, “next quarter” and so on.

The hardest case is numbers inside text. You can’t apply numeric criteria to a cell that contains text. Say your text cells contain a year of birth, and you need people born in 2006 or later. One option is to extract the numbers from the cells into a separate column and filter it. Another one is to list all the years from 2006 on in an array formula with SEARCH:

=COUNT(SEARCH({"2006","2007","2008","2009","2010","2011","2012","2013","2014","2015","2016","2017","2018","2019","2020","2021","2022","2023"},A1))>0

The formula returns TRUE or FALSE depending on whether the cell contains one of the years. In Excel 365, the SEQUENCE function generates the list of years for you, and it’s easy to extend:

=OR(ISNUMBER(SEARCH(SEQUENCE(20,,2006),A1)))

More on this in the articles on how to find cells with numbers, including how to find only certain numbers among all the digits.

Search among cells with formulas

The formula of a cell and its value are different things. Without a formula they are the same, but if a cell contains =50*2, it shows 100, and the text of the formula doesn’t contain “100” at all. That’s why Find and Replace can search both in formulas and in values: click Options and choose the right item in the Look in list. Important point: the default is Formulas!

To select all cells with formulas at once, press F5 → Special → Formulas. The same dialog selects constants or blank cells.

Finding cells by formatting

Excel offers a huge range of formatting options, and that makes dry reports readable. But what if you need to find the cells formatted in a certain way?

• bold cells;
• italic or underlined text;
• a certain font color, fill color or border.

Find and Replace can search by format: Options → Format. Here is a simple example:

The limitation is that it finds cells whose whole content has this format. If only one word in a cell is bold, Find and Replace won’t see it, and formulas can’t see formatting at all. For this case, see how to find cells with bold text.

Find cells in Excel with !SEMTools

The Find cells section of the !SEMTools add-in finds cells by content, frequency, emptiness or regex pattern. Some of these tasks have no built-in equivalent in Excel, others need formulas like the ones above. With the add-in, you don’t need to remember any syntax: select the range, choose the condition, and each cell gets TRUE or FALSE right in place. Then filter or delete what you need.

Find cells by content

These procedures find cells that contain certain text:

• Contains (from a list): finds cells that contain at least one item from a list. For example, all cells with “buy”, “order”, “deal” or “cheap”.
• Starts with…: finds cells that begin with certain patterns. You can use your own list or built-in ones, like common prepositions.
• Ends with…: the same for the end of the text. Useful for spotting location or context tags at the end of phrases, like “in NYC” or “for home”.
• Regex match: finds cells by a regular expression, such as numeric patterns, a certain string length or URLs.

Find cells by frequency

Frequency is an attribute of a cell value in relation to the range it’s in. It has two important aspects: whether the cell is unique, and which occurrence of the same value it is, the first or the last.

• Unique only: cells that occur once and only once.
• Duplicates only: cells that occur twice or more.
• First match only: TRUE only for the first occurrence of each value.
• Last match only: TRUE only for the last occurrence.

If a cell is unique, it counts as both the first and the last occurrence, so it always gets TRUE in the last two procedures. Which is logical, if you think about it.

Find blank cells

The procedure marks all empty cells in the selected range. It helps to check incomplete tables before building a report. If you need to get rid of them, see how to delete cells in Excel by different conditions.

Find duplicates

The Find duplicates menu marks repeated values by finer criteria than the built-in Excel tools.

Duplicates within a range:

• All except first: marks all duplicates starting from the second occurrence.
• All including first: marks every repeated cell, including the original.
• Implicit duplicates: finds phrases with the same words in a different order (“buy sofa” and “sofa buy”), marking all but the first. Extremely helpful for keyword cleanup.

Duplicates across ranges:

• Exact match: compares two ranges and finds matching values.
• Implicit duplicates: finds phrases that are the same but written in a different word order in two data sets.

Once the duplicates are found, you can remove duplicates in one more click.

See also:

• VLOOKUP function

Summary: how to find specific cells

I hope you found what you were looking for on this page and know how to find the cells you need. For a single value, Find and Replace or a filter is enough. As soon as there are several conditions, a list of words or duplicates, formulas get long, and each of them has to be typed, filled down and removed afterwards. That’s where !SEMTools saves the most time: select the range, choose the condition, done.

You may also be interested in related operations: find characters in cells, extract cells by condition or change cells in Excel.