What do you look for when checking a spreadsheet?

Given most of us use Excel to work with numbers, many analysts do not realise the importance of checking their work. For spreadsheets to be accurate, attention must be paid to detail.

End-users become uneasy when you review reports and see errors such as #DIV/0!, #N/A and so on. It is easy to find them, if possibly a little laborious when first starting out. Here are some tips: Use Excel’s Background Error Checking.

Strictly speaking, this should be instigated during the entire model development phase as it can assist the analyst throughout construction.

To enable this functionality, go to Excel’s options (File -> Options, or Alt + F + T) and in the ‘Formulas’ section, ensure that the ‘Enable background error checking’ tick box is checked. Once activated, the user can select which error checking rules should be catered for by inspecting the ‘Error checking rules’ section directly beneath this checkbox.

This functionality does not prevent errors from occurring, but potentially erroneous cells are highlighted by Excel in a fashion similar to cells that include comments:

The problem with this approach is it is easy to miss this annotation, but it is better than doing nothing.

Use Excel’s formula auditing tools

In the ‘Formulas’ tab of the ribbon, use the tools in the ‘Formula Auditing’ section of the toolbar. In particular, ‘Error Checking’ is useful (although it may only be applied to one worksheet at a time) as it highlights a lot of issues Excel is programmed to consider as “dubious” (for example, inconsistent formulas, #DIV/0! errors, and so on).

For those lucky enough to have the Professional Plus version of Excel 2019 or the Pro Plus version of Office 365, Spreadsheet Inquire adds to Excel’s in-built functionalities to allow users to analyse the links between workbooks, worksheets and / or individual cells:

This can help clean excess cell formatting and assist in understanding the relationships between cells and/or worksheets.

Find prima facie errors

There are glitches in Excel and occasionally, a prima facie error may slip through. These obvious errors are particularly embarrassing to miss, as these are usually identified by end-users in just a matter of seconds after a model has been handed over.

There is a simple sure-fire check: Ctrl + F (select ‘Find…’ from the drop-down list in ‘Find & Select’ in the ‘Editing’ grouping of the ‘Home’ tab of the ribbon).

Simply type ‘#’ in ‘Find what’ (the obvious errors all begin with ‘#’), but then click on the ‘Options’ button to display the options and change the ‘Within’ setting to ‘Workbook’ and then look at ‘Formulas’, ‘Values’ and ‘Comments’ in turn using the ‘Find All’ button to correct any issues identified:

Review inconsistencies in formulas

 Sometimes, data is in the form of formulas. Imagine the following represents a block of formulae:

Let’s assume this data is supposed to refer to a similar block of data elsewhere. How can we tell if the formula has been copied across and down correctly? Inspection by eye achieves nothing here.

One option is to use the keyboard shortcut Ctrl + ` (the character is the key to the left of the 1 on a standard US QWERTY keyboard):

Instead, there is a simpler, automatic approach. Select all of the data (click anywhere in the range and press Ctrl + A). Then use the keyboard shortcut Ctrl + \ which may reveal the following:

This automatically selects all of the cells whose contents are different from the comparison cell in each row (for each row, the comparison cell is in the same column as the active cell).

Ctrl + Shift + \ selects all cells whose contents are different from the comparison cell in each column (for each column, the comparison cell is in the same row as the active cell). In this example, where a formula is supposed to be copied across and down, there will be no difference.

These cells can now be highlighted and reviewed at leisure.

Create “quick” charts

For key outputs, you can graph the data momentarily. Simply highlight the data and press the F11 function key to create a chart on its own sheet or Alt + F1 to create a chart on the current worksheet.

Once the chart has been created, consider whether the chart(s) make sense: are there unseemly ‘blips’ or inconsistent trends? Can dramatic changes be readily explained? These rough and ready charts can highlight erroneous data in an instant.

Close and re-open

Do you get unexpected error messages upon opening? This is a frequent oversight made by modellers. Are calculations set to ‘Automatic’? Are there any unexpected links, circular arguments or other error messages (such as “Not enough memory to display”)? It is better that you discover these issues before your users do.

Highlight errors

If you would prefer, there are two ways you can highlight errors in data in Excel too:

1. Using conditional formatting

Select the entire data set, and go to Home –> Conditional Formatting –> New Rule. In the ‘New Formatting Rule’ dialog box, select ‘Format Only Cells that Contain’, then, in the ‘Rule Description’, select ‘Errors’ from the drop down:

Set the format as required and click ‘OK’. This will highlight any error values in the selected dataset.

 Select the entire data set and press F5 (or Ctrl + G, this opens the ‘Go To’ dialog box). Click on the ‘Special…’ button in the bottom left-hand corner:

Select ‘Formulas’ and uncheck all options except ‘Errors’:

This selects all the cells that contain errors. Now you can manually highlight these, delete them or edit them in some other way.

Spell check

Nothing lowers the credibility of your work more than a spelling mis-steak. These are so simple to avoid in Excel: simply highlight all worksheet tabs and then select ‘Spelling’ from the ‘Proofing’ section of the ‘Review’ tab of the ribbon:

You can also use the keyboard shortcut F7 to perform the same task.

In the next article in this series, we will look at how to solve common spreadsheet data issues.

/en/excelformulas/solving-reallife-problems-in-excel/content/

Double-check your formulas

One of the most powerful features of Excel is the ability to create formulas. You can use formulas to calculate new values, analyze data, and much more. But formulas also have a downside: If you make even a small mistake when typing a formula, it can give an incorrect result.

To make matters worse, your spreadsheet will not always tell you if a formula is wrong. It will usually just go ahead and run the calculations and give you the wrong answer. It's up to you to double-check your formulas whenever you create them.

We've put together a list of tips you can use to help check formulas for accuracy. These tips won't help you solve every problem you encounter, but they should provide you with the tools to identify many common errors.

Check the references

Most formulas use at least one cell reference. When you double-click a formula, it will highlight all of the referenced cells. You can then double-check each one to make sure they are correct.

Look for mix-ups

A common mistake is to use the correct cell references, but in the wrong order. For example, if you want to subtract C2 from C3, the formula should be =C3-C2, not =C2-C3.

Break it up

If a formula is too complicated to check, try breaking it up into several smaller formulas. This way, you can check each formula for accuracy, and if there are any problems you will know exactly where they are.

Ballpark it

You can use your own experience, critical-thinking skills, and common sense to estimate what the answer should be. If Excel gives you a much larger or smaller value than expected, there may be a problem with your formula (or with the values in the cells).

For example, if you are calculating the total price of 8 items that are 98 cents each, the answer should be slightly less than $8. In the example below, the formula calculated the answer as $784.00, which is incorrect. That's because the price in A2 was entered as 98, and it should have been 0.98. As you can see, even the smallest details can make a huge difference.

Note that this tip does not always work. In some cases, the wrong answer may be fairly close to the correct answer. However, in many situations it can help you quickly catch a problem in your formula.

Check the arguments

If you're using a function, make sure each required argument is included. A small dialog box should appear as you're typing the function to let you know what arguments are needed.

This can be especially useful when you're trying to fix a function that's not working correctly. For example, let's look at the function below:

In the example above, the NETWORKDAYS function is returning an error. If we enter the NETWORKDAYS function into a new cell, the reason becomes clear:

The NETWORKDAYS function requires at least two arguments—a start date and an end date. Our original function only has one argument, so we'll edit our function to include both arguments:

Now our function is working correctly!

Walk through the order of operations

Remember the order of operations from math class? If not (or if you want a refresher), you can check out our Complex Formulas lesson. Your spreadsheet will always use this ordering, which means it doesn't just calculate a formula from left to right. In the example below, the multiplication is calculated first, which isn't what we wanted. We could fix this formula by enclosing D2+D3 in parentheses.

Switch to formula view

If you have a lot of formulas and functions in your spreadsheet, you may want to switch to formula view to see all of them at the same time. Just hold the Ctrl key and press ` (grave accent). The grave accent key is usually located in the upper-left corner of the keyboard. Press Ctrl+` again to switch back to normal view.

Remember, it takes a lot of practice to master writing formulas. Even the most experienced spreadsheet users encounter formula errors. If your formula doesn't work or produces an incorrect value, don't panic! More often than not, there's probably a simple reason your formula resulted in an error—once you find it, you'll be able to get your formula working correctly.

/en/excelformulas/understanding-number-formats/content/