Named Fields in Excel

    

613-749-4695
[email protected]

Home Page

Tips and Ideas on Excel
Table of Contents on Excel

Tips and Ideas on Macros
Table of Contents on VBA

Quick Links

 

Excel Chapter 8A of 24: Name Fields in Excel

In Excel you can name  cells and sets of cells. Naming cells can be useful to simplify the writing of formulas specially the use of the dollar signs to make references absolute.

For example:

=A1 and =nfMyField are  the same if you have named cell A1 "nfMyField"
=SUM(A1:A34) and =SUM(nfMyField) are  the same if you have named the range A1 to A34  "nfMyField"

You will notice that it becomes very useful with SUMPRODUCT and INDEX/MATCH formulas.

Instead of writing a formula like this:
=SUMPRODUCT(($G$2:$G$20=$A2)*($H$2:$H$20=B$1)*($I$2:$I$20))
you will write this:
=SUMPRODUCT((nfColG=$A2)*(nfColH=B$1)*(nfCoI))

Instead of writing a formula like this:
=INDEX($G$2:$G$20,MATCH(B1,$H$2:$H$20,0)1)
you will write this:
=INDEX(nfColG,MATCH(B1,nfColH,0),1)

In these 2 previous formulas MAKE SURE that both named fields have the same number of cells.

Naming a field in Excel

To name a cell or a set of cells (range) in Excel you select it or them and you go to the Name  Box at the top right of the screen just above the column line and beside the formula bar. The "Name Box" tooltip appears when you put the cursor above it.:

Excel Name Box

In the Name Box above you see B3 which is the address of the cell that is selected. Click in the name box and write nfMyField and THEN CLICK ENTER. The selected cell or set of cells in now named. Select any other cell, go to the small arrow right of the name box and click on it. You see a list of your named field. Select nfMyField and the cell or the set of cells that you have named gets selected. Always use the prefix " nf" when you name a field you will not regret it when you start programming in VBA.

Remember that after naming a field in the Name Box YOU MUST CLICK ON ENTER.

Maintaining Named Fields in Excel

Sometimes you want to delete a named field or modify the cell or cells that it comprises. Go to "Insert/Name/Define" and you will see the following dialog box.

Excel define names

If you want to delete a named field select it in the list and when it appears in the box over the list click on "delete" . To modify its range go to the box "Refers to" change the addresses and then CLICK ON ADD. Don't forget to CLICK ON ADD.

As you can see instead of using the quick method above (in the Name Box) to name fields you can also do it in this dialog window.

 

Next Chapter: Signs and Formulas in Excel

Excel Table of Contents
or use the quick links below

 

Discover Even More in 50 Excel spreadsheets

 

    

613-749-4695
[email protected]

Home Page

Tips and Ideas on Excel
Table of Contents on Excel

Tips and Ideas on Macros
Table of Contents on VBA

Quick Links

Named Fields in Excel