Microsoft Excel 2007 offers the Formula Auditing tool that can help you figure out the relationship of the cells in your file. Here is how to audit formulas in Excel 2007.
- Open the Excel document that you would like to audit. Load the Excel file that you need to audit on your computer screen. Once open, highlight the cell that you want to audit by clicking on it once.
- Locate the Formula Auditing tool. On the Ribbon just below the title bar, locate the ‘Formula’ tab and click once. You will be presented with different icons. These are the command buttons which you may need to perform various auditing tasks.
- Navigate around the worksheet. A worksheet packed with data can be quite a handful when auditing formulas so the first thing you need to be familiar with is how to navigate around the worksheet. The 'Watch Window' command button will pull up the ‘Watch Window’ pane wherein you can add cells to make it easier for you to monitor all of them. If you want to know what formula is used to produce the data on a cell, highlight the cell and simply click on the 'Show Formulas' command button, or hit Ctrl+` on your keyboard. If you want to add more cells to keep track off in the Watch Window, simply highlight the cell and then click on 'Add Watch'.
- Map out the flow of data. Data on a certain cell are computed from data from other cells. In a large worksheet mapping out this flow could be quite cumbersome. Use the Trace Precedents and Trace Dependents command buttons to locate and mark these associated cells. When you click on these buttons, arrows will be drawn from the highlighted cell to the associated cells. When you're through, you can simply click on the Remove Arrows command button to erase these arrows.
- Do some assessment. Another useful command is 'Error Checking' so you can locate what cell contains an incorrect formula. To do this, simply click on the dropdown arrow beside the 'Error Checking' command button and select 'Trace Error'. Selecting 'Circular References' from the Error Checking dropdown menu will pull up a dialog box with the cell addresses which contains the formulas and the cells associated with it. To assess the formula in a specific cell, click on the 'Evaluate Formula' command button.
Familiarize yourself with the various command buttons and sub-options contained in the Formula Auditing tool. You'll soon discover how easy it is to trace errors and correct faulty computations in Excel 2007.