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. 😉
11 Responses to “Auditing Spreadsheets? – Disable Direct Editing Mode to save time [quick tip]”
fantastic! I learned not only how to disabling direct editing, but also realized (after all these years) that you can edit a cell just by double-clicking without even hitting [F2].
For those of us who are stuck with Excel 2003, go to Tools > Options > Edit and (de)select "Edit directly in cell".
I find it really useful to look at any names which have been defined in the sheet and use the 'Paste List' button in the 'Name>Paste..." dialog.
Mat
While these are all truly fantastic techniques, something to bear in mind is that the best way to learn about any system that someone has built and you have to work with is to TALK TO THEM. Granted, this is not always possible, and almost never easy to arrange. However, the benefits and time saved are so fortuitous that it is worth every second trying to hunt the 'guy-in-the-know' (or girl!) down.
With that out of the way, I just wanted to note that I've been recently digging into systems analysis, and a very good approach to figuring out how any system works is to begin by observing the inputs and outputs, then breaking down everything between into subsystems, each with their own inputs and outputs, and breaking it down further to the task level, with their inputs and outputs.
Ultimately you end up with a daisy-chain of events that are acted on by individual tasks. These tasks will be your formulas for the most part, but will include other transformational elements such as charts, pivot tables, filters, and macros.
Probably a bit much for a dinky little spreadsheet most times, but for the times that it counts, I've found that paper documentation of my understanding of a spreadsheet contributes so vastly to my understanding.
When I receive the 19 page worksheets with 2300 data lines, I would first look at the result pages and see what kind of results the readers are using. That will give you a 30K feet broad understanding of the graphs/data results.
Next I would go to Naming and see if I can comprehend why the creator define certain name. If I don't think the naming is easy to understand, I'd edit the name to something else which I can understand (if some formulae are links to outside the workbook, I will put a hold on to the name change though).
And to help me understand which page the names are from, I'd edit the existing names from "Reno" to "S1_Reno" if the data of "Reno" derive from "Sheet 1". And "QWERTY_NewProduct" from "NewProduct" because the range of "NewProduct" is from worksheet "QWERTY". It is quite difficult to remember all the 100s of names, let alone remember where the names are coming from which worksheet.
Fortunately I've never been in a position that I've needed to decipher someones elses spreadsheet (I am sure this day will soon come though...)..I have begun to get into the practice of giving my named formulas a descriptive label (a bit like what Fred has mentioned above, making reference to the sheet name in all named formulas, to make sure I know where they are being used), as well as making notes in the name manager explaining what each formula is doing....this is really for my sanity, as often after coming back to a spreadsheet in a couple of weeks I scratch my head trying to figure out why I did what I did.
I've also had to put together models for other excel users, and I find that having the first worksheet dedicated to explaining how the model works (and how to use it) helps.
A sign of an excel ninja is a spreadsheet that is self explanatory and is able to be quickly understood by any intermediate excel user....
Prem
I find Precedent and dependent tracing invaluable - I use it on my own work books as a quick double check to make sure I've referenced my formulas they way I intended. It was an amazing moment all those years ago when I found that function.
As the comments above inicate, I look for what looks like the 'end result' of the s/sheet.
So if it's a 19 page sales report for example, the end result would probably be the cell that indicates our total earnings of $99.3m, and then I work backwards, using the tracing function to see how the s/sheet comes to calculate that figure - always work backwards.
P.S. This is just my preference, but I try to stay away from over use of named ranges.
If it is a large range, or something frequently referenced, then of course. But I find named ranges to be over used and sometimes wonder if the time it has taken someone to define and name a range of 2 cells creates no efficiency.
Many Thanks
I use J-Walks Power Utility Pack (PUP) which is an Excel Addin and includes a couple of fantastic Audit Tools
It is available at: http://spreadsheetpage.com/index.php/pupv7/home
The best Audit tools are:
Workbook Link Reporting: Which reports all external links within a workbook, including those hidden in charts and named ranges
Workbook Map: Which makes a map of each worksheet and shows the cells status as Value, Text, Error or Formula as well as the formulas complexity. It makes it very simple to see where people have overwritten formulas with values
Thanks for the useful tip Chandoo.
I am a banker and regularly deal with complex financial models where I have to review each and every cell. I found the formula auditing tool by bpmTraverse to be very useful. It makes formula auditing a cakewalk and the best of all, it is free to use. You can download the tool from here: http://www.bestpracticemodelling.com/software/bpmtraverse
@kirti: Amazing... thanks for the website
hmmm....i must say this is something i find myself doing almost always at work, quite difficult sometimes especially where u feel u would have done something better than or different from what the originator of the file did.....in your attempt to do these u end up reivinventing the wheel which may be seen as a form of "new king new law", could also be a sign of inefficiency.
Hey everyone, I was just wondering, I deselected the option in excel 2003 and 2007, however I am still able to edit the contents of the cell within the cell. And this was attempted in 2 different computers. So did anyone else have this problem other than me where this feature didnt work.