How to Replace Data in Google Sheets

Replacing data in a Google Sheet doesn't have to be a tedious task of deleting and re-typing. Whether you need to fix a single typo across hundreds of cells, swap old product names with new ones, or even replace an entire dataset from an external file, Google Sheets has a tool for the job. This guide will walk you through the most effective methods, from the simple find-and-replace command to powerful formulas that give you complete control over your data updates.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

The Go-To Method: Using 'Find and Replace'

The fastest and most common way to substitute data is with the built-in "Find and Replace" tool. Think of it as your spreadsheet’s universal search-and-destroy button for data. It's perfect for bulk updates, correcting widespread misspellings, or standardizing terminology across your entire file.

Step-by-Step Guide to Find and Replace

Let’s imagine your company recently rebranded a product from "Pro Suite" to "Ultimate Plan" and you need to update your marketing campaign tracker sheet.

1. Open your Google Sheet.
2. Navigate to the top menu and click Edit > Find and replace. You can also use the keyboard shortcut: Ctrl + H on Windows/ChromeOS or Command + Shift + H on Mac.
3. A dialogue box will appear with two main fields: "Find" and "Replace with."
4. In the "Find" field, type the text you want to remove. In our example, you’d type Pro Suite.
5. In the "Replace with" field, type the new text you want to insert. Here, you’d type Ultimate Plan.
6. Choose where you want the search to apply. The "Search" dropdown gives you options to search in "All sheets," just "This sheet," or a "Specific range" you've highlighted. This is important to prevent accidental changes on other tabs. For our example, let's assume we want to update it on the current sheet only, so we select "This sheet."
7. Click "Replace all" to change every instance at once. If you'd prefer to review each change, you can click "Find" to cycle through each occurrence and then "Replace" one by one.
8. Once complete, Google Sheets will show a notification telling you how many instances were replaced.

Power-Up Your Search with Advanced Options

The "Find and Replace" tool has a few checkboxes that give you much more precision:

• Match case: This makes your search case-sensitive. If you check this box, searching for "pro builder" would not find "Pro Builder." This is useful for dealing with acronyms or proper nouns where capitalization matters.
• Match entire cell contents: Selecting this will only find cells where your search term is the only thing in the cell. For example, if you're looking for "Pro Suite" and check this box, it won't find a cell that contains "Pro Suite - Q4 Bundle." This prevents you from changing parts of longer text strings unintentionally.
• Search using regular expressions: This is an advanced option for pattern-based searching. For example, you could use a regular expression to find and replace any email address that ends in ".co.uk" with one that ends in ".com," without having to specify each email address individually.
• Also search within formulas: By default, Google Sheets only looks at the output of a cell, not the formula behind it. Checking this box lets you change parts of your actual formulas, which can be a lifesaver for complex sheets but should be used with caution!

Replacing Data Dynamically with Formulas

Sometimes you don't want to permanently change your source data. Instead, you want to create a new column that reflects a change, keeping your original data intact for reference. This is where formulas shine.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Using the SUBSTITUTE Function

The SUBSTITUTE function is perfect for swapping out specific pieces of text within a cell. It looks at a piece of text, finds a substring you specify, and replaces it with a new one.

The syntax is: `=SUBSTITUTE(text_to_search, search_for, replace_with, [occurrence_number])`

Imagine you have a list of US cities and states in column A (e.g., "Brooklyn, NY", "Austin, TX") and you want to replace the two-letter state codes with their full names. You could use this formula in column B:

`=SUBSTITUTE(A1, "NY", "New York")`

You can drag this formula down the column, and it will create a new, cleaner list of locations while leaving your original data untouched. If a cell in column A doesn't contain "NY," the formula will simply return the original text without making a change.

You can even nest SUBSTITUTE functions to handle multiple replacements at once:

`=SUBSTITUTE(SUBSTITUTE(A1, "NY", "New York"), "TX", "Texas")`

Using the REPLACE Function

While SUBSTITUTE looks for what to replace, the REPLACE function works based on where to replace. It lets you swap out characters based on a starting position and a character count. This is less common but incredibly useful for structured data like SKUs, order numbers, or phone numbers.

The syntax is: `=REPLACE(original_text, starting_position, number_of_characters, new_text)`

Suppose you have a list of product IDs in column A that look like "SKU-491-PROD". You discover that the middle number "491" needs to be updated to "550" across the board. The REPLACE function is perfect for this.

`=REPLACE(A1, 5, 3, "550")`

• A1 is our source cell.
• We start at position 5 (right after "SKU-").
• We replace 3 characters ("491").
• We insert the new text "550".

The result would be "SKU-550-PROD".

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Replacing an Entire Dataset with an Import

Many workflows involve receiving updated data as a separate file, like a monthly sales report export from Shopify or a new list of leads from your CRM in a CSV file. Instead of copy-pasting, you can use the Import feature to replace data in an entire sheet (or the whole spreadsheet).

How to Import and Replace Data

Let's say a colleague sends you an updated inventory list as a CSV file, and you want it to replace the outdated list in your main "Inventory" sheet.

1. In your Google Sheet, click File > Import.
2. In the import dialogue box, go to the Upload tab and select the file from your computer (e.g., inventory-updated.csv).
3. The "Import file" options window will appear. This is the crucial step. In the "Import location" section, you’ll see several powerful choices:
4. For our example, you would select Replace current sheet. This ensures your "Inventory" tab now contains only the fresh, up-to-date information.
5. Click "Import data."

Conditional Replacement Using the IF Function

Sometimes you need to replace data based on a rule or a condition in another cell. The classic IF() function is your best friend here. It checks if a condition is true, and then returns one value if it is, and another if it's not.

The simple syntax is: `=IF(condition, value_if_true, value_if_false)`

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Practical Example: Updating Contact Statuses

Imagine a simple CRM in Google Sheets. Column A has contact names, Column B shows their original status (e.g., "New Lead"), and Column C notes whether they've replied to your email outreach ("Yes" or "No"). You want to create a new "Updated Status" column (Column D) that changes "New Lead" to "Engaged" if they have replied.

In cell D2, you would write this formula:

`=IF(C2="Yes", "Engaged", B2)`

Let's break it down:

• C2="Yes": This is the condition being checked. Does the cell in the "Replied" column say "Yes"?
• "Engaged": This is the value a cell in Column D will have if the condition is true.
• B2: This is the value a cell in Column D will have if the condition is false. It simply keeps the original status from Column B.

When you drag this formula down Column D, you'll have a dynamic list of statuses that automatically updates based on whether or not a lead has responded, without you having to manually find and replace anything.

Final Thoughts

Mastering these various ways to replace data transforms Google Sheets from a static grid into a dynamic tool for data management. Whether you're making quick fixes with Find and Replace, creating adaptable new datasets with functions like SUBSTITUTE or IF, or overhauling a whole sheet with an import, these skills are essential for keeping your information accurate and up-to-date.

While learning these Google Sheets skills is incredibly useful, you'll often find yourself doing this kind of cleaning just to prepare a report for analysis. At Graphed, we created a way to skip the manual work entirely. By connecting directly to your data sources like Google Analytics, Shopify, or HubSpot, we make sure your data is always live and ready for analysis - no more exporting CSVs, performing manual replacements, or wrestling with pivot tables just to see what’s working. You can simply ask a question in plain English and get a dashboard or report in seconds.