One of Excel's many useful features is Find and Replace. This article describes how to use this often overlooked feature which can be used in some unusual ways. So next time you want to find something in Excel - this could be text, or a formula or perhaps some cell formatting - Find and Replace will do the trick.

To use Find and Replace, it's best to start with an Excel spreadsheet with some data. We'll start by looking for cell data, which could be text or a number. To open the Find panel in Excel 2003, choose Edit, then Find. In Excel 2007 ensure the Home tab is selected, then on the far right of the navigation bar click on Find & Select, then choose Find. You can also use the shortcut CTRL+F to open the Find panel.

If you highlight some cells first, Find will search only in the highlighted cells. If you don't, Find will search all of the current worksheet.

In the Find panel you can type in the text or number you're looking for, and then click on the Find All button. Excel will list all the cells containing the data you searched for, in the lower part of the Find panel. If there several cells listed, you can click on any one, and the cursor is taken to that cell in the spreadsheet. Note that you can carry on with regular spreadsheet work whilst the Find panel is open.

With the Find panel still open, try clicking on the Options button. The panel expands to show you how you can adjust how Find works. Notice that the "Match case" and "Match entire cell contents" check boxes are not ticked. This means that the searched for text is not case sensitive, so it doesn't matter if you use capitals or not. And also, that Excel will find partial matches for your text or numbers. So if you search for "reed" for example, Excel will find cells containing "John Reed" and "breeding dogs" as both contain "reed".

You can also change where Excel looks for your data. By default Excel looks in the current worksheet, but this can be changed to the current workbook - so all worksheets will be searched. To do this click on the pop down to the left of Within, and choose Workbook. Now Excel will search through all worksheets within the current workbook - a really useful feature.

Once you've got the hang of how to use Find, you'll discover that you can look for more that just text or numbers. You can also look for formulas or part formulas, and for cell formatting. So if you need to track down where particular formulas are, for example the SUM formula, try typing in SUM as the search criteria and click Find All. Excel will list all cells with text containing "SUM" and formula containing "SUM".

Searching for cell formatting can also be very useful. Perhaps you've some cells containing red font for example. To find all these cells, ensure the Find panel is open, delete any items in the Find box, and click on the Format button in the Find panel. Choose the Font tab, select the colour you want to look for, in our example red, click OK to set the criteria, then click Find All. Excel will oblige and list all cells with red font. If you are doing this, make sure the Find box is empty as the panel does default to keeping the last search criteria you used.

The second aspect of Find and Replace is of course the Replace feature. This allows you to replace contents of formatting of one or all of the cells you found using Find. Replace is often used to carry out blanket changes through a spreadsheet. So you could, for example, replace all occurrences of the work "good" with the word "best", or replace the phrase "Word L2 course" to "Word Intermediate training" throughout highlighted cells, an entire worksheet or an entire workbook.

Another example might be to update cell formatting, say, changing pound formatting to euros formatting.

To use Replace, first carry out you chosen Find, and hopefully Excel will have listed all cells with a match. Then in the Find panel click on the Replace tab. You can then complete the Replace details as you wish. Remember this can be data such as text or numbers, and/or formatting. So you could find all cells with red font and replace the cell formatting with a double line pattern, keeping the cell contents.

To learn much more about using Excel and its many features you might consider attending a training course, and really boost your Excel skills.