This particular tip has saved me a lot of time formatting Excel workbooks over the years. Unfortunately I did not learn it immediately, and I wish I had.
We are working on a series that will introduce Excel for the purpose of building financial models. The first three videos have been posted, and more will follow, but in the interim any feedback would be greatly appreciated.
I recently received a question from a user that highlighted the first error identified on the site since we launched in 2013:
Q: “I really appreciate your site and model. I’ve found it very valuable. I was rebuilding your model for an additional company and believe you may be double counting the RLOC interest. On row 28 of the completed Home Depot model you are adding rows 168 and 253 which are the RLOC interest rows and total interest rows. The total interest row (row 253) adds rows 168 and 251, thus I believe row 168 is being included twice. Am I overlooking something?”
This is a follow up to the post titled “Excel: Using F9 to Audit Formulas.” This video will explain how to extract nonblank or nonzero data from an array without using an array formula.
I was recently provided a workbook containing a balance sheet with over 700 line items. Many of the line items showed a value of zero, making the challenge one of extracting all nonzero values without having to go through and delete line items one row at a time. This required a slightly more complex formula, which frankly I got wrong a handful of times before finally arriving at the solution. And in that process of auditing (or debugging) the formula, the F9 key was fantastically useful.