Finding Data

By the Syncfusion DevTutorials team · Last updated

Find text and numbers, replace values safely, and replace formatting.

Big sheets can have thousands of rows. Scrolling to find one customer or one order number takes time, and fixing the same mistake in 200 cells by hand is even harder. Find and Replace can do both in seconds. In this lesson, you will find text and numbers, replace values, and replace formatting.

Open the Excel sandbox and follow along.

Find text

  1. Press Ctrl + F, or go to Home > Find & Select > Find.
  2. Type what you want to find, such as Nairobi.
  3. Click Find Next to move to the next match. Keep clicking to move through the matches.
  4. Find All lists every match, so you can click one and jump to it.

Options make searches more exact:

OptionWhat it does
Match caseNairobi will not match NAIROBI
Match entire cell contentsAnn will not match Joanna
Within: Sheet / WorkbookSearch only this sheet, or every sheet
Look in: Formulas / ValuesSearch the formulas, or the results they show

Match entire cell contents helps prevent unexpected matches. If you search for 5 without it, Excel can find cells like 15, 52, and 500.

Find numbers

Find also works for numbers. For example, search for 250 to find every cell that contains 250.

Be careful with formatted numbers. A cell that shows $1,500.00 actually stores 1500. Search for 1500 with Look in: Values, not the currency formatting.

Replace values

Replace finds one value and changes it to another.

  1. Press Ctrl + H, or go to Home > Find & Select > Replace.
  2. In Find what, type the old value, such as Nairobbi.
  3. In Replace with, type the new value, such as Nairobi.
  4. Click Replace to change one match at a time, or Replace All to change every match.

Replace All can change more cells than you expect, especially with short search text. Replacing 10 with 12 also changes 100 to 120 and 310 to 312. Turn on Match entire cell contents, or use Replace one match at a time.

Replace formatting

Replace can also change formatting:

  1. Open Replace (Ctrl + H) and click Options.
  2. Next to Find what, click Format and choose the formatting to find, such as a yellow fill.
  3. Next to Replace with, click Format and choose the new formatting, such as a green fill.
  4. Leave both text boxes empty to match any value, then click Replace All.

Replacing formatting is an Excel feature. If the sandbox's Replace dialog does not have a Format button, select the cells you want and change their formatting directly, or use Format Painter to copy the style from one cell to others.

Common mistakes

MistakeWhat happensHow to fix it
Short search text with Replace AllValues inside other values changeUndo, then use Match entire cell contents
Searching formatted text like $1,500No match is foundSearch for 1500
Find seems to miss matchesIt is searching one sheet onlySet Within to Workbook
Searching formulas instead of valuesCalculated results are not foundSet Look in to Values
Typo in Find what“We couldn’t find what you were looking for”Check the spelling, or search for part of the word

Exercise

Click Reset in the sandbox header. Type this list in A1:B6:

  • City, Orders
  • Nairobbi, 120
  • Mombasa, 85
  • Nairobbi, 95
  • Kisumu, 60
  • Nairobbi, 110

Then do each task and press Check or Mark as done.

What to remember

  • Ctrl + F finds; Ctrl + H replaces.
  • Match case and Match entire cell contents make searches more exact.
  • Search for stored numbers (1500), not formatted ones ($1,500.00).
  • Replace All is fast but can surprise you; check first, and undo if needed.
  • Excel can replace formatting with the Format buttons in Replace.

In the next lesson, you will sort data alphabetically, by number, and by several columns at once.