While doing data evaluation, referring to numerous data will be extra meaningful and easy if the reference is through Names instead of cell references – either a single cell or a number of cells. for instance, if you are calculating net present value based on a reduction rate and a series of cash Flows, the formula
Net_Present_Value = NPV (Discount_Rate, Cash_Flows)
Is more meaningful than
C10 = NPV (C2, C6:C8)
With Excel, you can create and use meaningful names to various parts of your data. The advantages of using range names include −
In this chapter, you will learn –
Type the first letter of the name in the formula. A drop-down box appears with function names and range names. Select the required name. It is copied into your formula.
Excel has the following syntax rules for names −
You can create Range Names in two ways −
To create a Range name, using the Name box that is to the left of formula bar is the fastest way. Follow the steps given below −
Step 1 − Select the range for which you want to define a Name.
Step 2 − Click on the Name box.
Step 3 − Type the name and press Enter to create the Name.
You can also create Range Names using the New Name dialog box from Formulas tab.
Step 1 − Select the range for which you want to define a name.
Step 2 − Click the Formulas tab.
Step 3 − Click Define Name in the Defined Names group. The New Name dialog box appears.
Step 4 − Type the name in the box next to Name
Step 5 − Check that the range that is selected and displayed in the Refers box is correct. Click OK.
You can also create range names using the Create Names from the selection dialog box from formulas tab, when you have text values that are adjacent to your range.
Step 1 − Select the range for which you want to define a name along with the row / column that contains the name.
Step 2 − Click the Formulas tab.
Step 3 − Click Create from Selection in the Defined Names group. The Create Names from Selection dialog box appears.
Step 4 − Select top row as the Text appears in the top row of the selection.
Step 5 − Check the range that got selected and displayed in the box next to Refers to be correct. Click OK.
Now, you can find the largest value in the range with =Sum(Student Name), as shown below –
You can create names with multiple selection also. In the example given below, you can name the row of marks of each student with the student’s name.
Now, you can find the total marks for each student with =Sum (student name), as shown below.
Suppose you have a constant that will be used throughout your workbook. You can assign a name to it directly, without placing it in a cell.
In the example below, Savings Bank Interest Rate is set to 5%.
The Name Savings_Bank_Interest_Rate is set to a constant 5%. You can verify this in Name Manager. You can see that the value is set to 0.05 and in the Refers to =0.05 is placed.
An Excel Workbook can have any number of named cells and ranges. you may manage these names with the name manager.
The List of Names are displayed with the defined Values, Cell Reference(including Sheet Name), Scope and Comment.
The Name Manager has the options to −
The Scope of a name by default is the workbook. You can find the Scope of a defined names from the list of names under the Scope column in the name manager.
You can define the Scope of a new name when you define the name the use of New name dialog box. for example, you are defining the call Interest_Rate. Then you can see that the Scope of the new name Interest_Rate is the Workbook.
Suppose you want the Scope of this interest rate restricted to this Worksheet only.
Step 1 − Click the down-arrow in the Scope Box. The available Scope options appear in the drop-down list.
The Scope options include Workbook, and the sheet names in the workbook.
Step 2 − click the current worksheet name, in this case NPV and click ok. you can define / find the sheet call in the worksheet tab.
Step 3 − To verify that Scope is worksheet, click Name manager. in the Scope column, you will find NPV for Interest_Rate. this means you can use the name Interest_Rate only in the Worksheet NPV, but not in the other Worksheets.
Note − Once you define the Scope of a Name, it cannot be modified later.
Sometimes, it may so happen that Name definition may have errors for various reasons. You can delete such names as follows −
Step 1 − Click Filter in the Name Manager dialog box.
The following filtering options appear −
You can apply Filter to the defined Names by selecting one or more of these options.
Step 2 − Select Names with Errors. Names that contain error values will be displayed.
Step 3 − From the obtained list of Names, select the ones you want to delete and click Delete.
You will get a message, confirming delete. Click OK.
You can use the Edit option in the Name Manager dialog box to −
Step 1 − Click the cell containing the function Large.
You can see, two more values are added in the array, but are not included in the function as they are not part of Array1.
Step 2 − Click the Name you want to edit in the Name Manager dialog box. In this case, Array1.
Step 3 − Click Edit. The Edit Name dialog box appears.
Step 4 − Change the Name by typing the new name that you want in the Name Box.
Step 5 − Click the Range button to the right of Refers to Box and include the new cell references.
Step 6 − Add a Comment (Optional)
Notice that Scope is deactive and hence cannot be changed.
Click OK. You will observe the changes made.
Consider the following example –
As you observe, names are not defined and used in PMT function. if you place this function somewhere else in the worksheet, you also need to remember where exactly the parameter values are. that using names is a better option.
In this case, the function is already defined with cell references that do not have names. you can still define names and apply them.
Step 1 − using Create from selection, define the names.
Step 2 − select the cell containing the formula. click select Button next to Define name in the Defined Names group on the formulas tab. From the drop-down list, click on Apply Names.
Step 3 − The Apply Names dialog box appears. Select the Names that you want to Apply and click OK.
The selected names will be applied to the selected cells.
You can also Apply Names to an entire worksheet, by selecting the worksheet and repeating the above steps.
You can use a Name in a Formula in the following ways −
You can get all the Names in your workbook along with their References and Save them or Print them.
The list of names and their corresponding references are copied at the specified location on your worksheet as shown in the screen shot given below –
Range Intersections are those individual cells that have two ranges in common.
For example, in the data given below, the range B6:F6 and the range C3:C8 have cell C6 in common, which actually represents the marks scored by the student Kodeda, Adam in exam 1.
You can make this more meaningful with the Range Names.
Here, you are using the Range Intersection operation, space between the two ranges.
This will display marks of Kodeda, Adam in Exam 1, that are given in Cell C6.
You can copy a formula with names by Copy and Paste within the same worksheet.
You can also copy a formula with names to a different worksheet by copy and paste, provided all the names in the formula have workbook as Scope. Otherwise, you will get a #VALUE error.
Excel Data Analysis Related Tutorials
|VBA For Excel Tutorial||Microsoft Excel Tutorial|
|Microsoft Word Tutorial||Microsoft Powerpoint Tutorial|
|Microsoft Entity Framework Tutorial||Microsoft Azure Tutorial|
|Microsoft Project Tutorial||Advanced Excel Charts Tutorial|
|Advanced Excel Functions Tutorial||Excel Dashboards Tutorial|
|Excel pivot tables Tutorial||Excel Power View Tutorial|
Excel Data Analysis Related Interview Questions
|VBA For Excel Interview Questions||Microsoft Excel Interview Questions|
|Microsoft Word Interview Questions||Microsoft Powerpoint Interview Questions|
|Microsoft Entity Framework Interview Questions||Microsoft Azure Interview Questions|
|Microsoft Project Interview Questions||Excel Data Analysis Interview Questions|
|Excel pivot tables Interview Questions||Excel Power View Interview Questions|
|Microsoft Interview Questions|
All rights reserved © 2020 Wisdom IT Services India Pvt. Ltd
Wisdomjobs.com is one of the best job search sites in India.