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 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.

Excel worksheets Autocalculate image

You can see for example that the value in cell G10 matches the SUM on the status bar i.e. 220.

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

Duplicates

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 the rest we have on our website?

Cookie Control

Cookie control

We have placed cookies on your device to help make this website better.

I'm fine with this

We have placed cookies on your device to help make this website better.

You can use this tool to change your cookie settings. Otherwise, we’ll assume you’re OK to continue.

Some of the cookies we use are essential for the site to work.

We also use some non-essential cookies to collect information for making reports and to help us improve the site. The cookies collect information in an anonymous form.

To control third party cookies, you can also adjust your browser settings.

I'm fine with this
(One cookie will be set to store your preference)
(Ticking this sets a cookie to hide this popup if you then hit close. This will not store any personal information)
Information and Settings Cookie policy