Excel: Find and Replace – made simple

When it comes to tables, calculations, and formulas, Excel is often the first choice for many users. The popular spreadsheet software is included in Microsoft’s 365 package and is compatible 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 combinations. We’ll show you how this works step-by-step.

Both features are tightly connected. However, Find and Replace can be used separately and are subject to different shortcuts. The features launch within the same dialogue 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 combination 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 dialogue window.

The dialogue 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 determines 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 replacement. 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 automatically searching for it.

If you’ve just searched for a text or a number, the dialogue 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 automatically 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 particularly true for large Excel documents. The spreadsheet software offers placeholders for these situations to search for character combinations which fulfill certain criteria. We’ll show you which placeholder symbols Excel recognises.

The question mark symbolises 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 difference being that it doesn’t just display a single character but also results containing multiple characters. 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 placeholder work? In that case, you can use the swung dash. Enter it before the placeholder sign you’re looking for, for example “~?” or “~*”. You can even search for a swung dash in this way “~~”.