How to Deal With Somebody Else's Excel Workbook

No matter how closely you follow your religion's prescriptions for righteous life sooner or later the life will serve you the task of dealing with an Excel model made by somebody else. The clash with a different design style, approach of problem solving and sheet organization is definitely not joyful pleasant experience. However, over the time I developed sort of a methodology to follow in this process to deliver fast result with minimum efforts and keeps my sanity intact.


The tasks related to transferring an Excel workbook are maintenance, building new functionality, fixing problems or any linear combination of these. No matter why it is on your screen, the steps to cover are listed below. 

Clear the purpose and the users
This step is usually overlooked for the expense of what needs to be done. However, more efforts have to be spent on clarification of the current requirements for the workbook, who uses it and how as well its place in the business process in the organization. This is a crucial step not only for understanding the problems at hand and the general context but also to finding the best solution of these problems. Sometimes we are so focused on fixing something that we cannot see a better and faster solution. People tend to focus on a specific problem rather than putting things in perspective so help them with that. 

Clear the logic and applied calculation methods
Even before opening the file, ask about the business logic, rules and calculation methods built in the worksheet. Even if there is very limited information on these it is still better than nothing and you would have some leads. Be careful though - some information can be misleading. Once the logic is cleared the technicalities are easier to manage. Knowing the methods could save tremendous amount of time in debugging and understanding complex and not-so-complex formulas and calculations.

Clear the output and input
You need to be absolutely clear on the outputs of the workbook, input data and parameters that change. It may sound as a no-brainer but there could many sheets that look important but are just a remnants of some obsolete requirements, tests or a long-forgotten ad hoc. Workbooks with some history tend to have many of these. The identification of the location, source and definition of input data is also a must as sources and definitions change. There could also be data that is no longer relevant and serves no purposes in the workbook.

Ask for guidance from the author
You might be lucky to access to the author of the workbook. If it is not the case, ask to contact him - often she is still part of the organization and easily reachable. If she is not in the organization, do not hesitate asking for a meeting - people usually are very happy somebody values their work and would  share everything they know. Go over the workbook and make as many notes as possible - this will pay back. 

Reverse engineer it
If you are not lucky enough to have access to the author and the tasks involve improvement or building new functionality your options are limited to reverse engineering the workbook. This process could be very tedious depending on the complexity of the workbook and skill level of the author. Start from the outputs and go back to the sources. Put notes on each clarified step as you could be easily confused if the business and calculation logic are not straight forward. Put the notes next to the calculations and make them as detailed as possible. Refine and edit them on every iteration until everything is clear. After the reverse engineering processes is completed, test if changing the input will produce the expected results. If not, then you need to continue the reverse engineering.

Add notes
Unlike you and me, most of the people do not document well enough or properly note the workbooks. Keep your good practices and enrich the new workbook with as many notes as possible. I have mentioned notes in couple of time earlier in this post but notes at the point of calculations/operations save time and efforts and helps keeping them updated. 

Be aware of hidden sheets, columns and rows
Always remember to check for that. Developers often use that for various of reasons and it could hamper the understanding of the workbook.

Simplify complex formulas
Some complex formulas are difficult to understand let alone evaluated if correct. When you hit on one, break it in its components track where data comes from how it is transformed.

Consider rebuilding it from scratch
Understanding the logic, methods, formulas in a workbook could be a daunting task. It is highly likely there is heavy burden of legacy as well as the workbook and everything in it is outright wrong or simply not serving the new requirements. Trying to fix it is one option but rebuilding it from scratch often is the better one. Personally I prefer the rebuilding option as the result is a clear, easy to follow workbook that I know inside-out. I have also found the rebuilding takes less time and efforts than the fixing with the additional benefit of dealing with something that follows my own practices. If the deadlines do not allow full rebuild, you could consider rebuilding some parts or modules in the workbook.

It all could be wrong
It is not nice to think for the work of co-workers like that but there is a reason this workbook has come to the screen of as an Excel advanced user as you. Sometimes if something looks wrong it is just because it is wrong. Excel lured people of all sorts to create complex calculations, usually in very short time and not much of a research on good practices. Bear in mind the technical approach and the business logic could be wrong as well in addition to the smaller errors in formulas. You need to understand well what the workbook is all about to detect that.

Some of these considerations could be implemented in model building in general and software. I am maybe missing some other principles that come handy and I will be happy to get some of your ideas and experience in this matter.