Remkomplekty.ru

IT Новости из мира ПК
2 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Excel named range

Excel named range

A named range is a short text description that can be used instead of the cell address to refer to individual cells or ranges of cells.
Providing cells with actual descriptive names can be used to simplify formulas and make them much more user friendly.
Descriptive names are also a lot easier to remember that actual cell addresses.
You can also provide descriptive names for your charts, and shapes although this is not discussed in this section.
You can also give descriptive names to constant values as well as formulas.
Any named ranges that are created are workbook specific and can only be used in the workbook they are created in.
There are two types of named ranges and it is possible to use both types in the same workbook.
Workbook — These can be referenced from any worksheet in the workbook.
Worksheet — These are worksheet specific and can only be referenced on that particular worksheet.

Using Named Ranges

If you define a name for a range of cells then you can use that name instead of the cell address.
Named Ranges can be used instead of the cell address references in calculations and formulas.
You can define more than one name to the same cell or range of cells, although if you use the same name to define two different ranges then the previous name is overwritten.
It is possible to paste your named ranges directly into the Formula Bar while you are in the middle of completing a formula.
Named ranges can be given to cells containing both numerical and text values.
Lets suppose you are using a worksheet to calculate a percentage from a particular number.

Cell «C5» contains the formula =C2*C3.
This method uses cell references in the formula giving the correct result.
Named ranges can be used to help clarify this formula.
You can insert a named range into a cell or formula using the (Insert > Name > Paste) dialog box and pressing OK.
You can also use the shortcut key (F3) to display the (Insert > Name > Paste) dialog box when editing a cell.
You can define a descriptive name for the cell containing the amount and another one for the cell containing the percentage discount.
Assume cell «C2» has been given the descriptive name «Total» and that the cell «C3» has been given the name «Percentage».
The formula in cell «C5» could now be written as «=Total*Percentage» which makes the formula a lot easier to understand.

Advantages of using Named Ranges

In addition to providing an alternative to repeatedly typing in cell addresses and cell ranges, using named ranges have several other advantages.
0) They improve readability and make your formulas much easier to understand meaning there is less chance of errors.
1) Moving or shifting cells that have a named range means that the formulas are adjusted automatically. There is no need to worry about whether the formulas use absolute or relative references.
2) Inserting and deleting cells, rows or columns will not change the location of your named ranges. Moving cells, rows or columns will though.
3) Typing a descriptive name is much easier than remembering a specific cell address, therefore simplifying your formulas.
4) You can quickly move to particular areas of your workbook (or worksheet) very quickly by either using the Name Box or the (Edit > Goto) dialog box.
5) You can also create 3-D named ranges that represent the same cell or range of cells across multiple worksheets.
6) Allows you to define Named Constants which are single, frequently used values.
7) Allows you to define Named Formulas which are common frequently used formulas (save re-typing them).

Rules for Named Ranges

A named range can be up to 255 characters long and can contain letters, numbers, periods and underscores (no spaces or special punctuation characters).
Named ranges are not case sensitive and they can contain both upper and lower case letters. They cannot resemble any actual cell addresses such as «B3» or «AA12».
All named ranges must begin with a letter, an underscore «_» or a backslash «».
Named ranges can include numbers but cannot include any spaces.
You cannot use any named ranges that resemble actual cell addresses (e.g. A$5 or R3C8).
You cannot use any symbols except for an underscore and a full stop. It is possible to include a backslash and a question mark as long as they are not the first characters.
Named ranges can be just single letters with the exception of the letters R and C.
When you add a named range it is the cell that is named and not the cell contents.
They are case insensitive. You cannot have another named range with the same letters but in a different case.
By default named ranges are created as absolute references.
It is possible for a cell (or range) to have more than one named range so typing a new name using the Name Box will not change the named range but will create a new one.

Options

(Calculation tab, Accept labels in formulas) — Lets you use the Natural Language Formulas.

Named Range in Excel

In this article we are going to learn about Named Range in Excel. Named Range gives the Excel users a privilege to provide names to a specific cell, cells (defined range of cells), formula or constant value. Named Range is supported in all versions of Excel. This article will brief you about why and how to use named range.

When and Why to Use Named Range:

When you normally enter data into columns of a spreadsheet you tend to give column headers so that the data in that particular column is much understandable. But when you use this data for various functions then all you use is the data range instead of the column headers. Named range is the name given to list of data, you can use this name in formulas to make them easier to understand.

Читать еще:  Коды ошибок vba excel

Have a look at below image to get an insight. In this example, I have given a Name – “Sales_of_Apple_in_US_in_KGS” to the sales of apples in US available from Jan 2014 to Sep 2014, making it easier to understand.

So, this is how a “named range” makes your formula easier to understand.

How to create a Named Range:

You have two methods to create a named range in excel. We have detailed both of them in this section.

Method 1: Create Named Range using “Formulas Tab” Define Name Option:

You need to follow below listed steps to create a “Named Range” in your excel using “Define Name” option under “Formulas Tab”.

  • Select the “Data range” which you want to name.
  • Click on “Formulas Tab” available on Excel Ribbon.
  • Click on “Define Name” button as shown in below image:

  • Now enter the desired name for named range in “Name” text-box and click on “OK” button once done.

Now, your data range is having a name that could be easily referred in formulas.

Method 2: Create Named Range using Name Box:

If you are looking for a much easier way of adding named range to your data instead of hopping onto a number of tabs, then this method is definitely for you.

Follow below steps to create a named range using name box:

  • Select the “data range” for which you want to create a named range.
  • Click on the “Name Box” which appears next to the “Formula bar” wherein the cell number is usually updated as shown in below image:

  • Now enter the “name” of this range and hit “enter button”.

That’s all your data range will be named after this and could be easily referred.

Name Manager in Excel:

Using Name Manager you can list all the “Named Ranges” that are present in the current workbook. Name Manager gives you a wide variety of operations to perform, which are listed below:

  • Create – Using this you can create new ranges in the workbook.
  • Edit – Using this you can edit any existing range created in your workbook.
  • Delete – As the name implies, using this you can delete the existing names created in your workbook.
  • Filter – Filter gives you many options as depicted in below image to sort the listed names.

How to Use Name Manager:

There are two options available to check the name manager.

Method 1: Selecting Name Manager from Excel Ribbon:

Below listed steps need to be followed to view Name Manager:

  • Navigate to “Formulas Tab” in Excel.
  • Click on “Name Manager” button available as shown in below image:

Method 2: Keyboard Shortcut for Name Manager:

Name Manager could be used easily by holding “Ctrl + F3” Keys together.

Deleting Named Ranges:

Named Ranges could be deleted using Name Manager only. All you need to do is to follow below steps to delete any named ranges available in your excel workbook.

  • Select “Name Manager” from “Formula Tab” or by holding “Ctrl + F3” Buttons together.
  • Once the “Name Manager Window” opens, select the range which you want to delete.
  • Click on “Delete” Button available on “Name Manager Window” as shown in below image:

  • Click on “OK button” on the Confirmation Page.

After this the selected “Name” will be deleted from your workbook.

So, this was all about Named Range functionality and various ways to use it in Excel.

Ankit is the founder of Excel Trick. He is tech Geek who loves to sit in front of his square headed girlfriend (his PC) all day long. :D. Ankit has a strong passion for learning Microsoft Excel. His only aim is to turn you guys into ‘Excel Geeks’.

VBA Named Range

Excel VBA Named Range

When we work with a large amount of data to avoid referring to a particular cell or cell ranges we usually create named ranges and it allows us to refer to the required cell range through the named range. In VBA to create name range we have Add Name Function.

We can select a cell or range of cells and give a name to it. After naming the cells we can refer to those cells by entering those defined names instead of a usual row or column references.

How to Create Named Ranges?

It is a walk in the park job to create named ranges. The first thing we need to do is to identify the cells we want to create named range.

For an example look at the below image.

To arrive the profit in B4 cell I have applied the formula B2 – B3.

This is the common thing everybody does. But how about creating the Names and apply the formula something like “Sales” – “Cost”.

Place a cursor on the cell B2 > Go to Name box and call it as Sales.

Place a cursor on the B3 cell and call it Cost.

Now in the profit column, we can refer these names instead of cell references.

This is the basic thing about Named Ranges.

How to Create Named Ranges using VBA Code?

Example #1

Have you ever thought of creating a named range using VBA Code?

Follow the below steps to create named range.

Step 1: Define the variable as “Range”.

Code:

Step 2: Now set the variable “Rng” to specific cells you wish to name.

Code:

Step 3: Using “ThisWorkbook” object access Names Property.

We have so many parameters with Names.Add method. Below are the explanations.

[Name]: Name is nothing but what is the name we would like to give to the range we specify.

While naming the cell it should not contain any special characters except underscore (_) symbol and it should not contain space characters as well, it should not start with numerical values.

[Refers to]: This is nothing but what is the range of cells we are referring to.

Читать еще:  Vba excel saveas fileformat значения

I think these two parameters are good enough to start off the proceedings.

Step 4: In the name, argument enters the name you wish to give. I have named as “SalesNumbers”.

Code:

Step 5: In the refers to argument enter the range of cells we wish to create. In the name of “Rng” variable we have already assigned the range of cells as A2 to A7, so supply the argument as “Rng”.

Code:

Ok, this code will create named range for cells from A2 to A7.

Now in the worksheet, I have created some numbers from A2 to A7.

In the A8 cell, I want to have the total of the above cell numbers. Using named range, we will create a SUM of these numbers.

Code:

If you run this code manually or by pressing f5 key then, we will get the total of a named range in cell A8.

This is the basic must-know facts about “Named Ranges”.

Example #2

In VBA using RANGE object, we can refer to the cells. Similarly, we can also refer to those cells by using named ranges as well.

For example, in the above example, we have named the cell B2 as “Sales” and B3 as “Cost”.

By using actual cell reference we refer to those cells like this.

Code:

Since we already created these cells we can refer to using those names like the below.

Code:

Like this using Named Ranges, we can make use of those cells. Using these named we can calculate the profit amount in cell B4. For this first name the cell B4 as Profit.

Now in the VBA editor apply this code.

Code:

This will calculate the profit amount in the cell named “Profit”.

Recommended Articles

This has been a guide to VBA Named Range. Here we discuss how to create and use VBA named ranges to select cell or range of cells in excel along with practical examples and a downloadable template. Below you can find some useful excel VBA articles –

Excel Dynamic Named Range [4 Ways]

Excel Dynamic Named Range is one of the Excel features that not so many Excel users know about. This article will make Dynamic Named Ranges closer to you. In the further lines, hints and tricks related to Excel Dynamic Named Range will be shown.

Table of Contents

Dynamic Named Range with Excel’s OFFSET Function

In order to make a dynamic range, Offset function can be used.

Excel’s Offset Function

Here is the syntax of Excel’s OFFSET function:

OFFSET (reference, rows, cols, [height], [width])

What the arguments mean in the function:

  1. Reference – Reference from which you want to base the offset.
  2. Rows – Number of rows, up or down from the reference cell.
  3. Cols – Number of columns, right or left from the reference cell.
  4. Height, width – Define the height and width of the selection, from the referenced cell.

Dynamic Named Range with OFFSET Function

In the Name Manager dialog box, it can be used for defining Dynamic Named Ranges.

Here is how you can do that:

Go to the Formulas tab, in the Defined Names group of commands, click on the Define name command, and you should get the New Name dialog box like this:

A Dynamic Named Range will be created to select all a range of cells that starts one cell lower than A1, in the same column, and selects the number of columns and rows given in K1 and K2 cells, from the example of the image below:

The formula in the “Refers to” field looks like this:

=OFFSET(Sheet1!$A$1;1;0;Sheet1!$K$1;Sheet1!$K$2)

All the elements in OFFSET function can be made dynamic. For example, I would like to select a number of dates for some specific day.

Here are some outputs:

=OFFSET (Sheet1!$A$1; 1; 0; 6; 2) = It will refer the range A2: B7

The formula that will depend on entries in K1, K2, K3 and K4 should look like this:

=OFFSET(Sheet1!$A$1;Sheet1!$K$3;Sheet1!$K$4;Sheet1!$K$1;Sheet1!$K$2)

As any other function in Excel, OFFSET can be combined with other functions that provide the number as a result, too. Let’s say that I would like to select all the Tuesday dates except the one in the last week, by taking Monday as a reference. Based on the same example, the formula should look like this:

=OFFSET(Sheet1!$A$1;1;1;COUNT(Sheet1!$B:$B);1)

Dynamic Named Range with INDEX Function

Other functions can also be used for Dynamic Named Ranges, like INDEX, for example – you would like to select all the “Mondays”, no matter how many of them there are:

=Sheet1!$A$2:INDEX(Sheet1!$A:$A;COUNTA(Sheet1!$A:$A))

If you would like to make the reference dynamic also, you can use a combination of formula. Let’s say that you want to enter a name of a day in one cell, and then to select all the dates in the table that belong to the day.

=OFFSET(INDIRECT(ADDRESS(2;MATCH(Sheet1!$K$6;Sheet1!$1:$1;0)));0;0;COUNT(Sheet1!$A:$A);1)

Dynamic Named Range with VBA

In their everyday work, professionals face situations where they need to perform the same operation over and over. The same thing might happen when creating Dynamic Named Ranges. In order to avoid repetitive tasks, VBA can be used for this, also.

VBA works exactly in the same way as formulas, just you need to follow the syntax that is characteristic for adding Named Ranges.

The syntax goes like this:

ActiveWorkbook.names.Add Name:=”NAME”, RefersTo =”RANGE THAT YOU WANT TO SELECT”

For example, one of the previous examples can be easily applied in VBA. A complete macro would look like this:

Sub naming()

ActiveWorkbook.names.Add Name:=”NAME9″, RefersTo:=”=OFFSET(Sheet1!$A$1,0,1,counta(A:A),2)”

End sub

Dynamic Named Range with Blank cells

You can often face issues when you don’t have all the cells populated, or, in other words, some of the cells are blank. The most important is to know definitions of the formula used to find a reference. For example, COUNTA is a function that counts all NON-BLANK! cells in a range. It depends if you want to include blanks or not.

Читать еще:  Размер листа excel

The image below shows the situation with a blank cell:

If you want to select all the “Mondays”, then using the formula like in previous examples would select every Monday but the last one, since there is a blank cell – A5. The solution for this would be using a different formula – combination of the formula:

=OFFSET(Sheet1!$A$1;1;0;SUMPRODUCT(MAX((Sheet1!$A:$A<>””)*ROW(Sheet1!$A:$A)))-1;1)

It will select all the cells from A2 to A7.

The previous lines and examples should help you to perform your work faster and more efficient. Dynamic Named Ranges represent a huge area that has many options to explore and can be a very useful tool, depending on your skills and creativity. It is shown that there are many combinations, and it is up to you to find their application.

Read More

  • Use of Offset Function in Excel [Offset – Match Combo, Dynamic Range]
  • Create a Chart from the Selected Range of Cells (4 Possible Ways)
  • Excel Data Validation Based on Another Cell

Download Working File

Download the working file from the link below:

Categorized | excel chart, excel tips

Create Chart using Named Range in Excel

Creating a chart that uses a named range is excel can be accomplished by following these basic steps. Before we begin, let us first look at the building blocks.

What is a named range?

A named range is a group of cells that have explicitly been given a name. Every time you refer to that name, the workbook automatically knows that the user is referring a collection of cells that name represents. You can assign a name to a range of cells by simply selecting the cells and then entering a name for them in the name box. The cells don’t have to be contiguous and can consist of several independent ranges.

What is a dynamic named range?

A dynamic named range is a special class of named range that can expand or contract based on a value specific in a criteria. Often a user or a formula will determine that value and the dynamic named range adjusts itself accordingly. The Excel OFFSET function is quite useful here. If used along with its height and width parameters, it allows you to offset a particular cell by a certain number of rows and columns and returns the range enclosed between those cells. The number of rows and columns to offset can be decided by other cells present on the worksheet or another function itself such as COUNT. You can read more about dynamic range in excel in this article.

How do you create a dynamic named range in excel?

To create a dynamic named range, you can simply go to the menu bar and click ‘Insert’ -> ‘Name’ -> ’Define’. In the box that appears, you can simply type a name for the range and the formula that defines that range.

How can you use named range in a chart?

A chart can make use of a named range in the same manner in which it would use a normal range. So rather than using a normal range, you simply enter the name of the named range as the chart’s source data. Infact if you use multiple series in a chart and they are located in different sheets and locations, providing a name to the source data range can result in an immense improvement in the readability of the chart.

In our example, let’s say that you were maintaining a list of students along with their marks in physics. You wanted to create a chart in excel that shows the scores of the students one after another. Now if you were to create a chart by specifying a range in excel, your customer list can always exceed the range specified. Also for some type of charts, for example line charts, the empty cells in the yet unfilled cells in the range can still be treated as 0 and hence may show a wrong picture. In this case, the chart will simply go from the last point straight to 0 and that can startle people (especially your head of department).

Named Range in Excel 2003 and Earlier

We can create a a named range in Excel 2003 and earlier versions using the menu -> ‘Insert’ -> ‘Name’ -> ’Define’ option.

Named Range in Excel 2007 and Later

We can create a a named range in Excel 2007 and later by clicking on the “Formulas” button in the ribbon and then the “Name Manager”.

For a simple chart with date and values You can create two ranges, one that contains the cells with the names and the other with the scores. In the example above, we define a new named range ‘scores’ as =OFFSET(Sheet1!$B$1,1,0,COUNTA(Sheet1!$B:$B)-1,1). You can work out the formula for the range that contains the names.

We can now simply create an empty chart. Then right click on the chart and click ‘Source Data’. In the ‘Series’ section, press ‘Add’. In the ‘Values’ box, type Sheet1!scores (the basic format is =SheetName!Name_Of_Range). Press ‘ok’. Similarly for the category labels, you can specify the ‘names’ range to be picked up. And there you are, ready with your excel chart with named ranges.

Now you can keep on typing as many name and score combinations as you want and the chart will keep expanding to accommodate all of them automatically.

You can download a sample worksheet with an example of chart using a named range in excel here or click on the button below:

Before we leave just a small tip: Incase the scores themselves were dynamic and were dependent upon other cells, you could face issues in terms of the chart not updating properly using the named range. In such a case, you can pick up the cell that was used as the offsetting point and add the volatile RAND() function to it. A volatile function gets recalculated every time a value changes in a worksheet and causes the chart to read the updated values. You can use =RAND() * 0 to minimize any side-effects

Ссылка на основную публикацию
ВсеИнструменты
Adblock
detector
×
×