Force Byte - Naming Cells and Ranges in Excel

0 Photo

You can assign names to cells and ranges in Excel and then use them instead of cell references in defining formulas such as "=Sheet1!A1+Sheet1!A2". Names are more convenient because they eliminate the need to type complex cell references. For example, "Police" instead of "Sheet1!A2". In addition, "=HK+Police" can be the equivalent of "= Sheet1!A1+Sheet1!A2" if you name "=Sheet1!A1" as "HK" and "Sheet1!A2" as "Police" respectively. Moreover, this also allows you to refer to named cells from anywhere in the workbook, or even from another workbook.

I will now explain how to define names using the "Name Box" and the "Name Command".

The easiest way to define a name is to use the "Name Box" in the formula bar. First select the cell or the range to be named, then click the box and type your defined name and press . You should note that if a selected cell or range is named, the name will take precedence over the cell reference and be displayed in the Name Box. Besides, you cannot use spaces in a name. Excel only accepts "HK_PF" and not "HK PF". Furthermore, when you define a name for a range of cells, the range name will only appear in the Name Box if the entire range has been selected.

Alternatively, you can choose "Name" and then "Define" under the "Insert" menu item or simply press and keys simultaneously to bring up the "Define Name" dialog box. Type your defined name in the "Names In Workbook" edit box and then type "=" followed by the selected cell or range (e.g. =D20 or =A1:A10) in the "Refers To" edit box. Lastly, click the "Add" button. When the "Define Name" dialog box is opened, you can also insert references in the "Refers To" edit box by dragging through cell(s) in the worksheet by mouse or keyboard. To close the dialog box, you just click the "OK" or the "Close" button.

"Sharing IT as it applies to your daily life"