Green Marks Part 3: More error handling

This is a continuation of the series on error handling – green marks. We will see how to handle Excel formula errors and two Text related (non-formula) errors in this article.

Please read these two articles before reading this article to maintain continuity.
Auditors, Risk Managers, Everyone: Did you know? These green marks are WARNINGS!!Green Marks Part 2: Formulas showing wrong results!

Formula Errors

These are the errors all of us are familiar with. #DIV/0, #VALUE, etc. These are already covered in detail on may sites. Therefore, I am not going to cover it again. Here is the Chandoo.org article on this topic.

When you click on the icon in a cell containing an error, it asks you if you want to Show Calculation Steps. This is a very powerful way of debugging the formula step by step. We will cover it in some future article. It is like having a slow motion action replay of every component of your formula going across related dependents, sheets and files.

There are two more useful things you should know about formula debugging

Trace Precedents line color

Let us say you got a #DIV/0 error somewhere. But the data and related formulas are far away. If you use Trace Precedents, it does show you color coded arrows… blue are the branches with no error and red lines are those where there is error.

This is nice. But not as good as you may want. Because you have to TRACE exactly which step is creating the problem. Double clicking on the RED line will take you to the next precedent. But that may not be the place where the actual error originated. You may (and usually will) have to trace it further. This is cumbersome (but possible).

There is a better way – as always.

Trace Error

Click inside the cell containing the formula error. Go to Formula Tab – DO NOT click on Trace Precedents. Open the dropdown of Error Checking and choose Trace Error.

Now, assuming the error generating cell is in the same sheet, Excel will trace the entire path and place your cursor in the cell which ACTUALLY GENERATED THE ERROR.

Obviously, this cell will have the Precedent arrow blue because there is no error BEFORE this cell. This can be an invaluable time saver while troubleshooting errors in complex files.

Dates with two digit years

This problem was relevant during the Y2K issue but continues to be important even today. Often data exported programmatically or reports copy pasted from system reports do not provide dates which are properly understood by Excel.

Excel puts the green mark with a different error this time, with option to convert the text date to 20^th or 21^st century.

If data is within the last 14 years, you will choose 20xx otherwise you will have to be careful. This type of error allows you to do bulk correction by selecting multiple cells containing the same error.

    

But there is another danger lurking here. Notice that I have made the column width more than required to show the dates. This shows that the dates are still left aligned, even after the conversion to 20xx format. What does that mean? The dates are still being considered as text by Excel. Therefore, you will need to convert these to proper dates after the error handling is done. How? Usually using Text to Columns or now using Power Query. But that is off-topic for this article.

Selecting multiple cells containing this type of error may be cumbersome if these cells are scattered or you have multiple blocks. We will cover the solution in later in this article.

Number stored as text

This is a very common error. Usually happens when data is imported from text files, dumped from programmatically generated reports, or copy pasted from web based reports. Options are simple – Convert to Number or Ignore Error (keep it as text)

The solution depends upon context. If the numbers need to be used in numeric calculations you will choose Convert to Number.

If the numbers are just identifiers, especially with padded zeros which are significant, you will want to ignore errors. This typically happens for Account Numbers, Customer ID, Product ID, etc. where there are padded zeros in front of the number and those zeros are required for matching with other data. There is no numeric significance of the number. It is just a unique identifier.

How to handle multiple errors in a single bulk operation?

The date and number stored as text errors are not formula related errors. Therefore, once you decide either this way (correct the problem) or that way (ignore error), it usually applies to all such values.

Now the issue is – how to select all these cells and apply this operation in one stroke.
Look at this data.

We have three independent blocks of data having the problem of Number stored as text. The cursor is inside cell D6, which contains that error. Therefore the icon which allows us to correct the error is available. Clicking on it and choosing Convert to Number will solve the problem only for D6.

We want to do a bulk select. Either one block at a time or entire worksheet. Let us try one block at a time. When you select the block, the correction icon disappears because now the current cell is D4. This cell does not have any error. Therefore the correction icon is not available.

That is a problem. Now we cannot do bulk correction for the entire block. Let us try selecting the entire sheet. Now the problem remains the same. The current cell becomes A2 in this case (top left visible cell). A2 does not have any error. Therefore the correction icon is missing – as expected.

So let us restate our problem. We want to be able to select the entire sheet while ensuring that the current cell (Active Cell) contains an error which we want to correct.

Here is how it is done. Click in any cell containing the error. I am clicking inside D6. Now press CTRL A. It selects the contiguous block. But it does not move the active cell to the top left cell in the block. The active cell remains D6. Therefore, the correction icon is still available. So far so good.

But we don’t want to do it repeatedly for individual blocks. We still want to select the whole sheet and perform a single step correction of all errors.

No worries!  Press     again.
This selects the entire sheet while retaining the active cell at D6.
The correction icon moves to the top left cell – which is good – easy to find it.

Now click on the icon and choose Convert to Number (in this case) or Ignore Error. This is how you perform bulk corrections of large number of errors.

If you get data dumps where individual columns have this error, it is a good idea to correct the problem one column at a time because in some columns there may be numbers (which you want to convert to actual numbers) and some may contain IDs (which you want to retain as text).

Remember that if the data was in tables, there are three CTRL A keystrokes required. FIRST CTRL A selects the data area. SECOND Ctrl A selects the Entire Table (including the header) and the THIRD CTRL A selects the entire sheet. All this happens without disturbing the current cell.

Progress so far

What next?

Our green mark series continues. In the next article, we will see other errors are handled.

***