User FAQs

If you have a question, please send it to us.

1) What are the advantages of using named ranges ?
You can insert or delete cells, rows and columns and the cell references remain intact.

2) How many named ranges should a workbook have ?
The more you have the more memory Excel needs to process the file.
10,000 or less would be recommended
20,000 would be excessive
40,000 would seriously impact performance
60,000+ would cause your file to crash

3) What is the difference between a Workbook Level named range and a Worksheet Level named range ?
Workbook Level - These can be referenced from any worksheet in the workbook, without prefixing.

=Name_Wbk 

Worksheet Level - These are intended to only be used on one specific worksheet although you can reference them from other worksheets by prefixing the name of the worksheet.

=Sheet1!MyWorksheetLevel 

4) What type of named range is created when you use the Name Box ?
Typing into the Name Box creates a named range with workbook level scope.
You can create worksheet level scope if you proceed it with the name of the worksheet and an exclamation mark.

5) What is the maximum number of characters you can enter in the Name Manager ?
The longest name is 255 characters.
The longest comment is 255 characters.
Using the Name Manager, its 2,083 characters.
Using VBA, its 8,192 characters.

6) What is a dynamic named range ?
A dynamic named range expands automatically when more data is added to the range.
This type of named range uses a combination of the worksheet functions OFFSET and COUNTA.

=OFFSET(Sheet1!$B$2,0,0,COUNTA(Sheet1!$B$1:$B$100),1) 

7) When you copy (or move) a worksheet across to a different workbook, do all the named ranges (wsh + wbk) get copied across ?
No. Excel does not copy all named ranges when you copy/move a worksheet to another workbook.
Worksheet level named ranges will get copied.
Workbook level named ranges that refer to a range on the sheet you are moving will be copied, the scope will change and the reference will be updated.
Workbook level named ranges that are used in formulas on the sheet you are moving will probably break.
Workbook level named ranges that are not used in formulas will not be copied.

8) Write the code to populate an array with the values in a named range ?
The following lines are all equivalent.

Dim arValues As Variant 
arValues = Range(NamedRange).Value 
arValues = Application.Evaluate[NamedRange].Value 
arValues = [NamedRange]           '// since value is the default  

9) Where do the named ranges that have an "IQ_" prefix come from ?
These prefixes are automatically created as part of the Capital IQ Add-in.

10) Where do the named ranges that have a "wrn." prefix come from ?
These prefixes were automatically created as part of the Report Manager Add-in.
This add-in was added in 1997 and removed in Excel 2002.