Excel Functions and Formulas – Naming

 Excel Functions and Formulas – Naming

Excel Functions and Formulas – Naming

Naming cells or range of cells is very useful in Excel especially in case of complex calculations as they help to understand the operation clearly. Instead of using cell names like A1 or B5, you can give meaningful names to the cells and use these names in formulas. To do this, you need to go to Formulas Defined Names Define Name which will open a New Name dialogue box or use shortcut key Ctrl+F3 which will open a Name Manager window. New Name window can be used only to give names to new cells or range of cells whereas Name Manager window can be used to add, edit or delete names.

Suppose, you name F5 as check1 and F6 as check2 and specify the formula check1-check2 in F7 and enter values 56 and 50 in F5 and F6 respectively, then F7 will contain the value 6. You can also specify names for a range of cells. You can use the names of these ranges also in formulas. Select the range of cells and right click and select Name a Range option to name the range.

To access the names, you just need to click F3 when you are entering values in the formula bar. It will open a Paste Name window that contains all names, so that you need not have to type names thus avoiding chances of making mistakes. Another interesting fact is that you have the freedom to name even formulas or constants. Only thing is that you need to start formula definition with equal sign in the Refers to: section.

You can make use of naming for easy understanding and reference. You have to take care while choosing names. Always give meaningful names. If the name contains more than a word, then join them with an underscore. Though you can use a combination of numbers and letters for names, you should not start name with a number. Absolute referencing is used by default in case of cells. You can edit it as you need by going to Refers to: area. The names you created would be available throughout the workbook.

If you wish to access values from a different worksheet for any of your calculations, that is also possible. The syntax is sheet name! cell reference. For example, if Sheet1 contains marks of first term exam and Sheet2 contains marks of second term exam and in Sheet3, you need to calculate total marks by adding marks in Sheet1 and Sheet2, it can be accomplished. To make it simpler, assume that cell F9 of Sheet1 and cell E5 of Sheet2 contains marks of a student. Now, if you wish to calculate the total marks in the cell G10 of Sheet3, then you should write the formula as =Sheet1!F9+Sheet2!E5. You will get the total marks in G10 of Sheet3. If there are spaces in the name of the worksheet, then you should enclose sheet name in single quotes.

You can access values from different workbooks also. But, if you do not have the workbook opened while referencing it, then you need to specify the full path of it. The actual syntax is [workbook.xls]sheetname!cell reference. If there are spaces in the name of the workbook, then you should enclose workbook name and sheet name in single quotes. Anyhow, it is always safe to specify the complete path of the workbook, as there is no guarantee that the referencing workbook would be opened. So, if the workbook Sample.xls is saved in the folder Excel Files in D drive, then you need to reference it as ‘D:\Excel Files\[Sample.xls]Sheet1’!A2. This will not make any trouble whether two Excel files are opened or not.

Read More about Excel here


Related post

Leave a Reply