Recently I was training a group of people who had large workbooks with a lot of formulas containing absolute cell references. I taught them how to name cells which not only saves the need to use absolute cell references but works right across the workbook. They were keen to use this in their workbooks but wondered how they could update all their current formulas if they started naming cells.
The quickest way to name a cell is to click in the cell, then type a name in the Name box to the left of the formula bar. Names cannot have a space in them and you need to remember to press the Enter key when you have named the cell.
To apply the name to formulas just in one worksheet, on the Formulas tab, in the Defined Names group, click the Define Names dropdown and click Apply Names. The Apply Names dialog box will be displayed.
Select all the names by dragging over them, and click OK. The names will be applied to all relevant places in the worksheet.
However, this only works for one worksheet and you may have several worksheets using the absolute cell references. You cannot group the worksheets together and apply the same technique.
In this scenario, go to the Home tab and in the Editing group, click Find and Select dropdown and click Find. The Find dialog box will be displayed. Click Options to open up the options. Ensure that Workbook is chosen from the Within dropdown and that Formulas is selected from the Look in dropdown.
In the Find what box type the first absolute cell reference which should be replaced by a name. Cick on the Replace tab, in the Replace with box, type in the cell name. Click Replace All.
The relevant names will be now used throughout the workbook instead of the absolute cell references.