Auditing Spreadsheets? – Disable Direct Editing Mode to save time [quick tip]

For most of us, the prospect of inheriting a large, undisclosed sum of money is bleak. But we have high probability of inheriting a complex Excel workbook with 19 worksheets and 2300 rows of data and 195 formulas. The kind where entire rainbow colors are used to color code accounts receivable statuses. Then what do we do? We spend a whole afternoon (and then the rest of the month) breaking our head trying to figure out why the total revenues are only $ 41.2 million when profits are $ 99.23 million.

So how do we deal with our inheritance?

Here is a quick tip to help you get started. Disable “Direct editing mode“.

What is direct editing mode?

By default Excel lets you edit a cell’s value (or formula) directly inside the cell. So if a cell contains the formula =C1+D3 and you double click on it, you will be able to edit the formula right inside the cell.

But many a times, we are only interested in knowing which cells a formula refers to.

So what happens when you disable direct editing mode?

When you double click on a cell (with formula), Excel will take you to the cells that are referred to in the formula. So in the above example, upon double click Excel selects both C1 and D3 cells.

See a demo to understand how this works:

How to Disable Direct Editing Mode?

Very simple. Click on Office Button > Excel Options > Advanced. From here, uncheck “Allow editing directly in cells” option.  This is in the “Editing Options”. See aside.

Things to keep in mind:

• This method also highlights any named ranges you have defined.
• This method also works for references in other sheets (as long as no cell in current sheet is referred)
  • For example: if a cell in Sheet1 has the formula =Sheet2!A1+Sheet2!A2 then upon double click, Excel will take you to Sheet2 and selects both A1, A2.
  • If a cell has the formula =Sheet2!A1+D3, then Excel will only select D3 (since D3 is in the current sheet).

Do you inherit Excel Workbooks? How do you audit / maintain them?

During my job as a business analyst, I used to deal with Excel files made by others all the time. So understanding and debugging others formulas is something I would do regularly.

Now as a consultant, I often get big, complex Excel workbooks and I have to understand them before doing any customizations.

My favorite techniques for dealing with inherited workbooks are,

• Using F9 key to evaluate portions of formulas
• Using CTRL+` (backquote key) to show all formulas and then go thru them
• Using formula auditing technique as discussed in this post
• Using trace dependents / precedents tools in Formula ribbon.
• Understanding and dealing with Excel formula errors

What about you? How do you deal with your inheritance? Go ahead and share techniques, shortcuts and ideas with us thru comments.

Now if you will excuse me,

I need to go. I have to reply to an email from one Mr. James Chui, an Offshore banker from Nigeria, who claims he has large quantities of undisclosed money waiting to be inherited. 😉