March 26


Excel – name a range of cells for quick referencing

By Sharyn Baines

March 26, 2014

Modify, Named Range

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.

Sharyn Baines

About the author

Sharyn is an expert trainer. She became the first certified Microsoft® MOUS Authorised Instructor in New Zealand. She is endorsed by Microsoft® as a qualified Microsoft® Office Specialist and has more than 20 years of experience in the training industry, developing and delivering technology training workshops. Her approach to taking the “techie-speak” out of technology training has placed her as a preferred supplier to many of New Zealand’s leading organisations.

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

JOIN FREE! 'Excel at Work Insiders Group' for free tutorials, mini-courses, videos and blogs!