Hi,

I’ve been in a bit of writer’s block recently and have struggled to find good things to write about. The most compelling topics all had confidential information, so I couldn’t send those out. But I was on a call yesterday when a common situation arose and thought I’d share. So this month’s topic is a good spreadsheet tip, but for next month I’m working on another company valuation teardown, similar to last year’s discussion of Amazon.

Best,

David

I created a short video where I describe some unique features of a financial model that I developed for a client that raised over $350 million. You can see it at brodetelecom.com/model-overview.

Finding Cells Which Reference Other Worksheets

Have you ever been looking at an Excel worksheet and found a cell which is pulled from another worksheet? Pretty quickly you realize that the rabbit hole goes deeper and that the workbook’s sheets are linked together in non-obvious ways. So the question that came up was this:

How can I find all the references to other worksheets on this page?

The solution relies on an often-overlooked part of the Find Dialog Box. But first, recall what a cell reference looks like. In the picture below, cell A1 on Sheet 1 has a formula referencing cell A1 on Sheet 2. The key thing to note is that when you reference another sheet Excel uses an exclamation point to as part of the cell address.



Thus when we search for “!” we can find a cell reference from another sheet. (Of course, we will find exclamation points used in text, but most spreadsheets don’t have many cells with text notes like “Net income!” in them.) Use Control-F to bring up the Find Dialog Box.



It’s important to make sure that the search space is limited to the one sheet that you’re on. To do that, use the Options>> button circled in red above and ensure that Within is set to Sheet and not to Workbook.



Finally, click the Find All button and Excel opens up a new pane below which shows all the places which have an exclamation point in this sheet.



And that’s it. I use Find All constantly because it doesn’t just show you the hits one at a time but gives you a full list to peruse. A final tip is that this technique can be used to identify links coming from external workbooks. Since an external reference looks like this: =[Book3]Sheet2!$A$1 you can search for “]” (a relatively rarely used character) and find all of those cells.