Naming Cells and Ranges in Excel

At Video Financial Modelling we like to keep up to date with opinions of other financial modelling and Excel professionals. We had a quick look at an articlehttp://www.fimodo.com/2009/08/3-approaches-to-avoid-complex-off-sheet-references/ which fimodo produced regarding off-sheet references. Whilst fimodo is great and has a lot of fantastic articles, we tended to disagree with the extensive use of named cells and ranges. The remainder of the article was spot on, in terms of both dedicated import lines for each sheet and avoiding multi-level linking.

Now, whilst there are some best practice methods of developing a model which are wide spread within the industry, personal preference does come into financial model development. At Video Financial Modelling we like naming cells and ranges within our models. There are a number of reasons for this; however the main two are model audit and efficiency.

Model Audit

It is much easier to do a simple sense check of a financial model with names. For example if we saw a formula for operating expenditure which says, opex x inf_index x opsind. i.e. this says the operating index multiplied by the inflation index multiplied by the operations indicator. This is much easier than quickly sense checking a formula as follows assG4 x ts33:33 x ts 55:55. This is especially helpful when a third party is looking through your model.

Of course, you have to be careful that the right cells and rows are named, however this should be relatively straightforward and no more onerous than looking up ordinary cell references.

Efficiency

If you start to use standard names regularly in your modelling then you can just type them in rather than going back to the page where the input is on. This is like a shortcut, and should be much quicker than navigating to the actual Excel sheet.

How to name cells and ranges

You can follow along with some of the below examples in the Excel spreadsheet and video tutorial contained in this blog.

Naming Cells

Now this is easy, you can simply put the data in a cell, write a name to the left of the data cell, select the data cell and push CTRL +F3. Follow and accept the prompts and the cell will be named. We also have some other tricks for naming numerous cells at one time which you can find in our Excel Functions training course.

Ok, let’s do an example. Put 3% into a cell, then label it Inf (for inflation) to the left of the data. Click on the data (3%) again and press CTRL+F3. Follow the prompts until you’ve named the cell.

Now if you go onto it, there will be a name in the top left hand of the Excel screen as per below.

Naming Ranges

Put your data in a row. Type a name in say column E of row. Then select the name, press SHIFT+spacebar, to select the row. Once selected press CTRL+F3 and follow the prompts.
Now let’s do an example by naming the inflation index below.

Write InfIndex, select the name, then select the whole row by pushing SHIFT+spacebar

Push CTRL+F3 and follow the prompts to name the row.

User beware – Naming Ranges

Now we do acknowledge that the use of named ranges can end in disaster, if they are not setup correctly. Here are a couple of tips to ensure that you setup named ranges in financial models correctly.

1. You should consistently setup time series or data pages starting in the same column, say G. This will allow labels and totals to the left of the first date.
2. Make sure that you name time data ranges and use the named ranges on other pages. This will ensure the other pages time data will be aligned correctly.
3. Split your time data pages into monthly and quarterly/semi-annual data. Project finance models are usually monthly during construction and quarterly/semi/annual thereafter when debt starts to be paid. Now from Excel 2007,(which has extra columns all the way out to XFD or 16,384 columns!) you could get away with an all monthly financial model, however that is a topic for another day.

Like this video tutorial. Check out our Excel and financial modelling training courses.