# Keep a formula close to its input

Place a formula close to its input variables. You reduce the chance of mistakes, you make optimal use of Excel’s support, and your spreadsheet becomes easier to carry over to someone else. Essentially, having formulas near their input makes your spreadsheet better.

## Optimal Use of the Support in Excel

If you modify a formula in Excel (F2), all the cells used in that formula will be highlighted for your convenience. This is great if you want to see what you are doing, but this only works if all the cells used in your formula are on the same worksheet. Ideally you would place the cells close to the formula itself, this way you will not have to scroll through your workbook to find the cells related to and referenced in your formula.

## Less Chance of Errors

The chance of making mistakes is reduced when the referenced cells and formulas are close to your calculation. This way you can visibly see which values are being referred to, and you can also visibly see the impact of changes that you make. It is also simply easier to follow the logic of a formula when you can see all its parts and there aren’t aspects of it hidden in other worksheets.

## Calculate Everything Once

What does this guideline have to do with “Calculate only once“? Quite simply, if a calculation is close to the variables, change the design to show that certain things belong together. Then, if you need the result of the same formula somewhere else and start recalculating you should quickly be able to see that this has already been done, and does not need to be repeated. This clarity you’ve given yourself will also help your colleagues understand your spreadsheets.

## Separate Input & Output?

You may be wondering, “is this not contrary to strict separation of input, calculations and output?” we have to admit, sometimes that is a bit of a problem. The clever solution is to create a new worksheet and ‘link in’ the source data by using direct references, then you can apply the calculations to the references rather than the original information. If possible, do this, as it also keeps the formulas short. Sometimes a spreadsheet is so big that it is better to leave out these kinds of solutions even though they do add some clarity. In those cases, we would prefer that you place the calculations on a separate tab and not on the same worksheet as your source data.

## Formula Location

Do you use the PerfectXL Add-in? If so you may have noticed that PerfectXL does not give a risk message for formula’s far away from the source cells. That would be too strict, we have to draw a line somewhere in what we can do for you. However, PerfectXL does warn against a specific cases related to formulas far from their sources. We call this the ‘Unexpected Formula Location’ and it occurs when, in a long formula, there are considerably more references to a different tab than there are within the tab of the formula. Then there is clearly room for improvement and you should consider moving the formula to a more appropriate location.

• Avoid >70 types of risks
• Improvement suggestions
• Quality reports

#### PerfectXL Risk Eliminator walkthrough

On this page you will find a step-by-step walkthrough of the PerfectXL Risk Eliminator.

#### PerfectXL Risk Eliminator technical information

Here you will find information like system requirements, release notes, and answers to most questions about our software.

#### Prefer INDEX & MATCH over VLOOKUP

VLOOKUP and the combination of INDEX and MATCH are well known ways to search in Excel, and to our great frustration, VLOOKUP is used a lot more often.

#### Pay attention to units and number formats

Excel horror stories are often related to accidentally changing, shifting or changing units or number formats.

#### Array functions are past times

Long ago, Excel was not as clever and advanced as today, but the so-called array functions have been around for a long time. However, most people do not know about them, and do not understand them.

#### (Hidden) circular references in your Excel file

(Hidden) circular references occur when Excel tries to compute a result of a cell that’s already been visited during the calculation round. Excel doesn’t warn us of conditional circular references.

#### Avoid long formulas

Whenever possible, keep your Excel formulas short and simple. Avoid long formulas; they are harder to read, harder to understand, difficult to check and tough to improve.

#### Never use fixed numbers in formulas

The use of fixed numbers is a bad idea. A future user will not know where the number came from, and fixed numbers don’t change automatically, and thus might be overlooked when a change is made.

#### The dangers of hidden information

Do not hide anything except some sheets. Excel has too many attractive options that look good but may be risky in the long run.

#### Calculate formulas only once

Sometimes, you need to use the exact same formula, based on the same numbers, in several other formulas. You might be tempted to calculate this formula twice, but resist the temptation. Calculate your formulas only once!

#### Do not merge cells

It is so tempting to merge cells in Excel so that they form a header above two or more columns. Yes, we must admit, it looks nice, but resist the temptation because it can be dangerous!

#### Don't neglect Excel errors

It’s a bad idea to leave standard Excel errors in your workbook. Take the time to clean up, because after a while, you no longer know whether you left a mistake consciously or if there is something wrong with your spreadsheet.

#### Risks & suggestions

To be able to trust a spreadsheet, a thorough check for errors and mistakes is essential. There are lots of different ways a simple mistake can destroy the validity of a spreadsheet.

#### Reports

Reporting is an integral part of Excel. Reports are generated in Excel, reports are built in Excel, and in many cases reports are a form of documentation required to properly use certain models in Excel.

Good, error-free spreadsheets are essential for using them reliably in business. This is why risk detection is an important aspect of spreadsheet validation.

#### Customization

Do you wish to refine PerfectXL Risk Eliminator’s analysis to cater for your specific requirements? PerfectXL allows for full customization of your user interface.

#### VBA risk detection

Excel is a powerful tool. And when it can’t do what you need, VBA can fill the gap. Unfortunately, using VBA can lead to unpredictable behaviour, slow performance, and corrupt Excel files.