PROBLEM: wasting time typing range references into formulas, only to have them change as you copy them.
SOLUTION: learn the quickest and easiest way to reference ranges in Excel.
Excel – name a range of cells for quick referencing
If you continually refer to a set range save yourself the hassle of having to type the range repeatedly. Instead give the range a name.
To quickly create a named range:
- Select the range. For example if you are continually typing the formula =SUM(A1:A14) select the range A1 to A14.
- Click into the Name Box (top left of the screen under the Clipboard group) and type the Name for the range. Make sure the name doesn’t include spaces.
- Press ENTER. To use the named range in a formula just replace the range reference with the name, e.g. =SUM(4thQtr).
Note: To name a range using more than one word consider using an underscore or running the words together, e.g. 4thQtr or 4th_Qtr.
To delete a range name click the Formulas tab, Name Manager, select the named range and then click Delete. If the named range has been used in formulas these may now show errors. Just enter the formulas again and save your file.