What are Named Ranges in Excel?

Jan 28, 2019 | Excel Hints and Tips

Did you know that you do not need to perform calculations using cell references?  Named ranges can be used instead which makes the formulas easier to read and can be used to update many calculations in one go if the range of data changes.  Using range names is not vital for calculations but at times is another option that can be used.

Creating Named Ranges

Image of the Name BoxThe quickest way to create a named range is to select the cell(s) in the range.  Type the range name in the name box and press return.  Named ranges cannot contain spaces.  The name box normally shows the cell reference you are in unless the cell(s) have been provided with a name.  You can navigate to any named ranged created by selecting them from the drop-down on the name box.

Editing Named Ranges

Named ranges may need to be amended or deleted as the range of the data alters or is no longer required.  Be careful not to delete a range that is currently being used in a calculation because the calculation will stop working.

On the formulas tab use the name manager to adjust the name of the range, the range of cells used or delete ranges no longer required.

Using named ranges in calculations

Image showing using a range name in a SUM functionThe named range replaces the cell references normally used.  For example, =SUM(A1:A8) becomes =SUM(Salary) if cells A1 to A8 have been called Salary.  Unlike cell references, named ranges cannot move when they are autofilled or copied so remain fixed.  This can be an advantage or a hindrance depending on how you need the autofill to behave.  They are often used as an alternative to absolute referencing.

 

Want to learn more about Excel to improve your productivity then email lara@laramellortraining.co.uk to discuss how I can help or have a look at some of the Excel Training courses I provide.