Insert a named range into a formula in Excel

You can quickly insert a named range to a formula that you are building in your Excel spreadsheet. What are named ranges?

 

Using named ranges in formulas makes it easier to build and maintain formulas. Using a named range in a formula also makes the formula look more user-friendly.

 

An example of a SUM function with a named range          An example of a SUM forrmula

An example of a SUM formula

 

An example of a SUM formula with a named range

 

Insert a range in a formula

Start typing your formula as necessary.

 

With the cursor still in the formula cell, click Formulas > Use in Formula > Paste Names.

 

In the Paste Name list, select a named range that you want to insert into your formula.

 

Optionally, repeat step 2 and 3 to add as many ranges as necessary.

 

Note: Updating a range itself will not automatically update the reference to the range in the formula. You will have to manually re-insert the named range in the formula.

 

Paste the range list in the spreadsheet

You can get a list of all named ranges that the current sheet or workbook uses, and paste them as a list in your sheet. This is particularly useful when you need to quickly see the list of ranges along with the data reference associated to your current spreadsheet.

 

Select a cell or a blank range where you want to paste the range details in the worksheet.

 

Click Formulas > Use in Formula > Paste Names.

 

In the Paste Name dialog box, click Paste List.

 

Click OK.

 

You will now see two columns in the spreadsheet—range names and the cell references that each range holds.

Leave a Comment