Have you ever used the Find and Select command in Excel 2007/2010? Then you'll have found that you can find cell contents such as text or formula, and you can replace one value for another if you wish. But did you know there are some other hidden gems available here too. This article summarises how to use Find and Replace and then looks at two other very useful features, Find and Replace formatting, and Go to Special.

Find and Replace Summary

Click the Find and Select in Excel 2007/2010 and the familiar Find and Replace panel appears with the two tabs, Find and Replace. You can use either tab to find something, but clearly if you want to replace one item with another you need to use the replace tab.

Suppose, for example, your spreadsheet contains the word "Profit" in several cells and you want to change all occurrences to "Net profit". You would type in "Profit" into the Find box and "Net Profit" into the Replace box. By default Excel will look in the current worksheet, but if you click the Options button you'll see you can change this to look in the entire workbook. If you then click the Find All option, Excel will list all the occurrences at the bottom of the panel. Click any one and you're taken to that occurrence. To make a blanket change you would click Replace All, and Excel makes the changes.

Find and Replace Formatting

Suppose you want to find and replace some formatting. For an exercise, apply red font formatting to several cells in the current worksheet. To change the formatting for all red cells, choose Find and Select, then Replace. Delete any previously entered data in the Find and Replace boxes.

Then to the right of the Find box click the pop down on the Format button and choose Format from cell. A crayon icon appears on the cursor. Click once on any red cell and you are returned to the Find and Select panel. Then to the right of the Replace button click the format button. Ensure the Font tab is selected and choose any other colour and click OK. Then choose Replace All to make the changes. You can find and replace any type of cell formatting or combinations of formatting, such as shading, borders and alignment, in this way.

If you subsequently use Find and Select again, without closing Excel, the panel remembers the last settings used, so these need to be cleared before used again. To clear formats, click the pop down on the Format button and choose Clear Format.

Go to Special

If you click Find and Select and then choose Go to Special, the Go to Special panel appears. Here you can select cells which meet criteria, such as containing a formula or containing values. Suppose you're spreadsheet does contain a small table consisting of several columns of numbers and you've created a Total row under the numbers by using Autosum. Then you click Find and Select and choose Go to Special. In the panel you select Formula, leave all the boxes ticked, and click OK.

If you look very carefully you'll see that Excel has selected all the cells containing a formula of any kind, with the cells showing with a light grey shading. Just to make the cells more visible try applying a yellow shading using the paint pot on the Home tab. Now all cells containing a formula are coloured yellow.

If you return to the Go to Special panel, and this time select Constants, leave all the boxes ticked, and click OK. Now all the cells containing values, rather than formula, are highlighted. You might like to apply a different shading colour to these cells. So you can see the end result of using Go to Special so far. You can highlight and format all cells containing formula or values. This can be particularly useful if you're troubleshooting a spreadsheet, particularly if it's been created by someone else. Just note that you can only use the Go to Special panel in one worksheet at a time.

Once you've got used to using Go to Special you can use a shorter cut to show the cells. So with the panel closed again, click Find and Select. In the pop list you can choose Formulas or Constants directly. However the Go to Special does give you more control over the options.

There are some other very useful choices available in the Go to Special panel. For example if you first select a cell containing a formula, choose Go to Special and then select Precedents, All levels and click OK, Excel highlights all cells containing data which are used in the formula. Alternatively if you select a cell containing a number, not a formula, the Go to Special and choose Dependents, All Levels and click OK, Excel will highlight all cells containing formula which use the value in that cell. Using Dependents and Precedents in this way can be very useful in fault finding problem formula, for example one showing an error message.

So as well as the traditional features of Find and Replace, Find and Select also contains the very useful features within Go to Special. Using these allows you to identify and format cells containing formula and values. You can also use Go to Special to identify which cells are used in which formula and this can be particularly useful in troubleshooting problem spreadsheets. Interested in learning more about Excel? Why not consider attending a training course? There are lots available at different levels. Choose the best one and then really watch your Excel skills take off.