Using Excel 2007/2010's Formula Checking Features

Fri 17th February 2012

Find out how Excel can help you determine visually which cells are used in a formula, and learn how to use alerts and formula auditing for further investigation.

Once you've used Excel 2007/2010 to create calculations using cells in the current worksheet, or in other worksheets or workbooks, you may need to fault-find specific formula which aren't producing the correct results.

This article summarises the different ways you can use Excel to audit or to show what is behind calculations to help in this process, and covers use of the Formula Bar, alerts, and Formula auditing. Identifying these features can be very useful in meeting specific user needs identified in Training Needs Analyses.

If you select an Excel cell containing a formula, you can of course look in the Formula Bar immediately above the worksheet to see the formula. You might then be able to work out what the formula is doing by just looking at it. If you click your mouse into the formula bar, you'll see that Excel colour codes the cells used in the formula. The first cell reference or range in the formula is usually coloured blue, the next green and so on. This makes it very easy to see how the formula is constructed. When you click into the formula bar the cell in question can then be edited.

So if there's an error in the formula, for example if the wrong cell range is selected, you can carefully drag the coloured marker around the cells to the correct ones, or hover over a corner of the coloured marker and resize it to cover the correct cells.

Of if a single cell reference is wrong you can drag the coloured marker to the correct cell. Once you've completed the changes you need to complete the cell edit. You can do this by clicking on the green tick to the immediate left of the Formula Bar, or pressing the Enter key and by pressing one of the arrow direction keys.

Excel has a further very useful error checking feature hidden in its innards in the form of an alert. This can be particularly useful when using formula which use cell ranges.

If for example you use a formula containing reference to a range of cells, and you only highlight some rather than all the cells in a continuous row or column range, then a green alert will appear in the cell with the formula. This indicates that Excel is questioning what you've done rather than suggesting an error. If you select the cell with the alert you'll see a yellow SmartTag appear to its left. Hover your mouse over the SmartTag and you'll see an explanation for the alert, usually that you've not selected all of the adjacent cells.

If you're happy with the formula you can hover over the SmartTag, click the down arrow and choose Ignore Error. The green alert will then turn off. If you've several cells with alerts, and you want to clear all the alerts, you can select all the cells at once, hover over the collective SmartTag and choose Ignore Error to turn off all the alerts.

The last Excel feature we'll look at in this article is Formula Auditing. If you select a cell containing a formula, then choose the Formulas tab you'll see a group of Formula Auditing commands to the right of centre in the tab. If you choose Trace Dependents you'll see that Excel draws coloured lines from the formula to the cells used in the formula. If you click Trace Dependents successively you can create further lines tracing the data flow to these other cells, and so on. Blue coloured lines show regular data flows, but red coloured lines show errors. The start of a red line represents an error source.

So if you need to create a particular training format in Excel or are drawing up a Training Needs Analysis, you can always take advantage of these very useful Excel auditing features to help troubleshoot Excel problems.

Author is a freelance copywriter. For more information on excel+training, please visit https://www.stl-training.co.uk

Original article appears here:
https://www.stl-training.co.uk/article-2049-using-excel-2007-2010-formula-checking-features.html