Cell names can help users navigate quickly around an Excel model.
Excel modelling with named cells: an example at work
Sometimes it helps to see an example at work. Please download the Excel names spreadsheet for an illustration of how cell names can be used in financial modelling. If you click on cell B5 and look at the LHS of the formula bar, you will see the names that have been applied in the spreadsheet (starting with “scen”). If you click elsewhere in the spreadsheet, and then click on the little drop down arrow in the LHS of the formula bar, you will see how Excel very quickly navigates you to the named cell.
Naming cells in a financial model
You can access cell naming features from the “Formulas” tab, “Defined Names”, “Name Manager”, “New”. Alternatively, if you select a cell or an area of your Excel spreadsheet, right mouse click (=”Shift” “F10” on many keyboards) and select “Define Name” – you can assign a name very quickly. Names are great for navigating quickly to remote areas of a financial model.
An alternative to navigating with cell names: hyperlinks
Cell names are great for navigating around spreadsheets. Occasionally you will see modellers using hyperlinks to help navigate in Excel, so we’ve provided a few details about that here: navigating using hyperlinks.
Financial modelling course tips
Experienced modellers know that there are some drawbacks to using names. Yes they can be helpful in identifying key parts of a spreadsheet, helping you navigate quickly. However, if your financial model becomes large and complex, and it’s gotten to the stage where many names are used, the profusion of names will risk making the spreadsheet confusing to a new user. With many names in a spreadsheet, a new user may have trouble working out what the names actually mean or refer to.
Although too many names can become confusing, there are other problems with cell names. Once you create a formula that makes use of a cell name, Excel replaces the regular “A1” style reference with a text name. If you fill the cell across or down as normal the reference won’t adjust.
Inadvertently you will find that, with the use of names, you have ‘locked’ the cell (the equivalent of inserting dollar “$” signs in your cell reference or pressing the “F4” keyboard shortcut). If you then copy and paste that tab somewhere else e.g into another model you will find that the names end up acting like broken links in your model.
Unfortunately if you find that you’ve got problems with names (too many names, too many locked cells, broken references) you will discover that by creating names you have taken a one way trip in Excel. If you create a cell name but decide to delete the name later from the name manager (“Ctrl” “F3”), you will end up with #NAME? errors in your spreadsheet.
So – quite a few problems with names! The lesson: use only a very few names to help navigate quickly around remote areas of your spreadsheet.
Coming up in the Excel financial modelling course extract
In the next lesson we look at using Excel scenario manager to output the results of scenario analysis quickly.
About this online financial modelling course material
You’re looking at a free online financial modelling course extract covering Excel functions.