Imagine you wanted to use a formula to reference tab names in your Excel model. Perhaps the context is creating a contents page in your Excel financial model. Here we’ve got a formula you can copy that will reference the active sheet tab name in a specific cell in Excel:
If you’d like to use it, just select a blank cell and then copy and paste the formula into your formula bar, then press the enter key.
The formula makes use of Excel’s text functions and you could use it to create a contents page in your Excel model. See the example above which you can click on and download.
Adding hyperlinks to the contents page
As well as using the formula to list out tabs, you can use hyperlinks to help people jump around the tabs in your model. The example above includes hyperlinks.
Making your model user-friendly
Making your work more user friendly (without over complicating it) requires a tiny bit of Excel modelling artistry. Like a contents page in a book, a contents page in Excel could give the user some initial clues to model structure, plus help them find their way around the spreadsheet quickly.