Local trumps Global – Local Named Ranges make the spreadsheet more tractable

Creating user friendly spreadsheets is not just a professional courtesy anymore. We have the obligation to help users decipher the spreadsheet we so hastily put together. Spending a little extra time setting up well designed spreadsheets help a firm reduce future costs by

1. reducing the time spent on testing and scrutinizing the sheet.
2. improving the productivity of the worker.
3. helping the ‘new guy’ figure out the sheet in no time.
4. increasing the visibility of errors, consequently reducing the need for rework.

I can say with absolute certainty that  authors of frequently used, but poorly designed spreadsheets would be cursed at more than a terrible driver. Local names go a long way in making your spreadsheet more tractable. I have to admit that there is no dearth of articles preaching spreadsheet etiquette. I am here to advocate only one important aspect, framed into three guidelines.

Guideline 1 : Structure the workbook

Firstly, I cannot stress the importance of sharpening the axe before getting down to the chopping. Developers need to partition the workbook into a judicious number of worksheets dedicated to a specific purpose. Most workbooks may be structured along the following scheme:

1. Data/Inputs – One or two sheets that contain the data used to make the calculations.
2. Constants – Its a good idea to put all your constants into a sheet, instead of typing them into the formula directly. Most experts would favor naming them as constants in your sheet, but this method makes it more visible to the user.
3. Variables – This sheet should contain the numbers that remain the same for one calculation, but change for the next. They are predominantly the assumptions you make.
4. Calculation – This is where the actual calculation takes place. I understand most people insert columns directly into the data sheet and finish their calculations there, but that method is not advisable for validating data. If you end up with a lot of corrections, you either have to copy paste the changes into individual cells, or do the calculations all over again.
5. Results – This is where the outputs are displayed to the user.

Guideline 2 : Use Local Named Ranges

I am sure you would have seen this in plenty of articles; a screenshot of a formula bar displaying a disgustingly long formula with cell references; after the cells are named properly, everything becomes fine and dandy. That is the idea, but one very important detail always slips away. By default, all names in excel are created with a Global scope.

Global names work well with small workbooks with few named ranges. However, when the workbook contains dozens of spreadsheets with innumerable range names, they make things a little complicated. Local names, on the other hand prove useful in larger spreadsheets for the following reasons:

1. Global names should be unique within the entire workbook. However, Local names need to be unique only within a spreadsheet. The user can create standard templates, and simply use the indirect function to decide which sheet to pick the values from.
2. When used in formulas, global names do not give any information about the location of the sheet they are stored in. Local names, on the other hand feature the name of the sheet, if they are not located in the current worksheet.
3. Local names supercede Global names within a sheet, i.e. if you have a local name and a global name with the same label, within the sheet where the local name is defined, the global name cannot be used. Hence, it is not a good idea to use global names and local names together.

Guideline 3 : Don’t forget to Label the ranges

Having a blank sheet with named ranges scattered randomly doesn’t help much. Putting a label to the top or left of a named range is an integral part of making the spreadsheet more tractable.

A Makeshift Solution:

Majority of the excel users have been inadvertently using Global Range Names for a long time, mostly because excel does not have a neat framework to name ranges locally. Sure we can specify the scope for individual names, but when you use the ‘Create from Selection’ option, only global names are created. Here is a makeshift solution to the problem: this code turns all the global names in a workbook into local ones.

'================================================================
'A simple sub that accepts a workbook as an argument and
'converts all the global names into local ones. The sub
'will work with the active workbook when called with no
'arguments.
'Author : Ejaz Ahmed - ejaz.ahmed.1989@gmail.com
'Date: 21 January 2014
'Website : http://strugglingtoexcel.wordpress.com/
'================================================================
Sub MakeLocal(Optional ByRef WhichBook As Excel.Workbook)

'If no workbook is passed to this sub, all the names in
'the active workbook will be converted to local names
If WhichBook Is Nothing Then
    Set WhichBook = Excel.ActiveWorkbook
End If

'Declaring Variables
Dim nmeName As Name
Dim strName, strRefersTo As String

'Looping through all the names in a workbook. This contains
'Local and Global variables.
For Each nmeName In WhichBook.Names
'Strictly speaking, Global names cannot be converted into
'local ones. The original name has to be deleted and a new
'name created.
        If TypeOf nmeName.Parent Is Workbook Then
            'store the name's label in a string variable
            strName = nmeName.Name
            'store the name's Refers to Range Address in
            'sring variable
            strRefersTo = nmeName.RefersTo
            'Add the sheet's name and ! to the original
            'name
            strName = nmeName.RefersToRange.Worksheet.Name _
                        & "!" & strName
            'Delete the global name
            nmeName.Delete
            'Create a new local name
            WhichBook.Names.Add Name:=strName, RefersTo:=strRefersTo
        End If
Next nmeName

End Sub

There are better ways to create local names. For instance, by using the CurrentRegion property, named ranges can be automatically resized to include new data, or exclude blank cells.