Names are very useful in Excel. And today we will talk about the names of certain ranges.
The range in Excel can be a cell or a certain part in Excel. You can certainly refer to a certain range by using the row numbers and column numbers. But on the other hand, you can also define a name for a certain range that you will refer to. If you delete a row or a column, the row and column number of target range will change. And this may produce errors in formulas that will refer to this range. However, using names can avoid this problem. Below are two methods to define names for a certain range.
Method 1: Define Name in Name Box
Usually the reference of a range can be found in the name box.
In this image, when we click cell A1, and the reference A1 appears in the name box. Using this method, you can directly define a name in the name box. Now suppose we want to change the name of cell C2 in the worksheet.
- Select cell C2.
- And then click the name box.
- Now you can input a name into the name box. For example, here we input “John” into the box because this is John’s sales volume.
- Next press “Enter”. Thus, you have defined a name for cell C2. When you select C2, you will see “John” instead of “C2” in the name box. Besides, if you input the “John” into the name box directly, the cursor will automatically position on cell C2. Therefore, this can also help you find a certain range directly.
On the other hand, if you want to modify the name, it is not suggested that you change it directly in the name box. Still take the above worksheet as an example. Suppose you want to change the “John” into “JohnExcel”. When you directly change it in the name box and then press “Enter”, you will still see the text “John” in the box. When you input the “JohnExcel” into name box, the cursor will also position on C2. This is because you have defined two names for the same cell if you modify directly in the name box. To avoid the error, you may continue following the left part of this article.
Method 2: Define Name in the Toolbar
The second method should be finished using the toolbar.
- Click the tab “Formulas” in the ribbon.
- Next click the button “Define Name”.
- And then you will see the “New Name”. Input the name into the text box.
- If you want use the name in the whole workbook, you can choose the scope of “Workbook”. But if you want to use it in a certain worksheet, you can also choose the worksheet here.
- In the “Refers to” text box, input the reference of the target cell. You can also use your mouse to select the cell directly.
- After that, click “OK”. Thus, you have defined name for Cell C2.
The default scope of a name created by the first method is “Workbook”. However, in this method, you can choose the scope of the name and add comments. This is exactly the advantage of using the second method. In addition, we only define name for a cell in this example. But the steps are the same if you want to define names for other ranges.
We have explained the shortcoming that will appear when modifying name directly in name box. Here you can use a better method.
- Click the tab “Formula” in the ribbon.
- Next click the button “Name Manager”.
- In the “Name Manager” window, choose the name that you want to modify.
- And then click the button “Edit”.
- In the “Edit Name” box, input the new name that you want to modify. You may also notice that the option of scope is not available here. In this step, you can only change the name or the range.
- When you finish the edit, click “OK”.
Thus, the name has changed and you will not create two names for the same range.
When it comes to delete names, don’t try to delete it in the name box. You may hold the idea that if you input the original cell reference such as C2 into the name box, the name that you have earlier will disappear. Actually this will not take effect. The specific name will still appear in the name box. Thus, you need to delete it through name manager.
- Open the “Name Manager” window.
- Choose the name that you want to delete.
- And then click the button “Delete”.
Thus, you can delete the name from the workbook.
Excel Corruption will Wipe out Names in Workbook
We have known that names are very useful in workbook. However, if you meet with Excel corruption, all the names will face the danger of being wiped out. At this time, you will certainly want to fix Excel and get back all the settings. But before you try to fix it, you need to figure out why Excel corrupts. You can contact out proficient programmer for help. And with our cutting-edge Excel repair software, the risk that you suffer will be reduced to a minimum.
Anna Ma is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including word recovery and outlook repair software products. For more information visit www.datanumen.com