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:

=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)

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.

Excel formula for tab

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.

Read more

You’re reading stories from Financial Training Associates Ltd, the company that runs courses in Excel modelling artistry.

Read more