Excel – name a range of cells for quick referencing

Posted: March 26, 2014

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:

  1. Select the range.  For example if you are continually typing the formula =SUM(A1:A14) select the range A1 to A14.
  2. 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. 
  3. 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.

Was this blog helpful? Let us know in the Comments below.

If you enjoyed this post check out the related posts below.

Join our free insiders Group!

Learn how to SAVE TIME and WORK SMARTER, without the 'techie' speak!

{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}

Join our free insiders club! 

Receive 100+ Steps to Becoming Proficient in Excel