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.
Formula Auditing
You can use Trace Precedents to trace back cells used in a formula or see what cells are called dependencies.
Evaluate a Formula
This feature lets you step through a formula in the way that Excel does its calculations per BODMAS. See below.
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.
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.
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.
If you liked this hint and tip, why not have a look at our previous one on 10 time saving tips in Excel?