Unit 4. Basic. References and names(II)


Names

 

As human beings we are more accustomed to name things by their name than by a reference number, as we perceive them to be thus clearer and more meaningful. We could look at a relatively simple formula, but if it contains many reference numbers we could find it difficult to interpret

For this reason Excel eases the procedure a little by allowing us to give a name to cells and to formulas in order to be able to identify them more easily.

 

To assign a name to a cell, we need to access the Insert > Name > Define... menu, or press the combination of Ctrl+F3.

 

definir nombresThe Define name dialogue box is opened as we can see in the image.

In the Names in workbook: we write the name that we want to assign to the cell.

In Refers to: we write the cell reference as we can see in the image.

It is important to write the full denotation and to use complete references ($A$1).

Excel will automatically fill the Refers to: box with the reference of the active cell, and so it can be easy to first position yourself in the cell which we want to assign a name to, and then to open the Define name dialogue box, this way we will have the box filled with the correct reference.

 

 

 

Create a constant.

A constant is an element that always has the same value, eg the mathematical constant pi always has the same value" 3,1415..." or our own name.

We can create constants without needing to use a cell in our sheet, and they can be numeric constants or text.

To create a constant we open the Define name dialogue box in the same way, by selecting Insert > Name > Define...

In the Names in workbook: we write the name that we want to assign to the cell.

In Refers to: we write the value of the constant without including the = symbol.

To finish we clic on OK.

 

We can create formulas with names in the same way, and without using a cell to save the formula in. We open the Define name dialogue box in the same way, we write the name of the formula, and in the Refers to: field we write the formula, in this case it is necessary to use the = symbol.

We can then later use this name instead of writing the formula.

We can also give names to existing formulas situated in cells on the sheet, following the same steps as we saw before, and in the Refers to: field we indicate where the formula terminology is found =Sheetwhereitisfound!Cell.

Later, to make a reference to this formula from another cell we put =formulaname

Eg: We write the formula =SUM(A1:A8) in cell B1 and we name the cell Total, we then simply put =Total in cell D5 and it will perform the operation of the formula that we defined, in this case we have used relative references, it will add from cell C5 to cell C13.

 

Apply name changes to references

 

When we assign names to diverse cells, the formulas that make reference to these cells are not automatically changed by substituting the terminology/nomenclature??? of column-row for the name.

Cuando asignamos nombres a diversas celdas, las fórmulas que hagan referencia a estas celdas no se cambian automáticamente sustituyendo la nomenclatura de columna-fila por el nombre.

Eg: We have the formula =A1+B1 and afterward we associate cell A1 with the name Expenditure 1, and cell B1 with the name Expenditure2. In the formula it will still appear as =A1+B1

In order for the names of all the references to be updated we need to go to the menu bar, to Insert > Name > Define... In the Apply names dialogue box that appears we need to select the names that we want apply and then clic on Accept.

 

We will now see the formula as =Expenditure1+Expenditure2, which is much clearer if we know which cells Expenditure1 and Expenditure2 are referring to.

 

 

 



   
   
Legal warning: Authorised on-line use only. It is not allowed the use of these courses in companies or private teaching centres.
© aulaClic. All rights reserved. Reproduction in any form whatsoever is prohibited.
November-2005.