Menu
CP008: 6 Tips to handle workbooks made by someone else, #4 is something I struggle with too!
Chandoo.org Podcast Sessions - 12 comments
In the 8th session of Chandoo.org podcast, lets tackle inheritance problem.
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
Podcast: Play in new window | Download
Subscribe: RSS
Links & Resources mentioned in this session:
Keyboard Shortcuts
- CTRL + ` to show / hide formulas
- F9 key to evaluate portions of a long formula
- F5 followed by ALT S and then F – Go to Special & Highlight formulas
- Shift + F2 – insert a comment in a cell
- CTRL + G – turn on / off immediate window in Visual Basic Editor (VBE)
- Hover mouse pointer on a variable in break-mode to see its value
- Related: Important Excel Keyboard Shortcuts
Important Excel Functions to learn
- Excel Lookup functions – Podcast, detailed article, recommended eBook
- Excel Sum functions – Podcast, SUMIFS, SUMPRODUCT
VBA Code concepts
- Introduction to VBA (5 part crash course)
- Excel VBA – tips, tutorials, examples & information
- VBA Classes – learn VBA online
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.
- For iPhone or iPod or iPad: Click here to subscribe.
- Andriod Phones & Tabs: Click here to subscribe on Stitcher. (You can download Stitcher free app from that link)
- Windows Phone: For Windows phone, search your podcasts app for our show.
- Or… As always, you can get latest episodes, show notes & resources from our Podcast page.
Hello Awesome...
My name is Chandoo. Thanks for dropping by. My mission is to make you awesome in Excel & your work. I live in Wellington, New Zealand. When I am not F9ing my formulas, I cycle, cook or play lego with my kids. Know more about me.
I hope you enjoyed this article. Visit Excel for Beginner or Advanced Excel pages to learn more or join my online video class to master Excel.
Thank you and see you around.
Related articles:
|
Leave a Reply
| « Building a simple timer using Excel VBA to track my Rubik’s cube solving speed [case study] | Excel Links – Delay in State migration visualization results edition » |
At Chandoo.org, I have one goal, "to make you awesome in Excel & Power BI". I started this website in 2007 and today it has 1,000+ articles and tutorials on data analysis, visualization, reporting and automation using Excel and Power BI. 
12 Responses to “CP008: 6 Tips to handle workbooks made by someone else, #4 is something I struggle with too!”
7. Remove frozen headers
8. Write a UDF to extract color from cells
9. Delete bad charts
10. Avoid snarking at shoddy quality
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.
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.
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.
Terry, I just saw this. It's free to try the beta:
http://fastexcel.wordpress.com/2014/04/28/making-sense-of-complex-formulas-an-indenting-viewer-editer/
Thanks Doug,
This looks good.
Unfortunately the system at work has severe restrictions about what we can instal, but I will definitely try this at home.
Whether deconstructing or building nested formulas, I prefer to the use of in cell carriage return (ALT+Enter) or notepad to parse the lines vertically.
The process also teaches as you parse since you need to learn where to parse the formulas!
-----
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++
@Dan L
I use the same addin for the same reason
Hui...
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).
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.
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.