Home  ›  Shortcut Key for Find and Replace Dialog Box

Shortcut Key for Find and Replace Dialog Box

Written By Friday 26 November 2021 Add Comment Edit

Find and Replace in Excel

In this article, I will be explaining the ways of using the FIND and REPLACE tools in excel.

  • Shortcut for Find in Excel is CTRL + F
  • Shortcut for Find and Replace in Excel is CTRL + H

We can find any text, number symbol, the special character not only in the active sheet but in the entire workbook as well. It does not stop there itself; we can find a particular word and REPLACE them with other words as well.

You can download this Find and Replace Excel Template here – Find and Replace Excel Template

#1 – How to use Find to Find Specific Word in Excel?

Ok, let me show you the basic find to find the specific word in the worksheet. I have two worksheets in an excel workbook one is Jan month region-wise sales, and another one is Feb month region-wise sales.

find and replace example

In the Jan sheet, I want to find the name MITCHEL.

  1. In order to find the name firstly, I need to select the worksheet and press CTRL = F, it will open up the below dialogue box.


    how to find example 3

  2. Now type the word you want to find in Find what: box.

    how to find example 4

    Note: it is not a case sensitive find; it will show you all the related finds.

  3. Now press either enter or FIND NEXT, it will show you the first found MICHEL in this worksheet.

    how to find example 5

    Now look, I was searching for Mitchel, but it has selected Daniel Mitchel for me. So FIND not necessarily looks for exact find word; it will show the result even though it is associated with other words as well

  4. Now, for sure, there are many MITCHEL in this worksheet. Instead of clicking on FIND NEXT, click on FIND ALL; it will lists up all the matching results.

    It will show you the Workbook Name, Worksheet Name, Cell, Value in the Cell, and Formula as well, if any.

    how to find example 6

#2 – How to Find the Word in the Entire Workbook?

  • Step 1: Whatever I have shown can fetch the word Mitchel from the active sheet only. If you want to find the word from the entire workbook, type the word, click on Option.
Find Word in Entire Workbook 1
  • Step 2: It will expand the current find dialogue box, select WORKBOOK from Within dropdown.
Find Word in Entire Workbook 2
  • Step 3: Now click on FIND ALL; it will show all the results.
Find Word in Entire Workbook 3

#3 – How to Find the Exact Word in the Entire Workbook?

Now we know FIND will show all the matching results. If you want to find the exact match under options, you need to select the option "Match Entire Cell Contents."

Find exact word in entire workbook 1

Click on FIND ALL; it will find the exact matches.

Find exact word in entire workbook 2

#4 – Combination of Find and Replace in Excel

A combination of FIND & REPLACE can do wonders and save tons of time on a daily basis. We can use this tool to change the formula reference.

Now, look at the below image. In cell G3, I have applied the SUM formula in excel The SUM function in excel adds the numerical values in a range of cells. Being categorized under the Math and Trigonometry function, it is entered by typing "=SUM" followed by the values to be summed. The values supplied to the function can be numbers, cell references or ranges. read more to get the total salary.

Combination of FIND & REPLACE 1

The formula range is from B2 to B10, but my data has a salary column from B2 to B22. Using FIND and REPLACE in Excel, we can change the reference.

Copy the entire formula from G3 cell and Press CTRL + H. it will open up the below dialogue box paste the copied formula to Find what:

Combination of FIND & REPLACE 2

In Replace with: change the reference as =SUM (B2:B2).

Combination of FIND & REPLACE 3

Click on REPLACE, it will change the formula from =SUM (B2:B10) to =SUM (B2:B22), and the total salary will change.

Combination of FIND & REPLACE 4

We will look at another technique with the combination of FIND and REPLACE. I have a  Product Code and price for each product in one sheet.

Combination of FIND & REPLACE 5

In the other sheet, I have Only Product code; using VLOOKUP, I need to fetch the data to this sheet.

Combination of FIND & REPLACE 6

I cannot apply VLOOKUP here because, in the main sheet, I have a product code as Prdct, but in this sheet, I have the code as Pdc, so VLOOUP cannot fetch the data here.

Use FIND and REPLACE Excel option and change the word Pdc to Prdct.

Combination of FIND & REPLACE 7

Now click on REPLACE ALL; it will replace the words Pdc to Prdct. Now we can apply the VLOOKUP formu The VLOOKUP excel function searches for a particular value and returns a corresponding match based on a unique identifier. A unique identifier is uniquely associated with all the records of the database. For instance, employee ID, student roll number, customer contact number, seller email address, etc., are unique identifiers. read more la.

#5 – FIND and REPLACE Format in Excel

We can not only Excel FIND and REPLACE words, numbers, but we can find and replace formats excel as well. No look at the below data.

FIND & REPLACE Format 1

In the above image, the department Marketing is marked in one color except the one in A4 cell. I want to change the color of the remaining marketing color to the one in cell A4.

Step 1: Select the data range.

Step 2: Press CTRL + H and click on Options.

FIND & REPLACE Format step 1

Step 3: Click on the first Format and select Choose format from the cell.

FIND & REPLACE Format step 2

Step 4: Select the format you want to replace.

FIND & REPLACE Format step 3

Step 5: Now click on the second Format and select the format you want to replace with.

FIND & REPLACE Format step 4

Step 6: Click on REPLACE ALL. It will replace the format.

FIND & REPLACE Format step 5

#6 – How to FIND Comments in Excel?

We have come to the final part of the article. We can find the comments as well. Follow the below steps to find comments in excel In Excel, Insert Comment is a feature used to share tips or details with different users working within the same spreadsheet. You can either right-click on the required cell, click on "Insert Comment" & type the comment, use the shortcut key, i.e., Shift+F2, or click on the Review Tab & select "New Comment". read more .

FIND Comments step 1

I have comments in column D, and comments are No commission, Commission @5%, and Commission @ 10%.

  • Step 1: Open Find dialogue box and click on Options
  • Step 2: Under Look in select Comments.
FIND Comments step 2
  • Step 3: Now, mention what comment you are searching for.
FIND Comments step 3
  • Step 4: Click on Find All it will show all the cells which have a comment No commission.
FIND Comments step 4

Things to Remember

  • If you are searching in a large file, it is good to select a particular row or column.
  • If you are replacing something, always select the replacing region.
  • You can replace the existing format with your own format as well.
  • If you are searching for something which is not there, then excel will show the dialogue box as we couldn't find what you were looking for.
  • You cannot replace it if the worksheet is protected.

Recommended Articles

This has been a guide to Find and Replace in Excel. Here we discuss how to Find and Replace in Excel using shortcut keys along with practical examples and downloadable excel templates. You may also look at these useful functions in excel –

  • How to Find and Select in Excel?
  • FIND VBA Function
  • Use FIND Function
  • Not Equal to in Excel
  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion

LEARN MORE >>

Shortcut Key for Find and Replace Dialog Box

Source: https://www.wallstreetmojo.com/excel-find-and-replace/

0 Response to "Shortcut Key for Find and Replace Dialog Box"

Post a Comment

Subscribe to: Post Comments (Atom)

Iklan Atas Artikel

Iklan Tengah Artikel 1

Iklan Tengah Artikel 2

Iklan Bawah Artikel