Check Formulas in Excel

Small formula errors can have big consequences. A misplaced comma or the wrong reference can severely warp your results. Excel has several tools to check your formulas for errors.

F2 – See formulas in Excel.

To check the formula of a specific cell, select the cell, then hit F2 on your keyboard. The cells referenced in the formula will be highlighted, and you will be able to edit the formula without a mouse click.

Control + ~ To show all Formulas

show_formulasSelect any cell in your spreadsheet and on your keyboard hit Control and the Tilde ( ~ ) sign at the same time. This will show all of the formulas in the worksheet. Toggle back to the normal view by hitting Control + ~ again.

You can also access this tool in the Formula Auditing group under the Formula tab. Toggle Show Formulas on and off to see the formulas in the workbook.

Trace Precedents and Trace Dependents

In Excel, a precedent is a cell being reference in a formula. If a formula is =C6+7 then C6 is a precedent. A dependent is a cell that depends on the data from another cell for its content, so if the formula above were typed into cell C9, then C9 would be a dependent of C6.

excel_precedentsTo display precedents, select the cell with the formula and click on the Formula tab. Then choose Trace Precedents in the Formula Auditing group. Arrow will appear showing which cells are referenced in the formula. Toggle this off by clicking on Remove Arrows in the same group.

Select a cell that is referenced in the formulas of other cells. Under the Formulas tab, in the Formula Auditing Group, click on Trace Dependents. Arrows will appear showing the cells that depend on that cell.

excel_dependents

 

 

 

 

For more excel techniques see our Excel Training Class.