Avoiding another Find mess

I’ve recently returned from an international Excel lecture tour, my first since the Covid pandemic paused all my in-person lectures. One of the best things about lectures and similar events, whether in-person or virtual, is the challenge of answering audience questions. The popularity of this, from the point of view both of the lecturer and the audience, has been evident in the reaction to the Excel Community Excel tips and tricks live - your questions answered webinars. The next webinar in the series is scheduled for June 7th: https://events.icaew.com/pd/27165.

On the tour, one of the questions asked in Frankfurt related to Excel’s Find feature. It was a very straightforward question but one which demonstrated how much of Excel lies hidden beneath the surface of the user interface. One of the delegates frequently had to find all occurrences of a particular piece of text somewhere in a workbook. They had got as far as using the Find dialog with its default settings, but this required going to each sheet in the workbook separately and running the Find command. The Find command can be found in the Home Ribbon tab, Editing group. The Find command itself is in the Find and Select dropdown. A quicker way to open the Find dialog is to use the Control+F keyboard shortcut:

The key to gaining more control over the Find operation is to click on the Options button in the dialog. This allows you to fine tune how Find works. You can choose whether to search within a sheet or the whole workbook; whether to search across each row and then down to the next row (By Rows) or to search each column and then across to the next column (By Columns) and to choose whether, for cells containing a formula, to search within the formula itself or in the value it returns. You can also choose to look in Notes and Comments.

Additionally, there are check boxes to make the search case sensitive and to only match cells that just contain the search term:

So, for our delegate, the answer was to click on the Options button and choose Workbook from the ‘Within’ dropdown. The Find All button would then select all the entries, throughout the workbook, that matched:

Similarly, the Previous and Next buttons would also work across all the sheets in the workbook.

Note also that the Options button causes the dialog to display options relating to formatting. The Format dropdown allows you to choose any formatting from the cell formatting dialog to search for and select, or to set the format to be searched for to match the format of a chosen cell. The ‘Find what:’ box contents and the Format search work together, so, to find cells formatted in a particular way, irrespective of cell contents, you would need to delete any entry in the ‘Find what:’ box. The Format dropdown includes a Clear Find Format option which becomes active when a format has been set and allows the format that has been set to be cleared.

It’s important to remember that if you set any of the options, then click on the Options button to just display the simple Find dialog, all the options set will remain in operation. This can cause Find to appear to operate unexpectedly. If simple Find isn’t doing what you think it should, perhaps the first thing to do would be to click on the Options button to see whether any optional settings remain in operation.

These additional options are also available in the Replace tab of the Find/Replace dialog and include the ability to replace formatting as well as cell contents:

At first glance, the Excel Find and Replace feature might appear to just allow a value to be located and possibly replaced with another value, but an examination of the full set of Options available shows that it has much more comprehensive features available, including the ability to Find and Replace cell formats as well as contents.avoid another find mess