Excel Worksheets – checking the integrity of your worksheets

Excel Worksheets

Checking the integrity of your Excel worksheets

Excel workbooks can get quite complicated and it’s sometimes quite tricky to ensure all your formulas and data is 100% correct before you hand them over to someone else.

This week’s blog is therefore a simple set of checks for you to do to increase accuracy.

These features are all covered in more detail on our Intermediate Excel training course.

For more details please contact us on johnlegge@jplcomputer.co.uk . If you find our hints and tips helpful please leave a comment.


Auto calculate on the Status Bar

The Status Bar in Excel is at the very bottom of the screen. The 6 functions SUM, AVERAGE, MAX, MIN, COUNT, NUMERICAL COUNT all you to check the integrity of a formula. Simply select a range of cells 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.

Excel worksheets Autocalculate image

Formula Auditing

You can use Trace Precedents to trace back cells used in a formula or see what cells are called dependencies.

Excel worksheets Precedents and Dependents image

Evaluate a Formula

This feature lets you step through a formula in the way that Excel does its calculations per BODMAS. See below.

Excel worksheets Evaluating a Formula image


It is possible to type in the same row of data twice in to Excel and therefore any formula adding up for example those cells are wrong. Conditional formatting identifies this problem.

Excel worksheets Duplicates image 1

You can see for example that Paul Martin’s rows are there twice. The pink colour across all columns shows this quite clearly.

To remove duplication simply choose Data tab then Remove Duplicate and leave all the ticks.

Excel worksheets Duplicates image 2

Error Checking

There are various errors which can appear in cells. Here’s one. We’ve named cell C25 ‘PERC’ but in the formula in cell I2 called it Percy by mistake. The error checking command on the Formulas tab makes us aware of the issue.

Excel worksheets Error Checking image

If you liked this hint and tip, why not have a look at our previous one on 10 time saving tips in Excel?