Excel: Find and Replace – made simple

When it comes to tables, cal­cu­la­tions, and formulas, Excel is often the first choice for many users. The popular spread­sheet software is included in Microsoft’s 365 package and is com­pat­i­ble with Windows and macOS operating systems. It’s an ideal solution to help bring structure to figures and values, but the more data included in a document, the more confusing it can get. That’s where the “Find” and “Replace” functions prove useful. The feature allows you to quickly search for and replace letters, words, and number com­bi­na­tions. We’ll show you how this works step-by-step.

Both features are tightly connected. However, Find and Replace can be used sep­a­rate­ly and are subject to different shortcuts. The features launch within the same dialog window but are located on different tabs. Find out how to launch and use the features.

If you’re working on a large Excel data table with multiple entries and need to find a unique number or letter com­bi­na­tion quickly without having to manually scan your document, the Find feature is the ideal tool. To launch it, choose “Find & Select” from the header dashboard. From the dropdown menu, select “Find” to launch the matching dialog window.

The dialog window will expand and launch various options: the dropdown menu next to “Find” allows you to define the cell area in which to search. If you select “Sheet,” Excel will only search for the specified terms within the current Excel sheet, while “Workbook” expands the search to all sheets within a Excel document. You can also search for terms by row or column. Selecting these will also affect the order of your search results. The feature de­ter­mines the direction in which a search will take place in – from the position of your current active cell. Besides “Search by…”, Excel lets you “Look in…” formulas, values, notes, and comments.

When working with large data sets, it can be hard to spot values that you already know will require re­place­ment. On some occasions, you may need to replace multiple values at the same time. That’s where the Replace feature can prove useful. Using this tool, Excel will replace the specified text or number after au­to­mat­i­cal­ly searching for it.

If you’ve just searched for a text or a number, the dialog window for Replace will already be opened (second tab). Otherwise, choose “Replace…” from the “Find & Select” option from the header dashboard.

If you’re certain that you’d like to replace all cells of a certain value, click “Replace All.” A window will pop up au­to­mat­i­cal­ly and show the number of cells that have been replaced.

Sometimes you may not know which values or results you’re looking for – that’s par­tic­u­lar­ly true for large Excel documents. The spread­sheet software offers place­hold­ers for these sit­u­a­tions to search for character com­bi­na­tions which fulfill certain criteria. We’ll show you which place­hold­er symbols Excel rec­og­nizes.

The question mark sym­bol­izes an unknown character. Let’s assume you’re searching for “y?s”, Excel would list results such as “yes”.

The star sign is similar to the question mark with the main dif­fer­ence being that it doesn’t just display a single character but also results con­tain­ing multiple char­ac­ters. If you search for “y*s”, Excel could list results such as “eyestalk” or “yes”. If you require a list of all words that begin on a certain letter, you could search for “S*”, for example. All words starting on “S” would be selected.

But how does a search for a place­hold­er work? In that case, you can use the swung dash. Enter it before the place­hold­er sign you’re looking for, for example “~?” or “~*”. You can even search for a swung dash in this way “~~”.