Naming cell references and ranges
A name can be given to a cell reference or range by one of these two methods:
Modifying the Name Box
– Select a cell or range
– In the box which displays the cell reference, type your desired name.
Defining a New Name
– Go to Insert > Name > Define…
– Type your desired name
– In the Refers To: box, select the worksheet and cell reference or range to which the name should refer
NOTE: Formulas containing the previous reference will not automatically be updated. If you wish for all formulas containing the cell reference to be updated with the new name, go to the Insert menu, then select Name > Apply.
Names in Excel:
CANNOT start with a number or any special character other than an underscore (_)
CANNOT contain spaces or symbols except the underscore (_), full stop (.), backslash (\) or question mark (?)
CANNOT look like a cell reference (eg: Q3)
CANNOT be more than 255 characters long
Naming constants and formulas
Names do not need to refer to specific cells or ranges. They can instead represent specific values or formulas, such as a prescribed interest rate.
To name a constant or formula:
– Go to Insert > Name > Define…
– Type your desired name
– In the Refers To: box, enter the value for the constant or formula
Referencing cells or ranges in other workbooks
Use the following formula:
=[WorkbookName]SheetName!CellAddress
If the workbook name or sheet name contain spaces, both must be enclosed in single quotation marks:
=B1*'[I am a workbook]I am a sheet'!A1
If the workbook you want to refer to is closed, you must add the complete path to the reference:
=B1*'C:\My Documents\Excel\[I am a workbook]I am a sheet'!A1
Terrible at Maths 