As with most Microsoft Office applications Excel has a Find dialog and a Replace dialog. These make it possible to quickly find a particular piece of text or a value in a Worksheet or Worksheets.
There are two rules that apply to both of these dialog boxes and these are:
- If we only have a single cell selected they search through the entire Worksheet.
- If we have more than one cell selected, they will search only through the selected range.
The Find dialog box and Replace dialog box are very closely related. They are more often than not used in conjunction with each other. Basically, if you opt for Edit you will be able to use the Find and/or the Replace. Our recommendation is to forget about the Find box unless you are searching for text or values that reside in cell Comments. We will use cell Comments as our first example, but first we feel it is important to point out that the Look in box within the Find dialog box has three options; Formulas, Values and Comments. The Formulas and Values options can give very misleading results, so I recommend not using them. We will however, show you an alternative later.
The comments option of the Find dialog box does not have a Replace option and works like this:
- On a clean Worksheet, type the simple formula =B1+2 in cell A1.
- In A2, type the number 2.
- In A3, insert a cell Comment and type the number 2 inside it.
- In A4, type the word Cat, push the space bar and then type cat.
- In A5, insert another cell Comment and type the number 2.
- In A6, type =2+B6
- With any single cell selected, either go to Edit>Find or push Ctrl + F
- Type the number 2 in the Find What box. You will notice that there is a Search box that contains the two options By Rows or By Columns. Note - These options will have very little bearing on a Worksheet unless virtually every cell is filled with data, which is very unlikely! Even then, it will only make the difference of a second or two in the way it searches
- Have Comments selected in the Look In box, then click Find Next and Excel will take you first to cell A3.
- Click Find Next again, and you will be taken to cell A5. Note - If you keep clicking Find Next, you will toggle between A3 and A5.
- What you need to do from here should you wish to replace the number 2 within the cell Comment(s) is click Cancel and edit the comment as we discussed previously.
Let's now use the Edit>Replace dialog to replace text or values in our cells.
We should note here that clicking the Replace button will only ever replace the text or value in the current active cell. Clicking the Replace All button will replace all matching text or values on the entire Worksheet, unless we had more than one cell or a range of cells selected before we activated the Replace dialog.
Let's now assume we want to replace the number 2, but only in cell A1, where it is part of our formula.
The reason the Replace All only replaced the number 2 in our formulas was simply because we had more than one cell selected, which is telling Excel to only replace the number 2 in the selected cells only. Got any Excel Questions? Free Excel Help
Go To Free Excel Training Lesson 28 . Back to Previous Lesson
Go to Excel Basic/Level 1 Training Index
Instant Download and Money Back Guarantee on Most Software
Excel Trader Package Technical Analysis in Excel With $139.00 of FREE software!
Microsoft ® and Microsoft Excel ® are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft
Some of our more popular products are below...
Convert Excel Spreadsheets To Webpages | Trading In Excel | Construction Estimators | Finance Templates & Add-ins Bundle | Code-VBA | Smart-VBA | Print-VBA | Excel Data Manipulation & Analysis | Convert MS Office Applications To...... | Analyzer Excel | Downloader Excel | MSSQL Migration Toolkit | Monte Carlo Add-in | Excel Costing Templates