Microsoft Excel – using named cells instead of absolute cell references and updating formulas based on them

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.

Apply Names dialig box

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.

Find dialog box

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.

Replace dialog box

The relevant names will be now used throughout the workbook instead of the absolute cell references.

Find out more about our Microsoft Excel training Sydney, Australia and our Microsoft Excel training London, Surrey, Middlesex or our Microsoft Excel training Wiltshire, Hampshire, Dorset,UK.

About jdonbavand

I am a trainer of Microsoft Office, Microsoft Project and Crystal Reports. I have called my blog "If Only I'd Known That...." because I hear it so many times in training sessions. In fact, if only I had a £100 (or 150 Aussie dollars)for every time someone says "If only I'd known that." ....
This entry was posted in Microsoft Excel 2003, Microsoft Excel 2007, Microsoft Excel 2010, Microsoft Excel 2013 and tagged , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s