Simon Sez IT

Online software training and video tutorials for Microsoft, Adobe & more

  • Course List
    • Adobe
      • Dreamweaver
        • Dreamweaver CC
        • Dreamweaver CS6
        • Dreamweaver CS5
        • Dreamweaver CS4
      • Flash
        • Flash CS5
      • InDesign
        • InDesign CS6
        • InDesign CS5
      • Photoshop
        • Photoshop CS6
        • Photoshop CS5
        • Adobe Photoshop CS4
      • Photoshop Elements
        • Photoshop Elements 2022
        • Photoshop Elements 2019
        • Photoshop Elements 2018
        • Photoshop Elements 15
        • Photoshop Elements 14
        • Photoshop Elements 13
        • Photoshop Elements 12
        • Photoshop Elements 11
        • Photoshop Elements 10
        • Photoshop Elements 9
        • Photoshop Elements 8
    • Microsoft
      • Access
        • Access 2021 Beginners
        • Access 2019
        • Access 2019 Advanced
        • Access 2016
        • Access 2016 Advanced
        • Access 2013
        • Access 2013 Advanced
        • Access 2010
        • Access 2010 Advanced
        • Access 2007
      • Excel
        • Data Analytics in Excel
        • Excel 2021 Advanced
        • Excel 2021 Intermediate
        • Excel 2021 Beginners
        • PivotTables for Beginners
        • Excel Dashboards
        • Advanced Formulas in Excel
        • Excel for Business Analysts
        • Advanced PivotTables
        • Power Pivot, Power Query and DAX in Excel
        • Excel 2019 Beginners (Mac)
        • Excel 2019 Beginners
        • Excel 2019 Advanced
        • Excel 2016 Beginners
        • Excel 2016 Intermediate
        • Excel 2016 Advanced
        • Excel 2013
        • Excel 2013 Advanced
        • Excel 2010 Beginners
        • Excel 2010 Advanced
        • Excel 2007
      • OneNote
        • OneNote Desktop and Windows 10
        • OneNote 2016
      • Outlook
        • Outlook 2021
        • Outlook 2019
        • Outlook 2016
        • Outlook 2013
        • Outlook 2010
        • Outlook 2007
      • Power Automate
        • Introduction to Power Automate
      • Power BI
        • Power BI
        • Power BI Intermediate
      • PowerPoint
        • PowerPoint 2021
        • PowerPoint 2019
        • PowerPoint 2016
        • PowerPoint 2013
        • PowerPoint 2010
        • PowerPoint 2007
      • Project
        • Project 2021 Beginners
        • Project for the Web
        • Project 2019
        • Project 2019 Advanced
        • Project 2016
        • Project 2016 Advanced
        • Project 2013
        • Project 2013 Advanced
        • Project 2010
        • Project 2010 Advanced
      • Publisher
        • Publisher 2013
      • SharePoint
        • SharePoint Online
        • SharePoint Foundation 2013
        • SharePoint Server 2013
        • SharePoint Foundation 2010
      • Teams
        • Microsoft Teams
      • VBA
        • Macros and VBA for Beginners
        • VBA for Excel
        • VBA Intermediate Training
      • Visio
        • Microsoft Visio 2019
        • Visio 2016
        • Visio 2013
        • Microsoft Visio 2010
      • Windows
        • Windows 11
        • Windows 10 (2020 Update)
        • Windows 10
        • Windows 8
        • Windows 7
        • Windows Vista
      • Word
        • Word 2021
        • Word 2019 Advanced
        • Word 2019
        • Word 2016
        • Word 2013
        • Word 2010
        • Word 2007
    • QuickBooks
      • QuickBooks
        • QuickBooks Desktop Pro 2022
        • QuickBooks Pro 2021
        • QuickBooks Online Advanced
        • QuickBooks Online
        • QuickBooks Canada
        • QuickBooks Pro 2020
        • QuickBooks 2019
        • QuickBooks 2018
        • QuickBooks Pro 2017
        • QuickBooks Pro 2016
        • QuickBooks Pro 2015
        • QuickBooks Pro 2014
        • QuickBooks Pro 2013
        • QuickBooks Pro 2012
        • QuickBooks Pro 2011
        • QuickBooks Pro 2010
        • QuickBooks Pro 2009
    • Web Development
      • AngularJs
        • AngularJS Crash Course
      • Dreamweaver
        • Dreamweaver CC
        • Dreamweaver CS6
        • Dreamweaver CS5
        • Dreamweaver CS4
      • Bootstrap
        • Bootstrap Framework
      • Html/CSS
        • HTML/CSS Crash Course
        • HTML5 Essentials
      • Python
        • Python Object-Oriented Programming
        • Pandas for Beginners
        • Introduction to Python
      • Java
        • Java for Beginners
      • JavaScript
        • JavaScript for Beginners
        • jQuery Crash Course
      • MySql
        • MySQL for Beginners
      • PHP
        • PHP for Beginners
        • Advanced PHP Programming
      • XML
        • XML Crash Course
    • Data Analysis
      • Financial Modeling
        • Financial Risk Management
        • Financial Forecasting and Modeling
      • Alteryx
        • Alteryx Advanced
        • Introduction to Alteryx
      • Power BI
        • Power BI Intermediate
        • Power BI
      • Qlik Sense
        • Qlik Sense Advanced
        • Qlik Sense
      • R Programming
        • R Programming
      • Tableau
        • Tableau Desktop Advanced
        • Tableau Desktop
      • Python
        • Python Object-Oriented Programming
        • Pandas for Beginners
        • Introduction to Python
    • Work Productivity
      • Google Sheets
        • Google Sheets for Beginners
      • Confluence
        • Introduction to Confluence
      • Monday
        • Getting Started in Monday.com
      • Asana
        • Asana for Employees and Managers
        • Introduction to Asana
      • Jira
        • Getting Started in Jira
  • For Business
  • About Us
    • Testimonials
    • Contact Us
    • FAQ
    • Membership
    • About Us
  • Pricing
  • Free Resources
  • Sign In
  • Get Started
Home > Microsoft Excel > How to Find and Replace in Excel? A Step-by-Step Guide

How to Find and Replace in Excel? A Step-by-Step Guide

(Note: This guide on how to Find and Replace in Excel is suitable for all Excel versions including Office 365)

Have you ever faced a situation where you have to change a particular data in multiple places? Of course, you can choose to find and replace the data manually. However, finding and changing each data one after the other might be easy when the data are less. You will need an effective way to find and replace multiple data simultaneously. 

In this article, I will tell you how to use find and replace in Excel along with its features. 

You’ll Learn:

  • What Is Find and Replace in Excel?
  • How to Find and Replace in Excel?
  • Additional Options to Find and Replace in Excel
How to Find and Replace in Excel - Worksheet
How to Find and Replace in Excel – Worksheet

Watch our video on how to use find and replace in Excel

Related Reads:

How to Calculate Factorial in Excel? Along with 2 Easy Examples

How to Graph a Function in Excel? 2 Easy Ways

Short Date Format in Excel – 3 Different Methods

What Is Find and Replace in Excel?

As its name suggests, the Find and Replace option in Excel is used to search for any particular data and replace it with the data of your choice. You can search for any number or string and replace them one after the other or replace all the data at the same time. 

Additionally, Excel’s Find and Replace option allows you to use wildcards to find any missing data only by using a part of the data. And, you can also search between worksheets or whole workbooks.

How to Find and Replace in Excel?

Now, let us see how to use the Find and Replace option in Excel along with its functionalities. 

In short, the Find option is mainly helpful when you want to find any particular data across multiple cells. The Replace option replaces the found data with any value of your choice. Using these options, you can find and replace the cells containing data across the spreadsheet or even the whole workbook.

Let us now see how the Find and Replace option works in Excel with an example. 

Consider an Excel worksheet that consists of the consolidated mark list of 20 students for 20 subjects. There happened to be a small mishap in the validation process and the students who scored 100 marks should be changed to 99. So, you have to find the cells which house the value “100” and change them to “99”. This process can be a little tedious manually. In these cases, you can use the Find and Replace in Excel.

To find and replace the values, navigate to Home. From the Find & Select dropdown, click on Replace. Or, you can use the keyboard shortcut Ctrl+H.

Select the Replace option
Select the Replace option

This opens up the Find and Replace dialog box. 

In the Find what: text box, enter the value you want to find. In the Replace with: dialog box, enter the value you want to replace the found value with. In this case, we want to search through the worksheet to find the value “100” and replace it with the value “99”.

Enter the values to find and replace
Enter the values to find and replace

Once you have entered the value, you can see two buttons to help you find the data and the other two buttons to help you replace the data. Let us see them one by one in detail.

Find Next 

This option finds the particular value and highlights them to the user one after the other.  

When you enter 100 and click on Find Next, Excel searches through the sheet and shows you the first occurrence of the particular data. When you click on the Find Next again, Excel searches for the next occurrence of the data in the worksheet. 

When just finding the data, Excel goes in loops displaying the same occurrences even after highlighting all the entries.

Find All

This option finds all the entries of the particular data and shows them to the user in an instant. 

When you click the Find All button, Excel shows you all the cells that contain the particular value. You can scroll down and click on the value to display it in the worksheet. 

Click on Find All
Click on Find All

If you want to see all the cells with the particular value at the same time, press Ctrl+A. This shows you all the entries of the particular value in the worksheet.

Excel highlights all cell with similar value
Excel highlights all cell with similar value

Note: If you want to only find the occurrences of the values without any need to replace them, just click on the Find tab in the Find and Replace dialog.

Replace

This option is used to replace the values in cells one after the other. This is particularly helpful when you have to confirm the values before replacing them. 

When you first click on the Replace button, Excel replaces the first instance of the value with the value in the Replace with: textbox and highlights the next occurrence of the value.

How to Find and Replace in Excel
How to Find and Replace in Excel

If you click on the Replace button again, the highlighted value will be replaced and the next occurrence of the value will be highlighted. 

Clicking on the Replace button replaces the values one after the other. If you don’t want to replace the particular value, click on the Find Next button. This will skip replacing the value in the cell and move on to highlight the next occurrence of the particular value.

Once you have replaced all the entries of the particular value, Excel will throw a pop-up saying “Excel couldn’t find a match”. Click OK to close the pop-up.

All the values are replaced
All the values are replaced

Note: If you only want to find and replace the values in Excel, you can straight away click on the Replace or Replace All button instead of finding the values using the Find Next and Find All buttons.

Replace All

If you feel like replacing the values one after the other is a little less effective, click on the Replace All button to replace all the values in one go.

This replaces all the values in the Find what: textbox with the value from the Replace with: text box.

Once you click on Replace All, Excel replaces all the data and throws a pop-up showing the number of replacements done. Click OK to close the pop-up.

All the values replaced
All the values replaced

Note: Always be very cautious when using the Replace All button because it changes all the occurrences of the particular value in the worksheet. If you ever feel like there might be some instances that need not be replaced, you can just use the normal Replace button to check and replace the values one after the other.

Replace AllReplaces all the cells with a particular value.
ReplaceReplaces the cell with the particular value one after the other.
Find AllThis option searches and finds the total occurrences of the particular value in a single go.
Find NextFind Next option searches and displays the occurrence of the value one after the other. 

Also Read:

Excel String Compare – 5 Easy Methods

How to Add a Secondary Axis in Excel? 2 Easy Ways

How to Wrap Text in Excel? With 6 Simple Methods

Additional Options to Find and Replace in Excel

Excel has various functionalities to Find and Replace the data in any way possible. Clicking on the Options button in the Find and Replace dialog box shows you advanced options to find and replace any data in Excel. Let us see each option and its functionalities in detail.

Click on Options
Click on Options

Format Option

This option enables you to set the format of the cells where you can search or replace the values. 

Only when the format of the cell is the same as the format specified in the Find what: textbox, Excel finds and displays the value. The same is true for the Replace with: text box. When you specify any format in the Replace with: textbox, Excel changes the format of the cell along with replacing the value.

There are two ways to set the format criteria. 

  1. Click on the Format button. This opens the Format Cells dialog box. Select the formatting of the cell and click OK.
  2. Click on the dropdown near the Format button and select Choose Format from Cell. This navigates to the worksheet and you can select any cell to copy its formatting.

You can also clear the formatting criteria from the dropdowns in the Find and Replace text boxes.

Change the Format of the cells
Change the Format of the cells

Within Dropdown

Using this option, you can change the scope of the Find and Replace option. You can either limit the functionality of the search to the current worksheet or the entire workbook.

Search Dropdown

By default, Excel searches for the value in the Find textbox in a row-wise manner. Using this option, you can assign the order of search either row-wise or column-wise.

Look in Dropdown

This option helps you specify the type of place where the find option has to search. You can choose to look for the data in formulas, comments, or cells with values. 

Match Case

When the match case option is checked, Excel searches and finds only the values that pertain to the case and format as specified in the text box. Check this option if you want the search to be case-sensitive.

Match Entire Cell Contents

When this checkbox is checked, Excel only takes into account the entire cell content. Partial data of any data type is ignored and only the complete entry in the textbox is searched.

Using Wildcards

Use the wildcards to search for any missing values. You can use the * wildcard to replace any character between the first and last characters, whereas using the ? wildcard between any two characters replaces exactly one character. Additionally, when you have to search for * or ? character, you can use the ~ tilde character along with * or ? to search for their occurrences in the worksheet.

Suggested Reads:

How to Merge Cells in Excel? 3 Easy Ways

How to Calculate Percentile in Excel? 3 Useful Formulas

How to Select Non Adjacent Cells in Excel? 5 Simple Ways

Frequently Asked Questions

How to minimize the extended dialog box without closing the Find and Replace dialog box?

To minimize the extended partition when you click on the Find All button, navigate to the corner of the dialog box and drag to the edges to hide them.

What are the advanced features of the Find and Replace option in Excel?

Clicking on the Option>>> button enables advanced options. From here, you can set the format criteria according to your needs.

How to access the find and replace option in Excel?

You can access the find and replace option by navigating to Home and under the Find & Select dropdown, click on Replace. You can also use the keyboard shortcut Ctrl+H. 

Closing Thoughts

The Find and Replace function is really helpful and offers a variety of functionalities to help you search and replace data or values in Excel. You can search and replace any data in Excel irrespective of their data type or the cell formatting.

If you find this guide helpful and are looking for more guides, please visit our Excel resources center. 

Want to skill up in Excel?

Simon Sez IT has been teaching Excel for ten years. For a low, monthly fee you can get access to 130+ IT training courses. Click here for advanced Excel courses with in-depth training modules.

Simon Calder

Chris “Simon” Calder was working as a Project Manager in IT for one of Los Angeles’ most prestigious cultural institutions, LACMA. He taught himself to use Microsoft Project from a giant textbook and hated every moment of it. Online learning was in its infancy then, but he spotted an opportunity and made an online MS Project course - the rest, as they say, is history!

Most Popular Posts

  • Kanban vs Scrum: Project Management Methodologies and Their Differences [2022]
  • How to Insert a Checkbox in Excel? 3 EASY Examples
  • How to Autofit Excel Cells? 3 Best Methods
  • XLOOKUP Google Sheets – 4 Best Alternatives!
  • Dashboards in Excel Using Pivot Tables, Pivot Charts and Slicers
  • Free Microsoft Project Training Course
  • Free Microsoft Access Tutorial for Beginners (3.5 Hours Video)
  • How to Use Blending Mode With Layers in Adobe Photoshop Elements 15

Similar Posts

Monday Project Management – 5 Important Features

Creating a New Presentation in PowerPoint 2016

How to Setup a Chart of Accounts in Quickbooks Pro 2012

Microsoft Teams Tutorial – Getting Started.

How to Create Charts and Graphs Using Excel 2010

How to Use Layers in Adobe Photoshop Elements 2019 Tutorial – Part 2

Course Categories

  • Adobe
  • Data Analysis
  • QuickBooks
  • Microsoft
  • Web Development
  • Work Productivity

About Us

  • About Us
  • Free Resources
  • Affiliates
  • Become an Instructor

Products

  • Pricing and Plans
  • Business Pricing
  • Government Discounts
  • Non-Profit Discounts

Support

  • FAQ’s
  • Contact Us
  • DVD support

Connect

YoutubeFacebookLinkedIn
© 2023 Simon Sez IT, Inc.
  • Terms
  • Privacy Policy
  • Sitemap
888.817.6665 Monday thru Friday 7:30 a.m. - 5:00 p.m. (ET)