Leading the way in Microsoft Office Development

Replacing your existing cell references

It is possible to replace existing cell references with their corresponding named ranges.

This lets you substitute named ranges for cell and range references in your formulas.

Using the (Insert > Name > Apply) dialog box

Click the Options button to display all the options.

Ignore Relative/Absolute - Replaces references with names regardless of the type of reference. This should normally be left checked.

Use row and column names - This is if you want to apply names in intersection cases.

Omit column name if same column - Allows you to insert the column range even when the cell is in the same column.

Omit row name if same row - Allows you to insert the row named range even when the cell is in the same row.

Name Order - This controls the order in which the row and column components appear.

Excel finds all the cells and ranges of cells that have named ranges assigned to them.

If you select a single cell before displaying the dialog box then all names in the whole workbook are applied ?

If you select a range of cells before displaying the dialog box then only names to that area are applied ?

Either select the cells containing the formulas that you want converted to named ranges or select a single cell if you want all the formulas converted.

If you only want to convert to cell references in one formula, then select that cell plus a blank one.

Excel normally does not apply the column or row name if either is superfluous ??

Things to Remember

Most named ranges use absolute cell references.