Question

please tell

I will mark him as brainlist

Answer 1

20 tips for Named Ranges in Excel

by DAVE BRUNS | September 15, 2016

Named ranges are one of these crusty old features in Excel that few users understand. New users may find them weird and scary, and even old hands may avoid them because they seem pointless and complex.

But named ranges are actually a pretty cool feature. They can make formulas *a lot* easier to create, read, and maintain. And as a bonus, they make formulas easier to reuse (more portable).

In fact, I use named ranges all the time when testing and prototyping formulas. They help me get formulas working faster. I also use named ranges because I'm lazy, and don't like typing in complex references :)

Like named ranges? Hate named ranges? Leave your thoughts below.

The basics of named ranges in Excel

What is a named range?

A named range is just a human-readable name for a range of cells in Excel. For example, if I name the range A1:A100 "data", I can use MAX to get the maximum value with a simple formula:

=MAX(data) // max value



The beauty of named ranges is that you can use meaningful names in your formulas without thinking about cell references. Once you have a named range, just use it just like a cell reference. All of these formulas are valid with the named range "data":

=MAX(data) // max value =MIN(data) // min value =COUNT(data) // total values =AVERAGE(data) // min value

Creating a named range is easy

Creating a named range is fast and easy. Just select a range of cells, and type a name into the name box. When you press return, the name is created:



To quickly test the new range, choose the new name in the dropdown next to the name box. Excel will select the range on the worksheet.

Excel can create names automatically (ctrl + shift + F3)

If you have well structured data with labels, you can have Excel create named ranges for you. Just select the data, along with the labels, and use the "Create from Selection" command on the Formulas tab of the ribbon:



You can also use the keyboard shortcut control + shift + F3.

Using this feature, we can create named ranges for the population of 12 states in one step:



When you click OK, the names are created. You'll find all newly created names in the drop down menu next to the name box:



With names created, you can use them in formulas like this

=SUM(MN,WI,MI)

Update named ranges in the Name Manager (Control + F3)

Once you create a named range, use the Name Manager (Control + F3) to update as needed. Select the name you want to work with, then change the reference directly (i.e. edit "refers to"), or click the button at right and select a new range.



There's no need to click the Edit button to update a reference. When you click Close, the range name will be updated.

Note: if you select an entire named range on a worksheet, you can drag to a new location and the reference will be updated automatically. However, I don't know a way to adjust range references by clicking and dragging directly on the worksheet. If you know a way to do this, chime in below!

See all named ranges (control + F3)

To quickly see all named ranges in a workbook, use the dropdown menu next to the name box.

If you want to see more detail, open the Name Manager (Control + F3), which lists all names with references, and provides a filter as well:



Note: on a Mac, there is no Name Manager, so you'll see the Define Name dialog instead.