Google Sheets has emerged as a leading online spreadsheet application, seamlessly blending functionality with user-friendliness. Whether you're an expert in data analytics or someone simply organizing personal records, the ability to efficiently navigate and search within your Google spreadsheets is of paramount importance. This tutorial shows how to use the Google Sheets Find and Replace feature so you can effortlessly locate and modify data when needed.
How to Use the Find Option to Highlight All Matching Cells
- Press ˝Ctrl˝ + ˝F˝ to show the search box
- Enter the words or characters you want to find
Type something you wish to search the worksheet for in the text box that appears just below the Extensions tab in the toolbar.
- Navigate through the search results
Use the arrows to the right of the text search box to navigate through each matched item. Green highlighting will allow you to scan through the results more easily.
How to Use Find and Replace in Google Sheets
“Find and replace” offers the ability to swiftly modify or correct data, ensuring accuracy and consistency. Whether rectifying errors, updating terminology, or ensuring uniformity, the Find and Replace function significantly enhances efficiency.
- Click “Edit” and then “Find and replace”
Alternatively, you can bring up “Find and replace” using the hotkey “CTRL+H”.
- You can use “Find and replace” as an advanced version of the search feature
- To replace text in the Google Sheet, enter the text at “Replace with” and choose the desired option
You can either click “Replace” once or several times which will only replace the matching text one after another. “Replace all” will replace all occurrences in the whole Google spreadsheet at once.
- Optional: Define, where text should be replaced
You can limit to use the Find and replace feature to the current sheet, apply it to “All sheets” or select a “Specific range” of sheets you want to replace text in.
- Define, how to search for text
“Match case” is very useful to distinguish between lower case and title case. You can also search for entire cell contents, expand or limit the search to formulas, and use regular expressions. If you don´t know how to use regular expressions, click “Help”.
How to Search in Google Sheets Using Conditional Formatting
Instead of using “Search” or “Find and replace”, you can use “Conditional Formatting” to mark/highlight cells that include certain text. The main benefit of doing this is that the highlighting will stay there until removed which allows you to work through the spreadsheet afterwards independently.
- Open ˝Format” from the menu and select ˝Conditional formatting˝
- Select cells for “Conditional formatting”
In “Single color” – “Apply to range”, enter the data range of the cells you want to find text and thus highlight.
- Select “Text contains” in “Format rules” – “Format cells if”
- Fill in the text or characters you want to find and highlight
- Choose your preferred “Formatting style”
You can choose text formatting, such as bold, italics, underlined,
strikethrough,or using a background color to highlight every cell where text is found.
- Using the yellow color for highlighters is usually a good option
- Click “Done” to activate and apply the conditional formatting rule
- Every cell containing the text in the Google sheet that contains the specified text will appear as highlighted
- Optional: Apply other formatting rules
Unlike the Google Sheets “Search” or “Find and replace” feature, you can use Conditional formatting also to highlight cells/text that does not contain or starts/ends with certain characters.
Extra: How to Make a Google Form / Google Survey
Google Forms is a web-based application that allows you to create and share online forms and use as a free survey maker with multiple question types. With Google Forms, you can easily design your forms with different themes and settings, send them to your target audience via email or link, analyze the responses in real time or export them to Google Sheets for further analysis. In our other guide, we show you how you can create Google Forms, share your Google survey and how to generate a results view.