What do you do if your Excel spreadsheet suddenly shows an error message in a cell with a formula? You might look at the formula in the edit bar and try and work out what might be causing the problem, or you might try to work out where the data in the formula comes from - this can be particularly difficult if you've long forgotten what the formula was used for or if the spreadsheet was created by someone else.

There is another way to fault find your formula error messages - Excel's hidden gem, the Formula Auditing feature. In Excel 2007 it's in the Formulas tab, in the Formula Auditing group; in previous Excel versions you need to choose Tools, Formula Auditing, Show Formula Auditing toolbar.

Formula Auditing lets you visually trace data in two different ways. Trace Precedents lets you check data back from the formula, so you're tracing data which precedes the formula. Trace Dependants lets you check where data in a cell is used in subsequent formula, so you're tracing which cells depend on the selected cell.

We'll look at Trace Precedents first. If you select a cell containing a formula, you can then visually trace which cells feed data to the formula by clicking Trace Precedents. If you click Trace Precedents successively, you'll see one or more blue or red lines appear on the spreadsheet, linking the cell with the formula back to the cells containing the data used in the formula.

If you have several formulas showing error messages you can then select the next formula and click Trace Precedents successfully to create trace lines for that formula too, so you can quickly build up sets of blue or red lines showing data flow in your spreadsheet.

A blue line indicates normal data flow, but a red line indicates an error flow. If you don't see any red lines, then the error has been created in the formula itself, whereas if you do see one or more red lines, the error was incoming, and the red line shows where from (usually from another formula). Trace Precedents looks backwards from the formula in the selected cell and can only be used from a cell containing a formula.

Next we'll look at Trace Dependants. If you select a cell containing data rather then a formula, you can visually trace where that cell data goes by clicking Trace Dependants. If you click Trace Dependants successively, you'll see blue lines on the spreadsheet linking the cell forwards to subsequent cells to show the data flow from the first cell. Trace Dependants looks forward from the selected cell.

If you want to, you can select different data cells in turn, and for each, click Trace Dependants successfully to build up a visual diagram of how data is used in your spreadsheet.

If you do end up with blue and red data lines all over your spreadsheet looking like spaghetti, you can click on Remove Arrows to clear all lines, and start again.

So Trace Precedents and Trace Dependants tend to be very useful first steps in establishing data flow from and to a problem formula. Don't be mislead by the absence of red lines by the way. Excel doesn't mind whether a cell contains numbers or text or a zero or nothing at all. It's what you do with the data that matters. So incorrect data type does not generate a red line - it's what you do with the data where the errors can occur.

Suppose your spreadsheet contains several formula and perhaps several error messages. This can happen if one formula containing an error is used in a subsequent formula, or there may be separate unrelated formula errors. You can take formula auditing further by having Excel successively select each formula in turn containing errors. You can then do your tracing from each, and then move on to the next.

To do this select the rightmost and lowest formula in your spreadsheet. Then in Formula Auditing click Error Checking. Excel then selects one of the cells containing an error. In the Error Checking panel click the Next button and Excel move the selection to the next cell containing an error. Clicking Next successively lets Excel select each error cell in turn until the entire spreadsheet has been checked. So this way you can have Excel automatically select each error cell in turn.

So if you have encountered multiple error cells, select the first Excel identified, trace the data and then move on to the next if appropriate and continue tracing. This way you can logically work through the data paths for all the error cells and hopefully find the problems.

Formula Auditing is just one of many useful features under Excel's surface. To really get to grips with Excel, consider attending one of the many excellent training courses available. This can be the best way to increase your Excel skills in a short time.