In order to use MS Excel formulas properly we need to have clarity about the different types of cell references. We’ve already discussed some of them. But today, we will discuss the concept of names, which make it easier to build, use, and understand formulas.
Names can be used to name MS Excel objects like cells, cell ranges, tables, formulas, constants, etc. These can then be referenced in any other object.
Let us further our understanding with the help of an example. I need to calculate the simple interest for the following data. I have given names to the cells B1, B2, and B3. So when I write the formula for SI, I can simply use the names rather than the cell references.
How to Create Names
1. There are many ways to create names. The first and the easiest method is to use the Name text box on the top left of the sheet, just below the ribbon and the task bar. When you have selected a cell, a range or any object, type a name in the box and hit Enter.
Note: When you create a name in this fashion, the scope of the name is limited to the current worksheet. So, if you want to use it in a different sheet you have to precede it with the sheet name.
2. Let’s try the second method. For this, select a range, navigate to Formulas -> Defined Names and click on Define Name.
Give the selected object a name and click on OK. This is where you can select the scope of the name for the entire workbook instead of just one sheet.
3. Create from selection is the coolest way to create names. Generally, we have the tendency to name data (rows and columns) based on the header. When that’s what’s needed, select your data and hit on Create from Selection.
A dialog window will pop up. Select row/column headers you wish to use for the data. In this case, I want only the top row to be used.
How to Use Names
It’s simple – whenever you want to use a name in a formula, start typing and you will see it appear in the suggestion list for you to select.
Otherwise navigate to Formulas -> Defined Names and select the required name from Use in Formula dropdown.
How to Manage Names
You must also have the option to edit and delete names in case you want to change few definitions or get rid of them entirely. That’s where the Name Manager comes into picture.
It also shows you details of values, cell and sheet references, and the scope of the name.
Cool Tip: When you create tables on a worksheet they are assigned names automatically. The automatic names will be Table1, Table2 and so on. However, you may change the names from the Name Manager window.
These are the basics of implementing and managing names that any Excel user should know. Once you get used to names, you can create and use formulas with ease. It may take some time to learn and get into this habit, but I suggest you put in the effort. It’s well worth it!