How to use Name Range in MS Excel.

Excel has an interesting feature Name Range. Name Range is name given to a particular cell or range of cell. Name range can be used in formulas or in creating graphs.

How it Works

Select the range you want to name and go to NAME BOX. Type name you want (refer below photo), alphabet in our case and press enter.Name_Range2

How to use in formula

In our case we have given range name alphabet. We will use COUNTIF

=COUNTIF(alphabet,”*”)

This counts number of alphabets in the range. But instead of giving range C4:C10 we have used alphabets (name of our range).

To view all named Ranges in a sheet click on Formula tab and Name Manager Button. Here you can change range just by clicking on Edit. Other function’s available in Name Manager

  • New – To create new Name Range
  • Edit – Edit existing Name Range
  • Delete- To delete existing Name Range

To Apply Newly created Name in Location where your range is used in Formula, Click Expand the Define (refer below Image) menu, then click on Apply Name. Select the New Name which have created and not used. All Ranges in formula will change to name you have created.

Excel 2010 Name Range Manager

Excel 2010 Name Range Manager