# Blog Archives

## Excel – Using the Formula Auditing Tools

Once you’ve created a spreadsheet or you get one from someone else, one of the more painful things you end up doing at some point is trying to work out which cells feed into any of the formulas throughout the workbook or alternatively, which cells are dependent on any given cell(s).

Finding which cells feed into a single formula is easy, just by clicking on the formula cell which then highlights all the cells that feed into it.

However, this is very limited as you can only check out one formula at a time and there is no way of knowing if any of the cells that feed into your formula are in turn dependent on other cells for their values short of checking each and every cell which of course no sane person would do.

So how can you check this without spending hours looking at all the cells in your worksheet/workbook?

This is where the **FORMULA AUDITING** tools come in. On your **FORMULAS** tab you will find the **FORMULA AUDITING** group.

In this blog I am going to concentrate on **TRACE PRECEDENTS** and **TRACE DEPENDENTS**, **ERROR CHECKING** and will write separately about **EVALUATE FORMULA**.

I will briefly mention **SHOW FORMULAS** before starting on the main topics of this blog. As the name suggests, this will convert all cells with formulas on the current worksheet from displaying their values to displaying the formulas contained within them.

You can achieve the same effect using **CTRL + `** (on an English – UK QWERTY keyboard). It’s a quick way of viewing all formulas on a worksheet without the need to view each cell separately. Can’t say any more than that!

So how do you find out which cells feed from or into another cell?

Let’s start by finding out which cells supply a formula cell with its inputs. Click on any cell containing a formula and then click on **TRACE PRECEDENTS**.

Any cell(s) that provide data to your formula should now be highlighted. But do any cells feed those before they feed into the formula cell? Simply click the **TRACE PRECEDENTS** button again.

Keep on clicking until it can go no further i.e. you have reached the original source to all the data feeding into your formula.

Now that you’ve found out all the cells that feed into your formula, is the result of your formula used anywhere else. In other words, are cells **DEPENDENT** on this one?

Staying on the same formula cell, now click on **TRACE DEPENDENTS**.

As with **TRACE PRECEDENTS** if you continue to click the **TRACE DEPENDENTS** button you will eventually get to the final cell in the chain that is influenced, however remotely, by the currently selected cell.

There will be times when you check for **PRECEDENTS** or **DEPENDENTS** and you will see this;

This tells you that data from an external source is feeding into the cell. This can mean from another worksheet in the same workbook, or from another workbook altogether. Double click the dashed line and it will open the **GO TO** dialog box.

This will show you the source of the data and if you click on the reference and click on **OK**, it will take you the feeder cell if it’s in the same workbook, or if the other workbook is open at the time.

There comes a point where there are too many lines/arrows to follow. To remove any lines/arrows click on **REMOVE ARROWS** to remove all visible lines/arrows, or select **REMOVE PRECEDENT ARROWS** or **REMOVE DEPENDENT ARROWS** depending on what you want clear off screen.

Alternatively, you can trace precedent and dependent cells by using **CTRL + [**and **CTRL + ]** respectively which simply highlights any cell(s) that either precede of follow the currently highlighted cell.

If your formulas produce error messages such as **#DIV/0!** And you have eliminated syntax errors, it could be that one of the feeder cells somewhere in the chain of cells has an error. Use **ERROR CHECKING** to find and correct those errors.

Click on **TRACE ERROR** to show all cells feeding into the formula producing the error. This works just like **TRACE PRECEDENTS** showing any external links.

For more information select **ERROR CHECKING**. This will show you where the error is and what type of error it is. You then have access to a number of options such as **EDIT IN FORMULA BAR** or **IGNORE ERROR** in case the problem is simply due to incomplete or missing data which will correct itself once it is updated.

So next time you are not sure where a formula gets its inputs from, or you need to trace an error you can now use your **AUDITING TOOLS** to help you out a bit.

## Excel – Data Validation Error Checking & Other Useful Tips

In my blog “Using Data Validation to Control Data Entry”, we looked at how to set up **DATA VALIDATION** rules, add prompts and customise alert messages.

Now, let’s look at a few other features you might want to use when working with **VALIDATION** rules;

- Finding cells that have
**DATA VALIDATION**rules applied - Checking for data that no longer meets your rules
- Preventing duplicate entries

**Finding cells that have DATA VALIDATION rules applied**

So, how do you find cells in a worksheet that have validation rules applied to them? You could click in each cell, one at a time and see if a drop down arrow appears. That’s fine as long as the validation involves a drop down list. But what if it has another type of rule or even a custom formula? No hints there!

For this, we need **Go To Special**. You can find this on the **HOME** tab under **FIND & SELECT**.

Click on **DATA VALIDATION** and any cells that have rules of any sort applied to them will be highlighted.

The grey cells are those with rules applied (the labels in this example also happen to be grey – but it should be pretty obvious when you run this)

If you like your keyboard shortcuts, you can access this by the following combination:

Ctrl + G followed by Alt + S, followed by V and then press ENTER.

**Checking for data that no longer meets your rules**

As you create your rules, naturally you will test them and make sure they work. But sometimes you may make changes to rules after data has been entered. Unfortunately, Excel will not automatically highlight data that no longer meets your new rules. If you want to check how your data is affected by a new rule and whether it breaks it you need to run **CIRCLE INVALID DATA**.

If you love your keyboard shortcuts, then you’ll need:

Alt followed by A, followed by V, followed by I.

So make a change to your rule(s) first. Continuing our timesheet example, I set up a rule that limited the comments to 150 characters. I’ve now decided that even that is too much and want to limit it to only 100 characters. I make the necessary change to my rule, but there is already some data in my worksheet. In this example there is a comment that is 126 characters long. This was fine under the old rule but breaks my new one.

Now turn on your **CIRCLE INVALID DATA**.

When you turn this on, any cells that contain data that breaks the edited rule will be highlighted by a red circle.

Unfortunately, Excel does not do this automatically for you, so you need to remember to do this after you have edited an existing rule. If applicable, make changes to the data in the cell(s) that are circled. Once they meet the new criteria, the circle will disappear.

**Preventing duplicate entries**

As well as controlling data entry through a set of simple rules you can use formulas to create complex rules with multiple conditions. One example where a formula can come in handy is preventing duplicate entries.

For this we need to use the **CUSTOM** option when creating our new rule within **DATA VALIDATION**.

In the FORMULA box enter the following formula:

=COUNTIF($A$1:$A$50,A1)<=1

What this does is check the selected range, in this example A1 to A50. Note the use of absolute references as you have to fix the range that is being checked.

*NOTE: if you are applying the rule to an existing list make sure you apply the validation rule BEYOND the end of the list otherwise as you add new records no rule will have been applied to the blank cells and therefore allowing you to potentially add duplicate values.*

The next bit A1 says look in cell A1 and check it against every cell in the selected range and count how many times it appears. This is left as a relative reference so that each cell is checked in turn when the formula is copied down the column.

The rule then states that when counted, each cell value must add up to 1 or less which means that if you add a duplicate value the count will be greater than 1 and therefore block it.

Let’s take this one step further…..

What if I want exceptions to the rule?

For that, all I need to do is modify my formula slightly….

=(COUNTIF($A$1:$A$50,A1)<=1)+(A1=“Dave”)

Essentially it is the same rule as before but with the addition of an exception. In this example I am allowing the name “Dave” to appear more than once. Note the use of brackets around the first part of the formula.

You can add further exceptions by adding more +(A1=”*text*”) on the end of the formula.

And there you have a few useful additional features from data validation!