Skip to content

Find and Select in Excel: Search Specific & Case-Sensitive

Find and Select in Excel: Search Specific & Case-Sensitive

You can use the Find and Select in Excel to quickly search, find, select, and replace data. This is an essential feature. In fact, you can find, select, and replace data with specific conditions up to case-sensitive.

Basically, this guide will explain how to use this feature – Find and Select in Excel. However, I will also show you how to use the Go To Special feature to search, find, and select data with certain conditions.

At least this will help you search for data in cells that contain:

  1. Specific Formulas/Functions.
  2. There are Comments.
  3. Using Conditional Formatting.
  4. Data Validation.
  5. And many others.

Before continuing, please download the following excel file to use Find and Select in Excel as practice.

Let’s read your guide further!

Use Find and Select in Excel to Search Your Data

In this section, I will answer the following question – “How to do Search in Excel?”

Yeah! If you’re working with very large amounts of data, finding cells that contain a particular value can be very difficult.

However, with Find and Select in Excel, you can search for data in the form of text, numbers, or a combination of text & numbers, even certain characters in a Worksheet or Workbook.

By default, this feature isn’t case-sensitive. This means that if you search for the word Shop, Excel will include any cells containing the word Shop in the search.

In other words, cells containing the words MYShop or Shopping Bag Crime will also be included in the search results. This means that Excel will not consider uppercase or lowercase letters.

Important! If you want to search for data by considering upper and lower case letters, please skip to the Find & Replace Case-Sensitive section.

So, to use the Find & Select feature in Excel, please follow these steps:

How to use find and select in excel
  1. Click the Home Tab and click Find & Select. Then click Find. You can also change these steps by pressing the Ctrl + F keys on the keyboard simultaneously.
  2. In the Find & Replace dialog, please type the letter/number/character you want to search for. For example, I will search for the word “Shop”.
  3. Click Find Next, Excel will automatically select the cell that contains all or part of the word “Shop”. Click Find Next again to see the next cell containing part/all of the word “Shop”.
  4. If you want to search for all cells containing (partial/entire) the word “Shop”, please click Find All. Here are the results:
Example of Find and Select in Excel

Excel provides results in table form with 5 columns, namely Book, Sheet, Name, Cell, and Value.

You just need to focus on the cell Column and Value. The Cell column contains information in the form of which cells contain the data you are looking for.

Meanwhile, the Value column contains the data contained in that cell. You only need to click on one of the results to move to that cell quickly. This feature is very useful if you have a very large number of rows of data.

Use Find, Select, and Replace to Mass Changing Your Data

From this Find & Select in Excel, you can also search and find large amounts of data, and then replace all the results quickly. However, like the Find, Replace feature in Excel also doesn’t support case-sensitive.

Let’s learn more…

I give the same example with the Find feature. Take a look at the title of the table on the following sheet which contains “TABLE OF PURCHASING GOODS IN MYSHOP MJURNAL.COM”:

Use Find, Select, and Replace to Mass Changing Your Data

Meanwhile, the Supplier column contains 2 types of data with the word “Shop”, namely ONE SHOP, Mike Shop, One sHop, and Shopping Bag Crime.

If you use the Find & Replace in Excel to change the word “Shop” to “Store”, automatically, all cells that contain (partially/all) the word Shop will also change. Let’s prove it.

How to bulk replace data with Find and Select in Excel ?
  1. Click Find & Select on the Home Tab, then click Replace – Or you can also press Ctrl + H simultaneously.
  2. Type “Shop” in the Find What column as the word we want to search for.
  3. Type “Store” in the Replace With column as a replacement word.
  4. Click Replace All to change all cell values that contain part/all of the words “Shop” to “Store”. Here are the results:
Example of mass replace data in Excel

That’s right?

All cells containing part or all of the word “Shop” will be replaced with the word “Store”.

In the image above, the header section which contains “TABLE OF PURCHASE OF GOODS IN MYSHOP MJURNAL.COM” has also changed to “TABLE OF PURCHASE OF GOODS IN MYStore MJURNAL.COM”.

This indicates that the Find, Select, and Replace feature in Excel isn’t case-sensitive.

MYSHOP in the table title uses capital letters. Meanwhile, in the example above, we are looking for the text “Shop” not “SHOP”.

So, if you want to use Case-Sensitive (considering upper and lower case letters), please follow the Find & Replace Case-Sensitive guide below.

Find and Select in Excel to Replace With Case-Sensitive

For example, I want to search for cells that contain the text “Shop”. This means that cells containing text such as sHop, shOp, or SHOP aren’t included in the search criteria.

For this goal, you can tell Excel to search for cells containing the text “Shop” by considering upper and lower case letters.

If you use the Find & Replace feature without being case-sensitive, of course, all cells containing the Shop text (some/all of its contents) will also change. Includes Header and cells containing the word “shop one” (in lowercase).

So, for this case, please use the following Excel case-sensitive Replace feature:

Example of Find and Select to Replace Case-Sensitive in Excel
  1. Open the Find & Replace Dialog as before.
  2. Type the word you want to search for in the Find What column and a replacement word in the Replace With column.
  3. Click Options, then check Match Case.
  4. Click Replace All. Results:

What do you think?

How to mass replace data in Excel with case-sensitive

Excel only replaces cells that contain text that exactly matches the text “Shop” – case sensitive.

See the header/title has not changed because it contains the text MYSHOP in capital letters. Meanwhile, the cell containing the text “One sHop” and ” in lowercase doesn’t change.

Find and Select in Excel with Criteria

Once again, there is an interesting feature that can make it easier for you to find, select, and replace data in Excel, namely Go To Special.

Note: You can use Excel’s Go To Special feature to search for more specific data such as searching for cells that use formulas, Conditional Formatting, Data Validations, and many other conditions.

For example, I will look for any cell that uses a formula/function. Please follow the following steps:

Find and Select in Excel with criteria
  1. Click Find & Select on the Home Tab, then select Go To Special.
  2. In the Go To Special Dialog, you can determine the data criteria to be searched for. To search for cells that use formulas/functions, please select Formula.
  3. Check Numbers, Text, Logicals, and Errors to search for all cells that contain that Function Category.
  4. Click OK to continue. Results:
Step by step find and select in Excel with criteria

Right?

Only cells that use formulas/functions will be selected automatically.

I gave an example of finding and selecting cells using Data Validation.

In this example, I created a Dropdown List using Data Validation in cells H4. It looks more or less like this:

Use go to special to Find and select in Excel with criteria

In this sheet, only cells H4 use the Data Validations feature. Let’s prove it, is Excel successful in finding and selecting it? Look at the following image:

Step by step to use Go To Special to Find and Select cell with criteria

Note: To prove this, please select another cell (other than cell H4) before using the Go To Special feature.

You only need to change the search criteria in the Go To Special Dialog to Data Validation then click OK. Results:

Example of Go To Special to find and select in Excel with criteria

Isn’t that right?

Excel only selects cells that use Data Validations (cell O4).

And next…

There are many useful Excel learning tutorials for you. Please use the following shortcut:

Guide Shortcuts: Find & Select

Previous Chapter: Custom Number Format

  1. Find & Select: You’re here!
  2. Delete Blank Rows: How to delete blank rows (useful if there are unwanted blank rows in the middle of data etc.).

Next Chapter: Excel Templates

Lihat Versi Bahasa Indonesia

Your Comment:

Your email address will not be published. Required fields are marked *