Don’t forget to share this post

If you’re ever working with large amounts of data, you need to know how to search in Google spreadsheets. Beyond the many practical options that enable you to fine-tune your search, simply knowing how to find specific cells will save you enormous amounts of time and energy. So, whether you want to search for duplicates, replace values, or just know the shortcut to finding the cell you need, learning how to search in Google spreadsheets is vital.

How to search in Google Sheets using Find?

If at some point you want to locate a group of cells from your Google Sheet containing a particular value or text, you can do this using what is called the Find option. The easiest way to use this option is with the shortcut explained here.

For the following step-by-step example, we’re going to search for cells containing the letter ‘X’:

  1. 1. Open the Google Sheet from which you want to work.
1
  1. 2. Press Ctrl + F / cmd + F and a search bar should appear in the top-right of your sheet.
2
  1. 3. Type in ‘X’.
3

This should highlight all the cells containing ‘X’ in your Google Sheet.

Use the up and down arrows beside the Find search bar to skim through the highlighted cells.

This method for finding cells is rapid and easy. However, if you want to change something about all of these cells (e.g., change X to Y), you would be better off using the Find and Replace option detailed in the following section.

How to VLOOKUP From Another Google Sheet or Workbook (+ Examples)

Sometimes our data may be spread out among different Google sheets or workbooks. Here's how to VLOOKUP from another sheet or workbook

READ MORE
How to VLOOKUP From Another Google Sheet or Workbook Examples

How to search in Google Sheets using Find and Replace?

Once again, we have a Google Sheet containing many different values. Our mission in this example is to locate all the cells containing ‘X’ and change their values to ‘Y’.

For the first example, you’ll learn how to locate cells and then change their values individually:

  1. 1. Go to Edit > Find and Replace. A dialogue box should open.
4
  1. 2. Type ‘X’ into the search bar labeled Find and click “Find”.
5
  1. 3. Type ‘Y’ into the search bar labeled “Replace with”.
6
  1. 4. Click “Replace” to replace the first ‘X’ with a ‘Y’, and so on.
7
  1. 5. Click “Done” when you finish.
8

For the second example, you’ll learn how to locate cells and then change all of their values simultaneously:

  1. 1. Go to Edit > Find and Replace. A dialogue box should open.
9
  1. 2. Type ‘X’ into the search bar labeled Find and click “Find”.
10
  1. 3. Type ‘Y’ into the search bar labeled Replace with.
11
  1. 4. Click “Replace all” - instead of “Replace” - to immediately replace every ‘X’ with a ‘Y’.
12
  1. 5. Click “Done”.
13

How to Find and Replace in all sheets/current sheet/range of cells?

For the sake of demonstration, the following three examples will assume you want to replace all the chosen values simultaneously using the Replace all option.

How to Find and Replace in all Google Sheets?

  1. 1. Go to Edit > Find and Replace. A dialogue box should open.
14
  1. 2. Type ‘X’ into the search bar labeled Find and click “Find”.
15
  1. 3. Type ‘Y’ into the search bar labeled Replace with.
16
  1. 4. Click the drop-down menu labeled Search and select “All sheets”.
17
  1. 5. Click “Replace all” to immediately replace every ‘X’ with a ‘Y’ in all your Google Sheets.

How to Find and Replace in your current Google Sheet?

  1. 1. Go to Edit > Find and Replace. A dialogue box should open.
18
  1. 2. Type ‘X’ into the search bar labeled Find and click “Find”.
19
  1. 3. Type ‘Y’ into the search bar labeled Replace with.
20
  1. 4. Click the drop-down menu labeled “Search” and select “This sheet”.
21
  1. 5. Click “Replace all” to immediately replace every ‘X’ with a ‘Y’ in that Google Sheet.

How to Find and Replace in a range of cells?

  1. 1. Go to Edit > Find and Replace. A dialogue box should open.
22
  1. 2. Type ‘X’ into the “Find” search bar, and click “Find”.
23
  1. 3. Type ‘Y’ into the search bar labeled “Replace with”.
24
  1. 4. Click the drop-down menu labeled Search and select “Specific range”.
25
  1. 5. Input the references of the cell range you want to use (e.g., B3:C7).
26
  1. 6. Click “Replace all” to immediately replace every ‘X’ with a ‘Y’ within your given range.

Find and Replace options

In the Find and Replace dialogue box, you can see that there are four tickable boxes below the options we’ve already discussed. Appropriately ticking these boxes enables you to tweak your parameters to search Google Sheets more precisely.

The following sections will explain how to use each of these options:

Match case

This refers to upper and lower-case letters. If you tick this box and type upper case ‘X’ into the search bar labeled Find, Google Sheets will not highlight lower-case ‘x’ in the result.

27

Match entire cell contents

Ticking this box means that the search will only highlight cells containing precisely what you have put into the search bar labeled Find - no less, no more. For example, you should tick this box if you want to locate all the cells containing ‘X’ but exclude those containing ‘XY’.

28

Search using regular expressions

As you should see, Google has placed a Help link beside this option. This link will bring you to a better understanding of regular expressions and how to Find and Replace them.

29

Also search within formulas

This final option will not only search through the range but also within the formulas used on your Google Sheets.

30

How To Use IMPORTRANGE Function In Google Sheets?

Google Sheets allows you to import and link a specific range of cells from another spreadsheet. Here's how to use the IMPORTRANGE function in Google Sheets

READ MORE
How To Use IMPORTRANGE Function In Google Sheets

How to search for duplicates in Google Sheets?

When working with a lot of data, you can accidentally duplicate values, rows, or columns. While this is not a huge problem, people often don’t notice it, leading to significant miscalculations further down the line.

For this reason, you should know how to locate and remove duplicates. The following example will show you in steps how to remove duplicates from a range using the Remove duplicates feature:

  1. 1. Select the data range potentially containing duplicates.
31
  1. 2. Go to Data > Data cleanup > Remove duplicates.
32
  1. 3. Tick the box labeled Select all in order to search all of your chosen range.
33
  1. 4. Click “Remove duplicates” to bring up a confirmation window that tells you what was found and removed.
34

How to highlight duplicates in Google Sheets?

Sometimes you may not want to remove duplicates, but only separate them from the rest of the data, and you can do this by highlighting them.

These steps will demonstrate how to use the Color Highlighting function:

  1. 1. Select the data range potentially containing duplicates.
35
  1. 2. Go to Format > Conditional formatting to open a sidebar.
36
  1. 3. Select “Custom formula is…” from the drop-down menu labeled Format rules.
37
  1. 4. Input a formula of your choice. In my case, I put: =countif(A:A,A1)>1.
38
  1. 5. Click “Done” when you see the desired values highlighted on the sheet.
39

How can Layer help?

Layer is a spreadsheet platform that works on top of Excel and Google Sheets. It allows you to easily manage and automate spreadsheet workflows. Using Layer, you can:

  • Upload or connect your existing Excel or Google Sheets-based budget.
  • Share different sheets or even cell ranges of your spreadsheet with various stakeholders or departments involved in the budgeting process.
  • Automate your communication flows and keep track of your data submissions, contributors, and deadlines.
  • Review every single change made and decide which ones to merge with your spreadsheet or discard.
  • Eliminate errors in your budget or manually copying and pasting data across files.

Sign up and schedule an onboarding call to get started with Layer right now.

Conclusion

As you can see, knowing how to search in Google spreadsheets is a core skill that can save you a lot of time when working with large amounts of data. Not only can you use shortcuts to find cells that you’re looking for, but you can also refine your search with details. You can then use the Find and Replace option to replace one or all of the values you want on a sheet, all sheets, or within a given range with the click of a button.

Duplicates are another source of pain for some Google Sheets users, so having the technique for locating and removing or highlighting them can save you much stress going forward. Thankfully, Google Sheets also has an IOS and Android app that offers nearly all the same tools as the desktop version.

Hady ElHady
Hady is Content Lead at Layer.

Hady has a passion for tech, marketing, and spreadsheets. Besides his Computer Science degree, he has vast experience in developing, launching, and scaling content marketing processes at SaaS startups.

Originally published Jan 31 2022, Updated Feb 20 2022