CP008: 6 Tips to handle workbooks made by someone else, #4 is something I struggle with too!

Posted on May 15th, 2014 in Chandoo.org Podcast Sessions - 11 comments

In the 8th session of Chandoo.org podcast, lets tackle inheritance problem.

CP008 - 6 tips to deal with complex workbooks made by someone else - Chandoo.org PODCAST - Become awesome in Excel

What is in this session?

Here is a problem we all face once in a while. We inherit this bulky, bloated, leaking at the edges workbook from a colleague. Now the onus of maintaining it is on us. The person who made this workbook is nowhere to be found. May be she is vacationing in Hawaii sipping pineapple juice. May be he became a vice president and roaming the country in your company’s private jet.

So what do we do? How do we handle this inheritance?

That is the topic of our podcast, episode 8.

In this podcast, you will learn,

  • An overview of the inheritance problem
  • 6 Tips to understand workbooks made by someone else
  • Tip 0: Talk to the creator
  • Tip 1: Model the workbook on paper
  • Tip 2: Locate the engine, ie the formulas
  • Tip 3: See what else is under the hood – hidden sheets, names, VBA code
  • Tip 4: Annotate (add comments) as you learn
  • Tip 5: Locate the controls – inputs, assumptions, scenarios
  • Tip 6: Re-construct from scratch
  • Deep dive in to understanding the formulas
  • Deep dive in to understanding VBA code
  • Conclusions

Go ahead and listen to the show

Links & Resources mentioned in this session:

Keyboard Shortcuts

Important Excel Functions to learn

VBA Code concepts

Transcript of this session:

Download this podcast transcript [PDF]

How do you handle workbooks made by others?

In corporate environment, inevitably, we all end up hauling others work. In this podcast, I have shared the techniques and ideas I frequently use to handle such workbooks.

What about you? Do you take over workbooks made by others? How to handle them? What tricks and techniques you apply to demystify the complexities of these files? Please share your thoughts in comments.

Subscribe to Chandoo.org Podcast

Do you know that you can subscribe and receive latest episodes of our podcast right to your ears? Use one of the below links to get started.

Written by Chandoo
Tags: , , , , , , ,
Home: Chandoo.org Main Page
? Doubt: Ask an Excel Question

11 Responses to “CP008: 6 Tips to handle workbooks made by someone else, #4 is something I struggle with too!”

  1. dan l says:

    7. Remove frozen headers
    8. Write a UDF to extract color from cells
    9. Delete bad charts
    10. Avoid snarking at shoddy quality

  2. Chandoo: Re: CP008, This was outstanding. Thank you very much!

    Related issues: When building a complicated workbook, I build a Table of Contents (TOC), number the steps, hyperlink the steps from the TOC to the spreadsheets and insert cell comments to explain what I did.

  3. Chandoo: Re: CP008, This was outstanding. Thank you very much!

    Related issues: When building a complicated workbook, I build a Table of Contents (TOC), number the steps, hyperlink the steps from the TOC to the spreadsheets and insert cell comments to explain what I did.

  4. Terry says:

    I would be very interested to know if anyone has any particular techniqes to analyse complex formulas, especially ones with nested ifs. I sometimes pull them apart in Excel or Word and break them down line by line to work out what's going on. So each open bracket starts a new line. I then indent if I need to and take back the indent level when a bracket closes. This helps me to visualise the logic. It's tedious but sometimes it's the best I can do.

  5. dan l says:

    -----
    I would be very interested to know if anyone has any particular techniqes to analyse complex formulas, especially ones with nested ifs.
    -----

    This helps a great deal:
    http://dailydoseofexcel.com/archives/2010/09/29/formula-editor-in-notepad/

    You still have manipulate it a bit, but it's way more readable in NP++

  6. Kiev says:

    Chandoo, thanks so much for this podcast. That give me a really good summary of past experience (although I may need to more background to understand the VBA ones).

  7. Andrew says:

    What about the reverse ? What are best practices to setting up your workbooks - even for the most simplest, even if you're never going to share your workbook with other people.

    I work with SharePoint and InfoPath predominantly. I've a glorified template in InfoPath, yet everybody knows my forms (InfoPath) are set up consistently and identically, they will contain any code used on buttons or how X formulas are configured on a Developer Notes page. There is also an Admin page which allows easy updating or checking of important fields.

Leave a Reply