fbpx
Search
Close this search box.

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

Share

Facebook
Twitter
LinkedIn

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.

Facebook
Twitter
LinkedIn

Share this tip with your colleagues

Excel and Power BI tips - Chandoo.org Newsletter

Get FREE Excel + Power BI Tips

Simple, fun and useful emails, once per week.

Learn & be awesome.

Welcome to Chandoo.org

Thank you so much for visiting. My aim is to make you awesome in Excel & Power BI. I do this by sharing videos, tips, examples and downloads on this website. There are more than 1,000 pages with all things Excel, Power BI, Dashboards & VBA here. Go ahead and spend few minutes to be AWESOME.

Read my storyFREE Excel tips book

Excel School made me great at work.
5/5

– Brenda

Excel formula list - 100+ examples and howto guide for you

From simple to complex, there is a formula for every occasion. Check out the list now.

Calendars, invoices, trackers and much more. All free, fun and fantastic.

Advanced Pivot Table tricks

Power Query, Data model, DAX, Filters, Slicers, Conditional formats and beautiful charts. It's all here.

Still on fence about Power BI? In this getting started guide, learn what is Power BI, how to get it and how to create your first report from scratch.

letter grades from test scores in Excel

How to convert test scores to letter grades in Excel?

We can use Excel’s LOOKUP function to quickly convert exam or test scores to letter grades like A+ or F. In this article, let me explain the process and necessary formulas. I will also share a technique to calculate letter grades from test scores using percentiles.

12 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.

  8. Erin says:

    This was a lifesaver! During the COVID pandemic one government agency handed us, another government agency, a half finished and complex data dashboard. To make things more complex, they were too busy to give us example reference data so we had to guess the variables. All of your tips are helpful. Thank you.

Leave a Reply