• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

Excelypedia

The Excel Encyclopedia of The Internet

  • Tutorials
  • Functions
  • Formulas
  • Templates

Find and Replace in Excel

June 18, 2021 by Andrew Childress
Find and replace in Excel

You might find yourself wanting to change one word, number, or phrase for another inside Excel. Here's how to use Find and Replace in Excel.

You might find yourself wanting to change one word, number, or phrase for another inside a spreadsheet. Of course, this could be done manually, but that would be difficult if you need to make many changes. Fortunately, Microsoft Excel has a handy feature to do the work automatically. Let’s learn how to use Find and Replace in Excel.

How to Use Find and Replace in Microsoft Excel

Consider a list of dates containing perhaps hundreds or thousands of rows. You’ve been sent the data, and you’ve spotted a problem.  The list is supposed to show dates for the current year. But instead of 2021, it says 2012. Are you now facing the daunting task of having to manually re-type and correct each year?

Fortunately, not at all! Excel has a handy feature built in, called Find & Replace. As the name suggests, it’s a two-step process. It first finds a given word, number, or phrase. Then, it allows you to input a replacement value.

Once you run it, the original value is replaced with the new. It’s especially useful in two cases: when you’re making bulk edits, and when you’re correcting values that Spell Check cannot identify. In this example, both of those conditions are true.

To get started using Find & Replace to clean up your data, begin by navigating to the Home tab on Excel’s ribbon. This is typically the starting point for many features in Excel. On the right side of the menu, in the upper right corner of your screen, locate the Find & Select dropdown.

Click here. Now, Excel’s standalone Find feature is your first option. This is essentially a search function that locates – but doesn’t replace – a given value anywhere in your spreadsheet. It’s useful as a standalone feature, but here, you’ll want to click on the second option: Replace.

Find and replace in Excel

Excel then launches the Replace menu. As you can see, you have several options here. Let’s work through them in order. Find What is where you’ll input the value that you want to search for and replace. Here, of course, that’s the year: 2012. Go ahead and type it into the white dialogue box.

Then, you can specify Excel’s exact search behavior. For example, you can search within the displayed sheet, or across multiple sheets to span your entire workbook. Additionally, you can determine whether to match case. This is most useful when searching for text. Plus, you can opt to find entire cells only.

Next up comes the true power of Excel’s Replace feature. Here, you’re telling the app what to do when it locates the Find value. Remember, you want any and every occurrence of 2012 to be replaced with 2021. With this in mind, type 2021 in the Replace With box at the bottom.

How to find and replace in Microsoft Excel

Once again, you have choices. You can opt to Replace, or Replace All. Let’s think about the difference there. In our example, we want to replace every last occurrence of 2012 with 2021. Replace All is the best bet here. Clicking on it will instantly replace the old value with the new. Excel performs the analysis automatically and performs the changes – not a single keystroke is required by you!

Use find and replace in Excel

Replace gives you more control. It’s useful if you only want to replace some of the values you’ve found, but not all of them. Whichever option you choose, Excel makes it easy to make bulk replacements in even the largest datasets. This is the true power of Microsoft Excel spreadsheets. With a few quick clicks, you can perform the equivalent of many hours of tedious work almost instantly.

Category: Excel FunctionsTag: Excel, How To, Instructions, Tutorial

About Andrew Childress

Previous Post:Hlookups in ExcelHLOOKUP in Excel
Next Post:Break Links in ExcelBreak links in Excel

Sidebar

Recent Posts

  • How to Do a Contingency Table in Excel
  • Format Cell Alignment in Excel
  • Default Width in Excel
  • Translate in Excel
  • Switch Windows in Excel

Categories

  • Basics
  • Examples
  • Excel Formulas
  • Excel Functions
  • Excel Shortcuts
  • Excel Templates
  • Excel Tutorials
  • Uncategorized

Excelypedia

Master Excel to add a highly valuable asset to your employability portfolio. Learn how to analyze data in Excel.

    • Facebook
  • Twitter
  • Instagram
  • YouTube

Learn

  • Functions
  • Formulas
  • Shortcuts

Legal

  • Privacy Policy
  • Cookie Policy
  • Terms & Conditions

Help

  • FAQ
  • Contact Us

Copyright © 2023 · Excelypedia · All Rights Reserved

Back to top