How to find and replace in Google Sheets?

In our professional lives, we often deal with vast amounts of data that constantly change as time goes on. Locating this data and replacing the existing values with new ones is something that many of us have to do. This may be easy enough when there are a few rows of data. But it gets even more complicated and time-consuming when this data grows significantly. Google Sheets is a great spreadsheet tool for storing and managing large amounts of data. But it also offers a fantastic feature to help make identifying and changing data even easier. It’s called the Find and Replace in Google Sheets.

The find and replace feature in Google Sheets is a fantastic tool to use when dealing with lots of data that constantly needs to be updated. Especially data reflecting a process or status. For example, let’s say I’m managing the order status of the latest product purchases. There will be a constant change between various values, such as the order status, stock levels or even price changes. Using the find and replace feature, I’d be able to locate and change these specific pieces of data from hundreds of rows and change them accordingly. Not only will this reduce the time it takes for me to find specific values, but it will also make the entire data management process more efficient.

Let’s take a look at the find and replace feature in more detail and go through a step-by-step on how to find and replace values in Google Sheets.

Why use the find and replace feature in Google Sheets?

The Find and Replace feature in Google Sheets has a number of variations that fulfill different objectives when managing your data. Not only can you identify and change values in your spreadsheet as we will demonstrate in this article, but you can also apply extra features which take your searches to the next level.

Here are some of the additional features of the Find and Replace in Google Sheets:

• Choose to filter where you search for your value: you can narrow the value search from your entire spreadsheet to a single sheet, or even a specific range.
• Match case: make your searches case sensitive for extra filtering, or even to help identify any grammatical errors.
• Match entire cell contents: ensure your searches are as accurate as possible by opting for this feature.
• Search using regular expressions: search for cells that match a pattern. You can learn more about this more advanced process on the Google Support site.
• Search within formulas: you can also search for specific formulas you have inputted into your spreadsheets.

Now that you have a good understanding of what the Find and Replace feature in Google Sheets offers, let’s put it into practice!

How to find and replace values in Google Sheets

Using the use case from above, let us locate and replace values within our spreadsheet full of data about the order statuses of our customers’ purchases.

Let’s take a look at the different ways you can use the Find and Replace feature for your data.

1. How to find a value in Google Sheets

Let’s say we want to find the number of orders of Jam products in our spreadsheet.

To open the Find and Replace feature, you have two options:

1. In the navigation menu in Google Sheets, press Edit > Find and Replace
2. Use the keyboard shortcut combination Ctrl + H (Cmd + H on Mac)

In the Find field, type the value you’re looking to find in the spreadsheet (in this case, “Jam”). If you want to make your search case-sensitive, tick the box Match case.

Then press the Find button located at the bottom of the pop up. The first value containing the value “Jam” will be highlighted. Continue to press the Find button to see the next time that the word is used.

Alternatively, there is a simpler method to find values that do not use the Find and Replace feature in Google Sheets.

Simply press the key combination Ctrl + F (Cmd + F on Mac). You will see a small text box on the top right section of the spreadsheet. Type in the value that we are seeking to find in the spreadsheet (in this case “Jam”.). The feature will then highlight all the cells that contain the search text. We can make use of the up and down arrow buttons to move to the next matching occurrence.

2. How to find and replace a single value in Google Sheets

Let’s say that I need to change the status of this particular order from “Dispatched” to “Received”.

First, open the Find and Replace feature using your preferred method. In the Find field, type in the value you want to find and replace. In this case, I will type in “Dispatched”.

In the Received field, type in the new value. In this example, it’ll be “Received”. In order to find the exact value you have searched for, tick the box Match entire cell contents.

Press the Find button. Make sure the cell belonging to the correct row is selected.

Once selected, press the Replace button located near the bottom of the pop up.

The feature will notify you that the value has been changed.

Once you’re happy with the result, press Done to return to your spreadsheet.

2. How to find and replace a single value in Google Sheets

Let’s say that I need to change the status of this particular order from “Dispatched” to “Received”.

Follow the same steps as above. Instead of choosing the Replace button, Select Replace all. With this option, every single “Dispatched” value will be replaced with “Received”.

As you can see, the feature identified 25 values as “Dispatched” and has successfully replaced these with “Received”.

Once you’re happy with the result, press Done to return to your spreadsheet.

For other ways to look up data, including searching across all sheets, inside formulas, and for duplicates, see how to search in Google Sheets.

Find and replace feature in Google Sheets

And there you have it! The Find and Replace feature is a fantastic tool when managing data that constantly requires manual changes. With different combinations of the numerous options available, you can carry out a range of different tasks from finding specific values, to finding and replacing multiple values.

Interested in learning about other features in Google Sheets? Take a look at our article on the History of changes in Google Sheets! Alternatively, discover our related articles below.