It can be quite confusing with Excel workbooks and it is often quite difficult to ensure that all the formulas and data are 100% accurate before you hand them over to someone else. Therefore, this week’s blog is a basic collection of check for you to do to improve precision.
Auto calculate on the Status Bar
At the bottom of the screen is a Status Bar in Excel. The 6 Total, Average, Max, Min, Count, Numerical Count functions all allow you to verify the integrity of a formula. Simply select the cell range and check your answer.
You can see for example that the value in cell G10 matches the SUM on the status bar i.e. 220.
Auditing Formula
To trace back the cells used in a formula, you can use Trace Precedents or see which cells are called dependencies.
Evaluating a Formula
This function helps you walk through a formula in the way that Excel executes its BODMAS calculations. See below.
Duplicates
It is possible to type twice in Excel in the same row of data, so any formula adding up those cells, for example, is incorrect. This issue is defined by conditional formatting.
You can see that Paul Martin’s rows are twice there for instance. This is illustrated very clearly by the pink colour in all columns.
Simply pick the Data tab and then Delete Duplicate and leave all the ticks to remove duplication.
Checking Error
In cells, there are many different errors that can occur. There’s one here. We labelled cell C25’PERC’, but by mistake it was called Percy in the formula in cell I2. On the Formulas page, the error checking command makes us aware of the problem.
